数据库笔记
(学习完廖雪峰老师的教程后写的笔记。)
第二篇补充的笔记地址:https://blog.csdn.net/m0_47812755/article/details/108801826
一、关系数据库概述
1.数据模型
层次模型
网状模型
关系模型
2.数据类型
名称 | 类型 | 说明 |
---|---|---|
INT | 整型 | 4字节整数类型,范围约+/-21亿 |
BIGINT | 长整型 | 8字节整数类型,范围约+/-922亿亿 |
REAL FLOAT(别名) | 浮点型 | 4字节浮点数,范围约+/-1038 |
DOUBLE | 浮点型 | 8字节浮点数,范围约+/-10308 |
DECIMAL(M,N) decimal (小写) | 高精度小数 | 由用户指定精度的小数,例如,DECIMAL(20,10)表示一共20位,其中小数10位,通常用于财务计算 |
CHAR(N) | 定长字符串 | 存储指定长度的字符串,例如,CHAR(100)总是存储100个字符的字符串 |
VARCHAR(N) | 变长字符串 | 存储可变长度的字符串,例如,VARCHAR(100)可以存储0~100个字符的字符串 |
BOOLEAN | 布尔类型 | 存储True或者False |
DATE | 日期类型 | 存储日期,例如,2018-06-22 |
TIME | 时间类型 | 存储时间,例如,12:20:59 |
DATETIME | 日期和时间类型 | 存储日期+时间,例如,2018-06-22 12:20:59 |
3.主流关系型数据库
- 商用数据库,例如:Oracle,SQL Server,DB2等;
- 开源数据库,例如:MySQL,PostgreSQL等;
- 桌面数据库,以微软Access为代表,适合桌面应用程序使用;
- 嵌入式数据库,以Sqlite为代表,适合手机应用和桌面程序。
二、SQL概念
结构化查询语言。用来访问和操作数据库系统。
不同的数据库,都支持SQL。
1.SQL语言操作数据库的能力:
DDL:Data Definition Language
DDL允许用户定义数据,也就是创建表、删除表、修改表结构这些操作。通常,DDL由数据库管理员执行。
DML:Data Manipulation Language
DML为用户提供添加、删除、更新数据的能力,这些是应用程序对数据库的日常操作。
DQL:Data Query Language
DQL允许用户查询数据,这也是通常最频繁的数据库日常操作。
2.SQL的全称
- Strange Question Language
- Structured Question Language
- Structured Query Language
三、关系模型
1.主键
能够通过某个字段唯一区分出不同的记录。常常用 id 来作为主键。
2.联合主键
通过多个字段唯一标识记录,即两个或更多的字段都设置为主键,这种主键被称为联合主键。
id_num | id_type | other columns… |
---|---|---|
1 | A | … |
2 | A | … |
2 | B | … |
非必要,不使用。因为联合主键会让数据库复杂度上升。
3.外键
把数据通过一个字段和另一张表联系起来。
实现方式
外键并不是通过列名实现的,而是通过定义外键约束实现的:
ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id) -- 指定了class_id作为外键
REFERENCES classes (id); -- 指定了这个外键将关联到classes表的id列
外键利弊
- 定义外键可以保证无法插入无效的数据。即如果
classes
表不存在id=99
的记录,students
表就无法插入class_id=99
的记录。 - 外键约束会降低数据库的性能
删除外键
删除一个外键约束,ALTER TABLE`
ALTER TABLE students
DROP FOREIGN KEY fk_class_id;
注意:删除外键约束并没有删除外键这一列。删除列是通过DROP COLUMN ...
实现的。
4.多对多
通过一个中间表,关联两个一对多的表。
5.一对一
例如,students
表的每个学生可以有自己的联系方式,如果把联系方式存入另一个表contacts
。即一对一。
- 之所以不把联系方式这个字段直接加到
students
表中,是想提高查询速度。 - 有些大表会拆分成两个一对一的表,把不常用的信息分离出来,提高性能。
例如,把一个大的用户表分拆为用户基本信息表user_info
和用户详细信息表user_profiles
,大部分时候,只需要查询user_info
表,并不需要查询user_profiles
表,这样就提高了查询速度。
6.索引
索引是关系数据库中对某一列或多个列的值进行 预排序 的数据结构。
注意:是预排序!
例子:
对于students
表:如果要经常根据score
列进行查询,就可以对score
列创建索引:
ALTER TABLE students
ADD INDEX idx_score (score);
ADD INDEX idx_name_score (name, score); -- 多个索引
使用ADD INDEX idx_score (score)
就创建了一个名称为idx_score
,使用列score
的索引。索引名称是任意的,索引如果有多列,可以在括号里依次写上。
创建多个索引
优点:
提高了查询效率
缺点:
在插入、更新和删除记录时,需要同时修改索引,因此,索引越多,插入、更新和删除记录的速度就越慢。
唯一索引
UNIQUE
关键字
ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);
也可以只对某一列添加一个唯一约束而不创建唯一索引:
ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);
这种情况下,name
列没有索引,但仍然具有唯一性保证。
四、查询
1.基本查询
SELECT * FROM <表名> -- 获得表的所有记录。
SELECT
是关键字,表示将要执行一个查询,*
表示“所有列”,FROM
表示将要从哪个表查询
SELECT 1001+200; -- 也可以当计算机使用。
SELECT 1; -- 测试数据库连接情况。
2.条件查询
可以通过WHERE
条件来设定查询条件
关键字:and、or、not
SELECT * FROM <表名> WHERE <条件表达式>
-- 例如:
SELECT * FROM students WHERE score >= 80; -- WHERE score >= 80; 就是条件
SELECT * FROM students WHERE score >= 80 AND gender = 'M'; -- 多条件查询中间用and连接。字符串用括号包起来。
SELECT * FROM students WHERE score >= 80 OR gender = 'M'; -- or 关键字
SELECT * FROM students WHERE NOT class_id = 2; -- not 关键字
-- 组合条件查询 用小括号()表示如何进行条件运算
SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';
如果不加括号,会按照NOT
、AND
、OR
的优先级进行。
3.常用的条件表达式
条件 | 表达式举例1 | 表达式举例2 | 说明 |
---|---|---|---|
使用=判断相等 | score = 80 | name = ‘abc’ | 字符串需要用单引号括起来 |
使用>判断大于 | score > 80 | name > ‘abc’ | 字符串比较根据ASCII码,中文字符比较根据数据库设置 |
使用>=判断大于或相等 | score >= 80 | name >= ‘abc’ | |
使用<判断小于 | score < 80 | name <= ‘abc’ | |
使用<=判断小于或相等 | score <= 80 | name <= ‘abc’ | |
使用<>判断不相等 | score <> 80 | name <> ‘abc’ | |
使用LIKE判断相似 | name LIKE ‘ab%’ | name LIKE ‘%bc%’ | %表示任意字符,例如’ab%‘将匹配’ab’,‘abc’,‘abcd’ |
例题:
-
查询分数在60分(含)~90分(含)之间的学生可以使用的WHERE语句是:
-
WHERE score >= 60 OR score <= 90
-
WHERE score >= 60 AND score <= 90
-
WHERE score IN (60, 90)
-
WHERE score BETWEEN 60 AND 90
-
WHERE 60 <= score <= 90
4.投影查询
SELECT 列1, 列2, 列3 FROM ...
让结果集仅包含指定列
SELECT id, score, name FROM students;
还可以更改指定列的列名SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM ...
SELECT id, score points, name FROM students; -- 逗号前加别名
SELECT id, score points, name FROM students WHERE score >= 90; -- 加WHERE 条件。
5.排序
ORDER BY
SELECT id, name, gender, score FROM students ORDER BY score;
SELECT id, name, gender, score FROM students ORDER BY score; -- 可以加上DESC表示“倒序”:
如果score
列有相同的数据,要进一步排序,可以继续添加列名。例如,使用ORDER BY score DESC, gender
表示先按score
列倒序,如果有相同分数的,再按gender
列排序:
SELECT id, name, gender, score FROM students ORDER BY score DESC, gender; -- 加逗号分隔。
默认的排序规则是ASC
:“升序。可以省略。
SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC; -- 有WHERE,ORDER BY子句要放到WHERE子句后面。
6.分页查询
LIMIT <M> OFFSET <N>
- 分页查询需要先确定每页的数量和当前页数,然后确定
LIMIT
和OFFSET
的值。 - 每页需要显示的结果数量
pageSize
- 当前页的索引
pageIndex
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0; -- 对结果集从0号记录开始,最多取3条
LIMIT 3 OFFSET 3; -- 从第二页开始看,也就是跳过第一页的三个。
LIMIT
总是设定为pageSize
;OFFSET
计算公式为pageSize * (pageIndex - 1)
。OFFSET
超过了查询的最大数量并不会报错,而是得到一个空的结果集。OFFSET
可以省略。省略时相当于0:LIMIT 15 OFFSET 0
。- 在MySQL中,
LIMIT 15 OFFSET 30
还可以简写成LIMIT 30, 15
。
7.聚合查询
使用聚合函数查询就是聚合查询。
SELECT COUNT(*) FROM students; -- 总数
聚合函数:
COUNT(*)
查询所有列的行数
SELECT COUNT(*) num FROM students; --通常,使用聚合查询时,我们应该给列名设置一个别名,便于处理结果。
SELECT COUNT(*) boys FROM students WHERE gender = 'M'; -- 也可以使用WHERE
函数 | 说明 |
---|---|
SUM | 计算某一列的合计值,该列必须为数值类型 |
AVG | 计算某一列的平均值,该列必须为数值类型 |
MAX | 计算某一列的最大值 |
MIN | 计算某一列的最小值 |
- 注意,
MAX()
和MIN()
函数并不限于数值类型。如果是字符类型,MAX()
和MIN()
会返回排序最后和排序最前的字符。 - 如果聚合查询的
WHERE
条件没有匹配到任何行,COUNT()
会返回0,而SUM()
、AVG()
、MAX()
和MIN()
会返回NULL
:
例题:
-
每页3条记录,如何通过聚合查询获得总页数?
-
SELECT COUNT(*) / 3 FROM students;
-
SELECT FLOOR(COUNT(*) / 3) FROM students;
-
SELECT CEILING(COUNT(*) / 3) FROM students;
分组
-- 笨方法:
SELECT COUNT(*) num FROM students WHERE class_id = 1 -- 需要所有班级挨个更改WHERE
-- 分组方法
SELECT COUNT(*) num FROM students GROUP BY class_id;
SELECT class_id, COUNT(*) num FROM students GROUP BY class_id; -- 加入class_id ,结果更直观。
查询结果:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TM4jssdc-1601000824001)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20200923182544008.png)]
加入class_id后的结果:[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lj100ZcW-1601000824011)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20200923183007365.png)]
GROUP BY
子句指定了按class_id
分组。查询时会先按class_id
分组,再分别计算。
使用多个列进行分组
SELECT class_id, gender ,COUNT(*) num FROM students GROUP BY class_id, gender;
-- 前面两个字段,后面必须是两个字段。否则会报错。
- 前面两个字段,后面必须是两个字段。否则会报错。
练习
- 请使用一条SELECT查询查出每个班级的平均分:查出每个班级的平均分,结果集应当有3条记录:
- 请使用一条SELECT查询查出每个班级男生和女生的平均分:
8.多表查询
SELECT * FROM <表1> <表2>
也叫笛卡尔查询。返回两个表的乘积。
SELECT * FROM students, classes;
为了避免相同列名不好区分,可以给列名加别名。
SELECT
students.id sid,
students.name,
students.gender,
students.score,
classes.id cid,
classes.name cname
FROM students, classes;
可以简写为:
```直接给表起别名。相比给列起别名要简洁一些。
SELECT
s.id sid,
s.name,
s.gender,
s.score,
c.id cid,
c.name cname
FROM students s, classes c; -- FROM <表名1> <别名1>, <表名2> <别名2>
WHERE s.gender = 'M' AND c.id = 1; -- 也可以使用WHERE.
9.连接查询
- 连接查询对多个表进行JOIN运算。
- 就是先确定一个主表作为结果集,然后,把其他表的行有选择性地“连接”在主表结果集上。
INNER JOIN
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
INNER JOIN classes c
ON s.class_id = c.id;
INNER JOIN查询的写法是:
- 先确定主表,仍然使用
FROM <表1>
的语法; - 再确定需要连接的表,使用
INNER JOIN <表2>
的语法; - 然后确定连接条件,使用
ON <条件...>
,这里的条件是s.class_id = c.id
,表示students
表的class_id
列与classes
表的id
列相同的行需要连接; - 可选:加上
WHERE
子句、ORDER BY
等子句。
OUTER JOIN
INNER JOIN只返回同时存在于两张表的行数据。
RIGHT OUTER JOIN返回右表都存在的行。如果某一行仅在右表存在,那么结果集就会以NULL
填充剩下的字段。
LEFT OUTER JOIN则返回左表都存在的行。
FULL OUTER JOIN 把两张表的所有记录全部选择出来,并且,自动把对方不存在的列填充为NULL。
五、修改数据
1.INSERT 插入
INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);
INSERT INTO students (class_id, name, gender, score) VALUES (2, '大牛', 'M', 80);
-- 一次性插入多条数据
INSERT INTO students (class_id, name, gender, score) VALUES
(1, '大宝', 'M', 87),
(2, '二宝', 'M', 81);
SELECT * FROM students;
2.UPDATE 更新
UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;
例子:
更新students
表id=1
的记录的name
和score
这两个字段,先写出UPDATE students SET name='大牛', score=66
,然后在WHERE
子句中写出需要更新的行的筛选条件id=1
:
UPDATE students SET name='大牛', score=66 WHERE id=1;
UPDATE students SET name='小牛', score=77 WHERE id>=5 AND id<=7; -- 一次更新多条记录
结果如下:
满足WHERE
条件的全部更新。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-28qRd6yW-1601000824014)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20200923223637148.png)]
使用表达式更新
UPDATE students SET score=score+10 WHERE score<80;
- 如果
WHERE
条件没有匹配到任何记录,UPDATE
语句不会报错,也不会有任何记录被更新。 UPDATE
语句可以没有WHERE
条件。这时会更新表的所有记录。- 在执行
UPDATE
时,最好先用SELECT
语句来测试WHERE
条件是否筛选出了期望的记录集,然后再用UPDATE
更新。
MySQL
UPDATE
语句会返回更新的行数以及WHERE
条件匹配的行数。
例如,更新id=1
的记录时:
mysql> UPDATE students SET name='大宝' WHERE id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
3.DELETE 删除
DELETE FROM <表名> WHERE ...;
例如:删除students
表中id=1
的记录。
DELETE FROM students WHERE id=1 ;
一次删除多条
DELETE FROM students WHERE id>=5 AND id<=7;
- 如果
WHERE
条件没有匹配到任何记录,DELETE
语句不会报错,也不会有任何记录被删除。 - 和
UPDATE
类似,不带WHERE
条件的DELETE
语句会删除整个表的数据。 - 同
UPDATE
最好先用SELECT
语句来测试WHERE
条件是否筛选出了期望的记录集,然后再用DELETE
删除。
DELETE FROM students; -- 删除整个表的全部记录
MySQL
DELETE
语句也会返回删除的行数以及WHERE
条件匹配的行数。
六、MySQL
1.连接数据库
mysql -u root -p
mysql -h 10.0.1.99 -u root -p -- 连接远程数据库。
exit
2.关系图
ySQL Client的可执行程序是mysql,MySQL Server的可执行程序是mysqld。
┌──────────────┐ SQL ┌──────────────┐
│ MySQL Client │───────>│ MySQL Server │
└──────────────┘ TCP └──────────────┘
命令行程序mysql
实际上是MySQL客户端,真正的MySQL服务器程序是mysqld
,在后台运行。
可视化图形界面MySQL Workbench
3.常用命令
1.列出所有数据库
SHOW DATABASES; -- 列出所有数据库
information_schema
、mysql
、performance_schema
和sys
是系统库。
2.创建一个新数据库
CREATE DATABASE test;
3.删除一个数据库
DROP DATABASE test;
- 删除一个数据库将导致该数据库的所有表全部被删除。
4.选择数据库
USE test;
5.列出当前数据库的所有表
SHOW TABLES;
6.查看一个表的结构
DESC students;
7.查看创建表的SQL语句
SHOW CREATE TABLE students;
执行结果:
students | CREATE TABLE `students` ( |
| | `id` bigint(20) NOT NULL AUTO_INCREMENT, |
| | `class_id` bigint(20) NOT NULL, |
| | `name` varchar(100) NOT NULL, |
| | `gender` varchar(1) NOT NULL, |
| | `score` int(11) NOT NULL, |
| | PRIMARY KEY (`id`) |
| | ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 |
8.创建表,删除表
CREATE TABLE -- 创建
DROP TABLE -- 删除
9.修改表
加列(add column)
如果要给students
表新增一列birth
:
ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;
alter table xx add column xx …… -- 小写方便认单词
改列(change column)
修改birth
列,例如把列名改为birthday
,类型改为VARCHAR(20)
:
ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;
alter table xx change column xx …… -- 小写方便认单词
删除列(DROP COLUMN)
ALTER TABLE students DROP COLUMN birthday;
10.退出
EXIT
EXIT
仅仅断开了客户端和服务器的连接,MySQL服务器仍然继续运行。
七、实用SQL语句
1.插入或替换(REPLACE INTO)
插入一条新记录(INSERT),如果记录已经存在,先删除,再插入。
REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
replace into -- 小写方便认单词
2.插入或更新(INSERT INTO ... ON DUPLICATE KEY UPDATE ...)
插入一条新记录(INSERT),如果记录已经存在,就更新。
INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;
insert into duplicate
-- 小写方便认单词
3.插入或忽略(INSERT IGNORE INTO)
插入一条新记录(INSERT),如果记录已经存在,直接忽略。
若不存在,即插入,若存在,则无视本条命令。
INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
insert ignore into -- 小写方便认单词
4.快照(CREATE TABLE..SELECT)
复制一份当前表的数据到一个新表
CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;
-- 创建一个名为students_of_class1的新表,从students复制内容,where class_id=1;
- 新创建的表结构和
SELECT
使用的表结构完全一致。
5.写入查询结果集(INSERT INTO...SELECT)
如果查询结果集需要写入到表中,可以结合INSERT
和SELECT
,将SELECT
语句的结果集直接插入到指定表中。
例子:
- 例如,创建一个统计成绩的表
statistics
,记录各班的平均成绩:
CREATE TABLE statistics (
id BIGINT NOT NULL AUTO_INCREMENT,
class_id BIGINT NOT NULL,
average DOUBLE NOT NULL,
PRIMARY KEY (id)
);
- 用一条语句写入各班的平均成绩:
INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;
确保INSERT
语句的列和SELECT
语句的列能一一对应,就可以在statistics
表中直接保存查询的结果:
> SELECT * FROM statistics;
+----+----------+--------------+
| id | class_id | average |
+----+----------+--------------+
| 1 | 1 | 86.5 |
| 2 | 2 | 73.666666666 |
| 3 | 3 | 88.333333333 |
+----+----------+--------------+
3 rows in set (0.00 sec)
6.强制使用指定索引(FORCE INDEX)
SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;
force index -- 小写方便认单词
指定索引的前提是索引idx_class_id
必须存在。
八、事务
把多条语句作为一个整体进行操作的功能,被称为数据库事务。
- 多条语句变为原子操作。
1.特性
数据库事务具有ACID这4个特性:
- A:Atomic,原子性,将所有SQL作为原子工作单元执行,要么全部执行,要么全部不执行;
- C:Consistent,一致性,事务完成后,所有数据的状态都是一致的,即A账户只要减去了100,B账户则必定加上了100;
- I:Isolation,隔离性,如果有多个事务并发执行,每个事务作出的修改必须与其他事务隔离;
- D:Duration,持久性,即事务完成后,对数据库数据的修改被持久化存储。
单条SQL语句也是一个事务。——隐式事务
。
2.语法
显式事务
开启事务:BEGIN
提交事务:COMMIT
例如,转账操作作为一个显式事务:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
COMMIT
是指提交事务,即试图把事务内的所有SQL所做的修改永久保存。如果COMMIT
语句执行失败了,整个事务也会失败。
回滚事务:ROLLBACK
有些时候,我们希望主动让事务失败,这时,可以用ROLLBACK
回滚事务,整个事务会失败:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK;
隔离级别
对于两个并发执行的事务,如果涉及到操作同一条记录的时候,可能会发生问题。因为并发操作会带来数据的不一致性,
SQL标准4种隔离级别:
Isolation Level | 脏读(Dirty Read) | 不可重复读(Non Repeatable Read) | 幻读(Phantom Read) |
---|---|---|---|
Read Uncommitted | Yes | Yes | Yes |
Read Committed | - | Yes | Yes |
Repeatable Read | - | - | Yes |
Serializable | - | - | - |
Read Uncommitted(最低等级)
一个事务会读到另一个事务更新后但未提交的数据,如果另一个事务回滚,那么当前事务读到的数据就是脏数据,这就是脏读(Dirty Read)。
例子:
- 首先,准备好
students
表的数据,该表仅一行记录:
mysql> select * from students;
+----+-------+
| id | name |
+----+-------+
| 1 | Alice |
+----+-------+
1 row in set (0.00 sec)
- 分别开启两个MySQL客户端连接,按顺序依次执行事务A和事务B:
时刻 | 事务A | 事务B |
---|---|---|
1 | SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; | SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; |
2 | BEGIN; | BEGIN; |
3 | UPDATE students SET name = ‘Bob’ WHERE id = 1; | |
4 | SELECT * FROM students WHERE id = 1; | |
5 | ROLLBACK; | |
6 | SELECT * FROM students WHERE id = 1; | |
7 | COMMIT; |
- 当事务A执行完第3步时,它更新了
id=1
的记录,但并未提交,而事务B在第4步读取到的数据就是未提交的数据。随后,事务A在第5步进行了回滚,事务B再次读取id=1
的记录,发现和上一次读取到的数据不一致,这就是脏读。
Read Committed
不可重复读。
在Read Committed
隔离级别下,事务不可重复读同一条记录,因为很可能读到的结果不一致。
Repeatable Read
时刻 | 事务A | 事务B |
---|---|---|
1 | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
2 | BEGIN; | BEGIN; |
3 | SELECT * FROM students WHERE id = 99; | |
4 | INSERT INTO students (id, name) VALUES (99, ‘Bob’); | |
5 | COMMIT; | |
6 | SELECT * FROM students WHERE id = 99; | |
7 | UPDATE students SET name = ‘Alice’ WHERE id = 99; | |
8 | SELECT * FROM students WHERE id = 99; | |
9 | COMMIT; |
事务B在第3步第一次读取id=99
的记录时,读到的记录为空,说明不存在id=99
的记录。随后,事务A在第4步插入了一条id=99
的记录并提交。事务B在第6步再次读取id=99
的记录时,读到的记录仍然为空,但是,事务B在第7步试图更新这条不存在的记录时,竟然成功了,并且,事务B在第8步再次读取id=99
的记录时,记录出现了。
幻读就是没有读到的记录,可以更新成功的。
Serializable(最严格)
在Serializable
隔离级别下,所有事务按照次序依次执行,因此,脏读、不可重复读、幻读都不会出现。
优点: 安全性高。
缺点: 由于事务是串行执行,所以效率会大大下降,应用程序的性能会急剧降低。
- 所以一般不使用
Serializable
。
默认隔离级别
如果没有指定隔离级别,数据库就会使用默认的隔离级别。在MySQL中,如果使用InnoDB,默认的隔离级别是Repeatable Read
。