【后端之旅】三、MySQL 该怎么写（上篇）本文从 DDL、DML、DQL 三个方面介绍了 MySQL 的基本 SQL

如果你是 Linux/iOS 用户，且本地缺少 MySQL 软件本身，可以先点击安装 MySQL 查看安装教程。本文从 SQL、函数、约束、多表查询、事务共五个板块展示了 MySQL 的基础操作。请注意，大部分语句为伪代码，其中少部分内容包含了 MySQL 8.0 相关知识。

SQL

DDL —— 用于定义数据库对象，包括数据库、表、字段等

查询数据库

-- 查询所有数据库
SHOW DATABASES;

-- 查询当前所处的数据库
SELECT DATABASE();

创建数据库

-- 创建数据库，中括号表示里面的内容是可选的
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARACTER 字符集名] [COLLATE 排序规则];

删除数据库

-- 删除数据库
DROP DATABASE [IF EXISTS] 数据库名;

使用数据库

-- 进入某个数据库内
USE 数据库名;

查询表

-- 查询当前数据库所有表
SHOW TABLES;

-- 查询表结构
DESC 表名;

-- 查询指定表的建表语句
SHOW CREATE TABLE 表名;

创建表

-- 创建表，中括号表示里面的内容是可选的
CREATE TABLE [IF NOT EXISTS] 表名 (
    字段1名名称 字段1类型 [字段约束] [COMMENT 字段1注释],
    ...
    字段n名称 字段n类型 [字段约束] [COMMENT 字段n注释]
)[COMMENT 表注释];

修改表

-- 为表添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度) [字段约束] [COMMENT 字段注释];

-- 修改某个字段的类型
ALTER TABLE 表名 MODIFY 字段名 新类型(长度);

-- 修改某个字段的名称和类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [字段约束] [COMMENT 字段注释];

-- 修改表的名称
ALTER TABLE 旧表名 RENAME TO 新表名;

删除表

-- 删除表的某个字段
ALTER TABLE 表名 DROP 字段名;

-- 删除表
DROP TABLE [IF EXISTS] 表名;

-- 删除指定表，并重新创建该表，常用于清除表中数据
TRUNCATE TABLE 表名;

DML —— 用来对数据库表中的数据进行增删改

添加数据，字符串和日期类型的数据须包含在引号中

-- 给指定字段添加数据
INSERT INTO 表名 (字段1, 字段2, ...) VALUES (值1, 值2, ...);

-- 给所有字段添加数据
INSERT INTO 表名 VALUES (值1, 值2, ...);

-- 批量添加数据
INSERT INTO 表名 (字段1, 字段2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), ...;
INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), ...;

修改数据

-- 修改某个字段的某些数据
UPDATE 表名 SET 字段1 = 值1, 字段2 = 值2, ... [WHERE 条件];

删除数据

-- 删除某些记录
DELETE FROM 表名 [WHERE 条件];

DQL —— 用于查询数据库中表的记录

基础查询

-- 查询语法顺序
SELECT 字段列表
    FROM 表名列表
        WHERE 条件列表
            GROUP BY 分组字段列表
                HAVING 分组后条件列表
                    ORDER BY 排序字段列表
                        LIMIT 分页参数

-- 查询执行顺序
FROM 表名列表
    WHERE 条件列表
        GROUP BY 分组字段列表
            HAVING 分组后条件列表
                SELECT 字段列表
                    ORDER BY 排序字段列表
                        LIMIT 分页参数

-- 查询多个字段
SELECT 字段1, 字段2, ... FROM 表名;
SELECT * FROM 表名;

-- 设置别名，关键字 AS 也是可以省略的
SELECT 字段1 AS 别名1, 字段2 AS 别名2, ... FROM 表名;

-- 去除重复记录
SELECT DISTINCT 字段1, 字段2, ... FROM 表名;

条件查询，条件由比较运算符构成，可使用逻辑运算符构造复杂的判断条件
```
-- 条件查询语法
SELECT 字段列表 FROM 表名 WHERE 条件列表;
```

聚合查询

-- 聚合查询语法，注意：null 值是不参与所有聚合函数运算的
SELECT 聚合函数(字段名) FROM 表名;

分组查询，通常配合聚合函数一起使用

-- 分组查询语法，WHERE 是分组之前进行过滤，不参与分组；HAVING 是分组之后对结果进行过滤；WHERE 不能使用聚合函HAVING 可以 -->
-- 所以执行顺序为 WHERE > 聚合函数 > HAVING
SELECT 字段列表 FROM 表名 [WHERE 条件列表] GROUP BY 分组字段名 [HAVING 分组后过滤条件];

排序查询

-- 排序查询语法，ORDER BY 后可以指定多个字段，默认是升序排序，可以指定 ASC 或 DESC 来改变排序方式
-- 如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2, ...;

分页查询

-- 分页查询语法，LIMIT 后可以指定两个参数，第一个参数表示从第几条开始，默认是 0，表示从第一条开始取；第二个参数少条，，第二个参数可以省略，表示取所有数据 -->
-- 起始索引从 0 开始，起始索引 = (查询页码 - 1) * 每页显示条数
-- 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL 中是 LIMIT
-- 如果查询的是第一页，其实索引可以省略，比如直接简写为 LIMIT 10
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;

DCL —— 用于创建数据库用户、控制数据库的访问权限

管理用户

-- 查询用户
USE mysql;
SELECT * FROM user;

-- 创建用户，如果希望该用户能在任意主机访问数据库，则可以把主机名写成 '%'
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

-- 修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

-- 删除用户
DROP USER '用户名'@'主机名';

权限控制

-- 查询权限
SHOW GRANTS FOR '用户名'@'主机名';

-- 授予权限，用 '*' 来表示全部的数据库名或表名，比如 '*'.'*' 表示全部数据库的全部表
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

-- 撤销权限，多个权限之间用逗号分隔
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

数据类型
- 数值类型，下面 8 种又可分为 SIGNED(有符号) 和 UNSIGNED(无符号) 两种
  - TINYINT: 1 byte
  - SMALLINT: 2 bytes
  - MEDIUMINT: 3 bytes
  - INT: 4 bytes
  - BIGINT: 8 bytes
  - FLOAT: 4 bytes
  - DOUBLE: 8 bytes
  - DECIMAL: 小数值，依赖于 M(精度) 和 D(标度) 的值
- 字符串类型
  - CHAR: 0 - 255 bytes，定长
  - VARCHAR: 0 - 65535 bytes，变长
  - TINYBLOB: 0 - 255 bytes，不超过 255 个字符的二进制数据
  - TINYTEXT: 0 - 255 bytes，不超过 255 个字符的文本数据
  - BLOB: 0 - 65535 bytes，长二进制数据
  - TEXT: 0 - 65535 bytes，长文本数据
  - MEDIUMBLOB: 0 - 16777215 bytes，中等长度二进制数据
  - MEDIUMTEXT: 0 - 16777215 bytes，中等长度文本数据
  - LONGBLOB: 0 - 4294967295 bytes，极大二进制数据
  - LONGTEXT: 0 - 4294967295 bytes，极大文本数据
- 日期时间类型
  - DATE: 3 bytes，格式为 YYYY-MM-DD
  - TIME: 3 bytes，格式为 HH:MM:SS
  - YEAR: 1 byte，格式为 YYYY
  - DATETIME: 8 bytes，格式为 YYYY-MM-DD HH:MM:SS，范围为 1000-01-01 00:00:00 到 9999-12-31 23:59:59
  - TIMESTAMP: 4 bytes，格式为 YYYY-MM-DD HH:MM:SS，范围为 1970-01-01 00:00:00 到 2038-01-19 03:14:07
条件列表相关运算符
- 比较运算符
  - >: 大于
  - >=: 大于等于
  - <: 小于
  - <=: 小于等于
  - =: 等于
  - != 或 <>: 不等于
  - BETWEEN ... AND ...: 在某个范围之间(含最小、最大值)，其中 BETWEEN 后跟着的必须是最小值
  - IN (...): 在 in 之后的列表中的值，多选一
  - LIKE 占位符: 模糊匹配(_ 匹配单个字符；% 匹配任意个字符)
  - IS NULL: 是 NULL
- 逻辑运算符
  - AND 或 &&: 与
  - OR 或 ||: 或
  - NOT 或 !: 非
聚合函数 —— 将一列数据作为一个整体，进行纵向计算
- count: 统计数量
- max: 求最大值
- min: 求最小值
- avg: 求平均值
- sum: 求和
常用权限类型
- ALL, ALL PRIVILEGES: 所有权限
- SELECT: 查询
- INSERT: 插入
- UPDATE: 修改
- DELETE: 删除
- ALTER: 修改表
- DROP: 删除数据库/表/视图
- CREATE: 创建数据库/表/视图

函数

字符串函数（实际使用时把字符串替换成字段名即可。当然，这里的 SELECT 是为了把结果输出到控制台，实际使用时不需要）
- CONCAT(str1, str2, ...): 字符串拼接，将 str1, str2, ... 拼接成一个字符串并返回
```
SELECT CONCAT('Hello', ' ', 'World');
```
- LENGTH(str): 返回字符串 str 的长度
```
SELECT LENGTH('Hello World');
```
- LOWER(str): 将字符串 str 中的所有大写字符转换为小写字符
```
SELECT LOWER('Hello World');
```
- UPPER(str): 将字符串 str 中的所有小写字符转换为大写字符
```
SELECT UPPER('Hello World');
```
- TRIM(str): 去除字符串 str 头部和尾部的空格
```
SELECT TRIM('   Hello World   ');
```
- LPAD(str, length, pad_str): 将字符串 str 左侧填充 pad_str，直到达到长度 length
```
SELECT LPAD('Hello', 10, '*');
```
- RPAD(str, length, pad_str): 将字符串 str 右侧填充 pad_str，直到达到长度 length
```
SELECT RPAD('Hello', 10, '*');
```
- SUBSTRING(str, start, length): 返回字符串 str 中从 start 开始的 length 个字符
```
SELECT SUBSTRING('Hello World', 1, 6);
```
数值函数（实际使用时把数字替换成字段名即可。当然，这里的 SELECT 是为了把结果输出到控制台，实际使用时不需要）
- CEIL(x): 向上取整
```
SELECT CEIL(3.14);
```
- FLOOR(x): 向下取整
```
SELECT FLOOR(3.14);
```
- MOD(x, y): 返回 x 除以 y 的余数（即 x % y）
```
SELECT MOD(7, 3);
```
- RAND(): 返回一个 0 ~ 1 内的随机数
```
SELECT RAND();
```
- ROUND(x, d): 四舍五入，保留 d 位小数
```
SELECT ROUND(3.145, 2);
```
日期函数
- CURDATE(): 返回当前日期
```
SELECT CURDATE();
```
- CURTIME(): 返回当前时间
```
SELECT CURTIME();
```
- NOW(): 返回当前日期和时间
```
SELECT NOW();
```
- YEAD(date): 返回 date 的所在年份
```
SELECT YEAR(NOW());
```
- MONTH(date): 返回 date 的所在月份
```
SELECT MONTH(NOW());
```
- DAY(date): 返回 date 的所在日期
```
SELECT DAY(NOW());
```
- DATE_ADD(date, INTERVAL interval unit): 返回 date 加上时间间隔 interval 后的结果
```
SELECT DATE_ADD(NOW(), INTERVAL 5 DAY);
```
- DATEDIFF(date1, date2): 返回 date1 和 date2 之间的天数差
```
SELECT DATEDIFF('2023-05-01', '2023-04-01');
```
流程函数
- IF(value, true_value, false_value): 如果 value 为 true，则返回 true_value，否则返回 false_value
```
SELECT IF(1 = 1, 'good', 'bad');
```
- IFNULL(value_1, value_2): 如果 value_1 不为 null，则返回 value_1，否则返回 value_2
```
SELECT IFNULL('ok', 'default');
SELECT IFNULL('', 'default');
SELECT IFNULL(null, 'default');
```
- CASE WHEN value_1 THEN result_1 [WHEN value_2 THEN result_2] ... [ELSE result_default] END: 如果 value_1 为 true，则返回 result_1，如果 value_2 为 true，则返回 result_2，...，否则返回 result_default
```
-- value_1, value_2 可以为判断表达式，只要结果为布尔值即可
SELECT 
    CASE
        WHEN value_1 THEN result_1
        WHEN value_2 THEN result_2
        ELSE result_default
    END
FROM 表名;
```
- CASE [expression] WHEN value_1 THEN result_1 [WHEN value_2 THEN result_2] ... [ELSE result_default] END: 如果 expression 的计算结果等于 value_1，则返回 result_1，如果 expression 等于 value_2，则返回 result_2，...，否则返回 result_default
```
SELECT 
    CASE expression
        WHEN value_1 THEN result_1
        WHEN value_2 THEN result_2
        ELSE result_default
    END
FROM 表名;
```

约束

约束的分类 —— 约束是作用于表中字段上的，可在创建表/修改表时添加约束

非空约束: NOT NULL，限制该字段的数据不能为 null

CREATE TABLE 表名 (
    ...
    name VARCHAR(20) NOT NULL COMMENT '姓名',
    ...
);

唯一约束: UNIQUE，保证该字段的所有数据都是唯一、不重复的

CREATE TABLE 表名 (
    ...
    nickname VARCHAR(20) UNIQUE COMMENT '昵称',
    ...
);

主键约束: PRIMARY KEY，是一行数据的唯一标识，要求非空且唯一

CREATE TABLE 表名 (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
    ...
);

默认约束: DEFAULT，保存数据时，如果未指定该字段的值，则采用默认值

CREATE TABLE 表名 (
    ...
    status char(1) DEFAULT '1' COMMENT '状态',
    ...
);

检查约束: CHECK，v8.0.16 以后支持。保证字段值满足一个条件

CREATE TABLE 表名 (
    ...
    age TINYINT CHECK (age >= 0 AND age <= 120) COMMENT '年龄',
    ...
);

外键约束: FOREIGN KEY，用来让两张表的数据之间建立连接，保证数据的一致性和完整性

-- 创建表时添加外键约束，中括号的内容表示可选
CREATE TABLE 表名 (
    ...
    [CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表名 (主表字段名)
);

-- 修改表结构时添加外键约束，外键名称一般用 fk_ 开头
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表名 (主表字段名);

-- 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

外键约束的删除/更新行为，使用方式为 ON UPDATE 行为名称 和 ON DELETE 行为名称
- NO ACTION: 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新
- RESTRICT: 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新
- CASCADE: 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录
- SET NULL: 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则设置子表中该外键值为 null (这要求该外键允许取 null)
- SET DEFAULT: 父表有变更时，子表将外键列设置成一个默认的值(但 Innodb 引擎不支持)

多表查询

表表关系 —— 多表查询时，需要消除无效的笛卡尔积
- 一对多: 多的一方建立外键
- 多对多: 需建立中间表，以关联两方
- 一对一: 在任意一方加入具有唯一约束的外键。常用于单表拆分，以提升查询效率

内连接 —— 相当于查询 A、B 两张表的交集部分

-- 隐式内连接
SELECT 字段列表 FROM 表A, 表B WHERE 条件 ...;

-- 显式内连接，[INNER] 代表 INNER 是可省略的
SELECT 字段列表 FROM 表A [INNER] JOIN 表B ON 条件 ...;

外连接 —— 外连接必须加 ON 条件

-- 左外连接 —— 相当于查询 A 表所有数据，以及 A、B 两张表的交集部分
SELECT 字段列表 FROM 表A LEFT [OUTER] JOIN 表B ON 条件 ...;

-- 右外连接 —— 相当于查询 B 表所有数据，以及 A、B 两张表的交集部分
SELECT 字段列表 FROM 表A RIGHT [OUTER] JOIN 表B ON 条件 ...;

自连接 —— 当前表与自身的连接查询，自连接必须使用表别名

SELECT 字段列表 FROM 表A [AS] 别名A, 表B [AS] 别名B ON 条件 ...;

-- 等同于
SELECT 字段列表 FROM 表A [AS] 别名A, 表B [AS] 别名B WHERE 条件 ...;

联合查询 —— 多个表的列数必须保持一致，字段类型也需保持一致

-- 非去重状态下(即保留 ALL 关键词时)，得到的结果集可能出现重复的记录
-- 即 UNION ALL 会把全部数据直接合并在一起；UNION 则会对合并之后的数据去重
SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...;

子查询 —— 子查询外部的语句可以是 SELECT、INSERT、UPDATE、DELETE 中的任何一个

根据子查询的位置，可以分为
- WHERE 之后的子查询
- FROM 之后的子查询
- SELECT 之后的子查询

标量子查询 —— 子查询结果为单个值（如数字、字符串、日期等）

-- 常用的符号有 = <> < > <= >=
SELECT 字段列表 FROM 表A WHERE 字段1 = (SELECT 字段2 FROM 表B ...);

列子查询 —— 子查询结果为一列

-- 常用的操作符有:
-- IN —— 在指定的集合范围之内，多选一
-- NOT IN —— 不在指定的集合范围之内
-- ANY —— 子查询返回列表中，有任意一个满足即可，比如 `WHERE V1 > ANY (SELECT V2 FROM T2)`
-- SOME —— 与 ANY 等同，使用 SOME 的地方都可以使用 ANY
-- ALL —— 子查询返回列表的所有值都必须满足，比如 "WHERE VAL > ALL (30, 40, 50)"
SELECT 字段列表 FROM 表A WHERE 字段1 IN (SELECT 字段2 FROM 表B ...);

行子查询 —— 子查询结果为一行

-- 常用的操作符有 =、<>
SELECT 字段列表 FROM 表A WHERE (字段11, 字段12, ...) = (SELECT 字段21, 字段22, ... FROM 表B ...);

表子查询 —— 子查询结果为多行多列

-- 常用操作符有 IN、NOT IN
SELECT 字段列表 FROM 表A WHERE (字段11, 字段12, ...) IN (SELECT 字段21, 字段22, ... FROM 表B ...);

事务

是一组操作的集合，要求所有的操作作为一个整体向系统提交或撤销，这些操作要么同时成功，要么同时失败

事务操作

开启事务 —— 默认的 MySQL 事务是自动提交的，即，执行一条 DML 语句，MySQL 立即隐式地提交事务

-- 查看事务提交方式，1 表示自动提交，0 表示手动提交
SELECT @@autocommit;

-- 方式一，开启事务就是把 自动提交 关闭
SET @@autocommit = 0;

-- 方式二，推荐
BEGIN;

-- 方式三，推荐
START TRANSACTION;

提交事务
```
-- 提交后事务自动关闭
COMMIT;
```

回滚事务

-- 回滚后事务自动关闭
ROLLBACK;

事务特性 —— ACID
- 原子性: 事务是不可分割的最小操作单元，要么全部成功, 要么全部失败
- 一致性: 事务完成时，必须使所有的数据都保持一致状态
- 隔离性: 数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
- 持久性: 事务一旦提交或回滚，它对数据库中的数据的改变就是永久的
并发问题
- 脏读: 一个事务读到另外一个事务还没提交的数据
- 不可重复读: 一个事务先后读取同一条记录，但两次读取的数据不同
- 幻读: 一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在

隔离级别

Read Uncommitted: 不解决。性能最高
Read Committed: 可解决脏读。
Repeatable Read: 可解决脏读、不可重复读。MySQL 的默认级别
Serializable: 可解决脏读、不可重复读、幻读。性能最差

操作用例

-- 查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;

-- 设置事务隔离级别
-- SESSION 表示当前会话，即只在当前窗口有效
-- GLOBAL 表示全局，针对所有客户端窗口有效
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }