MySQL 补充（来自MySQL必知必会）

MySQL 补充

SELECT 子句顺序

SELECT    # 必须
FROM      # 仅从表中选择数据时使用
WHERE     # 仅从表中来过滤行级数据时使用
GROUP BY  # 仅按组计算数据时使用
HAVING    # 组级别的过滤
ORDER BY  # 排序时使用
LIMIT     # 限制检索的行数

DISTINCT 去重

DISTINCT 必须放在所有字段的前面，并且它的作用范围是后续的所有字段，而不是单独某个字段：

mysql> select * from B;
+----+------+------+
| id | name | aid  |
+----+------+------+
|  1 | wang |    1 |
|  2 | han  |    1 |
|  3 | li   |    2 |
+----+------+------+


mysql> select distinct aid, name from B;  # 作用于 (aid, name) 上，而不是仅仅 aid 上
+------+------+
| aid  | name |
+------+------+
|    1 | wang |
|    1 | han  |
|    2 | li   |
+------+------+


mysql> select name, distinct aid from B;  # distinct 必须放在所有字段前面，否则报错。
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct aid from B' at line 1

Order by 排序

order by 可以对多个字段排序，它会先对第一个字段排序，如果第一个字段有重复相同的值，则对这个重复的值按照第二个字段排序...

正则表达式

mysql 也支持正则表达式，它和 like 关键字有些像，都可以匹配字段的值。

mysql 的正则表达式，如果需要转义某个字符，需要使用 \\ 。如转义 . 需要: \\. ; 转义 \ 自身需要：\\\

譬如：

mysql> select * from B;
+----+------+------+
| id | name | aid  |
+----+------+------+
|  1 | wang |    1 |
|  2 | han  |    1 |
|  3 | li   |    2 |
|  4 | li.  | NULL |
|  5 | aa   | NULL |
+----+------+------+




mysql> select name from B where name regexp '\\.';  # 转义 .
+------+
| name |
+------+
| li.  |
+------+


mysql> select name from B where name regexp '.an';
+------+
| name |
+------+
| wang |
| han  |
+------+



mysql> insert into B(name, aid) values ('Wang', 1);
mysql> select name from B where name regexp binary 'Wang';  # 使用 binary 关键字来区分大小写（默认不区分大小写）
+------+
| name |
+------+
| Wang |
+------+

函数

更多函数，查看：https://www.runoob.com/mysql/mysql-functions.html

CONCAT 拼接字符

CONCAT(str1, str2, ...) 可以用来拼接字符

mysql> select * from B;
+----+------+------+
| id | name | aid  |
+----+------+------+
|  1 | wang |    1 |
|  2 | han  |    1 |
|  3 | li   |    2 |
|  4 | li.  | NULL |
|  5 | aa   | NULL |
|  6 | Wang |    1 |
+----+------+------+


mysql> select concat(name, '(', aid, ')') from B;
+-----------------------------+
| concat(name, '(', aid, ')') |
+-----------------------------+
| wang(1)                     |
| han(1)                      |
| li(2)                       |
| NULL                        |
| NULL                        |
| Wang(1)                     |
+-----------------------------+

LENGTH

计算字符串的长度

mysql> select length('a');
+-------------+
| length('a') |
+-------------+
|           1 |
+-------------+

RTRIM

移除字符串右侧的空白符，同样的，还有：移除左侧空白符的 ltrim, 以及移除左右两侧空白符的 trim

mysql> select length(rtrim('a  '));
+----------------------+
| length(rtrim('a  ')) |
+----------------------+
|                    1 |  # 长度变成 1 了
+----------------------+

UPPER， LOWER

将字符转换成大写或小写：

mysql> select upper('a'), lower('A');
+------------+------------+
| upper('a') | lower('A') |
+------------+------------+
| A          | a          |
+------------+------------+

LEFT

left(str, num), 截取左侧几个字符串：

mysql> select left('abc',2);
+---------------+
| left('abc',2) |
+---------------+
| ab            |
+---------------+

同样的，还有 right 函数，可以截取右侧几位字符串

汇总数据

聚合函数（aggregate function），它是运行在行组(即一组数据)上的函数，来计算和返回单个值。常见的聚合函数有：

avg()    # 平均值
count()  # 行数
max()    # 最大值
min()    # 最小值
sum()    # 总和

因此，上面的聚合函数，都可以在分组的情况下使用，因为他们都只返回单个值。

COUNT

count(*) 只统计行数（不管值是不是 NULL 都会算上）

count(column) 统计某一列有值的行数（忽略 null 值）

mysql> select * from B;
+----+------+------+
| id | name | aid  |
+----+------+------+
|  1 | wang |    1 |
|  2 | han  |    1 |
|  3 | li   |    2 |
|  4 | li.  | NULL |
|  5 | aa   | NULL |
|  6 | Wang |    1 |
+----+------+------+


mysql> select count(aid) from B;
+------------+
| count(aid) |
+------------+
|          4 |
+------------+

子查询

子查询过滤

我们可以将一个查询结果，作用于另一个查询的 where 子句。

mysql> select * from B;
+----+------+------+
| id | name | aid  |
+----+------+------+
|  1 | wang |    1 |
|  2 | han  |    1 |
|  3 | li   |    2 |
|  4 | li.  | NULL |
|  5 | aa   | NULL |
|  6 | Wang |    1 |
+----+------+------+



mysql> select name
    -> from B
    -> where aid in (select aid
    ->               from B
    ->               where name like 'li%');
+------+
| name |
+------+
| li   |
+------+

子查询是从内而外的，先执行最里层的查询，然后将结果返回给外部的 where 子句，来进行外层的查询。

子查询不仅可以使用 IN ，还可以使用 >, =, <> 等符号，前提是子查询返回的结果是单个值。

相关子查询

还有一种子查询，需要从外部获取信息，这种查询叫做相关子查询

mysql> select * from A;
+----+------+
| id | num  |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+



mysql> select * from B;
+----+------+------+
| id | name | aid  |
+----+------+------+
|  1 | wang |    1 |
|  2 | han  |    1 |
|  3 | li   |    2 |
|  4 | li.  | NULL |
|  5 | aa   | NULL |
|  6 | Wang |    1 |
+----+------+------+



mysql> select id, (select count(*) from B where B.aid=A.id) as num from A;
+------+------+
| id   | num  |
+------+------+
|    1 |    3 |
|    2 |    1 |
|    3 |    0 |
+------+------+

先看外层查询：select ... from A

再看子查询：selct ... from B where B.aid=A.id

子查询中用到了外层的数据：A.id，因此这两个查询其实是相关的：即外层每查询一次，内层随着 A.id 的变化也跟着查询一次。

连接

UNION

UNION 可以用来连接多个查询结果，将其作为一个整体输出。

mysql> select * from A;
+----+------+
| id | num  |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+


mysql> select * from B;
+----+------+------+
| id | name | aid  |
+----+------+------+
|  1 | wang |    1 |
|  2 | han  |    1 |
|  3 | li   |    2 |
|  4 | li.  | NULL |
|  5 | aa   | NULL |
|  6 | Wang |    1 |
+----+------+------+


mysql> select id from A
    -> union
    -> select id from B;'
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
+----+
6 rows in set (0.00 sec)

UNION 默认会将两个表中重复的记录进行去重。不想去重的话，使用 UNION ALL

使用 UNION 时，只能使用一个 GROUP BY 语句，不允许每个查询都拥有一个自己的 GROUP BY 语句。并且这个 GROUP BY 只能放在最后一个 SELECT 查询后面。

mysql> select id from A
    -> union
    -> select id from B
    -> group by id;  # 看似这个 group by 属于后一个查询，但其实它针对的是 UNION 联合后的全表进行排序。
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
+----+

存储过程

存储过程就是先将一些复杂的查询语句写成类似于函数的形式，这样我们每次直接调用这个存储过程，就能执行这些复杂的查询语句。

创建和使用存储过程

创建语法：

create procedure Func()    # Func 是存储过程的名字，可以接受参数。
begin                      # BEGIN 和 END 来限制存储过程体
select ... from ... where...; # 存储过程体
end;

使用：

CALL Func();  # 使用上面定义的 Func 这个存储过程

示例：

mysql> delimiter //            # 修改默认的定界符
mysql> create procedure test()
    -> begin
    -> select id from B;      # 遇到 ; 也不会认为语句结束
    -> end //                 # 这里语句才结束


mysql> delimiter ;            # 将定界符重新修改回来
mysql> call test();           # 调用存储过程
+----+
| id |
+----+
|  4 |
|  5 |
|  1 |
|  2 |
|  6 |
|  3 |
|  7 |
+----+

注意：mysql 默认的语句定界符是 ; 但是我们在存储过程体中也要写 ; ，因此 MySQL 的命令行程序遇到存储过程体中的 ; 就会认为语句结束了，造成语法错误。因此，我们实现先将默认的定界符改成 // ，最后再改回来，就行了。

删除

mysql> drop procedure test;  # 删除 test 这个procedure

带参数的存储过程

从存储过程获取返回值

mysql> delimiter //
mysql> create procedure func(out arg1 int)  # out 定义了要给出的结果参数，int是参数返回的类型；当然你可以定义多个参数：out arg1 type, out arg2 type, ...
    -> begin
    -> select max(id) into arg1  # into 关键字指定了 max(id) 的值赋值给 arg1
    -> from B;
    -> end//


mysql> delimiter ;
mysql> call func(@max_price);  # 传递一个参数用来接收值


mysql> select @max_price;
+------------+
| @max_price |
+------------+
|          7 |
+------------+

注意：存储过程的参数，不能返回多行或多列这种数据集，只能接受单个值

out 关键字用来定义一个要从存储过程返回的参数（按理说不应该叫参数，因为它被用作返回值），语法是out arg_name type

into 关键字用来赋值

给存储过程输入值

mysql> delimiter //
mysql> create procedure func2(in arg1 int, out arg2 int)
    -> begin
    -> select count(*) from B where id=arg1 into arg2;
    -> end//


mysql> delimiter ;
mysql> call func2(1, @output);  # 第一个数字 1 代表了传递给存储过程的 arg1 参数


mysql> select @output;
+---------+
| @output |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

高级语法

-- this is comment   # -- 是注释符号，后面的都是注释
DECLARE   # 声明一个变量和其类型，也可以设置默认值： declare arg1 int default 1

mysql> delimiter //
mysql> create procedure fun3(out x int)
    -> -- this is comment
    -> begin
    -> declare y int default 2;  # 声明一个变量
    -> select max(id*2) from A into x;
    -> end//


mysql> delimiter ;
mysql> call fun3(@x);


mysql> select @x;
+------+
| @x   |
+------+
|    8 |
+------+

触发器

触发器的作用，就是当一个表发生更改时，会自动触发我们定义的触发器的操作，从而可以自动进行一些额外的操作

语法：

create trigger