MariaDB必知必会

MariaDB是基于MySQL的,大部分MySQL的知识在MariaDB上也相同。

本篇文章主要罗列基本的SQL语法及MariaDB的一些特性。
注:所有的SQL语句均以英文的半角分号结尾,所有以$(美元符号)开头的字段均为相关用户字段(例如数据库名称,表名称)

列出数据库:
-
SHOW DATABASES;

选择数据库:
-
USE $DATABASE_NAME;

列出数据库的表:
-
SHOW TABLES;

列出表的详细信息:
-
DESCRIBE $TABLE_NAME;

基础检索语句:
-
SELECT * / ($TABLE_NAME.)$COLUMN_NAME FROM ($DATABASE_NAME.)$TABLE_NAME WHERE $CLAUSE LIMIT $AMOUNT / $START,$AMOUNT ORDER BY $COLUMN_NAME ASC / DESC

WHERE子句:
-
可以使用AND OR IN NOT操作符进行高级操作
IN用逗号隔开括号中列出的所有合法值 例如 WHERE id IN (1002,1003)
注:MariaDB支持对IN BETWEEN EXISTS语句使用NOT

通配符:
-
在WHERE语句中,可以使用LIKE配合通配符进行条件过滤
%表示零个、一个或多个字符
_表示一个字符

正则表达式:
-
WHERE语句中可以使用REGEXP配合正则表达式过滤数据

计算字段:
-

  • 拼接字段
    使用Concat( )函数

例如Concat(name,'(',country,')')

  • 删除空格
    RTrim( )删掉右面所有的空格

LTrim( )删掉左面所有的空格
Trim( )同时裁剪左右两边

  • 别名
    使用AS操作符

例如title AS new_title

  • 算术运算
    SELECT语句中直接使用+ - * /符号即可进行计算

数据汇总
-
可以使用AVG( ) COUNT( ) MAX( ) MIN( ) SUM( )计算平均值、数量、最大值、最小值、总量
可以在函数中使用DISTINCT关键字,只计算不同的值

数据分组
-
使用GROUP BY子句创建分组
使用HAVING分句过滤分组(在分组中使用WHERE是无效的)

子查询
-

  • 可以使用嵌套进行子查询

多表连接
-

  • WHERE语句中可以判断外键
  • 内连接-join
    FROM $TABLE_NAME JOIN $TABLE_NAME ON $TABLE_NAME.$COLUMN_NAME = $TABLE_NAME.$COLUMN_NAME
  • 外连接
    INNER JOIN返回两表所有行

LEFT OUTER JOIN返回左边所有行
RIGHT OUTER JOIN返回右边所有行

表别名
-

  • 不仅可以给字段添加别名,也可以给表添加别名,使得在后面的WHERE语句中更加简便,例如FROM $TABLE_NAME AS $ALIAS_NAME WHERE $ALIAS_NAME.$COLUMN_NAME = ……

联合查询
-
使用UNION操作符可以讲若干个查询语句结果组合为一个。

适用条件:

  • 每个SELECT语句之间均需要使用UNION连接
  • 每一个查询都需要包含相同的列、表达式、聚合函数
  • 列数据类型必须兼容(MariaDB可以相互转换)

返回相同行:
UNION对于相同行默认只显示一行,如果需要显示多行,可以使用UNION ALL操作符。

ORDER BY能且只能出现在最后一个SELECT语句后面,不允许使用多个ORDER BY语句。

全文本搜索
-
针对需要搜索的字段,需要在创建数据表的时候指定FULLTEXT($FIELD_NAME),全文搜索默认不区分大小写,如果需要区分,请使用BINARY模式

语法:
SELECT * / ($TABLE_NAME.)$COLUMN_NAME FROM ($DATABASE_NAME.)$TABLE_NAME WHERE Match($COLUMN_NAME) Against($EXPRESSION)

返回结果会按照相关度降序排列

拓展搜索:
Against($EXPRESSION WITH QUERY EXPANSION)

布尔全文搜索:
可以使用全文本布尔操作符对文本进行更加详细的过滤,此处略。

插入语句基本语法
-
INSERT INTO $TABLE_NAME($COLUMN_NAMES) VALUES($VALUES);

更新语句基本语法
-
UPDATE $TABLE_NAME SET $FIELD_NAME = '$VALUE' WHERE $CLAUSE;

删除语句基本语法
-
DELETE FROM $TABLE_NAME WHERE $CLAUSE;

创建数据表基本语法
-

CREATE TABLE $TABLE_NAME
{
    $COLUMN_NAME    $TYPE    NOT NULL/NULL AUTO_INCREMENT,
    $COLUMN_NAME    $TYPE    NOT NULL/NULL DEFAULT $DEFAULT_VALUE,
    PRIMARY KEY ($COLUMN_NAME)
} ENGINE = $ENGINE_NAME;
  • 不允许使用函数作为默认值,只支持常量

更新表
-
增加列:
ALTER TABLE $TABLE_NAME ADD $COLUMN_NAME $TYPE;

删除列:
ALTER TABLE $TABLE_NAME DROP $COLUMN_NAME;

删除表:
-
DROP TABLE $TABLE_NAME;

重命名表:
-
RENAME TABLE $TABLE_NAME TO $TABLE_NAME;

视图:
-
创建视图:
CREATE VIEW $VIEW_NAME AS $CLAUSE

使用视图:
SELECT …… FROM $VIEW_NAME WHERE $CLAUSE

触发器:
-

CREATE TRIGGER $TRIGGER_NAME AFTER/BEFORE INSERT/DELETE/UPDATE ON $TABLE_NAME
FOR EACH ROW
BEGIN
    $CLAUSE;
END;

事务管理:
-
事务开始:
START TRANSACTION;

添加保存点:
SAVEPOINT $SAVEPOINT_NAME;

回滚:
ROLLBACK;
ROLLBACK TO $SAVEPOINT_NAME;

提交:
COMMIT;

用户管理
-
列出用户:
USE mysql;
SELECT user FROM user;

添加用户:
CREATE USER $USER_NAME IDENTIFIED BY $PASSWORD;

修改用户名:
RENAME USER $USER_NAME TO $USER_NAME;

修改密码:
SET PASSWORD FOR $USERNAME = Password('$PASSWORD');
SET PASSWORD = Password('$PASSWORD'); 设置当前登录用户的密码

删除用户:
DROP USER $USER_NAME;

列出权限:
SHOW GRANTS FOR $USER_NAME;

授予权限:
GRANT $GRANTS ON $DATABASE_NAME.$TABLENAME TO $USER_NAME;

撤销权限
REVOKE $GRANTS ON $DATABASE_NAME.$TABLENAME FROM $USER_NAME;

存储过程(PROCEDURE)、游标(CURSOR)

待补充

标签: none