MySQL学习

一、Ubuntu环境下安装MySQL

关于在Ubuntu22下安装MySQL5.7，我主要参考的是下面这几篇博客：

Ubuntu22部署MySQL5.7详细教程_ubuntu22安装mysql5.7-CSDN博客

Ubuntu 安装和使用MySQL_奉君逍遥-CSDN开发云

在配置文件中设置默认编码格式与存储引擎。

打开配置文件

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

添加下面这两条：

character-set-server=utf8
default-storage-engine=innodb

配置文件修改后重启MySQL服务即可生效。

二、数据库基础

1、登陆mysql

mysql是可以跨网络访问的，所以需要ip和端口。

mysql -h 127.0.0.1 -P 3306 -u root -p

去掉-h选项就代表登录本机搭建的mysql服务。

去掉-P选项就默认为配置的端口号3306。

2、mysql与mysqld

我们在安装数据库时分别安装了mysql和mysqld：

1. mysql其实是数据库服务的客户端。
2. mysqld是数据库服务的服务器端。
3. mysql的本质：基于CS模式的一种网络服务。

1. mysql是一套给我们提供数据存取服务的网络程序。
2. 我们在口语中说的数据库，一般指的是在磁盘或内存中存储的特定结构组织的数据。
3. 数据库服务指的是mysqld。

3、数据库存在的意义

一般文件确实提供了数据的存储功能，但文件并没有提供很好的数据管理能力（站在用户的角度来看）。

数据库的本质：对数据内容存储的一套解决方案，用户给出字段或者要求，数据库给用户结果。

4、见一见数据库

查看数据库：

show databases;

在配置文件中记录了数据库存放路径：

创建数据库：

create database helloworld;

创建了对应的文件夹

由此可以得出一个结论：建立数据库，本质就是在linux下创建一个目录。

使用数据库：

要先使用数据库，之后才能对数据库内的内容进行操作，比如创建表的操作。

use helloworld;

创建表：

后面会细讲。

create table student(
    name varchar(32),
    age int,
    gender varchar(2)
    );

在helloworld文件夹中创建了对应的文件

由此可以得出第二个结论：在数据库内创建表，本质就是在linux下创建对应的文件。

第三个结论：数据库本质也是文件！！只不过这些文件并不由程序员直接操作，而是由mysqld帮我们操作。

5、SQL语句分类

• DDL【data definition language】 数据定义语言，用来维护存储数据的结构，代表指令: create, drop, alter
• DML【data manipulation language】 数据操纵语言，用来对数据进行操作，代表指令： insert，delete，update，DML中又单独分了一个DQL，数据查询语言，代表指令： select
• DCL【Data Control Language】 数据控制语言，主要负责权限管理和事务，代表指令： grant，revoke，commi

6、存储引擎

存储引擎是：数据库管理系统如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。

查看存储引擎：

show engines;

最常用的存储引擎是InnoDB和MyISAM。

三、库的操作

1、创建数据库

create database database1;

create database if not exists database1;

加上if not exists的意思是如果数据库不存在就创建。

2、删除数据库

drop database database1;

3、数据库编码集

创建数据库时，有两个编码集：

1. 数据库字符集。（数据库未来存储数据）
2. 数据库校验规则。（支持数据库，进行字段比较使用的编码，本质是一种读取数据库数据库中数据采用的编码格式）

数据库无论对数据做任何操作，都必须保证操作和编码是编码一致的。

查看系统默认字符集：

show variables like 'character_set_database';

查看系统默认校验规则：

show variables like 'collation_database';

查看数据库支持的字符集：

show charset;

查看数据库支持的校验规则：

show collation;

4、创建指定编码的数据库

创建一个使用utf8字符集的数据库：

create database d1 charset=utf8;

create database d2 character set utf8;

创建一个使用utf8字符集以及对应校验规则的数据库：

create database d3 charset=utf8 collate utf8_general_ci;

5、校验规则对数据库的影响

创建一个数据库，校验规则使用utf8_ general_ ci（不区分大小写）：

使用数据库：

创建表：

向表中插入数据：

查找表中name=‘a’的数据：

 创建一个数据库，校验规则使用utf8_ bin（区分大小写）：

使用数据库：

创建表：

向表中插入数据：

查找表中name=‘a’的数据：

6、操纵数据库

查看有哪些数据库：

show databases;

使用数据库（进入数据库）：

use test2;

查看自己目前在哪个数据库：

select database();

修改test2数据库的字符集与校准规则：

alter database test2 charset=gbk collate gbk_chinese_ci;

显示创建数据库时的语句：

show create database test2;

/*!40100 default.... */ 这个不是注释，表示当前mysql版本大于4.01版本，就执行这句话。

7、数据库的备份与恢复

不建议直接删除数据库，最好在删除前进行备份。

备份test1数据库：

mysqldump -P 3306 -u root -p -B test1 >test1.sql

删除test1数据库：

恢复test1数据库：

source ~/MySQL/backups/test1.sql;

也可以备份数据库中的表：

mysqldump -P 3306 -u root -p test1 person > ./test1.sql

还可备份多个数据库：

mysqldump -P 3306 -u root -p -B test1 test2 > ./test1.sql

如果备份一个数据库时，没有带上-B参数， 在恢复数据库时，需要先创建空数据库，然后使用数据库，再使用source来还原。

8、查看连接情况

show processlist;

可以告诉我们当前有哪些用户连接到我们的MySQL，如果查出某个用户不是你正常登陆的，很有可能你的数据库被人入侵了。以后大家发现自己数据库比较慢时，可以用这个指令来查看数据库连接情况。

四、表的操作

1、创建表

语法如下：

CREATE TABLE table_name (
	field1 datatype,
	field2 datatype,
	field3 datatype
) character set 字符集 collate 校验规则 engine 存储引擎;

• field 表示列名。
• datatype 表示列的类型。
• character set 字符集，若没有指定字符集，以所在数据库的字符集为准。
• collate 校验规则，若没有指定校验规则，以所在数据库的校验规则为准。

create table if not exists user(
     id int,
     name varchar(20),
     password varchar(32),
     birthday date
 );

上面没有指定存储引擎默认用的是InnoDB。

create table if not exists user2(
     id int,
     name varchar(20),
     password varchar(32),
     birthday date
)engine=MyIsam;

上面选择的是MyIsam引擎。

不同的存储引擎创建的文件不一样：

2、查看表

查看当前数据库下有哪些表：

show tables;

查看表的详细信息：

desc user;

查看创建表时的详细信息：

show create table user \G

3、修改表

修改表名：

alter table user2 rename to user;

在表中添加字段：

alter table user add image_path varchar(128) after birthday;

after表示把新字段添加到birthday字段后面，可省略。

在表中修改字段：

alter table user modify name varchar(10);

在表中删除字段：

alter table user drop password;

当我们删除一个字段时，这一列曾经所有的数据都没有了，所以轻易不要删除。

在表中修改字段名：

alter table user change name xingming varchar(10);

在修改字段名时，新的字段名必须带上完整的定义。

4、删除表

删除user表：

drop table user;

最后说一句：尽量不要修改表结构！尽量在一开始就确定好表结构。

五、数据类型

1、数据类型分类

2、数值类型

（1）tinyint类型

创建一张表：

tinyint是有符号的，最小值为-128，最大值为127。

插入几个值试试：

都没出问题。

插入几个超过tinyint的值试试：

报错了，没让我们插入。

再创建一张表：

tinyint unsigned是无符号的，最小值为0，最大值为225。

插入几个值试试：

当我们插入范围外数据时，会报错，不让我们插入。

如果我们向MySQL特定的类型中插入不合法的数据，MySQL一般都会直接拦截我们，不让我们做出对应的操作。

反过来，如果我们已经有数据成功插入了MySQL中，那么插入的数据一定是合法的。所以MySQL中，数据类型本身也是一种约束。

这样可以倒逼程序员尽可能正确插入。

另外，如果你不是一个很好的使用者，MySQL也能保证数据插入的合法性。

这样就能保证数据库中的数据是可预期的、完整的。

（2）bit类型

基本语法：

bit[(M)] : 位字段类型。M表示指定的位数，范围从1到64。如果M被省略，默认为1。

[]里的内可省略。

创建一张表：

其中的online大小只有一个比特位。

插入数据：

当插入的online超过一个比特位大小时就报错了。

查看表中内容：

发现online为空。

bit类型在显示时会按照ASSIC码代表的符号来显示，ASSIC码值0和1代表的符号是不可显示的。

修改online大小为8比特位，插入69：

3、小数类型

（1）float类型

基本语法：

float[(m, d)] [unsigned] : M指定显示长度，d指定小数位数，占用空间4个字节。

创建一张表：

salary的范围为-99.99~99.99。

插入数据：

继续插入数据：

当我们插入超过指定精度的值时，会进行四舍五入。

再插入数据：

当我们插入的值四舍五入后，超过了指定的长度，就不会四舍五入，无法插入数据。

新建一张表：

salary为无符号时，直接去掉负数部分，salary的范围为0~99.99。

插入数据：

float是有精度损失的。

修改salary的类型：

再插入数据，就出现了精度损失：

（2）decimal类型

基本语法：

decimal[(m, d)] [unsigned] : 定点数m指定长度，d表示小数点的位数。m最大为65，默认为10。d最大为30，默认为0。

新建表：

尝试插入数据：

修改表：

向f1和f2插入相同的高精度浮点数：

f1出现了精度损失，但是f2没有。

4、字符串类型

（1）char类型

基本语法：

char(L): 固定长度字符串，L是可以存储的长度，单位为字符，最大长度值可以为255。

创建表：

插入数据：

插入中文字符：

在utf-8中，一个汉字是要占三个字节，在gbk中，一个汉字占两个字节。

MySQL中的字符和C/C++中的字符概念不一样。C/C++中一个字符就是一个字节。MySQL中一个字符就真的是一个符号，一个汉字或者一个英文符号就是一个字符。

（2）varchar类型

基本语法：

varchar(L): 可变长度字符串，L表示字符长度，最大长度65535个字节。

建表：

插入数据：

尝试修改name的类型：

失败了，提示说name列的最大长度为21845。

我们上面说的，varchar的最大长度为65535个字节，注意，是字节，不是字符。65545字节/3=21845字符。

char和varchar的区别：

char是固定长度字符串，varchar是变长字符串，用多少开辟多少空间，L是上限。

varchar中有1~3个字节用于记录数据大小。

所以实际上varchar的上限为65532字节，即21844字符。L的上限也就是21844。

再次修改：

还是错了。为什么呢？这次提示的是行大小太大，这一行里除了name，还有id也占了空间。

改小一点就成功了：

新创建一个表，只有name一个字段：

如何选择定长或变长字符串？

• 如果数据确定长度都一样，就使用定长（char），比如：身份证，手机号，md5
• 如果数据长度有变化,就使用变长(varchar), 比如：名字，地址，但是你要保证最长的能存的进去。
• 定长的磁盘空间比较浪费，但是效率高。
• 变长的磁盘空间比较节省，但是效率低。
• 定长的意义是，直接开辟好对应的空间
• 变长的意义是，在不超过自定义范围的情况下，用多少，开辟多少。

5、日期与时间类型

常见的有三种：

• date：日期，“yyyy-mm-dd”，占三个字节。
• datetime：日期时间，“yyyy-mm--dd HH:ii:ss”，占八个字节。
• timestamp：时间戳，从1970年开始，格式和datetime一致，占四个字节，会自动变化（当修改表数据时）。

创建表：

尝试插入数据：

进行修改：

t3也随之改变了。

再次插入数据：

6、enum与set类型

enum：枚举，单选类型。

enum('选项1','选项2','选项3',...);

set：集合，多选类型。

set('选项值1','选项值2','选项值3', ...);

建表：

插入数据：

不允许插入不存在的选项。

插入数据，gender字段插入数字：

枚举类型可以直接写选项对应的下标，下标从1开始。

再次插入数据：

允许数据为空。

插入数据，hobby字段插入数字：

插入1时，是代码没错，插入2时，是羽毛球也没错，插入3时，不应该是乒乓球吗，怎么变成羽毛球了？

其实可以以二进制的视角来理解：

有为1，没有为0。

插入hobby为7：

所以7代表的就是代码、羽毛球、乒乓球。

在表中查找所有性别为男的：

在表中查找爱好为羽毛球的人：

如此查找有一点小问题，有些人的爱好十分广泛，除了羽毛球还有别的爱好，这次查找，为什么找到的是只有羽毛球爱好的人呢？

此时就需要用到find_ in_ set函数：

find_in_set(sub,str_list) ：如果 sub 在 str_list 中，则返回下标；如果不在，返回0；str_list 用逗号分隔的字符串。

find_in_set只能查一个元素是否在对应的字符串中：

在表中查找爱好有羽毛球的人：

在表中查找爱好有羽毛球和代码的人：

六、表的约束

真正约束字段的是数据类型，但是数据类型约束很单一，所以在表中一定要有各种约束，通过约束，让我们未来插入数据库中的数据是符合预期的。约束本质是通过技术手段倒逼程序员插入正确的数据。反过来，站在MySQL的视角看，凡是插入进来的数据，都是符合约束的。

约束的最终目标：保证数据的完整性和可预期性。

表的约束很多，这里主要介绍如下几个： null/not null,default, comment, zerofill，primary key，auto_increment，unique key 。

1、空属性约束

• 两个值：null（允许为空，默认的）和not null(不允许为空)
• 数据库默认字段基本都是字段为空，但是实际开发时，尽可能保证字段不为空，因为数据为空没办法参与运算。

新建表：

插入数据：

当插入一行时，不指明class_room，就会提示没有默认值（这是接下来要讲的）。

当插入一行时，class_room设置为NULL，就会提示不能为空。

2、默认值约束

默认值：某一种数据会经常性的出现某个具体的值，可以在一开始就指定好，在需要真实数据的时候，用户可以选择性的使用默认值。在建表时，如果没有指定默认值，MySQL会自动设置默认值为NULL。

建表：

插入数据：

如果某个字段设置了default，将来插入数据时，该字段指定了值，就用指定的，如果没有，就用默认值。

再建一张表：

空属性约束和默认值约束可以一起使用。

插入数据：

由此可以得出一个结论：空属性约束和默认值约束并不冲突，而是相互补充的。

空属性约束作用于用户插入数据指定字段的值时，指定的值不能为NULL。

默认值约束作用于用户插入数据忽略字段的值时，使用默认值（如果设置了），如果没有设置，直接报错。

3、列描述约束

列描述：comment，没有实际含义，专门用来描述字段，会根据表创建语句保存，用来给程序员进行了解。（类似于C/C++中的注释）

建表：

这是一种软性的约束，是给程序员看的。

4、zerofill约束

这是关于显示方面的约束。

建表：

int后面的(10)是MySQL自动添加的，这是什么意思呢？这个其实是设定宽度。无符号的int默认为10。

插入数据：

没有出现任何问题。

给b字段添加zerofill约束：

再次查看：

zerofill约束作用是：如果插入的数据宽度小于小于设定宽度，在显示时，会自动填充0。该约束显示出来的数据是等宽的。

当然，这个数据在数据库中依旧存的是原数据，不会改变：

当插入数据的宽度大于或等于设定宽度时，就按原样显示：

5、主键约束

• 主键：primary key用来唯一的约束该字段里面的数据，不能重复，不能为空，一张表中最多只能有一个。
• 主键：主键所在的列通常是整数类型。

建表：

插入数据：

当我们插入的数据中主键字段与历史数据发生冲突时，会报错。

利用主键查找：

主键时唯一的、不重复的，所以我们可以用主键来找到唯一的一行数据。

去掉主键：

alter table t15 drop primary key;

此时就可以插入重复id的数据了：

添加主键：

alter table t15 add primary key(id);

报错了，为什么？其实是因为我们的表中id字段出现了重复的数据。主键时唯一的、不可重复的。

删除重复id数据后，再次添加主键：

上面说了一张表最多只能有一个主键。但这并不意味着一个表中的主键只能添加给一个字段，一个主键可以被添加到一个字段或多个字段。添加到多个字段的主键被称为复合主键。

新建表：

插入数据：

当多个字段为主键时，只有所有主键字段加一起和历史数据冲突了，才会报错。

比如1,40和1,50不会冲突，因为他们的course_id是不同的。1,40和1,40会冲突，他们的id和course_id都相同。

6、自增长约束

auto_increment：当对应的字段，不给值，会自动的被系统触发，系统会从当前字段中已经有的最大值+1操作，得到一个新的不同的值。通常和主键搭配使用，作为逻辑主键。

自增长的特点:

• 任何一个字段要做自增长，前提是本身是一个索引（key一栏有值）。
• 自增长字段必须是整数。
• 一张表最多只能有一个自增长。

建表：

插入数据：

插入数据是没有指定id字段的值，MySQL保证了被设为自增长约束的字段不会与历史数据冲突且保持连续。自增主键没有设定起始值，默认从1开始。

再次插入数据：

新的id是历史最大id+1。

再建表：

制定自增长约束字段从13开始。

插入数据，不指定id字段的值：

从13开始。

获取上次插入的AUTO_INCREMENT的值：

之所以能拿到这个值，是因为在表中维护了这个值：

7、唯一键约束

一张表中有往往有很多字段需要唯一性，数据不能重复，但是一张表中只能有一个主键，唯一键就可以解决表中有多个字段需要唯一性约束的问题。

唯一键的本质和主键差不多，唯一键允许为空，而且可以多个为NULL，NULL字段不做唯一性比较。

建表：

插入数据：

当唯一键字段与历史数据冲突时，就报错。

但是，唯一键是允许为NULL的，并且NULL不会冲突。

唯一键和主键功能相似，二者是相互补充的。

一般而言，我们建议将主键设计成为和当前业务无关的字段，这样，当业务调整的时候，我们可以尽量不会对主键做过大的调整。

8、外键约束

外键用于定义主表和从表之间的关系：外键约束主要定义在从表上，主表则必须是有主键约束或unique约束。当定义外键后，要求外键列数据必须在主表的主键列存在或为NULL。

案例：

如果将班级表中的数据都设计在学生表的每一行数据的后面，就会出现数据冗余，所以我们只需要设计让学生表的class_id和班级标的id形成关联的关系即可。

建立两个表：

新增两个班级：

新增几个学生：

当我们想要查看张三的班级时，直接拿着他的class_id去班级表查：

再添加一个学生：

田七被添加到了3号班级，但是，这个班级是不存在的，这合理吗？这非常不合理！MySQL需要用过技术手段避免这种逻辑上不合理的现象发生。

删除通信101班级：

直接就把通信101班级删掉了，这合理吗？这也不合理！还有学生是隶属于通信101班级的。

外键需要考虑两个关系：

• 主表和从表的关联关系
• 主表和从表的约束关系

我们建立的学生表中的class_id是有外键之名（关联关系）的，class_id和班级表的id确实把两张表联系起来了。但是没有外键之实（约束关系）。

删除掉原来的学生表，重新建立学生表，class_id字段设置外键约束：

重新添加通信101班级：

重新添加学生：

添加王五，将他的class_id设置为3：

报错了，想把王五添加到一个不存在的班级时，不让我们插入，太合理了。

把1号班级删掉：

报错了，1号班级里还有学生，不让我们删除，很合理。

将隶属于1号班级的所有学生删除，再删除1号班级：

没报错，能删除1号班级了，因为这个班级已经没有学生了，合理。

9、综合案例

有一个商店的数据，记录客户及购物情况，有以下三个表组成：

• 商品goods(商品编号goods_id，商品名goods_name, 单价unitprice, 商品类别category, 供应商provider)。
• 客户customer(客户号customer_id,姓名name,住址address,邮箱email,性别sex，身份证card_id)。
• 购买purchase(购买订单号order_id,客户号customer_id,商品号goods_id,购买数量nums)。

要求：

• 每个表的主外键
• 客户的姓名不能为空值
• 邮箱不能重复
• 客户的性别(男，女)

建立数据库：

create database if not exists shop;

建立商品表：

create table if not exists goods
(
	goods_id int primary key auto_increment comment '商品编号',
	goods_name varchar(32) not null comment '商品名称',
	unitprice int not null default 0 comment '单价，单位分',
	category varchar(12) comment '商品分类',
	provider varchar(64) not null comment '供应商名称'
);

建立客户表：

create table if not exists customer(
     customer_id int primary key auto_increment comment '客户编号',
     address varchar(256) comment '客户地址',
     name varchar(32) not null comment '客户姓名',
     email varchar(64) unique key comment '电子邮箱',
     sex enum('男','女') not null comment '性别',
     card_id char(18) unique key comment '身份证'
   );

建立购买表：

create table if not exists purchase
(
	order_id int primary key auto_increment comment '订单号',
	customer_id int comment '客户编号',
	goods_id int comment '商品编号',
	nums int default 0 comment '购买数量',
	foreign key (customer_id) references customer(customer_id),
	foreign key (goods_id) references goods(goods_id)
);

七、表的增删查改

CRUD : Create(创建), Retrieve(读取)，Update(更新)，Delete（删除）。

1、Create

增加就是insert。

新建一张表：

指定列进行单行插入：

在values左侧指定了要插入的字段。

全列单行插入：

在values左侧没有指定要插入的字段。

全列多行插入：

指定列多行插入也是同理。

插入替换：

在插入数据是，由于主键或者唯一键对应的值已经存在而导致插入失败。此时可以选择性的进行同步更新操作。

替换：

该语句和插入替换效果一样。

如果出现冲突，就删掉原来的数据，再插入新数据

如果没出现冲突，就直接插入。

2、Retrieve

查询语句是MySQL中最常用的语句。

创建表：

CREATE TABLE exam_result (
     id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
     name VARCHAR(20) NOT NULL COMMENT '同学姓名',
     chinese float DEFAULT 0.0 COMMENT '语文成绩',
     math float DEFAULT 0.0 COMMENT '数学成绩',
     english float DEFAULT 0.0 COMMENT '英语成绩'
);

插入数据：

INSERT INTO exam_result (name, chinese, math, english) VALUES
     ('唐三藏', 67, 98, 56),
     ('孙悟空', 87, 78, 77),
     ('猪悟能', 88, 98, 90),
     ('曹孟德', 82, 84, 67),
     ('刘玄德', 55, 85, 45),
     ('孙权', 70, 73, 78),
     ('宋公明', 75, 65, 30);

（1）select 列

全列查询：

select 后面表示要查询的列，*代表全部，from后面表示从哪张表查询。

指定列查询：

指定查询id列与math列。

select后面是可以跟表达式的：

表达式的结果会被计算出来。

表达式的名字太长了，可以起别名：

as可以被省略：

对查询结果去重：

（2）where 条件

where 条件是一种筛选子句，它决定了要拿哪些行的数据。

比较运算符：

运算符	说明
>,>=,<,<=	大于，大于等于，小于，小于等于
=	等于，NULL不安全，例如NULL=NULL的结果是 NULL
<=>	等于，NULL安全，例如NULL<=>的结果是true(1)
!=,<>	不等于
between a0 and a1	范围匹配，如果a0<=values<=a1，返回true(1)
in (option,......)	如果是option中的任意一个，返回true(1)
is null	是NULL
is not null	不是NULL
like	模糊匹配，%表示任意多个任意符号，_表示任意一个字符

逻辑运算符：

运算符	说明
and	多个条件必须都为 true(1)，结果才是 true(1)
or	任意一个条件为 true(1), 结果为 true(1)
not	条件为 true(1)，结果为 false(0)

查询英语不及格的学生及其分数：

英语不及格，也就是英语成绩小于60。

查询语文成绩在80分~90分的学生及其分数：

两种写法查询出来的结果都是一样的。

查询数学成绩为58分或者59分或者99分的学生及其分数：

查询姓孙的学生：

查询孙某同学：

查询语文成绩好于英语成绩的学生：

总分在200分以下的学生：

给总分起个别名：

报错了，提示total这一列是未知的，我们前面不是把name,chinese+math+english重命名为total吗？为什么还报错？

这其实就是SQL语句执行顺序的问题。

这个顺序很合理，肯定是要先确定从那张表查询，再通过筛选语句选出要的数据，最后才把查询结果显示出来。

也就是就是说，当我们执行where自己时，还没有别名呢，当然用不了别名。

那我们直接在where语句中起别名可以吗：

这样的写法是错误的，语法上不支持。

只能这么写：

查询语文成绩大于80分且不姓孙的学生：

查询孙某同学或者总成绩大于200分并且语文小于数学并且英语大于80分学生：

新建一张表并插入数据，用于测试NULL查询：

查询姓名为NULL的人：

查询姓名为空的人：

查询姓名不为NULL的人：

持续更新中。。。

（3）结果排序

用order by子句对结果进行排序，没有order by子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序。

升序用asc，降序用desc。

查询学生的数学成绩，数学成绩按升序排列：

如果没有指明是asc还是desc，默认按asc排序。

查询学生的数学成绩，数学成绩按降序排列：

NULL参与排序，视为比任何值都小：

查询学生的各门成绩，依次按数学降序、英语升序、语文升序的方式排序：

什么意思呢？先按照数学降序排序，如果数学成绩相等，相等的成绩就按英语升序排序，如果英语成绩也相等，相等的成绩就按语文升序排序。

查询学生的总分，从高到低排序：

没有出现问题，但是有一点指的注意，order by语句这里咋又能用别名了？

先想一想，要对数据进行排序，是不是得现有数据。那表中就有数据，直接对这所有数据排序，如何呢？可以，先排序，再筛选，可以这么做。但是这样做会导致排序时其实有大量数据是不需要排序的（这些数据会被筛选掉）。所以，MySQL必然先筛先得到数据，再进行排序。

查询姓孙或者姓曹的学生的数学成绩，并且按由高到低显示：

（4）筛选分页结果

一个表如果数据量太大，那么筛选出来的结果就可能有一大堆，我们并不想看这么多，此时就可以用limit进行分页。

从表的开始位置连续显示3行：

从2号位置开始，连续显示4行：

表的0号位置为开始位置。

从开始位置连续显示3行，从0号位置开始连续显示3行：

建议：对未知表进行查询时，最好加一条 limit 1，避免因为表中数据过大，查询全表数据导致数据库卡死。

limit的执行顺序在order by之后。

3、Update

将孙悟空同学的数学成绩变更为80分：

将曹孟德同学的数学成绩变更为60分，语文成绩变更为70分：

将总成绩倒数前三的3位同学的数学成绩加上60分：

将所有同学的语文成绩更新为原来的2倍：

Update会修改数据，所以使用时要慎重。特别是对于全表修改。

4、Delete

删除孙悟空同学的考试成绩：

删除总分最低的学生：

新建一张表，用于测试删除整张表：

-- 准备测试表
CREATE TABLE for_delete (
		id INT PRIMARY KEY AUTO_INCREMENT,
		name VARCHAR(20)
);

-- 插入测试数据
INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');

查看表的结构：

该表维护了一个计数器，id字段之所以会自增，就是因为有这个计数器。

删除全表：

再查看表的结构：

此时计数器依为4，并不会因为删掉了所有的数据而清零。

此时插入一个数据：

id从4开始。

再创建一张测试表：

-- 准备测试表
CREATE TABLE for_truncate (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);

-- 插入测试数据
INSERT INTO for_truncate (name) VALUES ('A'), ('B'), ('C');

查看表的结构：

截断表：

truncate的效果和delete一样，但是在细节上有所差异。

再次查看表结构：

此时插入一个数据：

id从1开始。

再查看表结构：

delete和truncate的区别：

• 只能对整表操作，不能像delete一样针对部分数据操作。
• truncate比delete更快，但是truncate在删除数据的时候，并不经过真正的事务，所以无法回滚。（关于事务，后面会讲）
• 会重置AUTO_INCREMENT项。

平时我们在做数据库操作时，操作会被包装成事务，并且操作痕迹会被记录到日志中，方便能对操作溯源与恢复。这也是持久化的一种方式。

持久化的方式：

1. 记录历史SQL语句。
2. 记录数据本身。

truncate操作不会把自己记录到日志中，所以要慎用truncate。

5、插入查询结果

将select和insert组合操作。

创建表并插入数据：

-- 创建原数据表
CREATE TABLE duplicate_table (
		id int,
  	name varchar(20)
);

-- 插入测试数据
INSERT INTO duplicate_table VALUES
     (100, 'aaa'),
     (100, 'aaa'),
     (200, 'bbb'),
     (200, 'bbb'),
     (200, 'bbb'),
     (300, 'ccc');

要求对该表进行去重，需要以下几步：

创建一张新表，该表为空，且和原表结构一样：

create table no_duplicate_table like duplicate_table;

查询出原表中不重复的数据：

把查询结果插入到新表中：

通过重命名实现去重：

6、聚合统计

聚合函数：

函数	说明
count([distinct] expr)	返回查询到的数据的数量
sum([distinct] expr)	返回查询到的数据的总和，不是数字没有意义
avg([distinct] expr)	返回查询到的数据的平均值，不是数字没有意义
max([distinct] expr)	返回查询到的数据的最大值，不是数字没有意义
min([distinct] expr)	返回查询到的数据的最小值，不是数字没有意义

统计有多少行：

统计不重复的数学成绩个数：

统计数学总分：

统计英语不及格的人数：

统计数学平均分：

统计总分的平均分：

统计英语最高分：

统计时加上name会怎么样呢：

报错了，这也是应该的，五个人有五不同的个name，英语最高分却只有一个，如何能形成表呢？

那如果我就想看到最该分及其对应的学生姓名怎么办？何须用聚合函数，直接降序排列，再limit 1即可。

统计数学高于100分的最低分：

再补充一点：

NULL不会计入统计结果。

使用 * 来统计：

不受NULL影响。

7、分组查询

分组的目的是为了分组之后，方便进行聚合统计。

先准备数据：

https://www.alipan.com/s/Tt5WnWPDyzN

想要统计每个部门的最高工资与平均工资，该如何统计呢？根据前面的学习，我们只能统计全公司中最高工资与平均工资：

此时就需要用到先分组再聚合统计了：

group by 后面跟列名，指定根据该列来进行分组，但实际分组，使用该列的不同的行数据进行分组的。

分组之后，在同一组内，分组条件一定是相同的，比如上面的deptno。这也就意味着，deptno是可以被聚合压缩的。之所以就能够显示出来，是因为一个组内只有一个平均值、一个最大值、一个deptno。

分组，在我看来就是“分表”。就是把一张表按照条件在逻辑上拆成了多个表，然后分别对各自的子表进行聚合统计。

显示每个部门的每种岗位的平均工资和最低工资：

这次不仅要分组，还有进行多次分组。

先分组再聚合图解：

①这张是总表：

②先按照deptno进行分组

③再按照job进行分组

④宏观步骤：

显示平均工资低于2000的部门的平均工资：

第一步要统计出每个部门的平均工资，第二步对聚合统计的结果进行判断。

这里用到了having 筛选条件。这是新东西，它一般和group by搭配使用，语义其实和where 筛选条件一样，区别在于筛选的阶段不同，having是分组之后，对最后聚合统计的结果进行条件判断。

一定是现有聚合统计的结果才去筛选，所以having的运行顺序是靠后的。

显示每个部门每个岗位的平均工资，要求显示的岗位平均工资小于2000，且SMITH员工不参与统计：

执行顺序：

在此处输出一个理解：

不要单纯地认为，只有在磁盘上表结构导入到MySQL，真实存在的表，才叫做表。中间筛选出来的，包括最终结果，都是逻辑上的表。“MySQL一切皆表”。

八、内置函数

1、日期函数

函数名称	描述
current_date()	当前日期
current_time()	当前时间
current_timestamp()	当前时间戳
date(datetime)	返回datetime参数的日期部分
date_add(date,interval d_value_type)	在date中增加日期或时间，interval后面的数字单位可以是year、minute、second、day
date_sub(date,interval d_value_type)	在date中减少日期或时间，interval后面的数字单位可以是year、minute、second、day
datediff(date1,date2)	两个日期的差，单位是天
now()	当前日期时间

获取年月日：

获取时分秒：

获取时间戳：

获取日期时间：

提取日期部分：

提取now函数中的日期部分：

增加时间：

减少时间：

求两个日期的差距：

创建一表，记录生日：

reate table tmp(
     id int primary key auto_increment,
     birthday date
);

插入数据：

创建一个留言表：

create table msg (
     id int primary key auto_increment,
     content varchar(30) not null,
     sendtime datetime
);

插入数据（发表留言）：

查找2分钟内发表的留言：

2、字符串函数

函数名称	描述
charset(str)	返回字符串的字符集
concat(str1,str2,......)	连接字符串
instr(str,substr)	返回substr在str中出现的位置，没有返回0
ucase(str)	转换成大写
lcase(str)	转换成小写
left(str,length)	从str的左边起取length个字符
right(str,length)	从str的右边起取length个字符
length(str)	str的长度（单位字节）
replace(str,search_str,replace_str)	在str中用replace_str替换成search_str
strcmp(str1,str2)	逐字符比较两个字符串的大小
substring(str,postion[,length])	从str的postion开始，取length个字符
ltrim(str) rtrim(str) trim(str)	去除前空格或后空格

获取字符串的字符集：

字符串拼接：

在字符串中查找字符串：

将字符串转换成大写：

将字符串转换成小写：

从字符串中提取字符：

获取字符串长度：

在str中找到search_str并替换成replace_str：

比较字符串大小：

获取emp表的ename列的字符集：

要求显示exam_result表中的信息，显示格式：“XXX的语文是XXX分，数学XXX分，英语XXX分”：

求student表中学生姓名占用的字节数：

将emp表中所有名字中有S的替换成'上海'：

replace不会修改原数据：

截取EMP表中ename字段的第二个到第三个字符：

以首字母小写的方式显示所有员工的姓名：

去除左空格：

去除右空格：

去除左右两边的空格：

中间的空格不会被去除：

3、数学函数

函数名称	描述
abs(number)	绝对值函数
bin(decimal_number)	十进制转换为二进制
hex(decimal_number)	十进制转换为十六进制
conv(number,from_base,to_base)	进制转换
ceiling(number)	向上取整
floor(number)	向下取整
format(number,decimal_places)	格式化，保留小数位数
rand()	返回随机浮点数，范围[0.0,1.0)
mod(number,denominator)	取模，求余

取绝对值：

将十进制数字转换为二进制：

将十进制数字转换为十六进制：

将一个数字从一个进制转换成另一个进制：

格式化，指定精度：

模运算：

生成随机数：

关于取整：

除了四舍五入，还有很多其他的取整方式，比如：

• 零向取整
• 向上取证
• 向下取整

向上取整：

向下取整：

4、其它函数

user()，查询当前用户：

md5(str)，对一个字符串进行md5摘要，摘要后得到一个32位字符串，可以用于密码加密：

database()，显示当前正在使用的数据库：

password()函数，MySQL数据库使用该函数对用户加密：

ifnull(val1, val2) 如果val1为null，返回val2，否则返回val1的值：

九、复合查询

1、子查询

在MySQL中，允许在一条SQL语句内部再执行一条select查询。

显示工资最高的员工的名字和工作岗位：

显示工资高于平均工资的员工信息：

2、笛卡尔积

实际开发中往往数据来自不同的表，所以需要多表查询。

scott数据库中有三张表，emp,dept,salgrade，接下来将会用这三张表来演示如何进行多表查询。

显示员工名、员工工资、以及所在部门的名称：

emp表中只有员工所在部门的编号，部门名称在dept表中，所以需要在多张表中进行查询。

①对两张表进行笛卡尔积：

在指定表时指定了emp和dept两张表。

可以从查询结果中看出，其实是把emp表的数据和dept表的数据进行组合，比如说，SMITH这一个人的数据，就和dept每一个数据进行一次组合，最终SMITH这一个人的数据就有四行。

这其实就是将数据进行穷举组合，这种组合方式称为笛卡尔积。

将两张表进行笛卡尔积之后，不就把两张表合并为一张表了么？一张表的查询还不会么？

还有一点，在将两张表进行合并后，有一些数据是没有意义的，比如：

emp表中的deptno和dept表中的deptno如果不一样，这样的数据就没有意义。

②去除没有意义的数据：

③进行查询：

显示部门号为10的部门名，员工名和工资：

显示各个员工的姓名，工资，及工资级别：

3、自连接

前面我们对两张不同的表进行了笛卡尔积，那能否对同一张表进行笛卡尔积呢？

对同一张表进行笛卡尔积：

两张表名称一样不行，要重命名。

这种对自己进行笛卡尔积的行为称为自连接。

显示员工FORD的上级领导的编号和姓名：

emp表中mgr字段就是该员工上级领导的编号。

也可以用子查询来实现：

4、再谈子查询

子查询是指嵌入在其他SQL语句中的select语句，也叫嵌套查询。

（1）单行子查询

返回一行记录的子查询

显示与SMITH同一部门的员工：

（2）多行子查询

返回多行记录的子查询。

查询和10号部门的工作岗位相同的员工的名字，岗位，工资，部门号，但是不包含10号部门自己的员工：

in:在范围内的值，只要有就true。

显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号：

all: 与子查询返回的所有值比较为true，则返回true。

显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工）：

any:与子查询返回的任何值比较为true，则返回true。

（3）多列子查询

单行子查询是指子查询只返回单列、单行数据；多行子查询是指返回单列、多行数据，都是针对单列而言的，而多列子查询则是指查询返回多个列数据的子查询语句。

查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人：

任何时刻，查询出来的临时结果，本质在逻辑上也是一张表。

（4）在from子句中使用子查询

前面我们用的子查询，都是出现在where 条件中。

显示每个高于自己部门平均工资的员工的信息：

显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资：

查找每个部门工资最高的人的姓名、工资、部门、最高工资：

解决多表问题的本质：想办法把多表转换成单表。

5、内连接

内连接实际上就是利用where子句对两张表形成的笛卡儿积进行筛选，我们前面学习的查询都是内连接，也是在开发过程中使用的最多的连接查询。

语法：

select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件;

这种写法在语法上更规范。

显示SMITH的名字和部门名称：

6、外连接

（1）左外连接

能够让左侧的表完全保留。

语法：

select 字段名 from 表名1 left join 表名2 on 连接条件;

创建测试表：

-- 建两张表
create table stu (id int, name varchar(30)); -- 学生表
insert into stu values(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');
create table exam (id int, grade int); -- 成绩表
insert into exam values(1, 56),(2,76),(11, 8);

查询所有学生的成绩，如果这个学生没有成绩，也要将学生的个人信息显示出来：

列出部门名称和这些部门的员工信息，同时列出没有员工的部门：

（2）右外连接

能够让右侧的表完全保留。

语法：

select 字段 from 表名1 right join 表名2 on 连接条件;

对stu表和exam表联合查询，把所有的成绩都显示出来，即使这个成绩没有学生与它对应，也要显示出来：

十、索引

1、认识索引

索引：

提高数据库的性能，索引是物美价廉的东西了。不用加内存，不用改程序，不用调sql，只要执行正确的 create index ，查询速度就可能提高成百上千倍。但是天下没有免费的午餐，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的IO。所以它的价值，在于提高海量数据的检索速度。

常见索引分为：

• 主键索引(primary key)
• 唯一索引(unique)
• 普通索引(index)
• 全文索引(fulltext)--解决中子文索引问题。

MySQL的服务器，本质上是在内存中运行的。所有的数据库CURD操作，也全都是在内存中进行的。索引也是如此。

提高算法效率的因素：

• 组织数据的方式
• 算法本身

索引就是通过改变组织数据的方式来提高检索速度。

接下来要见一见索引。

导入数据：

https://www.alipan.com/s/E9QoEQL2Lew

导入的数据是一张有8000000条数据的表，创建表时是没有创建索引的。

查询员工编号为998877的员工：

花了4.16秒。

给表添加索引：

alter table EMP add index (empno);

再次查询员工编号为998877的员工：

查询效率大大提高。

2、认识磁盘

MySQL与存储：

MySQL 给用户提供存储服务，而存储的都是数据，数据在磁盘这个外设当中。磁盘是计算机中的一个机械设备，相比于计算机其他电子元件，磁盘效率是比较低的，在加上IO本身的特征，可以知道，如何提高效率，是 MySQL 的一个重要话题。

磁盘：

磁盘中的一个盘片：

扇区：

数据库文件，本质其实就是保存在磁盘的盘片当中。也就是上面的一个个小格子中，就是我们经常所说的扇区。当然，数据库文件很大、也很多，一定需要占据多个扇区。

题外话：

• 从上图可以看出来，在半径方向上，距离圆心越近，扇区越小，距离圆心越远，扇区越大。
• 那么，所有扇区都是默认512字节吗？目前是的，我们也这样认为。因为保证一个扇区多大，是由比特位密度决定的。
• 不过最新的磁盘技术，已经慢慢的让扇区大小不同了，不过我们现在暂时不考虑。

我们在使用Linux，所看到的大部分目录或者文件，其实就是保存在硬盘当中的。所以，最基本的，找到一个文件的全部，本质，就是在磁盘找到所有保存文件的扇区。而我们能够定位任何一个扇区，那么便能找到所有扇区，因为查找方式是一样的。

定位扇区：

• 柱面(磁道): 多盘磁盘，每盘都是双面，大小完全相等。那么同半径的磁道，整体上便构成了一个柱面。
• 每个盘面都有一个磁头，那么磁头和盘面的对应关系便是1对1的。
• 所以，我们只需要知道，磁头（Heads）、柱面(Cylinder)(等价于磁道)、扇区(Sector)对应的编号。即可在磁盘上定位所要访问的扇区。这种磁盘数据定位方式叫做CHS，不过实际系统软件使用并不是CHS（但硬件是），而是LBA，一种线性地址，可以想象成虚拟地址与物理地址。系统将LBA地址最后会转化成为CHS，交给磁盘去进行数据读取。不过，我们现在不关心转化细节。

结论：

我们现在已经能够在硬件层面定位，任何一个基本数据块了(扇区)。那么在系统软件上，就直接按照扇区(512字节，部分4096字节),进行IO交互吗？不是

• 如果操作系统直接使用硬件提供的数据大小进行交互，那么系统的IO代码，就和硬件强相关，换言之，如果硬件发生变化，系统必须跟着变化。
• 从目前来看，单次IO 512字节，还是太小了。IO单位小，意味着读取同样的数据内容，需要进行多次磁盘访问，会带来效率的降低。
• 之前学习文件系统，就是在磁盘的基本结构下建立的，文件系统读取基本单位，就不是扇区，而是数据块。

故，系统读取磁盘，是以块为单位的，基本单位是4kb。

磁盘随机访问(Random Access)与连续访问(Sequential Access)：

随机访问：本次IO所给出的扇区地址和上次IO给出扇区地址不连续，这样的话磁头在两次IO操作之间需要作比较大的移动动作才能重新开始读/写数据。

连续访问：如果当次IO给出的扇区地址与上次IO结束的扇区地址是连续的，那磁头就能很快的开始这次IO操作，这样的多个IO操作称为连续访问。

因此尽管相邻的两次IO操作在同一时刻发出，但如果它们的请求的扇区地址相差很大的话也只能称为随机访问，而非连续访问。

磁盘是通过机械运动进行寻址的，随机访问不需要过多的定位，故效率比较高。

3、MySQL与磁盘交互基本单位

MySQL作为一款应用软件，可以想象成一种特殊的文件系统。它有着更高的IO场景，所以，为了提高本的IO效率，MySQL进行IO的基本单位是16kb。这个基本数据单元，在MySQL这里叫做page（页）（注意和系统的page区分）。（后面统一使用InnoDB存储引擎讲解）

4、建立共识

• MySQL中的数据文件，是以page为单位保存在磁盘当中的。
• MySQL的CURD操作，都需要通过计算，找到对应的插入位置，或者找到对应要修改或者查询的数据。
• 而只要涉及计算，就需要CPU参与，而为了便于CPU参与，一定要能够先将数据移动到内存当中。
• 所以在特定时间内，数据一定是磁盘中有，内存中也有。后续操作完内存数据之后，以特定的刷新策略，刷新到磁盘。而这时，就涉及到磁盘和内存的数据交互，也就是IO了。
• 为了更好的进行上面的操作，MySQL服务器在内存中运行的时候，在服务器内部，就申请了被称为 buffer_pool 的的大内存空间，来进行各种缓存。其实就是很大的内存空间，来和磁盘数据进行IO交互。
• 为了更高的效率，一定要尽可能的减少系统和磁盘IO的次数。

5、理解索引

建立测试表：

一定要添加主键，只有这样才会默认生成主键索引。

create table if not exists user (
     id int primary key,
     age int not null,
     name varchar(16) not null
);

插入数据：

注意，我们并没有按照主键的大小顺序插入。

insert into user (id, age, name) values(3, 18, '杨过');
insert into user (id, age, name) values(4, 16, '小龙女');
insert into user (id, age, name) values(2, 26, '黄蓉');
insert into user (id, age, name) values(5, 36, '郭靖');
insert into user (id, age, name) values(1, 56, '欧阳锋');

查看插入结果：

查询出来的结果居然变成了有序。

我们向一个具有主键的表中乱序插入数据，发现数据会自动排序。

如何理解MySQL中page的概念：

MySQL内部，一定需要并且会存在大量的page。也就决定了MySQL必须将多个同时存在的page管理起来（如何管理？先描述，再组织）。所以，不要简单地将page认为是一个内存块，page内部也必须写入对应的管理信息。

//例如
struct page
{
	struct page* prev;
    struct page* next;
    char buffer[NUM];
};//16kb
//申请一个page，就是：
new page;
//将所有的page用"链表"的形式连接起来。
//这样就简单粗暴地在buffer_pool内部，对MySQL中的page进行建模并管理。

理解单个page：

因为有主键的存在，MySQL会默认按照主键给数据进行排序，从上面的page内的数据记录可以看出，数据是有序且彼此关联的。

为什么数据库在插入数据时要对其进行排序呢？我们按正常顺序插入数据不是也挺好的吗？

插入数据时排序的目的，就是优化查询的效率。 page内部存放数据的模块，实质上也是一个链表的结构，链表的特点也就是增删快，查询修改慢，所以优化查询的效率是必须的。 正是因为有序，在查找的时候，从头到后都是有效查找，没有任何一个查找是浪费的，而且，如果运气好，是可以提前结束查找过程的。

理解多个page：

• 通过上面的分析，我们知道，上面page模式中，只有一个功能，就是在查询某条数据的时候直接将一整个page的数据加载到内存中，以减少硬盘IO次数，从而提高性能。但是，我们也可以看到，现在的page模式内部，实际上是采用了链表的结构，前一条数据指向后一条数据，本质上还是通过数据的逐条比较来取出特定的数据。
• 如果有1千万条数据，一定需要多个page来保存1千万条数据，多个page彼此使用双链表链接起来，而且每个page内部的数据也是基于链表的。那么，查找特定一条记录，也一定是线性查找。这效率也太低了。

页目录：

我们在看《谭浩强C程序设计》这本书的时候，如果我们要看<指针章节>，找到该章节有两种做法

• 从头逐页的向后翻，直到找到目标内容。
• 通过书提供的目录，发现指针章节在234页(假设)，那么我们便直接翻到234页。同时，查找目录的方案，可以顺序找，不过因为目录肯定少花了时间，所以可以快速提高定位。
• 本质上，书中的目录，是多花了纸张的，但是却提高了效率。
• 所以，目录，是一种“空间换时间的做法”。、

单页情况：

针对单个page，也可以引入目录。

现在在一个Page内部，我们引入了目录。比如，我们要查找id=4记录，之前必须线性遍历4次，才能拿到结果。现在直接通过目录2[3]，直接进行定位新的起始位置，提高了效率。为何通过键值 MySQL 会自动排序？ 因为可以很方便引入目录。

多页情况：

MySQL 中每一个page的大小只有 16KB ，单个page大小固定，所以随着数据量不断增大， 16KB 不可能存下所有的数据，那么必定会有多个page来存储数据。

需要注意，上面的图，是理想结构，是为了保证目前整体有序，那么新插入的数据，不一定会在新Page上面，这里仅仅做演示。

我们可以通过多个page遍历，page内部通过目录来快速定位数据。可是，貌似这样也有效率问题，在page之间，也是需要 MySQL 遍历的，遍历意味着依旧需要进行大量的IO，将下一个page加载到内存，进行线性检测。这样就显得我们之前的page内部的目录，有点杯水车薪了。

如何解决这个问题呢？就是给page也加上目录。

• 使用一个目录项来指向某一页，而这个目录项存放的就是将要指向的page中存放的最小数据的键值。
• 和page内目录不同的地方在于，这种目录管理的级别是page，而page内目录管理的级别是行。
• 其中，每个目录项的构成是：键值+指针。图中没有画全。

其实目录页的本质也是页，普通页中存的数据是用户数据，而目录页中存的数据是普通页的地址。

可是，我们每次检索数据的时候，该从哪里开始呢？虽然顶层的目录页少了，但是还要遍历啊？不用担心，可以在再加目录页。

这个，其实就是传说中的B+树（B+树只有叶子结点会用链表连接起来，上面的结实际上也是只有叶子结点会用链表连起来）。 至此，完成了对表的主键索引的构建。一般我们给表进行CURD操作时，就是在上面这个结构下进行的。

那么，如果在建表时没有主键呢？也是这样的结构吗？

是的，也是这样的结构。在建表时如果没有指定主键，MySQL会自动生成一个隐藏列，用这个隐藏列来充当主键、构建索引。

几个细节：

• 叶子结点保存数据，路上节点没有。非叶子结点，不要数据，只要目录项。这也就意味着可以存更多的目录项。也就意味着一个目录页可以管理更多的叶子结点（数据）。这一定是一棵 矮胖型 的树结构，这就意味着从根节点到叶子结点经过的节点减少。经过的叶子结点少了，那就代表需要的page少了。也就是需要加载到内存的page少了，也就减少了IO次数，提高了效率。
• 叶子结点全部用链表连接起来，这是B+树的特点。这个特点能够更方便进行范围查找。

6、为何选择B+树

InnoDB 在建立索引结构来管理数据的时候，为什么选择了B+树？其他数据结构为何不行？

• 链表？线性遍历，效率太低。
• 二叉搜索树？存在效率退化的问题，可能退化为线性结构。
• AVL树与红黑树？虽然是平衡或者近似平衡，但是毕竟是二叉结构，相比较多叉的B+树，意味着树整体高度过高，大家都是自顶向下找，层高越低，意味着系统与硬盘更少的IO Page交互。所以还是B+树更优秀。
• 哈希？哈希的搜索效率确实很高，但是它有一个短板，就是不方便进行范围查找。
• B树？

B树：

B+树：

• B树的节点，既有目录页，又有数据，这也就意味着一个目录页所能管理的子目录页就变少了。导致了B树相较于B+树会更高一些。
• B树的叶子结点是没有相连的，不方便进行范围查找。

7、聚簇索引与非聚簇索引

MyISAM 引擎同样使用B+树作为索引结构，叶子节点的data域存放的是数据记录的地址。下图为 MyISAM 表的主索引， Col1 为主键。

这种将B+树与数据分离的存储方案称为非聚簇索引。

InnoDB 是将索引和数据放在一起的。这种存储方案是聚簇索引。

验证：

create database test_db; --创建数据库

use test_db;

 create table mtest(
    id int primary key,
    name varchar(11) not null
)engine=MyISAM; --使用engine=MyISAM

create table itest(
    id int primary key,
    name varchar(11) not null
)engine=InnoDB; --使用engine=InnoDB

当然， MySQL 除了默认会建立主键索引外，我们用户也有可能建立按照其他列信息建立的索引，一般这种索引可以叫做辅助（普通）索引。 对于 MyISAM ,建立辅助（普通）索引和主键索引没有差别，无非就是主键不能重复，而非主键可重复。

下图就是基于 MyISAM 的 Col2 建立的索引，和主键索引没有差别：

同样， InnoDB 除了主键索引，用户也会建立辅助（普通）索引，我们以上表中的 Col3 建立对应的辅助索引如下图：

可以看到， InnoDB 的非主键索引中叶子节点并没有数据，而只有对应记录的key值。

所以通过辅助（普通）索引，找到目标记录，需要两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。这种过程，就叫做回表查询。

为何 InnoDB 针对这种辅助（普通）索引的场景，不给叶子节点也附上数据呢？原因就是太浪费空间。

8、索引操作

（1）查询索引

第一种方式：

show keys from 表名;

第二种方式：

show index from 表名;

（2）创建主键索引

第一种方式：

-- 在创建表的时候，直接在字段名后指定 primary key
create table user1(
  id int primary key, 
  name varchar(30)
);

第二种方式：

-- 在创建表的最后，指定某列或某几列为主键索引
create table user2(
  id int, name varchar(30),
  primary key(id)
);

第三种方式：

create table user3(
  id int, name varchar(30)
);
-- 创建表以后再添加主键
alter table user3 add primary key(id);

（3）创建唯一索引

第一种方式：

-- 在表定义时，在某列后直接指定unique唯一属性。
create table user4(
  id int primary key,
  name varchar(30) unique
);

第二种方式：

-- 创建表时，在表的后面指定某列或某几列为unique
create table user5(
  id int primary key, 
  name varchar(30),
  unique(name)
);

第三种方式：

create table user6(
  id int primary key,
  name varchar(30)
);

alter table user6 add unique(name);

（4）创建普通索引

第一种方式：

create table user8(id int primary key,
	name varchar(20),
	email varchar(30),
	index(name) --在表的定义最后，指定某列为索引
);

第二种方式：

create table user9(
  id int primary key,
  name varchar(20),
  email varchar(30)
);

alter table user9 add index(name); --创建完表以后指定某列为普通索引

第三种方式：

create table user10(
  id int primary key,
  name varchar(20),
  email varchar(30));
  
-- 创建一个索引名为 idx_name 的索引
create index idx_name on user10(name);

（5）创建复合索引

可以创建以多列为主键的索引结构。

create table if not exists test1(
    id int primary key,
    name varchar(20) not null,
    email varchar(20) not null
);

创建复合索引：

查询：

创建复合索引时并不会创建多棵树，而是会用创建复合索引的多列共同作为主键来构建同一棵树。

什么时候要用复合索引呢？

比如我们希望高频地用name来查找email，用name和email创建一个复合索引，当name匹配上时，也就同时找到了对应的email（就不需要通过name找到对应的主键，再用主键去主键索引中找到email）。注意最左匹配原则，也就是说复合索引在匹配时是从左往右匹配的，可以用如果是用(name,email)创建的复合索引，可以通过name查找email，但不能通过email查找name。

（6）创建全文索引

当对文章字段或有大量文字的字段进行检索时，会使用到全文索引。MySQL提供全文索引机制，但是有要求，要求表的存储引擎必须是MyISAM，而且默认的全文索引支持英文，不支持中文。如果对中文进行全文检索，可以使用sphinx的中文版(coreseek)。

CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title,body)
)engine=MyISAM;

INSERT INTO articles (title,body) VALUES
    ('MySQL Tutorial','DBMS stands for DataBase ...'),
    ('How To Use MySQL Well','After you went through a ...'),
    ('Optimizing MySQL','In this tutorial we will show ...'),
    ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    ('MySQL vs. YourSQL','In the following database comparison ...'),
    ('MySQL Security','When configured properly, MySQL ...');

查询有没有database数据：

虽然查询出数据，但是没有使用到全文索引。

可以用explain工具看一下，是否使用到索引：

使用全文索引：

（7）删除索引

删除主键索引：

alter table 表名 drop primary key;

删除其它索引：

alter table 表名 drop index 索引名;

drop index 索引名 on 表名;

索引名可以通过上面的查询索引来查询。

（8）索引创建原则

• 比较频繁作为查询条件的字段应该创建索引。
• 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件。
• 更新非常频繁的字段不适合作创建索引。
• 不会出现在where子句中的字段不该创建索引。

十一、事务

1、什么是事务

MySQL是一个网络服务，是有可能被多个用户同时访问的，数据库中的数据是被所有用户共享的。MySQL内部是采用多线程的方式来实现的，所以就注定了会有对数据并发访问的场景。为了解决这样的问题，MySQL为我们提供了事务的概念。

实例：

客户端A发现还有一张票，于是就打算卖掉这张票（进入if语句），同一时间（客户端A进入if语句了，但是还没修改票数），客户端B发现还有一张票，就要卖掉这张票（进入if语句），然后客户端A修改了票数，此时票数为0，然后客户端B也修改了票数，此时票数为-1，这也就意味着同一张票，被卖了两次。

如何解决呢：

• 买票的过程得是原子的。
• 买票互相应该不能影响。
• 买完票应该要永久有效。
• 买前，和买后都要是确定的状态。

事务的概念：

事务是由一组DML语句组成，这些语句在逻辑上存在相关性，这一组DML语句要么全部成功，要么全部失败，是一个整体。（比如转账，其实就是给我的余额update减100，再给别人的余额update加100，这两条update语句要么全部成功，要么全部失败，不可能给我的余额减了100但是不给别人的余额加100）MySQL提供一种机制，保证我们达到这样的效果。

事务就是要做的或所做的事情，主要用于处理操作量大，复杂度高的数据。假设一种场景：你毕业了，学校的教务系统后台 MySQL 中，不在需要你的数据，要删除你的所有信息(一般不会:) ), 那么要删除你的基本信息(姓名，电话，籍贯等)的同时，也删除和你有关的其他信息，比如：你的各科成绩，你在校表现，甚至你在论坛发过的文章等。这样，就需要多条 MySQL 语句构成，那么所有这些操作合起来，就构成了一个事务。

正如我们上面所说，一个 MySQL 数据库，可不止你一个事务在运行，同一时刻，甚至有大量的请求被包装成事务，在向 MySQL 服务器发起事务处理请求。而每条事务至少一条 SQL ，也可以有很多 SQL ,这样如果大家都访问同一张表数据，在不加保护的情况，就绝对会出现问题。甚至，因为事务由多条 SQL 构成，那么，也会存在事务中的SQL执行到一半出错或者不想再执行的情况，那么已经执行的怎么办呢？

所有，一个完整的事务，绝对不是简单的 sql 集合，还需要满足如下四个属性：

• 原子性（Atomicity）：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• 一致性（Consistency）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• 隔离性（Isolation）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（ Read uncommitted ）、读提交（ read committed ）、可重复读（ repeatable read ）和串行化（ Serializable ）
• 持久性（Durability）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

上面四个属性可以简称为ACID。

2、为什么要有事务

事务被 MySQL 编写者设计出来，本质是为了当应用程序访问数据库的时候，事务能够简化我们的编程模型，不需要我们去考虑各种各样的潜在错误和并发问题。可以想一下当我们使用事务时，要么提交，要么回滚，我们不会去考虑网络异常了，服务器宕机了，同时更改一个数据怎么办对吧？因此事务本质上是为了应用层服务的。而不是伴随着数据库系统天生就有的。

备注：后面把MySQL一行信息称为一行记录。

3、事务的版本支持

在MySQL中，只有InnoDB存储引擎的数据库或表才支持事务，MyISAM不支持。

查看数据库引擎：

show engines;

4、事务的提交方式

事务的提交方式有两种：

• 手动提交
• 自动提交

查看事务提交方式：

show variables like 'autocommit';

用 SET 来改变 MySQL 的自动提交模式：

set autocommit=0;

关闭自动提交。

set autocommit=1;

打开自动提交。

5、事务的常见操作方式

为了便于演示，我们将MySQL的默认隔离级别设置为读未提交（先使用，后面再讲解）：

set global transaction isolation level read uncommitted;

设置好后需要重启客户端：

查看事务的隔离级别：

已经修改为了读未提交。

创建测试表：

create table if not exists account(
    id int primary key,
    name varchar(50) not null default '',
    blance decimal(10,2) not null default 0.0
)ENGINE=InnoDB DEFAULT CHARSET=UTF8;

正常演示1-进行事务的开始与回滚：

可以用 start transaction 和 begin 来启动事务。

正常演示2-不设置保存点：

正常演示3-提交事务后再回滚：

事务提交后，对数据的修改就是永久的（持久性）。

回滚操作，在事务运行期间可以进行回滚，一旦事务提交了，就无法进行回滚了。

非正常演示1-未提交事务，客户端异常终止：

当客户端A异常终止时，MySQL完成了自动回滚，这也就保证了，事务内的操作，要么全部完成，要么全部不完成，没有中间状态（原子性）。

非正常演示2-提交事务，客户端异常终止：

只要事务被提交了，对数据的修改就是永久的，即便系统故障也不会丢失（持久性）。

非正常演示3 - 对比试验，证明begin操作会自动更改提交方式，不会受MySQL是否自动提交影响：

上面我们说了，MySQL的事务提交方式默认是自动提交的，但是经过我们刚才的演示发现，不进行commit，出现异常了，数据直接自动回滚。能回滚，就说明没有被自动提交。换言之，我们的 begin-commit 是手动开启的事务，就一定要手动提交，它不会受 autocommit 的影响。

同样的试验，在autocommit=1与autocommit=0的情况下，试验结果都是一样的，也就证明了 begin-commit 是手动开启并提交事务，它不会受 autocommit 的影响。那么autocommit到底有啥用呢？后面再说。

非正常演示4-证明单条SQL语句与事务的关系：

我们发现客户端异常终止后数据被回滚了。

开启自动提交后，这一次客户端异常终止后，被删除的数据永久消失了。

从上面的两次试验可以看出，autocommit会影响单条SQL语句。单条SQL就相当于一个事务，单条SQL语句都会被MySQL打包成事务（即使我们没写begin-commit）。

关闭自动提交后，单条SQL语句后，手动提交事务，即使客户端异常终止了，对数据的修改依旧是永久的。

结论：

• 只要输入begin或者start transaction，事务便必须要通过commit提交，才会持久化，与是否设置set autocommit无关。
• 事务可以手动回滚，同时，当操作异常，MySQL会自动回滚。
• 对于 InnoDB 每一条 SQL 语言都默认封装成事务，自动提交。（select有特殊情况，因为MySQL 有 MVCC ）
• 从上面的例子，我们能看到事务本身的原子性(回滚)，持久性(commit)，那么隔离性呢？一致性呢？现在还看不出来。

事务操作注意事项：

• 如果没有设置保存点，也可以回滚，只能回滚到事务的开始。直接使用 rollback(前提是事务还没有提交)。
• 如果一个事务被提交了（commit），则不可以回滚（rollback）。
• 可以选择回退到哪个保存点。
• InnoDB 支持事务， MyISAM 不支持事务。
• 开始事务可以使 start transaction 或者 begin。

6、事务的隔离级别

如何理解隔离性：

• MySQL服务可能会同时被多个客户端进程(线程)访问，访问的方式以事务方式进行。
• 一个事务可能由多条SQL构成，也就意味着，任何一个事务，都有执行前，执行中，执行后的阶段。而所谓的原子性，其实就是让用户层，要么看到执行前，要么看到执行后。执行中出现问题，可以随时回滚。所以单个事务，对用户表现出来的特性，就是原子性。
• 但，毕竟所有事务都要有个执行过程，那么在多个事务各自执行多个SQL的时候，就还是有可能会出现互相影响的情况。比如：多个事务同时访问同一张表，甚至同一行数据。
• 就如同你妈妈给你说：你要么别学，要学就学到最好。至于你怎么学，中间有什么困难，你妈妈不关心。那么你的学习，对你妈妈来讲，就是原子的。那么你学习过程中，很容易受别人干扰，此时，就需要将你的学习与别人隔离开，保证你的学习环境是健康的。
• 数据库中，为了保证事务执行过程中尽量不受干扰，就有了一个重要特征：隔离性。
• 数据库中，允许事务受不同程度的干扰，就有了一种重要特征：隔离级别。
• 隔离，基本都是通过锁实现的，不同的隔离级别，锁的使用是不同的。

有两个事务，一个要修改数据，一个要查询数据，谁先执行呢？按理来说，应该先修改数据，再查询数据，这样才能保证查询出来的数据是最新的。然而实际并不是这样的，哪一个事务先执行，取决于谁先到来。修改数据的事务先到，那么后到的查询数据的事务就会查到最新的数据。如果查询数据的事务先到，那么就会查到老的数据，然后再修改数据。

其实，我就是想说，不同的事务，它们的生命周期是不一样的，到来的时间也是不一样的，那就导致了它们看到的数据是有可能不一样的。一个事务看到的数据不一定是最新的。

在事务中，隔离是必须的。所谓隔离，是对运行中的事务进行隔离。隔离存在的意义，就是尽量保证在事务运行中不会出现互相干扰。而根据出现干扰程度的不同，引入了隔离级别的概念。

隔离级别：

• 读未提交【Read Uncommitted】： 在该隔离级别，所有的事务都可以看到其他事务没有提交的执行结果。（实际工作中不可能使用这种隔离级别的），但是相当于没有任何隔离性，也会有很多并发问题，如脏读，幻读，不可重复读等，我们上面为了做实验方便，用的就是这个隔离性。
• 读提交【Read Committed】 ：该隔离级别是大多数数据库的默认的隔离级别（不是 MySQL 默认的）。它满足了隔离的简单定义:一个事务只能看到其他的已经提交的事务所做的改变。这种隔离级别会引起不可重复读，即一个事务执行时，如果多次 select， 可能得到不同的结果。
• 可重复读【Repeatable Read】： 这是 MySQL 默认的隔离级别，它确保同一个事务，在执行中，多次读取操作数据时，会看到同样的数据行。但是会有幻读问题。
• 串行化【Serializable】: 这是事务的最高隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决了幻读的问题。它在每个读的数据行上面加上共享锁，。但是可能会导致超时和锁竞争（这种隔离级别太极端，实际工作基本不使用）。

（1）查看与设置隔离级别

查看隔离级别：

有三种方式。

select @@global.tx_isolation;--查看全局隔离界别

select @@session.tx_isolation;--查看当前会话的隔离级别，新起一个会话时会用global来初始化

select @@tx_isolation;--与上一个一样

设置隔离级别：

set [session | global] transaction isolation level [read uncommit | read commit | repeatable read | serializable]

设置当前会话的隔离级别为读提交：

此时另起一个客户端：

这也就表明了，修改当前会话的隔离级别，只会影响自己本次会话的隔离级别，并不会影响其它会话。

设置全局隔离级别为串行化：

修改全局隔离级别后，当前会话的隔离级别并没有收到影响，这其实也很正常，全局隔离级别和当前会话隔离级别是两个不同的变量。但是当一个会话开始时，会用全局隔离级别来初始化当前会话隔离级别。

新起一个客户端：

（2）读未提交

先将全局隔离级别设置为读未提交：

两个事务并发运行：

客户端A在事务运行中的每一步操作之后，客户端B就能立刻看到，我们常说事务里的操作要被看做一个整体，要么不做，要么就全做。现在客户端A的事务做了一半，就被客户端B看到了，这就是读未提交，客户端B读到了客户端A还没有提交的数据。 这就是隔离级别读未提交的表现。

读未提交隔离级别几乎没有加锁，虽然效率高，但是问题太多，严重不建议采用。

一个事务在执行中，读到另一个执行中事务的更新(或其他操作)但是未commit的数据，这种现象叫做脏读（dirty read）。

（3）读提交

先将全局隔离级别设置为读提交：

两个事务并发运行：

在客户端A的事务运行期间，客户端A对表进行的操作，客户端B是看不到的。一旦客户端A提交事务了，客户端B就能看到客户端A的操作了。这就是读提交。

客户端A提交事务后，客户端B能看到客户端A进行的操作了。但是，此时客户端B还在当前事务中，并未提交，那么就造成了，同一个事务内，同样的读取，在不同的时间段(依旧还在事务操作中！)，读取到了不同的内容，这种现象叫做不可重复读(non reapeatable read)！！

这个不可重复读是问题吗？举个例子：

公司决定发奖品，对于工资在[1000,2000)的员工送水杯，在[2000,3000)的员工送键盘，在[3000,4000)的员工送微波炉，在[4000,5000)的员工送电视，在[5000,6000)的员工送手机。

老板决定由小王来通过数据库来统计各个薪资级别的员工名单。

同时，有一个叫tom的员工来找老板加薪，老板同意了，决定把他的工资从2100涨到4200。老板让小李负责将tom的新薪资更新到数据库。

由于不可重复读的问题，导致了tom这个员工能够领取到两份奖品。

所以，不可重复读，一定是一个问题。为了解决这个问题，就有了可重复度隔离级别。

（4）可重复读

这是MySQL的默认隔离级别。

先将全局隔离级别设置为可重复读：

两个事务并发运行：

可以看到，在客户端B中，事务无论什么时候进行查找，看到的结果都是一致的，这叫做可重复读。

客户端A在对应事务中insert的数据，在客户端B的事 务周期中，也没有什么影响，也符合可重复的特点。但是，一般的数据库在可重复读情况的时候，无法屏蔽其它事务insert的数据(为什么？因为隔离性实现是对数据加锁完成的，而insert待插入的数据因为并不存在，那么一般加锁无法屏蔽这类问题)，会造成虽然大部分内容是可重复读的，但是insert的数据在可重复读 情况被读取出来，导致多次查找时，会多查找出来新的记录，就如同产生了幻觉。这种现象，叫做幻读 (phantom read)。很明显，MySQL在可重复读隔离级别的时候，是解决了幻读问题的。

（5）串行化

对所有操作全部加锁，进行串行化，不会有问题，但是只要串行化，效率很低，几乎完全不会被采用。

先将全局隔离级别设置为可重复读：

两个事务并发运行：

当客户端A与客户端B都以串行化的隔离级别去运行事务。事务中如果有查询时就会获取锁，去查询数据。如果有增删改时，就会进入等待队列，只有查询的事务都结束了，等待队列的事务才会执行。

（6）总结

• 隔离级别越严格，安全性越高，但数据库的并发性能也就越低，往往需要在两者之间找一个平衡点。
• 不可重复读的重点是修改和删除：同样的条件, 你读取过的数据,再次读取出来发现值不一样了，幻读的重点在于新增：同样的条件, 第一次和二次读出来的记录数不一样。
• MySQL默认的隔离级别是可重复读,一般情况下不要修改。
• 上面的例子可以看出，事务也有长短事务这样的概念。事务间互相影响，指的是事务在并发行执行的 时候，即都没有commit的时候，影响会比较大。

7、事务的一致性

• 事务执行的结果，必须使数据库从一个一致性状态，变到另一个一致性状态。当数据库只包含事务成功提交的结果时，数据库处于一致性状态。如果系统运行发生中断，某个事务尚未完成而被迫中断，而改未完成的事务对数据库所做的修改已被写入数据库，此时数据库就处于一种不正确（不一致）的状态。因此一致性是通过原子性来保证的。
• 其实一致性和用户的业务逻辑强相关，一般MySQL提供技术支持，但是一致性还是要用户业务逻辑做支撑，也就是，一致性，是由用户决定的。
• 而技术上，通过AID保证C。

总而言之，一致性并不是一个技术层面的概念，而是使用层面的概念。MySQL其实并没有为一致性做什么工作，而是通过实现原子性、隔离性、持久性来保证一致性。一致性是要由MySQL和用户共同维护的。

举个例子，甲要给乙转账100块钱，程序员丙来执行这个业务。转账成功了，那么甲的账户会少100块，乙的账户会多100块，如果失败，也不会影响甲乙的账户余额，使用MySQL的所有行为都是可预测的，这就是一致性。现在丙封装一个事务，该事务从甲的账户划掉100块钱，但是就是故意不给乙的账户加100块钱。这是MySQL的问题吗？并不是，这是使用者的问题。所以才说，一致性是要由MySQL和用户共同维护的。

8、深度理解隔离性

数据库的并发场景有三种：

• 读-读 ：不存在任何问题，也不需要并发控制。
• 读-写 ：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读。
• 写-写 ：有线程安全问题，可能会存在更新丢失问题。

一个数据库，大部分情况下是读写并发，所以下面主要讲解的就是读写并发。

（1）MVCC

多版本并发控制（ MVCC ）是一种用来解决 读写冲突 的无锁并发控制。

为事务分配单向增长的事务ID，为每个修改保存一个版本，版本与事务ID关联，读操作只读该事务开始前的数据库的快照。 所以MVCC 可以为数据库解决以下问题：

• 在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能。
• 同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题。

mysqld可能会面临处理多个事务的情况，事务有自己的生命周期，mysqld需要对多个事务进行管理。如何管理？先描述，再组织。所以，事务在我看来，一定在mysqld中有一套对应的结构体或类对象。

理解 MVCC 需要知道三个知识点：

• 3个记录隐藏列字段
• undo log
• Read View

了解三个隐藏列字段：

• DB_TRX_ID ：6 byte，最近修改( 修改/插入 )事务ID，记录创建这条记录/最后一次修改该记录的事务ID。
• DB_ROLL_PTR : 7 byte，回滚指针，指向这条记录的上一个版本（简单理解成，指向历史版本就行，这些数据一般在 undo log中）。
• DB_ROW_ID : 6 byte，隐含的自增ID（隐藏主键），如果数据表没有主键， InnoDB 会自动以DB_ROW_ID 产生一个聚簇索引。
• 补充：实际还有一个删除flag隐藏字段, 即记录被更新或删除并不代表真的删除，而是删除flag变了。

示例：

创建一张表。

create table if not exists student(
    name varchar(11) not null,
    age int not null
);

插入数据。

我们原本以为插入的数据就是这样的结构。

实际上是这样的结构。

关于undo log：

我们这里理解undo log，简单理解成，就是 MySQL 中的一段内存缓冲区，用来保存日志数据的就行。 

模拟MVCC：

现在有一个事务10，对student表中记录进行修改(update)：将name(张三)改成name(李四)。

①事务10,因为要修改，所以要先给该记录加行锁。

②修改前，现将改行记录拷贝到undo log中，所以，undo log中就有了一行副本数据。（原理就是写时拷贝） 

③现在修改原始记录中的name，改成 '李四'。并且修改原始记录的隐藏字段 DB_TRX_ID 为当前 事务10 的ID, 我们默认从 10 开始，之后递增。而原始记录的回滚指针 DB_ROLL_PTR 列，里面写入undo log中副本数据的地址，从而指向副本记录，既表示我的上一个版本就是它。

④事务10提交，释放锁。

现在又有一个事务11，对student表中记录进行修改(update)：将age(25)改成age(38)。

①事务11，因为也要修改，所以要先给该记录加行锁。

②修改前，现将改行记录拷贝到undo log中，所以，undo log中就又有了一行副本数据。此时，新的副本，我们采用头插方式，插入undo log。

③现在修改原始记录中的age，改成 38。并且修改原始记录的隐藏字段 DB_TRX_ID 为当前 事务11 的ID。而原始记录的回滚指针 DB_ROLL_PTR 列，里面写入undo log中副本数据的地址，从而指向副本记录，既表示我的上一个版本就是它。

④事务11提交，释放锁。

这样，我们就有了一个基于链表记录的历史版本链。所谓的回滚，无非就是用历史数据，覆盖当前数据。

上面的一个一个版本，我们可以称之为一个一个的快照。

一些思考：

上面是以更新（upadte）主讲的,如果是delete呢？一样的，别忘了，删数据不是清空，而是设置flag为删除即可。也可以形成版本。

如果是insert呢？因为insert是插入，也就是之前没有数据，那么insert也就没有历史版本。但是一般为了回滚操作，insert的数据也是要被放入undo log中，如果当前事务commit了，那么这个undo log 的历史insert记录就可以被清空了。

总结一下，也就是我们可以理解成，update和delete可以形成版本链，insert暂时不考虑。

当前读：读取最新的记录，就是当前读。增删改，都叫做当前读，select也有可能当前读，比如：select lock in share mode(共享锁), select for update。

快照读：读取历史版本(一般而言)，就叫做快照读。(这个我们后面重点讨论)

是什么决定了，select是当前读，还是快照读呢？是由隔离级别决定的，比如在RU级别下，select就是当前读。

根据我们之前的试验，不管在RC还是RR级别下，都是可以读写并发的。根据隔离性的不同，客户端A对数据做了修改客户端B可能都看不到。这就注定了客户端A和客户端B读到的绝对是不同的数据。为什么读写可以并发呢？因为写的是当前最新的数据，读的是历史版本。所以客户端A和客户端B不会出现访问同一个位置，就不需要加锁，也就能够并发进行读写操作。隔离性本质上是在数据层面的隔离，在做法上是在版本上做隔离。所以为什么我们在不同隔离级别下能看到的数据是不一样的，取决于不同的隔离级别让我们看到不同的版本。

所以，隔离性的本质实现是用MVCC多版本来实现的，事务回滚也是MVCC多版本控制来实现的。

那么多个事务在执行中，CURD操作是会交织在一起的。那么，为了保证事务的“有先有后”，是不是应该让不同的事务看到它该看到的内容，这就是所谓的隔离性与隔离级别要解决的问题。

那么，如何保证，不同的事务，看到不同的内容呢？也就是如何如何实现隔离级别？此时就需要认识视图了。

关于Read View：

Read View就是事务进行 快照读 操作的时候生产的 读视图 (Read View)，在该事务执行快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID(当每个事务开启时，都会被分配一个ID, 这个ID是递增的，所以最新的事务，ID值越大)。

Read View 在 MySQL 源码中,就是一个类，本质是用来进行可见性判断的。 即当我们某个事务执行快照读的时候，对该记录创建一个 Read View 读视图，把它比作条件，用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的 undo log 里面的某个历史版本的数据。

下面是Read View简化后的结构：

class ReadView {
		// 省略...
private:
		/** 高水位线，大于等于这个ID的事务均不可见*/
		trx_id_t m_low_limit_id//ReadView生成时刻系统尚未分配的下一个事务ID，也就是目前已出现过的事务ID的最大值+1
    
		/** 低水位线：小于这个ID的事务均可见 */
		trx_id_t m_up_limit_id;//记录m_ids列表中事务ID最小的ID
    
		/** 创建该 Read View 的事务的ID*/
		trx_id_t m_creator_trx_id;//ReadView一定与某个事务相关，这就是那个事务的ID
    
		/** 创建视图时的活跃事务id列表*/
		ids_t m_ids;//创建视图时尚未提交的事务们
    
		// 省略...
};

通过源码来验证一下上面这张图。

Read View是事务可见性的一个类，并不是事务一创建出来就有对应的Read View，而是当这个事务首次进行快照读的时候，才会形成Read View。

Read View实验：

假设当前有条记录：

事务操作：

• 事务4修改name(张三)变成name(李四)。
• 当事务2进行快照读时，就会为事务2形成一个Read View。

//事务2的Read View

m_ids;             //1,3，创建视图时活跃的事务ID
up_limit_id;       //1，m_ids中最小的事务ID
low_limit_id;      //5，ReadView生成时刻，系统尚未分配的下一个事务ID
creator_trx_id;    //2，创建该ReadView的事务的ID

此时的版本链是：

• 只有事务4修改过该行记录，并在事务2执行快照读前，就提交了事务。
• 事务2在快照读改行记录的时候，就会拿着改行记录的DB_TRX_ID去和up_limit_id、low_limit_id、m_ids进行比较，判断事务2能看到当前记录的版本。

//事务2的 Read View
m_ids; // 1,3
up_limit_id; // 1
low_limit_id; // 4 + 1 = 5，原因：ReadView生成时刻，系统尚未分配的下一个事务ID
creator_trx_id // 2

//事务4提交的记录对应的事务ID
DB_TRX_ID=4

//比较步骤
DB_TRX_ID（4）< up_limit_id（1） ? 不小于，下一步
DB_TRX_ID（4）>= low_limit_id(5) ? 不大于或等于，下一步
m_ids.contains(DB_TRX_ID) ? 不在m_ids中，说明，事务4不在当前的活跃事务中。

//结论
故，事务4的更改，应该看到。
所以事务2能读到的最新数据记录是事务4所提交的版本，而事务4提交的版本也是全局角度上最新的版本

（2）RR与RC的本质区别

将隔离级别设置为RR：

测试表：

--用之前用过的这张表
create table if not exists user (
     id int primary key,
     age int not null,
     name varchar(16) not null
);

--清空表里原来的数据，再插入一条数据
insert into user (id, age, name) values (1, 15,'黄蓉');

示例1：

示例2：

示例1与用示例2唯一区别，仅仅是示例1的客户端B事务在客户端A事务修改age前快照读过一次age数据。而示例2的客户端B事务在客户端A事务修改age前没有进行过快照读。

所以我们能够得出一个结论：Read View形成时机的不同，会影响事务的可见性。

RR 与 RC的本质区别：

• 正是Read View生成时机的不同，从而造成RC,RR级别下快照读的结果的不同。
• 在RR级别下的某个事务的对某条记录的第一次快照读会创建一个快照及Read View, 将当前系统活跃的其他事务记录起来
• 此后再调用快照读的时候，还是使用的是同一个Read View，所以只要当前事务在其他事务提交更新之前使用过快照读，那么之后的快照读使用的都是同一个Read View，所以对之后的修改不可见。
• 即RR级别下，快照读生成Read View时，Read View会记录此时所有其他活动事务的快照，这些事务的修改对于当前事务都是不可见的。而早于Read View创建的事务所做的修改均是可见。
• 而在RC级别下的，事务中，每次快照读都会新生成一个快照和Read View, 这就是我们在RC级别下的事务中可以看到别的事务提交的更新的原因。
• 总之在RC隔离级别下，是每个快照读都会生成并获取最新的Read View；而在RR隔离级别下，则是同一个事务中的第一个快照读才会创建Read View, 之后的快照读获取的都是同一个Read View。
• 正是RC每次快照读，都会形成Read View，所以，RC才会有不可重复读问题。

十二、视图

这里要讲的视图和上面刚刚学的视图没有任何关系。

视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。视图的数据变化会影响到基表，基表的数据变化也会影响到视图。

1、基本使用

创建视图：

create view 视图名 as select语句;

修改视图：

修改基表：

删除视图：

drop view 视图名;

2、视图的规则与限制

• 与表一样，必须唯一命名（不能出现同名视图或表名）。
• 创建视图数目无限制，但要考虑复杂查询创建为视图之后的性能影响。
• 视图不能添加索引，也不能有关联的触发器或者默认值。
• 视图可以提高安全性，必须具有足够的访问权限。
• 视图可以和表一起使用。

十三、用户管理

我们之前使用MySQL都是用root账户登录的，这样存在安全隐患。这时，就需要使用MySQL的用户管理。

1、用户

MySQL的信息也是在MySQL内特定的表中保存的。

其中用户信息就保存在user表中。

只看部分信息。

host：表示这个用户可以从哪个主机登陆，如果是localhost，表示只能从本机登陆，%表示能从任意主机登录。

user：用户名。

authentication_string：用户密码通过password函数加密后的。

所以，所谓的创建新用户，其实就是把新用户的用户名、允许从哪里登陆、密码等数据插入到user表中，这是最简单粗暴的做法，但是太麻烦了，一般不这么做。

创建用户：

create user '用户名'@'登陆主机/ip' identified by '密码';

创建完用户后刷新一下：

flush privileges;

用新用户来登录MySQL：

删除用户：

drop user '用户名'@'主机名';

修改用户密码：

自己修改自己的密码。

set password=password('新的密码');

root用户修改指定用户的密码。

set password for '用户名'@'主机名'=password('新的密码')

2、数据库的权限

MySQL数据库提供的权限列表：

创建一个新用户：

给用户授权：

刚刚创建的用户没有任何权限，需要给用户授权。

grant 权限列表 on 库.对象名 to '用户名'@'登陆位置' [identified by '密码']

• *.* : 代表本系统中的所有数据库的所有对象（表，视图，存储过程等）。
• 库.* : 表示某个数据库中的所有数据对象(表，视图，存储过程等)。
• identified by可选。 如果用户存在，赋予权限的同时修改密码,如果该用户不存在，就是创建用户。

注意：如果发现赋权限后，没有生效，执行如下指令。

flush privileges;

在root用户下创建一个数据库并创建一张表：

zhangsan看不到这个数据库，因为没给他授权。

给zhangsan授权：

all表示赋予该用户在该对象上的所有权限。

此时zhangsan就能看到root_db数据库了，还能使用里面的user表。

查看给zhangsan用户的权限：

show grants for 'zhangsan'@'%';

回收权限：

revoke 权限列表 on 库.对象名 from '用户名'@'登陆位置';

取消zhangsan对user表的插入权限：

取消zhangsan对user表的所有权限：

十四、C语言连接MySQL

创建一个用户专门用于C语言连接MySQL。

为该用户创建一个数据库，授权给该用户。

要使用C语言连接MySQL，需要使用MySQL官网提供的库。

其实当我们在最开始安装MySQL的时候，这些库就已经准备好了，所以不需要做额外的工作。

获取MySQL客户端的版本：

mysql_get_client_info();

#include <iostream>
#include <mysql/mysql.h>
int main()
{
    std::cout<<"MySQL version: "<<mysql_get_client_info()<<std::endl;
    return 0;
}

初始化MySQL与关闭MySQL链接：

MYSQL *mysql_init(MYSQL *mysql);
//要使用库，必须初始化，失败返回空指针

void mysql_close(MYSQL *sock);
//使用结束后一定要调用该接口来关闭MySQL链接

#include <iostream>
#include <mysql/mysql.h>
int main()
{
    MYSQL* my=mysql_init(nullptr);
    if(my==nullptr)
    {
        std::cerr<<"init MySQL error"<<std::endl;
        return 1;
    }
    std::cout<<"init MySQL success"<<std::endl;
    mysql_close(my);
    return 0;
}

链接MySQL：

//MySQL初始化完毕后，必须先链接数据库，再进行后续操作
MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, 
                          const char *user,
                          const char *passwd,
                          const char *db,
                          unsigned int port,
                          const char *unix_socket,
                          unsigned long client_flag); 
//如果连接成功，返回MYSQL*连接句柄。
//如果连接失败，返回NULL。对于成功的连接，返回值与第1个参数的值相同。

#include <iostream>
#include <mysql/mysql.h>
#include <string>

const std::string host="localhost";
const std::string user="connector";
const std::string passwd="123456";
const std::string db="conn";
const unsigned int port=3306;
int main()
{
    MYSQL* my=mysql_init(nullptr);
    if(my==nullptr)//初始化MySQL
    {
        std::cerr<<"init MySQL error"<<std::endl;
        return 1;
    }
    //链接MySQL
    if(mysql_real_connect(my,host.c_str(),user.c_str(),passwd.c_str(),db.c_str(),port,nullptr,0)==nullptr)
    {
        std::cerr<<"connect MySQL error"<<std::endl;
        return 2;
    }
    std::cout<<"connect MySQL success"<<std::endl;
    mysql_close(my);
    return 0;
}

用户connector在conn数据库下建立一张user表：

create table if not exists user(
    id bigint primary key auto_increment,
    name varchar(32) not null,
    age int not null,
    telphone varchar(32) unique
);

向MySQL下达指令：

int mysql_query(MYSQL *mysql, const char *query);
//成功返回0，失败返回非0.
//query为执行的SQL语句对应的字符长串

#include <iostream>
#include <mysql/mysql.h>
#include <string>

const std::string host="localhost";
const std::string user="connector";
const std::string passwd="123456";
const std::string db="conn";
const unsigned int port=3306;
int main()
{
    MYSQL* my=mysql_init(nullptr);
    if(my==nullptr)//初始化MySQL
    {
        std::cerr<<"init MySQL error"<<std::endl;
        return 1;
    }
    //链接MySQL
    if(mysql_real_connect(my,host.c_str(),user.c_str(),passwd.c_str(),db.c_str(),port,nullptr,0)==nullptr)
    {
        std::cerr<<"connect MySQL error"<<std::endl;
        return 2;
    }

    std::string sql;
    while(true)
    {
        std::cout<<"MySQL>>";
        //输入sql语句
        if(!std::getline(std::cin,sql)||sql=="quit")
        { 
            std::cout<<"bye bye"<<std::endl;
            break;
        }
        int n=mysql_query(my,sql.c_str());//向MySQL下达sql指令
        if(n==0)//成功
        {
            std::cout<<sql<<",success,n="<<n<<std::endl;
        }
        else//失败
        {
            std::cerr<<sql<<",failed,n="<<n<<std::endl;
        }
    }
    mysql_close(my);
    return 0;
}

第二次输入报错的原因是telphone有唯一性约束。

connector在MySQL中查看user表：

也可以这么用：

#include <iostream>
#include <mysql/mysql.h>
#include <string>

const std::string host = "localhost";
const std::string user = "connector";
const std::string passwd = "123456";
const std::string db = "conn";
const unsigned int port = 3306;
int main()
{
    MYSQL *my = mysql_init(nullptr);
    if (my == nullptr) // 初始化MySQL
    {
        std::cerr << "init MySQL error" << std::endl;
        return 1;
    }
    // 链接MySQL
    if (mysql_real_connect(my, host.c_str(), user.c_str(), passwd.c_str(), db.c_str(), port, nullptr, 0) == nullptr)
    {
        std::cerr << "connect MySQL error" << std::endl;
        return 2;
    }

    std::string sql="update user set name='alan' where id=3";
    int n = mysql_query(my, sql.c_str()); // 向MySQL下达sql指令
    if (n == 0) // 成功
    {
        std::cout << sql << ",success,n=" << n << std::endl;
    }
    else // 失败
    {
        std::cerr << sql << ",failed,n=" << n << std::endl;
    }
    mysql_close(my);
    return 0;
}

connector在MySQL中查看user表：

尝试插入中文数据：

查看时发现出现了乱码：

解决乱码问题：

//建立好链接之后，获取英文没有问题，如果获取中文是乱码：
//设置链接的默认字符集是utf8，原始默认是latin1
mysql_set_character_set(my, "utf8");

下达select指令：

代码虽然成功运行了，但是查询结果要如何得到呢？查询的结果其实已经在MYSQL结构体中了，需要用接口把结果提取出来。

提取查询结果：

MYSQL_RES *mysql_store_result(MYSQL *mysql);
//具有多个结果的MYSQL_RES结果集合。如果出现错误，返回NULL
  
//下面的接口基本就是读取 MYSQL_RES 中的数据
//该函数会返回MYSQL_RES 这样一个变量，该变量主要用于保存查询的结果。同时该函数malloc了一片内存空间来存储查询过来的数据
//所以我们一定要记得释放掉申请的空间

void mysql_free_result(MYSQL_RES *result);
//释放MYSQL_RES申请的空间

可以这样理解MYSQL_RES的结构：

获取行数：

my_ulonglong mysql_num_rows(MYSQL_RES *res);

获取列数：

my_ulonglong mysql_num_fields(MYSQL_RES *res);

获取列属性：

MYSQL_FIELD *mysql_fetch_fields(MYSQL_RES *res);

获取结果内容：

MYSQL_ROW mysql_fetch_row(MYSQL_RES *result);
//它会返回一个MYSQL_ROW变量，MYSQL_ROW其实就是char **.就当成一个二维数组来用吧。
//每一次调用该接口，指针就会自动向下移动。

最终代码：

#include <iostream>
#include <mysql/mysql.h>
#include <string>

const std::string host = "localhost";
const std::string user = "connector";
const std::string passwd = "123456";
const std::string db = "conn";
const unsigned int port = 3306;
int main()
{
    MYSQL *my = mysql_init(nullptr);
    if (my == nullptr) // 初始化MySQL
    {
        std::cerr << "init MySQL error" << std::endl;
        return 1;
    }
    // 链接MySQL
    if (mysql_real_connect(my, host.c_str(), user.c_str(), passwd.c_str(), db.c_str(), port, nullptr, 0) == nullptr)
    {
        std::cerr << "connect MySQL error" << std::endl;
        return 2;
    }

    mysql_set_character_set(my, "utf8");//设置字符集

    std::string sql="select * from user";
    int n = mysql_query(my, sql.c_str()); // 向MySQL下达sql指令
    if (n == 0) // 成功
    {
        std::cout << sql << ",success,n=" << n << std::endl;
    }
    else // 失败
    {
        std::cerr << sql << ",failed,n=" << n << std::endl;
        return 3;
    }

    MYSQL_RES* res=mysql_store_result(my);//提取查询结果
    int rows=mysql_num_rows(res);//获取行数
    int fields=mysql_num_fields(res);//获取列数
    std::cout<<"行="<<rows<<"，列="<<fields<<std::endl;
    //获取列属性
    MYSQL_FIELD* field=mysql_fetch_field(res);
    for(int i=0;i<fields;++i)
    {
        std::cout<<field[i].name<<"\t";   
    }
    std::cout<<std::endl;
    //获取表内容
    for(int i=0;i<rows;++i)
    {
        MYSQL_ROW row=mysql_fetch_row(res);
        for(int j=0;j<fields;++j)
        {
            std::cout<<row[j]<<"\t";
        }
        std::cout<<std::endl;
    }
    mysql_free_result(res);
    mysql_close(my);
    return 0;
}

完结。。。。。。