MySQL学习笔记基础

共计 9382 个字符，预计需要花费 24 分钟才能阅读完成。

SQL 各种名词

sql_mode

即 SQL 模式，作用是规范 SQL 语句书写方式，查看命令：select @@sql_mode;。

MySQL 通过设定 sql_mode 参数，规范比如除法运算，从而保证不会出现违背现实数学逻辑的 SQL 语句。

核心 SQL 语句

要保证表中的字段、表名等没有和保留字、数据库系统或常用方法冲突。如果真的相同，请在 SQL 语句中使用一对 “（着重号）引起来。

通用语法：

• SQL 语句可以单行或多行书写，以分号结尾
• SQL 语句可以使用空格 / 缩进来增强语句的可读性
• SQL 语句不区分大小写，关键字建议使用大写
• 单行注释：-- 注释内容 或# 注释内容(MySQL 特有)。多行注释：/* 注释内容 */

分类：

• DDL，Data Definition Language，数据定义语言，用来定义数据库对象（数据库，表，字段）
• DCL，Data Control Language，数据控制语言，用来创建数据库用户、控制数据库访问权限
• DML，Data Manipulation Language，数据操作语言，用来对数据库表中的数据进行增删改
• DQL，Data Query Language，数据查询语言，用来查询数据库中表记录

DDL

数据库操作

查询所有数据库：show databases;

查询当前所在数据库：select database();

创建数据库：create database if not exists test default charset utf8mb4 COLLATE utf8mb4_general_ci;

修改数据库：alter database test charset utf8mb4;

删除数据库：drop database if exists test;

使用数据库：use test;

查看数据库基本信息：show create database test;

查看 DDL 语句的具体指令：? data definition

表操作

查询当前数据库所有表：show tables;

查看表结构：desc user;

查看建表语句：show create table user;

创建表：

CREATE TABLE if NOT EXISTS USER(
 id INT NOT NULL AUTO_INCREMENT COMMENT '编号',
 name VARCHAR(64) COMMENT '姓名',
 gender CHAR(1) DEFAULT 'F' COMMENT '性别',
 PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=UTF8MB4 COMMENT '用户表';

建表规范：

• 表名：小写字母、不能数字开头、表名和业务有关、表名不要太长、不能使用关键字
• 必须设置存储引擎和字符集
• 数据类型要合适、简短、足够
• 必须要有主键（不使用任何业务相关的字段作为主键，使用自增整数类型或全局唯一 GUID 类型）
• 每列尽量设置 not null，不知道填啥，设定默认值
• 每列要有注释
• 列名不要太长
• 要有表注释

修改表名：alter table user rename to tb_user;

修改存储引擎：alter table user engine=innodb;，此命令也可以用来进行 InnoDB 表的碎片整理。

删除指定表，并重新创建该表：truncate table tb_user;

删除表：drop table if exists tb_user;

truncate 语句不能回滚，而使用 delete 语句删除数据，可以回滚。

delete、drop、truncate 三者区别：

• delete：逐行删除，操作很慢，可以回滚。delete 操作并没有真正从磁盘上删除，只是在存储层面打标记，磁盘空间不立即释放。高水位线（High-Water Mark, HWM）不会降低
• drop：将表结构（元数据）和数据行物理层次删除
• truncate：清空表中所有数据页，物理磁盘空间立即释放，HWM 高水位线会降低

添加字段：alter table user add nickname varchar(20) comment ‘ 昵称 ’;

修改字段数据类型：alter table 表名 modify column 字段名 数据类型;

修改字段名称：alter table user change nickname username varchar(20) comment ‘ 用户名 ’;

change 是 modify 的增强版，modify 可以修改字段的长度和默认值，change 在 modify 的基础上还可以修改字段的名称，即重命名字段。

删除字段：alter table user drop username;

🤔2 亿行表，想要删除其中 1000W，怎么做？

• 如果 2 亿行表，还没有生成，建议在设计表时，采用分区表的方式（按月 range），然后删除时 truncate
• 如果 2 亿行表已经存在，建议使用 pt-archive 工具进行归档表，并且删除无用数据

DML

添加数据：insert into user values (1, ‘ 张三 ’, ‘ 男 ’);

修改数据：update user set name=’ 李四 ’ where id=1;

删除数据：delete from user where id=1;

DQL

• SELECT
  • 字段列表
• FROM
  • 表名列表
• WHERE
  • 条件列表
• GROUP BY
  • 分组字段列表
• HAVING
  • 分组后条件列表
• ORDER BY
  • 排序字段列表
• LIMIT
  • 分页参数

分页查询：limit 起始索引, 查询记录数。

起始索引 = (查询页码 -1) * 每页显示记录数。如查询第 2 页，每页显示 10 条记录：limit 10, 10;

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

distinct 只能出现在所有字段最前面，当 distinct 出现后，后面多个字段一定是联合去重的。

注意：null 值不参与所有聚合函数运算。

查看 MySQL 版本信息：select version();

组内排序：
案例：找出每个工作岗位工资排名前两名：select substring_index(group_concat(empno order by sal desc), ',', 2) from emp group by job;

模糊查询 like：通配符主要包括两个：一个是 % 代表任意多个字符；一个是下划线_代表任意一个字符。如果想让下划线变成一个普通的下划线字符，就要使用转义字符“\”：select * from student where name like '%\_%';。

DCL

查询用户：select * from user \G，使用 \G 把行转化成列显示，结尾就不需要 ; 了。或者：select user,host,authentication_string from mysql.user;。

获取当前登录用户：select current_user();

mysql.user 表中用户条目的优先级，基本规则如下：

• IP 条目的优先级最高。IP 条目中没有通配符，精确的 IP 和 IP 地址段都是 IP 条目
• 精确 IP 的优先级比 IP 地址段的优先级高
• 对于 2 个 IP 地址段，前缀长的优先级更高。比如 172.16.121.0/24 优先级比 172.16.0.0/16 高
• 不使用通配符的条目比使用通配符的条目优先级高
• 对于都使用了通配符的条目，则根据第一个通配符在 host 字段中出现的位置来判断优先级。通配符出现的位置越靠前，优先级越低。比如 ‘%’ 的优先级最低，’abc%’ 的优先级比 ‘abcd%’ 低

在 MySQL 5.6 或更早版本中，使用 mysql_install_db 来初始化数据库，会创建一个用户名为空的无密码用户，这会引起一个问题：在数据库服务器本地使用一个正常账号登录数据库，会发现无法登录，报密码错误。实际上，这并不是密码问题，而是在本地登录时，使用了 ”@’localhost’ 这个条目来进行用户认证。这个问题的解决方法一般是 删除用户名为空的用户。

创建用户：create user '名字'@'允许登录主机名或 IP' identified by '密码';。例如：create user 'test'@'%' identified by '123456';，百分号 “%” 匹配任意字符串，表示所有主机网段。默认创建的用户只有一个 Usage 权限，作用是只能够登录 MySQL。

也可以使用 IP 地址段来指定客户端 IP 范围，比如下面创建的 test 用户可以在 172.16 这个网段内访问数据库。

create user 'test'@'172.16.0.0/16' identified by 'somepassword';

修改用户密码：alter user 'test'@'localhost' identified with mysql_native_password by '123';，identified with 指定使用 MySQL 本地密码的处理方式。

删除用户：drop user ‘test’@’localhost’;

注意：8.0 版本以前，可以通过 grant 命令建立用户 + 授权。

显示 MySQL 支持的系统权限列表：show privileges;

查询用户权限：show grants for ‘test’@’localhost’;

授予权限：

• grant 权限列表 on 数据库名. 表名 to ‘ 用户名 ’@’ 主机名 ’;
• grant 权限列表 on 数据库名. 表名 to ‘ 用户名 ’@’ 主机名 ’ with grant option; — grant option 是可以给别的用户授权
• grant all privileges on 数据库名.* to 用户名 ’@’ 主机名;

撤销权限：revoke 权限列表 on 数据库名. 表名 from ‘ 用户名 ’@’ 主机名 ’;

授权后必须刷新权限才能生效：flush privileges;

授权时，要遵循 最小权限原则，给用户授予正常业务需求之外的权限会带来额外的安全风险。

SQL 解析器原理流程

MySQL 体系结构：连接层、服务层、存储引擎层、系统文件层。

连接层（协议层）：有个连接池，作用是让后续的 MySQL 连接，可以反复用一个连接信息，以减少数据库连接创建销毁的资源开销。

MySQL 客户端与服务端的通信方式是“半双工”:

• 全双工：能同时发送和接收数据，例如打电话
• 半双工：指的某一时刻，要么发送数据，要么接收数据，不能同时
• 单工：只能发送数据或只能接收数据

服务层包括：SQL 接口、解析器、查询优化器、缓存。

• 检测 SQL 正确性，看是否符合 DDL、DML 等规则
• 针对不同 SQL 分类，分发给不同底层模块去执行
• 比如接收到的是 select 语句，会先去 cache 中寻找缓存
• SQL 解析流程，对 SQL 语句进行解析
• 准备 SQL 执行计划
• 执行 SQL 计划，查询数据
• 数据读取到之后，会添加到缓存中，便于下次加速查找

运算符

安全等于运算符

安全等于运算符（<=>）与等于运算符（=）的作用是相似的，唯一的区别是‘<=>’可以用来对 NULL 进行判断。在两个操作数均为 NULL 时，其返回值为 1；当一个操作数为 NULL 时，其返回值为 0。

+-----------+-------------+---------------+
| 1 <=> '1' | '' <=> null | null <=> null |
+-----------+-------------+---------------+
|         1 |           0 |             1 |
+-----------+-------------+---------------+

非符号类型运算符

select least(1,2,66,3), greatest(1,2,66,3);
select 'start' regexp '^s', 'start' regexp 't$';

约束

• 非空约束：not null
• 检查约束：check
• 唯一性约束：unique
• 主键约束：primary key
• 外键约束：foreign key

所有约束都存储在一个系统表当中：information_schema.table_constraints。

检查约束

create table t_stu(
  age int,
  check(age > 18)
);

唯一性约束

唯一性的字段值是可以为 NULL 的，但不能重复。使用表级约束可以为多个字段添加联合唯一：

create table t_stu(
  no int,
  name varchar(255),
  email varchar(255),
  unique(name, email)
);

也可以给约束起名字，将来可通过约束名来删除约束：constraint t_stu_name_email_unique unique(name, email)

外键约束

a 表如果引用 b 表中的数据，可以把 b 表叫做父表，把 a 表叫做子表：

• 创建表时，先创建父表，再创建子表
• 插入数据时，先插入父表，在插入子表
• 删除数据时，先删除子表，再删除父表
• 删除表时，先删除子表，再删除父表

添加外键：alter table user add constraint fk_user_sex foreign key(gender) references sex(gender);

删除外键：alter table user drop foreign key fk_user_sex;

删除 / 更新行为：

• 级联删除 / 更新：alter table user add constraint fk_user_sex foreign key (gender) references sex (gender) on update cascade on delete cascade;
• 级联置空：alter table user add constraint fk_user_sex foreign key (gender) references sex (gender) on update set null on delete set null;

多表查询

连接查询

内连接

隐式内连接：select name from user, sex where user.gender=sex.gender;

显示内连接（inner 可省略）：select name from user inner join sex on user.gender=sex.gender;

非等值连接：select e.ename, e.sal, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;

自连接：select e.ename 员工名, l.ename 领导名 from emp e join emp l on e.mgr = l.empno;

外连接

左外连接（左连接，outer 可省略）：select name from user left outer join sex on user.gender=sex.gender;

右外连接（右连接）：select name from user right join sex on user.gender=sex.gender;

MySQL 不支持满外连接(full outer join 全连接)，但是可以用 left join union right join 代替。

联合查询

mysql> select gender from user
    -> union all
    -> select gender from sex;

mysql> select gender from user
    -> union
    -> select gender from sex;

union 会对查询结果合并之后进行去重，union all 不会去重。

子查询

标量子查询：返回结果是单个值。

列子查询：返回结果是一列。常用操作符有：in、not in、any、some、all。

行子查询：返回结果是一行。常用操作符有：in、not in、=、<>。

select * from sex where (id,gender)=(select id,gender from user where gender=’ 男 ’);

表子查询：返回结果是多行多列。常用操作符有：in。

• in 的执行原理实际上是采用 = 和 or 的方式，也就是说，SQL 语句 comm in(NULL, 300); 实际上是comm = NULL or comm = 300;，in 是自动忽略 NULL 的
• not in 的执行原理实际上是采用!= 和 and 的方式，也就是说，SQL 语句 comm not in(NULL, 300); 实际上是comm != NULL and comm != 300;，not in 不会自动忽略 NULL，在使用 not in 时一定要提前过滤掉 NULL

in 和 or 的效率比拼：

• in 和 or 所在列有索引或者主键的话，没啥差别，执行计划和执行时间都几乎一样
• 如果 in 和 or 所在列没有索引，性能差别就很大了。随着 in 或者 or 后面的数据量越多，in 的效率不会有太大的下降，但是 or 会随着记录越多的话性能下降非常厉害
• 因此在给 in 和 or 的效率下定义的时候，应该再加上一个条件，就是所在的列是否有索引或者是否是主键。如果有索引或者主键性能没啥差别，如果没有索引，性能差别不是一点点！

in 和 exists 区别：

• in 操作符是根据指定列表中的值来判断是否满足条件，而 exists 操作符则是根据子查询结果是否有返回记录集来判断
• exists 操作符通常比 in 操作符更快，尤其是在子查询返回记录数很大的情况下。因为 exists 只需要判断是否存在符合条件的记录，而 in 操作符需要比对整个列表，因此执行效率相对较低
• in 操作符可同时匹配多个值，而 exists 只能匹配一组条件

事务

事务四大特性 ACID：

• 原子性（Atomicity）：不可分割的特性，要么全成功，要么全回滚
• 一致性（Consistency）：事务发生前、中、后都应该保证数据始终是一致状态
• 隔离性（Isolation）：多个并发事务之间相互隔离
• 持久性（Durability）：事务一旦提交，对数据库中数据的改变是永久性的，不会因为数据库实例发生故障，导致数据失效

日志序列号 LSN：保存在磁盘数据页、redo 文件、buffer pool、redo buffer。MySQL 每次数据库启动，都会比较磁盘数据页和 Redo Log 的 LSN，必须要求两者 LSN 一致数据库才能正常启动。

WAL：Write Ahead Log，日志优先于数据页写的方式实现持久化。

脏页：内存脏页，即内存中发生了修改，没回写入到磁盘之前，把内存页称之为脏页。

CKPT：Checkpoint 检查点，就是将脏页刷写到磁盘的动作。

TXID：事务号，InnoDB 会为每一个事务生成一个事务号，伴随着整个事务生命周期。

双一标准：参数 innodb_flush_log_at_trx_commit=0/1/2。

• 1：在每次事务提交时，会立即刷新 redo buffer 到磁盘，commit 才能成功
• 0：每秒刷新 redo buffer 到 OS Cache，再 fsync 到磁盘，异常宕机时，会有可能导致丢失 1s 内的事务
• 2：每次事务提交，都立即刷新 redo buffer 到 OS Cache，再每秒 fsync 到磁盘，异常宕机时，会有可能导致丢失 1s 内的事务

事务日志

包括重做日志（Redo Log）和回滚日志（Undo Log）。

Redo Log

文件位置在数据目录下的：ib_logfile0~ib_logfileN。Redo Log 是用来存储 MySQ 在做修改类 (DML) 操作时数据页变化过程及 LSN 版本号，属于物理日志。默认两个文件存储 Redo，是循环覆盖使用的。

commit 时会立即写入磁盘，即日志落盘成功。当 MySQL 出现 Crash 异常宕机时，主要提供前滚功能(CSR)。

控制参数：

• innodb_log_file_size：设置文件大小
• innodb_log_files_in_group：设置文件个数
• innodb_log_group_home_dir：设置存储位置

Undo Log

5.7 默认文件位置在数据目录下的：ibdataN、ibtmp1。Undo Log 是用来存储回滚日志的，可以理解为记录了每次操作的反操作，属于逻辑日志。功能：

• 使用快照功能，提供 InnoDB 多版本并发读写
• 通过记录的反操作，提供回滚功能

在 rolback 时，将数据恢复到修改之前的状态；在 CSR 实现的是，将 redo 当中记录的未提交事务进行回滚（先 redo 前滚，再 undo 回滚）。undo 在生成过程中，也是会记录到 redo 信息中的。

查看回滚段个数：select @@innodb_rollback_segments;

事务操作

查看 / 设置事务提交方式

select @@autocommit;
set @@autocommit=0;

开启事务：start transaction; 或 begin;

提交事务：commit; 刷新当前事务 redo buffer 到磁盘，还会顺便将 redo buffer 中没有提交的事务日志也刷新到磁盘。此时为了区分不同状态的 redo，会加一些比较特殊的标记（是否提交标记）。

回滚事务：rollback;

自动提交功能：select @@autocommit;

隐式提交：DDL、DCL、锁定语句等非 DML 语句时，会触发隐式提交。

隐式回滚：会话关闭、数据库宕机、事务语句执行失败。

并发事务问题

• 脏读：一个事务读到另外一个事务还没有提交的数据
• 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读
• 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在（别的事务插入数据的幻行），好像出现了幻影

事务隔离级别

• RU：读未提交
• RC：读已提交
• RR：可重复读，利用 undo 的一致性快照读
• SR：可串行化

这里的读不是 SOL 层数据行的 select，而是指存储引擎的读，是 page 的读取。

隔离级别	脏读	不可重复读	幻读
read uncommitted	✓	✓	✓
read committed	✕	✓	✓
repeatable read（默认）	✕	✕	✓
serializable	✕	✕	✕

SR 串行化事务可以规避以上问题，但不利于事务的并发。

查看当前会话隔离级别：select @@transaction_isolation;

查看全局隔离级别：select @@gobal.transaction_isolation;

设置事务隔离级别：

• 会话级：set session transaction isolation level read committed;
• 全局级：set global transaction isolation level read committed;

RR 级别（MySQL 默认隔离级别）已经可以解决 99% 以上的幻读，但为了更加严格还加入了 GAP 锁、Next-Lock 锁。

• 针对 快照读（普通 select 语句），通过 MVCC（多版本并发控制）方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好的避免了幻读问题
• 针对 当前读（select … for update 等语句），通过 next-key lock（记录锁 + 间隙锁）方式解决了幻读，因为当执行 select … for update 语句时，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好的避免了幻读问题

MySQL 可重复读隔离级别并没有彻底解决幻读，只是很大程度上避免了幻读现象的发生。要避免特殊场景下发生幻读现象的话，就是尽量在开启事务之后，马上执行 select … for update 这类当前读的语句，因为它会对记录加 next-key lock，从而避免其他事务插入一条新记录。