MySQL学习笔记基础

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，从而避免其他事务插入一条新记录。