文章目录
- 数据库基础概念
- 什么是数据库
- 数据库软件
- 表
- 模式
- 列和数据类型
- 行
- 主键
- 什么是 SQL
- 什么是 MySQL
- 登录和退出MySQL服务器
- 数据类型
- 基本语法
- 输入中文乱码解决
- 建表约束
- 数据库的三大设计范式
- 查询边学边练
- 准备数据
- 查询基本使用
- WHERE子句注意
- AND和OR的计算次序
- 通配符LIKE
- where子句中的正则表达式
- 创建计算字段
- 数据汇总
- 分组数据
- 分组计算平均成绩
- 分组条件与模糊查询
- 多表查询 - 1
- 多表查询 - 2
- 三表关联查询
- 子查询加分组求平均分
- 子查询 - 1
- 子查询 - 2
- YEAR 函数与带 IN 关键字查询
- 多层嵌套子查询
- 多表查询
- 子查询 - 3
- UNION 和 NOTIN 的使用
- ANY 表示至少一个 - DESC ( 降序 )
- 表示所有的 ALL
- 别名
- 复制表的数据作为条件查询
- 子查询 - 4
- 条件加组筛选
- NOTLIKE 模糊查询取反
- YEAR 与 NOW 函数
- MAX 与 MIN 函数
- 多段排序
- 子查询 - 5
- MAX 函数与子查询
- 子查询 - 6
- 子查询 - 7
- 子查询 - 8
- 按等级查询
- 连接查询
- 视图
- 事务
- 缓存
- 索引
- Explain执行计划
- MySQL中常见Log
- 锁。
- MVCC和事务的隔离级别
数据库基础概念
什么是数据库
数据库是一个以某种有组织的方式存储的数据集合。理解数据库的一种最简单的办法是将其想象为一个文件柜。此文件柜是一个存放数据的物理位置,不管数据是
什么以及如何组织的。
数据库软件
数据库软件应称为DBMS(数据库管理系统)。数据库
是通过DBMS创建和操纵的容器。
表
在你将资料放入自己的文件柜时,并不是随便将它们扔进某个抽屉就完事了,而是在文件柜中创建文件,然后将相关的资料放入特定的文件中。
在数据库领域中,这种文件称为表。表是一种结构化的文件,可用来存储某种特定类型的数据。
模式
表具有一些特性,这些特性定义了数据在表中如何存储,如可以存储什么样的数据,数据如何分解,各部分信息如何命名,等等。描述表的这组信息就是所谓的模式,模式可以用来描述数据库中特定的表以及整个数据库(和其中表的关系)。
列和数据类型
列(column) 表中的一个字段。所有表都是由一个或多个列组成的。
数据库中每个列都有相应的数据类型。数据类型定义列可以存储的数据种类。例如,如果列中存储的为数字(或许是订单中的物品数),则相应的数据类型应该为数值类型。如果列中存储的是日期、文本、注释、金额等,则应该用恰当的数据类型规定出来。
行
表中的数据是按行存储的,所保存的每个记录存储在自己的行内。
主键
表中每一行都应该有可以**唯一标识自己的一列(**或一组列)。一个顾客表可以使用顾客编号列,而订单表可以使用订单ID,雇员表可以使用雇员ID或雇员社会保险号。
- 任意两行都不具有相同的主键值;
- 每个行都必须具有一个主键值(主键列不允许NULL值)。
什么是 SQL
结构化查询语言(Structured Query Language) , SQL是一种专门用来与数据库通信的语言。
什么是 MySQL
数据的所有存储、检索、管理和处理实际上是由数据库软件——DBMS(数据库管理系统)完成的。MySQL是一种DBMS,即它是一种数据库软件。
基于客户机—服务器的数据库
服务器部分是负责所有数据访问和处理的一个软件。这个软件运行在称为数据库服务器的计算机上。关于数据、数据添加、删除和数据更新的所有请求都由服务器软件完成。
这些请求或更改来自运行客户机软件的计算机。
-
服务器软件为MySQL DBMS。你可以在本地安装的副本上运行,也可以连接到运行在你具有访问权的远程服务器上的一个副本。
-
客户机可以是MySQL提供的工具、脚本语言(如Perl)、Web应用开发语言(如ASP、ColdFusion、JSP和PHP)、程序设计语言(如C、C++、Java)等。
登录和退出MySQL服务器
# 登录MySQL
$ mysql -u root -p12345612
# 退出MySQL数据库服务器
exit;
数据类型
- 可以通过查看帮助文档查阅所有支持的数据类型
- 使用数据类型的原则是：够用就行，尽量使用取值范围小的，而不用大的，这样可以更多的节省存储空间
- 常用数据类型如下：
- 整数：int，bit
- 小数：decimal
- 字符串：varchar,char
- 日期时间: date, time, datetime
- 枚举类型(enum)
- 特别说明的类型如下：
- decimal表示浮点数，如decimal(5,2)表示共存5位数，小数占2位
- char表示固定长度的字符串，如char(3)，如果填充’ab’时会补一个空格为
'ab ' - varchar表示可变长度的字符串，如varchar(3)，填充’ab’时就会存储’ab’
- 字符串text表示存储大文本，当字符大于4000时推荐使用
- 对于图片、音频、视频等文件，不存储在数据库中，而是上传到某个服务器上，然后在表中存储这个文件的保存路径
- 更全的数据类型可以参考http://blog.csdn.net/anxpp/article/details/51284106
数值类型(常用)
| 类型 | 字节大小 | 有符号范围(Signed) | 无符号范围(Unsigned) |
|---|---|---|---|
| TINYINT | 1 | -128 ~ 127 | 0 ~ 255 |
| SMALLINT | 2 | -32768 ~ 32767 | 0 ~ 65535 |
| MEDIUMINT | 3 | -8388608 ~ 8388607 | 0 ~ 16777215 |
| INT/INTEGER | 4 | -2147483648 ~2147483647 | 0 ~ 4294967295 |
| BIGINT | 8 | -9223372036854775808 ~ 9223372036854775807 | 0 ~ 18446744073709551615 |
字符串
| 类型 | 字节大小 | 示例 |
|---|---|---|
| CHAR | 0-255 | 类型:char(3) 输入 ‘ab’, 实际存储为’ab ‘, 输入’abcd’ 实际存储为 ‘abc’ |
| VARCHAR | 0-255 | 类型:varchar(3) 输 ‘ab’,实际存储为’ab’, 输入’abcd’,实际存储为’abc’ |
| TEXT | 0-65535 | 大文本 |
日期时间类型
| 类型 | 字节大小 | 示例 |
|---|---|---|
| DATE | 4 | ‘2020-01-01’ |
| TIME | 3 | ‘12:29:59’ |
| DATETIME | 8 | ‘2020-01-01 12:29:59’ |
| YEAR | 1 | ‘2017’ |
| TIMESTAMP | 4 | ‘1970-01-01 00:00:01’ UTC ~ ‘2038-01-01 00:00:01’ UTC |
基本语法
-- 显示所有数据库
show databases;
-- 创建数据库
CREATE DATABASE test;
-- 切换数据库
use test;
-- 显示数据库中的所有表
show tables;
-- 创建数据表
CREATE TABLE pet (
name VARCHAR(20),
owner VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth DATE,
death DATE
);
-- 查看数据表结构
-- describe pet;
desc pet;
-- 查询表
SELECT * from pet;
-- 插入数据
INSERT INTO pet VALUES ('puffball', 'Diane', 'hamster', 'f', '1990-03-30', NULL);
-- 修改数据
UPDATE pet SET name = 'squirrel' where owner = 'Diane';
-- 删除数据
DELETE FROM pet where name = 'squirrel';
-- 删除表
DROP TABLE myorder;
输入中文乱码解决
--创建数据库的时候默认utf-8
create database abcd DEFAULT CHARACTER SET utf8;
--查看mysql的字符集：
show variables where Variable_name like '%char%';
--查看某一个数据库字符集：
show create database test;(注：test为数据库)
--看数据表的编码：
show create table pet;
--修改数据库test的字符集：
alter database test character set utf8
--修改表的编码方式：
alter table pet default character set utf8;
--修改字段的编码方式：
alter table pet change name name varchar(20) character set utf8;
该命令用于将表pet中name字段的编码方式改为utf8
--修改之后可以插入中文
INSERT INTO pet VALUE('旺财','周星驰','狗','公','1993-1-1',NULL);
建表约束
主键约束
-- 主键约束
-- 使某个字段不重复且不得为空，确保表内所有数据的唯一性。
CREATE TABLE user (
id INT PRIMARY KEY,
name VARCHAR(20)
);
-- 联合主键
-- 联合主键中的每个字段都不能为空，并且加起来不能和已设置的联合主键重复。
CREATE TABLE user (
id INT,
name VARCHAR(20),
password VARCHAR(20),
PRIMARY KEY(id, name)
);
insert into usr(1,'zhangsa','fff');
-- 自增约束
-- 自增约束的主键由系统自动递增分配。
CREATE TABLE user (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
-- 添加主键约束
-- 如果忘记设置主键，还可以通过SQL语句设置（两种方式）：
ALTER TABLE user ADD PRIMARY KEY(id);
ALTER TABLE user MODIFY id INT PRIMARY KEY;
-- 删除主键
ALTER TABLE user drop PRIMARY KEY;
唯一约束
--主键值不能为空，唯一约束值可以为空
-- 建表时创建唯一主键
CREATE TABLE user (
id INT,
name VARCHAR(20),
UNIQUE(name)
);
--两个不能同时重复
CREATE TABLE user (
id INT,
name VARCHAR(20),
UNIQUE(id,name)
);
-- 添加唯一约束
-- 如果建表时没有设置唯一建，还可以通过SQL语句设置（两种方式）：
ALTER TABLE user ADD UNIQUE(name);
ALTER TABLE user MODIFY name VARCHAR(20) UNIQUE;
-- 删除唯一约束
ALTER TABLE user DROP INDEX name;
非空约束
-- 建表时添加非空约束
-- 约束某个字段不能为空
CREATE TABLE user (
id INT,
name VARCHAR(20) NOT NULL
);
-- 移除非空约束
ALTER TABLE user MODIFY name VARCHAR(20);
默认约束
-- 建表时添加默认约束
-- 约束某个字段的默认值
CREATE TABLE user2 (
id INT,
name VARCHAR(20),
age INT DEFAULT 10
);
--只插入两个字段,默认年龄为10
insert into user2 (id,name) values(1,'zhagnsa');
-- 移除非空约束
ALTER TABLE user MODIFY age INT;
外键约束
-- 班级
CREATE TABLE classes (
id INT PRIMARY KEY,
name VARCHAR(20)
);
-- 学生表
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(20),
-- 这里的 class_id 要和 classes 中的 id 字段相关联
class_id INT,
-- 表示 class_id 的值必须来自于 classes 中的 id 字段值
FOREIGN KEY(class_id) REFERENCES classes(id)
);
-- 1. 主表（父表）classes 中没有的数据值，在副表（子表）students 中，是不可以使用的；
-- 2. 主表中的记录被副表引用时，主表不可以被删除。
数据库的三大设计范式
1NF
数据表中的所有字段都是不可分割的原子值。
只要字段值还可以继续拆分，就不满足第一范式。
范式设计得越详细，对某些实际操作可能会更好，但并非都有好处，需要对项目的实际情况进行设定。
2NF
必须在满足第一范式的前提下，其他列都必须完全依赖于主键列。如果出现不完全依赖，只可能发生在联合主键的情况下：
-- 订单表
CREATE TABLE myorder (
product_id INT,
customer_id INT,
product_name VARCHAR(20),
customer_name VARCHAR(20),
PRIMARY KEY (product_id, customer_id)
);
实际上，在这张订单表中，product_name 只依赖于 product_id ，customer_name 只依赖于 customer_id 。也就是说，product_name 和 customer_id 是没用关系的，customer_name 和 product_id 也是没有关系的。
这就不满足第二范式：除主键以外其他列都必须完全依赖于主键列！
CREATE TABLE myorder (
order_id INT PRIMARY KEY,
product_id INT,
customer_id INT
);
CREATE TABLE product (
id INT PRIMARY KEY,
name VARCHAR(20)
);
CREATE TABLE customer (
id INT PRIMARY KEY,
name VARCHAR(20)
);
拆分之后，myorder 表中的 product_id 和 customer_id 完全依赖于 order_id 主键，而 product 和 customer 表中的其他字段又完全依赖于主键。满足了第二范式的设计！
3NF
必须在满足第二范式的前提下，除了主键列之外，其他列之间不能有传递依赖关系。
CREATE TABLE myorder (
order_id INT PRIMARY KEY,
product_id INT,
customer_id INT,
customer_phone VARCHAR(15)
);
表中的 customer_phone 有可能依赖于 order_id 、 customer_id 两列，也就不满足了第三范式的设计：其他列之间不能有传递依赖关系。
CREATE TABLE myorder (
order_id INT PRIMARY KEY,
product_id INT,
customer_id INT
);
CREATE TABLE customer (
id INT PRIMARY KEY,
name VARCHAR(20),
phone VARCHAR(15)
);
修改后就不存在其他列之间的传递依赖关系，其他列都只依赖于主键列，满足了第三范式的设计！
查询边学边练
准备数据
-- 创建数据库
CREATE DATABASE select_test;
-- 切换数据库
USE select_test;
-- 创建学生表
CREATE TABLE student (
no VARCHAR(20) PRIMARY KEY,
name VARCHAR(20) NOT NULL,
sex VARCHAR(10) NOT NULL,
birthday DATE, -- 生日
class VARCHAR(20) -- 所在班级
);
-- 创建教师表
CREATE TABLE teacher (
no VARCHAR(20) PRIMARY KEY,
name VARCHAR(20) NOT NULL,
sex VARCHAR(10) NOT NULL,
birthday DATE,
profession VARCHAR(20) NOT NULL, -- 职称
department VARCHAR(20) NOT NULL -- 部门
);
-- 创建课程表
CREATE TABLE course (
no VARCHAR(20) PRIMARY KEY,
name VARCHAR(20) NOT NULL,
t_no VARCHAR(20) NOT NULL, -- 教师编号
-- 表示该 tno 来自于 teacher 表中的 no 字段值
FOREIGN KEY(t_no) REFERENCES teacher(no)
);
-- 成绩表
CREATE TABLE score (
s_no VARCHAR(20) NOT NULL, -- 学生编号
c_no VARCHAR(20) NOT NULL, -- 课程号
degree DECIMAL, -- 成绩
-- 表示该 s_no, c_no 分别来自于 student, course 表中的 no 字段值
FOREIGN KEY(s_no) REFERENCES student(no),
FOREIGN KEY(c_no) REFERENCES course(no),
-- 设置 s_no, c_no 为联合主键
PRIMARY KEY(s_no, c_no)
);
-- 查看所有表
SHOW TABLES;
-- 添加学生表数据
INSERT INTO student VALUES('101', '曾华', '男', '1977-09-01', '95033');
INSERT INTO student VALUES('102', '匡明', '男', '1975-10-02', '95031');
INSERT INTO student VALUES('103', '王丽', '女', '1976-01-23', '95033');
INSERT INTO student VALUES('104', '李军', '男', '1976-02-20', '95033');
INSERT INTO student VALUES('105', '王芳', '女', '1975-02-10', '95031');
INSERT INTO student VALUES('106', '陆军', '男', '1974-06-03', '95031');
INSERT INTO student VALUES('107', '王尼玛', '男', '1976-02-20', '95033');
INSERT INTO student VALUES('108', '张全蛋', '男', '1975-02-10', '95031');
INSERT INTO student VALUES('109', '赵铁柱', '男', '1974-06-03', '95031');
-- 添加教师表数据
INSERT INTO teacher VALUES('804', '李诚', '男', '1958-12-02', '副教授', '计算机系');
INSERT INTO teacher VALUES('856', '张旭', '男', '1969-03-12', '讲师', '电子工程系');
INSERT INTO teacher VALUES('825', '王萍', '女', '1972-05-05', '助教', '计算机系');
INSERT INTO teacher VALUES('831', '刘冰', '女', '1977-08-14', '助教', '电子工程系');
-- 添加课程表数据
INSERT INTO course VALUES('3-105', '计算机导论', '825');
INSERT INTO course VALUES('3-245', '操作系统', '804');
INSERT INTO course VALUES('6-166', '数字电路', '856');
INSERT INTO course VALUES('9-888', '高等数学', '831');
-- 添加添加成绩表数据
INSERT INTO score VALUES('103', '3-105', '92');
INSERT INTO score VALUES('103', '3-245', '86');
INSERT INTO score VALUES('103', '6-166', '85');
INSERT INTO score VALUES('105', '3-105', '88');
INSERT INTO score VALUES('105', '3-245', '75');
INSERT INTO score VALUES('105', '6-166', '79');
INSERT INTO score VALUES('109', '3-105', '76');
INSERT INTO score VALUES('109', '3-245', '68');
INSERT INTO score VALUES('109', '6-166', '81');
-- 查看表结构
SELECT * FROM course;
SELECT * FROM score;
SELECT * FROM student;
SELECT * FROM teacher;
查询基本使用
-- 查询 student 表的所有行
SELECT * FROM student;
-- 查询 student 表中的 name、sex 和 class 字段的所有行
SELECT name, sex, class FROM student;
-- 查询 teacher 表中不重复的 department 列
-- department: 去重查询
SELECT DISTINCT department FROM teacher;
-- 查询 score 表中成绩在60-80之间的所有行（区间查询和运算符查询）
-- BETWEEN xx AND xx: 查询区间, AND 表示 "并且"
SELECT * FROM score WHERE degree BETWEEN 60 AND 80;
SELECT * FROM score WHERE degree > 60 AND degree < 80;
-- 查询 score 表中成绩为 85, 86 或 88 的行
-- IN: 查询规定中的多个值
SELECT * FROM score WHERE degree IN (85, 86, 88);
-- 查询 student 表中 '95031' 班或性别为 '女' 的所有行
-- or: 表示或者关系
SELECT * FROM student WHERE class = '95031' or sex = '女';
-- 以 class 降序的方式查询 student 表的所有行
-- DESC: 降序，从高到低
-- ASC（默认）: 升序，从低到高
SELECT * FROM student ORDER BY class DESC;
SELECT * FROM student ORDER BY class ASC;
-- 以 c_no 升序、degree 降序查询 score 表的所有行
SELECT * FROM score ORDER BY c_no ASC, degree DESC;
-- 查询 "95031" 班的学生人数
-- COUNT: 统计
SELECT COUNT(*) FROM student WHERE class = '95031';
-- 查询 score 表中的最高分的学生学号和课程编号（子查询或排序查询）。
-- (SELECT MAX(degree) FROM score): 子查询，算出最高分
SELECT s_no, c_no FROM score WHERE degree = (SELECT MAX(degree) FROM score);
-- 排序查询,最高分有两个出现问题
-- LIMIT r, n: 表示从第r行开始，查询n条数据
SELECT s_no, c_no, degree FROM score ORDER BY degree DESC LIMIT 0, 1;
WHERE子句注意
-
WHERE 子句的位置
在同时使用 ORDER BY 和 WHERE 子句时,应
该让 ORDER BY 位于 WHERE 之后,否则将会产生错误 -
WHERE 子句操作符
= ,!=,<>不等于,<,<=,>,>=,BETWEEN...AND...,IS NULL检查是否为空值，is not null检查不为空值的列
AND和OR的计算次序
可使用 AND 操作符给 WHERE 子句附加条件。WHERE 可包含任意数目的 AND 和 OR 操作符。SQL(像多数语言一样)在处理 OR 操作符前,优先处理 AND 操
作符。所以在指定顺序的附加条件需要加括号。比如
select name,price from products where (id = 1002 or id =1003) and price >=10;
通配符LIKE
最常使用的通配符是百分号( % )。在搜索串中, % 表示任何字符出现任意次数。另一个有用的通配符是下划线( _ )。下划线的用途与 % 一样,但下划线只匹配单个字符而不是多个字符。
where子句中的正则表达式
则表达式的作用是匹配文本,将一个模式(正则表达式)与一个文本串进行比较。关键词REGEXP
--下面的语句检索name中包含1000的所有行
select name from products where name REGEXP '1000' ordered by name;
--.代替任意字符
select name from products where name REGEXP '.000' ordered by name;
进行OR匹配
select name from products
where name REGEXP '1000|2000'
ordered by name;
匹配几个字符之一
select name from products
where name REGEXP '[123] ton'
ordered by name;
范围匹配
select name from products
where name REGEXP '[1-5] ton'
ordered by name;
匹配特殊字符
--如果要找出包含 . 字符的值,怎样搜索?
select name from products
where name REGEXP '\\.'
ordered by name;
匹配字符类为更方便工作,可以使用预定义的字符集,称为字符类(character class)。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cGKM5YYj-1620719083307)(./SQL入门学习/正则字符表.png)]
匹配多个实例
| 元字符 | 说明 |
|---|---|
| * | 0个或者多个匹配 |
| + | 1个或者多个匹配 |
| ? | 0或1个匹配 |
| {n} | 指定数目的匹配 |
| {n,} | 不少于n个的匹配 |
| {n,m} | 匹配数目范围n-m |
--匹配带有扩按0-9数字任意一个后接stick,s可有可无
select name from products
where naem regexp '\\([0-9] sticks?\\)'
--匹配任意数字连续四个
select name from products
where name REGEXP '[[:digits:]]{4}'
ordered by name;
定位符
| 元字符 | 说明 |
|---|---|
| ^ | 文本的开始 |
| $ | 文本的结尾 |
| [[:<:]] | 词的开始 |
| [[:>:]] | 词的结尾 |
--例如,如果你想找出以一个数(包括以小数点开始的数)开始的所有产品
select name from products
where name REGEXP '^[[:digit:]\\.]'
ordered by name;
创建计算字段
存储在数据库表中的数据一般不是应用程序所需要的格式。
例如：如果想在一个字段中既显示公司名,又显示公司的地址,但这两个信息一般包含在不同的表列中。但报表程序需要把所有数据按大写表示出来。物品订单表存储物品的价格和数量,但不需要存储每个物品的总价格(用价格乘以数量即可)。需要根据表数据进行总数、平均数计算或其他计算。
存储在表中的数据都不是应用程序所需要的。
我们需要直接从数据库中检索出转换、计算或格式化过的数据;而不是检索出数据,然后再在客户机应用程序或报告程序中重新格式化。
- 拼接字段：将值联结到一起构成单个值。
select concat(name,'(',"生日",birthday,')') from student;
+----------------------------------------+
| concat(name,'(',"生日",birthday,')') |
+----------------------------------------+
| 曾华(生日1977-09-01) |
| 匡明(生日1975-10-02) |
| 王丽(生日1976-01-23) |
| 李军(生日1976-02-20) |
| 王芳(生日1975-02-10) |
| 陆军(生日1974-06-03) |
| 王尼玛(生日1976-02-20) |
| 张全蛋(生日1975-02-10) |
| 赵铁柱(生日1974-06-03) |
| brook(生日1995-01-01) |
+----------------------------------------+
- 去除空格 RTrim(),LTrim()去除左边的空格，Trim()去除空格
- 使用别名
select concat(name,'(',"生日",birthday,')') as name_birth from student;
+-----------------------------+
| name_birth |
+-----------------------------+
| 曾华(生日1977-09-01) |
...
- 执行算数计算
--建设有字段数量和单价格，打出总价
select id ,quality,price,
qualitu * price as totalPrice
from orderitems
where nume=20000;
数据汇总
我们经常需要汇总数据而不用把它们实际检索出来,为此MySQL提供了专门的函数。使用这些函数,MySQL查询可用于检索数据,以便分析和报表生成。
聚集函数
| 函数 | 说明 |
|---|---|
| AVG() | 返回某列的平均值 |
| COUNT() | 返回莫列的行数 |
| MAX() | 返回某列的最大值 |
| MIN() | 返回某列的最小值 |
| SUM() | 返回某列值之和 |
- AVG():AVG() 可用来返回所有列的平均值,也可以用来返回特定列或行的平均值。
--返回特定列平均
select avg(degree) from score;
--返回特定课程平均
select avg(degree) from score where s_no = 101;
- count()：计数，表中行数或符合特定条件的行数。
select count(*) from score;
- sum
--计算总和，例子
select sum(item_price*quantity) as total_price from oedereditems
where order_num = 20000;
--使用 AVG() 函数返回特定供应商提供的产品的平均价格。
select avg(distinct prod_rice) as avg_price from products
where vend_id = 1003;
分组数据
如何分组数据,以便能汇总表内容的子集。这涉及两个
新 SELECT 语句子句,分别是 GROUP BY 子句和HAVING 子句。
--特定供应商提供的产品数量
select count(*) as num_prods
from products
where vend_id = 1003;
--列出全部供应商呢？ROUP BY 子句指示MySQL按 vend_id 排序并分组数据。这导致对每个 vend_id 而不是整个表计算 num_prods 一次。
select count(*) as num_prods
from products
where group by vend_id;
GROUP BY 子句必须出现在 WHERE 子句之后, ORDER BY 子句之前。
过滤分组
因为 WHERE 过滤指定的是行而不是分组，MySQL为此目的提供了另外的子句,那就是 HAVING 子句。HAVING 非常类似于 WHERE 。事实上,目前为止所学过的所有类型的 WHERE 子句都可以用 HAVING 来替代。里有另一种理解方法, **WHERE 在数据分组前进行过滤, HAVING 在数据分组后进行过滤。**这是一个重要的区别, WHERE 排除的行不包括在分组中。这可能会改变计算值,从而影响 HAVING 子句中基于这些值过滤掉的分组。
--列出两个含以上，价格10以上的产品的供应商
select vend_id,count(*) as num_products
from products
where prod_price>=10
group by vend_id
having count(*) >=2;
分组计算平均成绩
查询每门课的平均成绩。
-- AVG: 平均值
SELECT AVG(degree) FROM score WHERE c_no = '3-105';
SELECT AVG(degree) FROM score WHERE c_no = '3-245';
SELECT AVG(degree) FROM score WHERE c_no = '6-166';
-- GROUP BY: 分组查询
SELECT c_no, AVG(degree) FROM score GROUP BY c_no;
分组条件与模糊查询
查询 score 表中至少有 2 名学生选修，并以 3 开头的课程的平均分数。
SELECT * FROM score;
-- c_no 课程编号
+------+-------+--------+
| s_no | c_no | degree |
+------+-------+--------+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+------+-------+--------+
分析表发现，至少有 2 名学生选修的课程是 3-105 、3-245 、6-166 ，以 3 开头的课程是 3-105 、3-245 。也就是说，我们要查询所有 3-105 和 3-245 的 degree 平均分。
-- 首先把 c_no, AVG(degree) 通过分组查询出来
SELECT c_no, AVG(degree) FROM score GROUP BY c_no
+-------+-------------+
| c_no | AVG(degree) |
+-------+-------------+
| 3-105 | 85.3333 |
| 3-245 | 76.3333 |
| 6-166 | 81.6667 |
+-------+-------------+
-- 再查询出至少有 2 名学生选修的课程
-- HAVING: 表示持有
HAVING COUNT(c_no) >= 2
-- 并且是以 3 开头的课程
-- LIKE 表示模糊查询，"%" 是一个通配符，匹配 "3" 后面的任意字符。
AND c_no LIKE '3%';
-- 把前面的SQL语句拼接起来，
-- 后面加上一个 COUNT(*)，表示将每个分组的个数也查询出来。
SELECT c_no, AVG(degree), COUNT(*) FROM score GROUP BY c_no
HAVING COUNT(c_no) >= 2
AND c_no LIKE '3%';
+-------+-------------+----------+
| c_no | AVG(degree) | COUNT(*) |
+-------+-------------+----------+
| 3-105 | 85.3333 | 3 |
| 3-245 | 76.3333 | 3 |
+-------+-------------+----------+
多表查询 - 1
查询所有学生的 name，以及该学生在 score 表中对应的 c_no 和 degree 。
SELECT no, name FROM student;
+-----+-----------+
| no | name |
+-----+-----------+
| 101 | 曾华 |
| 102 | 匡明 |
| 103 | 王丽 |
| 104 | 李军 |
| 105 | 王芳 |
| 106 | 陆军 |
| 107 | 王尼玛 |
| 108 | 张全蛋 |
| 109 | 赵铁柱 |
+-----+-----------+
SELECT s_no, c_no, degree FROM score;
+------+-------+--------+
| s_no | c_no | degree |
+------+-------+--------+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+------+-------+--------+
通过分析可以发现，只要把 score 表中的 s_no 字段值替换成 student 表中对应的 name 字段值就可以了，如何做呢？
-- FROM...: 表示从 student, score 表中查询
-- WHERE 的条件表示为，只有在 student.no 和 score.s_no 相等时才显示出来。
SELECT name, c_no, degree FROM student, score
WHERE student.no = score.s_no;
+-----------+-------+--------+
| name | c_no | degree |
+-----------+-------+--------+
| 王丽 | 3-105 | 92 |
| 王丽 | 3-245 | 86 |
| 王丽 | 6-166 | 85 |
| 王芳 | 3-105 | 88 |
| 王芳 | 3-245 | 75 |
| 王芳 | 6-166 | 79 |
| 赵铁柱 | 3-105 | 76 |
| 赵铁柱 | 3-245 | 68 |
| 赵铁柱 | 6-166 | 81 |
+-----------+-------+--------+
多表查询 - 2
查询所有学生的 no 、课程名称 ( course 表中的 name ) 和成绩 ( score 表中的 degree ) 列。
只有 score 关联学生的 no ，因此只要查询 score 表，就能找出所有和学生相关的 no 和 degree ：
SELECT s_no, c_no, degree FROM score;
+------+-------+--------+
| s_no | c_no | degree |
+------+-------+--------+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+------+-------+--------+
然后查询 course 表：
+-------+-----------------+
| no | name |
+-------+-----------------+
| 3-105 | 计算机导论 |
| 3-245 | 操作系统 |
| 6-166 | 数字电路 |
| 9-888 | 高等数学 |
+-------+-----------------+
只要把 score 表中的 c_no 替换成 course 表中对应的 name 字段值就可以了。
-- 增加一个查询字段 name，分别从 score、course 这两个表中查询。
-- as 表示取一个该字段的别名。
SELECT s_no, name as c_name, degree FROM score, course
WHERE score.c_no = course.no;
+------+-----------------+--------+
| s_no | c_name | degree |
+------+-----------------+--------+
| 103 | 计算机导论 | 92 |
| 105 | 计算机导论 | 88 |
| 109 | 计算机导论 | 76 |
| 103 | 操作系统 | 86 |
| 105 | 操作系统 | 75 |
| 109 | 操作系统 | 68 |
| 103 | 数字电路 | 85 |
| 105 | 数字电路 | 79 |
| 109 | 数字电路 | 81 |
+------+-----------------+--------+
三表关联查询
查询所有学生的 name 、课程名 ( course 表中的 name ) 和 degree 。
只有 score 表中关联学生的学号和课堂号，我们只要围绕着 score 这张表查询就好了。
SELECT * FROM score;
+------+-------+--------+
| s_no | c_no | degree |
+------+-------+--------+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+------+-------+--------+
只要把 s_no 和 c_no 替换成 student 和 srouse 表中对应的 name 字段值就好了。
首先把 s_no 替换成 student 表中的 name 字段：
SELECT name, c_no, degree FROM student, score WHERE student.no = score.s_no;
+-----------+-------+--------+
| name | c_no | degree |
+-----------+-------+--------+
| 王丽 | 3-105 | 92 |
| 王丽 | 3-245 | 86 |
| 王丽 | 6-166 | 85 |
| 王芳 | 3-105 | 88 |
| 王芳 | 3-245 | 75 |
| 王芳 | 6-166 | 79 |
| 赵铁柱 | 3-105 | 76 |
| 赵铁柱 | 3-245 | 68 |
| 赵铁柱 | 6-166 | 81 |
+-----------+-------+--------+
再把 c_no 替换成 course 表中的 name 字段：
-- 课程表
SELECT no, name FROM course;
+-------+-----------------+
| no | name |
+-------+-----------------+
| 3-105 | 计算机导论 |
| 3-245 | 操作系统 |
| 6-166 | 数字电路 |
| 9-888 | 高等数学 |
+-------+-----------------+
-- 由于字段名存在重复，使用 "表名.字段名 as 别名" 代替。
SELECT student.name as s_name, course.name as c_name, degree
FROM student, score, course
WHERE student.NO = score.s_no
AND score.c_no = course.no;
子查询加分组求平均分
查询 95031 班学生每门课程的平均成绩。
在 score 表中根据 student 表的学生编号筛选出学生的课堂号和成绩：
-- IN (..): 将筛选出的学生号当做 s_no 的条件查询
SELECT s_no, c_no, degree FROM score
WHERE s_no IN (SELECT no FROM student WHERE class = '95031');
+------+-------+--------+
| s_no | c_no | degree |
+------+-------+--------+
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+------+-------+--------+
这时只要将 c_no 分组一下就能得出 95031 班学生每门课的平均成绩：
SELECT c_no, AVG(degree) FROM score
WHERE s_no IN (SELECT no FROM student WHERE class = '95031')
GROUP BY c_no;
+-------+-------------+
| c_no | AVG(degree) |
+-------+-------------+
| 3-105 | 82.0000 |
| 3-245 | 71.5000 |
| 6-166 | 80.0000 |
+-------+-------------+
子查询 - 1
查询在 3-105 课程中，所有成绩高于 109 号同学的记录。
首先筛选出课堂号为 3-105 ，在找出所有成绩高于 109 号同学的的行。
SELECT * FROM score
WHERE c_no = '3-105'
AND degree > (SELECT degree FROM score WHERE s_no = '109' AND c_no = '3-105');
子查询 - 2
查询所有成绩高于 109 号同学的 3-105 课程成绩记录。
-- 不限制课程号，只要成绩大于109号同学的3-105课程成绩就可以。
SELECT * FROM score
WHERE degree > (SELECT degree FROM score WHERE s_no = '109' AND c_no = '3-105');
YEAR 函数与带 IN 关键字查询
查询所有和 101 、108 号学生同年出生的 no 、name 、birthday 列。
-- YEAR(..): 取出日期中的年份
SELECT no, name, birthday FROM student
WHERE YEAR(birthday) IN (SELECT YEAR(birthday) FROM student WHERE no IN (101, 108));
多层嵌套子查询
查询 ‘张旭’ 教师任课的学生成绩表。
首先找到教师编号：
SELECT NO FROM teacher WHERE NAME = '张旭'
通过 sourse 表找到该教师课程号：
SELECT NO FROM course WHERE t_no = ( SELECT NO FROM teacher WHERE NAME = '张旭' );
通过筛选出的课程号查询成绩表：
SELECT * FROM score WHERE c_no = (
SELECT no FROM course WHERE t_no = (
SELECT no FROM teacher WHERE NAME = '张旭'
)
);
多表查询
查询某选修课程多于5个同学的教师姓名。
首先在 teacher 表中，根据 no 字段来判断该教师的同一门课程是否有至少5名学员选修：
-- 查询 teacher 表
SELECT no, name FROM teacher;
+-----+--------+
| no | name |
+-----+--------+
| 804 | 李诚 |
| 825 | 王萍 |
| 831 | 刘冰 |
| 856 | 张旭 |
+-----+--------+
SELECT name FROM teacher WHERE no IN (
-- 在这里找到对应的条件
);
查看和教师编号有有关的表的信息：
SELECT * FROM course;
-- t_no: 教师编号
+-------+-----------------+------+
| no | name | t_no |
+-------+-----------------+------+
| 3-105 | 计算机导论 | 825 |
| 3-245 | 操作系统 | 804 |
| 6-166 | 数字电路 | 856 |
| 9-888 | 高等数学 | 831 |
+-------+-----------------+------+
我们已经找到和教师编号有关的字段就在 course 表中，但是还无法知道哪门课程至少有5名学生选修，所以还需要根据 score表来查询：
-- 在此之前向 score 插入一些数据，以便丰富查询条件。
INSERT INTO score VALUES ('101', '3-105', '90');
INSERT INTO score VALUES ('102', '3-105', '91');
INSERT INTO score VALUES ('104', '3-105', '89');
-- 查询 score 表
SELECT * FROM score;
+------+-------+--------+
| s_no | c_no | degree |
+------+-------+--------+
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+------+-------+--------+
-- 在 score 表中将 c_no 作为分组，并且限制 c_no 持有至少 5 条数据。 group by 和having配合使用实现先分组在执行having后的条件。having后接函数条件。
SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) > 5;
+-------+
| c_no |
+-------+
| 3-105 |
+-------+
根据筛选出来的课程号，找出在某课程中，拥有至少5名学员的教师编号：
SELECT t_no FROM course WHERE no IN (
SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) > 5
);
+------+
| t_no |
+------+
| 825 |
+------+
在 teacher 表中，根据筛选出来的教师编号找到教师姓名：
SELECT name FROM teacher WHERE no IN (
-- 最终条件
SELECT t_no FROM course WHERE no IN (
SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) > 5
)
);
子查询 - 3
查询 “计算机系” 课程的成绩表。
思路是，先找出 course 表中所有 计算机系 课程的编号，然后根据这个编号查询 score 表。
-- 通过 teacher 表查询所有 `计算机系` 的教师编号
SELECT no, name, department FROM teacher WHERE department = '计算机系'
+-----+--------+--------------+
| no | name | department |
+-----+--------+--------------+
| 804 | 李诚 | 计算机系 |
| 825 | 王萍 | 计算机系 |
+-----+--------+--------------+
-- 通过 course 表查询该教师的课程编号
SELECT no FROM course WHERE t_no IN (
SELECT no FROM teacher WHERE department = '计算机系'
);
+-------+
| no |
+-------+
| 3-245 |
| 3-105 |
+-------+
-- 根据筛选出来的课程号查询成绩表
SELECT * FROM score WHERE c_no IN (
SELECT no FROM course WHERE t_no IN (
SELECT no FROM teacher WHERE department = '计算机系'
)
);
+------+-------+--------+
| s_no | c_no | degree |
+------+-------+--------+
| 103 | 3-245 | 86 |
| 105 | 3-245 | 75 |
| 109 | 3-245 | 68 |
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
+------+-------+--------+
UNION 和 NOTIN 的使用
查询 计算机系 与 电子工程系 中的不同职称的教师。
-- NOT: 代表逻辑非
SELECT * FROM teacher WHERE department = '计算机系' AND profession NOT IN (
SELECT profession FROM teacher WHERE department = '电子工程系'
)
-- 合并两个集
UNION
SELECT * FROM teacher WHERE department = '电子工程系' AND profession NOT IN (
SELECT profession FROM teacher WHERE department = '计算机系'
);
ANY 表示至少一个 - DESC ( 降序 )
查询课程 3-105 且成绩 至少 高于 3-245 的 score 表。
SELECT * FROM score WHERE c_no = '3-105';
+------+-------+--------+
| s_no | c_no | degree |
+------+-------+--------+
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
+------+-------+--------+
SELECT * FROM score WHERE c_no = '3-245';
+------+-------+--------+
| s_no | c_no | degree |
+------+-------+--------+
| 103 | 3-245 | 86 |
| 105 | 3-245 | 75 |
| 109 | 3-245 | 68 |
+------+-------+--------+
-- ANY: 符合SQL语句中的任意条件。
-- 也就是说，在 3-105 成绩中，只要有一个大于从 3-245 筛选出来的任意行就符合条件，
-- 最后根据降序查询结果。
SELECT * FROM score WHERE c_no = '3-105' AND degree > ANY(
SELECT degree FROM score WHERE c_no = '3-245'
) ORDER BY degree DESC;
+------+-------+--------+
| s_no | c_no | degree |
+------+-------+--------+
| 103 | 3-105 | 92 |
| 102 | 3-105 | 91 |
| 101 | 3-105 | 90 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
+------+-------+--------+
表示所有的 ALL
查询课程 3-105 且成绩高于 3-245 的 score 表。
-- 只需对上一道题稍作修改。
-- ALL: 符合SQL语句中的所有条件。
-- 也就是说，在 3-105 每一行成绩中，都要大于从 3-245 筛选出来全部行才算符合条件。
SELECT * FROM score WHERE c_no = '3-105' AND degree > ALL(
SELECT degree FROM score WHERE c_no = '3-245'
);
+------+-------+--------+
| s_no | c_no | degree |
+------+-------+--------+
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
+------+-------+--------+
别名
查询所有教师和通许的name,sex,和birthday
select name,sex,birthday from student
union
select name,sex,birthday from teacher;
select name as ts_name,sex,birthday from student union select name,sex,birthday from teacher;
+-----------+-----+------------+
| ts_name | sex | birthday |
+-----------+-----+------------+
| 曾华 | 男 | 1977-09-01 |
| 匡明 | 男 | 1975-10-02 |
| 王丽 | 女 | 1976-01-23 |
| 李军 | 男 | 1976-02-20 |
| 王芳 | 女 | 1975-02-10 |
| 陆军 | 男 | 1974-06-03 |
| 王尼玛 | 男 | 1976-02-20 |
| 张全蛋 | 男 | 1975-02-10 |
| 赵铁柱 | 男 | 1974-06-03 |
| brook | 男 | 1995-01-01 |
| 李诚 | 男 | 1958-12-02 |
| 王萍 | 女 | 1972-05-05 |
| 刘冰 | 女 | 1977-08-14 |
| 张旭 | 男 | 1969-03-12 |
+-----------+-----+------------+
复制表的数据作为条件查询
查询某课程成绩比该课程平均成绩低的 score 表。
-- 查询平均分
SELECT c_no, AVG(degree) FROM score GROUP BY c_no;
+-------+-------------+
| c_no | AVG(degree) |
+-------+-------------+
| 3-105 | 87.6667 |
| 3-245 | 76.3333 |
| 6-166 | 81.6667 |
+-------+-------------+
-- 查询 score 表
SELECT degree FROM score;
+--------+
| degree |
+--------+
| 90 |
| 91 |
| 92 |
| 86 |
| 85 |
| 89 |
| 88 |
| 75 |
| 79 |
| 76 |
| 68 |
| 81 |
+--------+
-- 将表 b 作用于表 a 中查询数据,a,b表相当于相同的表，就是起了个别名
-- score a (b): 将表声明为 a (b)，
-- 如此就能用 a.c_no = b.c_no 作为条件执行查询了。
SELECT * FROM score a WHERE degree < (
(SELECT AVG(degree) FROM score b WHERE a.c_no = b.c_no)
);
select * from score as a where degree < (select avg(degree) from score as b where a.c_no = b.c_no);
+------+-------+--------+
| s_no | c_no | degree |
+------+-------+--------+
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+------+-------+--------+
子查询 - 4
查询所有任课 ( 在 course 表里有课程 ) 教师的 name 和 department 。
SELECT name, department FROM teacher WHERE no IN (SELECT t_no FROM course);
+--------+-----------------+
| name | department |
+--------+-----------------+
| 李诚 | 计算机系 |
| 王萍 | 计算机系 |
| 刘冰 | 电子工程系 |
| 张旭 | 电子工程系 |
+--------+-----------------+
条件加组筛选
查询 student 表中至少有 2 名男生的 class 。
-- 查看学生表信息
SELECT * FROM student;
+-----+-----------+-----+------------+-------+
| no | name | sex | birthday | class |
+-----+-----------+-----+------------+-------+
| 101 | 曾华 | 男 | 1977-09-01 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 | 95031 |
| 106 | 陆军 | 男 | 1974-06-03 | 95031 |
| 107 | 王尼玛 | 男 | 1976-02-20 | 95033 |
| 108 | 张全蛋 | 男 | 1975-02-10 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 | 95031 |
| 110 | 张飞 | 男 | 1974-06-03 | 95038 |
+-----+-----------+-----+------------+-------+
-- 只查询性别为男，然后按 class 分组，并限制 class 行大于 1。
SELECT class FROM student WHERE sex = '男' GROUP BY class HAVING COUNT(*) > 1;
+-------+
| class |
+-------+
| 95033 |
| 95031 |
+-------+
NOTLIKE 模糊查询取反
查询 student 表中不姓 “王” 的同学记录。
-- NOT: 取反
-- LIKE: 模糊查询
mysql> SELECT * FROM student WHERE name NOT LIKE '王%';
+-----+-----------+-----+------------+-------+
| no | name | sex | birthday | class |
+-----+-----------+-----+------------+-------+
| 101 | 曾华 | 男 | 1977-09-01 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 | 95031 |
| 104 | 李军 | 男 | 1976-02-20 | 95033 |
| 106 | 陆军 | 男 | 1974-06-03 | 95031 |
| 108 | 张全蛋 | 男 | 1975-02-10 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 | 95031 |
| 110 | 张飞 | 男 | 1974-06-03 | 95038 |
+-----+-----------+-----+------------+-------+
YEAR 与 NOW 函数
查询 student 表中每个学生的姓名和年龄。
-- 使用函数 YEAR(NOW()) 计算出当前年份，减去出生年份后得出年龄。
SELECT name, YEAR(NOW()) - YEAR(birthday) as age FROM student;
+-----------+------+
| name | age |
+-----------+------+
| 曾华 | 42 |
| 匡明 | 44 |
| 王丽 | 43 |
| 李军 | 43 |
| 王芳 | 44 |
| 陆军 | 45 |
| 王尼玛 | 43 |
| 张全蛋 | 44 |
| 赵铁柱 | 45 |
| 张飞 | 45 |
+-----------+------+
MAX 与 MIN 函数
查询 student 表中最大和最小的 birthday 值。
SELECT MAX(birthday), MIN(birthday) FROM student;
+---------------+---------------+
| MAX(birthday) | MIN(birthday) |
+---------------+---------------+
| 1977-09-01 | 1974-06-03 |
+---------------+---------------+
多段排序
以 class 和 birthday 从大到小的顺序查询 student 表。
SELECT * FROM student ORDER BY class DESC, birthday;
+-----+-----------+-----+------------+-------+
| no | name | sex | birthday | class |
+-----+-----------+-----+------------+-------+
| 110 | 张飞 | 男 | 1974-06-03 | 95038 |
| 103 | 王丽 | 女 | 1976-01-23 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 | 95033 |
| 107 | 王尼玛 | 男 | 1976-02-20 | 95033 |
| 101 | 曾华 | 男 | 1977-09-01 | 95033 |
| 106 | 陆军 | 男 | 1974-06-03 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 | 95031 |
| 105 | 王芳 | 女 | 1975-02-10 | 95031 |
| 108 | 张全蛋 | 男 | 1975-02-10 | 95031 |
| 102 | 匡明 | 男 | 1975-10-02 | 95031 |
+-----+-----------+-----+------------+-------+
子查询 - 5
查询 “男” 教师及其所上的课程。
SELECT * FROM course WHERE t_no in (SELECT no FROM teacher WHERE sex = '男');
+-------+--------------+------+
| no | name | t_no |
+-------+--------------+------+
| 3-245 | 操作系统 | 804 |
| 6-166 | 数字电路 | 856 |
+-------+--------------+------+
MAX 函数与子查询
查询最高分同学的 score 表。
-- 找出最高成绩（该查询只能有一个结果）
SELECT MAX(degree) FROM score;
-- 根据上面的条件筛选出所有最高成绩表，
-- 该查询可能有多个结果，假设 degree 值多次符合条件。
SELECT * FROM score WHERE degree = (SELECT MAX(degree) FROM score);
+------+-------+--------+
| s_no | c_no | degree |
+------+-------+--------+
| 103 | 3-105 | 92 |
+------+-------+--------+
子查询 - 6
查询和 “李军” 同性别的所有同学 name 。
-- 首先将李军的性别作为条件取出来
SELECT sex FROM student WHERE name = '李军';
+-----+
| sex |
+-----+
| 男 |
+-----+
-- 根据性别查询 name 和 sex
SELECT name, sex FROM student WHERE sex = (
SELECT sex FROM student WHERE name = '李军'
);
+-----------+-----+
| name | sex |
+-----------+-----+
| 曾华 | 男 |
| 匡明 | 男 |
| 李军 | 男 |
| 陆军 | 男 |
| 王尼玛 | 男 |
| 张全蛋 | 男 |
| 赵铁柱 | 男 |
| 张飞 | 男 |
+-----------+-----+
子查询 - 7
查询和 “李军” 同性别且同班的同学 name 。
SELECT name, sex, class FROM student WHERE sex = (
SELECT sex FROM student WHERE name = '李军'
) AND class = (
SELECT class FROM student WHERE name = '李军'
);
+-----------+-----+-------+
| name | sex | class |
+-----------+-----+-------+
| 曾华 | 男 | 95033 |
| 李军 | 男 | 95033 |
| 王尼玛 | 男 | 95033 |
+-----------+-----+-------+
子查询 - 8
查询所有选修 “计算机导论” 课程的 “男” 同学成绩表。
需要的 “计算机导论” 和性别为 “男” 的编号可以在 course 和 student 表中找到。
SELECT * FROM score WHERE c_no = (
SELECT no FROM course WHERE name = '计算机导论'
) AND s_no IN (
SELECT no FROM student WHERE sex = '男'
);
+------+-------+--------+
| s_no | c_no | degree |
+------+-------+--------+
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 104 | 3-105 | 89 |
| 109 | 3-105 | 76 |
+------+-------+--------+
按等级查询
建立一个 grade 表代表学生的成绩等级，并插入数据：
CREATE TABLE grade (
low INT(3),
upp INT(3),
grade char(1)
);
INSERT INTO grade VALUES (90, 100, 'A');
INSERT INTO grade VALUES (80, 89, 'B');
INSERT INTO grade VALUES (70, 79, 'C');
INSERT INTO grade VALUES (60, 69, 'D');
INSERT INTO grade VALUES (0, 59, 'E');
SELECT * FROM grade;
+------+------+-------+
| low | upp | grade |
+------+------+-------+
| 90 | 100 | A |
| 80 | 89 | B |
| 70 | 79 | C |
| 60 | 69 | D |
| 0 | 59 | E |
+------+------+-------+
查询所有学生的 s_no 、c_no 和 grade 列。
思路是，使用区间 ( BETWEEN ) 查询，判断学生的成绩 ( degree ) 在 grade 表的 low 和 upp 之间。
SELECT s_no, c_no, grade FROM score, grade
WHERE degree BETWEEN low AND upp;
+------+-------+-------+
| s_no | c_no | grade |
+------+-------+-------+
| 101 | 3-105 | A |
| 102 | 3-105 | A |
| 103 | 3-105 | A |
| 103 | 3-245 | B |
| 103 | 6-166 | B |
| 104 | 3-105 | B |
| 105 | 3-105 | B |
| 105 | 3-245 | C |
| 105 | 6-166 | C |
| 109 | 3-105 | C |
| 109 | 3-245 | D |
| 109 | 6-166 | B |
+------+-------+-------+
连接查询
准备用于测试连接查询的数据：
CREATE DATABASE testJoin;
CREATE TABLE person (
id INT,
name VARCHAR(20),
cardId INT
);
CREATE TABLE card (
id INT,
name VARCHAR(20)
);
INSERT INTO card VALUES (1, '饭卡'), (2, '建行卡'), (3, '农行卡'), (4, '工商卡'), (5, '邮政卡');
SELECT * FROM card;
+------+-----------+
| id | name |
+------+-----------+
| 1 | 饭卡 |
| 2 | 建行卡 |
| 3 | 农行卡 |
| 4 | 工商卡 |
| 5 | 邮政卡 |
+------+-----------+
INSERT INTO person VALUES (1, '张三', 1), (2, '李四', 3), (3, '王五', 6);
SELECT * FROM person;
+------+--------+--------+
| id | name | cardId |
+------+--------+--------+
| 1 | 张三 | 1 |
| 2 | 李四 | 3 |
| 3 | 王五 | 6 |
+------+--------+--------+
分析两张表发现，person 表并没有为 cardId 字段设置一个在 card 表中对应的 id 外键。如果设置了的话，person 中 cardId 字段值为 6 的行就插不进去，因为该 cardId 值在 card 表中并没有。
内连接
要查询这两张表中有关系的数据，可以使用 INNER JOIN ( 内连接 ) 将它们连接在一起。
-- INNER JOIN: 表示为内连接，将两张表拼接在一起。
-- on: 表示要执行某个条件。
SELECT * FROM person INNER JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id | name | cardId | id | name |
+------+--------+--------+------+-----------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
+------+--------+--------+------+-----------+
-- 将 INNER 关键字省略掉，结果也是一样的。
-- SELECT * FROM person JOIN card on person.cardId = card.id;
注意：
card的整张表被连接到了右边。
左外连接
完整显示左边的表 ( person ) ，右边的表如果符合条件就显示，不符合则补 NULL 。
-- LEFT JOIN 也叫做 LEFT OUTER JOIN，用这两种方式的查询结果是一样的。
SELECT * FROM person LEFT JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id | name | cardId | id | name |
+------+--------+--------+------+-----------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
| 3 | 王五 | 6 | NULL | NULL |
+------+--------+--------+------+-----------+
右外链接
完整显示右边的表 ( card ) ，左边的表如果符合条件就显示，不符合则补 NULL 。
SELECT * FROM person RIGHT JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id | name | cardId | id | name |
+------+--------+--------+------+-----------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
| NULL | NULL | NULL | 2 | 建行卡 |
| NULL | NULL | NULL | 4 | 工商卡 |
| NULL | NULL | NULL | 5 | 邮政卡 |
+------+--------+--------+------+-----------+
全外链接
完整显示两张表的全部数据。
-- MySQL 不支持这种语法的全外连接
-- SELECT * FROM person FULL JOIN card on person.cardId = card.id;
-- 出现错误：
-- ERROR 1054 (42S22): Unknown column 'person.cardId' in 'on clause'
-- MySQL全连接语法，使用 UNION 将两张表合并在一起。
SELECT * FROM person LEFT JOIN card on person.cardId = card.id
UNION
SELECT * FROM person RIGHT JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id | name | cardId | id | name |
+------+--------+--------+------+-----------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
| 3 | 王五 | 6 | NULL | NULL |
| NULL | NULL | NULL | 2 | 建行卡 |
| NULL | NULL | NULL | 4 | 工商卡 |
| NULL | NULL | NULL | 5 | 邮政卡 |
+------+--------+--------+------+-----------+
视图
视图是虚拟的表。与包含数据的表不一样,视图只包含使用时动态检索数据的查询。作为视图,它不包含表中应该有的任何列或数据,它包含的是一个SQL查询。
--定了某个特定商品的客户
select cust_name,cust_contact
from customers,orders,orderitems
where customers.cust_id = orders.cust_id
and orderitems.order_num = orders.order_num
and prod_id = 'TNT2';
--可以把整个查询过程包装成一个名为prodectcustomers的虚拟表，上述过程简写
select cust_name,cust_contact
from prodectcustomers
and prod_id = 'TNT2';
为什么用视图？
- 重用sql语句
- 简化sql操作
- 使用表的组成部分而不是整个表
- 保护数据，给予表特定的访问权限
- 更改表的数据格式和表示。
在视图创建之后，可以用与表基本相同的方式利用他们。可以对试图执行select操作，过滤和排序数据。
使用视图
--视图使用create view语句来创建
--show create view viewname;查看创建视图的语句
--drop删除视图，drop view viewname;
--更新视图，先drop在create
--定了某个特定商品的客户,视图
create view productcustomers as
select cust_name,cust_contact
from customers,orders,orderitems
where customers.cust_id = orders.cust_id
and orderitems.order_num = orders.order_num;
--使用视图格式化检索出来的数据
create view vendorlocation as
select concat(RTrim(vend_name),'(',RTrim(vend_contry),')') as vend_title
from venders
order by vend_name;
事务
在 MySQL 中，事务其实是一个最小的不可分割的工作单元。事务能够保证一个业务的完整性。
比如我们的银行转账：
-- a -> -100
UPDATE user set money = money - 100 WHERE name = 'a';
-- b -> +100
UPDATE user set money = money + 100 WHERE name = 'b';
在实际项目中，假设只有一条 SQL 语句执行成功，而另外一条执行失败了，就会出现数据前后不一致。
因此，在执行多条有关联 SQL 语句时，事务可能会要求这些 SQL 语句要么同时执行成功，要么就都执行失败。
如何控制事务 - COMMIT / ROLLBACK
在 MySQL 中，事务的自动提交状态默认是开启的。
-- 查询事务的自动提交状态
SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
| 1 |
+--------------+
自动提交的作用：当我们执行一条 SQL 语句的时候，其产生的效果就会立即体现出来，且不能回滚。
什么是回滚？举个例子：
CREATE DATABASE bank;
USE bank;
CREATE TABLE user (
id INT PRIMARY KEY,
name VARCHAR(20),
money INT
);
INSERT INTO user VALUES (1, 'a', 1000);
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
+----+------+-------+
可以看到，在执行插入语句后数据立刻生效，原因是 MySQL 中的事务自动将它提交到了数据库中。那么所谓回滚的意思就是，撤销执行过的所有 SQL 语句，使其回滚到最后一次提交数据时的状态。
在 MySQL 中使用 ROLLBACK 执行回滚：
-- 回滚到最后一次提交
ROLLBACK;
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
+----+------+-------+
由于所有执行过的 SQL 语句都已经被提交过了，所以数据并没有发生回滚。那如何让数据可以发生回滚？
-- 关闭自动提交
SET AUTOCOMMIT = 0;
-- 查询自动提交状态
SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
| 0 |
+--------------+
将自动提交关闭后，测试数据回滚：
INSERT INTO user VALUES (2, 'b', 1000);
-- 关闭 AUTOCOMMIT 后，数据的变化是在一张虚拟的临时数据表中展示，
-- 发生变化的数据并没有真正插入到数据表中。
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
+----+------+-------+
-- 数据表中的真实数据其实还是：
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
+----+------+-------+
-- 由于数据还没有真正提交，可以使用回滚
ROLLBACK;
-- 再次查询
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
+----+------+-------+
那如何将虚拟的数据真正提交到数据库中？使用 COMMIT :
INSERT INTO user VALUES (2, 'b', 1000);
-- 手动提交数据（持久性），
-- 将数据真正提交到数据库中，执行后不能再回滚提交过的数据。
COMMIT;
-- 提交后测试回滚
ROLLBACK;
-- 再次查询（回滚无效了）
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
+----+------+-------+
总结
自动提交
- 查看自动提交状态：
SELECT @@AUTOCOMMIT；- 设置自动提交状态：
SET AUTOCOMMIT = 0。手动提交
@@AUTOCOMMIT = 0时，使用COMMIT命令提交事务。事务回滚
@@AUTOCOMMIT = 0时，使用ROLLBACK命令回滚事务。
事务的实际应用，让我们再回到银行转账项目：
-- 转账
UPDATE user set money = money - 100 WHERE name = 'a';
-- 到账
UPDATE user set money = money + 100 WHERE name = 'b';
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
+----+------+-------+
这时假设在转账时发生了意外，就可以使用 ROLLBACK 回滚到最后一次提交的状态：
-- 假设转账发生了意外，需要回滚。
ROLLBACK;
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
+----+------+-------+
这时我们又回到了发生意外之前的状态，也就是说，事务给我们提供了一个可以反悔的机会。假设数据没有发生意外，这时可以手动将数据真正提交到数据表中：COMMIT 。
手动开启事务 - BEGIN / START TRANSACTION
事务的默认提交被开启 ( @@AUTOCOMMIT = 1 ) 后，此时就不能使用事务回滚了。但是我们还可以手动开启一个事务处理事件，使其可以发生回滚：
-- 使用 BEGIN 或者 START TRANSACTION 手动开启一个事务
-- START TRANSACTION;
BEGIN;
UPDATE user set money = money - 100 WHERE name = 'a';
UPDATE user set money = money + 100 WHERE name = 'b';
-- 由于手动开启的事务没有开启自动提交，
-- 此时发生变化的数据仍然是被保存在一张临时表中。
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
+----+------+-------+
-- 测试回滚
ROLLBACK;
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
+----+------+-------+
仍然使用 COMMIT 提交数据，提交后无法再发生本次事务的回滚。
BEGIN;
UPDATE user set money = money - 100 WHERE name = 'a';
UPDATE user set money = money + 100 WHERE name = 'b';
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
+----+------+-------+
-- 提交数据
COMMIT;
-- 测试回滚（无效，因为表的数据已经被提交）
ROLLBACK;
事务的 ACID 特征与使用
事务的四大特征：
- A 原子性：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性
- C 一致性：数据库总是从一个一致性的状态转换到另一个一致性的状态。拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。
- I 隔离性：通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。事务1 和 事务2 之间是具有隔离性的；(在前面的例子中，当执行完第1条语句、第2条语句还未开始时，此时有另外的一个账户汇总程序开始运行，则其看到支票帐户的余额并没有被减去100美元。）
- D 持久性：一旦事务提交，则其所做的修改会永久保存到数据库。（此时即使系统崩溃，修改的数据也不会丢失。）事务一旦结束 (
COMMIT) ，就不可以再返回了 (ROLLBACK) 。
事务的隔离性
事务的隔离性可分为四种 ( 性能从低到高 ) ：
-
READ UNCOMMITTED ( 读取未提交 )
如果有多个事务，那么任意事务都可以看见其他事务的未提交数据。
e.g. 如果有事物a和事务b，a事务对数据进行操作，在操作过程中，事务a没有被提交，但是b可以看见a操作的结果。
-
READ COMMITTED ( 读取已提交 )
只能读取到其他事务已经提交的数据。
-
REPEATABLE READ ( 可被重复读 ，默认级别)
如果有多个连接都开启了事务，那么事务之间不能共享数据记录，否则只能共享已提交的记录。
-
SERIALIZABLE ( 串行化 )
所有的事务都会按照固定顺序执行，执行完一个事务后再继续执行下一个事务的写入操作。
查看当前数据库的默认隔离级别：
-- MySQL 8.x, GLOBAL 表示系统级别，不加表示会话级别。
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
SELECT @@TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| REPEATABLE-READ | -- MySQL的默认隔离级别，可以重复读。
+--------------------------------+
-- MySQL 5.x
SELECT @@GLOBAL.TX_ISOLATION;
SELECT @@TX_ISOLATION;
修改隔离级别：
-- 设置系统隔离级别，LEVEL 后面表示要设置的隔离级别 (READ UNCOMMITTED)。
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- 查询系统隔离级别，发现已经被修改。
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| READ-UNCOMMITTED |
+--------------------------------+
脏读
测试 READ UNCOMMITTED ( 读取未提交 别) 的隔离性：
INSERT INTO user VALUES (3, '小明', 1000);
INSERT INTO user VALUES (4, '淘宝店', 1000);
SELECT * FROM user;
+----+-----------+-------+
| id | name | money |
+----+-----------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
+----+-----------+-------+
-- 开启一个事务操作数据
-- 假设小明在淘宝店买了一双800块钱的鞋子：
START TRANSACTION;
UPDATE user SET money = money - 800 WHERE name = '小明';
UPDATE user SET money = money + 800 WHERE name = '淘宝店';
-- 然后淘宝店在另一方查询结果，发现钱已到账。
SELECT * FROM user;
+----+-----------+-------+
| id | name | money |
+----+-----------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 200 |
| 4 | 淘宝店 | 1800 |
+----+-----------+-------+
由于小明的转账是在新开启的事务上进行操作的，而该操作的结果是可以被其他事务（另一方的淘宝店）看见的，因此淘宝店的查询结果是正确的，淘宝店确认到账。但就在这时，如果小明在它所处的事务上又执行了 ROLLBACK 命令，会发生什么？
-- 小明所处的事务
ROLLBACK;
-- 此时无论对方是谁，如果再去查询结果就会发现：
SELECT * FROM user;
+----+-----------+-------+
| id | name | money |
+----+-----------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
+----+-----------+-------+
这就是所谓的脏读，一个事务读取到另外一个事务还未提交的数据。这在实际开发中是不允许出现的。
不可重复读
把隔离级别设置为 READ COMMITTED ：
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| READ-COMMITTED |
+--------------------------------+
这样，再有新的事务连接进来时，它们就只能查询到已经提交过的事务数据了。但是对于当前事务来说，它们看到的还是未提交的数据，例如：
-- 正在操作数据事务（当前事务）
START TRANSACTION;
UPDATE user SET money = money - 800 WHERE name = '小明';
UPDATE user SET money = money + 800 WHERE name = '淘宝店';
-- 虽然隔离级别被设置为了 READ COMMITTED，但在当前事务中，
-- 它看到的仍然是数据表中临时改变数据，而不是真正提交过的数据。
SELECT * FROM user;
+----+-----------+-------+
| id | name | money |
+----+-----------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 200 |
| 4 | 淘宝店 | 1800 |
+----+-----------+-------+
-- 假设此时在远程开启了一个新事务，连接到数据库。
$ mysql -u root -p12345612
-- 此时远程连接查询到的数据只能是已经提交过的
SELECT * FROM user;
+----+-----------+-------+
| id | name | money |
+----+-----------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
+----+-----------+-------+
但是这样还有问题，那就是假设一个事务在操作数据时，其他事务干扰了这个事务的数据。例如：
-- 小张在查询数据的时候发现：
SELECT * FROM user;
+----+-----------+-------+
| id | name | money |
+----+-----------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 200 |
| 4 | 淘宝店 | 1800 |
+----+-----------+-------+
-- 在小张求表的 money 平均值之前，小王做了一个操作：
START TRANSACTION;
INSERT INTO user VALUES (5, 'c', 100);
COMMIT;
-- 此时表的真实数据是：
SELECT * FROM user;
+----+-----------+-------+
| id | name | money |
+----+-----------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 100 |
+----+-----------+-------+
-- 这时小张再求平均值的时候，就会出现计算不相符合的情况：
SELECT AVG(money) FROM user;
+------------+
| AVG(money) |
+------------+
| 820.0000 |
+------------+
虽然 READ COMMITTED 让我们只能读取到其他事务已经提交的数据，但还是会出现问题，就是在读取同一个表的数据时，可能会发生前后不一致的情况。这被称为不可重复读现象 ( READ COMMITTED ) 。
幻读
将隔离级别设置为 REPEATABLE READ ( 可被重复读取 ) :
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| REPEATABLE-READ |
+--------------------------------+
测试 REPEATABLE READ ，假设在两个不同的连接上分别执行 START TRANSACTION :
-- 小张 - 成都
START TRANSACTION;
INSERT INTO user VALUES (6, 'd', 1000);
-- 小王 - 北京
START TRANSACTION;
-- 小张 - 成都
COMMIT;
当前事务开启后，没提交之前，查询不到，提交后可以被查询到。但是，在提交之前其他事务被开启了，那么在这条事务线上，就不会查询到当前有操作事务的连接。相当于开辟出一条单独的线程。
无论小张是否执行过 COMMIT ，在小王这边，都不会查询到小张的事务记录，而是只会查询到自己所处事务的记录：
SELECT * FROM user;
+----+-----------+-------+
| id | name | money |
+----+-----------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 100 |
+----+-----------+-------+
这是因为小王在此之前开启了一个新的事务 ( START TRANSACTION ) ，那么在他的这条新事务的线上，跟其他事务是没有联系的，也就是说，此时如果其他事务正在操作数据，它是不知道的。
然而事实是，在真实的数据表中，小张已经插入了一条数据。但是小王此时并不知道，也插入了同一条数据，会发生什么呢？
INSERT INTO user VALUES (6, 'd', 1000);
-- ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY'
报错了，操作被告知已存在主键为 6 的字段。这种现象也被称为幻读，一个事务提交的数据，不能被其他事务读取到。
串行化
顾名思义，就是所有事务的写入操作全都是串行化的。什么意思？把隔离级别修改成 SERIALIZABLE :
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| SERIALIZABLE |
+--------------------------------+
还是拿小张和小王来举例：
-- 小张 - 成都
START TRANSACTION;
-- 小王 - 北京
START TRANSACTION;
-- 开启事务之前先查询表，准备操作数据。
SELECT * FROM user;
+----+-----------+-------+
| id | name | money |
+----+-----------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 100 |
| 6 | d | 1000 |
+----+-----------+-------+
-- 发现没有 7 号王小花，于是插入一条数据：
INSERT INTO user VALUES (7, '王小花', 1000);
此时会发生什么呢？由于现在的隔离级别是 SERIALIZABLE ( 串行化 ) ，串行化的意思就是：假设把所有的事务都放在一个串行的队列中，那么所有的事务都会按照固定顺序执行，执行完一个事务后再继续执行下一个事务的写入操作 ( 这意味着队列中同时只能执行一个事务的写入操作 ) 。
根据这个解释，小王在插入数据时，会出现等待状态，直到小张执行 COMMIT 结束它所处的事务，或者出现等待超时。
缓存
SQL执行过程
- 1.当客户端连接到MySQL服务器时，服务器对其进行认证。可以通过用户名与密码认证，也可以通过SSL证书进行认证。登录认证后，服务器还会验证客户端是否有执行某个查询的操作权限。
- 2.在正式查询之前，服务器会检查查询缓存，如果能找到对应的查询，则不必进行查询解析，优化，执行等过程，直接返回缓存中的结果集。
- 3.MySQL的解析器会根据查询语句，构造出一个解析树，主要用于根据语法规则来验证语句是否正确，比如SQL的关键字是否正确，关键字的顺序是否正确。
而预处理器主要是进一步校验，比如表名，字段名是否正确等
- 4.查询优化器将解析树转化为查询计划，一般情况下，一条查询可以有很多种执行方式，最终返回相同的结果，优化器就是根据
成本找到这其中最优的执行计划 - 5.执行计划调用查询执行引擎，而查询引擎通过一系列API接口查询到数据
- 6.得到数据之后，在返回给客户端的同时，会将数据存在查询缓存中
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-n1djGQp0-1620719083311)(./SQL入门学习/sql执行.png)]
查询缓存
我们先通过show variables like '%query_cache%'来看一下默认的数据库配置，此为本地数据库的配置。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-i7kbWAst-1620719083312)(./SQL入门学习/sql_cache.png)]
have_query_cache:当前的MYSQL版本是否支持“查询缓存”功能。
query_cache_limit:MySQL能够缓存的最大查询结果，查询结果大于该值时不会被缓存。默认值是1048576(1MB)
query_cache_min_res_unit:查询缓存分配的最小块（字节）。默认值是4096（4KB）。当查询进行时，MySQL把查询结果保存在query cache，但是如果保存的结果比较大，超过了query_cache_min_res_unit的值，这时候MySQL将一边检索结果，一边进行保存结果。他保存结果也是按默认大小先分配一块空间，如果不够，又要申请新的空间给他。如果查询结果比较小，默认的query_cache_min_res_unit可能造成大量的内存碎片，如果查询结果比较大，默认的query_cache_min_res_unit又不够，导致一直分配块空间，所以可以根据实际需求，调节query_cache_min_res_unit的大小。
query_cache_size:为缓存查询结果分配的总内存。
query_cache_type:为on，可以缓存除了以select sql_no_cache开头的所有查询结果。默认query_cache_type设置为OFF，其实网上资料和各大云厂商提供的云服务器都是将这个功能关闭的，从上面的原理来看，在一般情况下，他的弊端大于优点。
query_cache_wlock_invalidate:如果该表被锁住，是否返回缓存中的数据，默认是关闭的。
原理
MYSQL的查询缓存实质上是缓存SQL的hash值和该SQL的查询结果，如果运行相同的SQL,服务器直接从缓存中去掉结果，而不再去解析，优化，寻找最低成本的执行计划等一系列操作，大大提升了查询速度。
- 第一个弊端就是如果表的数据有一条发生变化，那么缓存好的结果将全部不再有效。这对于频繁更新的表，查询缓存是不适合的。
比如一张表里面只有两个字段，分别是id和name，数据有一条为1，张三。我使用select * from 表名 where name=“张三”来进行查询，MySQL发现查询缓存中没有此数据，会进行一系列的解析，优化等操作进行数据的查询，查询结束之后将该SQL的hash和查询结果缓存起来，并将查询结果返回给客户端。但是这个时候我有新增了一条数据2，张三。如果我还用相同的SQL来执行，他会根据该SQL的hash值去查询缓存中，那么结果就错了。所以MySQL对于数据有变化的表来说，会直接清空关于该表的所有缓存。这样其实是效率是很差的。
- 第二个弊端就是缓存机制是通过对SQL的hash，得出的值为key，查询结果为value来存放的，那么就意味着SQL必须完完全全一模一样，否则就命不中缓存。
我们都知道hash值的规则，就算很小的查询，哈希出来的结果差距是很多的，所以select * from 表名 where name=“张三”和SELECT * FROM 表名 WHERE NAME=“张三”和select * from 表名 where name = “张三”，三个SQL哈希出来的值是不一样的，大小写和空格影响了他们，所以并不能命中缓存，但其实他们搜索结果是完全一样的。
索引
首先明白为什么索引会增加速度，DB在执行一条Sql语句的时候，默认的方式是根据搜索条件进行全表扫描，遇到匹配条件的就加入搜索结果集合。如果我们对某一字段增加索引，查询时就会先去索引列表中一次定位到特定值的行数，大大减少遍历匹配的行数，所以能明显增加查询的速度。
原理部分转载
大部分数据库系统都采用B+树作为索引结构。在mysql中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，MyISAM和InnoDB两个存储引擎的索引实现方式见下。
MyISAM索引实现
MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。主键索引的key不可以重复，而辅助索引的key可以重复。MyISAM 的索引方式也叫做“非聚集索引”。
InnoDB索引实现
InnoDB也是使用B+Tree作为索引结构，但是具体实现方式却与MyISAM截然不同。
1 InnoDB的数据文件本身就是索引文件。数据文件本身按照B+Tree组织的一个索引结构，这棵树的叶子节点data域完整保存数据记录。这个索引的key是数据表的主键。这种索引叫做聚集索引。因为 InnoDB 的数据文件本身要按主键聚集。
同时,请尽量在 InnoDB 上采用自增字段做表的主键。因为 InnoDB 数据文件本身是一棵B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。
2 .第二个与 MyISAM 索引的不同是 InnoDB 的辅助索引 data 域存储相应记录主键的值而不是地址。
聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引:首先检索**辅助索引获得主键,**然后用主键到主索引中检索获得记录。
因为辅助索引存储的数据是主键索引的key，这样就不建议主键过长，主键过长会导致辅助索引变的过大。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ojak07eq-1620719083313)(./SQL入门学习/索引.jpg)]
联合索引及最左原则
将多个字段联合一起作为索引，使用最左原则
例如有联合索引三个字段（A，B，C）
查询条件：
查询条件：
（A，，）—会使用索引
（A，B，）—会使用索引
（A，B，C）—会使用索引
（，B，C）—不会使用索引
（，，C）—不会使用索引
最左匹配原则：
- 索引可以简单如一个列
(a)，也可以复杂如多个列(a, b, c, d)，即联合索引。 - 如果是联合索引，那么key也由多个列组成，同时，索引只能用于查找key是否存在（相等），遇到范围查询
(>、<、between、like左匹配)等就不能进一步匹配了，后续退化为线性查找。 - 因此，列的排列顺序决定了可命中索引的列数。
例子：
-
如有索引
(a, b, c, d)，查询条件a = 1 and b = 2 and c > 3 and d = 4，则会在每个节点依次命中a、b、c，无法命中d。(很简单：索引命中只能是相等的情况，不能是范围匹配) -
=、in自动优化顺序
不需要考虑=、in等的顺序，mysql会自动优化这些条件的顺序，以匹配尽可能多的索引列。
例子：
- 如有索引
(a, b, c, d)，查询条件c > 3 and b = 2 and a = 1 and d < 4与a = 1 and c > 3 and b = 2 and d < 4等顺序都是可以的，MySQL会自动优化为a = 1 and b = 2 and c > 3 and d < 4，依次命中a、b、c。
- 如有索引
索引的分类
常见的索引类型有：主键索引、唯一索引、普通索引、全文索引、组合索引
创建索引
1普通索引
在创建表的时候添加索引
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);
在创建表以后添加索引
ALTER TABLE my_table ADD INDEX index_name(column_name);
或者
CREATE INDEX index_name ON my_table(column_name);
删除索引的语法：
DROP INDEX [indexName] ON mytable;
2唯一索引
它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
--创建索引
CREATE UNIQUE INDEX indexName ON mytable(username(length))
--修改索引
ALTER mytable ADD UNIQUE [indexName] ON (username(length))
--建表时直接指定
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE [indexName] (username(length))
);
3主键索引
它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
PRIMARY KEY(ID)
);
4组合索引
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
city VARCHAR(50) NOT NULL,
age INT NOT NULL
);
ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);
建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：
1. usernname,city,age
2. usernname,city
3. usernname
5全文索引
FULLTEXT（全文）索引，仅可用于MyISAM针对较大的数据，生成全文索引非常的消耗时间和空间。对于文本的大对象，或者较大的CHAR类型的数据，如果使用普通索引，那么匹配文本前几个字符还是可行的，但是想要匹配文本中间的几个单词，那么就要使用LIKE %word%来匹配，这样需要很长的时间来处理，响应时间会大大增加，这种情况，就可使用时FULLTEXT索引了，使用全文本搜索时,MySQL不需要分别查看每个行,不需要分别分析和处理每个词。MySQL创建指定列中各词的一个索引,搜索可以针对这些词进行。样,MySQL可以快速有效地决定哪些词匹配(哪些行包含它们)
哪些词不匹配,它们匹配的频率,等等。**在生成FULLTEXT索引时，会为文本生成一份单词的清单，在索引时及根据这个单词的清单来索引。**FULLTEXT可以在创建表的时候创建，也可以在需要的时候用ALTER或者CREATE INDEX来添加：
--创建表的时候添加FULLTEXT索引
CTREATE TABLE my_table(
id INT(10) PRIMARY KEY,
name VARCHAR(10) NOT NULL,
my_text TEXT,
FULLTEXT(my_text)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
--创建表以后，在需要的时候添加FULLTEXT索引
ALTER TABLE my_table ADD FULLTEXT INDEX
ft_index(column_name);
全文索引的查询也有自己特殊的语法，而不能使用LIKE %查询字符串%的模糊查询语法
SELECT * FROM table_name MATCH(my_index) AGAINST('查询字符串');
删除和查询索引
alter table table_name drop index index_name;
show index from table_name;
索引的不足之处
上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：
-
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
-
建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。
使用索引的注意事项
使用索引时，有以下一些技巧和注意事项：
-
最佳左前缀法则
如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引
中的列。 -
索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
- 使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
- 索引列排序
MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
- like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
- 不要在列上进行运算
select * from users where YEAR(adddate)<2007;
将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成
select * from users where adddate<‘2007-01-01’;
- 不使用NOT IN和<>操作
Explain执行计划
如果有些SQL贼慢，我们需要知道他有没有走索引，走了哪个索引，这个时候我就需要通过explain关键字来深入了解MySQL内部是如何执行的。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-S8nhmcvA-1620719083315)(./SQL入门学习/explain.png)]
id
一般来说一个select一个唯一id，如果是子查询，就有两个select，id是不一样的，但是凡事有例外，有些子查询的，他们id是一样的。这是为什么呢？
那是因为MySQL在进行优化的时候已经将子查询改成了连接查询，而连接查询的id是一样的。
id 如果相同,可以认为是一组,从上往下顺序执行;在所有组中,id 值越大,优先级越高,越先执行
select_type
查询的类型，主要用于:查询的类型,
用于区别:普通查询、联合查询、子查询等的复杂查询
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DjBaYH8J-1620719083316)(./SQL入门学习/select_type.png)]
type列
type 显示的是访问类型,是较为重要的一个指标,结果值从最好到最坏依次是:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery >
index_subquery > range > index > ALL
需要记忆的
system>const>eq_ref>ref>range>index>ALL
一般来说**,得保证查询至少达到 range 级别**,最好能达到 ref。
-
system:只有一行记录（等于系统表），这是const类型的特例，平时不会出现这个也可以忽略不计。
-
const:通过索引一次就找到了，const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据,所以很快
如将主键置于 where 列表中,MySQL 就能将该查询转换为一个常量 e.gselect * from t1 where id =1; -
eq_ref:**唯一性索引扫描,**对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引
扫描e.gselect * from t1,t2 where t1.id = t2.id; -
Ref:
**非唯一性索引扫描,返回匹配某个单独值的所有行.本质上也是一种索引访问,它返回所有匹配某个单独值的行,**然而,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体e.g
select count(distinct col1) from t1;select * from t1 where col1 = ‘ac’; -
range: 只检索给定范围的行，使用一个索引来选择行。key列显示了使用了哪个索引，一般这就是在你的where中出席拿了between、<、>、in等的查询。这种范围扫描比全表扫描哟啊好，因为需要开始于索引的某一点，而结束语另一点，不用全表扫描。e.g
select * from t1 between 30 and 60 -
index:当查询的结果全为索引列的时候,虽然也是全部扫描,但是只查询的索引库,而没有去查询
数据。e.gselect id from t1; -
all: 将遍历全表来找到匹配的行
select * from t1 where column_without_index = ‘’
possible_keys与key
-
possible_keys:
指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询 -
key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）
不损失精确性的情况下，长度越短越好 根据这个值,就可以判断索引使用情况
特别是在组合索引的时候,判断所有的索引字段是否都被查询用到。char 和 varchar 跟字符编码也有密切的联系,latin1 占用 1 个字节,gbk 占用 2 个字节,utf8 占用 3 个字节。(不同字符编码占用的存储空间不同)。如果索引允许为null那么该长度还需要加1个字节。varchar类型还需要加2字节。（变长类型需要额外字节保存长度）
ref
显示索引的哪一列被使用了,如果可能的话,是一个常数。列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
rows
估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数
Extra
包含不适合在其他列中显示但十分重要的额外信息。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fyoof50y-1620719083317)(./SQL入门学习/extra.png)]
Using filesort
说明 mysql 会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。
MySQL 中无法利用索引完成的排序操作称为“文件排序”
当发现有 Using filesort 后,实际上就是发现了可以优化的地方
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-O1wdTHdc-1620719083317)(./SQL入门学习/filesort.png)]
上图其实是一种索引失效的情况,后面会讲,可以看出查询中用到了个联合索引,索引分别为 col1,col2,col3
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-p758pSna-1620719083318)(./SQL入门学习/filesort2.png)]
当我排序新增了个 col2,发现 using filesort 就没有了。
Using temporary
使了用临时表保存中间结果,MySQL 在对查询结果排序时使用临时表。常见于排序 order by和分组查询 group by。
Using index
表示相应的 select 操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错!
如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’。即只需扫描索引而无须回表。
只扫描索引而无需回表的优点：
1.索引条目通常远小于数据行大小，只需要读取索引，则mysql会极大地减少数据访问量。
2.因为索引是按照列值顺序存储的，所以对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO少很多。
3.一些存储引擎如myisam在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用
4.innodb的聚簇索引，覆盖索引对innodb表特别有用。(innodb的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询)
覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引不存储索引列的值，所以mysql只能用B-tree索引做覆盖索引。
MySQL中常见Log
MySQL日志文件系统的组成
a、错误日志：记录启动、运行或停止mysqld时出现的问题。 b、通用日志：记录建立的客户端连接和执行的语句。 c、更新日志：记录更改数据的语句。该日志在MySQL 5.1中已不再使用。 d、二进制日志：binlog记录所有更改数据的语句。还用于复制。 e、慢查询日志：记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询。 f、Innodb日志：innodb redo log 和undo log
缺省情况下，所有日志创建于mysqld数据目录中。 可以通过刷新日志，来强制mysqld来关闭和重新打开日志文件（或者在某些情况下切换到一个新的日志）。 当你执行一个FLUSH LOGS语句或执行mysqladmin flush-logs或mysqladmin refresh时，则日志被老化。 对于存在MySQL复制的情形下，从复制服务器将维护更多日志文件，被称为接替日志。
错误日志
错误日志是一个文本文件。 错误日志记录了MySQL Server每次启动和关闭的详细信息以及运行过程中所有较为严重的警告和错误信息。 可以用–log-error[=file_name]选项来开启mysql错误日志，该选项指定mysqld保存错误日志文件的位置。 对于指定–log-error[=file_name]选项而未给定file_name值，mysqld使用错误日志名host_name.err 并在数据目录中写入日志文件。 在mysqld正在写入错误日志到文件时，执行FLUSH LOGS 或者mysqladmin flush-logs时，服务器将关闭并重新打开日志文件。 建议在flush之前手动重命名错误日志文件，之后mysql服务将使用原始文件名打开一个新文件。 以下为错误日志备份方法： shell> mv host_name.err host_name.err-old shell> mysqladmin flush-logs shell> mv host_name.err-old backup-directory
InnoDB中的日志
MySQL数据库InnoDB存储引擎Log漫游
Undo Log
Undo Log 是为了实现事务的原子性，在MySQL数据库InnoDB存储引擎中，undo log主要有两个作用：回滚和多版本控制(MVCC)
- 事务的原子性(Atomicity) 事务中的所有操作，要么全部完成，要么不做任何操作，不能只做部分操作。如果在执行的过程中发生 了错误，要回滚(Rollback)到事务开始前的状态，就像这个事务从来没有执行过。
- 原理 Undo Log的原理很简单，为了满足事务的原子性，在操作任何数据之前，首先将数据备份到一个地方 （这个存储数据备份的地方称为Undo Log）。
undo log主要存储的也是逻辑日志，比如我们要insert一条数据了，那undo log会记录的一条对应的delete日志。我们要update一条记录时，它会记录一条对应相反的update记录。然后进行数据的修改。如果出现了错误或者用户执行了 ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。 - 因为
undo log存储着修改之前的数据，相当于一个前版本，MVCC实现的是读写不阻塞，读的时候只要返回前一个版本的数据就行了。
除了可以保证事务的原子性，Undo Log也可以用来辅助完成事务的持久化。
-
事务的持久性(Durability) 事务一旦完成，该事务对数据库所做的所有修改都会持久的保存到数据库中。为了保证持久性，数据库 系统会将修改后的数据完全的记录到持久的存储上。
-
用Undo Log实现原子性和持久化的事务的简化过程 假设有A、B两个数据，值分别为1,2。 A.事务开始. B.记录A=1到undo log. C.修改A=3. D.记录B=2到undo log. E.修改B=4. F.将undo log写到磁盘。 G.将数据写到磁盘。 H.事务提交 这里有一个隐含的前提条件：‘数据都是先读到内存中，然后修改内存中的数据，最后将数据写回磁盘’。
之所以能同时保证原子性和持久化，是因为以下特点： A. 更新数据前记录Undo log。 B. 为了保证持久性，必须将数据在事务提交前写到磁盘。只要事务成功提交，数据必然已经持久化。 C. Undo log必须先于数据持久化到磁盘。如果在G,H之间系统崩溃，undo log是完整的， 可以用来回滚事务。 D. 如果在A-F之间系统崩溃,因为数据没有持久化到磁盘。所以磁盘上的数据还是保持在事务开始前的状态。
缺陷：每个事务提交前将数据和Undo Log写入磁盘，这样会导致大量的磁盘IO，因此性能很低。
如果能够将数据缓存一段时间，就能减少IO提高性能。但是这样就会丧失事务的持久性。因此引入了另外一 种机制来实现持久化，即Redo Log.
Redo Log
- 什么是redo log?及作用
假设我们有一条sql语句：
update user_table set name='java3y' where id = '3'
MySQL执行这条SQL语句，肯定是先把id=3的这条记录查出来，然后将name字段给改掉。这没问题吧？
实际上Mysql的基本存储结构是页(记录都存在页里边)，所以MySQL是先把这条记录所在的页找到，然后把该页加载到内存中，将对应记录进行修改。
现在就可能存在一个问题：如果在内存中把数据改了，还没来得及落磁盘，而此时的数据库挂了怎么办？显然这次更改就丢了。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kJlgWrTn-1620719083319)(./SQL入门学习/异步.png)]
如果每个请求都需要将数据立马落磁盘之后，那速度会很慢，MySQL可能也顶不住。所以MySQL是怎么做的呢？
MySQL引入了redo log，内存写完了，然后会写一份redo log，这份redo log记载着这次在某个页上做了什么修改。
其实写redo log的时候，也会有buffer，是先写buffer，再真正落到磁盘中的。至于从buffer什么时候落磁盘，会有配置供我们配置。
所以，redo log的存在为了：当我们修改的时候，写完内存了，但数据还没真正写到磁盘的时候。此时我们的数据库挂了，我们可以根据redo log来对数据进行恢复。因为redo log是顺序IO，所以写入的速度很快，并且redo log记载的是物理变化（xxxx页做了xxx修改），文件的体积很小，恢复速度很快。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GIC1j6hZ-1620719083319)(./SQL入门学习/redo_log.png)]
确保事务的持久性，防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。
-
原理 和Undo Log相反，Redo Log记录的是新数据的备份。在事务提交前，只要将Redo Log持久化即可， 不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，但是Redo Log已经持久化。系统可以根据 Redo Log的内容，将所有数据恢复到最新的状态。
-
Undo + Redo事务的简化过程 假设有A、B两个数据，值分别为1,2. A.事务开始. B.记录A=1到undo log. C.修改A=3. D.记录A=3到redo log. E.记录B=2到undo log. F.修改B=4. G.记录B=4到redo log. H.将redo log写入磁盘。 I.事务提交
undo log 保存的是修改前的数据,并且保存到内存中,回滚的时候在读取里面的内容(从而实现了原子性),redolog保存的是修改后的数据(对新数据的备份,同时也会将redo log备份),在事务提交写入到磁盘,从而保证了持久性
慢查询日志
数据库查询快慢是影响项目性能的一大因素，对于数据库，我们除了要优化 SQL，更重要的是得先找到需要优化的 SQL。如何找到低效的 SQL 是写这篇文章的主要目的。
MySQL 数据库有一个“慢查询日志”功能，用来记录查询时间超过某个设定值的SQL，这将极大程度帮助我们快速定位到问题所在，以便对症下药。至于查询时间的多少才算慢，每个项目、业务都有不同的要求，传统企业的软件允许查询时间高于某个值，但是把这个标准放在互联网项目或者访问量大的网站上，估计就是一个bug，甚至可能升级为一个功能性缺陷。
为避免误导读者，特申明本文的讨论限制在 Win 64位 + MySQL 5.6 范围内。其他平台或数据库种类及版本，我没有尝试过，不做赘述。
设置日志功能 关于慢查询日志，主要涉及到下面几个参数：
slow_query_log ：是否开启慢查询日志功能（必填） long_query_time ：超过设定值，将被视作慢查询，并记录至慢查询日志文件中（必填） log-slow-queries ：慢查询日志文件（不可填），自动在 \data\ 创建一个 [hostname]-slow.log 文件 也就是说，只有满足以上三个条件，“慢查询功能”才可能正确开启或关闭。
二进制日志
- 主从复制的基础：binlog日志和relaylog日志
什么是MySQL主从复制 简单来说就是保证主SQL（Master）和从SQL（Slave）的数据是一致性的，向Master插入数据后，Slave会自动从Master把修改的数据同步过来（有一定的延迟），通过这种方式来保证数据的一致性，就是主从复制
复制方式 MySQL5.6开始主从复制有两种方式：基于日志（binlog）、基于GTID（全局事务标示符）。 本文只涉及基于日志binlog的主从配置
复制原理 1、Master将数据改变记录到二进制日志(binary log)中，也就是配置文件log-bin指定的文件，这些记录叫做二进制日志事件(binary log events) 2、Slave通过I/O线程读取Master中的binary log events并写入到它的中继日志(relay log) 3、Slave重做中继日志中的事件，把中继日志中的事件信息一条一条的在本地执行一次，完成数据在本地的存储，从而实现将改变反映到它自己的数据(数据重放)
- 什么是binlog?
binlog是一个二进制格式的文件，用于记录用户对数据库更新的SQL语句信息，例如更改数据库表和更改内容的SQL语句都会记录到binlog里，但是对库表等内容的查询不会记录。
- binlog长什么样子？
binlog我们可以简单理解为：存储着每条变更的SQL语句（当然从下面的图看来看，不止SQL，还有XID「事务Id」等等）默认情况下，binlog日志是二进制格式的，不能使用查看文本工具的命令（比如，cat，vi等）查看，而使用mysqlbinlog解析查看。
- binlog的作用：
- 因为
binlog记录了数据库表的变更，所以我们可以用binlog进行复制（主从复制)和恢复数据。当有数据写入到数据库时，还会同时把更新的SQL语句写入到对应的binlog文件里，这个文件就是上文说的binlog文件。使用mysqldump备份时，只是对一段时间的数据进行全备，但是如果备份后突然发现数据库服务器故障，这个时候就要用到binlog的日志了。 - MySQL在公司使用的时候往往都是一主多从结构的，从服务器需要与主服务器的数据保持一致，这就是通过
binlog来实现的 - 数据库的数据被干掉了，我们可以通过
binlog来对数据进行恢复。
- 因为
binlog和reglog的写入细节
redo log是MySQL的InnoDB引擎所产生的。
binlog无论MySQL用什么引擎，都会有的。
InnoDB是有事务的，事务的四大特性之一：持久性就是靠redo log来实现的（如果写入内存成功，但数据还没真正刷到磁盘，如果此时的数据库挂了，我们可以靠redo log来恢复内存的数据，这就实现了持久性）。
上面也提到，在修改的数据的时候，binlog会记载着变更的类容，redo log也会记载着变更的内容。（只不过一个存储的是物理变化，一个存储的是逻辑变化）。那他们的写入顺序是什么样的呢？
redo log事务开始的时候，就开始记录每次的变更信息，而binlog是在事务提交的时候才记录。
于是新有的问题又出现了：我写其中的某一个log，失败了，那会怎么办？现在我们的前提是先写redo log，再写binlog，我们来看看：
- 如果写
redo log失败了，那我们就认为这次事务有问题，回滚，不再写binlog。 - 如果写
redo log成功了，写binlog，写binlog写一半了，但失败了怎么办？我们还是会对这次的事务回滚，将无效的binlog给删除（因为binlog会影响从库的数据，所以需要做删除操作） - 如果写
redo log和binlog都成功了，那这次算是事务才会真正成功。
简单来说：MySQL需要保证redo log和binlog的数据是一致的，如果不一致，那就乱套了。
- 如果
redo log写失败了，而binlog写成功了。那假设内存的数据还没来得及落磁盘，机器就挂掉了。那主从服务器的数据就不一致了。（从服务器通过binlog得到最新的数据，而主服务器由于redo log没有记载，没法恢复数据） - 如果
redo log写成功了，而binlog写失败了。那从服务器就拿不到最新的数据了。
MySQL通过两阶段提交来保证redo log和binlog的数据是一致的。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hDlDfJGp-1620719083320)(./SQL入门学习/relogbinlog.png)]
redo log日志与binlog日志的区别
1、redo log是在InnoDB存储引擎层产生，而binlog是MySQL数据库的上层产生的，并且二进制日志不仅仅针对INNODB存储引擎，MySQL数据库中的任何存储引擎对于数据库的更改都会产生二进制日志。
2、两种日志记录的内容形式不同。MySQL的binlog是逻辑日志，其记录是对应的SQL语句。而innodb存储引擎层面的重做日志是物理日志。
3、两种日志与记录写入磁盘的时间点不同，二进制日志只在事务提交完成后进行一次写入。而innodb存储引擎的重做日志在事务进行中不断地被写入，并日志不是随事务提交的顺序进行写入的。二进制日志仅在事务提交时记录，并且对于每一个事务，仅在事务提交时记录，并且对于每一个事务，仅包含对应事务的一个日志。而对于innodb存储引擎的重做日志，由于其记录是物理操作日志，因此每个事务对应多个日志条目，并且事务的重做日志写入是并发的，并非在事务提交时写入，其在文件中记录的顺序并非是事务开始的顺序。
4、binlog不是循环使用，在写满或者重启之后，会生成新的binlog文件，redo log是循环使用。
5、binlog可以作为恢复数据使用，主从复制搭建，redo log作为异常宕机或者介质故障后的数据恢复使用。
锁。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MuVb864k-1620719083320)(./SQL入门学习/锁.png)]
即使我们不会这些锁知识，我们的程序在一般情况下还是可以跑得好好的。因为这些锁数据库隐式帮我们加了
- 对于
UPDATE、DELETE、INSERT语句，InnoDB会自动给涉及数据集加排他锁（X) - MyISAM在执行查询语句
SELECT前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预
首先，从锁的粒度，我们可以分成两大类：
- 表锁
- 开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低
- 行锁
- 开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高
不同的存储引擎支持的锁粒度是不一样的：
- InnoDB行锁和表锁都支持！
- MyISAM只支持表锁！
InnoDB只有通过索引条件检索数据才使用行级锁，否则，InnoDB将使用表锁
- 也就是说，InnoDB的行锁是基于索引的！
表锁
表读锁（Table Read Lock）
表写锁（Table Write Lock）
在表读锁和表写锁的环境下：读读不阻塞，读写阻塞，写写阻塞
- 读读不阻塞：当前用户在读数据，其他的用户也在读数据，不会加锁
- 读写阻塞：当前用户在读数据，其他的用户不能修改当前用户读的数据，会加锁！
- 写写阻塞：当前用户在修改数据，其他的用户不能修改当前用户正在修改的数据，会加锁！
从上面已经看到了：读锁和写锁是互斥的，读写操作是串行。
-
如果某个进程想要获取读锁，同时另外一个进程想要获取写锁。在mysql里边，写锁是优先于读锁的！
-
MyISAM写锁和读锁优先级的问题是可以通过参数调节的：
max_write_lock_count和low-priority-updates -
MyISAM可以支持查询和插入操作的并发进行。可以通过系统变量
concurrent_insert来指定哪种模式，在MyISAM中它默认是：如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。但是InnoDB存储引擎是不支持的！
max_write_lock_count：
缺省情况下，写操作的优先级要高于读操作的优先级，即便是先发送的读请求，后发送的写请求，此时也会优先处理写请求，然后再处理读请求。这就造成一个问题：一旦我发出若干个写请求，就会堵塞所有的读请求，直到写请求全都处理完，才有机会处理读请求。此时可以考虑使用 max_write_lock_count：
max_write_lock_count=1
有了这样的设置，当系统处理一个写操作后，就会暂停写操作，给读操作执行的机会。
low-priority-updates：
我们还可以更干脆点，直接降低写操作的优先级，给读操作更高的优先级。
low-priority-updates=1
concurrent_insert：
通常来说，在MyISAM里读写操作是串行的，但当对同一个表进行查询和插入操作时，为了降低锁竞争的频率，根据concurrent_insert的设置，MyISAM是可以并行处理查询和插入的：
当concurrent_insert=0时，不允许并发插入功能。
当concurrent_insert=1时，允许对没有洞洞的表使用并发插入，新数据位于数据文件结尾（缺省）。
当concurrent_insert=2时，不管表有没有洞洞，都允许在数据文件结尾并发插入。
这样看来，把concurrent_insert设置为2是很划算的，至于由此产生的文件碎片，可以定期使用OPTIMIZE TABLE语法优化。
行锁
上边简单讲解了表锁的相关知识，我们使用Mysql一般是使用InnoDB存储引擎的。InnoDB和MyISAM有两个本质的区别：
- InnoDB支持行锁
- InnoDB支持事务
从上面也说了：我们是很少手动加表锁的。表锁对我们程序员来说几乎是透明的，即使InnoDB不走索引，加的表锁也是自动的！
我们应该更加关注行锁的内容，因为InnoDB一大特性就是支持行锁！
InnoDB实现了以下两种类型的行锁。
- 共享锁（S锁）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
- 也叫做读锁：读锁是共享的，多个客户可以同时读取同一个资源，但不允许其他客户修改。
- 排他锁（X锁)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
- 也叫做写锁：写锁是排他的，写锁会阻塞其他的写锁和读锁。
看完上面的有没有发现，在一开始所说的：X锁，S锁，读锁，写锁，共享锁，排它锁其实总共就两个锁，只不过它们有多个名字罢了~~~
select ... lock in share mode: 会加共享锁(即下文中的Shared Lock)select ... for update: 会加排它锁
意向锁
为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：
- 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
- 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。
- 意向锁也是数据库隐式帮我们做了，不需要程序员操心！
Record Locks记录锁
行锁，顾名思义，是加在索引行(对！是索引行！不是数据行！)上的锁。比如select * from user where id=1 and id=10 for update，就会在id=1和id=10的索引行上加Record Lock
Gap Locks间隙锁
间隙锁，它会锁住两个索引之间的区域。比如select * from user where id>1 and id<10 for update，就会在id为(1,10)的索引区间上加Gap Lock
Next-Key Locks临键锁
也叫间隙锁，它是Record Lock + Gap Lock形成的一个闭区间锁。比如select * from user where id>=1 and id<=10 for update，就会在id为[1,10]的索引闭区间上加Next-Key Lock
悲观锁
当我们要对一个数据库里的一条数据进行修改的时候，为了避免被其他人修改，最好的方法是直接对数据库加锁以防并发。这种借助数据库的锁机制，在修改数据之前先锁定，再修改的方式被称之为悲观锁。
所以，按照上面的例子。我们使用悲观锁的话其实很简单(手动加行锁就行了)：
select * from xxxx for update
在select 语句后边加了 for update相当于加了排它锁(写锁)，加了写锁以后，其他的事务就不能对它修改了！需要等待当前事务修改完之后才可以修改.
乐观锁
乐观锁假设一般情况下不会造成冲突，所以在数据提交更新的时候，才会对数据的冲突与否进行检测，如果发生冲突了，则返回给用户错误信息，让用户决定怎么去做。
乐观锁不是数据库层面上的锁，是需要自己手动去加的锁。一般我们添加一个版本字段来实现：
具体过程是这样的：
张三select * from table —>会查询出记录出来，同时会有一个version字段
| ID | name | version |
|---|---|---|
| 1 | zhangsan | 1 |
李四select * from table —>会查询出记录出来，同时会有一个version字段
| ID | name | version |
|---|---|---|
| 1 | zhangsan | 1 |
李四对这条记录做修改：update A set Name=lisi,version=version+1 where ID=#{id} and version=#{version}，判断之前查询到的version与现在的数据的version进行比较，同时会更新version字段
此时数据库记录如下：
| ID | name | version |
|---|---|---|
| 1 | lisi | 2 |
张三也对这条记录修改：update A set Name=lisi,version=version+1 where ID=#{id} and version=#{version}，但失败了！因为当前数据库中的版本跟之前查询出来的版本不一致！
MVCC和事务的隔离级别
数据库事务有不同的隔离级别，不同的隔离级别对锁的使用是不同的，锁的应用最终导致不同事务的隔离级别
MVCC(Multi-Version Concurrency Control)多版本并发控制，可以简单地认为：MVCC就是行级锁的一个变种(升级版)。
- 事务的隔离级别就是通过锁的机制来实现，只不过隐藏了加锁细节
在表锁中我们读写是阻塞的，基于提升并发性能的考虑，MVCC一般读写是不阻塞的(所以说MVCC很多情况下避免了加锁的操作)
- MVCC实现的读写不阻塞正如其名：多版本并发控制—>通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot)，并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度来看，好像是数据库可以提供同一数据的多个版本。
快照有两个级别：
- 语句级
- 针对于
Read committed隔离级别
- 针对于
- 事务级别
- 针对于
Repeatable read隔离级别
- 针对于
MVCC原理
InnoDB的MVCC，是通过每行记录后边保存的两个隐藏列来实现的。一个保存了行的穿件时间，一个保存行的过期时间。当然实际存储的并不是实际的时间，而是系统版本号。每开始一个新的事务，系统版本号都会自动递增，事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号比较。看一下repeeated read 隔离级别下，MVCC具体如何工作。
- select：InnoDB根据以下两个条件检查每行记录
- InnoDB只查找版本早于当前事务版本的数据行（也就是，行的系统版本号小于等于当前事务的版本号，）这样可以确保事务读取的行，要么是在事务开始之前存在的，要么是事务自身插入或者修改过的。
- 行的删除版本要么之前未定义，要么大于当前事务的版本号。这样确保事务读取到的行，在事务开始之前没有删除。
- insert:InnoDB为新的插入的每一行保存当前的系统把呢本好作为当前行的版本号。
- delete:InnoDB为删除的每一行保存当前系统的版本号作为行删除标识。
- updata:InnoDB为插入一行新的记录，保存当前的系统版本号作为行版本号，同时作为行删除标识。
保存这两个额外的版本号，使得大多数读操作都可以不用加锁。这样设计使得读数据操作很简单，性能更好，并且也能保证只会读到符合标准的行。不足之处是每行记录都需要额外的空间，需要更多的行检查工作，以及一些额外的维护工作。
知乎大佬解释为
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xxGElRK7-1620719083321)(./SQL入门学习/MVCCzh.png)]
隔离
我们在初学的时候已经知道，事务的隔离级别有4种：
- Read uncommitted
- 会出现脏读，不可重复读，幻读
- Read committed
- 会出现不可重复读，幻读
- Repeatable read
- 会出现幻读(但在Mysql实现的Repeatable read配合gap锁不会出现幻读！)
- Serializable
- 串行，避免以上的情况！
Read uncommitted
会出现的现象—>脏读：一个事务读取到另外一个事务未提交的数据
- 例子：A向B转账，A执行了转账语句，但A还没有提交事务，B读取数据，发现自己账户钱变多了！B跟A说，我已经收到钱了。A回滚事务【rollback】，等B再查看账户的钱时，发现钱并没有多。
- 出现脏读的本质就是因为操作(修改)完该数据就立马释放掉锁，导致读的数据就变成了无用的或者是错误的数据。
READ COMMITTED
既然读未提交有那么大的数据可靠性问题，那就往前迈一小步，读已提交。该级别下将锁的释放时机延迟到事务提交之后，从而实现了读已提交，解决了脏读
但是！好像哪里不太对？！锁的释放时机延迟了，写与写操作之间产生锁竞争就算了，那在锁释放之前，读也不能读了吗？这并发性能不能忍！这时就该MVCC出马了，既然不想阻塞等待最新的数据，那就无视当前持有锁的操作，读取最新的历史版本数据先用着
因此，在读已提交的级别下，每次select时都会通过MVCC获取当前数据的最新快照，不加任何锁，也无视任何锁(因为历史数据是构造出来的，身上不可能有锁)，完美解决读写之间的并发问题，和READ UNCOMMITTED的并发性能只差在写写操作上
而为了进一步提升写写操作上的并发性能，该级别下不会使用前文提到的间隙锁，无论什么查询都只会加行锁，而且在执行完WHERE条件筛选之后，会立即释放掉不符合条件的行锁，对于并发性能的追求可谓仁至义尽了
但是，正因为对并发性能的极致追求或者说贪婪，该级别下还是遗留了不可重复读和幻读问题：
- MVCC版本的生成时机: 是每次select时，这就意味着，如果我们在事务A中执行多次的select，在每次select之间有其他事务更新了我们读取的数据并提交了，那就出现了不可重复读
- 锁的范围: 因为没有间隙锁，这就意味着，如果我们在事务A中多次执行
select * from user where age>18 and age<30 for update时，其他事务是可以往age为(18,30)这个区间插入/删除数据的，那就出现了幻读
REPEATABLE READ
既然读已提交依然有较大的数据可靠性能问题，那就再往前迈一小步，可重复读，该级别在读已提交的基础上做了两点修改，从而避免了不可重复读和幻读：
- MVCC版本的生成时间: 一次事务中只在第一次select时生成版本，后续的查询都是在这个版本上进行，从而实现了可重复读
锁的范围: 在行锁的基础上，加上Gap Lock，从而形成Next-Key Lock，在所有遍历过的(不管是否匹配条件)索引行上以及之间的区域上，都加上锁，阻塞其他事务在遍历范围内进行写操作，从而避免了幻读
看似很完美了对吧，并发性能上、读读、读写操作依旧两不误，写写操作为了数据可靠性做了妥协也是能接受的，皆大欢喜？
图样图森破！这个世界怕什么？猪队友啊！InnoDB在可重复读级别下已经将数据可靠性和并发性能两方面做得尽善尽美了，但前提是用户查询时能够主动善用Locking Reads，即前文提到的select ... lock in share mode和select ... for update。如果只是使用普通的select，依然防不住幻读
这是因为MVCC的快照只对读操作有效，对写操作无效，举例说明会更清晰一点： 事务A依次执行如下3条sql，事务B在语句1和2之间，插入10条age=20的记录，事务A就幻读了
1\. select count(1) from user where age=20;
-- return 0: 当前没有age=20的
2\. update user set name=test where age=20;
-- Affects 10 rows: 因为事务B刚写入10条age=20的记录，而写操作是不受MVCC影响，能看到最新数据的，所以更新成功，而一旦操作成功，这些被操作的数据就会对当前事务可见
3\. select count(1) from user where age=20;
-- return 10: 出现幻读
这种场景，需要用户主动使用Locking Read来防止其他事务在查询范围内进行写操作，因此，为了防患于未然，隔离级别又往前迈了一步
SERIALISABLE
大杀器，该级别下，会自动将所有普通select转化为select ... lock in share mode执行，即针对同一数据的所有读写都变成互斥的了，可靠性大大提高，并发性大大降低
机智如你可能会问，那可重复读级别下使用Locking Read不也变成读写互斥了嘛，那这两个有什么区别呢？可重复读你可以自己选择是否使用Locking Read呀，艺高人胆大可以使用普通的select读写并发的嘛
| 隔离级别 | MVCC版本生成时机 | 写操作释放锁的时机 | 锁的范围 | 丢失修改 | 脏读 | 不可重复度 | 幻读 |
|---|---|---|---|---|---|---|---|
| READ UNCOMMITTED | / | SQL执行完立即释放 | 行锁 | √ | √ | √ | √ |
| READ COMMITTED | 每次select时 | 事务结束后 | 行锁 | √ | √ | ||
| REPEATABLE READ | 事务第一次select时 | 事务结束后 | 行锁或间隙锁 | 特定情况下 | |||
| SERIALIZABLE | 事务第一次select时 | 事务结束后 | 行锁或间隙锁 |
874
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
