mirror of
https://github.com/CyC2018/Interview-Notebook.git
synced 2026-11-02 12:23:45 +00:00
802 lines
21 KiB
Markdown
802 lines
21 KiB
Markdown
# SQL 语法
|
||
<!-- GFM-TOC -->
|
||
* [SQL 语法](#sql-语法)
|
||
* [一、基础](#一基础)
|
||
* [二、创建表](#二创建表)
|
||
* [三、修改表](#三修改表)
|
||
* [四、插入](#四插入)
|
||
* [五、更新](#五更新)
|
||
* [六、删除](#六删除)
|
||
* [七、查询](#七查询)
|
||
* [DISTINCT](#distinct)
|
||
* [LIMIT](#limit)
|
||
* [八、排序](#八排序)
|
||
* [九、过滤](#九过滤)
|
||
* [十、通配符](#十通配符)
|
||
* [十一、计算字段](#十一计算字段)
|
||
* [十二、函数](#十二函数)
|
||
* [汇总](#汇总)
|
||
* [文本处理](#文本处理)
|
||
* [日期和时间处理](#日期和时间处理)
|
||
* [数值处理](#数值处理)
|
||
* [十三、分组](#十三分组)
|
||
* [十四、子查询](#十四子查询)
|
||
* [十五、连接](#十五连接)
|
||
* [内连接](#内连接)
|
||
* [自连接](#自连接)
|
||
* [自然连接](#自然连接)
|
||
* [外连接](#外连接)
|
||
* [十六、组合查询](#十六组合查询)
|
||
* [十七、视图](#十七视图)
|
||
* [十八、存储过程](#十八存储过程)
|
||
* [十九、游标](#十九游标)
|
||
* [二十、触发器](#二十触发器)
|
||
* [二十一、事务管理](#二十一事务管理)
|
||
* [二十二、字符集](#二十二字符集)
|
||
* [二十三、权限管理](#二十三权限管理)
|
||
* [参考资料](#参考资料)
|
||
<!-- GFM-TOC -->
|
||
|
||
|
||
## 一、基础
|
||
|
||
模式定义了数据如何存储、存储什么样的数据以及数据如何分解等信息，数据库和表都有模式。
|
||
|
||
主键的值一般不修改，也不复用（不把已经删除的主键值赋给新数据行的主键），这是普遍遵循的习惯，数据库本身并不禁止。
|
||
|
||
SQL（Structured Query Language），标准 SQL 由 ANSI 标准委员会管理，从而称为 ANSI SQL。各个 DBMS 都有自己的实现，如 PL/SQL、Transact-SQL 等。
|
||
|
||
SQL 语句不区分大小写，但是数据库表名、列名和值是否区分依赖于具体的 DBMS 以及配置。
|
||
|
||
MySQL 支持以下三种注释，其中 # 是 MySQL 特有的，-- 后面必须跟空白字符（例如空格）：
|
||
|
||
```sql
|
||
# 注释
|
||
SELECT *
|
||
FROM mytable; -- 注释
|
||
/* 注释1
|
||
注释2 */
|
||
```
|
||
|
||
数据库创建与使用：
|
||
|
||
```sql
|
||
CREATE DATABASE test;
|
||
USE test;
|
||
```
|
||
|
||
## 二、创建表
|
||
|
||
```sql
|
||
CREATE TABLE mytable (
|
||
# int 类型，不为空，自增
|
||
id INT NOT NULL AUTO_INCREMENT,
|
||
# int 类型，不可为空，默认值为 1
|
||
col1 INT NOT NULL DEFAULT 1,
|
||
# 变长字符串类型，最长为 45 个字符，可以为空
|
||
col2 VARCHAR(45) NULL,
|
||
# 日期类型，可为空
|
||
col3 DATE NULL,
|
||
# 设置主键为 id
|
||
PRIMARY KEY (`id`));
|
||
```
|
||
|
||
## 三、修改表
|
||
|
||
添加列
|
||
|
||
```sql
|
||
ALTER TABLE mytable
|
||
ADD col CHAR(20);
|
||
```
|
||
|
||
删除列
|
||
|
||
```sql
|
||
ALTER TABLE mytable
|
||
DROP COLUMN col;
|
||
```
|
||
|
||
删除表
|
||
|
||
```sql
|
||
DROP TABLE mytable;
|
||
```
|
||
|
||
## 四、插入
|
||
|
||
普通插入
|
||
|
||
```sql
|
||
INSERT INTO mytable(col1, col2)
|
||
VALUES(val1, val2);
|
||
```
|
||
|
||
插入检索出来的数据
|
||
|
||
```sql
|
||
INSERT INTO mytable1(col1, col2)
|
||
SELECT col1, col2
|
||
FROM mytable2;
|
||
```
|
||
|
||
将一个表的内容插入到一个新表
|
||
|
||
```sql
|
||
CREATE TABLE newtable AS
|
||
SELECT * FROM mytable;
|
||
```
|
||
|
||
## 五、更新
|
||
|
||
```sql
|
||
UPDATE mytable
|
||
SET col = val
|
||
WHERE id = 1;
|
||
```
|
||
|
||
## 六、删除
|
||
|
||
```sql
|
||
DELETE FROM mytable
|
||
WHERE id = 1;
|
||
```
|
||
|
||
**TRUNCATE TABLE** 可以清空表，也就是删除所有行。
|
||
|
||
```sql
|
||
TRUNCATE TABLE mytable;
|
||
```
|
||
|
||
与不带 WHERE 的 DELETE 不同，TRUNCATE 会隐式提交当前事务，不能回滚，并且会把 AUTO_INCREMENT 的值重置为初始值。
|
||
|
||
使用更新和删除操作时一定要用 WHERE 子句，不然会把整张表的数据都破坏。可以先用 SELECT 语句进行测试，防止错误删除。
|
||
|
||
## 七、查询
|
||
|
||
### DISTINCT
|
||
|
||
相同值只会出现一次。它作用于所有列，也就是说所有列的值都相同才算相同。
|
||
|
||
```sql
|
||
SELECT DISTINCT col1, col2
|
||
FROM mytable;
|
||
```
|
||
|
||
### LIMIT
|
||
|
||
限制返回的行数。可以有两个参数，第一个参数为起始行，从 0 开始；第二个参数为返回的总行数。
|
||
|
||
返回前 5 行：
|
||
|
||
```sql
|
||
SELECT *
|
||
FROM mytable
|
||
LIMIT 5;
|
||
```
|
||
|
||
```sql
|
||
SELECT *
|
||
FROM mytable
|
||
LIMIT 0, 5;
|
||
```
|
||
|
||
返回第 3 \~ 5 行：
|
||
|
||
```sql
|
||
SELECT *
|
||
FROM mytable
|
||
LIMIT 2, 3;
|
||
```
|
||
|
||
## 八、排序
|
||
|
||
- ASC：升序（默认）；
|
||
- DESC：降序。
|
||
|
||
可以按多个列进行排序，并且为每个列指定不同的排序方式：
|
||
|
||
```sql
|
||
SELECT *
|
||
FROM mytable
|
||
ORDER BY col1 DESC, col2 ASC;
|
||
```
|
||
|
||
## 九、过滤
|
||
|
||
不进行过滤的数据非常大，导致通过网络传输了多余的数据，从而浪费了网络带宽。因此尽量使用 SQL 语句来过滤不必要的数据，而不是传输所有的数据到客户端中然后由客户端进行过滤。
|
||
|
||
```sql
|
||
SELECT *
|
||
FROM mytable
|
||
WHERE col IS NULL;
|
||
```
|
||
|
||
下表显示了 WHERE 子句可用的操作符
|
||
|
||
| 操作符 | 说明 |
|
||
| :---: | :---: |
|
||
| = | 等于 |
|
||
| < | 小于 |
|
||
| > | 大于 |
|
||
| <> != | 不等于 |
|
||
| <= !> | 小于等于 |
|
||
| >= !< | 大于等于 |
|
||
| BETWEEN | 在两个值之间 |
|
||
| IS NULL | 为 NULL 值 |
|
||
|
||
其中 !> 和 !< 只有 SQL Server 等少数 DBMS 支持，MySQL 中会报语法错误。
|
||
|
||
应该注意到，NULL 与 0、空字符串都不同。
|
||
|
||
**AND 和 OR** 用于连接多个过滤条件。优先处理 AND，当一个过滤表达式涉及到多个 AND 和 OR 时，可以使用 () 来决定优先级，使得优先级关系更清晰。
|
||
|
||
**IN** 操作符用于匹配一组值，其后也可以接一个 SELECT 子句，从而匹配子查询得到的一组值。
|
||
|
||
**NOT** 操作符用于否定一个条件。
|
||
|
||
## 十、通配符
|
||
|
||
通配符也是用在过滤语句中，但它只能用于文本字段。
|
||
|
||
- **%** 匹配 \>=0 个任意字符；
|
||
|
||
- **\_** 匹配 ==1 个任意字符；
|
||
|
||
- **[ ]** 可以匹配集合内的字符，例如 [ab] 将匹配字符 a 或者 b。用脱字符 ^ 可以对其进行否定，也就是不匹配集合内的字符。需要注意的是，只有 SQL Server 等少数 DBMS 支持 [ ] 通配符，MySQL 不支持，需要改用 REGEXP。
|
||
|
||
使用 LIKE 来进行通配符匹配。
|
||
|
||
```sql
|
||
SELECT *
|
||
FROM mytable
|
||
WHERE col LIKE '[^AB]%'; -- 不以 A 和 B 开头的任意文本
|
||
```
|
||
|
||
MySQL 中的等价写法：
|
||
|
||
```sql
|
||
SELECT *
|
||
FROM mytable
|
||
WHERE col REGEXP '^[^AB]';
|
||
```
|
||
|
||
不要滥用通配符。通配符位于开头时，无法用这个条件在 B+ 树索引中做范围查找，大表上会非常慢。
|
||
|
||
## 十一、计算字段
|
||
|
||
在数据库服务器上完成数据的转换和格式化的工作往往比客户端上快得多，并且转换和格式化后的数据量更少的话可以减少网络通信量。
|
||
|
||
计算字段通常需要使用 **AS** 来取别名，否则输出的时候字段名为计算表达式。
|
||
|
||
```sql
|
||
SELECT col1 * col2 AS alias
|
||
FROM mytable;
|
||
```
|
||
|
||
**CONCAT()** 用于连接多个字段。许多数据库会使用空格把一个值填充为列宽，因此连接的结果会出现一些不必要的空格，使用 **TRIM()** 可以去除首尾空格。
|
||
|
||
```sql
|
||
SELECT CONCAT(TRIM(col1), '(', TRIM(col2), ')') AS concat_col
|
||
FROM mytable;
|
||
```
|
||
|
||
## 十二、函数
|
||
|
||
各个 DBMS 的函数都是不相同的，因此不可移植，以下主要是 MySQL 的函数。
|
||
|
||
### 汇总
|
||
|
||
|函 数 |说 明|
|
||
| :---: | :---: |
|
||
| AVG() | 返回某列的平均值 |
|
||
| COUNT() | 返回行数，COUNT(*) 统计所有行，COUNT(列) 只统计该列不为 NULL 的行 |
|
||
| MAX() | 返回某列的最大值 |
|
||
| MIN() | 返回某列的最小值 |
|
||
| SUM() |返回某列值之和 |
|
||
|
||
除了 COUNT(*)，这些函数都会忽略 NULL 行。
|
||
|
||
使用 DISTINCT 可以汇总不同的值。
|
||
|
||
```sql
|
||
SELECT AVG(DISTINCT col1) AS avg_col
|
||
FROM mytable;
|
||
```
|
||
|
||
### 文本处理
|
||
|
||
| 函数 | 说明 |
|
||
| :---: | :---: |
|
||
| LEFT() | 左边的字符 |
|
||
| RIGHT() | 右边的字符 |
|
||
| LOWER() | 转换为小写字符 |
|
||
| UPPER() | 转换为大写字符 |
|
||
| LTRIM() | 去除左边的空格 |
|
||
| RTRIM() | 去除右边的空格 |
|
||
| LENGTH() | 字节数 |
|
||
| CHAR_LENGTH() | 字符数 |
|
||
| SOUNDEX() | 转换为语音值 |
|
||
|
||
其中， **SOUNDEX()** 可以将一个字符串转换为描述其语音表示的字母数字模式。
|
||
|
||
```sql
|
||
SELECT *
|
||
FROM mytable
|
||
WHERE SOUNDEX(col1) = SOUNDEX('apple');
|
||
```
|
||
|
||
### 日期和时间处理
|
||
|
||
|
||
- 日期格式：YYYY-MM-DD；
|
||
- 时间格式：HH:MM:SS。
|
||
|
||
|函 数 | 说 明|
|
||
| :---: | :---: |
|
||
| ADDDATE() | 增加一个日期（天、周等）|
|
||
| ADDTIME() | 增加一个时间（时、分等）|
|
||
| CURDATE() | 返回当前日期 |
|
||
| CURTIME() | 返回当前时间 |
|
||
| DATE() |返回日期时间的日期部分|
|
||
| DATEDIFF() |计算两个日期之差|
|
||
| DATE_ADD() |高度灵活的日期运算函数|
|
||
| DATE_FORMAT() |返回一个格式化的日期或时间串|
|
||
| DAY()| 返回一个日期的天数部分|
|
||
| DAYOFWEEK() |对于一个日期，返回对应的星期几|
|
||
| HOUR() |返回一个时间的小时部分|
|
||
| MINUTE() |返回一个时间的分钟部分|
|
||
| MONTH() |返回一个日期的月份部分|
|
||
| NOW() |返回当前日期和时间|
|
||
| SECOND() |返回一个时间的秒部分|
|
||
| TIME() |返回一个日期时间的时间部分|
|
||
| YEAR() |返回一个日期的年份部分|
|
||
|
||
```sql
|
||
mysql> SELECT NOW();
|
||
```
|
||
|
||
```text
|
||
2018-04-14 20:25:11
|
||
```
|
||
|
||
### 数值处理
|
||
|
||
| 函数 | 说明 |
|
||
| :---: | :---: |
|
||
| SIN() | 正弦 |
|
||
| COS() | 余弦 |
|
||
| TAN() | 正切 |
|
||
| ABS() | 绝对值 |
|
||
| SQRT() | 平方根 |
|
||
| MOD() | 余数 |
|
||
| EXP() | 指数 |
|
||
| PI() | 圆周率 |
|
||
| RAND() | 随机数 |
|
||
|
||
## 十三、分组
|
||
|
||
把具有相同的数据值的行放在同一组中。
|
||
|
||
可以对同一分组数据使用汇总函数进行处理，例如求分组数据的平均值等。
|
||
|
||
MySQL 5.7 及之前的版本在一定条件下会自动按分组字段排序，MySQL 8.0 不再隐式排序，需要排序时应当使用 ORDER BY。
|
||
|
||
```sql
|
||
SELECT col, COUNT(*) AS num
|
||
FROM mytable
|
||
GROUP BY col;
|
||
```
|
||
|
||
ORDER BY 也可以按汇总字段来进行排序。
|
||
|
||
```sql
|
||
SELECT col, COUNT(*) AS num
|
||
FROM mytable
|
||
GROUP BY col
|
||
ORDER BY num;
|
||
```
|
||
|
||
WHERE 在分组之前过滤行，不能使用汇总函数；HAVING 在分组之后过滤分组，可以使用汇总函数。能写在 WHERE 中的条件不放到 HAVING，以减少参与分组的行。
|
||
|
||
```sql
|
||
SELECT col, COUNT(*) AS num
|
||
FROM mytable
|
||
WHERE col > 2
|
||
GROUP BY col
|
||
HAVING num >= 2;
|
||
```
|
||
|
||
分组规定：
|
||
|
||
- GROUP BY 子句出现在 WHERE 子句之后，ORDER BY 子句之前；
|
||
- 除了汇总字段外，SELECT 语句中的每一字段都必须在 GROUP BY 子句中给出；
|
||
- NULL 的行会单独分为一组；
|
||
- 有些 DBMS 不允许按大文本类型的列分组，MySQL 中 VARCHAR 和 TEXT 列都可以分组。
|
||
|
||
## 十四、子查询
|
||
|
||
作为 IN 等条件与一个字段比较的子查询，只能返回一个字段的数据；MySQL 也支持 (col1, col2) IN (SELECT x, y ...) 这样同时比较多个字段。
|
||
|
||
可以将子查询的结果作为 WHERE 语句的过滤条件：
|
||
|
||
```sql
|
||
SELECT *
|
||
FROM mytable1
|
||
WHERE col1 IN (SELECT col2
|
||
FROM mytable2);
|
||
```
|
||
|
||
下面的语句可以检索出客户的订单数量，子查询语句会对第一个查询检索出的每个客户执行一次：
|
||
|
||
```sql
|
||
SELECT cust_name, (SELECT COUNT(*)
|
||
FROM Orders
|
||
WHERE Orders.cust_id = Customers.cust_id)
|
||
AS orders_num
|
||
FROM Customers
|
||
ORDER BY cust_name;
|
||
```
|
||
|
||
## 十五、连接
|
||
|
||
连接用于连接多个表，使用 JOIN 关键字，并且条件语句使用 ON 而不是 WHERE。
|
||
|
||
很多子查询可以改写为连接。MySQL 8.0 的优化器会把 IN 子查询自动转换为半连接，两种写法常常得到相同的执行计划，快慢以 EXPLAIN 为准。
|
||
|
||
可以用 AS 给列名、计算字段和表名取别名，给表名取别名是为了简化 SQL 语句以及连接相同表。
|
||
|
||
### 内连接
|
||
|
||
内连接只返回两张表中满足连接条件的行，使用 INNER JOIN 关键字；连接条件是等值比较时也叫等值连接。
|
||
|
||
```sql
|
||
SELECT A.value, B.value
|
||
FROM tablea AS A INNER JOIN tableb AS B
|
||
ON A.key = B.key;
|
||
```
|
||
|
||
可以不明确使用 INNER JOIN，而使用普通查询并在 WHERE 中将两个表中要连接的列用等值方法连接起来。
|
||
|
||
```sql
|
||
SELECT A.value, B.value
|
||
FROM tablea AS A, tableb AS B
|
||
WHERE A.key = B.key;
|
||
```
|
||
|
||
### 自连接
|
||
|
||
自连接可以看成内连接的一种，只是连接的表是自身而已。
|
||
|
||
一张员工表，包含员工姓名和员工所属部门，要找出与 Jim 处在同一部门的所有员工姓名。
|
||
|
||
子查询版本
|
||
|
||
```sql
|
||
SELECT name
|
||
FROM employee
|
||
WHERE department = (
|
||
SELECT department
|
||
FROM employee
|
||
WHERE name = 'Jim');
|
||
```
|
||
|
||
自连接版本
|
||
|
||
```sql
|
||
SELECT e1.name
|
||
FROM employee AS e1 INNER JOIN employee AS e2
|
||
ON e1.department = e2.department
|
||
AND e2.name = 'Jim';
|
||
```
|
||
|
||
### 自然连接
|
||
|
||
自然连接是把同名列通过等值测试连接起来的，同名列可以有多个。
|
||
|
||
内连接和自然连接的区别：内连接用 ON 或 USING 指定连接条件，自然连接自动用所有同名列做等值连接，结果中同名列只出现一次。例如 Customers 和 Orders（内容见下文的外连接）只有 cust_id 一个同名列：
|
||
|
||
```sql
|
||
SELECT cust_id, cust_name, order_id
|
||
FROM Customers NATURAL JOIN Orders;
|
||
```
|
||
|
||
需要注意的是，两表都有 key 和 value 列时，NATURAL JOIN 要求两列都相等，而不只是 key 相等。
|
||
|
||
### 外连接
|
||
|
||
外连接保留了没有关联的那些行。分为左外连接，右外连接以及全外连接，左外连接就是保留左表没有关联的行。MySQL 不支持 FULL OUTER JOIN，可以用 UNION ALL 合并左外连接的结果和右外连接中左表没有匹配的行（WHERE 左表主键 IS NULL）；直接用 UNION 合并两个外连接会把相同的行去重，可能少行。
|
||
|
||
检索所有顾客的订单信息，包括还没有订单信息的顾客。
|
||
|
||
```sql
|
||
SELECT Customers.cust_id, Customers.cust_name, Orders.order_id
|
||
FROM Customers LEFT OUTER JOIN Orders
|
||
ON Customers.cust_id = Orders.cust_id;
|
||
```
|
||
|
||
customers 表：
|
||
|
||
| cust_id | cust_name |
|
||
| :---: | :---: |
|
||
| 1 | a |
|
||
| 2 | b |
|
||
| 3 | c |
|
||
|
||
orders 表：
|
||
|
||
| order_id | cust_id |
|
||
| :---: | :---: |
|
||
|1 | 1 |
|
||
|2 | 1 |
|
||
|3 | 3 |
|
||
|4 | 3 |
|
||
|
||
结果：
|
||
|
||
| cust_id | cust_name | order_id |
|
||
| :---: | :---: | :---: |
|
||
| 1 | a | 1 |
|
||
| 1 | a | 2 |
|
||
| 3 | c | 3 |
|
||
| 3 | c | 4 |
|
||
| 2 | b | NULL |
|
||
|
||
如果改成内连接，结果只有前 4 行，没有订单的顾客 b 不会出现。
|
||
|
||
## 十六、组合查询
|
||
|
||
使用 **UNION** 来组合两个查询。如果第一个查询返回 M 行，第二个查询返回 N 行，UNION 默认去除相同的行，结果最多 M+N 行；需要保留相同的行时使用 UNION ALL，结果正好是 M+N 行。
|
||
|
||
每个查询的列数必须相同，对应列的类型要兼容，结果的列名取第一个查询的列名。
|
||
|
||
只能包含一个 ORDER BY 子句，并且必须位于语句的最后。
|
||
|
||
```sql
|
||
SELECT col
|
||
FROM mytable
|
||
WHERE col = 1
|
||
UNION
|
||
SELECT col
|
||
FROM mytable
|
||
WHERE col = 2;
|
||
```
|
||
|
||
## 十七、视图
|
||
|
||
视图是虚拟的表，本身不包含数据，也就不能对其进行索引操作。
|
||
|
||
查询视图和查询普通表一样；只有不含汇总函数、DISTINCT、GROUP BY、UNION 等的简单视图才能用 INSERT、UPDATE、DELETE 修改，修改会作用到底层的表。
|
||
|
||
视图具有如下好处：
|
||
|
||
- 简化复杂的 SQL 操作，比如复杂的连接；
|
||
- 只使用实际表的一部分数据；
|
||
- 通过只给用户访问视图的权限，保证数据的安全性；
|
||
- 更改数据格式和表示。
|
||
|
||
```sql
|
||
CREATE VIEW myview AS
|
||
SELECT Concat(col1, col2) AS concat_col, col3*col4 AS compute_col
|
||
FROM mytable
|
||
WHERE col5 = val;
|
||
```
|
||
|
||
## 十八、存储过程
|
||
|
||
存储过程可以看成是对一系列 SQL 操作的批处理。
|
||
|
||
使用存储过程的好处：
|
||
|
||
- 代码封装，保证了一定的安全性；
|
||
- 代码复用；
|
||
- 减少客户端与服务器之间的往返。需要注意的是，MySQL 并不预先编译存储过程，只在每个会话第一次调用时解析并缓存。
|
||
|
||
命令行中创建存储过程需要自定义分隔符，因为命令行是以 ; 为结束符，而存储过程中也包含了分号，因此会错误把这部分分号当成是结束符，造成语法错误。
|
||
|
||
包含 in、out 和 inout 三种参数。
|
||
|
||
可以用 SET 或者 SELECT ... INTO 给变量赋值。SELECT ... INTO 可以一次给多个变量赋值，但查询结果只能有一行，否则报错 1172（Result consisted of more than one row），多行结果要用下面的游标逐行处理。
|
||
|
||
```sql
|
||
delimiter //
|
||
|
||
create procedure myprocedure( out ret int )
|
||
begin
|
||
declare y int;
|
||
select sum(col1)
|
||
from mytable
|
||
into y;
|
||
select y*y into ret;
|
||
end //
|
||
|
||
delimiter ;
|
||
```
|
||
|
||
```sql
|
||
call myprocedure(@ret);
|
||
select @ret;
|
||
```
|
||
|
||
## 十九、游标
|
||
|
||
在存储过程中使用游标可以对一个结果集进行移动遍历。
|
||
|
||
MySQL 的游标只能在存储过程等存储程序中使用，并且是只读的，只能从前往后逐行读取。
|
||
|
||
使用游标的四个步骤：
|
||
|
||
1. 声明游标，这个过程没有实际检索出数据；
|
||
2. 打开游标；
|
||
3. 取出数据；
|
||
4. 关闭游标。
|
||
|
||
```sql
|
||
delimiter //
|
||
create procedure myprocedure(out ret int)
|
||
begin
|
||
declare done boolean default 0;
|
||
|
||
declare mycursor cursor for
|
||
select col1 from mytable;
|
||
# 定义了一个 continue handler，当 sqlstate '02000' 这个条件出现时，会执行 set done = 1
|
||
declare continue handler for sqlstate '02000' set done = 1;
|
||
|
||
open mycursor;
|
||
|
||
repeat
|
||
fetch mycursor into ret;
|
||
# 最后一次 fetch 没有取到数据时 done 已经为 1，不能再输出 ret
|
||
if not done then
|
||
select ret;
|
||
end if;
|
||
until done end repeat;
|
||
|
||
close mycursor;
|
||
end //
|
||
delimiter ;
|
||
```
|
||
|
||
## 二十、触发器
|
||
|
||
触发器会在某个表执行以下语句时而自动执行：DELETE、INSERT、UPDATE。
|
||
|
||
触发器必须指定在语句执行之前还是之后自动执行，之前执行使用 BEFORE 关键字，之后执行使用 AFTER 关键字。BEFORE 用于数据验证和净化，AFTER 用于审计跟踪，将修改记录到另外一张表中。
|
||
|
||
INSERT 触发器包含一个名为 NEW 的虚拟表。
|
||
|
||
```sql
|
||
CREATE TRIGGER mytrigger AFTER INSERT ON mytable
|
||
FOR EACH ROW SELECT NEW.col into @result;
|
||
|
||
SELECT @result; -- 获取结果
|
||
```
|
||
|
||
DELETE 触发器包含一个名为 OLD 的虚拟表，并且是只读的。
|
||
|
||
UPDATE 触发器包含一个名为 NEW 和一个名为 OLD 的虚拟表，其中 NEW 只能在 BEFORE 触发器中修改（SET NEW.col = ...），而 OLD 是只读的。
|
||
|
||
MySQL 的触发器可以使用 CALL 语句调用存储过程，但是该存储过程不能向客户端返回数据，也不能使用动态 SQL。
|
||
|
||
## 二十一、事务管理
|
||
|
||
基本术语：
|
||
|
||
- 事务（transaction）指一组 SQL 语句；
|
||
- 回退（rollback）指撤销指定 SQL 语句的过程；
|
||
- 提交（commit）指结束当前事务，使事务中的修改永久生效；
|
||
- 保留点（savepoint）指事务中设置的临时标记，可以只回退到这个标记，而不回退整个事务。
|
||
|
||
不能回退 SELECT 语句，回退 SELECT 语句也没意义；也不能回退 CREATE 和 DROP 语句，这类语句执行前会隐式提交当前事务，之前的修改也无法再回滚。
|
||
|
||
MySQL 默认开启自动提交（autocommit），每执行一条语句就把这条语句当成一个事务然后进行提交。当出现 START TRANSACTION 语句时，会关闭自动提交；当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭，重新恢复自动提交。
|
||
|
||
设置 autocommit 为 0 可以取消自动提交；autocommit 标记是针对每个连接而不是针对服务器的。
|
||
|
||
如果没有设置保留点，ROLLBACK 会回退到 START TRANSACTION 语句处；如果设置了保留点，并且在 ROLLBACK 中指定该保留点，则会回退到该保留点。
|
||
|
||
```sql
|
||
START TRANSACTION;
|
||
-- ...
|
||
SAVEPOINT delete1;
|
||
-- ...
|
||
ROLLBACK TO delete1;
|
||
-- ...
|
||
COMMIT;
|
||
```
|
||
|
||
## 二十二、字符集
|
||
|
||
基本术语：
|
||
|
||
- 字符集为字母和符号的集合；
|
||
- 编码为某个字符集成员的内部表示；
|
||
- 校对（collation）指定字符如何比较，主要用于排序和分组。
|
||
|
||
MySQL 8.0 默认的字符集是 utf8mb4，校对是 utf8mb4_0900_ai_ci（ci 表示不区分大小写，'a' = 'A' 为真）。MySQL 的 utf8 实际是 utf8mb3，每个字符最多 3 字节，存不下 emoji 等 4 字节字符。
|
||
|
||
除了给表指定字符集和校对外，也可以给列指定：
|
||
|
||
```sql
|
||
CREATE TABLE mytable
|
||
(col VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci )
|
||
DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;
|
||
```
|
||
|
||
可以在排序、分组时指定校对：
|
||
|
||
```sql
|
||
SELECT *
|
||
FROM mytable
|
||
ORDER BY col COLLATE latin1_general_ci;
|
||
```
|
||
|
||
## 二十三、权限管理
|
||
|
||
MySQL 的账户信息保存在 mysql 这个数据库中。
|
||
|
||
```sql
|
||
USE mysql;
|
||
SELECT user FROM user;
|
||
```
|
||
|
||
创建账户：
|
||
|
||
新创建的账户没有任何权限。
|
||
|
||
```sql
|
||
CREATE USER myuser IDENTIFIED BY 'mypassword';
|
||
```
|
||
|
||
修改账户名：
|
||
|
||
```sql
|
||
RENAME USER myuser TO newuser;
|
||
```
|
||
|
||
删除账户：
|
||
|
||
```sql
|
||
DROP USER myuser;
|
||
```
|
||
|
||
查看权限：
|
||
|
||
```sql
|
||
SHOW GRANTS FOR myuser;
|
||
```
|
||
|
||
授予权限：
|
||
|
||
账户用 username@host 的形式定义，省略主机名时为 username@'%'，表示可以从任意主机连接。
|
||
|
||
```sql
|
||
GRANT SELECT, INSERT ON mydatabase.* TO myuser;
|
||
```
|
||
|
||
删除权限：
|
||
|
||
GRANT 和 REVOKE 可在几个层次上控制访问权限：
|
||
|
||
- 整个服务器，使用 ON \*.\*；
|
||
- 整个数据库，使用 ON database.\*；
|
||
- 特定的表，使用 ON database.table；
|
||
- 特定的列；
|
||
- 特定的存储过程。
|
||
|
||
```sql
|
||
REVOKE SELECT, INSERT ON mydatabase.* FROM myuser;
|
||
```
|
||
|
||
更改密码：
|
||
|
||
MySQL 8.0 移除了 Password() 函数，直接指定新密码即可。
|
||
|
||
```sql
|
||
SET PASSWORD FOR myuser = 'new_password';
|
||
```
|
||
|
||
## 参考资料
|
||
|
||
- Ben Forta. SQL 必知必会 [M]. 人民邮电出版社, 2013.
|