【连载】MySQL笔记——是时候拥有自己的数据库啦

最新推荐文章于 2021-08-23 16:25:18 发布

衍欲瑜

最新推荐文章于 2021-08-23 16:25:18 发布

阅读量824

点赞数

分类专栏： MySQL 学习笔记基础知识文章标签：数据库 mysql java 大数据后端

本文链接：https://blog.csdn.net/weixin_52777510/article/details/113264752

版权

学习笔记同时被 3 个专栏收录

86 篇文章 7 订阅

订阅专栏

基础知识

26 篇文章 1 订阅

订阅专栏

MySQL

4 篇文章 0 订阅

订阅专栏

我是灼灼，一只初学Java的大一金渐层。
向往余秀华和狄兰·托马斯的疯狂，时常沉溺于将情感以诗相寄；追逐过王尔德、王小波的文字，后陷于毛姆和斯蒂芬·金不可自拔；热爱文学的浪潮，白日梦到底却总在现实里清醒；艳羡平静又极度渴盼奔跑的力量。
欢迎与我交流鸭· QQ:1517526827；
个人博客：https://blog.csdn.net/weixin_52777510?spm=1001.2101.3001.5343

MySQL学习笔记

Java相关笔记正在连载中，欢迎来其他内容逛逛哟~

相关内容如下：
【连载1】Java笔记——基本结构与流程控制
【连载2】Java笔记——数组操作
【连载3】Java笔记——面向对象编程
【连载4】Java笔记——Java核心类
【连载5】Java笔记——异常处理
【连载6】Java笔记——反射和注解
【连载7】Java笔记——泛型
【连载8】Java笔记——集合
【连载9】MySQL学习笔记
【连载10】JDBC学习笔记
【连载11】Git和GitHub的使用笔记

笔记内容来源于廖雪峰官方网站MySQL教程~

这里是今天复习的时候整理的思维导图，可以看看噢~

MySQL复习的框架（主命令）

在这里插入图片描述

文章目录

MySQL学习笔记

什么是SQL？

SQL就是访问和处理关系数据库的计算机标准语言。现代程序离不开关系数据库，要使用关系数据库就必须掌握SQL。

内容：关系数据库的基本概念;如何使用SQL操作数据库;以及一种最流行的开源数据库MySQL的基本安装和使用方法;

NoSQL

NoSQL数据库，就是非SQL的数据库，包括MongoDB、Cassandra、Dynamo等等，它们都不是关系数据库。事实上，SQL数据库从始至终从未被取代过；NoSQL的发展历程：

1970: NoSQL = We have no SQL
1980: NoSQL = Know SQL
2000: NoSQL = No SQL!
2005: NoSQL = Not only SQL
2013: NoSQL = No, SQL!

目前，SQL数据库仍然承担了各种应用程序的核心数据存储，而NoSQL数据库作为SQL数据库的补充，两者不再是二选一的问题，而是主从关系。所以，无论使用哪种编程语言，无论是Web开发、游戏开发还是手机开发，掌握SQL，是所有软件开发人员所必须的；

关系数据库概述

为什么需要数据库？

因为应用程序需要保存用户的数据；

要保存用户的数据，一个最简单的方法是把用户数据写入文件（例如CSV）；

but随着应用程序的功能越来越复杂，数据量越来越大，如何管理这些数据就成了大问题：

读写文件并解析出数据需要大量重复代码；
从成千上万的数据中快速查询出指定数据需要复杂的逻辑。

方法一：每个应用程序都各自写自己的读写数据的代码：

效率低，容易出错；

每个应用程序访问数据的接口都不相同，数据难以复用；

方法二：数据库

作为一种专门管理数据的软件。

应用程序不需要自己管理数据，而是通过数据库软件提供的接口来读写数据。至于数据本身如何存储到文件，那是数据库软件的事情，应用程序自己并不关心；

所以，数据库的优势：

1）简化数据读写的功能；

2）只需要通过数据库软件提供的接口来读写数据；不需要关心多余的事情~

数据模型

数据库按照数据结构来组织、存储和管理数据，数据库一共有三种模型：

层次模型
网状模型
关系模型

1）”层“次模型就是以“上下级”的层次关系来组织数据的一种方式，层次模型的数据结构看起来就像一颗树：

2）”网“状模型把每个数据节点和其他很多节点都连接起来，它的数据结构看起来就像很多城市之间的路网：

3）”关系“模型把数据看作是一个二维表格，任何数据都可以通过行号+列号来唯一确定，它的数据模型看起来就是一个Excel表：

随着时间的推移和市场竞争，最终基于关系模型的关系数据库获得了绝对市场份额;

为什么关系数据库获得了最广泛的应用？

因为相比层次模型和网状模型，关系模型理解和使用起来最简单；

/关系数据库的关系模型是基于数学理论建立的。把域（Domain）定义为一组具有相同数据类型的值的集合，给定一组域D1,D2,…,Dn，它们的笛卡尔集定义为D1×D2×……×Dn={(d1,d2,…,dn)|di∈Di,i=1,2,…,n}，而D1×D2×……×Dn的子集叫作在域D1,D2,…,Dn上的关系，表示为R(D1,D2,…,Dn)，这里的R表示…/

基于数学理论的关系模型虽然挺复杂，但是基于日常生活的关系模型却十分容易理解；

数据类型

对于一个关系表，除了定义每一列的名称外，还需要定义每一列的数据类型。关系数据库支持的标准数据类型包括数值、字符串、时间等：

名称	类型	说明
INT	整型	4字节整数类型，范围约+/-21亿
BIGINT	长整型	8字节整数类型，范围约+/-922亿亿 //bigint（20）
REAL	浮点型	4字节浮点数，范围约+/-1038
DOUBLE	浮点型	8字节浮点数，范围约+/-10308
DECIMAL(M,N)	高精度小数	由用户指定精度的小数，例如，DECIMAL(20,10)表示一共20位，其中小数10位，通常用于财务计算
CHAR(N)	定长字符串	存储指定长度的字符串，例如，CHAR(100)总是存储100个字符的字符串
VARCHAR(N)	变长字符串	存储可变长度的字符串，例如，VARCHAR(100)可以存储0~100个字符的字符串 //varchar(225)
BOOLEAN	布尔类型	存储True或者False
DATE	日期类型	存储日期，例如，2018-06-22
TIME	时间类型	存储时间，例如，12:20:59
DATETIME	日期和时间类型	存储日期+时间，例如，2018-06-22 12:20:59

表中列举了最常用的数据类型。很多数据类型还有别名，例如，REAL又可以写成FLOAT(24)。还有一些不常用的数据类型，例如，TINYINT（范围在0~255）。各数据库厂商还会支持特定的数据类型，例如JSON。

选择数据类型的时候，要根据业务规则选择合适的类型。通常来说，BIGINT能满足整数存储的需求，VARCHAR(N)能满足字符串存储的需求，这两种类型是使用最广泛的。

主流关系数据库

目前主流的关系数据库主要分为以下几类：

商用数据库，例如：Oracle，SQL Server，DB2等；
开源数据库，例如：MySQL，PostgreSQL等；
桌面数据库，以微软Access为代表，适合桌面应用程序使用；
嵌入式数据库，以Sqlite为代表，适合手机应用和桌面程序。

SQL

什么是SQL？SQL（structed query language ）是结构化查询语言的缩写，用来访问和操作数据库系统。SQL语句的功能包括：

查询数据库中的数据； //查query（）；

**添加、更新和删除数据库中的数据 ** //增删改CRUD；

对数据库进行管理和维护操作；

不同的数据库，都支持SQL，所以通过学习SQL这一种语言，就可以操作各种不同的数据库。

虽然SQL已经被ANSI组织定义为标准，不幸地是，各个不同的数据库对标准的SQL支持不太一致。并且，大部分数据库都在标准的SQL上做了扩展。也就是说，如果只使用标准SQL，理论上所有数据库都可以支持，但如果使用某个特定数据库的扩展SQL，换一个数据库就不能执行了：Oracle把自己扩展的SQL称为PL/SQL，Microsoft把自己扩展的SQL称为T-SQL。

现实是，如果我们只使用标准SQL的核心功能，那么所有数据库通常都可以执行。不常用的SQL功能，不同的数据库支持的程度都不一样。而各个数据库支持的各自扩展的功能，通常我们把它们称之为“方言”（具有区域性）。

SQL语言定义了几种操作数据库的能力：

DDL：Data Definition Language

DDL允许用户定义数据，也就是创建表、删除表、修改表结构这些操作。通常，DDL由数据库管理员执行。

DML：Data Manipulation Language

DML为用户提供添加、删除、更新数据的能力，这些是应用程序对数据库的日常操作。

DQL：Data Query Language

DQL允许用户查询数据，这也是通常最频繁的数据库日常操作。

语法特点

SQL语言关键字不区分大小写；但是，针对不同的数据库，对于表名和列名，有的数据库区分大小写，有的数据库不区分大小写。同一个数据库，有的在Linux上区分大小写，有的在Windows上不区分大小写。

所以，本教程约定：

SQL关键字总是大写，以示突出，表名和列名均使用小写（我们实际编写不怎么区分大小写）。

安装MySQL

MySQL是目前应用最广泛的开源关系数据库。MySQL最早是由瑞典的MySQL AB公司开发，该公司在2008年被SUN公司收购，紧接着，SUN公司在2009年被Oracle公司收购，所以MySQL最终就变成了Oracle旗下的产品。

和其他关系数据库有所不同的是，MySQL本身实际上只是一个SQL接口，它的内部还包含了多种数据引擎，常用的包括：

InnoDB：由Innobase Oy公司开发的一款支持事务的数据库引擎，2006年被Oracle收购；
MyISAM：MySQL早期集成的默认数据库引擎，不支持事务。

MySQL接口和数据库引擎的关系就好比某某浏览器和浏览器引擎（IE引擎或Webkit引擎）的关系。对用户而言，切换浏览器引擎不影响浏览器界面，切换MySQL引擎不影响自己写的应用程序使用MySQL的接口。

使用MySQL时，不同的表还可以使用不同的数据库引擎；

如果不知道应该采用哪种引擎，记住总是选择InnoDB就好；

因为MySQL一开始就是开源的，所以基于MySQL的开源版本，又衍生出了各种版本：

MariaDB

由MySQL的创始人创建的一个开源分支版本，使用XtraDB引擎。

Aurora

由Amazon改进的一个MySQL版本，专门提供给在AWS托管MySQL用户，号称5倍的性能提升。

PolarDB

由Alibaba改进的一个MySQL版本，专门提供给在阿里云托管的MySQL用户，号称6倍的性能提升。

而MySQL官方版本又分了好几个版本：

Community Edition：社区开源版本，免费；
Standard Edition：标准版；
Enterprise Edition：企业版；
Cluster Carrier Grade Edition：集群版。

以上版本的功能依次递增，价格也依次递增。不过，功能增加的主要是监控、集群等管理功能，对于基本的SQL功能是完全一样的。

所以使用MySQL就带来了一个巨大的好处：可以在自己的电脑上安装免费的Community Edition版本，进行学习、开发、测试，部署的时候，可以选择付费的高级版本，或者云服务商提供的兼容版本，而不需要对应用程序本身做改动；

安装与测试：

在安装过程中，MySQL会自动创建一个root用户，并提示输入root口令。

在命令提示符下输入

mysql -u root -p

然后输入口令，如果一切正确，就会连接到MySQL服务器，同时提示符变为

mysql>

输入这个命令退出MySQL命令行

exit

注意，MySQL服务器仍在后台运行。

关系模型

关系数据库是建立在关系模型（类似于表格）上的；而关系模型本质上就是若干个存储数据的二维表，可以把它们看作很多Excel表。

表的每一行称为记录（Record），记录是一个逻辑意义上的数据。

表的每一列称为字段（Column），同一个表的每一行记录都拥有相同的若干字段。

字段定义了数据类型（整型、浮点型、字符串、日期等），以及是否允许为NULL；NULL表示字段数据不存在。一个整型字段如果为NULL不表示它的值为0，同样的，一个字符串型字段为NULL也不表示它的值为空串''。

通常情况下，字段应该避免允许为NULL,这样可以简化查询条件，加快查询速度，也利于应用程序读取数据后无需判断是否为NULL。

和Excel表有所不同的是，关系数据库的表和表之间需要建立“一对多”，“多对一”和“一对一”的关系（三种关系），这样才能够按照应用程序的逻辑来组织和存储数据。

在关系数据库中，关系是通过主键和外键来维护的。

主键

在关系数据库中，一张表中的每一行数据被称为一条记录record，一条记录就是由若干个定义好的个字段column组成的。同一个表的所有记录都有相同的字段定义（可以看作是表格标题类型，一个类型对应若干个字段）。

关系表的一个很重要的约束是任意两条记录不能重复。不能重复不是指两条记录不完全相同，而是指能够通过某个字段（看作是表格的类型名）唯一区分出不同的记录（这个字段对应的行即记录有很多个，主键就是可以根据这个字段来区分不同的行即记录），这个字段被称为主键（列名）。

对主键的要求，最关键的一点是：记录一旦插入到表中，主键最好不要再修改，因为主键是用来唯一定位记录的，修改了主键，会造成一系列的影响。（那么最好是先想好这个字段的名字，再向里面插入一行行的记录）

由于主键的作用十分重要，所以如何选取主键会对业务开发产生重要影响。

如果以学生的身份证号作为主键，似乎能唯一定位记录。然而，身份证号也是一种业务场景，如果身份证号升位了，或者需要变更，作为主键，不得不修改的时候，就会对业务产生严重影响（要考虑变动性，这个数据库的合理存活期）。

所以，选取主键的一个基本原则是：不使用任何业务相关的字段作为主键。

因此，身份证号、手机号、邮箱地址这些看上去可以唯一的字段，均不可用作主键（看上去可以但是实际不行，变更性要考虑）。

作为主键最好是**完全业务无关的字段，**一般把这个字段命名为id；

常见的可作为id字段的类型有：

自增整数类型：数据库会在插入数据时自动为每一条记录分配一个自增整数，这样就完全不用担心主键重复，也不用自己预先生成主键；（应用较广泛，方便常用）
全局唯一GUID类型：使用一种全局唯一的字符串作为主键，类似8f55d96b-8acc-4636-8cb8-76bf8abc2f57。GUID算法通过网卡MAC地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的（虽然随机但是种子可以保证是不同的），大部分编程语言都内置了GUID算法，可以自己预算出主键。

对于大部分应用来说，通常自增类型的主键就能满足需求；在students表中定义的主键也是BIGINT NOT NULL AUTO_INCREMENT类型（意思是bigint类型字段、非空、自增）。

如果使用INT自增类型，那么当一张表的记录数超过2147483647（约21亿）时，会达到上限而出错。使用BIGINT（用这个！）自增类型则可以最多约922亿亿条记录；这样够大！

联合主键

关系数据库实际上还允许通过多个字段唯一标识记录，即两个或更多的字段都设置为主键，这种主键被称为联合主键。

对于联合主键，允许一列有重复，只要不是所有主键列都重复即可；

id_num	id_type	other columns…
1	A	…
2	A	…
2	B	…

如果把上述表的id_num和id_type这两列作为联合主键，那么上面的3条记录都是允许的，因为没有两列主键组合起来是相同的（正好避开，一个下面同，一个上面同）。

没有必要的情况下，尽量不使用联合主键，因为它给关系表带来了复杂度的上升（就是复杂~）。

外键

在某个表中，通过某个特殊列的字段，可以把数据与另一张表关联起来，这种列称为外键。

外键并不是通过列名实现的，而是通过定义外键约束实现的：

ALTER TABLE students
ADD CONSTRAINT fk_class_id			//添加外键约束命令
FOREIGN KEY (class_id//列的类型名)		//指定外键约束字段命令
REFERENCES//references classes (id//被关联的列);		//指定外键关联字段命令

其中，外键约束的名称fk_class_id可以任意，FOREIGN KEY (class_id)指定了class_id作为外键，REFERENCES classes (id)指定了这个外键将关联到classes表的id列（即classes表的主键）。

通过定义外键约束，关系数据库可以保证无法插入无效的数据。即如果classes表不存在id=99的记录，students表就无法插入class_id=99的记录。

由于外键约束会降低数据库的性能，大部分互联网应用程序为了追求速度，并不设置外键约束，而是仅靠应用程序自身来保证逻辑的正确性。这种情况下，class_id仅仅是一个普通的列，只是它起到了外键的作用而已。

要删除一个外键约束，也是通过ALTER TABLE实现的：

ALTER TABLE students
DROP FOREIGN KEY fk_class_id;			//删除外键约束命令

注意：删除外键约束并没有删除外键这一列。删除列是通过DROP COLUMN ...实现的。

多对多

多对多关系实际上是通过两个一对多关系实现的，即通过一个中间表，关联两个一对多关系，就形成了多对多关系；

一对一

一对一关系是指，一个表的记录对应到另一个表的唯一一个记录。

如果业务允许，可以把两个表合为一个表。一对一关系准确地说，是一个表中的每一个数据都必须唯一对应另一个表中的唯一数据，注意唯一，注意要存在。

还有一些应用会把一个大表拆成两个一对一的表，目的是把经常读取和不经常读取的字段分开，以获得更高的性能。

索引

在关系数据库中，如果有上万甚至上亿条记录，在查找记录的时候，想要获得非常快的速度，就需要使用索引。

索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。

通过使用索引，数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。

如果要经常根据某一列（例如score）进行查询，就可以对该列创建索引：

ADD INDEX idx_score (score);		//idx就是索引的意思，利用下划线_来连接索引和字段

使用ADD INDEX idx_score (score)就创建了一个名称为idx_score，使用列score的索引。索引名称是任意的，索引如果有多列，可以在括号里依次写上，例如：

ADD INDEX idx_name_score (name, score);		//利用下划线_来连接两个或者多个字段

索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高。

反过来，如果记录的列存在大量相同的值，那么对该列创建索引就没有意义。

可以对一张表创建多个索引

索引的优点：提高了查询效率；

索引的缺点：

在插入、更新和删除记录时，需要同时修改索引；因此，索引越多，插入、更新和删除记录的速度就越慢。

对于主键，关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的，因为主键会保证绝对唯一。

唯一索引

在设计关系数据表的时候，看上去唯一的列，例如身份证号、邮箱地址等，因为他们具有业务含义，因此不宜作为主键。

但是，这些列根据业务要求，又具有唯一性约束：即不能出现两条记录存储了同一个身份证号。这个时候，就可以给该列添加一个唯一索引。假设students表的name不能重复：

ADD UNIQUE INDEX uni_name (name);		//uni关键字

通过UNIQUE关键字我们就添加了一个唯一索引。

唯一索引写法：

ADD UNIQUE INDEX uni_字段名（字段名）

也可以只对某一列添加一个唯一约束而不创建唯一索引：

ADD CONSTRAINT uni_name UNIQUE (name);

唯一约束写法：

ADD CONSTRAINT uni_字段名 UNIQUE （字段名）

这种情况下，name列没有索引，但仍然具有唯一性保证。

无论是否创建索引，对于用户和应用程序来说，使用关系数据库不会有任何区别。意思是说，当在数据库中查询时，如果有相应的索引可用，数据库系统就会自动使用索引来提高查询效率，如果没有索引，查询也能正常执行，只是速度会变慢。因此，索引可以在使用数据库的过程中逐步优化。

在线SQL

在线运行SQL的功能实际上是在浏览器页面运行的一个JavaScript编写的内存型SQL数据库AlaSQL。不必运行MySQL等实际的数据库软件，即可在线编写并执行SQL语句；

-- 以双减号开头的是注释

在页面加载时，students表和classes表就自动被创建并填入了若干数据。由于数据只存在于浏览器的内存中，因此，如果修改了数据，重新刷新页面后，数据会重置为初始值。

查询数据

在关系数据库中，最常用的操作是查询。

和MySQL的持久化存储不同的是，由于使用的是AlaSQL内存数据库，两张表的数据在页面加载时导入，并且只存在于浏览器的内存中，所以在刷新页面后，数据会重置为上述初始值。

MySQL（可不看）

如果想用MySQL练习，可以下载这个SQL脚本，然后在命令行运行：
$ mysql -u root -p < init-test-data.sql
就可以自动创建test数据库，并且在test数据库下创建students表和classes表，以及必要的初始化数据。

和内存数据库不同的是，对MySQL数据库做的所有修改，都会保存下来。如果希望恢复到初始状态，可以再次运行该脚本。

基本查询

要查询数据库表的数据，使用如下的SQL语句：

SELECT * FROM <表名>

SELECT是关键字，表示将要执行一个查询，*表示“所有列”，FROM表示将要从哪个表查询，后面写表的名字；

查询结果也是一个二维表，它包含列名和每一行的数据。

SELECT语句其实并不要求一定要有FROM子句，例如这个失去了语句：

SELECT 100+200;

运行结果：

100 + 200
300

上述查询会直接计算出表达式的结果。虽然SELECT可以用作计算，但它并不是SQL的强项；

不带FROM子句的SELECT语句可以用来判断当前到数据库的连接是否有效。许多检测工具会执行一条SELECT 1;（连接成功返回1）来测试数据库连接。

条件查询

如果想根据条件选择性地获取指定条件的记录时，SELECT语句可以通过WHERE条件来设定查询条件，查询结果是满足查询条件的记录。

条件查询的语法是：

SELECT * FROM <表名> WHERE <条件表达式>；

第一种条件是<条件1> AND <条件2>表达满足条件1并且满足条件2，语法：

SELECT * FROM <表名> WHERE <条件一> AND <条件二>；

第二种条件是<条件1> OR <条件2>，表示满足条件1或者满足条件2,语法：

SELECT * FROM <表名> WHERE <条件一> OR <条件二>;

OR条件要比AND条件宽松，返回的符合条件的记录更多。

第三种条件是NOT <条件>，表示“不符合该条件”的记录，语法：

SELECT * FROM <表名> WHERE NOT <条件表达式>;

NOT条件NOT xxxxx = y等价于xxxxx <> y，所以NOT查询不是很常用。

要组合三个或者更多的条件，就需要用小括号()表示如何进行条件运算，语法举例：

SELECT * FROM <表名> WHERE (score < 80 OR score > 90) AND gender = ‘M’;

如果不加括号，条件运算按照NOT、AND、OR的优先级进行，即NOT优先级最高，其次是AND，最后是OR。加上括号可以改变优先级。

常用的条件表达式

条件	表达式举例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’

投影查询

如果只希望返回某些列的数据，而不是所有列的数据，可以用SELECT 列1, 列2, 列3 FROM ...，让结果集仅包含指定列。这种操作称为投影查询，结果集的列的顺序和原表可以不一样。

语法：

SELECT 列1, 列2, 列3 FROM …

还可以给每一列起个别名，这样，结果集的列名就可以与原表的列名不同，语法：

SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM …

投影查询同样可以接WHERE条件，实现复杂的查询，语法：

SELECT 列1，列2 别名2，列3 FROM <表名> WHERE <条件>;

排序

使用SELECT查询时，查询结果集通常是按照id排序的，也就是根据主键排序。这是大部分数据库的做法。如果要根据其他条件排序可以加上ORDER BY子句，语法（从低到高排序）：

SELECT <列名1，列名2，排序列列名> FROM <表名> ORDER BY <规定的排序列>;

如果要反过来，按照从高到底排序，可以加上DESC表示“倒序”，语法：

SELECT <列名1，列名2，排序列列名> FROM <表名> ORDER BY <规定的排序列> DESC;

如果该特定的排序列有相同的数据，要进一步排序，可以继续添加列名，语法：

SELECT <列名1，列名2，排序列列名1，排序列列名2> FROM <表名> ORDER BY <排序列列名1> DESC, <排序列列名2>;

排序列有先后顺序！

默认的排序规则是ASC：“升序”，即从小到大，ASC可以省略；

如果有WHERE子句，那么ORDER BY子句要放到WHERE子句后面，语法：

SELECT <列名1，列名2，排序列列名1，排序列列名2>
FROM <表名>
WHERE <条件表达式>
ORDER BY 排序列列名1 DESC;

这样，结果集仅包含符合WHERE条件的记录，并按照ORDER BY的设定排序。

分页查询

分页

使用SELECT查询时，如果结果集数据量很大，不如分页显示，每次显示100条。

分页实际上就是从结果集中“截取”出第M~N条记录，实现语法：

LIMIT OFFSET

先进行排序，再把结果集分页，每页M条记录。要获取第1页的记录：使用LIMIT M OFFSET 0

表示：对结果集从0号记录开始，最多取M条。注意SQL记录集的索引从0开始。

分页查询的关键在于，首先要确定每页需要显示的结果数量pageSize（这里是M），然后根据当前页的索引pageIndex（从1开始），确定LIMIT和OFFSET应该设定的值：

LIMIT总是设定为pageSizeM；
OFFSET计算公式为pageSize * (pageIndex - 1)。

这样就能正确查询出第n页的记录集。

如果原本记录集一共就n条记录，但把OFFSET设置为大于n的数，会得到：

Empty result set

OFFSET超过了查询的最大数量并不会报错，而是得到一个空的结果集。

注意

OFFSET是可选的，如果只写LIMIT M，那么相当于LIMIT M OFFSET 0。

在MySQL中，LIMIT M OFFSET N还可以简写成LIMIT N, M;这里要注意MN反过来了！

使用LIMIT <M> OFFSET <N>分页时，随着N越来越大，查询效率也会越来越低。

聚合查询

对于统计总数、平均数这类计算，SQL提供了专门的聚合函数，使用聚合函数进行查询，就是聚合查询，它可以快速获得结果。

使用SQL内置的COUNT()函数查询某个表一共有多少条记录，语法：

SELECT COUNT(*) FROM <表名>;

COUNT(*)表示查询所有列的行数，要注意聚合的计算结果虽然是一个数字，但查询的结果仍然是一个二维表，只是这个二维表只有一行一列，并且列名是COUNT(*)。

使用聚合查询时，应该给列名设置一个别名，便于处理结果，语法：

SELECT COUNT(*) <列名别名> FROM <表名>;

COUNT(*)和COUNT(id主键)实际上是一样的效果。

聚合查询同样可以使用WHERE条件，语法：

SELECT COUNT(*) <列名别名> FROM <表名> WHERE <条件>;

SQL还提供了的聚合函数：

函数	说明
SUM	计算某一列的合计值，该列必须为数值类型
AVG	计算某一列的平均值，该列必须为数值类型
MAX	计算某一列的最大值
MIN	计算某一列的最小值

MAX()和MIN()函数并不限于数值类型。如果是字符类型，MAX()和MIN()会返回排序最后和排序最前的字符。

要统计某一数值类型列的平均成绩，使用聚合查询的语法：

SELECT AVG(数值类型列) average FROM <表名> WHERE <条件>;

！！如果聚合查询的WHERE条件没有匹配到任何行，COUNT()会返回0，而SUM()、AVG()、MAX()和MIN()会返回NULL：

每页3条记录，如何通过聚合查询获得总页数，语法：

SELECT CEILING(COUNT(*) / 3) FROM <表名>;

分组

对于聚合查询，SQL还提供了“分组聚合”的功能。该种分组聚合查询的语法：

SELECT COUNT(*) <列名别名> FROM <表名> GROUP BY <依据的分组列>;

执行这个查询时，COUNT()的结果通常会是多个，这是因为，GROUP BY子句指定了按<依据的分组列>分组，因此，执行该SELECT语句时，会把<依据的分组列字段>相同的列先分组，再分别计算，所以会得到多行结果。

区分对应多个count结果可以把<依据的分组列>也放入结果集中，语法：

SELECT <依据的分组列>, COUNT(*) <列名别名> FROM <表名> GROUP BY <依据的分组列>；

那么第一种语法因为缺陷就可以被丢弃了~

在任意一个分组中，只有<依据的分组列>都相同，其余的字段是不同的，SQL引擎不能把多个其余列的值放入一行记录中。因此，聚合查询的列中，只能放入依据的分组列。

也可以使用多个列进行分组，语法：

SELECT <依据的分组列1>, <依据的分组列2>, COUNT(*) <列名别名> FROM <表名> GROUP BY <依据的分组列1>, <依据的分组列2>;

查询结果集分别对应分组一的分组二…

练习

请使用一条SELECT查询查出每个班级的平均分，语法：

请使用一条SELECT查询查出每个班级男生和女生的平均分：

多表查询

SELECT查询不但可以从一张表查询数据，还可以从多张表同时查询数据，语法：

SELECT * FROM <表1> <表2>

这种一次查询两个表的数据，查询的结果也是一个二维表，它是表1和表2的“乘积”，即表1的每一行与表2的每一行都两两拼在一起返回。结果集的列数是两个表的列数之积，行数是两个表的行数之积。

这种多表查询又称笛卡尔查询，使用笛卡尔查询时要非常小心，由于结果集是目标表的行数乘积，返回的结果集会很庞大；

在结果集中，不容易区分两个表的同名字段，可以利用投影查询的“设置列的别名”来给两个表各自的同名列起别名，语法举例：

SELECT
students.id sid,
students.name,
students.gender,
students.score,
classes.id cid,
classes.name cname
FROM students, classes;

多表查询时，要使用表名.列名这样的方式来引用列和设置别名，这样就避免了结果集的列名重复问题。但是，用表名.列名这种方式列举两个表的所有列实在是很麻烦，所以SQL还允许给表设置一个别名，使得在投影查询中引用起来稍微简洁一点，语法举例：

SELECT
s.id sid,
s.name,
s.gender,
s.score,
c.id cid,
c.name cname
FROM students s, classes c;

FROM子句给表设置别名的语法：

FROM <表名1> <别名1>, <表名2> <别名2>；

这样我们用别名分别表示两个表。

多表查询也是可以添加WHERE条件的，语法：

在上面的代码中加上：、

WHERE <条件一> AND <条件二>;

添加WHERE条件后结果集的数量大大减少了；

连接查询

连接查询是另一种类型的多表查询。连接查询对多个表进行JOIN运算，简单地说，就是先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上。

最常用的一种内连接——INNER JOIN来实现：

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
1.FROM students s
2.INNER JOIN classes c
3.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）,把内连接查询改成外连接查询:

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
RIGHT OUTER JOIN classes c
ON s.class_id = c.id;

有RIGHT OUTER JOIN，就有LEFT OUTER JOIN，以及FULL OUTER JOIN。它们的区别是：

INNER JOIN只返回同时存在于两张表的行数据;

RIGHT OUTER JOIN返回右表都存在的行。如果某一行仅在右表存在，那么结果集就会以NULL填充剩下的字段。

LEFT OUTER JOIN则返回左表都存在的行。如果我们给左表增加一行，并添加字段值，由于右表并不存在左表中的新记录，所以，LEFT OUTER JOIN的结果会增加一行，对应的右表字段值是NULL；

FULL OUTER JOIN，它会把两张表的所有记录全部选择出来，并且，自动把对方不存在的列填充为NULL：

用图来表示结果集就一目了然了。

假设查询语句是：

SELECT ... FROM tableA ??? JOIN tableB ON tableA.column1 = tableB.column2;

把tableA看作左表，把tableB看成右表，那么INNER JOIN是选出两张表都存在的记录：

inner-join

LEFT OUTER JOIN是选出左表存在的记录：

left-outer-join

RIGHT OUTER JOIN是选出右表存在的记录：

right-outer-join

FULL OUTER JOIN则是选出左右表都存在的记录：

full-outer-join

修改数据

关系数据库的基本操作就是增删改查，即CRUD：Create、Retrieve、Update、Delete。其中，对于查询，我们已经详细讲述了SELECT语句的详细用法。

而对于增、删、改，对应的SQL语句分别是：

INSERT：插入新记录；
UPDATE：更新已有记录；
DELETE：删除已有记录。

INSERT

当需要向数据库表中插入一条新记录时，就必须使用INSERT语句。

INSERT语句的基本语法是：

INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);

向表插入一条新记录，先列举出需要插入的字段名称，然后在VALUES子句中依次写出对应字段的值：

id字段是一个自增主键，它的值可以由数据库自己推算出来，所以不用写这个字段。此外，如果一个字段有默认值，那么在INSERT语句中也可以不出现。

字段顺序不必和数据库表的字段顺序一致，但值的顺序必须和字段顺序一致。

还可以一次性添加多条记录，只需要在VALUES子句中指定多个记录值，每个记录是由(...)包含的一组值：

VALUES子句中是一组组的值，类似于多维数组；

UPDATE

如果要更新数据库表中的记录，必须使用UPDATE语句。

UPDATE语句的基本语法是：

UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;

例如更新表id=1的记录的两个字段，先写出UPDATE <表名> SET 字段一=字段一的值, 字段二=字段二的值，然后在WHERE子句中写出需要更新的行的筛选条件id=1：

UPDATE语句的WHERE条件和SELECT语句的WHERE条件是一样的，所以可以一次更新多条记录；

在UPDATE语句中，更新字段时可以使用表达式；

如果WHERE条件没有匹配到任何记录，UPDATE语句不会报错，也不会有任何记录被更新；

最后，要特别小心的是，UPDATE语句可以没有WHERE条件，例如：

UPDATE students SET score=60;

这时，整个表的所有记录都会被更新。所以，在执行UPDATE语句时要非常小心，最好先用SELECT语句来测试WHERE条件是否筛选出了期望的记录集，然后再用UPDATE更新。

MySQL

在使用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

MySQL会返回1，可以从打印的结果Rows matched: 1 Changed: 1看到。

当更新id=999的记录时：

mysql> UPDATE students SET name='大宝' WHERE id=999;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

MySQL会返回0，可以从打印的结果Rows matched: 0 Changed: 0看到。

DELETE

如果要删除数据库表中的记录，可以使用DELETE语句。

DELETE语句的基本语法是：

DELETE FROM <表名> WHERE ...;
-- 查询并观察结果:
SELECT * FROM <表名>;

DELETE语句的WHERE条件也是用来筛选需要删除的行，因此和UPDATE类似，DELETE语句也可以一次删除多条记录：

如果WHERE条件没有匹配到任何记录，DELETE语句不会报错，也不会有任何记录被删除；

最后，要特别小心的是，和UPDATE类似，不带WHERE条件的DELETE语句会删除整个表的数据：

DELETE FROM <表名>;

这时，整个表的所有记录都会被删除。所以，在执行DELETE语句时也要非常小心，最好先用SELECT语句来测试WHERE条件是否筛选出了期望的记录集，然后再用DELETE删除。

MySQL

在使用MySQL这类真正的关系数据库时，DELETE语句也会返回删除的行数以及WHERE条件匹配的行数。

例如，分别执行删除id=1和id=999的记录：

mysql> DELETE FROM students WHERE id=1;
Query OK, 1 row affected (0.01 sec)

mysql> DELETE FROM students WHERE id=999;
Query OK, 0 rows affected (0.01 sec)

MySQL

安装完MySQL后，除了MySQL Server，即真正的MySQL服务器外，还附赠一个MySQL Client程序。MySQL Client是一个命令行客户端，可以通过MySQL Client登录MySQL，然后，输入SQL语句并执行。

打开命令提示符，输入命令mysql -u root -p，提示输入口令。填入MySQL的root口令，如果正确，就连上了MySQL Server，同时提示符变为mysql>：

┌────────────────────────────────────────────────────────┐
│Command Prompt                                    - □ x │
├────────────────────────────────────────────────────────┤
│Microsoft Windows [Version 10.0.0]                      │
│(c) 2015 Microsoft Corporation. All rights reserved.    │
│                                                        │
│C:\> mysql -u root -p                                   │
│Enter password: ******                                  │
│                                                        │
│Server version: 5.7                                     │
│Copyright (c) 2000, 2018, ...                           │
│Type 'help;' or '\h' for help.                          │
│                                                        │
│mysql>                                                  │
│                                                        │
└────────────────────────────────────────────────────────┘

输入exit断开与MySQL Server的连接并返回到命令提示符。

MySQL Client的可执行程序是mysql，MySQL Server的可执行程序是mysqld。

MySQL Client和MySQL Server的关系如下：

┌──────────────┐  SQL   ┌──────────────┐
│ MySQL Client │───────>│ MySQL Server │
└──────────────┘  TCP   └──────────────┘

在MySQL Client中输入的SQL语句通过TCP连接发送到MySQL Server。默认端口号是3306，即如果发送到本机MySQL Server，地址就是127.0.0.1:3306。

也可以只安装MySQL Client，然后连接到远程MySQL Server。假设远程MySQL Server的IP地址是10.0.1.99，那么就使用-h指定IP或域名：

mysql -h 10.0.1.99 -u root -p

管理MySQL

管理MySQL，可以使用可视化图形界面MySQL Workbench。

MySQL Workbench可以用可视化的方式查询、创建和修改数据库表，但是，归根到底，MySQL Workbench是一个图形客户端，它对MySQL的操作仍然是发送SQL语句并执行。因此，本质上，MySQL Workbench和MySQL Client命令行都是客户端，和MySQL交互，唯一的接口就是SQL。

因此，MySQL提供了大量的SQL语句用于管理。虽然可以使用MySQL Workbench图形界面来直接管理MySQL，但是，很多时候，通过SSH远程连接时，只能使用SQL命令，所以，了解并掌握常用的SQL管理操作是必须的。

数据库

在一个运行MySQL的服务器上，实际上可以创建多个数据库（Database）。要列出所有数据库，使用命令：

SHOW DATABASES;

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| shici              |
| sys                |
| test               |
| school             |
+--------------------+

其中，information_schema、mysql、performance_schema和sys是系统库，不要去改动它们。其他的是用户创建的数据库。

要创建一个新数据库，使用命令：

CREATE DATABASE test;

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.01 sec)

要删除一个数据库，使用命令：

DROP DATABASE test;

mysql> DROP DATABASE test;
Query OK, 0 rows affected (0.01 sec)

注意：删除一个数据库将导致该数据库的所有表全部被删除。

对一个数据库进行操作时，要首先将其切换为当前数据库，命令：

USE test;

mysql> USE test;
Database changed

表

列出当前数据库的所有表，使用命令：

SHOW TABLES;

mysql> SHOW TABLES;
+---------------------+
| Tables_in_test      |
+---------------------+
| classes             |
| statistics          |
| students            |
| students_of_class1  |
+---------------------+

要查看一个表的结构，使用命令：

DESC students;

mysql> DESC students;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| class_id | bigint(20)   | NO   |     | NULL    |                |
| name     | varchar(100) | NO   |     | NULL    |                |
| gender   | varchar(1)   | NO   |     | NULL    |                |
| score    | int(11)      | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

还可以查看创建表的SQL语句，命令：

SHOW CREATE TABLE students;

mysql> 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 |
+----------+-------------------------------------------------------+
1 row in set (0.00 sec)

创建表命令：

CREATE TABLE；

删除表命令：

DROP TABLE；

mysql> DROP TABLE students;
Query OK, 0 rows affected (0.01 sec)

修改表就比较复杂，命令：

ALTER TABLE 表名 ADD COLUMN 字段名 VARCHAR(10) NOT NULL;

要修改新增列，例如把列名修改，类型改为VARCHAR(20)，命令：

ALTER TABLE 表名 CHANGE COLUMN 原列名新列名 VARCHAR(20) NOT NULL;

要删除列，使用命令：

ALTER TABLE 表名 DROP COLUMN 新列名；

退出MySQL

使用EXIT命令退出MySQL：

mysql> EXIT
Bye

注意EXIT仅仅断开了客户端和服务器的连接，MySQL服务器仍然继续运行。

实用SQL语句

在编写SQL时，灵活运用一些技巧，可以大大简化程序逻辑。

插入或替换

如果希望插入一条新记录（INSERT），但如果记录已经存在，就先删除原记录，再插入新记录。此时，可以使用REPLACE语句，这样就不必先查询，再决定是否先删除再插入：

REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

若id=1的记录不存在，REPLACE语句将插入新记录，否则，当前id=1的记录将被删除，然后再插入新记录。

插入或更新

如果希望插入一条新记录（INSERT），但如果记录已经存在，就更新该记录，此时，可以使用INSERT INTO ... ON DUPLICATE KEY UPDATE ...语句：

INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;

若id=1的记录不存在，INSERT语句将插入新记录，否则，当前id=1的记录将被更新，更新的字段由UPDATE指定。

插入或忽略

如果希望插入一条新记录（INSERT），但如果记录已经存在，就啥事也不干直接忽略，此时，可以使用INSERT IGNORE INTO ...语句：

INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

若id=1的记录不存在，INSERT语句将插入新记录，否则，不执行任何操作。

快照

如果想要对一个表进行快照，即复制一份当前表的数据到一个新表，可以结合CREATE TABLE和SELECT：

-- 对class_id=1的记录进行快照，并存储为新表students_of_class1:
CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;

新创建的表结构和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)

强制使用指定索引

在查询的时候，数据库系统会自动分析查询语句，并选择一个最合适的索引。但是很多时候，数据库系统的查询优化器并不一定总是能使用最优索引。如果知道如何选择索引，可以使用FORCE INDEX强制查询使用指定的索引。例如：

> SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;

指定索引的前提是索引idx_class_id必须存在。

事务

在执行SQL语句的时候，某些业务要求，一系列操作必须全部执行，而不能仅执行一部分。

由于某些原因，如果第一条语句成功，第二条语句失败，就必须全部撤销。

这种把多条语句作为一个整体进行操作的功能，被称为数据库事务。数据库事务可以确保该事务范围内的所有操作都可以全部成功或者全部失败。如果事务失败，那么效果就和没有执行这些SQL一样，不会对数据库数据有任何改动。

数据库事务具有ACID这4个特性：

A：Atomic，原子性，将所有SQL作为原子工作单元执行，要么全部执行，要么全部不执行；
C：Consistent，一致性，事务完成后，所有数据的状态都是一致的，即A账户只要减去了100，B账户则必定加上了100；
I：Isolation，隔离性，如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离；
D：Duration，持久性，即事务完成后，对数据库数据的修改被持久化存储。

对于单条SQL语句，数据库系统自动将其作为一个事务执行，这种事务被称为隐式事务。

要手动把多条SQL语句作为一个事务执行，使用BEGIN开启一个事务，使用COMMIT提交一个事务，这种事务被称为显式事务；

多条SQL语句要想作为一个事务执行，就必须使用显式事务。

COMMIT是指提交事务，即试图把事务内的所有SQL所做的修改永久保存。如果COMMIT语句执行失败了，整个事务也会失败。

有些时候希望主动让事务失败，可以用ROLLBACK回滚事务，整个事务会失败：
BEGIN;
事务语句一
事务语句二
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

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 Uncommitted隔离级别下，一个事务可能读取到另一个事务更新但未提交的数据，这个数据有可能是脏数据。

时刻	事务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;

Read Committed

在Read Committed隔离级别下，一个事务可能会遇到不可重复读（Non Repeatable 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 COMMITTED; SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
2 BEGIN; BEGIN;
3 SELECT * FROM students WHERE id = 1;
4 UPDATE students SET name = ‘Bob’ WHERE id = 1;
5 COMMIT;
6 SELECT * FROM students WHERE id = 1;
7 COMMIT;

当事务B第一次执行第3步的查询时，得到的结果是Alice，随后，由于事务A在第4步更新了这条记录并提交，所以，事务B在第6步再次执行同样的查询时，得到的结果就变成了Bob，因此，在Read Committed隔离级别下，事务不可重复读同一条记录，因为很可能读到的结果不一致。

时刻	事务A	事务B
1	SET TRANSACTION ISOLATION LEVEL READ COMMITTED;	SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
2	BEGIN;	BEGIN;
3		SELECT * FROM students WHERE id = 1;
4	UPDATE students SET name = ‘Bob’ WHERE id = 1;
5	COMMIT;
6		SELECT * FROM students WHERE id = 1;
7		COMMIT;

Repeatable Read

在Repeatable Read隔离级别下，一个事务可能会遇到幻读（Phantom 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 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的记录时，记录出现了。

可见，幻读就是没有读到的记录，以为不存在，但其实是可以更新成功的，并且，更新成功后，再次读取，就出现了。

时刻	事务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;

Serializable

Serializable是最严格的隔离级别。在Serializable隔离级别下，所有事务按照次序依次执行，因此，脏读、不可重复读、幻读都不会出现。

虽然Serializable隔离级别下的事务具有最高的安全性，但是，由于事务是串行执行，所以效率会大大下降，应用程序的性能会急剧降低。如果没有特别重要的情景，一般都不会使用Serializable隔离级别。

默认隔离级别

如果没有指定隔离级别，数据库就会使用默认的隔离级别。在MySQL中，如果使用InnoDB，默认的隔离级别是Repeatable Read。

笔记结束啦！

在这里插入图片描述
如果对你有帮助的话不要忘记一键三连噢~
谢谢鸭~

初次编写于2021//19日；

一改于2021/1/27日。

衍欲瑜

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
1
评论
【连载】MySQL笔记——是时候拥有自己的数据库啦

我是灼灼，一只初学Java的大一金渐层。向往余秀华和狄兰·托马斯的疯狂，时常沉溺于将情感以诗相寄；追逐过王尔德、王小波的文字，后陷于毛姆和斯蒂芬·金不可自拔；热爱文学的浪潮，白日梦到底却总在现实里清醒；艳羡平静又极度渴盼奔跑的力量。欢迎与我交流鸭· QQ:1517526827；个人博客：https://blog.csdn.net/weixin_52777510?spm=1001.2101.3001.5343MySQL学习笔记Java相关笔记正在连载中，欢迎来其他内容逛逛哟~相关内容如下：.
复制链接

扫一扫