Mysql数据库
约 11694 字大约 39 分钟
1.基础概念
SQL概念
结构化查询语言(Structured Query Language)简称SQL，结构化查询语言是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；
sql 语句就是对数据库进行操作的一种语言。SQL注释
## 注释1
-- 注释2
/* 注释3 */SQL语法
SQL 语句不区分大小写。
多条 SQL 语句必须以分号（;）分隔。
处理 SQL 语句时，所有空格都被忽略。SQL约束
SQL 约束用于规定表中的数据规则。如果存在违反约束的数据行为，行为会被约束终止。
约束类型：
NOT NULL - 指示某列不能存储 NULL 值。
UNIQUE - 保证某列的每行必须有唯一的值。
PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
DEFAULT - 规定没有给列赋值时的默认值。
CHECK - 保证列中的值符合指定的条件。(正则表达式、验证数据的合法性。)
约束注意：
DEFAULT注意：empty string和NULL区别
UNIQUE注意：可以是多个NULL
PRIMARY KEY注意：使用递增或其他算法自动生成(雪花算法、UUID等)。
FOREIGN KEY注意：多的一方添加公共列、并设置此公共列为外键。主键外键
主键和外键有什么区别?
主键(主码)：主键用于唯一标识一个元组，不能有重复，不允许为空。一个表只能有一个主键。
外键(外码)：外键用来和其他表建立联系用，外键是另一表的主键，外键是可以有重复的，可以是空值。一个表可以有多个外键。
为什么不推荐使用外键与级联？
对于外键和级联，阿里巴巴开发手册这样说到：【强制】不得使用外键与级联，一切外键概念必须在应用层解决。说明: 以学生和成绩的关系为例，学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群; 级联更新是强阻塞，存在数据库更新风暴的风险; 外键影响数据库的插入速度数据类型
三范式
数据库范式有 3 种：
1NF(第一范式)：属性不可再分。
2NF(第二范式)：1NF 的基础之上，消除了非主属性对于主码的【复核主键中部分函数依赖】。
3NF(第三范式)：3NF 在 2NF 的基础之上，消除了非主属性对于主码的【传递函数依赖】。
E-R图
ER 图 全称是 Entity Relationship Diagram（实体联系图），提供了表示实体类型、属性和联系的方法。
ER 图由下面 3 个要素组成：
实体：通常是现实世界的业务对象。在 ER 图中，实体使用矩形框表示。
属性：某个实体拥有的属性。在 ER 图中，属性使用椭圆形表示。
联系：实体与实体之间的关系。有多对多（M: N）、1 对 1（1:1）、1 对多（1: N）。
日志
错误日志（error log）
error log主要记录MySQL在启动、关闭或者运行过程中的错误信息，在MySQL的配置文件my.cnf中，可以通过log-error=/var/log/mysqld.log 执行mysql错误日志的位置。
慢查询日志（slow query log）
MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
long_query_time的默认值为10，意思是运行10秒以上的语句。
由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析。
默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。
一般查询日志（general log）
general log 记录了客户端连接信息以及执行的SQL语句信息，通过MySQL的命令
回滚日志（undo log）
undo log主要用来回滚到某一个版本，是一种逻辑日志。
undo log记录的是修改之前的数据，比如：当delete一条记录时，undolog中会记录一条对应的insert记录，从而保证能恢复到数据修改之前。在执行事务回滚的时候，就可以通过undo log中的记录内容并以此进行回滚。
undo log还可以提供多版本并发控制下的读取（MVCC）。
二进制日志（bin log）
MySQL的bin log日志是用来记录MySQL中增删改时的记录日志。
当你的一条sql操作对数据库中的内容进行了更新，就会增加一条bin log日志。查询操作不会记录到bin log中。
bin log最大的用处就是进行主从复制，以及数据库的恢复。
重写日志（redo log）
redo log是一种基于磁盘的数据结构，用来在MySQL宕机情况下将不完整的事务执行数据纠正，redo日志记录事务执行后的状态。
当事务开始后，redo log就开始产生，并且随着事务的执行不断写入redo log file中。redo log file中记录了xxx页做了xx修改的信息，我们都知道数据库的更新操作会在内存中先执行，最后刷入磁盘。
redo log就是为了恢复更新了内存但是由于宕机等原因没有刷入磁盘中的那部分数据。2.SQL语句
SQL分类
SQL语句是数据库操作必备的语句，我们把它主要分为：DDL、DML、DCL、DQL、TCL。
DDL(Data Definition Language)：数据库定义语言，用于创建、修改和删除数据库、表以及其他数据库对象的语言。DDL包括创建、修改和删除数据库、表、索引、视图、触发器等操作。常见的DDL命令有CREATE、ALTER和DROP。
DML(Data Manipulation Language)：数据库操作语言，用于对数据库中的数据进行操作的语言。DML允许插入、更新、删除和查询数据记录。常见的DML命令有INSERT、UPDATE、DELETE和SELECT。
DQL(Data Query Language)：数据查询语言，用于从数据库中检索数据的语言。DQL主要用于查询和获取保存在数据库中的数据。常见的DQL命令是SELECT。
DCL(Data Control Language)：用于控制数据库用户访问权限和安全性的语言。DCL包括授予和撤销用户权限的命令，以及管理数据库对象的权限。常见的DCL命令有GRANT和REVOKE。
TCL(Transaction Control Language)：事务管理语言，用于管理事务的语言。TCL包括开始事务、提交事务、回滚事务等命令。常见的TCL命令有BEGIN、COMMIT和ROLLBACK。DDL
常用的DDL(数据定义语言)的操作
Data Definition Language，数据定义语言，用来定义数据库，表，字段。
创建数据库
CREATE DATABASE database_name;
删除数据库
DROP DATABASE database_name;
切换数据库
USE database_name;
创建表
CREATE TABLE table_name (
column1 datatype1 constraints,
column2 datatype2 constraints,
...
);
修改表结构(添加列)
ALTER TABLE table_name ADD COLUMN column_name datatype constraints;
修改表结构(修改列)
ALTER TABLE table_name MODIFY COLUMN column_name new_datatype constraints;
修改表结构(删除列)
ALTER TABLE table_name DROP COLUMN column_name;
修改表名
ALTER TABLE table_name RENAME TO new_table_name;
删除表
DROP TABLE table_name;
添加主键约束
ALTER TABLE table_name ADD PRIMARY KEY (column1, column2, ...);
添加外键约束
ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES referenced_table(reference_column);
删除外键约束
ALTER TABLE table_name DROP FOREIGN KEY constraint_name;
创建索引
CREATE INDEX index_name ON table_name (column1, column2, ...);
删除索引
DROP INDEX index_name ON table_name;DCL
常用DCL(数据控制语言)的操作
DCL(Data Control Language)：数据控制语言，创建数据库用户，控制数据库的访问权限。
授权
GRANT privileges ON database_name.table_name TO user@host;
撤销授权
REVOKE privileges ON database_name.table_name FROM user@host;
创建用户
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
删除用户
DROP USER 'username'@'localhost';
刷新权限
FLUSH PRIVILEGES;TCL
START TRANSACTION - 指令用于标记事务的起始点。
SAVEPOINT - 指令用于创建保留点。
ROLLBACK TO - 指令用于回滚到指定的保留点；如果没有设置保留点，则回退到 START TRANSACTION 语句处。
COMMIT - 提交事务。
START TRANSACTION;
INSERT INTO com(name) VALUES('IBM');
SAVEPOINT point1;
INSERT INTO com(name) VALUES('HP');
SAVEPOINT point2;
INSERT INTO com(name) VALUES('DELL');
COMMIT; 或 ROLLBACK TO point2; 或 ROLLBACK;DML(insert)
全部字段添加
注意：添加数据的个数、顺序、类型必须和表的结构一致。
INSERT INTO person VALUES(1,'a',100,'2023-10-10','男');
全部字段批量添加
INSERT INTO person VALUES(2,'b',200,'2023-11-11','女'),(3,'c',300,'2023-12-12','男');
部分字段添加
注意：添加数据的个数、顺序、类型必须和指定字段一致。
INSERT INTO person(name) VALUES('张三');
部分字段批量添加
INSERT INTO person(name) VALUES('李四'),('王五');
添加查询结果
INSERT INTO com(name) SELECT name FROM com;DML(update)
注意：通常和where条件语句一起使用。否则表中所有记录都会被修改。
UPDATE person SET salary = salary + 100;
UPDATE person SET name='张三', salary=8888 WHERE id = 21;DML(delete)
注意：通常和where条件语句一起使用。否则表中所有记录都会被删除。
DELETE FROM person;
DELETE FROM person WHERE id = 21;
清空表：慢、支持事务回滚、有日志、自动递增继续递增
DELETE FROM person;
截断表：快、不支持事务回滚、无日志、索引重新设置
TRUNCATE TABLE person;DQL(select)
常用DQL(数据查询语言)的操作
DQL(Data Query Language)：数据查询语言，查询数据库中表的记录。
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
(1)SELECT查询字段
SELECT * FROM person;
SELECT name, salary FROM person;
SELECT name n, salary s FROM person;
SELECT salary, (salary+100) s FROM person;
SELECT db01.person.name FROM person;
(2)FROM查询表
注意：如果给表起别名、在通过表名引用字段的时候就必须使用别名。
SELECT * FROM person;
SELECT person.name FROM person;
SELECT t.name FROM person t;
(3)WHERE查询条件
比较运算符：6个
SELECT * FROM person WHERE salary > 3000;
SELECT * FROM person WHERE salary >= 3000;
SELECT * FROM person WHERE salary < 3000;
SELECT * FROM person WHERE salary <= 3000;
SELECT * FROM person WHERE salary = 3000;
SELECT * FROM person WHERE salary != 3000;
逻辑运算符：3个
AND逻辑与：表示并且的意思、多个条件都满足。
SELECT * FROM person WHERE salary > 3000 AND salary < 5000 AND sex='男';
OR逻辑或：表示或者的意思、多个条件满足一个即可。
SELECT * FROM person WHERE salary = 3100 OR salary = 4100 OR salary=8100;
NOT逻辑非：表示取反、需要和其它运算符一起使用。
特殊运算符：4个
BETWEEN AND用于数字或日期区间的查询
SELECT * FROM person WHERE salary BETWEEN 3100 AND 8100;
SELECT * FROM person WHERE salary NOT BETWEEN 3100 AND 8100;
SELECT * FROM person WHERE salary >= 3100 AND salary <= 8100
IN用于集合：in (数据1,数据2)
SELECT * FROM person WHERE name in ('张三', '李四');
SELECT * FROM person WHERE name NOT in ('张三', '李四');
SELECT * FROM person WHERE name = '张三' OR name = '李四';
LIKE用于字符或日期的模糊查询、必须和匹配符%或_一起使用才有效。
%匹配【零个或多个】任意字符。
_匹配【一个】任意字符。
SELECT * FROM person WHERE name LIKE '张%';
SELECT * FROM person WHERE name LIKE '%张';
SELECT * FROM person WHERE name LIKE '%张%';
SELECT * FROM person WHERE name LIKE '_a__';
SELECT * FROM person WHERE name NOT LIKE '_a__';
NULL空、表示未知、不知道、必须和is或is not连用查询。
INSERT INTO person(name) VALUES(NULL);
SELECT * FROM person WHERE name is null;
SELECT * FROM person WHERE name is NOT null;
(4)GROUP BY分组查询
理解：相同的是一组。显示的组信息、相同有几组最终就显示几条记录。
注意：如果按照某个字段分组、select后边只能写分组的组名或聚合函数。
SELECT sex, max(salary) ,min(salary) ,count(*) ,sum(salary) ,avg(salary)
FROM person
GROUP BY sex
(5)HAVING分组条件
注意区分：where是在分组之前过滤每一条记录。HAVING是在分组之后进行过滤筛选。
SELECT sex, count(*) c FROM person WHERE salary > 3000 GROUP BY sex HAVING c = 3;
(6)ORDER BY排序
默认是升序
SELECT * FROM person ORDER BY salary;
SELECT * FROM person ORDER BY salary ASC;
降序
SELECT * FROM person ORDER BY salary DESC;
注意：排序如果是多个字段、先按照第一个字段排序、再按照第N个字段排序
SELECT * FROM person ORDER BY birthday, salary DESC;
(7)DISTINCT返回唯一不同的值
SELECT DISTINCT salary FROM person;
SELECT DISTINCT name, salary FROM person;
(8)LIMIT限制返回的行数
排名功能：LIMIT n：表示返回前n条记录
注意：先排序、再截取
SELECT * FROM person LIMIT 5;
SELECT * FROM person ORDER BY salary DESC LIMIT 3;
分页功能：LIMIT m, n：从第m个记录开始、返回n条。
注意：SELECT * FROM person LIMIT (页码-1)*每页条数, 每页条数;DQL(select)
常用DQL(数据查询语言)的操作
DQL(Data Query Language)：数据查询语言，查询数据库中表的记录。
(1)错误查询:笛卡尔积(A表m条、B表n条、查询结果m*n条)
SELECT * FROM com, emp;
(2)JOIN连接
理解：先使用JOIN连接n个表、把n张表横向连接为一个临时的大表、再进行条件查询。
注意：如果查询n张表、至少需要指定n-1个公共列。
如果没有任何修饰词、只写join、默认就是inner join
显示内连接
SELECT * FROM com JOIN emp on com.id = emp.comid;
SELECT * FROM com INNER JOIN emp on com.id = emp.comid;
隐士内连接
SELECT * FROM com, emp WHERE com.id = emp.comid;
(3)左连接(左外连接)
说明：左表中满足条件的和不满足条件的都会被查询出来。
SELECT * FROM com LEFT JOIN emp on com.id = emp.comid;
SELECT * FROM com LEFT OUTER JOIN emp on com.id = emp.comid;
(4)右连接(右外连接)：
说明：右表中满足条件的和不满足条件的都会被查询出来。
SELECT * FROM emp RIGHT JOIN com on com.id = emp.comid;
SELECT * FROM emp RIGHT OUTER JOIN com on com.id = emp.comid;
(5)多表查询典型案例：
根据A表查询B表
SELECT com.* FROM emp INNER JOIN com ON com.id = emp.comid WHERE emp.name = '张三';
根据B表查询A表
SELECT emp.* FROM emp INNER JOIN com ON com.id = emp.comid WHERE com.name = '淘宝';
(6)多表查询注意事项：
如果多个表有相同字段、必须使用【表名.字段】的方式来引用
SELECT emp.name ename, com.name cname
FROM emp INNER JOIN com ON com.id = emp.comid;
SELECT e.name ename, c.name cname FROM emp e INNER JOIN com c ON c.id = e.comid;DQL(select)
常用DQL(数据查询语言)的操作
DQL(Data Query Language)：数据查询语言，查询数据库中表的记录。
思想策略：拆分为多个步骤、一步一步完成。
(1)SELECT子查询
SELECT (SELECT name FROM com WHERE id = comid) 公司名
FROM emp
WHERE name = '张三';
(2)WHERE子查询(常用)
说明：子查询结果是一个数据数值。
SELECT name
FROM com
WHERE id in (SELECT comid FROM emp WHERE name = '张三' or name = '王五');
(3)FROM子查询(常用)
说明：子查询的结果是一个临时表格
SELECT t.n, t.s
FROM (SELECT name n, salary s FROM emp LIMIT 3) t
WHERE t.s = 4000;DQL(union)
常用DQL(数据查询语言)的操作
DQL(Data Query Language)：数据查询语言，查询数据库中表的记录。
UNION 运算符将两个或更多查询的结果组合起来，并生成一个结果集，其中包含来自 UNION 中参与查询的提取行。
UNION 基本规则：
所有查询的列数和列顺序必须相同。
每个查询中涉及表的列的数据类型必须相同或兼容。
允许重复
SELECT * from emp WHERE salary >= 4000
UNION ALL
SELECT * from emp WHERE salary <= 6000
不允许重复
SELECT * from emp WHERE salary >= 4000
UNION
SELECT * from emp WHERE salary <= 6000
JOIN vs UNION：
JOIN 中连接表的列可能不同，但在 UNION 中，所有查询的列数和列顺序必须相同。横向连接。
UNION 将查询之后的行放在一起（垂直放置），但 JOIN 将查询之后的列放在一起（水平放置），即它构成一个笛卡尔积。纵向连接。ANY/SOME/ALL/IN/EXISTS
子查询关键字ANY、SOME、ALL、IN、EXISTS
1、ANY和SOME大于子查询中的任意一个，等同于大于子查询的最小值
select age from t1 WHERE age > ANY( select age from t2);
select age from t1 WHERE age > SOME( select age from t2);
2、ALL大于子查询中的每一个，等同于大于子查询的最大值
select age from t1 WHERE age > ALL( select age from t2);
3、EXISTS查询存在条件
select age from t1 WHERE EXISTS( select age from t2 WHERE age >30);
4、IN查询存在集合
select age from t1 WHERE age in ( select age from t2);
5、EXISTS和IN区别
先查询t2，再查询t1
EXPLAIN
select age from t1 WHERE EXISTS( select age from t2 WHERE age >20);
先查询t1，再查询t2
EXPLAIN
select age from t1 WHERE age in ( select age from t2 WHERE age >20);
当子查询结果集很大，而外部表较小的时候，Exists查询效率会优于IN。
当子查询结果集较小，而外部表很大的时候，IN的查询效率会优于Exists。
注意：网上说法不准确。“表的规模”不是看内部表和外部表，而是外部表和子查询结果集。存储过程
存储过程就是一条或者多条SQL语句的集合，可以视为批文件。它可以定义批量插入的语句，也可以定义一个接收不同条件的SQL。
创建存储过程的语句为 CREATE PROCEDURE，创建存储函数的语句为CREATE FUNCTION。
调用存储过程的语句为CALL。
调用存储函数的形式就像调用MySQL内部函数一样。
DROP TABLE IF EXISTS t_student;
CREATE TABLE t_student
(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
age INT(11) NOT NULL
);
INSERT INTO t_student VALUES(NULL,'大宇',22),(NULL,'小宇',20);
定义一个根据ID查询学生记录的存储过程。
DROP PROCEDURE IF EXISTS getStuById;
DELIMITER // -- 定义存储过程结束符号为//
CREATE PROCEDURE getStuById(IN stuId INT(11),OUT stuName VARCHAR(255),OUT stuAge INT(11)) -- 定义输入与输出参数
COMMENT 'query students by their id' -- 提示信息
SQL SECURITY DEFINER -- DEFINER指明只有定义此SQL的人才能执行，MySQL默认也是这个
BEGIN
SELECT name ,age INTO stuName , stuAge FROM t_student WHERE id = stuId; -- 分号要加
END // -- 结束符要加
DELIMITER ; -- 重新定义存储过程结束符为分号
语法： CREATE PROCEDURE sp_name(定义输入输出参数) [ 存储特性 ] BEGIN SQL语句； END
IN 表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出的参数。sp_name为存储过程的名字。
查看刚才创建的存储过程。
SHOW PROCEDURE STATUS LIKE 'g%'
下面是调用存储过程。对于存储过程提供的临时变量而言，MySQL规定要加上@开头。
CALL db01.getStuById(1,@name,@age);
SELECT @name AS stuName,@age AS stuAge;
CALL getStuById(2,@name,@age);
SELECT @name AS stuName,@age AS stuAge;
注意：@是用户变量，@@是系统变量。
用户定义变量语法
set @t1 =100;
select @t1 from dual;3.函数
字符函数
数字函数
日期函数
聚合函数
流程控制
CASE表达式
(CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 ELSE 结果n END)
案例1
SELECT name,
sum(CASE WHEN sub='数学' THEN scr ELSE 0 END) 数学,
sum(CASE WHEN sub='语文' THEN scr ELSE 0 END) 语文
FROM t2
GROUP BY name;
案例2
名字 语文 数学 英语
张三 × √ ×
李四 √ × ×
SELECT name,
(CASE WHEN sub = '语文' THEN '√' ELSE '×' END) 语文,
(CASE WHEN sub = '数学' THEN '√' ELSE '×' END) 数学,
(CASE WHEN sub = '英语' THEN '√' ELSE '×' END) 英语
FROM t3;
案例3
考试名字 4月 5月 6月
日语 √
英语 √
SELECT name,
(CASE WHEN id in (SELECT fid FROM t5 WHERE month(m)='04' ) THEN '√' ELSE '×' END) 4月,
(CASE WHEN id in (SELECT fid FROM t5 WHERE month(m)='05' ) THEN '√' ELSE '×' END) 5月,
(CASE WHEN id in (SELECT fid FROM t5 WHERE month(m)='06' ) THEN '√' ELSE '×' END) 6月
FROM t4;json函数
SELECT json_array(1,2,3,4,5);
SELECT json_object('nane','张三','age',30);
INSERT INTO t_json(config) VALUES(json_object('name','张三'));
INSERT INTO t_json(config) VALUES(json_object('name','李四','age',40));
SELECT * FROM t_json;
DELETE FROM t_json;
update t_json set config = json_set(config,"$.age","50");
update t_json set config = json_set(config,'$','{a:1,b:2}');
INSERT INTO t_json(config) VALUES('{"name":"张三","age":30}');
update t_json set config = json_set(config,"$.name","测试") WHERE id = 5;
SELECT * FROM t_json;4.事务
事务概念
一、什么是事务？
事务是一些列SQL语句的执行单元、逻辑上的一组操作、要么全执行，要么全不执行。用户一次请求就是一个事务。
说明：查询难点：多表查询业务复杂。修改难点：单表修改操作高并发。
二、事务控制语言 (Transaction Control Language, TCL)
MySQL 默认是隐式提交，每执行一条语句就把这条语句当成一个事务然后进行提交。
当出现 START TRANSACTION 语句时，会关闭隐式提交；当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭，重新恢复隐式提交。
第一步：事务开启
START TRANSACTION;
或
BEGIN;
第二步：执行SQL语句(添加、修改、删除)、模拟转账逻辑业务单元
UPDATE person SET salary=salary-100 WHERE name='张三';
UPDATE person SET salary=salary+100 WHERE name='李四';
第三步：事务结束
rollback;
或
commit;
注意：不能回退 SELECT 语句，也不能回退 CREATE 和 DROP 语句。
注意：通过 set autocommit=0 可以取消自动提交，autocommit 标记是针对每个连接而不是
针对服务器的。
三、事务的特性（ACID）
原子性(Atomicity)：事务最小的执行单位，不允许分割。事务的原子性确保动作要么全部执行，要么全部不执行。
持久性(Durability)：一个事务被提交后，它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有影响。
隔离性(Isolation)：并发访问数据库时，一个用户的事务不应该被其他事务所影响，各并发事务之间数据库是独立的。
一致性(Consistency)：执行事务的前后，数据保持一致。例如转账的业务中，无论事务是否成功，转账者和收款人的总额应该是不变的。
原子性 + 持久性 + 隔离性 => 一致性事务并发问题
多个用户对同一数据进行操作可能会带来以下的问题：
脏读（Dirty read）：当一个事务正在访问数据并且对其进行了修改，但是还没提交事务，这时另外一个事务也访问了这个数据，然后使用了这个数据，因为这个数据的修改还没提交到数据库，所以另外一个事务读取的数据就是“脏数据”。
不可重复读（Unrepeatableread）：指在一个事务内多次读取同一数据，在这个事务还没结束时，另外一个事务也访问了这个数据并对这个数据进行了修改，那么就可能造成第一个事务两次读取的数据不一致，这种情况就被称为不可重复读。
幻读（Phantom read）：幻读与不可重复读类似，幻读是指一个事务读取了几行数据，这个事务还没结束，接着另外一个事务插入了一些数据，在随后的查询中，第一个事务读取到的数据就会比原本读取到的多，就好像发生了幻觉一样，所以称为幻读。
不可重复读和幻读区别：
不可重复读的重点是【修改】，第1次和第2次读取出来的【数据】不一样
幻读的重点是【新增或者删除】，第1次和第2次读取出来的【记录条数】不一样事务并发问题上机
select @@tx_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL READ SERIALIZABLE;
(一)脏读：
B窗口
(2)
BEGIN;
SELECT * FROM person;
A窗口
(1)
BEGIN;
INSERT INTO person(name,age) VALUES('王五',50);
(3)
rollback
(二)不可重复读：
B窗口
(1)
BEGIN;
SELECT * from person where id = 1;
(3)
SELECT * from person where id = 1;
A窗口
(2)
BEGIN;
UPDATE person set name='张三' WHERE id=1;
(三)幻读：
B窗口
(1)
BEGIN;
SELECT count(*) from person;
(3)
SELECT count(*) from person;
A窗口
(2)
BEGIN;
INSERT INTO person(name,age) VALUES('王五',50);事务隔离级别
SELECT @@TRANSACTION_isolation;
我们可以通过下面命令设置事务隔离级别。
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL READ SERIALIZABLE;
项目中事务的具体应用
@Transactional(isolation= Isolation.DEFAULT)
Isolation.DEFAULT:为数据源的默认隔离级别
isolation=Isolation.READ_UNCOMMITTED:未授权读取级别
iIsolation.READ_COMMITTED:授权读取级别
iIsolation.REPEATABLE_READ:可重复读取级别
iIsolation.SERIALIZABLE:序列化级别
事务注解详解
@Transactional
【Spring定义了七种传播方式】
如果在开始当前事务之前，一个事务上下文已经存在，此时有若干选项可以指定一个事务性方法的执行行为；
工作开发中，我们最常用的就是默认的事务传播行为：如果外层方法有事务则加入进去，没有则在当前方法创建一个事务；
1. TransactionDefinition.PROPAGATION_REQUIRED：
"如果当前存在事务，则加入该事务；如果当前没有事务，则创建一个新的事务。这是默认值。"
2. TransactionDefinition.PROPAGATION_REQUIRES_NEW：
"创建一个新的事务，如果当前存在事务，则把当前事务挂起。"
3. TransactionDefinition.PROPAGATION_SUPPORTS：
"如果当前存在事务，则加入该事务；如果当前没有事务，则以非事务的方式继续运行。"
4. TransactionDefinition.PROPAGATION_NOT_SUPPORTED：
"以非事务方式运行，如果当前存在事务，则把当前事务挂起。"
5. TransactionDefinition.PROPAGATION_NEVER：
"以非事务方式运行，如果当前存在事务，则抛出异常。"
6. TransactionDefinition.PROPAGATION_MANDATORY：
"如果当前存在事务，则加入该事务；如果当前没有事务，则抛出异常。"
7. TransactionDefinition.PROPAGATION_NESTED：
"如果当前存在事务，则创建一个事务作为当前事务的嵌套事务来运行；"
"如果当前没有事务，则该取值等价于TransactionDefinition.PROPAGATION_REQUIRED。"
【Spring中定义了五种隔离规则】
1. @Transactional(isolation = Isolation.DEFAULT)
"使用后端数据库默认的隔离级别 对于MYSQL来说就是可重复读"
1. @Transactional(isolation = Isolation.READ_UNCOMMITTED)
"是最低的隔离级别，允许读取尚未提交的数据变更(会出现脏读,不可重复读)，基本不使用"
2. @Transactional(isolation = Isolation.READ_COMMITTED)
"允许读取并发事务已经提交的数据(会出现不可重复读和幻读)"
3. @Transactional(isolation = Isolation.REPEATABLE_READ)
"事物开启后，对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改(会出现幻读)"
4. @Transactional(isolation = Isolation.SERIALIZABLE)
"最高的隔离级别，完全服从ACID的隔离级别，也是最慢的事务隔离级别，因为它通常是通过完全锁定事务相关的数据库表来实现的"
【回滚规则】
@Transactional(rollbackFor = Exception.class)
@Transactional(notRollbackFor=RunTimeException.class)
事务回滚规则定义了哪些异常会导致事务回滚而哪些不会；默认情况下，只有未检查异常（RuntimeException和Error类型的异常）会导致事务回滚，而在遇到检查型异常时不会回滚； 但是可以声明事务在遇到特定的检查型异常时像遇到运行期异常那样回滚，并且相反，还可以声明事务遇到特定的异常不回滚，即使这些异常是运行期异常；
【事务超时】
@Transactional(timeout=30) //默认是30秒
为了使应用程序很好地运行，事务不能运行太长的时间，因为事务可能涉及对后端数据库的锁定，会占用数据库资源；事务超时就是事务的一个定时器，在特定时间内事务如果没有执行完毕，那么就会自动回滚，而不是一直等待其结束；
【是否只读】
@Transactional(readOnly = true)
如果在一个事务中所有关于数据库的操作都是只读的，也就是说，这些操作只读取数据库中的数据，而并不更新数据, 这个时候我们应该给该事务设置只读属性，这样可以帮助数据库引擎优化事务，从而提升数据库读写效率；
在将事务设置成只读后，相当于将数据库设置成只读数据库，此时若要进行写的操作，会出现错误。
【事务失效】
（1）@Transactional注解未打在public方法上
（2）目标方法用final修饰
（3）同一个类中的方法直接内部调用
原因：方法被事务管理是因为Apring AOP为其生成代理了对象，但是直接this调用同类方法，调用的是目标类对象的方法，而非代理类方法，因此，在同类中的方法直接内部调用，会导致事务失效；当前读和快照读
mysql有两种读取的方式：快照读和当前读
快照读【Consistent Read】
也叫普通读，读取的是记录数据的可见版本，不加锁，不加锁的普通select语句都是快照读，即不加锁的非阻塞读。
快照读的执行方式是生成 ReadView，直接利用 MVCC 机制来进行读取，并不会对记录进行加锁。
简单的select操作(不包括 select ... lock in share mode, select ... for update)
当前读
也称锁定读【Locking Read】，读取的是记录数据的最新版本，并且需要先获取对应记录的锁。
select ... lock in share mode
select ... for update
insert
update
delete
在RR级别下，快照读是通过MVVC(多版本控制)和undo log来实现的，当前读是通过加record lock(记录锁)和gap lock(间隙锁)来实现的。，如果需要实时显示数据，还是需要通过加锁来实现。这个时候会使用next-key技术来实现。
总结：
在mysql中，提供了两种事务隔离技术，第一个是mvcc，第二个是next-key技术。
不加lock inshare mode之类的就使用mvcc。否则使用next-key。
mvcc的优势是不加锁，并发性高。缺点是不是实时数据。
next-key的优势是获取实时数据，但是需要加锁。5.锁
全局锁
全局锁：数据库的锁。场景：需要确保整个数据库一致性的操作、全库备份全库导出。
全局锁（读锁、共享锁）：允许其它用户读取数据、阻止其它用户更新数据。在一段时间内保持数据一致性。
全局锁（写锁、排它锁）：阻止其它用户读取和更新数据。场景：在一段时间内不希望其它用户干扰。
上机演示：
A窗口
##全局读锁
flush tables with read lock；
##导出数据库(注意：导出必须是cmd命令行、不能是mysql或navicat)
mysqldump -h localhost -u root -p db01 > d:\db01.sql
释放锁定
unlock tables；
B窗口
##阻塞(等待释放锁定)
update person set name='王五' where id = 1;表锁
表级锁：特点开销小、加锁快、锁定力度越大、发生所冲突的概率越高、并发度越低。
表读锁(共享锁)：允许其它事务进行读取操作、不允许其它事务进行写操作。
表写锁(独占锁)：不允许其它事务对该表进行任何操作、必须等表写锁结束。
说明一：InnoDB在必要情况下使用表锁、注意使用行锁来实现多版本并发控制MVCC、它能更好的并发性能和更少的锁冲突。
说明二：MyISAM表的读操作、会自动加上读锁、对MyISAM表的写操作、会自动加上写锁。因为不支持事务还要保证数据的一致性。
发生表级锁的命令：
alter table
drop table和truncate table
lock tables
上机演示：
A窗口：
lock tables t1 write;
lock tables t2 read;
lock tables t1 write, t2 read;
lock tables person write;
unlock tables;
B窗口：
##阻塞
select * from person;行锁
行级锁：表中一行进行锁定、粒度更小、处理高并发时提供更好并发性能更少锁冲突
共享锁（读锁、S锁）：其它事务可以读取这行数据、但不能对其进行修改。
排它锁（写锁、X锁）：其它事务不能查询、添加、修改、删除这行数据。
注意：行级锁只在事务中有效、在一个事务开始后并在事务提交或回滚之前、才能对数据行进行锁定。
行锁发生命令：
select...for update 查询对选定的行添加一个排它锁（X锁）、又叫做记录锁
select...lock in share mode 查询对选定的行添加一个共享锁（S锁）
insert 新添加的行添加一个排它锁（X锁）
update 被修改的行添加一个排它锁（X锁）
delete 被删除的行添加一个排它锁（X锁）
上机演示：
A窗口：X锁
START TRANSACTION
select * from bank where id = 1 for update;
commit;
B窗口：X锁
START TRANSACTION
select * from bank where id = 1 for update;
commit;
A窗口：S锁
START TRANSACTION
select * from bank where id = 1 lock in share mode
commit;
B窗口：S锁
START TRANSACTION
select * from bank where id = 1 lock in share mode
DELETE FROM bank where id = 1
commit;意向锁
意向锁：意向共享锁和意向排它锁
意向锁是表锁、为了协调行锁和表锁的关系、支持多粒度(表锁和行锁)的锁并存。
作用：当有事务A有行锁时、mysql会自动为该表添加意向锁、事务B如果想申请表写锁、
不需要遍历每一行判断是否存在行锁、直接判断是否存在意向锁、增强性能。
意向锁为什么是表级锁？
如果意向锁是行锁、则需要判断每一行数据去确认
如果意向锁是表锁、则只需要判断一次即可知道有么有数据行被锁定、提升性能
A窗口
START TRANSACTION
select * from person where id = 1 lock in share mode
select * from person where id = 1 for update
SELECT * FROM performance_schema.data_locks
COMMIT;
B窗口
lock tables person read;
unlock tables;间隙锁
间隙锁：它锁定的不是具体的行记录、而是两个索引之间的间隙或区间、这样可以防止新的记录插入到间隙、可以解决幻读问题、确保数据的一致性和隔离性。
A窗口
START TRANSACTION
select * from person where age >= 30 and age <= 40 for update;
COMMIT;
B窗口
DELETE FROM person where age = 33临界锁
临键锁是一种特殊的间隙锁、通过临键锁可以解决幻读问题。
每个数据行上的非唯一索引列上都会存在一把临键锁、当某个事物持有该数据行的临键锁时、会锁住一段左开右闭的数据。
注意：InnoDB中行级锁时基于索引实现的、临键锁只与非唯一索引列有关，在唯一索引列不存在临键锁。除了唯一索引其它都有临键锁、没创建索引也有临键锁。
理解：临键锁 = 行锁 + 间隙锁
上机演示
A窗口：
START TRANSACTION
select * from person where age = 30 lock in share mode
COMMIT;
B窗口
INSERT into person(name,age) values('赵六',33)6.MVCC
MVCC概念
什么是MVCC？
MVCC就是多版本并发控制，是为了在读取数据时不加锁来提高读取效率和并发性的一种手段。
数据库并发有以下几种场景：
读-读：不存在任何问题。
读-写：有线程安全问题，可能出现脏读、幻读、不可重复读。
写-写：有线程安全问题，可能存在更新丢失等。
mvcc解决的就是读写时的线程安全问题，线程不用去争抢读写锁。
mvcc锁提到的读是快照读，也就是普通的select语句。快照读在读写时不用加锁，不过可能会读到历史数据。
还有一种读取数据的方式是当前读，是一种悲观锁的操作(注意：还有一中乐观锁的操作详见MP乐观锁实现)。它会对当前读取的数据进行加锁，所以读到的数据都是最新的。主要包括以下几种操作：
select lock in share mode（共享锁）
select for update（排他锁）
update（排他锁）
insert（排他锁）
delete（排他锁）事务回顾
MVCC的实现
1.回顾事务的特性
原子性：通过undolog实现。
持久性：通过redolog实现。
隔离性：通过加锁（当前读）&MVCC（快照读）实现。
一致性：通过undolog、redolog、隔离性共同实现。
2.回顾事务的隔离级别
读未提交：允许读取尚未提交的数据变更。可能会导致脏读、幻读或不可重复读。
读已提交：允许读取已经提交的数据。可能会导致幻读和不可重复读。
可重复读：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改。可能会导致幻读。
可串行化：最高隔离级别。
在读已提交和可重复读隔离级别下的快照读，都是基于MVCC实现的！MVCC实现原理
MVCC的实现，基于undolog、版本链、readview。
在mysql存储的数据中，除了我们显式定义的字段，mysql会隐含的帮我们定义几个字段。
trx_id:事务id，每进行一次事务操作，就会自增1。
roll_pointer:回滚指针，用于找到上一个版本的数据，结合undolog进行回滚。
什么是readview呢？
当我们用select读取数据时，这一时刻的数据会有很多个版本（例如上图有四个版本），但我们并不知道读取哪个版本，这时就靠readview来对我们进行读取版本的限制，通过readview我们才知道自己能够读取哪个版本。
在一个readview快照中主要包括以下这些字段：
m_ids：活跃的事务就是指还没有commit的事务。
max_trx_id：例如m_ids中的事务id为（1，2，3），那么下一个分配的max_trx_id就是4。
creator_trx_id：执行select读这个操作的事务的id。
readview如何判断版本链中的哪个版本可用呢？（重点！）
从上到下分别为（1）（2）（3）（4），依次进行解释
trx_id表示要读取的事务id
（1）如果要读取的事务id等于进行读操作的事务id，说明是我读取我自己创建的记录，那么为什么不可以呢。
（2）如果要读取的事务id小于最小的活跃事务id，说明要读取的事务已经提交，那么可以读取。
（3）max_trx_id表示生成readview时，分配给下一个事务的id，如果要读取的事务id大于max_trx_id，说明该id已经不在该readview版本链中了，故无法访问。
（4）m_ids中存储的是活跃事务的id，如果要读取的事务id不在活跃列表，那么就可以读取，反之不行。
RC/RR隔离原理和区别
mysql四种隔离级别：
1未提交读（READ UNCOMMITED）脏读
2已提交读 （READ COMMITED）简称(RC) 不可重复读
3可重复读（REPEATABLE READ）简称（RR ）
4可串行化（SERIALIZABLE）
实验1 ，采用mysql 默认的隔离级别 RR ，启动A、B两个事务对比，阿拉伯数字递增代表事务执行的时间顺序
实验2 ，隔离级别设置为 RC ，启动A、B两个事务对比，阿拉伯数字递增代表事务执行的时间顺序
总结∶
可重复读级别下，开启事务之后第一个select才会生成快照，而不是事务一开始就生成快照。
读已提交级别下，每次select都会生成一个快照。
7.视图
视图（VIEW）是基于 SQL 语句的结果集的可视化的表。视图是虚拟的表，本身不包含数据，也就不能对其进行索引操作。
对视图的操作和对普通表的操作一样。
作用：
简化复杂的 SQL 操作，比如复杂的联结；
只使用实际表的一部分数据；
通过只给用户访问视图的权限，保证数据的安全性；
更改数据格式和表示。
本质：
select查询语句的定义、保存到数据库中。
创建视图
CREATE or REPLACE VIEW com_emp_view AS
SELECT com.id cid, com.name cname, emp.id eid, emp.name ename
FROM com INNER JOIN emp ON com.id = emp.comid;
查询视图
SELECT cname FROM com_emp_view WHERE ename = '张三';
删除视图
DROP VIEW com_emp_view;8.索引
基础概念
执行计划
执行计划 是指一条 SQL 语句在经过 MySQL 查询优化器的优化后，具体的执行方式。执行计划通常用于 SQL 性能分析、优化等场景。通过 EXPLAIN 的结果，可以了解到如数据表的查询顺序、数据查询操作的操作类型、哪些索引可以被命中、哪些索引实际会命中、每个数据表有多少行记录被查询等信息。
需要注意的是，EXPLAIN 语句并不会真的去执行相关的语句，而是通过查询优化器对语句进行分析，找出最优的查询方案，并显示对应的信息。
EXPLAIN 执行计划支持 SELECT、DELETE、INSERT、REPLACE 以及 UPDATE 语句。我们一般多用于分析 SELECT 查询语句，使用起来非常简单，语法如下
上机演示
按字段个数分类：单列索引、联合索引（也叫复合索引、组合索引）。
按字段特性分类：主键索引(PRIMARY KEY)、唯一索引(UNIQUE)、普通索引(INDEX)、全文索引(FULLTEXT)。
按数据结构分类：B+tree索引、Hash索引、Full-text索引。
按物理存储分类：聚集索引、非聚集索引（也叫二级索引、辅助索引）。
SHOW index FROM employees;
Explain执行计划
com公司表
id name
1 IBM
2 HP
emp员工表
id name comid
1 张三 1
2 李四 1
3 王五 2
(1)id和table
SELECT查询的序列号、表示查询中执行select语句或操作表的顺序。
多表连接：id相同时、执行顺序由上至下。
EXPLAIN SELECT * FROM emp,com WHERE emp.comid = com.id;
EXPLAIN SELECT * FROM emp INNER JOIN com on emp.comid = com.id;
EXPLAIN SELECT * FROM emp LEFT JOIN com on emp.comid = com.id;
EXPLAIN SELECT * FROM emp RIGHT JOIN com on emp.comid = com.id;
子查询：id不同时、id值越大优先级越高。
EXPLAIN SELECT (SELECT name FROM com WHERE id = comid) FROM emp WHERE name = '张三';
EXPLAIN SELECT name FROM com WHERE id = (SELECT comid FROM emp WHERE name = '张三');
(2)select_type
SIMPLE：简单的select查询、查询中不包含子查询或者union。
EXPLAIN SELECT * FROM emp,com WHERE emp.comid = com.id;
EXPLAIN SELECT * FROM emp INNER JOIN com on emp.comid = com.id;
EXPLAIN SELECT * FROM emp LEFT JOIN com on emp.comid = com.id;
EXPLAIN SELECT * FROM emp RIGHT JOIN com on emp.comid = com.id;
PRIMARY：查询中包含子查询、最外层被标记为primary。
EXPLAIN SELECT (SELECT name FROM com WHERE id = comid) FROM emp WHERE name = '张三';
EXPLAIN SELECT name FROM com WHERE id = (SELECT comid FROM emp WHERE name = '张三');
DEPENDENT SUBQUERY：select子查询
子查询的查询方式依赖于外面的查询结果、需要引起最大关注：可谓是性能杀手，无数开发着已经踩坑。
mysql的子查询一直都是坑,虽然优化了一些,但还是缺陷很多,尽量少用子查询吧。
另外,在执行sql前,都尽量的explain一下,该加索引的加,该改sql的改。
EXPLAIN SELECT (SELECT name FROM com WHERE id = comid) FROM emp WHERE name = '张三';
SUBQUERY：where子查询。
EXPLAIN SELECT name FROM com WHERE id = (SELECT comid FROM emp WHERE name = '张三');
DERIVED衍生：from子查询
EXPLAIN SELECT * FROM (SELECT name FROM emp limit 1) t1;
(3)type
system > const > eq_ref > ref > range > index > all
ALL：遍历全表(name无索引)
EXPLAIN SELECT name FROM emp;
index: 遍历全索引树(name有索引)
EXPLAIN SELECT name FROM emp;
range：范围(age有索引)
EXPLAIN SELECT * FROM emp WHERE age > 30;
ref：普通索引(name普通索引、联表查询的状况、先查公司再查员工、因为公司主键是员工外键不唯一)EXPLAIN SELECT * FROM emp RIGHT JOIN com on emp.comid = com.id AND emp.name='张三';
eq_ref：唯一索引(联表查询的状况、先查员工再查公司、因为员工外键是公司主键唯一)
EXPLAIN SELECT * FROM emp LEFT JOIN com on emp.comid = com.id AND emp.name='张三';
const：主键索引、唯一索引(name唯一索引)
EXPLAIN SELECT * FROM emp WHERE id = 1;
EXPLAIN SELECT * FROM emp WHERE name = '张三';
system：子查询表只有一条记录。平时不会出现、可以忽略不计。
EXPLAIN SELECT * FROM (SELECT * FROM emp WHERE id = 1) t1;
(4)possible_keys、key、key_len(name有索引、age有索引)
possible keys：显示可能应用在这张表中的索引、一个或多个。
key查询中实际使用的索引、如果为null、则没有使用索引。
key_len表示使用的索引长度，key_len可以衡量索引的好坏，key_len越小 索引效果越好
EXPLAIN SELECT * FROM emp WHERE name = '张三';
EXPLAIN SELECT * FROM emp WHERE name = '张三' AND age = 30;
(5)ref
显示索引的哪一列被使用了。
员工表的外键索引
EXPLAIN SELECT * FROM emp LEFT JOIN com on emp.comid = com.id;
公司表的主键索引
EXPLAIN SELECT * FROM emp RIGHT JOIN com on emp.comid = com.id;
(6)rows(删除索引、添加索引、前后对比值大小直观)
显示mysql认为它执行查询时必须检查的行数。越少越好。
(7)extra
using filesort：排序没有使用索引。效率低。(age无索引)
EXPLAIN SELECT * FROM emp ORDER BY age;
using temporary：分组没有使用索引。效率低。(age无索引)
EXPLAIN SELECT age, count(*) FROM emp GROUP BY age;
using index：索引被用来执行索引键值的查找。
EXPLAIN SELECT id FROM emp WHERE id = 1;
EXPLAIN SELECT id, name FROM emp WHERE id = 1;(select查询无索引字段则为空)
using where：使用了where过滤。
EXPLAIN SELECT * FROM emp WHERE name = '张三';
(8)索引失效
(8.1)复核索引最佳左前缀法则：索引的最左前列开始并且不能跳过索引中的列。
EXPLAIN SELECT * FROM emp WHERE name = '张三';
EXPLAIN SELECT * FROM emp WHERE name = '张三' AND age = 30;
EXPLAIN SELECT * FROM emp WHERE age = 30;
(8.2)索引列函数/计算失效
EXPLAIN SELECT * FROM emp WHERE CONCAT(name,'') = '张三';
EXPLAIN SELECT * FROM emp WHERE age+0 = 30;
(8.3)!=失效
EXPLAIN SELECT * FROM emp WHERE name != '张三';
(8.4)like通配符开头失效
EXPLAIN SELECT * FROM emp WHERE name LIKE '%张';
(8.5)OR失效
EXPLAIN SELECT * FROM emp WHERE name = '张三' OR age = 40;