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 <trigger_name> <time> <operation> on <table> for each row  # for each row 意思是针对这张表的某一行，都会激活触发器
<other_operate>;

trigger_name: 就是普通的触发器名字，自定义

time： 指定触发器何时触发：before, after

operation: 何种操作：insert, update, delete

table: 触发器设置在哪个表上

other_operate: 就是其他查询操作

下面是一个例子：

mysql> create trigger new_add1          # 创建一个名为 new_add1 的触发器
    -> after insert on A for each row   # 这个触发器会在插入 A 表之后执行
    -> update B set name=0 where id=1;  # 这是触发器自动执行的操作：更新 B 表id=1 的 name 字段

执行插入 A 表的操作：

mysql> insert into A(num) values(2);
Query OK, 1 row affected (0.17 sec)


mysql> select * from B where id=1;
+----+------+------+
| id | name | aid  |
+----+------+------+
|  1 | 0    |    1 |  # name 变成了 0
+----+------+------+

事务

所谓的事务，就是一组 SQL 语句。它要么执行成功，要么完全不执行。试想一下你银行转账给别人，钱已经从你的账户扣除了，但是银行出现故障导致对方没收到钱，这种情况是任何人都无法忍受的。因此，像这种操作，要么就完全执行成功：你转出去，对方收到。要么就没转成功，你钱没少，对方也没收到。

事务(transaction): 一组SQL语句

回退(rollback)：撤销指定的SQL语句

提交(commit)：将未存储的SQL结果存入数据库

保留点(savepoint)：临时存档，可以对它进行回退

开始事务和回退

start transaction 标志着事务的开始。

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


mysql> start transaction;           # 开始事务


mysql> delete from B;               # 将 B 表整个清空


mysql> select * from B;             # 清空了，没查询到数据
Empty set (0.00 sec)

mysql> rollback;                    # 回退事务
Query OK, 0 rows affected (0.08 sec)

mysql> select * from B;             # 表 B 的内容又回来了
+----+------+------+
| id | name | aid  |
+----+------+------+
|  1 | 0    |    1 |
|  2 | han  |    1 |
|  3 | li   |    2 |
|  4 | li.  | NULL |
|  5 | aa   | NULL |
|  6 | Wang |    1 |
|  7 | aab  |    2 |
+----+------+------+

事务可以用来处理 update, delete, insert

rollback 之后，事务会自动关闭。

提交

commit 可以用来提交一个事务，即认为事务完成了。

mysql> start transaction;
mysql> delete from B where id=7;
mysql> delete from B where id=8;
mysql> commit;

假设第一个删除语句成功，第二个删除语句失败，那么commit 也不会提交（一个事务必须全部成功，才能提交）

commit 后事务会自动关闭

保留点

我们可以在事务中创建保留点，然后我们在这个事务后续处理过程种，可以随时回退到这个存档点。

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


mysql> start transaction;


mysql> delete from B where id=6;
mysql> savepoint s1;
mysql> delete from B where id=5;
mysql> rollback to s1;

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

mysql> commit;

全球化和本地化

字符集：字母和符号的集合

编码：就是编码

校对：如何对字符集进行比较，如排序（大小写怎么排序）

show character set;  # 查看所有的字符集
show collation;      # 查看所有的校对 （_cs 后缀代表区分大小写， ci 代表忽略大小写）

创建表

针对某个表设置字符集和校对：

create table <table_name> (columns ...)  default character set <字符集> collate <校对>

针对某个字段设置字符集和校对：

create table <table_name> (
    id int,
    name varchar(10) character set latin1 collate latin1_general_ci  # 对某个字段也可以设置字符集和校对
);

查询某个字段时，可以临时给它指定一个校对方式：

select * from <table_name> order by name collate latin1_general_cs;  # 区分大小写

安全管理

用户管理

mysql 的用户信息表，位于名为 mysql 的数据库中：

mysql> use mysql;
Database changed

mysql> select user from user;
+------------------+
| user             |
+------------------+
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| root             |
+------------------+