MySQL
概述
语句分类
DDL语句：数据定义语句（Data Define Language），例如：创建（create），修改（alter），删除（drop）等
DML语句：数据操作语句，例如：增（insert)，删（delete），改（update），查（select）
DCL语句：数据控制语句，例如：grant，commit，rollback等
命令语句：USE语句，SHOW语句，SET语句等。
注意
mysql的sql语法不区分大小写
关键字作为变量名一般用飘号扩起 ( ' ')
注释
单行注释：#注释内容（mysql特有的）
单行注释：--空格注释内容 其中--后面的空格必须有
多行注释：/* 注释内容 */
数据库架构
两层结构
服务器既负责处理业务请求，也负责数据库的管理
三层架构
逻辑处理层与数据处理层分离，由单独的数据库服务器执行数据处理
主从式结构
主数据库处理写操作，从数据库处理读操作。从数据库可以作为主数据库的备份，相比分布式架构，主从复制结构相对简单
分布式结构
将数据分布在多个节点上，每个节点负责一部分数据，提高了数据处理的能力。
数据库模式
- 内模式：数据库的物理存储结构，描述了数据在存储介质上如何存储（如使用了b+树）
- 模式：定义了数据库的全局逻辑结构，定义了一个电商数据库，其中包含用户、商品、订单等表及这些表之间的关系。
- 外模式：数据库用户的视图，如销售部门的用户只能看到订单和客户相关的数据视图，而不可以访问财务数据。
DDL语句
数据库
#创建数据库
create database 数据库名;
#查看所有的数据库
show databases;
#查看某个数据库的详细定义语句
show create database 数据库名;
#修改数据库编码以及数据库字符集和校对规则,修改数据库编码只会影响之后创建的表的默认编码，之前创建的表不会受影响。
#例：ALTER DATABASE atguigu_chapter3_two CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER DATABASE 数据库名称 CHARACTER SET 数据库编码 COLLATE 字符集对应校对规则;
# 删除数据库
drop database 数据库名;
# 使用数据库
#例：ALTER DATABASE 0225db CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
use 数据库名;
#一般先要指定在哪个数据库中对表进行操作，使用哪个数据库
USE 数据库名;
#查看当前数据库下面，当前用户可以查看的表有哪些
SHOW TABLES;表
#创建表格
/*
CREATE TABLE stu(
sid INT,
sname VARCHAR(20),
birthday DATE,
score INT,
gender ENUM('男','女'),
weight DOUBLE(4,1),
tel CHAR(11)
);
*/
CREATE TABLE 表名称(
字段名1 数据类型,
字段名2 数据类型,
字段名3 数据类型
);
#查看表结构
DESC 表名称;
#查看表的详细定义
SHOW CREATE TABLE 表名称;
#删除表格
DROP TABLE IF EXISTS 表名称;
#修改表的字符集和校对规则
#例：ALTER TABLE stu CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
ALTER TABLE 表名称 CHARSET=新字符集 COLLATE=新校对规则;
#增加一个字段
ALTER TABLE 表名称 ADD COLUMN 字段名 数据类型;
#删除一个字段
ALTER TABLE 表名称 DROP COLUMN 字段名;
#修改一个字段名称
ALTER TABLE 表名称 CHANGE 旧字段名称 新的字段名称 数据类型;
#修改一个字段的数据类型
ALTER TABLE 表名称 MODIFY 字段名称 新数据类型;
#修改字段的位置，顺序
#例：ALTER TABLE stu MODIFY phone CHAR(11) AFTER sname;
ALTER TABLE 表名称 MODIFY 字段名称 数据类型 AFTER 另一个字段;
ALTER TABLE 表名称 MODIFY 字段名称 数据类型 FIRST;
#重命名表
ALTER TABLE 表名称 RENAME TO 新名称;导入/导出数据
-- 导入数据
#注意：执行前需先查看SQL脚本中是否有USE语句，如果没有，那么在命令行中需要先使用USE语句指定具体的数据库，否则会报“No database selected”的错误。
#命令行登录客户端执行命令
mysql> source 文件路径
#设置编码格式为gbk
set names gbk;
-- 导出数据
#在命令行客户端不登录mysql，使用mysqldump命令
mysqldump -u用戶名 -p密码 数据库名 > 脚本名.sql
mysqldump -u用戶名 -p密码 数据库名 表名 > 脚本名.sqlDML语句
SELECT
七大子句
编写顺序
（1）from：从哪些表中筛选
（2）on：关联多表查询时，去除笛卡尔积
（3）where：从表中筛选的条件
（4）group by：分组依据
（5）having：在统计结果中再次筛选（with rollup)，必须和group一起使用
（6）order by：排序，默认是升序（asc），降序要加desc，当然升序也可以加asc
（7）limit：分页
where和having的区别？
where是针对原表的元素数据筛选，后面不能接分组函数（avg,sum,count,max,min等）
having是针对分组统计结果的再次筛选，后面可以接分组函数，还可以使用统计结果的别名
limit
limit (n-1)*total, total（记录开始的下标, 每页的总数）
读写顺序
from> on> join> where> group by> having>select>distinct>order by>limit
FROM子句组合所有的表如果查询涉及多个表，那么
FROM子句就会将这些表组合在一起，形成一个结果集。这个过程通常被称为"笛卡尔积"或"交叉连接"。如果employees表有m行，departments表有n行，那么结果集将有m*n行。（并不会真的生成和存储整个笛卡尔积，因为存在索引等优化方式）ON子句定义了表的连接条件如果查询中包含
JOIN操作，那么ON子句会定义如何连接这些表。MySQL会根据ON子句中的条件，从FROM子句生成的结果集中选择满足条件的行。JOIN操作执行了表的连接根据
ON子句的条件，JOIN操作会将FROM子句中的表连接在一起。连接的方式取决于JOIN的类型（如INNER JOIN，LEFT JOIN等）。WHERE子句过滤出符合条件的记录GROUP BY子句将数据按照指定的字段进行分组HAVING分组之后，根据HAVING子句中的条件来过滤组。只有满足条件的组才会被包含在结果集中。SELECT子句选择需要显示的列DISTINCT子句去除重复的行ORDER BY子句对结果进行排序LIMIT子句限制返回的行数
关联查询
1、内连接：inner join ... on A表 ∩ B表，即只有二者的关联词相交部分才会输出，null与null是不等的
2、左连接：A left join B on#即左边全会输出，当存在右边没有时，使用null替代 A表全部（A表- A∩B）
3、右连接：A right join B on#即右边会全输出，当左边不存在时，使用null替代 B表全部（B表-A∩B）
4、全外连接：full outer join ... on，但是mysql不支持这个关键字，mysql使用union（合并）结果的方式代替、
5、自连接：进行关联查询的两个表，本质上是一个表。
#查询每一个员工的编号，姓名，和他的领导的编号和姓名
SELECT emp.eid,emp.ename,emp.`mid`,mgr.ename
FROM t_employee AS emp LEFT JOIN t_employee AS mgr
ON emp.mid = mgr.eid;
分析员工表：有一个字段是mid，是领导编号，即表示这个员工归谁管。那么这里的领导编号其实就是这个领导他作为员工的员工编号。通过取别名的方式，把一张表虚拟成两张表。当然，也要关联字段。 A、B两表共有
所有有门派的人员以及部门信息（A、B两表共有），使用内连接，只查询满足条件的数据。
# sql92
SELECT *
FROM t_emp t1,t_dept t2 WHERE t1.`deptId` = t2.`id`;
# sql99语法
SELECT *
FROM t_emp t1
INNER JOIN t_dept t2
ON t1.`deptId` = t2.`id`;A的全集
列出所有用户，并显示其部门信息
SELECT *
FROM t_emp t1
LEFT JOIN t_dept t2
ON t1.`deptId` = t2.`id`;B的全集
列出有部门的人员以及所有部门
SELECT *
FROM t_emp t1
RIGHT JOIN t_dept t2
ON t1.`deptId` = t2.`id`;A的独有
SELECT *
FROM t_emp t1
LEFT JOIN t_dept t2
ON t1.`deptId` = t2.`id`
WHERE t2.`id` IS NULL;B的独有
SELECT *
FROM t_emp t1
RIGHT JOIN t_dept t2
ON t1.`deptId` = t2.`id`
WHERE t1.id IS NULL;AB全有
UNION可以合并两个查询的结果集，但是两个结果的列必须一致，还可以自动去重
SELECT *
FROM t_emp t1
LEFT JOIN t_dept t2
ON t1.`deptId` = t2.`id`
UNION
SELECT *
FROM t_emp t1
RIGHT JOIN t_dept t2
ON t1.`deptId` = t2.`id`;A的独有+B的独有
列出所有没部门的人员和没人的部门
SELECT *
FROM t_emp t1
LEFT JOIN t_dept t2
ON t1.`deptId` = t2.`id`
WHERE t2.`id` IS NULL
UNION
SELECT *
FROM t_emp t1
RIGHT JOIN t_dept t2
ON t1.`deptId` = t2.`id`
WHERE t1.id IS NULL;子查询
通常子查询都是先执行的SELECT查询语句，需要用()括起来。子查询的查询结果是作为另一个SQL语句的 条件、数据范围等。
SELECT中嵌套子查询
#在“t_employee”表中查询每个人薪资和公司平均薪资的差值，并显示员工薪资和公司平均薪资相差5000元以上的记录。
SELECT ename,salary,
salary - (SELECT AVG(salary) FROM t_employee) AS 与公司平均薪资的差值
FROM t_employee
WHERE ABS(salary - (SELECT AVG(salary) FROM t_employee)) > 5000;WHERE或HAVING中嵌套子查询
1、子查询的结果是单列的，可以嵌套在select后面和where后面。根据子查询结果的情况，分为如下三种情况。
- 当子查询的结果是单列单个值，那么可以直接使用比较运算符，如“<”、“<=”、“>”、“>=”、“=”、“!=”等与子查询结果进行比较。
- 当子查询的结果是单列多个值，那么可以使用比较运算符IN或NOT IN进行比较。
- 当子查询的结果是单列多个值，还可以使用比较运算符, 如“<”、“<=”、“>”、“>=”、“=”、“!=”等搭配ANY、SOME、ALL等关键字与查询结果进行比较。
-- ANY 和 SOME 是同义词，它们用于比较目标值与子查询或列表中的值，如果满足任一比较条件，则返回TRUE。例如，SELECT * FROM t1 WHERE column1 = ANY (SELECT column1 FROM t2); 会返回在t1中的column1的值在t2的column1中存在的所有记录。
-- ALL 用于比较目标值与子查询或列表中的所有值，只有当目标值满足所有比较条件时，才返回TRUE。例如，SELECT * FROM t1 WHERE column1 > ALL (SELECT column1 FROM t2); 会返回在t1中的column1的值大于t2的column1中所有值的所有记录。
-- IN 用于指定多个值在WHERE语句中。如果目标值在列表中，则返回TRUE。例如，SELECT * FROM t1 WHERE column1 IN (1, 2, 3); 会返回在t1中的column1的值为1，2或3的所有记录。
#在“t_employee”表中查询薪资最高的员工姓名（ename）和薪资（salary）。
#第一步：全公司薪资最高的薪资值是多少
SELECT MAX(salary) FROM t_employee;
#第二步：谁的薪资和 这个最高薪资值相同
SELECT *
FROM t_employee
WHERE salary = (SELECT MAX(salary) FROM t_employee);FROM后面嵌套子查询
子查询的结果是单列多个值，只能嵌套在where后面。与in, =any, >any, =all....结合做条件判断。 子查询的结果是多列多个值（类似于表格），只能嵌套在from后面，当成临时表使用
#在“t_employee”表中，查询每个部门的平均薪资，然后与“t_department”表联合查询，所有部门的部门编号、部门名称、部门平均薪资。
#方案一：先联合两个表，再分组
SELECT dept.did,dname,AVG(salary)
FROM t_employee AS emp RIGHT JOIN t_department AS dept ON emp.did = dept.did
GROUP BY dept.did
#方案二：先分组后联合
SELECT dept.did,dname,a
FROM
(SELECT did,AVG(salary) AS a FROM t_employee GROUP BY did) AS avgtable
RIGHT JOIN
t_department AS dept
ON avgtable.did = dept.did;特殊的子查询
如果EXISTS关键字后面的参数是一个任意的子查询
- 如果至少返回一行，那么EXISTS的结果为true，此时外层查询语句将进行查询；
- 如果子查询没有返回任何行，那么EXISTS的结果为false，此时外层查询语句不进行查询。
- EXISTS和NOT EXISTS的结果只取决于是否返回行，而不取决于这些行的内容
如果EXISTS关键字后面的参数是一个关联子查询（即子查询的WHERE条件中包含与外层查询表的关联条件）
- 那么此时将对外层查询表做循环，即在筛选外层查询表的每一条记录时，都看这条记录是否满足子查询的条件，如果满足就再用外层查询的其他WHERE条件对该记录进行筛选，否则就丢弃这行记录。
#（1）查询“t_employee”表中是否存在部门编号为NULL的员工，
#如果存在，查询“t_department”表的部门编号、部门名称。
#第一步：查询“t_employee”表中是否存在部门编号为NULL的员工
SELECT * FROM t_employee WHERE did IS NULL
#第二步：查询“t_department”表的部门编号、部门名称。
SELECT did,dname FROM t_department WHERE EXISTS (SELECT * FROM t_employee WHERE did IS NULL)
#（2）查询“t_department”表是否存在与“t_employee”表相同部门编号的记录，
#如果存在，查询这些部门的编号和名称。
SELECT * FROM t_department
WHERE EXISTS (SELECT * FROM t_employee WHERE t_department.did = t_employee.did);
#等价于
SELECT DISTINCT t_department.*
FROM t_department INNER JOIN t_employee
ON t_department.did = t_employee.did;将子查询结果插入表
#依靠t_employee创建一个表tab，tab含有t_employee的所有字段
CREATE TABLE tab LIKE t_employee;
#通过子查询添加数据
INSERT INTO tab (SELECT * FROM t_employee WHERE did =2);INSERT
#添加语句，要求值列表的数量、顺序要与表结构一一对应
INSERT INTO 表名称 VALUES(值列表);
#添加语句，可以指定部分字段赋值，值列表只需要和前面的字段列表数量、顺序一一对应就可以
INSERT INTO 表名称(字段列表) VALUES(值列表);
#一条insert语句同时添加多行记录
INSERT INTO 表名称 VALUES(值列表),(值列表),(值列表)...;
INSERT INTO 表名称(字段列表) VALUES(值列表),(值列表),(值列表)...;UPDATE
#修改该字段下的所有值
UPDATE 表名称 SET 字段名 = 值,字段名 = 值,字段名 = 值;
#有where条件的修改，只会修改满足条件的值
UPDATE 表名称 SET 字段名 = 值,字段名 = 值,字段名 = 值 WHERE 条件;
#修改“何进”员工的薪资和“孙洪亮”的薪资一样
#报错，现在在from子句中出现了select，说明它正在被“查询”，就不能同时被修改。
update t_employee set salary = (select salary from t_employee where ename = '孙洪亮') where ename = '何进';
#把子查询(SELECT salary FROM t_employee WHERE ename = '孙洪亮') 的结果放到一个内存中“临时表”中，可以解开t_employee的锁，然后修改t_employee表就不会因为锁的问题被拒绝。
UPDATE t_employee SET salary=(SELECT salary FROM(SELECT salary FROM t_employee WHERE ename='孙洪亮')emp)
WHERE ename='何进';DELETE
#删除整个表的数据
DELETE FROM 表名称;
#删除部分行
DELETE FROM 表名称 WHERE 条件;
#删除weight列的数据
UPDATE student SET weight=NULL;
#删除weight列的定义
ALTER TABLE student DROP weight;
#删除和“何进”同一个部门的员工
DELETE FROM t_employee WHERE did=(SELECT did FROM (SELECT did FROM t_employee WHERE ename='何进')temp);其他关键字
distinct
#结果去重：将distinct后指定的字段列表拼接起来，进行去重（要求拼接起来的字段不相同）
select distinct 字段列表 from 表名称 【where 条件】;
select distinct test1,test2 from t_employee;
#仅对test字段进行去重
select distinct test from 表名称 【where 条件】;
#多个字段，对指定字段进行去重，注意去重后，不能出现一个去重字段，对应多个未去重字段
SELECT GROUP_CONCAT(DISTINCT station_number) FROM climate_info约束
主键约束(PRIMARY KEY)
主键约束元素必须唯一并且非空，且一张表最多只能有一个主键约束。但是主键可以由多列组成（复合主键）
主键列会自动创建索引，如果删除主键约束了，主键约束对应的索引就自动删除了。
#did单约束
CREATE TABLE tab(
did INT PRIMARY KEY,
eid INT
);
#did和eid复合约束，它俩的组合不能为NULL，并且唯一
CREATE TABLE tab(
did INT ,
eid INT,
PRIMARY KEY (did,eid)
);
#建表后增加主键约束,如果字段列表是一个字段，就是一个字段是主键；如果是多个字段，就是复合主键
ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表);唯一键约束(UNIQUE KEY)
- 一个表可以有很多个唯一键约束，每一个唯一键约束字段都会自动创建索引。唯一键约束允许为空,不允许重复，也可以创建复合索引
- 删除唯一键约束的索引来删除唯一键约束，索引名默认是字段名，复合唯一默认是第一个字段名。
CREATE TABLE tab(
did INT UNIQUE KEY,
eid INT UNIQUE KEY
);
CREATE TABLE tab(
did INT ,
eid INT ,
UNIQUE KEY(did,eid)
);
#建表后增加唯一键约束,如果字段列表是一个字段，就是一个字段是唯一键；如果是多个字段，就是复合唯一键
ALTER TABLE tab ADD UNIQUE KEY(did,eid);非空约束
一个表可以限制多列非空，但只能单独限定非空，不能组合非空。
CREATE TABLE tab(
did INT ,
eid INT NOT NULL
);
/*
如果某个字段有not null非空约束，使用modify修改字段的数据类型、位置、字符集和校对规则、默认值约束时，
想要保留非空约束，必须把not null带上，否则会在修改字段的数据类型、位置、字符集和校对规则时，会把非空约束给丢掉。
*/
alter table 表名称 modify 【column】 字段名 数据类型 not null;默认值约束
给某个字段/某列指定默认值，当添加时或修改时，未指定对应数据，将会使用默认值。
#说明：默认值约束一般不在唯一键和主键列上加，可以与not null一同
create table 表名称(
字段名 数据类型 default 默认值 ,
字段名 数据类型 not null default 默认值,
primary key(字段名),
unique key(字段名)
);
#增加默认值约束
alter table 表名称 modify 字段名 数据类型 default 默认值;
#删除score的默认值约束
ALTER TABLE student MODIFY score INT;
#删除gender的默认值约束，保留非空约束
ALTER TABLE student MODIFY gender ENUM('男','女') NOT NULL;
#删除gender的非空约束，保留默认值约束
ALTER TABLE student MODIFY gender ENUM('男','女') DEFAULT '男';
#保留非空和默认值约束
ALTER TABLE student MODIFY gender ENUM('男','女') DEFAULT '男' NOT NULL;检查约束
控制表中的元素范围
-- 如果省略或指定为ENFORCED，则会创建检查约束并强制执行约束，不满足约束的数据行不能插入成功。
-- 如果写的是not ENFORCED，则不满足检查约束也没关系。
CREATE TABLE student(
sid INT PRIMARY KEY, #主键约束
sname VARCHAR(20) ,
age INT CHECK (age>=18 && age<=35) enforced
);
#如何在建表后添加检查约束，使用add check
alter table 表名称 add check(条件);
alter table 表名称 drop check 检查约束名;外键约束
建外键约束后，增删改操作从表中修改对应字段，必须在主表中存在。同理主表的相关操作也受到从表的限制
（1）从表的外键列的数据类型，要与主表被参考/被引用的列的数据类型一致，并且逻辑意义一致。名字不一定要求相同。
（2）外键列也会自动建立索引（根据外键查询效率很高，很多）
（3）外键约束的删除，所以不会自动删除，如果要删除对应的索引，必须手动删除
添加外键约束后，在 t_student 表中，sno 列的每个值都必须在 t_school 表的 sno 主键列中有对应的值。
#创建主表
create table t_school(
sno int primary key,
sname varchar(255)
);
#创建从表，并添加外键，关联从表的某个字段到主表
create table t_student(
no int primary key,
name varchar(255),
age int,
sno int,
-- 约束起名为t_school_sno_fk，t_student表的sno关联t_school(sno)
constraint t_school_sno_fk foreign key(sno) references t_school(sno)
);
alter table 从表名称 add foreign key (从表的字段) references 主表（被引用字段) on update xx on delete xx;级联删除
创建子表时，外键可以添加：on delete cascade，这样在删除父表数据时，子表会级联删除。谨慎使用。
create table t_student(
no int primary key,
name varchar(255),
age int,
sno int,
constraint t_school_sno_fk foreign key(sno) references t_school(sno) on delete cascade
);级联更新
create table t_student(
no int primary key,
name varchar(255),
age int,
sno int,
constraint t_school_sno_fk foreign key(sno) references t_school(sno) on update cascade
);级联置空
create table t_student(
no int primary key,
name varchar(255),
age int,
sno int,
constraint t_school_sno_fk foreign key(sno) references t_school(sno) on delete set null
);约束删除
mysql中，会给主键约束，唯一键约束，外键约束，自动创建索引。通过底层单独的索引表，来记录每一个索引值对应的数据地址。作用是提高查询的效率。因为唯一键约束，主键约束等，它们都是表中“关键“信息，可以通过它们快速的定位到某个记录。
-- 查看表的索引
show index from 表名称;
-- 删除表索引
ALTER TABLE 表名称 DROP INDEX 索引名;
-- 删除主键，因为主键约束是唯一的，所以直接删
ALTER TABLE 表名称 DROP PRIMARY KEY;
-- 查看一个表的约束名等信息，查看系统库“information_schema”的“table_constraints”
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';
-- 删除约束
alter table 从表名 drop foreign key 外键约束名;自增属性
- 一个表最多只能有一个字段是自增的
- 自增的字段必须是整数类型.自增字段赋值为null和0的时候，会从按当前值继续自增，如果赋值为其他的值，按照你指定的值来继续自增。
- 自增的字段必须是键约束的字段（通常是主键、唯一键）
ALTER TABLE 表名称 AUTO_INCREMENT = 起始值;
ALTER TABLE stu AUTO_INCREMENT = 1001;事务
事务是指一组sql操作要么一起成功后提交，要么一起失败后回滚。
- mysql中默认事务是自动提交的。通过
set autocommit = false开启手动提交模式。 - create ,drop, truncate，alter，只有insert,update,delete支持事务。
事务四大特征【ACID】
- 原子性：要么全部完成，要么全部不完成，如果事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。。
- 一致性：事务的一致性指的是在一个事务执行之前和执行之后数据库都必须处于一致性状态。如转账前后的一致性规则是：转账前后两个账户的总金额是不变的。
- 隔离性：指的是在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。事务查看数据更新时，数据所处的状态要么是另一事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看到中间状态的数据。
- 持久性：指的是只要事务成功结束，它对数据库所做的更新就必须保存下来。即使发生系统崩溃，重新启动数据库系统后，数据库还能恢复到事务成功结束时的状态。
事务创建流程
开启事务：START TRANSACTION;
创建一个还原点tx1：SAVEPOINT tx1;
还原到还原点tx1：ROLLBACK TO tx1;
回滚：ROLLBACK;
提交事务：COMMIT;
事务回滚原理
undo log（即撤销日志）是InnoDB引擎中用来记录事务执行过程中所做的修改操作的日志。当一个事务修改数据库中的数据时，它会生成一些更改操作，这些更改操作就会记录在undo log 中。如果此时需要回滚，那么按照 undo log 链表执行一遍即可。
insert 操作
插入操作向数据库中增加了一条记录，回滚时就需要删除掉这条记录，此时 undo log 只需要记录该记录的主键即可。
delete 操作
逻辑删除阶段：将当前记录的deleted_flag 字段标记为 1
实际删除阶段：事务提交后，会有专门的线程将记录删除掉，此后该记录被真正删除。
回滚操作只会出现在第一阶段，只需要将deleted_flag字符标记为0即可。
update 操作
update 操作可以拆分为两个操作：删除旧记录，插入新记录。
- 不涉及了主键字段 由于不涉及主键字段，InnoDB会在删除阶段直接将记录删除，只生成一条 undo log。
- 涉及主键字段 涉及主键字段的更新，将会影响该记录在聚簇索引中的位置。因此，在删除时会使用逻辑删除，然后插入新的记录。这种情况下，会生成两条 undo log。
事务隔离级别：Isolation
三种问题
所以不可重复读和幻读都是读的过程中数据前后不一致，只是前者侧重于修改，后者侧重于增加。严格来讲“幻读”可以被称为“不可重复读”的一种特殊情况，但是从数据库管理的角度来看二者是有区别的。解决“不可重复读”只要加行级锁就可以了（对第一次查出来的数据行加锁，禁止了修改和删除）。而解决“幻读”则需要加表级锁（加行级锁无法禁止添加新的数据，所以需要用表级锁）
脏读：脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问 这个数据，然后使用了这个数据。（读取了未提交的数据，禁止写时读，避免了“脏读”）
幻读：指读的过程中，某些元组被增加或删除，这样进行一些集合操作，比如算总数，平均值等等，就会每次算出不一样的数。（指加入了新的行）
不可重复读：读到的是已提交的数据，比如某个读事务持续时间比较长，期间多次读取某个元组，每次读到的都是被别人改过并已提交的不同数据。可以理解为在执行任务的过程中，领导的指令一直在变。。但好歹是正式下达的指令。（读取的数据被改变，禁止读时写，避免了“不可重复读）
4 种事务隔离级别
Oracle 支持的 2 种事务隔离级别：READ-COMMITED, SERIALIZABLE。 Oracle 默认的事务隔离级别为: READ COMMITED 。
| 隔离级别 | 描述 |
|---|---|
| read-uncommitted 读未提交 | 允许A事务读取其他事务未提交和已提交的数据。会出现脏读、不可重复读、幻读问题 |
| read-committed读已提交 | 只允许A事务读取其他事务已提交的数据。可以避免脏读，但仍然会出现不可重复读、幻读问题 |
| repeatable-read可重复读 | 数据库会为查询到的每一行数据都创建一个数据版本（snapshot），并在事务内保持这个版本的一致性。也就是说，即使其他事务修改了这些数据，当前事务读取的还是开始时的数据版本，从而避免了"不可重复读"的问题。可以避免脏读和不可重复读。但是幻读问题仍然存在。 |
| serializable序列化 | 确保事务可以从一个表中读取相同的行，相同的记录。在这个事务持续期间，禁止其他事务对该表执行插入、更新、删除操作。所有并发问题都可以避免，但性能十分低下。【锁表，同一表不能被同时操作】 |
MySQL内置
运算符
- 算术运算符
- +、-、*、/、%、div（只保留整数部分）
- 比较运算符
- 大于：>、小于：<、大于等于：>=、小于等于：>=、不等于：!=和<>、等于：=和<=>
- 区间运算符
- between x and y 和 not between x and y
- in (x,x,x) 和 not in(x,x,x)
- 模糊运算符
- %：代表任意个字符，如：
'%冰%';#冰前面可以有任意字符，冰后面可以有任意字符，但是必须含有冰 - _：代表一个字符，如果两个下划线代表两个字符，如：
'__冰';#只能有三个字符,__表示冰前面有两个字符，且冰是第三个。
- %：代表任意个字符，如：
- 逻辑运算符
- 逻辑与：&& 或 and
- 逻辑或：|| 或 or
- 逻辑非：! 或 not
- 逻辑异或： xor ,即前后两个条件，只满足一个，且至少满足一个。
- null
- 在 SQL 中，NULL 不等于任何值，包括它自己。不能使用比较运算符进行null值的比较（除了<=> 能用于null判判等）
- IS NULL：判断是否为null（
name<=> NULL起到同样的作用） - IS NOT NULL：判断是否不为null（!(
name<=> NULL)起到同样的作用） - ifnull(xx,代替值) ：当xx是null时，用代替值计算。因为null与任何数进行运算，结果均为null。
数据类型
数值类型
关键字INT是INTEGER的同义词。
关键字DEC和FIXED是DECIMAL的同义词。
NUMERIC和DECIMAL类型被视为相同的数据类型。
DOUBLE视为DOUBLE PRECISION的同义词，并在REAL_AS_FLOAT SQL模式未启用的情况下，将REAL也视为DOUBLE PRECISION的同义词。
整数类型
对于整数类型，MySQL还支持在类型名称后面加小括号(M)，而小括号中的M表示显示宽度，M的取值范围是(0, 255)。int(M)这个M在字段的属性中指定了unsigned（无符号）和zerofill（零填充）的情况下才有意义。表示当整数值不够M位时，用0填充，如果达到M位，使用实际储存的值
-- 对于生效的int(4)
-- 插入1===>显示0001
-- 插入12345===>显示12345
create table t_int(
i1 int,
i2 int(2) #没有unsigned zerofill，(2)没有意义
);bit类型
bit类型，如果没有指定(M)，默认是1bit。
对于位类型字段，之前版本直接使用SELECT语句将不会看到结果，而在MySQL8版本中默认以“0X”开头的十六进制形式显示，可以通过BIN()函数显示为二进制格式。
create table t_bit(
b1 bit, #没有指定(M)，默认是1位二进制
b2 bit(4) #能够存储4位二进制0000~1111
);小数类型
浮点数有两种类型：单精度浮点数（FLOAT）和双精度浮点数（DOUBLE），定点数只有DECIMAL。浮点数和定点数都可以用(M，D)来表示。
- M是精度，表示该值总共显示M位，包括整数位和小数位，对于FLOAT和DOUBLE类型来说，M取值范围为0~255，而对于DECIMAL来说，M取值范围为0~65。
- D是标度，表示小数的位数，取值范围为0~30，同时必须<=M。
概述
浮点型FLOAT(M，D) 和DOUBLE(M，D)是非标准用法，如果考虑到数据库迁移，则最好不要使用。类型在不指定（M，D）时，默认会按照实际的精度来显示。
DECIMAL类型在不指定（M，D）时，默认只保留整数部分。定义DECIMAL（5,2）的类型，表示该列取值范围是-999.99~999.99。如果用户插入数据的小数部分位数超过D位，MySQL会四舍五入处理，但是如果用户插入数据的整数部分位数超过“M-D”位，则会报“Out of range”的错误。DECIMAL实际是以字符串形式存放的，精度高，但是占用储存大
create table t_double(
d1 double,
d2 double(5,2) #-999.99~999.99
);字符串类型
文本字符串类型：
二进制字符串类型：
char和varchar
CHAR(M)为固定长度的字符串，利于检索，但是消耗空间。 M表示最多能存储的字符数，取值范围是0~255个字符，如果未指定(M)表示只能存储1个字符。如果存储的值少于指定字符，右侧将用空格填充以达到指定的长度，当查询显示CHAR值时，尾部的空格将被删掉。
VARCHAR(M)为可变长度的字符串，不利于检索，但是节省空间。M表示最多能存储字符数，M的范围由最长的行的大小（通常是65535）和使用的字符集确定。
Enum和Set类型
字段名ENUM（‘值1’，‘值2’，…‘值n’）
字段名 SET（‘值1’，‘值2’，…‘值n’）
ENUM类型的字段在赋值时，只能在指定的枚举列表中取值，而且一次只能取一个。枚举列表最多可以有65535个成员。ENUM值在内部用整数表示，每个枚举值均有一个索引值， MySQL存储的就是这个索引编号。例如，定义ENUM类型的列(‘first’, ‘second’, ‘third’)。
SET类型的字段在赋值时，可从定义的值列表中选择1个或多个值的组合。SET列最多可以有64个成员。SET值在内部也用整数表示，分别是1，2，4，8……，都是2的n次方值，因为这些整数值对应的二进制都是只有1位是1，其余是0。
演示枚举类型：
create table temp(
gender enum('男','女'),
hobby set('睡觉','打游戏','泡妞','写代码')
);insert into temp values('男','睡觉,打游戏'); #成功
insert into temp values('男,女','睡觉,打游戏'); #失败
#ERROR 1265 (01000): Data truncated for column 'gender' at row 1
insert into temp values('妖','睡觉,打游戏');#失败
ERROR 1265 (01000): Data truncated for column 'gender' at row 1
insert into temp values('男','睡觉,打游戏,吃饭');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1BINARY和VARBINARY类型
BINARY和VARBINARY类似于CHAR和VARCHAR，只是它们存储的是二进制字符串。
BINARY (M)为固定长度的二进制字符串，M表示最多能存储的字节数，取值范围是0~255个字节，如果未指定(M)表示只能存储1个字节。例如BINARY (8)，表示最多能存储8个字节，如果字段值不足(M)个字节，将在右边填充'\0'以补齐指定长度。
VARBINARY (M)为可变长度的二进制字符串，M表示最多能存储的字节数，总字节数不能超过行的字节长度限制65535，另外还要考虑额外字节开销，VARBINARY类型的数据除了存储数据本身外，还需要1或2个字节来存储数据的字节数。VARBINARY类型和VARCHAR类型一样必须指定(M)，否则报错。
二进制字符串和文本字符串
二进制字符串是存储客户端给服务器端传输的字符串的原始二进制值，而文本字符串则会按照表和字段的字符集编码方式对客户端给服务器传输的字符串进行转码处理。
二进制字符串严格区分大小写（因为大小写字符的编码值不同）【注意不同平台的编码规则】，文本字符串在大多数字符集和校对规则中不区分大小写。
BLOB和TEXT类型
BLOB是一个二进制大对象，用来存储可变数量的二进制字符串，分为TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB四种类型。TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT四种文本类型，它们分别对应于以上四种BLOB类型，具有相同的最大长度和存储要求。
BLOB类型与TEXT类型的区别如下：
（1）BLOB类型存储的是二进制字符串，TEXT类型存储的是文本字符串。BLOB类型还可以存储图片和声音等二进制数据。
（2）BLOB类型没有字符集，并且排序和比较基于列值字节的数值，TEXT类型有一个字符集，并且根据字符集对值进行排序和比较。
#演示blob和text
#blob系列是大的二进制数据类型
#text系列是大的文本字符串类型
#创建表格
create table t_blob_text(
b blob,
t text
);日期时间类型
如果需要经常插入或更新日期时间为系统日期时间，则通常使用TIMESTAMP类型，格式为“YYYY-MM-DD HH:MM:SS”，例如“2022-02-04 10:08:08”。TIMESTAMP与DATETIME的区别在于TIMESTAMP的取值范围小，只支持1970-01-01 00:00:01 UTC至2038-01-19 03:14:07 UTC范围的日期时间值，其中UTC是世界标准时间，并且TIMESTAMP类型的日期时间值在存储时会将当前时区的日期时间值转换为时间标准时间值，检索时再转换回当前时区的日期时间值，这会更友好。而DATETIME则只能反映出插入时当地的时区，其他时区的人查看数据必然会有误差的。另外。
字符串比较规则
比较运算
- 数字和数字比较, 字符串和字符串比较, 都会按字面比较
- 数字和字符串比较, 会把字符串转成数字,再比较
- 数字和字符串相进行算数运算, 也会将字符串转为数字
逻辑运算
- 数字和数字运算, 会将数字转成boolean(true和false), 非0的都是true
- 数字和字符串运算, 会先把字符串转成数字, 再把数字转成boolean
字符串转数字
从字符串的左边开始，一直到遇到一个不能被识别为数字的字符为止。如果字符串的开头就不是数字，那么这个字符串会被转换为 0。
eg：3121e9695202b02b253b10cb65c12f51=====>3121 e 9695202(e也是有效数字)，字符串可能会被转换为 1.8e308，这是 MySQL 可以处理的最大的浮点数。（实际查询值：1.7976931348623157e308）
内置函数
分组函数【聚合函数，多行函数】：一行或多行记录通过单行函数计算完，结果的记录数会减少
单行函数：表示会对表中的每一行记录分别计算，有n行得到还是n行结果
数学函数、字符串函数、日期时间函数、条件判断函数、窗口函数等
分组函数
- AVG(x) ：求平均值
- SUM(x)：求总和
- MAX(x)/MIN(x)：求最大/小值
- count(字段名/表达式) 只统计非NULL值的记录数。
- count(*)/count(常量值)：是对满足条件的（如果没有where条件，就是对所有记录）记录累加数量
单行函数
1、数学函数
| 函数 | 用法 |
|---|---|
| ABS(x) | 返回x的绝对值 |
| CEIL(x) | 向上取整 |
| FLOOR(x) | 向下取整 |
| MOD(x,y) | 返回x/y的模 |
| RAND() | 返回0~1的随机值 |
| ROUND(x,y) | 四舍五入x，保留y位 |
| TRUNCATE(x,y) | 直接阶段x，保留y位 |
| FORMAT(x,y) | 与ROUND相同，但是正数部分以逗号分隔，三位一组。 |
| SQRT(x) | 返回x的平方根 |
| POW(x,y) | 返回x的y次方 |
2、字符串函数
下面列出部分字符串函数：
| 函数 | 功能描述 |
|---|---|
| CONCAT(S1,S2,……Sn) | 连接S1,S2,……Sn为一个字符串 |
| CONCAT_WS(s,S1,S2,……Sn) | 同CONCAT(S1,S2,…)函数，但每个字符串之间要加上s |
| CHAR_LENGTH(s) | 返回字符串s的字符数 |
| LENGTH(s) | 返回字符串s的字节数，和字符集有关 |
| LOCATE(str1,str)或 POSITION(str1 in str)或 INSTR(str,str1) | 返回子字符串str1在str中的开始位置 |
| UPPER(s)或UCASE(s) | 将字符串s的所有字母转成大写字母 |
| LOWER(s)或LCASE(s) | 将字符串s的所有字母转成小写字母 |
| LEFT(s,n) | 返回字符串s最左边的n个字符 |
| RIGHT(s,n) | 返回字符串s最右边的n个字符 |
| LPAD(str,len,pad) | 用字符串pad对str最左边进行填充直到str的长度达到len |
| RPAD(str,len,pad) | 用字符串pad对str最右边进行填充直到str的长度达到len |
| LTRIM(s) | 去掉字符串s左侧的空格 |
| RTRIM(s) | 去掉字符串s右侧的空格 |
| TRIM(s) | 去掉字符串s开始与结尾的空格 |
| TRIM([BOTH] s1 FROM s) | 去掉字符串s开始与结尾的s1 |
| TRIM([LEADING] s1 FROM s) | 去掉字符串s开始处的s1 |
| TRIM([TRAILING]s1 FROM s) | 去掉字符串s结尾处的s1 |
| INSERT(str,index,len,instr) | 将字符串str从index位置开始len个字符的替换为字符串instr |
| REPLACE(str,a,b) | 用字符串b替换字符串str中所有出现的字符串a |
| REPEAT(str,n) | 返回str重复n次的结果 |
| REVERSE(s) | 将字符串反转 |
| STRCMP(s1,s2) | 比较字符串s1,s2 |
| SUBSTRING(s,index,len) | 返回从字符串s的index位置截取len个字符，没有指定len，只返回一个字符，-1表示从末尾开始截取 |
| SUBSTRING_INDEX(str, 分隔符，count) | 如果count是正数，那么从左往右数，第n个分隔符的左边的全部内容。例如，substring_index("www.atguigu.com",".",1)是"www"。如果count是负数，那么从右边开始数，第n个分隔符右边的所有内容。例如，substring_index("www.atguigu.com",".",-1)是"com"。 |
3、日期时间函数
| 函数 | 功能描述 |
|---|---|
| CURDATE()或CURRENT_DATE() | 返回当前系统日期 |
| CURTIME()或CURRENT_TIME() | 返回当前系统时间 |
| NOW()/SYSDATE()/CURRENT_TIMESTAMP()/ LOCALTIME()/LOCALTIMESTAMP() | 返回当前系统日期时间 |
| UTC_DATE()/UTC_TIME() | 返回当前UTC日期值/时间值 |
| UNIX_TIMESTAMP(date) | 返回一个UNIX时间戳 |
| YEAR(date)/MONTH(date)/DAY(date)/ HOUR(time)/MINUTE(time)/SECOND(time) | 返回具体的时间值 |
| EXTRACT(type FROM date) | 从日期中提取一部分值 |
| DAYOFMONTH(date)/DAYOFYEAR(date) | 返回一月/年中第几天 |
| WEEK(date)/WEEKOFYEAR(date) | 返回一年中的第几周 |
| DAYOFWEEK() | 返回周几，注意，周日是1，周一是2，…周六是7 |
| WEEKDAY(date) | 返回周几，注意，周一是0，周二是1，…周日是6 |
| DAYNAME(date) | 返回星期，MONDAY,TUESDAY,…SUNDAY |
| MONTHNAME(date) | 返回月份，January,… |
| DATEDIFF(date1,date2)/TIMEDIFF(time1,time2) | 返回date1-date2的日期间隔/返回time1-time2的时间间隔 |
| DATE_ADD(date,INTERVAL expr type)或ADDDATE/DATE_SUB/SUBDATE | 返回与给定日期相差INTERVAL时间段的日期 |
| ADDTIME(time,expr)/SUBTIME(time,expr) | 返回给定时间加上/减去expr的时间值 |
| DATE_FORMAT(datetime,fmt)/ TIME_FORMAT(time,fmt) | 按照字符串fmt格式化日期datetime值/时间time值 |
| STR_TO_DATE(str,fmt) | 按照字符串fmt对str进行解析，解析为一个日期 |
| GET_FORMAT(val_type,format_type) | 返回日期时间字符串的显示格式 |
函数中日期时间类型说明
| 参数类型 | 描述 | 参数类型 | 描述 |
|---|---|---|---|
| YEAR | 年 | YEAR_MONTH | 年月 |
| MONTH | 月 | DAY_HOUR | 日时 |
| DAY | 日 | DAY_MINUTE | 日时分 |
| HOUR | 时 | DAY_SECOND | 日时分秒 |
| MINUTE | 分 | HOUR_MINUTE | 时分 |
| SECOND | 秒 | HOUR_SECOND | 时分秒 |
| WEEK | 星期 | MINUTE_SECOND | 分秒 |
| QUARTER | 一刻 |
函数中format参数说明
| 格式符 | 说明 | 格式符 | 说明 |
|---|---|---|---|
| %Y | 4位数字表示年份 | %y | 两位数字表示年份 |
| %M | 月名表示月份（January,…） | %m | 两位数字表示月份（01,02,03，…） |
| %b | 缩写的月名（Jan.,Feb.,…） | %c | 数字表示月份（1,2,3…） |
| %D | 英文后缀表示月中的天数（1st,2nd,3rd,…） | %d | 两位数字表示表示月中的天数（01,02,…） |
| %e | 数字形式表示月中的天数（1,2,3,…） | %p | AM或PM |
| %H | 两位数字表示小数，24小时制（01,02,03,…） | %h和%I | 两位数字表示小时，12小时制（01,02,03,…） |
| %k | 数字形式的小时，24小时制（1,2,3,…） | %l | 数字表示小时，12小时制（1,2,3,…） |
| %i | 两位数字表示分钟（00,01,02,…） | %S和%s | 两位数字表示秒（00,01,02,…） |
| %T | 时间，24小时制（hh:mm:ss） | %r | 时间，12小时制（hh:mm:ss）后加AM或PM |
| %W | 一周中的星期名称（Sunday,…） | %a | 一周中的星期缩写（Sun.,Mon.,Tues.,…） |
| %w | 以数字表示周中的天数（0=Sunday,1=Monday,…） | %j | 以3位数字表示年中的天数（001,002,…） |
| %U | 以数字表示的的第几周（1,2,3,…） 其中Sunday为周中的第一天 | %u | 以数字表示年中的年份（1,2,3,…） 其中Monday为周中第一天 |
| %V | 一年中第几周（01~53），周日为每周的第一天，和%X同时使用 | %X | 4位数形式表示该周的年份，周日为每周第一天，和%V同时使用 |
| %v | 一年中第几周（01~53），周一为每周的第一天，和%x同时使用 | %x | 4位数形式表示该周的年份，周一为每周第一天，和%v同时使用 |
| %% | 表示% |
GET_FORMAT函数中val_type 和format_type参数说明
| 值类型 | 格式化类型 | 显示格式字符串 |
|---|---|---|
| DATE | EUR | %d.%m.%Y |
| DATE | INTERVAL | %Y%m%d |
| DATE | ISO | %Y-%m-%d |
| DATE | JIS | %Y-%m-%d |
| DATE | USA | %m.%d.%Y |
| TIME | EUR | %H.%i.%s |
| TIME | INTERVAL | %H%i%s |
| TIME | ISO | %H:%i:%s |
| TIME | JIS | %H:%i:%s |
| TIME | USA | %h:%i:%s %p |
| DATETIME | EUR | %Y-%m-%d %H.%i.%s |
| DATETIME | INTERVAL | %Y%m%d %H%i%s |
| DATETIME | ISO | %Y-%m-%d %H:%i:%s |
| DATETIME | JIS | %Y-%m-%d %H:%i:%s |
| DATETIME | USA | %Y-%m-%d %H.%i.%s |
SELECT DATE_FORMAT(NOW() , '%Y年%m月%d日 %H时%i分%s秒');
SELECT * FROM ucenter_member WHERE DATE_FORMAT(gmt_create , '%Y-%m-%d') = '2019-01-02';4、加密函数
列出了部分的加密函数。
| 函数 | 用法 |
|---|---|
| password(str) | 返回字符串str的加密版本，41位长的字符串<font color='red'>（mysql8不再支持）</font> |
| md5(str) | 返回字符串str的md5值，也是一种加密方式 |
| SHA(str) | 返回字符串str的sha算法加密字符串，40位十六进制值的密码字符串 |
| SHA2(str,hash_length) | 返回字符串str的sha算法加密字符串，密码字符串的长度是hash_length/4。hash_length可以是224、256、384、512、0，其中0等同于256。 |
5、系统信息函数
| 函数 | 用法 |
|---|---|
| database() | 返回当前数据库名 |
| version() | 返回当前数据库版本 |
| user() | 返回当前登录用户名 |
6、条件判断函数
| 函数 | 功能 |
|---|---|
| IF(value,t,f) | 如果value是真，返回t,否则返回f |
| IFNULL(value1,value2) | 如果value1不为空，返回value1,否则返回value2 |
| CASE WHEN 条件1 THEN result1 WHEN 条件2 THEN result2 … ELSE resultn END | 依次判断条件，哪个条件满足了，就返回对应的result,所有条件都不满足就返回ELSE的result。如果没有单独的ELSE子句，当所有WHEN后面的条件都不满足时则返回NULL值结果。等价于Java中if...else if.... |
| CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值2 THEN 值2 … ELSE 值n END | 判断表达式expr与哪个常量值匹配，找到匹配的就返回对应值，都不匹配就返回ELSE的值。如果没有单独的ELSE子句，当所有WHEN后面的常量值都不匹配时则返回NULL值结果。等价于Java中switch....case |
7、窗口函数
窗口函数也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据进行实时分析处理。窗口函数是每条记录都会分析，有几条记录执行完还是几条，因此也属于单行函数。
| 函数分类 | 函数 | 功能描述 |
|---|---|---|
| 序号函数 | ROW_NUMBER() | 顺序排序，每行按照不同的分组逐行编号，例如：1,2,3,4 |
| RANK() | 并列排序，每行按照不同的分组进行编号，同一个分组中排序字段值出现重复值时，并列排序并跳过重复序号，例如：1,1,3 | |
| DENSE_RANK() | 并列排序，每行按照不同的分组进行编号，同一个分组中排序字段值出现重复值时，并列排序不跳过重复序号，例如：1,1,2 | |
| 分布函数 | PERCENT_RANK() | 排名百分比，每行按照公式（rank-1）/ （rows-1）进行计算。其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数。返回值为在0-1之间的百分比 |
| CUME_DIST() | 累积分布值，表示分组内小于、等于当前rank值的行数 / 分组内总行数 | |
| 前后函数 | LAG（expr，n） | 返回位于当前行的前n行的expr值，行的序列需要Order By获取 |
| LEAD（expr，n） | 返回位于当前行的后n行的expr值，行的序列需要Order By获取 | |
| 首尾函数 | FIRST_VALUE（expr） | 返回当前分组第一行的expr值 |
| LAST_VALUE（expr） | 返回当前分组每一个rank最后一行的expr值 | |
| 其他函数 | NTH_VALUE（expr，n） | 返回当前分组第n行的expr值 |
| NTILE（n） | 用于将分区中的有序数据分为n个等级，记录等级数 |
窗口函数的语法格式如下
函数名([参数列表]) OVER ()
函数名([参数列表]) OVER (子句)#OVER用于给数据分组，括号中没东西表示仅一组over关键字用来指定窗口函数的窗口范围。如果OVER后面是空（），则表示SELECT语句筛选的所有行是一个窗口。OVER后面的（）中支持以下4种语法来设置窗口范围。
- WINDOW：给窗口指定一个别名；
- PARTITION BY子句：按照对应关键词分组，相同的分为一组
- ORDER BY子句：按照哪些字段进行排序，窗口函数将按照排序后结果进行分析处理；默认升序，加DESC表示降序。
- FRAME子句：FRAME是当前分区的一个子集，FRAME子句用来定义子集的规则。
#（1）在“t_employee”表中查询薪资在[8000,10000]之间的员工姓名和薪资并给每一行记录编序号
SELECT ROW_NUMBER() OVER () AS "row_num",ename,salary
FROM t_employee WHERE salary BETWEEN 8000 AND 10000;
#（2）计算每一个部门的平均薪资与全公司的平均薪资的差值。
SELECT distinct did,AVG(salary) OVER() AS avg_all,
AVG(salary) OVER(PARTITION BY did) AS avg_did,
ROUND(AVG(salary) OVER()-AVG(salary) OVER(PARTITION BY did),2) AS deviation
FROM t_employee;
#（3）在“t_employee”表中查询女员工姓名，部门编号，薪资，查询结果按照部门编号分组后在按薪资升序排列，并分别使用ROW_NUMBER（）、RANK（）、DENSE_RANK（）三个序号函数给每一行记录编序号。
SELECT ename,did,salary,gender,
ROW_NUMBER() OVER (PARTITION BY did ORDER BY salary) AS "row_num",
RANK() OVER (PARTITION BY did ORDER BY salary) AS "rank_num" ,
DENSE_RANK() OVER (PARTITION BY did ORDER BY salary) AS "ds_rank_num"
FROM t_employee WHERE gender='女';
#或
SELECT ename,did,salary,
ROW_NUMBER() OVER w AS "row_num",
RANK() OVER w AS "rank_num" ,
DENSE_RANK() OVER w AS "ds_rank_num"
FROM t_employee WHERE gender='女'
WINDOW w AS (PARTITION BY did ORDER BY salary);
#（4）在“t_employee”表中查询每个部门最低3个薪资值的女员工姓名，部门编号，薪资值。
SELECT ROW_NUMBER() OVER () AS "rn",temp.*
FROM(SELECT ename,did,salary,
ROW_NUMBER() OVER w AS "row_num",
RANK() OVER w AS "rank_num" ,
DENSE_RANK() OVER w AS "ds_rank_num"
FROM t_employee WHERE gender='女'
WINDOW w AS (PARTITION BY did ORDER BY salary))temp
WHERE temp.rank_num<=3;
#或
SELECT ROW_NUMBER() OVER () AS "rn",temp.*
FROM(SELECT ename,did,salary,
ROW_NUMBER() OVER w AS "row_num",
RANK() OVER w AS "rank_num" ,
DENSE_RANK() OVER w AS "ds_rank_num"
FROM t_employee WHERE gender='女'
WINDOW w AS (PARTITION BY did ORDER BY salary))temp
WHERE temp.ds_rank_num<=3;
#（5）在“t_employee”表中查询每个部门薪资排名前3的员工姓名，部门编号，薪资值。
SELECT temp.*
FROM(SELECT ename,did,salary,
DENSE_RANK() OVER w AS "ds_rank_num"
FROM t_employee
WINDOW w AS (PARTITION BY did ORDER BY salary DESC))temp
WHERE temp.ds_rank_num<=3;
#（6）在“t_employee”表中查询全公司薪资排名前3的员工姓名，部门编号，薪资值。
SELECT temp.*
FROM(SELECT ename,did,salary,
DENSE_RANK() OVER w AS "ds_rank_num"
FROM t_employee
WINDOW w AS (ORDER BY salary DESC))temp
WHERE temp.ds_rank_num<=3;
#（7）查询每一个部门薪资最低的员工
SELECT * FROM
(SELECT DENSE_RANK() over (PARTITION BY did ORDER BY salary) r3,
did,salary,eid,ename
FROM t_employee) temp
WHERE r3=1;MySQL架构
逻辑架构
MySQL5.7使用的经典架构图，MySQL 8中去掉了Caches&Buffers部分
连接层
最上层是一些客户端和连接服务，包含本地socket通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
服务层
a) Management Serveices & Utilities： 系统管理和控制工具
b) SQL Interface: SQL接口
接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface
c) Parser: 解析器
SQL命令传递到解析器的时候会被解析器验证和解析。解析器中SQL 语句进行词法分析、语法分析、语义分析，并为其创建语法树。
d) Optimizer: 查询优化器。
SQL语句在查询之前会使用查询优化器对查询进行优化。
用一个例子就可以理解： select uid,name from user where gender= 1;
优化器来决定先投影还是先过滤。
这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤
这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤
e) Cache和Buffer： 查询缓存。
如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。
这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等
mysql8禁用记录缓存(查询缓存，数据库表一旦修改数据了查询缓存需要删除)
引擎层
存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。后面介绍MyISAM和InnoDB
存储层
数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。
查询流程
- mysql客户端通过协议与mysql服务器建连接，发送查询语句，先检查查询缓存，如果命中，直接返回结果，否则进行语句解析,
- 语法解析器和预处理：首先mysql通过关键字将SQL语句进行解析，并生成一颗对应的“解析树”。<font color='red'>mysql解析器将使用mysql语法规则验证和解析查询</font>；预处理器则根据一些mysql规则进一步检查解析树是否合法。
- 当解析树被认为是合法的了，<font color='red'>查询优化器将其转化成执行计划</font>。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。
SQL执行计划
profiling性能分析工具，一般用来查询sql执行过程每个阶段占用的时间
-- 查看profiling状态
SHOW VARIABLES LIKE '%profiling%';
-- 开启profiling工具： 生产环境一般关闭(它会保存大量的查询sql执行的日志)
SET profiling=1;
-- 执行多个查询sql生成profiles数据
SELECT * FROM t_emp;
SELECT * FROM t_dept;
-- 查询性能分析工具保存的最近查询的 执行日志
SHOW PROFILES;
-- queryId代表一个sql操作的 id值
-- cpu表示显示cpu相关的日志、block io显示磁盘操作相关日志
SHOW PROFILE cpu,block io FOR QUERY 214;常用参数
show profile的常用查询参数。
ALL：显示所有的开销信息。
BLOCK IO：显示块IO开销。
CONTEXT SWITCHES：上下文切换开销。
CPU：显示CPU开销信息。
IPC：显示发送和接收开销信息。
MEMORY：显示内存开销信息。
PAGE FAULTS：显示页面错误开销信息。
SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息。
SWAPS：显示交换次数开销信息。sql执行顺序
Executing hook on transaction: 启用事务
checking permissions：检查权限
Opening tables：打开表
init ： 初始化
System lock ：系统锁
optimizing ： 优化sql
statistics ： 统计
preparing ：准备执行
executing ：执行sql
Sending data ：发送数据
Sorting result ：排序
end ：结束
query end ：查询 结束
closing tables ： 关闭表 ／去除TMP 表
freeing items ： 释放物品
cleaning up ：清理数据引擎
InnoDB存储引擎
InnoDB是MySQL的默认事务型引擎，它被设计用来<font color='red'>处理大量的短期(short-lived)事务</font>。除非有非常特别的原因需要使用其他的存储引擎，否则<font color='red'>应该优先考虑InnoDB引擎。</font>
MyISAM存储引擎
MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但<font color='red'>MyISAM不支持事务和行级锁</font>，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。
Archive引擎
<font color='red'>Archive档案存储引擎只支持INSERT和SELECT操作</font>，在MySQL5.1之前不支持索引。
Archive表适合日志和数据采集类应用。
根据英文的测试结论来看，Archive表比MyISAM表要小大约75%，比支持事务处理的InnoDB表小大约83%。
Blackhole引擎
<font color='red'>Blackhole引擎没有实现任何存储机制，它会丢弃所有插入的数据，不做任何保存。</font>但服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者简单地记录到日志。但这种应用方式会碰到很多问题，因此并不推荐。
CSV引擎
<font color='red'>CSV引擎可以将普通的CSV文件作为MySQL的表来处理，但不支持索引。</font>
CSV引擎可以作为一种数据交换的机制，非常有用。
CSV存储的数据直接可以在操作系统里，用文本编辑器，或者excel读取。
Memory引擎
如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory表是非常有用。Memory表至少比MyISAM表要快一个数量级。
Federated引擎
Federated引擎<font color='red'>是访问其他MySQL服务器的一个代理</font>，尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。
| 对比项 | MyISAM | InnoDB |
|---|---|---|
| 外键 | 不支持 | 支持 |
| 事务 | 不支持 | 支持 |
| 行表锁 | 表锁，即使操作一条记录也会锁住整个表 <font color='red'>不适合高并发的操作 </font> | 行锁,操作时只锁某一行，不对其它行有影响 <font color='red'>适合高并发的操作 </font> |
| 缓存 | 只缓存索引，不缓存真实数据 | 不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响 |
| 关注点 | 节省资源、消耗少、简单业务 | 并发写、事务、更大资源 |
| 默认安装 | Y | Y |
| 用户表默认使用 | N | Y |
| 自带系统表使用 | Y | N |
索引概述
数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。索引往往以索引文件的形式存储的磁盘上。mysql内存足够时 会将索引结构加载到内存中。创建索引后，索引不一定会生效，优化器会基于开销选择索引，怎么开销小就怎么来。不是基于规则，也不是基于语义。
优势：
类似大学图书馆建书目索引，<font color='red'>提高数据检索的效率</font>，降低数据库的IO成本。
通过索引列对数据进行排序或分组，<font color='red'>降低数据排序的成本</font>，降低了CPU的消耗。
劣势：
虽然索引大大提高了查询速度，同时却会<font color='red'>降低更新表的速度</font>，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以<font color='red'>索引列也是要占用空间的</font>
BTree索引
B-Tree即B树，Balance Tree，平衡树,它的高度远小于平衡二叉树的高度。B树的阶：节点的最多子节点个数。比如2-3树的阶是3，2-3-4树的阶是4。
【初始化介绍】 一颗b树，浅蓝色的块我们称之为一个磁盘块(innodb默认16kb一个磁盘块)，可以看到每个磁盘块包含几个<font color='red'>数据项（深蓝色所示）、指向关键字具体信息的指针（红色）和指向其他磁盘块的指针（黄色所示）</font> P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。
【查找过程】 如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。
真实的情况是，3层的b树可以表示上百万的数据，如果上百万的数据查找<font color='red'>只需要三次IO</font>，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。
B+Tree索引
数据结构
B+Tree与B-Tree 的区别
1）B-树的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息；<font color='red'>B+树的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中。树的高度会更矮胖，IO次数也会更少。</font> 2）在B-树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在；而B+树中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。从这个角度看B-树的性能好像要比B+树好，而在实际应用中却是B+树的性能要好些。因为B+树的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比B-树多，树高比B-树小，这样带来的好处是减少磁盘访问次数。尽管B+树找到一个记录所需的比较次数要比B-树多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间，因此实际中B+树的性能可能还会好些，而且B+树的叶子节点使用指针连接在一起，方便顺序遍历（例如查看一个目录下的所有文件，一个表中的所有记录等），这也是很多数据库和文件系统使用B+树的缘故。 思考：为什么说B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引？
- B+树的磁盘读写代价更低 B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。
- B+树的查询效率更加稳定 由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。
数据页大小
查看mysql文件页大小（16K）：
-- 查看方法
SHOW GLOBAL STATUS LIKE '%page_size%'
-- 假设我们一行数据大小为1K，那么一页就能存16条数据，也就是一个叶子节点能存16条数据；再看非叶子节点，假设主键ID为bigint类型，那么长度为8B，指针大小在Innodb源码中为6B，一共就是14B，那么一页里就可以存储16K/14B=1170个(主键+指针)
-- 一颗高度为2的B+树能存储的数据为:1170*16=18720条，
-- 一颗高度为3的B+树能存储的数据为：1170*1170*16=21902400（千万级）聚簇索引与非聚簇索引
聚簇索引：
将数据存储与索引放到了一块，找到索引也就找到了数据。
页内的记录是按照主键的大小顺序排成一个单向链表。
页和页之间也是根据页中记录的主键的大小顺序排成一个双向链表。
非叶子节点存储的是记录的
主键+页号。叶子节点存储的是完整的用户记录。
非聚簇索引：
将数据存储与索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因
<font color='red'>innodb中，非聚簇索引又称辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。</font>
InnoDB使用聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。
若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）
聚簇索引的好处：
由于行数据和叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中，再次访问的时候，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。
聚簇索引适合用在排序的场合，非聚簇索引不适合
取出一定范围数据的时候，使用聚簇索引
二级索引需要两次索引查找，而不是一次才能取到数据，因为存储引擎第一次需要通过二级索引找到索引的叶子节点，从而找到数据的主键，然后在聚簇索引中用主键再次查找索引，再找到数据
聚簇索引的限制：
- 对于mysql数据库目前只有innodb数据引擎支持聚簇索引，而Myisam并不支持聚簇索引。
- 由于数据物理存储排序方式只能有一种，所以每个Mysql的表只能有一个聚簇索引。
- 一般情况下就是该表的主键。
- 如果没有primary key,会以(not null unique key)非空的唯一索引保存数据
- 内部自己生成一个字段保存数据
- 为了充分利用聚簇索引的聚簇的特性，所以innodb表的主键列尽量选用有序非空的字段，而不建议用无序的id，比如uuid这种。
联合索引
基于多个字段创建的索引就是联合索引，也称为复合索引，比如我们创建索引create index idx on table(a,b,c) 我们称在字段a,b,c上创建了一个联合索引。同时以这三个列的大小作为排序规则。
- 记录先按照a列排序
- a列值相同时使用b列排序
- b列值相同时使用c列排序
然后将排好序的abc三列的值组织到非聚簇索引索引结构中。
联合索引结构：
索引类型
- 主键索引：设定为主键后数据库会自动建立索引，innodb为聚簇索引
- 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引
- 唯一索引：索引列的值必须唯一，但允许有空值
- 复合索引：即一个索引包含多个列
CREATE TABLE customer (
id INT (10) UNSIGNED AUTO_INCREMENT,
customer_no VARCHAR (200),
customer_name VARCHAR (200),
PRIMARY KEY (id), # 主键索引
KEY idx_name (customer_name), # 单值索引
UNIQUE KEY uk_name (customer_name), # 唯一索引
KEY idx_no_name (customer_no, customer_name) # 复合索引
);单独建索引：
# 使用CREATE语句：CREATE [UNIQUE ] INDEX [indexName] ON table_name(column))
CREATE INDEX idx_customer_name ON customer(customer_name); # 单值索引
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no); # 唯一索引
CREATE INDEX idx_no_name ON customer(customer_no,customer_name); # 复合索引
# 使用ALTER命令：
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list);
# 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list);
# 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list);
# 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list);
# 该语句指定了索引为 FULLTEXT ，用于全文索引。删除索引：DROP INDEX [indexName] ON mytable;
ALTER TABLE customer drop PRIMARY KEY; # 删除主键索引
DROP INDEX idx_customer_name on customer; # 删除单值、唯一、复合索引查看索引：SHOW INDEX FROM table_name\G;
修改主键索引：必须先删除掉(drop)原索引，再新建(add)索引
索引创建情况
哪些情况需要创建索引：
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其它表关联的字段，外键关系建立索引
- 单键/组合索引的选择问题， 组合索引性价比更高
- 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
- 查询中统计或者分组字段
哪些情况不要创建索引：
表记录太少
经常增删改的表或者字段。（提高了查询速度，同时却会降低更新表的速度）
Where条件里用不到的字段不创建索引
过滤性不好的不适合建索引
有大量重复数据的列上。
索引优化
性能分析（explain）
执行sql前添加explain：explain select * from t1, t2, t3 where t1.id=t2.id and t2.id=t3.id;
id
select查询的序列号，表示查询中执行select子句或操作表的顺序。<font color='red'>每个id号码，表示一趟独立的查询。一个sql 的查询趟数越少越好。</font>
- id相同，执行顺序由上至下。例如上图
- id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
- id为NULL最后执行。
select_type
查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询
SIMPLE | 简单的 SELECT(单表查询) |
|---|---|
PRIMARY | 最外层的Select 作为primary 查询。(PS:含有子查询的情况,但是并不复杂) |
DERIVED | 在from 查询语句中的（派生，嵌套很多）子查询.(PS:递归操作这些子查询) |
SUBQUERY | 在SELECT或WHERE列表中包含了子查询。 |
DEPENDENT SUBQUERY | 第一个查询是子查询，依赖于外部查询（相关查询）。 |
MATERIALIZED | 在非相关子查询中 并且需要进行物化时会出现MATERIALIZED关键词。 |
UNCACHEABLE SUBQUERY | 子查询结果(系统变量)不能被缓存， 而且必须重写（分析）外部查询的每一行。 |
UNION | 若第二个SELECT出现在UNION之后，则被标记为UNION。若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED |
UNION RESULT | 结果集是通过union 而来的。 |
table
显示这一行的数据是关于哪张表的
partitions
代表分区表中的命中情况，非分区表，该项为null
type(优化指标)
type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：
null>system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > <font color='#FFD700'>range</font> ><font color="orange"> index</font> > <font color="red">ALL </font>
常见：system > const > eq_ref > ref > <font color='#FFD700'>range</font> ><font color="orange"> index</font> > <font color="red">ALL </font>
一般来说，得保证查询至少达到range级别，最好能达到ref。
null: MySQL不访问任何表或索引，直接返回结果
system：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计。
const：表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。（select * from t1 where t1.id=1）
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。（select * from t1,t2 where t1.id=t2.id）
ref: 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
range：只检索给定范围的行，使用一个索引来选择行。key 列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询。这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。（select * from t1 where t1.id<10）
index：出现index是sql使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组。（explain select id from t1）
all：Full Table Scan，将遍历全表以找到匹配的行。（explain select * from t1）
index_merge：在查询过程中需要多个索引组合使用，通常出现在有 or 的关键字的sql中。
ref_or_null：对于某个字段既需要关联条件，也需要null值得情况下。查询优化器会选择用ref_or_null连接查询。
index_subquery：利用索引来关联子查询，不再全表扫描。
unique_subquery：该联接类型类似于index_subquery。 子查询中的唯一索引。
possible_keys
显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
key(优化指标)
实际使用的索引。如果为NULL，则没有使用索引。
key_len(分析指标)
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。 key_len字段能够帮你检查是否充分的利用上了索引。
1 、先看索引上字段的类型长度，比如 int=4 ; varchar(20) =20 ; char(20) =20
2 、如果是varchar或者char这种字符串字段，视字符集要乘不同的值，比如utf8mb3要乘 3(MySQL5.7)，如果是utf8mb4要乘4，,GBK要乘2
3 、varchar这种动态字符串要加2个字节
4、 允许为空的字段要加1个字节
索引字段最好不要为NULL，因为NULL让统计更加复杂，并且需要额外一个字节的存储空间。
ref
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
rows
rows列显示MySQL认为它执行查询时必须检查的行数。越少越好
filtered
这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数，值越大越好。
extra(分析指标)
包含不适合在其他列中显示但十分重要的额外信息，通过这些额外信息来理解MySQL到底将如何执行当前的查询语句。
<font color="red">Using filesort</font>
- 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。即无法利用索引完成（该排序操作称为“文件排序”），这类SQL语句性能极差，需要进行优化。
- 在一个非索引列上进行了order by，就会触发filesort，常见的优化方案是，在order by的列上添加索引，避免每次查询都全量排序(只查询索引列的值)。
<font color="red">Using temporary</font>
- 使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。group by和order by同时存在，且作用于不同的字段时，就会建立临时表，以便计算出最终的结果集。
USING index
- 利用索引进行了排序或分组。表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！（EXPLAIN select * from t_emp where age=30 ORDER BY name）
- 如果同时出现using where，表明索引被用来执行索引键值的查找;
- 如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。
Using where
- 表明使用了where过滤
<font color="red">using join buffer</font>
- 使用了连接缓存，非主键关联(mysql8
Using join buffer (hash join)速度要好于 mysql5.7Using join buffer (Block Nested Loop))
impossible where
- where子句的值总是false，不能用来获取任何元组。（EXPLAIN select * from t_emp where false;）
select tables optimized away
- 在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者
- 对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
Json格式的执行计划
以JSON格式输出执行计划：EXPLAIN FORMAT=json SELECT * FROM t_emp;
优化原则
单表优化
单索引优化
- 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
- like以通配符开头('%abc...')，mysql索引失效会变成全表扫描的操作
- mysql 在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描
- is not null 也无法使用索引，但是is null是可以使用索引的
- 字符串不加单引号索引失效
-- 创建索引，查找姓名以"abc"开头的员工信息
create index idx_emp_age on emp(age);
create index idx_emp_name on emp(name);
-- 快，只要不以%通配符开头，就会走索引
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name LIKE 'abc%';
-- 慢，对索引字段进行了操作，不走索引
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE LEFT(emp.name,3)='abc';组合索引原则
查询的所有条件都与索引相匹配，即全值匹配，此时最优。
符合最左原则：不跳过索引中的列。
sqlcreate index idx_age_deptId_name on emp(age, deptId, name); -- 索引生效 SELECT * FROM emp WHERE age=30 and deptId=1 and name='abc'; -- 跳过了age，索引失效 SELECT * FROM emp WHERE deptId=1 and name='abc';如果where条件中是OR关系，加索引不起作用
存储引擎不能使用索引中范围条件右边的列
sql-- deptId右边的索引无法使用，仅age、deptId索引生效 SELECT * FROM emp WHERE age=30 and deptId>1 and name='abc';
总结
- 对于单键索引，尽量选择针对当前query过滤性更好的索引
- 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
- 在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
- 在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面
- 书写sql语句时，尽量避免造成索引失效的情况。
假设index(a,b,c)
| Where语句 | 索引是否被使用 |
|---|---|
| where a = 3 | Y,使用到a |
| where a = 3 and b = 5 | Y,使用到a，b |
| where a = 3 and b = 5 and c = 4 | Y,使用到a,b,c |
| where b = 3 或者 where b = 3 and c = 4 或者 where c = 4 | N |
| where a = 3 and c = 5 | 使用到a， 但是c不可以，b中间断了 |
| where a = 3 and b > 4 and c = 5 | 使用到a和b， c不能用在范围之后，b断了 |
| where a is null and b is not null | is null 支持索引 is not null 类似范围查询，ab能使用，b右边的会失效 |
| where a <> 3 | 不能使用索引 |
| where abs(a) =3 | 不能使用索引 |
| where a = 3 and b like 'kk%' and c = 4 | Y,使用到a,b,c |
| where a = 3 and b like '%kk' and c = 4 | Y,只用到a |
| where a = 3 and b like '%kk%' and c = 4 | Y,只用到a |
| where a = 3 and b like 'k%kk%' and c = 4 | Y,使用到a,b,c |
关联查询优化
- 为外连接查询被驱动表的连接字段创建索引即可
- left/right join 时，选择小表作为驱动表，大表作为被驱动表。
- inner join 时，mysql会自己帮你把小结果集的表选为驱动表,对被驱动表连接字段创建索引。
- 子查询尽量不要放在被驱动表，有可能使用不到索引。
- 能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数)
子查询优化
尽量不要使用not in 或者 not exists
尽量不要使用子查询
排序优化
以下三种情况不走索引：
无过滤，不索引
sql-- 排序时，必须过滤数据才可以走索引查询 -- limit 过滤数据量较少时，会走二级索引查询主键值最后通过主键索引查询行数据 -- limit 查询行数较多时，优化器认为回表查询性能较差，直接读表数据 EXPLAIN SELECT * FROM emp ORDER BY age,deptid LIMIT 0,1400; -- where条件过滤: 过滤条件的列和后面的排序字段需要满足最左原则和复合索引原则 EXPLAIN SELECT * FROM emp WHERE age = 20 ORDER BY deptid,NAME;顺序错，不索引
sql-- 最左原则，条件字段和排序字段要和复合索引列顺序一致 CREATE INDEX idx_age_deptid_name ON emp (age,deptid,NAME) EXPLAIN SELECT * FROM emp WHERE age = 20 ORDER BY `name`,deptid;方向反，不索引
sql-- 排序的多个字段的排序顺序需要一样,如果deptid升序，name降序，则索引不会生效 EXPLAIN SELECT * FROM emp WHERE age = 20 ORDER BY deptid ,`name` DESC;
以后创建索引的方式有多种选择：过滤条件列索引和排序列索引的选择
- 所有的排序都是在条件过滤之后才执行的，所以如果条件过滤了大部分数据的话，几百几千条数据进行排序其实并不是很消耗性能，即使索引优化了排序但实际提升性能很有限。
- 如果条件过滤掉的数据足够多，优先对条件列创建索引（数据少的时候就算是filesort排序 性能依然高）
- 如果条件过滤的数据不多，剩余数据量大，分析条件列索引和排序列索引查询的性能哪个高用哪个（ 数据量非常大的时候优先使用 排序列创建索引）
filesort算法
如果不在索引列上，filesort有两种算法：双路排序和单路排序
双路排序(mysql4.1前)
- 读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。
单路排序(mysql4.1后)
从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。
单路排序是把所有字段都取出, 所以更有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，多次IO读取排序。
解决策略
- select 只查询需要的字段
- 尝试提高 sort_buffer_size
- 尝试提高 max_length_for_sort_data（提高这个参数， 会增加用改进算法的概率）
分组优化
group by 使用索引的原则几乎跟order by一致 ，唯一区别是groupby 即使没有过滤条件用到索引，也可以直接使用索引。
group by 先排序再分组，遵照索引建的最佳左前缀法则
当无法使用索引列，增大max_length_for_sort_data和sort_buffer_size参数的设置
where高于having, 能写在where限定的条件就不要写在having中了
只要对分组列创建索引即可
覆盖索引
select 到 from 之间查询的列 <=使用的索引列+主键，即从索引树上就可以读到所有数据，但是优化效率并不高
强制索引
回表，非聚簇索引排序后 得到的主键id值集合是乱序的，然后到聚簇索引中随机io读取行数据。所以强制索引后，消耗的时间可能更久。
CREATE INDEX idx_age_deptid_name ON emp (age,deptid,NAME)
-- 1.686:
EXPLAIN SELECT * FROM emp FORCE INDEX (idx_age_deptid_name) ORDER BY age,deptid; 指定索引长度
在 varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。
-- address长度为10,当截取到5的时候查询区分度高达0.9572(dept表是随机数据 根据自己的情况判断)
SELECT COUNT(DISTINCT LEFT(address,5)) / COUNT(*) FROM dept;
-- 创建address列的索引并指定长度为5(address可以为空 varchar类型，字节数为：5*3+3 = 18)
ALTER TABLE dept ADD INDEX idx_address(address(5));
-- 可以看到address使用的索引长度为18
EXPLAIN SELECT * FROM dept WHERE address IS NULL;SQL实战
行转列
SELECT stuid ,
-- NULL值在数学运算中被视为不存在，实际上计算的是所有SUBJECT为'Java基础'的score值的总和。
-- IF的作用是如果SUBJECT列的值等于'Java基础'，那么就将该行的score值包括在内
SUM(IF(SUBJECT = 'Java基础' , score , NULL)) 'Java基础',
SUM(IF(SUBJECT = 'mysql' , score , NULL)) 'mysql',
SUM(IF(SUBJECT = 'Javaweb' , score , NULL)) 'Javaweb',
SUM(IF(SUBJECT = 'ssm' , score , NULL)) 'ssm'
FROM t_score
GROUP BY stuid;删除重复行
每个学生同一学科有多个成绩的，保留分数高的
# 方式1： 查询学生每个学科最高成绩的id集合，删除该id集合以外的其他数据
DELETE FROM t_score WHERE id NOT IN(
-- 将数据储存到临时表中，确保只有内部查询完后，才会在外部开始查询
SELECT tmp.id FROM
-- 找出满足条件的id
(SELECT id FROM t_score t1 JOIN
-- 先分组查询出，同一个人，同一个学科的最高分
(SELECT stuid , SUBJECT , MAX(score) m_score
FROM t_score
GROUP BY stuid , SUBJECT) t2
ON t1.`stuid` = t2.stuid
AND t1.`subject` = t2.subject
AND t1.`score` = t2.m_score)tmp
);
# 方式2： 按照stuid排序，然后subject排序，最后按照score降序排列
SET @stuid:=0;
SET @subject:='';
SET @rank:= 1;
DELETE FROM t_score WHERE id IN(
SELECT id
FROM(
SELECT * , IF(@stuid = stuid , IF(@subject = SUBJECT , @rank:=@rank+1 ,@rank:=1) , @rank:=1) 'rank',
@stuid:=stuid , @subject:=SUBJECT
FROM t_score
ORDER BY stuid , SUBJECT ,score DESC) tmp
WHERE tmp.rank !=1);视图
视图只保存了SQL查询语句，不会保存任何查询结果。修改视图数据，本质就是修改其对应的基础表，所以只有包含的SQL语句相对简单，并且直接映射到一个基础表上，才可以修改。
语句复用、简化复杂查询、增强数据独立性、权限控制
#创建视图（WITH CHECK OPTION：限制用户只能插入符合视图要求的数据）
CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition WITH CHECK OPTION
#查询
select * from view_name
#更新
CREATE OR REPLACE VIEW view_name
AS SELECT column_name(s) FROM table_name WHERE condition
#修改数据
UPDATE v_students_computer SET age = 20;
#删除
DROP VIEW view_name;Mysql锁
mysql支持读锁和写锁(都属于悲观锁)
读锁（共享锁，S锁(Shared)）：针对同一份数据，多个读操作可以同时进行而不会互相影响
写锁（排它锁，X锁(eXclusive)）：当前写操作没有完成前，它会阻断其他写锁和读锁
从对数据操作的粒度分，分为表锁和行锁
表锁
每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；一般用在整表数据迁移的场景。
-- 加锁
lock table 表1 read(write),表2 read(write);
-- 查看表锁
show open tables;
-- 删除本次session的表锁
unlock tables;行锁
每次操作锁住一行数据，锁定粒度最小，发生锁冲突的概率最低，并发能力强。但是开销大、加锁慢、可能会出现死锁。InnoDB存储引擎在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁。
查看行锁：
show status like 'innodb_row_lock%';
-- 各个状态量的说明
Innodb_row_lock_current_waits: 当前正在等待锁定的数量
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg: 每次等待所花平均时间(*)
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间Innodb_row_lock_waits:系统启动后到现在总共等待的次数(*)
Innodb_row_lock_waits: 等待总次数(*)测试行锁：
-- for update会给当前sql查询出的行上锁，该锁是排他可重入的
-- Session1和Session2的语句都分别在各自的事务中执行，当提交后，就会出现死锁现象
-- Session_1执行
select * from account where id=1 for update;
-- Session_2执行
select * from account where id=2 for update;
-- Session_1执行
select * from account where id=2 for update;
-- Session_2执行
select * from account where id=1 for update;
-- 查看近期死锁日志信息：
show engine innodb status\G;