MySQL

MySQL(学习笔记,不定时更新)

MySQL基础

原文地址：https://blog.csdn.net/weixin_45851945/article/details/114287877
在原文的基础上做了一些补充以及遇到的一些问题的解决办法，有错误的地方一定一定指出来

SQL简述

1.SQL的概述

Structure Query Language(结构化查询语言)简称SQL，它被美国国家标准局(ANSI)确定为关系型数据库语言的美国标准，后被国际化标准组织(ISO)采纳为关系数据库语言的国际标准。数据库管理系统可以通过SQL管理数据库；定义和操作数据，维护数据的完整性和安全性。

2.MYSQL优点

• 简单易学，具有很强的操作性
• 绝大多数重要的数据库管理系统均支持SQL
• 高度非过程化；用SQL操作数据库时大部分的工作由DBMS自动完成

3.SQL的分类

• DDL(Data Definition Language) 数据定义语言，用来操作数据库、表、列等； 常用语句：CREATE、 ALTER、DROP
• DML(Data Manipulation Language) 数据操作语言，用来操作数据库中表里的数据；常用语句：INSERT、 UPDATE、 DELETE
• DCL(Data Control Language) 数据控制语言，用来操作访问权限和安全级别； 常用语句：GRANT、DENY
• DQL(Data Query Language) 数据查询语言，用来查询数据 常用语句：SELECT

数据库的三大范式

• 第一范式(1NF)是指数据库表的每一列都是不可分割的基本数据线；也就是说：每列的值具有原子性，不可再分割。
• 第二范式(2NF)是在第一范式(1NF)的基础上建立起来得，满足第二范式(2NF)必须先满足第一范式(1NF)。如果表是单主键，那么主键以外的列必须完全依赖于主键；如果表是复合主键，那么主键以外的列必须完全依赖于主键，不能仅依赖主键的一部分。
• 第三范式(3NF)是在第二范式的基础上建立起来的，即满足第三范式必须要先满足第二范式。第三范式(3NF)要求：表中的非主键列必须和主键直接相关而不能间接相关；也就是说：非主键列之间不能相关依赖。

数据库的数据类型

使用MySQL数据库存储数据时，不同的数据类型决定了 MySQL存储数据方式的不同。为此，MySQL数据库提供了多种数据类型，其中包括整数类型、浮点数类型、定点 数类型、日期和时间类型、字符串类型、二进制…等等数据类型。

1.整数类型

根据数值取值范围的不同MySQL 中的整数类型可分为5种，分别是TINYINT、SMALUNT、MEDIUMINT、INT和 BIGINT。下图列举了 MySQL不同整数类型所对应的字节大小和取值范围而最常用的为INT类型的，

数据类型	字节数	无符号数的取值范围	有符号数的取值范围
TINYINT	1	0~255	-128~127
SMALLINT	2	0~65535	-32768~32768
MEDIUMINT	3	0~16777215	-8388608~8388608
INT	4	0~4294967295	-2147483648~ 2147483648
BIGINT	8	0~18446744073709551615	-9223372036854775808~9223372036854775808

2.浮点数类型和定点数类型

在MySQL数据库中使用浮点数和定点数来存储小数。浮点数的类型有两种：单精度浮点数类型（FLOAT)和双精度浮点数类型（DOUBLE)。而定点数类型只有一种即DECIMAL类型。下图列举了 MySQL中浮点数和定点数类型所对应的字节大小及其取值范围：

数据类型	字节数	有符号的取值范围	无符号的取值范围
FLOAT	4	-3.402823466E+38~-1.175494351E-38	0和1.175494351E-38~3.402823466E+38
DOUBLE	8	-1.7976931348623157E+308~2.2250738585072014E-308	0和2.2250738585072014E-308~1.7976931348623157E+308
DECIMAL（M,D）	M+2	-1.7976931348623157E+308~2.2250738585072014E-308	0和2.2250738585072014E-308~1.7976931348623157E+308

从上图中可以看出：DECIMAL类型的取值范围与DOUBLE类型相同。但是，请注意：DECIMAL类型的有效取值范围是由M和D决定的。其中，M表示的是数据的长 度，D表示的是小数点后的长度。比如，将数据类型为DECIMAL(6,2)的数据6.5243 插人数据库后显示的结果为6.52

3.字符串类型

在MySQL中常用CHAR 和 VARCHAR 表示字符串。两者不同的是：VARCHAR存储可变长度的字符串。
当数据为CHAR(M)类型时，不管插入值的长度是实际是多少它所占用的存储空间都是M个字节；而VARCHAR(M)所对应的数据所占 用的字节数为实际长度加1

插入值	CHAR(3)	存储需求	VARCHAR(3)	存储需求
‘’	‘’	3个字节	‘’	1个字节
‘a’	‘a’	3个字节	‘a’	2个字节
‘ab’	‘ab’	3个字节	‘ab’	3个字节
‘abc’	‘ab’	3个字节	‘abc’	4个字节
‘abcd’	‘ab’	3个字节	‘abc’	4字节

文本类型用于表示大文本数据，例如，文章内容、评论、详情等，它的类型分为如下4种：

数据类型	储存范围
TINYTEXT	0~255字节
TEXT	0~65535字节
MEDIUMTEXT	0~16777215字节
LONGTEXT	0~4294967295字节

4.日期与时间类型

MySQL提供的表示日期和时间的数据类型分别是 ：YEAR、DATE、TIME、DATETIME 和 TIMESTAMP。下图列举了日期和时间数据类型所对应的字节数、取值范围、日期格式以及零值：

数据类型	字节数	取值范围	日期格式	零值
YEAR	1	1901~2155	YYYY	0000
DATE	4	1000-01-01~9999-12-31	YYYY-MM-DD	0000-00-00
TIME	3	-838：59：59~ 838：59：59	HH:MM:SS	00:00:00
DATETIME	8	1000-01-01 00:00:00~9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	0000-00-00 00:00:00
TIMESTAMP	4	1970-01-01 00:00:01~2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	0000-00-00 00:00:00

4.1YEAR类型

YEAR类型用于表示年份，在MySQL中，可以使用以下三种格式指定YEAR类型 的值。
1、使用4位字符串或数字表示，范围为’1901’—'2155’或1901—2155。例如，输人 ‘2019’或2019插人到数据库中的值均为2019。
2、使用两位字符串表示，范围为’00’—‘99’。其中，‘00’—'69’范围的值会被转换为 2000—2069范围的YEAR值，‘70’—'99’范围的值会被转换为1970—1999范围的YEAR 值。例如，输人’19’插人到数据库中的值为2019。
3、使用两位数字表示，范围为1—99。其中，1—69范围的值会被转换为2001— 2069范围的YEAR值，70—99范围的值会被转换为1970—1999范围的YEAR值。例 如，输人19插入到数据库中的值为2019。
请注意：当使用YEAR类型时，一定要区分’0’和0。因为字符串格式的’0’表示的YEAR值是2000而数字格式的0表示的YEAR值是0000。

4.2TIME类型

TIME类型用于表示时间值，它的显示形式一般为HH:MM:SS，其中，HH表示小时， MM表示分,SS表示秒。在MySQL中，可以使用以下3种格式指定TIME类型的值。
1、以’D HH:MM:SS’字符串格式表示。其中，D表示日可取0—34之间的值, 插人数据时，小时的值等于(DX24+HH)。例如，输入’2 11:30:50’插人数据库中的日期为59:30:50。
2、以’HHMMSS’字符串格式或者HHMMSS数字格式表示。 例如，输人’115454’或115454,插入数据库中的日期为11:54:54
3、使用CURRENT_TIME或NOW()输人当前系统时间。

4.3DATETIME类型

DATETIME类型用于表示日期和时间，它的显示形式为’YYYY-MM-DD HH: MM:SS’，其中，YYYY表示年，MM表示月，DD表示日，HH表示小时，MM表示分，SS 表示秒。在MySQL中，可以使用以下4种格式指定DATETIME类型的值。
以’YYYY-MM-DD HH:MM:SS’或者’YYYYMMDDHHMMSS’字符串格式表示的日期和时间，取值范围为’1000-01-01 00:00:00’—‘9999-12-3 23:59:59’。例如，输人’2019-01-22 09:01:23’或 ‘20190122_0_90123’插人数据库中的 DATETIME 值都为 2019-01-22 09:01:23。
1、以’YY-MM-DD HH:MM:SS’或者’YYMMDDHHMMSS’字符串格式表示的日期和时间，其中YY表示年，取值范围为’00’—‘99’。与DATE类型中的YY相同，‘00’— '69’范围的值会被转换为2000—2069范围的值，‘70’—'99’范围的值会被转换为1970—1999范围的值。
2、以YYYYMMDDHHMMSS或者YYMMDDHHMMSS数字格式表示的日期 和时间。例如，插入20190122090123或者190122090123,插人数据库中的DATETIME值都 为 2019-01-22 09:01:23。
3、使用NOW来输人当前系统的日期和时间。

4.4TIMESTAMP类型

TIMESTAMP类型用于表示日期和时间，它的显示形式与DATETIME相同但取值范围比DATETIME小。在此，介绍几种TIMESTAMP类型与DATATIME类型不同的形式：
1、使用CURRENT_TIMESTAMP输人系统当前日期和时间。
2、输人NULL时系统会输人系统当前日期和时间。
3、无任何输人时系统会输入系统当前日期和时间。

二进制类型

在MySQL中常用BLOB存储二进制类型的数据，例如：图片、PDF文档等。BLOB类型分为如下四种：

数据类型	储存范围
TINYBLOB	0~255字节
BLOB	0~65535字节
MEDIUMBLOB	0~16777215字节
LONGBLOB	0~4294967295字节

数据库、数据表的基本操作

1.数据库的基本操作

MySQL安装完成后，要想将数据存储到数据库的表中，首先要创建一个数据库。创 建数据库就是在数据库系统中划分一块空间存储数据，语法如下

create database 数据库名称;

创建数据库命令：

-- 创建一个叫db1的数据库
create database db1;

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-K2Nth5Yw-1621128108297)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210511143242211.png)]

查看数据库基本信息命令：

show create database db1;

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TeHesYwV-1621128108300)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210511143259802.png)]

删除数据库命令：

drop database db1;

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lDNofjF1-1621128108301)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210511143354154.png)]

查询出所有的数据库命令：

show databases;

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-n7TyEtbd-1621128108303)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210511144224814.png)]

将数据库的字符集修改为gbk命令：

alter database db1 character set gbk;

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VQCwj6xd-1621128108304)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210511144500932.png)]

切换数据库 命令：

use db1;

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mGzYcrad-1621128108305)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210511145023716.png)]

查看当前使用的数据库命令：

select database();

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6O9ZXZUT-1621128108305)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210511145053690.png)]

2.数据表的基本操作

数据库创建成功后可在该数据库中创建数据表(简称为表)存储数据。请注意：在操作数据表之前应使用“USE 数据库名;”指定操作是在哪个数据库中进行先关操作，否则会抛出“No database selected”错误。
语法如下：

create table 表名(
         字段1 字段类型,
         字段2 字段类型,
         …
         字段n 字段类型
);

创建数据表

示例：创建学生表 MySQL命令：

create table student(
 id int,
 name varchar(20),
 gender varchar(10),
 birthday date
 );

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ds3ft1d8-1621128108306)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210511150951262.png)]

查看数据表

示例：查看当前数据库中所有表 MySQL命令

show tables;

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9GzlXDkl-1621128108306)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210511151123562.png)]

示例：查表的基本信息 MySQL命令：

show create table student;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2hS3ZJTJ-1621128108307)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210511151241241.png)]

示例：查看表的字段信息 MySQL命令

desc student;

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WlGvSSJv-1621128108307)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210511151512479.png)]

修改数据表

有时，希望对表中的某些信息进行修改，例如：修改表名、修改字段名、修改字段 数据类型…等等。在MySQL中使用alter table修改数据表.
示例：修改表名 MySQL命令：

alter table student rename to stu;

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YaPSvf2C-1621128108308)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210511152152488.png)]

示例：修改字段名 MySQL命令：

alter table stu change name sname varchar(10);

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qczBVhh2-1621128108309)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210511152624375.png)]

示例：修改字段数据类型 MySQL命令：

alter table stu modify sname int;

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-w5xJYmYW-1621128108309)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210511152739781.png)]

示例：增加字段 MySQL命令：

alter table stu add address varchar(50);

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-t2JJ3iQs-1621128108310)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210511153505516.png)]

示例：删除字段 MySQL命令：

alter table stu drop address;

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kANGsJMN-1621128108310)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210511153648813.png)]

删除数据表

语法：

drop table 表名;

示例：删除数据表 MySQL命令：

drop table stu;

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XcBCSzbW-1621128108311)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210511153828626.png)]

数据表的约束

为防止错误的数据被插入到数据表，MySQL中定义了一些维护数据库完整性的规则；这些规则常称为表的约束。常见约束如下：

约束条件	说明
PRIMARY KEY	主键约束用于唯一标识对应的记录
FOREIGN KEY	外键约束
NOT NULL	非空约束
UNIQUE	唯一性约束
DEFAULT	默认值约束，用于设置字段的默认值

以上五种约束条件针对表中字段进行限制从而保证数据表中数据的正确性和唯一性。换句话说，表的约束实际上就是表中数据的限制条件。

1.主键约束

主键约束即primary key用于唯一的标识表中的每一行。被标识为主键的数据在表中是唯一的且其值不能为空。这点类似于我们每个人都有一个身份证号，并且这个身份证号是唯一的。
主键约束基本语法：

字段名 数据类型 primary key;

设置主键约束(primary key)的第一种方式
示例：MySQL命令：

create table student(
id int primary key not null auto_increment,
name varchar(20)
);

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SagXcgnW-1621128108311)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210511165458640.png)]

设置主键约束(primary key)的第二·种方式
示例：MySQL命令：

create table student01(
id int,
name varchar(20),
primary key(id)
);

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HECMxMVY-1621128108312)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210511165901236.png)]

2.非空约束

非空约束即 NOT NULL指的是字段的值不能为空，基本的语法格式如下所示：

字段名 数据类型 NOT NULL;

示例：MySQL命令：

create table student02(
id int
name varchar(20) not null
);

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DD2cOml6-1621128108312)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210511170427037.png)]

3.默认值约束

默认值约束即DEFAULT用于给数据表中的字段指定默认值，即当在表中插入一条新记录时若未给该字段赋值，那么，数据库系统会自动为这个字段插人默认值；其基本的语法格式如下所示：

字段名 数据类型 DEFAULT 默认值；

示例：MySQL命令：

create table student03(
id int,
name varchar(20),
gender varchar(10) default 'male'
);

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EclLHK9V-1621128108313)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210511170812318.png)]

4.唯一性约束

唯一性约束即UNIQUE用于保证数据表中字段的唯一性，即表中字段的值不能重复出现，其基本的语法格式如下所示：

字段名 数据类型 UNIQUE;

示例：MySQL命令：

create table student04(
id int,
name varchar(20) unique
);

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SyYsjtzz-1621128108313)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210512104518179.png)]

5.外键约束

外键约束即FOREIGN KEY常用于多张表之间的约束。基本语法如下：

-- 在创建数据表时语法如下：
CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段)
-- 将创建数据表创号后语法如下：
ALTER TABLE 从表名 ADD CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段);

示例：创建一个学生表 MySQL命令：

create table student05(
id int primary key,
name varchar(20)
);

示例：创建一个班级表 MySQL命令：

create table class(
classid int primary key,
studentid int
);

示例：学生表作为主表，班级表作为副表设置外键， MySQL命令：

alter table class add constraint fk_class_studentid foreign key(studentid) references student05(id);

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JinqJ8kJ-1621128108314)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210512105017972.png)]

5.1数据一致性概念

大家知道：建立外键是为了保证数据的完整和统一性。但是，如果主表中的数据被删除或修改从表中对应的数据该怎么办呢？很明显，从表中对应的数据也应该被删除，否则数据库中会存在很多无意义的垃圾数据。

5.2 删除外键

语法如下：

alter table 从表名 drop foreign key 外键名；

示例：删除外键 MySQL命令：

alter table class drop foreign key fk_class_studentid;

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Q0CIMLof-1621128108315)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210512105546737.png)]

外键的那个字段不在了证明删除成功了

5.3 关于外键约束需要注意的细节

1、从表里的外键通常为主表的主键
2、从表里外键的数据类型必须与主表中主键的数据类型一致
3、主表发生变化时应注意主表与从表的数据一致性问题

6、数据表插入数据

在MySQL通过INSERT语句向数据表中插入数据。在此，我们先准备一张学生表，代码如下：

create table student(
 id int,
 name varchar(30),
 age int,
 gender varchar(30)
 );

1. 为表中所有字段插入数据

每个字段与其值是严格一一对应的。也就是说：每个值、值的顺序、值的类型必须与对应的字段相匹配。但是，各字段也无须与其在表中定义的顺序一致，它们只要与 VALUES中值的顺序一致即可。
语法如下：

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

示例：向学生表中插入一条学生信息 MySQL命令：

insert into student (id,name,age,gender) values (1,'bob',16,'male');

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ny2xJgc1-1621128108315)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210512163850006.png)]

2.为表中指定字段插入数据

语法如下：

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

插入数据的方法基本和为表中所有字段插入数据，一样，只是需要插入的字段由你自己指定

3. 同时插入多条记录

语法如下：

INSERT INTO 表名 [(字段名1,字段名2,...)]VALUES (值 1,值 2,…),(值 1,值 2,…),...;

在该方式中：(字段名1,字段名2,…)是可选的，它用于指定插人的字段名；(值 1,值 2,…),(值 1,值 2,…)表示要插人的记录，该记录可有多条并且每条记录之间用逗号隔开。
示例：向学生表中插入多条学生信息 MySQL命令：

insert into student (id,name,age,gender) values (2,'lucy',17,'female'),(3,'jack',19,'male'),(4,'tom',18,'male');

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-L0Jd0h4n-1621128108316)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210512164951882.png)]

7、更新数据

在MySQL通过UPDATE语句更新数据表中的数据。在此，我们将就用六中的student学生表

1. UPDATE基本语法

UPDATE 表名 SET 字段名1=值1[,字段名2 =值2,…] [WHERE 条件表达式];

在该语法中：字段名1、字段名2…用于指定要更新的字段名称；值1、值 2…用于表示字段的新数据；WHERE 条件表达式 是可选的，它用于指定更新数据需要满足的条件

2. UPDATE更新部分数据

示例：将name为tom的记录的age设置为20并将其gender设置为female MySQL命令：

update student set age=20,gender='female' where name='tom';

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tZsA6Mlp-1621128108316)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210512165436866.png)]

3. UPDATE更新全部数据

示例：将所有记录的age设置为18 MySQL命令：

update student set age=18;

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-x4CUzp4y-1621128108317)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210512165549298.png)]

8、删除数据

在MySQL通过DELETE语句删除数据表中的数据。在此，我们先准备一张数据表，代码如下：

-- 创建学生表
 create table student(
 id int,
 name varchar(30),
 age int,
 gender varchar(30)
 );
 -- 插入数据
 insert into student (id,name,age,gender) values (2,'lucy',17,'female'),(3,'jack',19,'male'),(4,'tom',18,'male'),(5,'sal',19,'female'),(6,'sun',20,'male')
,(7,'sad',13,'female'),(8,'sam',14,'male');

1. DELETE基本语法

在该语法中：表名用于指定要执行删除操作的表；[WHERE 条件表达式]为可选参数用于指定删除的条件。

DELETE FROM 表名 [WHERE 条件表达式];

2. DELETE删除部分数据

示例：删除age等于14的所有记录 MySQL命令：

delete from student where age=14;

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XIaNpypf-1621128108317)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210512165929823.png)]

3. DELETE删除全部数据

示例：删除student表中的所有记录 MySQL命令：

delete from student;

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sBewWSs4-1621128108318)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210512170038660.png)]

4.TRUNCATE和DETELE的区别

TRUNCATE和DETELE都能实现删除表中的所有数据的功能，但两者也是有区别的：
1、DELETE语句后可跟WHERE子句，可通过指定WHERE子句中的条件表达式只删除满足条件的部分记录；但是，TRUNCATE语句只能用于删除表中的所有记录。
2、使用TRUNCATE语句删除表中的数据后，再次向表中添加记录时自动增加字段的默认初始值重新由1开始；使用DELETE语句删除表中所有记录后，再次向表中添加记录时自动增加字段的值为删除时该字段的最大值加1
3、DELETE语句是DML语句，TRUNCATE语句通常被认为是DDL语句

9、MySQL数据表简单查询

1.简单查询概述

简单查询即不含where的select语句。在此，我们讲解简单查询中最常用的两种查询：查询所有字段和查询指定字段。
在此，先准备测试数据，代码如下：

-- 创建数据库
DROP DATABASE IF EXISTS mydb;
CREATE DATABASE mydb;
USE mydb;

-- 创建student表
CREATE TABLE student (
    sid CHAR(6),
    sname VARCHAR(50),
    age INT,
    gender VARCHAR(50) DEFAULT 'male'
);

-- 向student表插入数据
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1001', 'lili', 14, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1002', 'wang', 15, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1003', 'tywd', 16, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1004', 'hfgs', 17, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1005', 'qwer', 18, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1006', 'zxsd', 19, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1007', 'hjop', 16, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1008', 'tyop', 15, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1009', 'nhmk', 13, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1010', 'xdfv', 17, 'female');

2.查询所有字段（方法不唯一只是举例）

查询所有字段 MySQL命令：

select * from student;

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QflYLOd9-1621128108318)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513095715363.png)]

3.查询指定字段（sid、sname）

查询指定字段（sid、sname） MySQL命令：

select sid,sname from student;

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DkqYK8x5-1621128108319)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513095856584.png)]

4.常数的查询

在SELECT中除了书写列名，还可以书写常数。可以用于标记
常数的查询日期标记 MySQL命令：

select sid,sname,'2021-03-02' from student;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Y2dGmOgI-1621128108319)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513100024536.png)]

5.从查询结果中过滤重复数据

在使用DISTINCT 时需要注意：
在SELECT查询语句中DISTINCT关键字只能用在第一个所查列名之前。
MySQL命令：

select distinct gender from student;

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TtLgGnrb-1621128108320)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513100210744.png)]

6.算术运算符（举例加运算符）

在SELECT查询语句中还可以使用加减乘除运算符。
查询学生10年后的年龄 MySQL命令：

 select sname,age+10 from student;

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UnzIMPYs-1621128108321)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513100639026.png)]

7、Limit分页

limit关键字的用法：

LIMIT [offset,] rows

offset指定要返回的第一行的偏移量，rows第二个指定返回行的最大数目。初始行的偏移量是0(不是1)。

取前5条数据

select * from table_name limit 0,5

或

select * from table_name limit 5

取第11条到第15条数据，共5条

select * from table_name limit 10,5

十、函数

1.聚合函数

在开发中，我们常常有类似的需求：统计某个字段的最大值、最小值、 平均值等等。为此，MySQL中提供了聚合函数来实现这些功能。所谓聚合，就是将多行汇总成一行；其实，所有的聚合函数均如此——输入多行，输出一行。聚合函数具有自动滤空的功能，若某一个值为NULL，那么会自动将其过滤使其不参与运算。

聚合函数使用规则：

只有SELECT子句和HAVING子句、ORDER BY子句中能够使用聚合函数。例如，在WHERE子句中使用聚合函数是错误的。
接下来，我们学习常用聚合函数。

1、where 后不能跟聚合函数，因为where执行顺序大于聚合函数。

2、where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件 中不能包含聚组函数，使用where条件显示特定的行。

3、having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件显示特定的组，也可以使用多个分组标准进行分组。

where是在判断数据从磁盘读入内存的时候，而having是判断分组统计之前的所有条件，所以having是在对select查询的字段中进行的操作,where后面不能写别名，因为还没有进入内存

1.1、count（）

统计表中数据的行数或者统计指定列其值不为NULL的数据个数
查询有多少该表中有多少人
MySQL命令：

select count(*) from student;

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AFt5JUyA-1621128108321)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513102022959.png)]

1.2、max（）

计算指定列的最大值，如果指定列是字符串类型则使用字符串排序运算

查询该学生表中年纪最大的学生
MySQL命令：

select max(age) from student;

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-k5gVNqBI-1621128108322)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513102134964.png)]

1.3、min（）

计算指定列的最小值，如果指定列是字符串类型则使用字符串排序运算

查询该学生表中年纪最小的学生 MySQL命令：

select sname,min(age) from student;

运行效果展示：

当mysql的sql_mode是only_full_group_by的时候，在不使用group by 并且select后面出现聚集函数的话，那么所有被select的都应该是聚集函数，否则就会报错！

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nMWHeOld-1621128108322)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513102758021.png)]

解决方案，两种：

方案一：修改SQL，因为出现这个问题，基本都是因为这个问题造成的，不确定返回字段可以使用

select sname,min(age) from student group by sname;

方案二：关闭ONLY_FULL_GROUP_BY，我的是Linux环境，我就说一下Linux的解决步骤：

①、登录进入MySQL，linux登录的：mysql -u username -p ，然后输入密码，输入SQL：show variables like ‘%sql_mode’;

②、编辑my.cnf文件，文件地址一般在：/etc/my.cnf，/etc/mysql/my.cnf，找到sql-mode的位置，去掉ONLY_FULL_GROUP_BY，然后重启MySQL；有的my.cnf中没有sql-mode，需要加入，注意要加入到[mysqld]下面。

[mysqld]
sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

③、修改成功后重启MySQL服务，service mysql restart，重启好后，再登录mysql，输入SQL：show variables like ‘%sql_mode’; 如果没有ONLY_FULL_GROUP_BY，就说明已经成功了。

1.4、sum（）

计算指定列的数值和，如果指定列类型不是数值类型则计算结果为0
查询该学生表中年纪的总和 MySQL命令：

select sum(age) from student;

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gOf3GQVA-1621128108323)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513104006041.png)]

1.5、avg（）

计算指定列的平均值，如果指定列类型不是数值类型则计算结果为

查询该学生表中年纪的平均数 MySQL命令

select avg(age) from student;

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vBjuIpsF-1621128108324)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513104334367.png)]

2.其他常用函数

这里我就不一一举例了，基本混个眼熟，以后用到再细说

2.1、时间函数

SELECT NOW();
SELECT DAY (NOW());
SELECT DATE (NOW());
SELECT TIME (NOW());
SELECT YEAR (NOW());
SELECT MONTH (NOW());
SELECT CURRENT_DATE();
SELECT CURRENT_TIME();
SELECT CURRENT_TIMESTAMP();
SELECT ADDTIME('14:23:12','01:02:01');
SELECT DATE_ADD(NOW(),INTERVAL 1 DAY);
SELECT DATE_ADD(NOW(),INTERVAL 1 MONTH);
SELECT DATE_SUB(NOW(),INTERVAL 1 DAY);
SELECT DATE_SUB(NOW(),INTERVAL 1 MONTH);
SELECT DATEDIFF('2019-07-22','2019-05-05');

2.2、字符串函数

--连接函数
SELECT CONCAT ()
--
SELECT INSTR ();
--统计长度
SELECT LENGTH();

2.3、数学函数

-- 绝对值
SELECT ABS(-136);
-- 向下取整
SELECT FLOOR(3.14);
-- 向上取整
SELECT CEILING(3.14);

十一、条件查询

数据库中存有大量数据，我们可根据需求获取指定的数据。此时，我们可在查询语句中通过WHERE子句指定查询条件对查询结果进行过滤。
在开始学习条件查询之前，我们先准备测试数据，代码如下：

1.使用关系运算符查询

在WHERE中可使用关系运算符进行条件查询，常用的关系运算符如下所示：

关系运算符	说明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于

查询年龄等于或大于17的学生的信息 MySQL命令：

select * from student where age>=17;

运行效果：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-l9WLnroe-1621128108325)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513105413965.png)]

2.使用IN关键字查询

IN关键字用于判断某个字段的值是否在指定集合中。如果字段的值恰好在指定的集合中，则将字段所在的记录将査询出来。

查询sid为S_1002和S_1003的学生信息 MySQL命令：

select * from student where sid in ('S_1002','S_1003');

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BZcVyiQX-1621128108325)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513105626414.png)]

查询sid为S_1001以外的学生的信息 MySQL命令：

select * from student where sid not in ('S_1001');

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xGwPx75j-1621128108326)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513105736362.png)]

3.使用BETWEEN AND关键字查询

BETWEEN AND用于判断某个字段的值是否在指定的范围之内。如果字段的值在指定范围内，则将所在的记录将查询出来
查询15到18岁的学生信息 MySQL命令：

select * from student where age between 15 and 18;

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rOY9IAnm-1621128108326)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513110020976.png)]

查询不是15到18岁的学生信息 MySQL命令：

select * from student where age not between 15 and 18;

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LbRGnZqh-1621128108327)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513110121382.png)]

4.使用空值查询

在MySQL中，使用 IS NULL关键字判断字段的值是否为空值。请注意：空值NULL不同于0，也不同于空字符串
由于student表没有空值就不演示查询空值的了
查询sname不为空值的学生信息 MySQL命令：

select * from student where sname is not null;

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-l6T7y2eD-1621128108328)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513110254307.png)]

5.使用AND关键字查询

在MySQL中可使用AND关键字可以连接两个或者多个查询条件。
查询年纪大于15且性别为male的学生信息 MySQL命令：

select * from student where age>15 and gender='male';

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zaPcANfA-1621128108328)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513110450463.png)]

6.使用OR关键字查询

在使用SELECT语句查询数据时可使用OR关键字连接多个査询条件。在使用OR关键字时，只要记录满足其中任意一个条件就会被查询出来
查询年纪大于15或者性别为male的学生信息 MySQL命令：

select * from student where age>15 or gender='male';

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MhLq1BTj-1621128108329)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513110642522.png)]

7.使用LIKE关键字查询(模糊查询)

MySQL中可使用LIKE关键字可以判断两个字符串是否相匹配

7.1 普通字符串

查询sname中与wang匹配的学生信息 MySQL命令：

select * from student where sname like 'wang';

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kQ0dbaf5-1621128108329)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513110813824.png)]

7.2 含有%通配的字符串

%用于匹配任意长度的字符串。例如，字符串“a%”匹配以字符a开始任意长度的字符串
查询学生姓名以li开始的记录 MySQL命令：

select * from student where sname like 'li%';

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oQyiBzkM-1621128108330)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513110941965.png)]

查询学生姓名以g结尾的记录 MySQL命令：

select * from student where sname like '%g';

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4VvGTsKH-1621128108330)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513111558042.png)]

查询学生姓名包含s的记录 MySQL命令：

select * from student where sname like '%s%';

运行效果展示

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wlWp1TnF-1621128108331)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513111705510.png)]

7.3 含有_通配的字符串

下划线通配符只匹配单个字符，如果要匹配多个字符，需要连续使用多个下划线通配符。例如，字符串“ab_”匹配以字符串“ab”开始长度为3的字符串，如abc、abp等等；字符串“a__d”匹配在字符“a”和“d”之间包含两个字符的字符串，如"abcd"、"atud"等等。
查询学生姓名以zx开头且长度为4的记录 MySQL命令：

select * from student where sname like 'zx__';

运行效果展示:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FPf77xQ4-1621128108331)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513111933161.png)]

查询学生姓名以g结尾且长度为4的记录 MySQL命令

select * from student where sname like '___g';

运行效果展示:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qpo2wf6C-1621128108332)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513112047320.png)]

8.使用LIMIT限制查询结果的数量

当执行查询数据时可能会返回很多条记录，而用户需要的数据可能只是其中的一条或者几条
查询学生表中年纪最小的3位同学 MySQL命令：

select * from student order by age asc limit 3;

运行效果展示

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yenOQHMO-1621128108332)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513112333480.png)]

9.使用GROUP BY进行分组查询

GROUP BY 子句可像切蛋糕一样将表中的数据进行分组，再进行查询等操作。换言之，可通俗地理解为：通过GROUP BY将原来的表拆分成了几张小表。
接下来，我们通过一个例子开始学习GROUP BY，代码如下

-- 创建数据库
DROP DATABASE IF EXISTS mydb;
CREATE DATABASE mydb;
USE mydb;

-- 创建员工表
CREATE TABLE employee (
    id int,
    name varchar(50),
    salary int,
    departmentnumber int
);

-- 向员工表中插入数据
INSERT INTO employee values(1,'tome',2000,1001); 
INSERT INTO employee values(2,'lucy',9000,1002); 
INSERT INTO employee values(3,'joke',5000,1003); 
INSERT INTO employee values(4,'wang',3000,1004); 
INSERT INTO employee values(5,'chen',3000,1001); 
INSERT INTO employee values(6,'yukt',7000,1002); 
INSERT INTO employee values(7,'rett',6000,1003); 
INSERT INTO employee values(8,'mujk',4000,1004); 
INSERT INTO employee values(9,'poik',3000,1001);

9.1 GROUP BY和聚合函数一起使用

统计各部门员工个数 MySQL命令：

select count(*), departmentnumber from employee group by departmentnumber;

运行效果展示

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Rej2QhPL-1621128108333)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513112809508.png)]

统计部门编号大于1001的各部门员工个数 MySQL命令

select count(*), departmentnumber from employee where departmentnumber>1001 group by departmentnumber;

运行效果展示

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mUdqGc9k-1621128108334)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513113013897.png)]

9.2 GROUP BY和聚合函数以及HAVING一起使用

统计工资总和大于8000的部门 MySQL命令：

select sum(salary),departmentnumber from employee group by departmentnumber having sum(salary)>8000;

运行效果展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LlKQeJmD-1621128108334)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513141720683.png)]

10.使用ORDER BY对查询结果排序

从表中査询出来的数据可能是无序的或者其排列顺序不是我们期望的。为此，我们可以使用ORDER BY对查询结果进行排序
其语法格式如下所示：

SELECT 字段名1,字段名2,…
FROM 表名
ORDER BY 字段名1 [ASC 丨 DESC],字段名2 [ASC | DESC];

在该语法中：字段名1、字段名2是查询结果排序的依据；参数 ASC表示按照升序排序，DESC表示按照降序排序；默认情况下，按照ASC方式排序。通常情况下，ORDER BY子句位于整个SELECT语句的末尾。
查询所有学生并按照年纪大小升序排列 MySQL命令：

select * from student order by age asc;

运行效果展示

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-k16ABs3p-1621128108335)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513142552032.png)]

查询所有学生并按照年纪大小降序排列 MySQL命令：

select * from student order by age desc;

运行效果展示

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CLqx7uwC-1621128108335)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513142708945.png)]

十二、别名设置

在査询数据时可为表和字段取別名，该别名代替表和字段的原名参与查询操作。
操作的表事先已准备

1.为表取别名

在查询操作时，假若表名很长使用起来就不太方便，此时可为表取一个別名，用该别名来代替表的名称。语法格式如下所示：

SELECT * FROM 表名 [AS] 表的别名 WHERE .... ;

将student改为stu查询整表 MySQL命令：

select * from student as stu;

运行效果：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LwcGYbLk-1621128108336)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513155350311.png)]

2.为字段取别名

在查询操作时，假若字段名很长使用起来就不太方便，此时可该字段取一个別名，用该别名来代替字段的名称。语法格式如下所示：

SELECT 字段名1 [AS] 别名1 , 字段名2 [AS] 别名2 , ... FROM 表名 WHERE ... ;

将student中的name取别名为“姓名” 查询整表 MySQL命令：

select name as '姓名',id from student;

运行效果展示

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fl7WHx0p-1621128108336)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513160510560.png)]

十三、表的关联关系

在实际开发中数据表之间存在着各种关联关系。在此，介绍MySQL中数据表的三种关联关系。
多对一
多对一(亦称为一对多)是数据表中最常见的一种关系。例如：员工与部门之间的关系，一个部门可以有多个员工；而一个员工不能属于多个部门只属于某个部门。在多对一的表关系 中，应将外键建在多的一方否则会造成数据的冗余。
多对多
多对多是数据表中常见的一种关系。例如：学生与老师之间的关系，一个学生可以有多个老师而且一个老师有多个学生。通常情况下，为了实现这种关系需要定义一张中间表(亦称为连接表)该表会存在两个外键分别参照老师表和学生表。
一对一
在开发过程中，一对一的关联关系在数据库中并不常见；因为以这种方式存储的信息通常会放在同一张表中。
接下来，我们来学习在一对多的关联关系中如果添加和删除数据。先准备一些测试数据，代码如下：

DROP TABLE IF EXISTS student;
DROP TABLE IF EXISTS class;

-- 创建班级表
CREATE TABLE class(
    cid int(4) NOT NULL PRIMARY KEY,
    cname varchar(30) 
);

-- 创建学生表
CREATE TABLE student(
    sid int(8) NOT NULL PRIMARY KEY,
    sname varchar(30),
    classid int(8) NOT NULL
);

-- 为学生表添加外键约束
ALTER TABLE student ADD CONSTRAINT fk_student_classid FOREIGN KEY(classid) REFERENCES class(cid);
-- 向班级表插入数据
INSERT INTO class(cid,cname)VALUES(1,'Java');
INSERT INTO class(cid,cname)VALUES(2,'Python');

-- 向学生表插入数据
INSERT INTO student(sid,sname,classid)VALUES(1,'tome',1);
INSERT INTO student(sid,sname,classid)VALUES(2,'lucy',1);
INSERT INTO student(sid,sname,classid)VALUES(3,'lili',2);
INSERT INTO student(sid,sname,classid)VALUES(4,'domi',2);

1.关联查询

查询Java班的所有学生 MySQL命令：

select * from student where classid=(select cid from class where cname='Java');

运行效果图：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-je9de3Wu-1621128108337)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513161927647.png)]

2.关于关联关系的删除数据

请从班级表中删除Java班级。在此，请注意：班级表和学生表之间存在关联关系；要删除Java班级，应该先删除学生表中与该班相关联的学生。否则，假若先删除Java班那么学生表中的cid就失去了关联
删除Java班 MySQL命令：

delete from student where classid=(select cid from class where cname='Java');
delete from class where cname='Java';

运行效果图:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ekpGe7Jw-1621128108337)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513162208477.png)]

十四、多表连接查询

1.交叉连接查询

交叉连接返回的结果是被连接的两个表中所有数据行的笛卡儿积；比如：集合A={a,b}，集合B={0,1,2}，则集合A和B的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。所以，交叉连接也被称为笛卡尔连接，其语法格式如下：

SELECT * FROM 表1 CROSS JOIN 表2;

在该语法中：CROSS JOIN用于连接两个要查询的表，通过该语句可以查询两个表中所有的数据组合。
由于这个交叉连接查询在实际运用中没有任何意义，所以只做为了解即可

2.内连接查询

内连接(Inner Join)又称简单连接或自然连接，是一种非常常见的连接查询。内连接使用比较运算符对两个表中的数据进行比较并列出与连接条件匹配的数据行，组合成新的 记录。也就是说在内连接查询中只有满足条件的记录才能出现在查询结果中。其语法格式如下：

SELECT 查询字段1,查询字段2, ... FROM 表1 [INNER] JOIN 表2 ON 表1.关系字段=表2.关系字段

在该语法中：INNER JOIN用于连接两个表，ON来指定连接条件；其中INNER可以省略。

准备数据，代码如下：

-- 若存在数据库mydb则删除
DROP DATABASE IF EXISTS mydb;
-- 创建数据库mydb
CREATE DATABASE mydb;
-- 选择数据库mydb
USE mydb;

-- 创建部门表
CREATE TABLE department(
  did int (4) NOT NULL PRIMARY KEY, 
  dname varchar(20)
);

-- 创建员工表
CREATE TABLE employee (
  eid int (4) NOT NULL PRIMARY KEY, 
  ename varchar (20), 
  eage int (2), 
  departmentid int (4) NOT NULL
);

-- 向部门表插入数据
INSERT INTO department VALUES(1001,'财务部');
INSERT INTO department VALUES(1002,'技术部');
INSERT INTO department VALUES(1003,'行政部');
INSERT INTO department VALUES(1004,'生活部');
-- 向员工表插入数据
INSERT INTO employee VALUES(1,'张三',19,1003);
INSERT INTO employee VALUES(2,'李四',18,1002);
INSERT INTO employee VALUES(3,'王五',20,1001);
INSERT INTO employee VALUES(4,'赵六',20,1004);

查询员工姓名及其所属部门名称 MySQL命令：

select employee.ename,department.dname from department inner join employee on department.did=employee.departmentid;

运行效果展示:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Frr8NLNT-1621128108338)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513164104772.png)]

3.外连接查询

在使用内连接查询时我们发现：返回的结果只包含符合查询条件和连接条件的数据。但是，有时还需要在返回查询结果中不仅包含符合条件的数据，而且还包括左表、右表或两个表中的所有数据，此时我们就需要使用外连接查询。外连接又分为左(外)连接和右(外)连接。其语法格式如下：

SELECT 查询字段1,查询字段2, ... FROM 表1 LEFT | RIGHT [OUTER] JOIN 表2 ON 表1.关系字段=表2.关系字段 WHERE 条件

由此可见，外连接的语法格式和内连接非常相似，只不过使用的是LEFT [OUTER] JOIN、RIGHT [OUTER] JOIN关键字。其中，关键字左边的表被称为左表，关键字右边的表被称为右表；OUTER可以省略。
在使用左(外)连接和右(外)连接查询时，查询结果是不一致的，具体如下：
1、LEFT [OUTER] JOIN 左(外)连接：返回包括左表中的所有记录和右表中符合连接条件的记录。
2、RIGHT [OUTER] JOIN 右(外)连接：返回包括右表中的所有记录和左表中符合连接条件的记录。

1）左连接LEFT JOIN的含义就是求两个表A表和B表的交集外加左表剩下的数据。依旧从笛卡尔积的角度讲，就是先从笛卡尔积中挑出ON子句条件成立的记录，然后加上左表A表中剩余的记录

2）右连接 RIGHT JOIN

同理右连接RIGHT JOIN就是求两个表A和B表的交集外加右表B剩下的数据。再次从笛卡尔积的角度描述，右连接就是从笛卡尔积中挑出ON子句条件成立的记录，然后加上右表中剩余的记录（见最后一条）

3）外连接 FULL OUTER JOIN

外连接就是求两个表A和B集合的并集。从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录，然后加上左表中剩余的记录，最后加上右表中剩余的记录。另外MySQL不支持OUTER JOIN，但是我们可以对左连接和右连接的结果做 UNION 操作来实现。

情景一：A和B的并集

-- 若存在数据库mydb则删除
DROP DATABASE IF EXISTS mydb;
-- 创建数据库mydb
CREATE DATABASE mydb;
-- 选择数据库mydb
USE mydb;

-- 创建班级表
CREATE TABLE class(
  cid int (4) NOT NULL PRIMARY KEY, 
  cname varchar(20)
);

-- 创建学生表
CREATE TABLE student (
  sid int (4) NOT NULL PRIMARY KEY, 
  sname varchar (20), 
  sage int (2), 
  classid int (4) NOT NULL
);
-- 向班级表插入数据
INSERT INTO class VALUES(1001,'Java');
INSERT INTO class VALUES(1002,'C++');
INSERT INTO class VALUES(1003,'Python');
INSERT INTO class VALUES(1004,'PHP');

-- 向学生表插入数据
INSERT INTO student VALUES(1,'张三',20,1001);
INSERT INTO student VALUES(2,'李四',21,1002);
INSERT INTO student VALUES(3,'王五',24,1002);
INSERT INTO student VALUES(4,'赵六',23,1003);
INSERT INTO student VALUES(5,'Jack',22,1009);

准备这组数据有一定的特点，为的是让大家直观的看出左连接与右连接的不同之处
1、班级编号为1004的PHP班级没有学生
2、学号为5的学生王跃跃班级编号为1009，该班级编号并不在班级表中

3.1 左（外）连接查询

左(外)连接的结果包括LEFT JOIN子句中指定的左表的所有记录，以及所有满足连接条件的记录。如果左表的某条记录在右表中不存在则在右表中显示为空。
查询每个班的班级ID、班级名称及该班的所有学生的名字 MySQL命令：

select class.cid,class.cname,student.sname from class left outer join student on class.cid=student.classid;

运行效果展示

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Pqbjbyzh-1621128108340)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513165543851.png)]

展示结果分析：
1、分别找出Java班、C++班、Python班的学生
2、右表的王跃跃不满足查询条件故其没有出现在查询结果中
3、虽然左表的PHP班没有学生，但是任然显示了PHP的信息；但是，它对应的学生名字为NULL

3.2 右（外）连接查询

右(外)连接的结果包括RIGHT JOIN子句中指定的右表的所有记录，以及所有满足连接条件的记录。如果右表的某条记录在左表中没有匹配，则左表将返回空值。
查询每个班的班级ID、班级名称及该班的所有学生的名字 MySQL命令：

select class.cid,class.cname,student.sname from class right outer join student on class.cid=student.classid;

运行效果展示

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lUe8NuYP-1621128108340)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513170524484.png)]

展示结果分析：
1、分别找出Java班、C++班、Python班的学生
2、左表的PHP班不满足查询条件故其没有出现在查询结果中
3、虽然右表的jack没有对应班级，但是任然显示王跃跃的信息；但是，它对应的班级以及班级编号均为NULL

十五、子查询

子查询是指一个查询语句嵌套在另一个查询语句内部的查询；该查询语句可以嵌套在一个 SELECT、SELECT…INTO、INSERT…INTO等语句中。在执行查询时，首先会执行子查询中的语句，再将返回的结果作为外层查询的过滤条件。在子査询中通常可以使用比较运算符和IN、EXISTS、ANY、ALL等关键字。

准备数据，代码如下：

DROP TABLE IF EXISTS student;
DROP TABLE IF EXISTS class;

-- 创建班级表
CREATE TABLE class(
  cid int (4) NOT NULL PRIMARY KEY, 
  cname varchar(20)
);

-- 创建学生表
CREATE TABLE student (
  sid int (4) NOT NULL PRIMARY KEY, 
  sname varchar (20), 
  sage int (2), 
  classid int (4) NOT NULL
);

-- 向班级表插入数据
INSERT INTO class VALUES(1001,'Java');
INSERT INTO class VALUES(1002,'C++');
INSERT INTO class VALUES(1003,'Python');
INSERT INTO class VALUES(1004,'PHP');
INSERT INTO class VALUES(1005,'Android');

-- 向学生表插入数据
INSERT INTO student VALUES(1,'张三',20,1001);
INSERT INTO student VALUES(2,'李四',21,1002);
INSERT INTO student VALUES(3,'王五',24,1003);
INSERT INTO student VALUES(4,'赵六',23,1004);
INSERT INTO student VALUES(5,'小明',21,1001);
INSERT INTO student VALUES(6,'小红',26,1001);
INSERT INTO student VALUES(7,'小亮',27,1002);

1.带比较运算符的子查询

比较运算符前面我们提到过得，就是>、<、=、>=、<=、!=等
查询张三同学所在班级的信息 MySQL命令：

select * from class where cid=(select classid from student where sname='张三');

运行效果展示

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MU4nCABj-1621128108341)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210513171143811.png)]

查询比张三同学所在班级编号还大的班级的信息 MySQL命令：

select * from class where cid>(select classid from student where sname='张三');

运行结果:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mkHy9OYc-1621128108342)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210514081342270.png)]

2.带EXISTS关键字的子查询

EXISTS关键字后面的参数可以是任意一个子查询， 它不产生任何数据只返回TRUE或FALSE。当返回值为TRUE时外层查询才会 执行
假如王五同学在学生表中则从班级表查询所有班级信息 MySQL命令：

select * from class where exists (select * from student where sname='王五');

运行结果:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-skopcQpz-1621128108342)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210514081747319.png)]

3.带ANY关键字的子查询

ANY关键字表示满足其中任意一个条件就返回一个结果作为外层查询条件。

查询比任一学生所属班级号还大的班级编号 MySQL命令：

select * from class where cid > any (select classid from student);

运行结果:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-57jNjJAA-1621128108343)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210514082258744.png)]

4.带ALL关键字的子查询

ALL关键字与ANY有点类似，只不过带ALL关键字的子査询返回的结果需同时满足所有内层査询条件。

查询比所有学生所属班级号还大的班级编号 MySQL命令：

select * from class where cid > all (select classid from student);

运行效果展示

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cg7sWHje-1621128108343)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210514083049475.png)]

总结
重要（从关键字分析）：
查询语句的书写顺序和执行顺序
select ===> from ===> where ===> group by ===> having ===> order by ===> limit
查询语句的执行顺序
from ===> where ===> group by ===> having ===> select ===> order by ===> limi

MySQL锁机制

原文链接：https://blog.csdn.net/qq_39390545/article/details/107786761

数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，为了保证数据的一致性，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要机制。

简单来说，数据表就好比开的一家酒店，而每行数据就像酒店的房间，如果大家随意进出，就会出现多人抢夺同一个房间的情况，而在房间上装上锁，申请到钥匙的人才可以入住并且将房间锁起来，其他人只有等他用完退房后才可以再次使用，这样保证了房间的一致性，方便酒店进行管理。

MySQL都分为哪些锁？

1. 按锁粒度从大到小分类：表锁，页锁和行锁；以及特殊场景下使用的行锁,全局锁

2. 如果按锁级别分类则有：共享（读）锁**、排他（写）锁、意向共享（读）锁、**意向排他（写）锁；

3. 以及Innodb引擎为解决幻读等并发场景下事务存在的数据问题，引入的Record Lock（行记录锁）**、Gap Lock（间隙锁）、**Next-key Lock（Record Lock + Gap Lock结合）等；

4. 还有就是我们面向编程的两种锁思想：悲观锁、乐观锁。

表锁

​ 表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。

当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，大大降低并发度。

使用表级锁定的主要是MyISAM，MEMORY，CSV等一些==非事务性存储引擎==。

行锁

​ 与表锁正相反，行锁最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力从而提高系统的整体性能。

虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。

使用行级锁定的主要是InnoDB存储引擎。

适用场景：从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新数据的情况，同时又有并发查询的应用场景。

页锁

除了表锁、行锁外，MySQL还有一种相对偏中性的页级锁，页锁是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。

使用页级锁定的主要是BerkeleyDB存储引擎。

全局锁

首先全局锁，是对整个数据库实例加锁。使用场景一般在全库逻辑备份时。

MySQL提供加全局读锁的命令：Flush tables with read lock (FTWRL)

这个命令可以使整个库处于只读状态。使用该命令之后，数据更新语句、数据定义语句和更新类事务的提交语句等修改数据库的操作都会被阻塞。

风险：

如果在主库备份，在备份期间不能更新，业务停摆
如果在从库备份，备份期间不能执行主库同步的binlog，导致主从延迟同步
  还有一种锁全局的方式：set global readonly=true ，相当于将整个库设置成只读状态，但这种修改global配置量级较重，和全局锁不同的是：如果执行Flush tables with read lock 命令后，如果客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。但将库设置为readonly后，客户端发生异常断开，数据库依旧会保持readonly状态，会导致整个库长时间处于不可写状态，试想一下微信只能看，不能打字~~

1. MySQL基于锁级别又分为：共享（读）锁、排他（写）锁、意向共享（读）锁、意向排他（写）锁

   共享（读）锁、排他（写）锁、意向共享（读）锁、意向排他（写）锁

   ​ 对于共享（读）锁、排他（写）锁，比如咱们住酒店，入住前顾客都是有权看房的，只看不住想白嫖都是可以的，前台小姐姐会把门给你打开。当然，也允许不同的顾客一起看（共享 读）。

   ​ 看房时房间相当于公共场所，小姐姐嘱咐不能乱涂乱画，也不能偷喝免费的矿泉水。。如果你觉得不错，偷偷跑到前台要定这间房，交钱后会给你这个房间的钥匙并将房间状态改为已入住，不再允许其他人看房（排他 写）。

   对了，当办理入住时前台小姐姐也会通知看房的杀马特小伙子说这间房已经有人定了！！等看房的杀马特小伙儿骂骂咧咧出门后，看到满头大汗的你，鄙夷着咽了一口口水，咳tui！然后你锁上门哼着歌儿，开始干那些见不得人的事儿~~直到你退房前，其他人无法在看你的房。

   可见，读锁是可以并发获取的（共享的），而写锁只能给一个事务处理（排他的）。当你想获取写锁时，需要等待之前的读锁都释放后方可加写锁；而当你想获取读锁时，只要数据没有被写锁锁住，你都可以获取到读锁，然后去看房。

   另外还有意向读\写锁，严格来说他们并不是一种锁，而是存放表中所有行锁的信息。就像我们在酒店，当我们预定一个房间时，就对该行（房间）添加 意向写锁，但是同时会在酒店的前台对该行（房间）做一个信息登记（旅客姓名、男女、住多长时间、家里几头牛等）。大家可以把意向锁当成这个酒店前台，它并不是真正意义上的锁（钥匙），它维护表中每行的加锁信息，是共用的。后续的旅客通过酒店前台来看哪个房间是可选的，那么，如果没有意图锁，会出现什么情况呢？假设我要住房间，那么我每次都要到每一个房间看看这个房间有没有住人，显然这样做的效率是很低下的。杀马特小伙儿表示支持！

读写锁、意向锁的兼容性如下所示；

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HoENpqmz-1621128108344)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210504104646183.png)]

共享（读）锁（Share Lock）

共享锁，又叫读锁，是读取操作（SELECT）时创建的锁。其他用户可以并发读取数据，但在读锁未释放前，也就是查询事务结束前，任何事务都不能对数据进行修改（获取数据上的写锁），直到已释放所有读锁。

如果事务A对数据B（1024房）加上读锁后，则其他事务只能对数据B上加读锁，不能加写锁。获得读锁的事务只能读数据，不能修改数据。

SQL显示加锁写法:

SELECT … LOCK IN SHARE MODE;

排他（写）锁（Exclusive Lock）

排他锁又称写锁、独占锁，如果事务A对数据B加上写锁后，则其他事务不能再对数据B加任何类型的锁。获得写锁的事务既能读数据，又能修改数据

SQL显示加锁写法:

SELECT … FOR UPDATE;

在查询语句后面增加FOR UPDATE，MySQL 就会对查询结果中的每行都加写锁，当没有其他线程对查询结果集中的任何一行使用写锁时，可以成功申请写锁，否则会被阻塞。另外成功申请写锁后，也要先等待该事务前的读锁释放才能操作。

意向锁（Intention Lock）

意向锁属于表级锁，其设计目的主要是为了在一个事务中揭示下一行将要被请求锁的类型。InnoDB 中的两个表锁：

• 意向共享锁（IS）：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁；

• 意向排他锁（IX）：类似上面，表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。

意向锁是 InnoDB 自动加的，不需要用户干预。

再强调一下，对于INSERT、UPDATE和DELETE，InnoDB 会自动给涉及的数据加排他锁；对于一般的SELECT语句，InnoDB 不会加任何锁，事务可以通过以下语句显式加共享锁或排他锁。

共享锁： SELECT … LOCK IN SHARE MODE;
排他锁： SELECT … FOR UPDATE;

乐观锁和悲观锁

悲观锁和乐观锁，也并不是 MySQL 或者数据库中独有的概念，而是并发编程的基本概念。主要区别在于，操作共享数据时，`“悲观锁”即认为数据出现冲突的可能性更大，而“乐观锁”则是认为大部分情况不会出现冲突，进而决定是否采取排他性措施。

反映到 MySQL 数据库应用开发中，悲观锁一般就是利用类似 SELECT … FOR UPDATE 这样的语句，对数据加锁，避免其他事务意外修改数据。乐观锁则与 Java 并发包中的 AtomicFieldUpdater 类似，也是利用 CAS 机制，并不会对数据加锁，`而是通过对比数据的时间戳或者版本号，来实现乐观锁需要的版本判断。

MySQL的多版本并发控制 （MVCC），其本质就可以看作是种乐观锁机制，而排他性的读写锁、两阶段锁等则是悲观锁的实现。

MySQL事务隔离级别

MySQL在多线程并发场景下，可能会出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）这类==并发问题==，为了解决这些问题，引申出“隔离级别”的概念。

谈隔离级别之前，你首先需要知道，隔离得越严实，效率就会越低。隔离就像是生活中那一件件锁事儿，枷锁越多，活得越累。在很多时候，我们都要在二者之间寻找一个平衡点。

测试数据:

mysql> select * from department;
+----+-----------+-----+-----+------------+------+-----------+
| ID | NAME      | SEX | AGE | CLASS      | PAY  | HOBBY     |
+----+-----------+-----+-----+------------+------+-----------+
|  1 | 陈哈哈    | 男  |  25 | 技术1部    | 3000 | 摸鱼      |
|  2 | 扈亚鹏    | 男  |  25 | 技术1部    | 4000 | 美食      |
|  3 | 刘晓莉    | 女  |  24 | 技术1部    | 4000 | 摸鱼      |
|  5 | 徐立楠    | 女  |  24 | 技术1部    | 4000 | 阅读      |
|  6 | 顾昊      | 男  |  25 | 技术1部    | 4000 | 摸鱼      |
|  7 | 陈子凝    | 女  |  25 | 技术1部    | 5000 | 看电影    |
| 14 | 朱志鹏    | 男  |  25 | 技术1部    | 5000 | 看小说    |
| 19 | 李昂      | 男  |  27 | 技术1部    | 7000 | 看片儿    |
+----+-----------+-----+-----+------------+------+-----------+
8 rows in set (0.00 sec)

为了更好理解下文，这里先给出个业务场景：

老板：原来陈哈哈是我失散多年的大侄子！财务，给 “陈哈哈” 的工资涨 “10000” 大洋。

陈哈哈：谢谢老叔！MUA~

并发场景下事务存在的数据问题

下面我们介绍一下脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）这三类并发问题，以及每种问题出现的原理及场景。

脏读（针对的是未提交读数据）

事务A修改了数据，但未提交，而事务B查询了事务A修改过却没有提交的数据，这就是脏读，因为事务A可能会回滚。

**场景：**老板（老叔）大喊了一嗓子，但没有指定哪个财务改。财务A大姐和财务B大哥都听到了，但他俩不知道由谁来改，就分别进行了下方流程操作：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-q631ww7K-1621128108344)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210504141856700.png)]

就这样，因为 “脏读”就导致我每个月少1万大洋？

不可重复读（针对其他提交前后，读取数据本身的对比）

事务A 先 查询了工资金额，是3000块钱，未提交 。事务B在事务A查询完之后，修改了工资金额，变成了13000, 在事务A前提交了；如果此时事务A再查询一次数据，就会发现钱跟上一次查询不一致，是13000，而不是3000。这就是不可重复读。强调事务A对要操作的数据被别人修改了，但在不知请的情况下拿去做之前的用途。

**场景同上：**老板嗷一嗓子，但没有指定哪个财务改。财务A大姐和财务B大哥都听到了，但他俩不知道由谁来改，就分别进行了下方流程操作：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-R662lMJG-1621128108345)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210504142211040.png)]

对于不可重复读，说简单点就是同一个事物内，查到的结果都不一致，就失去了MySQL的**“一致性”**，这是很严重的错误。你想，如果财务大姐没有二次确认，而是直接以第一次查询为准，又给我加了1万怎么办？想想还有点小激动呢。

幻读（针对其他提交前后，读取数据条数的对比）

幻读是指在同一个事务中，存在前后两次查询同一个范围的数据，但是第二次查询却看到了第一次查询没看到的行，一般情况下只新增。

事务A先修改了某个表的所有纪录的状态字段为已处理，未提交；事务B也在此时新增了一条未处理的记录，并提交了；事务A随后查询记录，却发现有一条记录是未处理的，很是诧异，刚刚不是全部修改为已处理嘛，以为出现了幻觉，这就是幻读。

场景：老板每个月审批一次涨薪（审批表：shenpiTable），这时财务刚刚把我的工资申请提交了，老板正好在审批。一键审批通过后，突然看到了一条新的“未审批”记录（新增的），还是大侄子陈哈哈的。

老板：有幻觉？有BUG！！等等，我如果假装看不到这月是不是就省了1万块大洋？

陈哈哈：？？？

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nn3ubg8p-1621128108345)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210504142959853.png)]

• 脏读说的是事务知道了自己本不应该知道的东西，强调的动作是查询，我看到了自己不该看的东西 ;

• 不可重复读强调的是一个人查的时候，其他人却可以增删改, 但我却不知道数据被改了，还拿去做了之前的用途；

• 幻读强调的是我修改了数据，等我要查的时候，却发现有我没有修改的记录，为什么，因为有其他人插了一条新的。

隔离级别概述

为了解决上述问题，MySQL制定了四种不同的“隔离级别”，包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。

隔离级别	效果
读未提交（RU）	一个事务还没提交时，它做的变更就能被别的事务看到。（别的事务指同一时间进行的增删改查操作）
读提交（RC）	一个事务提交（commit）之后，它做的变更才会被其他事务看到。
**可重复读（RR） **	一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
**串行(xíng) 化 **	正如物理书上写的，串行是单线路，顾名思义在MySQL中同一时刻只允许单个事务执行，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

实例分析

（场景再现）老板：原来陈哈哈又双叒（ruò）叕（zhuó）是我失散多年的大侄子！财务，把 “陈哈哈” 的工资涨 “10000” !

以下表中的两个事务为例，看看在不同隔离级别下，分别会出现什么结果。能否避免上述问题呢?

时间点	事务A	事务B
T1	Begin; SELECT PAY from department where NAME = ‘陈哈哈’; （查询工资：3000）
T2		Begin;
T3		SELECT PAY from department where NAME = ‘陈哈哈’; （查询工资：3000）
T4		UPDATE department SET PAY = PAY + 10000 where NAME = ‘陈哈哈’;
T5	SELECT PAY from department where NAME = ‘陈哈哈’; （查询工资：Res_A1）
T6		commit;
T7	SELECT PAY from department where NAME = ‘陈哈哈’; （查询工资：Res_A2）
T8	commit;
T9	SELECT PAY from department where NAME = ‘陈哈哈’; （查询工资：Res_A3）

读未提交（RU）

读未提交	Res_A1	Res_A2	Res_A3
结果	13000	13000	13000

在RU隔离级别下，事务A 在T5时刻，就可以提前读到未提交的事务B 结果。

读提交（RC）

读提交	Res_A1	Res_A2	Res_A3
结果	3000	13000	13000

读提交又叫读已提交，在RC隔离级别下，事务A 需要在 事务B commit提交后，才能看到事务B 修改的结果。所以在T5时刻，事务A 查到的陈哈哈的工资是 3000。

可重复读(RR)

可重复读	Res_A1	Res_A2	Res_A3
结果	3000	3000	13000

可重复读是MySQL默认的隔离级别，在RR级别下，对于所有进行中（begin - commit）的事务，比如事务A，无论执行多少次SELECT（查询表 department ），只能看到的是同一张 department 表的结果视图（ReadView），该视图（ReadView）是在本事务启动（begin）时生成的，在事务A 结束（commit）后释放。该隔离级别会保证单事务内查看视图的一致性，称为“可重复读”。

串行化

串行化	Res_A1	Res_A2	Res_A3
结果	3000	3000	13000

串行化隔离级别不支持并发事务，由于事务A 早于事务B，事务A执行SELECT时，就给 department 表加了锁，事务B 需要等事务A 结束后才能执行，因此T5、T7时刻是 3000，T8时刻事务A提交，事务B释放锁并执行，最后T9时刻查到我的工资是 13000。

原理描述

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。在MySQL默认的隔离级别“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。这里需要注意的是，“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；而“串行化”隔离级别下直接用加锁的方式来避免并行访问。

我们可以看到在不同的隔离级别下，数据库行为是有所不同的。Oracle 数据库的默认隔离级别其实就是“读提交”，因此对于一些从 Oracle 迁移到 MySQL 的应用，为保证数据库隔离级别的一致，你一定要记得将 MySQL 的隔离级别设置为“读提交”。

配置的方式是，将启动参数 transaction-isolation 的值设置成 READ-COMMITTED。你可以用 show variables 来查看当前的值。

mysql> show variables like 'transaction_isolation';
 
+-----------------------+----------------+
| Variable_name | Value |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+

总结来说，存在即合理，每种隔离级别都有自己的使用场景，你要根据自己的业务情况来定。我想你可能会问那什么时候需要“可重复读”的场景呢？我们来看一个数据校对逻辑的案例。

假设你在管理一个个人银行账户表。一个表存了每个月月底的余额，一个表存了账单明细。这时候你要做数据校对，也就是判断上个月的余额和当前余额的差额，是否与本月的账单明细一致。你一定希望在校对过程中，即使有用户发生了一笔新的交易，也不影响你的校对结果。

这时候使用“可重复读”隔离级别就很方便。事务启动时的视图可以认为是静态的，不受其他事务更新的影响。

四种隔离级别的问题解决情况

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-W33qGYqI-1621128108346)(C:\Users\QiaoZheng\AppData\Roaming\Typora\typora-user-images\image-20210504150252723.png)]

建立测试表语句

-- 建表语句
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(30) CHARACTER SET utf8mb4 NOT NULL,
  `SEX` char(2) NOT NULL,
  `AGE` int(11) NOT NULL,
  `CLASS` varchar(10) NOT NULL,
  `PAY` int(11) NOT NULL,
  `HOBBY` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;

INSERT INTO `department` (`ID`, `NAME`, `SEX`, `AGE`, `CLASS`, `PAY`, `HOBBY`) VALUES ('1', '陈哈哈', '男', '25', '技术1部', '3000', '摸鱼');
INSERT INTO `department` (`ID`, `NAME`, `SEX`, `AGE`, `CLASS`, `PAY`, `HOBBY`) VALUES ('2', '扈亚鹏', '男', '25', '技术1部', '4000', '美食');
INSERT INTO `department` (`ID`, `NAME`, `SEX`, `AGE`, `CLASS`, `PAY`, `HOBBY`) VALUES ('3', '刘晓莉', '女', '24', '技术1部', '4000', '摸鱼');
INSERT INTO `department` (`ID`, `NAME`, `SEX`, `AGE`, `CLASS`, `PAY`, `HOBBY`) VALUES ('5', '徐立楠', '女', '24', '技术1部', '4000', '阅读');
INSERT INTO `department` (`ID`, `NAME`, `SEX`, `AGE`, `CLASS`, `PAY`, `HOBBY`) VALUES ('6', '顾昊', '男', '25', '技术1部', '4000', '摸鱼');
INSERT INTO `department` (`ID`, `NAME`, `SEX`, `AGE`, `CLASS`, `PAY`, `HOBBY`) VALUES ('7', '陈子凝', '女', '25', '技术1部', '5000', '看电影');
INSERT INTO `department` (`ID`, `NAME`, `SEX`, `AGE`, `CLASS`, `PAY`, `HOBBY`) VALUES ('14', '朱志鹏', '男', '25', '技术1部', '5000', '看小说');
INSERT INTO `department` (`ID`, `NAME`, `SEX`, `AGE`, `CLASS`, `PAY`, `HOBBY`) VALUES ('19', '李昂', '男', '27', '技术1部', '7000', '看片儿');

以上就是对MySQL中三种并发数据问题以及四种隔离级别的介绍。

CHAR和VARCHAR有哪些区别

固定长度 & 可变长度

• VARCHAR
  VARCHAR类型用于存储可变长度字符串，是最常见的字符串数据类型。它比固定长度类型更节省空间，因为它仅使用必要的空间(根据实际字符串的长度改变存储空间)。
    有一种情况例外，如果MySQL表使用ROW_FORMAT=FIXED创建的话，每一行都会使用定长存储

• CHAR

  ​ CHAR类型用于存储固定长度字符串：MySQL总是根据定义的字符串长度分配足够的空间。当存储CHAR值时，MySQL会删除字符串中的末尾空格(在MySQL 4.1和更老版本中VARCHAR 也是这样实现的——也就是说这些版本中CHAR和VARCHAR在逻辑上是一样的，区别只是在存储格式上)。
    同时，CHAR值会根据需要采用空格进行剩余空间填充，以方便比较和检索。但正因为其长度固定，所以会占据多余的空间，也是一种空间换时间的策略；

存储方式

• VARCHAR

  ​ VARCHAR需要使用1或2个额外字节记录字符串的长度：如果列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节。假设采用latinl字符集，一个VARCHAR(10)的列需要11个字节的存储空间。VARCHAR(1000)的列则需要1002 个字节，因为需要2个字节存储长度信息。

  VARCHAR节省了存储空间，所以对性能也有帮助。但是，由于行是变长的，在UPDATE时可能使行变得比原来更长，这就导致需要做额外的工作。如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，在这种情况下，不同的存储引擎的处理方式是不一样的。例如，MylSAM会将行拆成不同的片段存储，InnoDB则需要分裂页来使行可以放进页内。

• CHAR

  ​ CHAR适合存储很短或长度近似的字符串。例如，CHAR非常适合存储密码的MD5值，因为这是一个定长的值。对于经常变更的数据，CHAR也比VARCHAR更好，因为定长的CHAR类型不容易产生碎片。对于非常短的列，CHAR比VARCHAR在存储空间上也更有效率。例如用CHAR(1)来存储只有Y和N的值，如果采用单字节字符集只需要一个字节，但是VARCHAR(1)却需要两个字节，因为还有一个记录长度的额外字节。

存储容量

• CHAR

  对于char类型来说，最多只能存放的字符个数为255，和编码无关，任何编码最大容量都是255。

• VARCHAR

  MySQL行默认最大65535字节，是所有列共享（相加）的，所以VARCHAR的最大值受此限制。

  表中只有单列字段情况下，varchar一般最多能存放(65535 - 3)个字节，varchar的最大有效长度通过最大行数据长度和使用的字符集来确定，通常的最大长度是65532个字符（当字符串中的字符都只占1个字节时，能达到65532个字符）；

为什么是65532个字符？算法如下（有余数时向下取整）：

最大长度(字符数) = （行存储最大字节数 - NULL标识列占用字节数 - 长度标识字节数） / 字符集单字符最大字节数

• NULL标识列占用字节数：允许NULL时，占一字节
• 长度标识字节数：记录长度的标识，长度小于等于255（28）时，占1字节；小于65535时（216）,占2字节

VARCHAR类型在4.1和5.0版本发生了很大的变化，使得情况更加复杂。从MySQL 4.1开始，每个字符串列可以定义自己的字符集和排序规则。这些东西会很大程度上影响性能。

• 4.0版本及以下，MySQL中varchar长度是按**字节**展示，如varchar(20)，指的是20字节；
• 5.0版本及以上，MySQL中varchar长度是按**字符**展示。如varchar(20)，指的是20字符。

当然，行总长度还是65535字节，而字符和字节的换算，则与编码方式有关，不同的字符所占的字节是不同的。编码划分如下：

GBK编码：
一个英文字符占一个字节，中文2字节，单字符最大可占用2个字节。

UTF-8编码：
一个英文字符占一个字节，中文3字节，单字符最大可占用3个字节。

utf8mb4编码：
一个英文字符占一个字节，中文3字节，单字符最大占4个字节（如emoji表情4字节）。

假设当前还有6字节可以存放字符，按单字符占用最大字节数来算，可以存放3个GBK、或2个utf8、或1个utf8mb4。

既然VARCHAR长度可变，那我要不要定到最大?

没错，相信你已经有答案了，别这么干！

就像使用VARCHAR(5)和VARCHAR(200)存储 '陈哈哈’的磁盘空间开销是一样的。那么使用更短的列有什么优势呢？

事实证明有很大的优势。更长的列会消耗更多的内存，因为MySQL通常会分配固定大小的内存块来保存内部值。

当然，在没拿到存储引擎存储的数据之前，并不会知道我这一行拿出来的数据到底有多长，可能长度只有1，可能长度是500，那怎么办呢？那就只能先把最大空间分配好了，避免放不下的问题发生，这样实际上对于真实数据较短的varchar确实会造成空间的浪费。

举例：我向数据类型为：varchar（1000）的列插入了1024行数据，但是每个只存一个字符，那么这1024行真实数据量其实只有1K，但是我却需要约1M的内存去适应他。所以最好的策略是只分配真正需要的空间。

CHAR和VARCHAR在SQL中需要注意的点

下面通过一个具体的示例来说明CHAR和VARCHAR类型存储时的区别。我们创建一张同时存在CHAR(10)字段、VARCHAR(10)字段的表，并且往里面插入一些值来做对比验证：

-- 建表语句
CREATE TABLE `str_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `str_char` char(10) DEFAULT NULL,
  `str_varchar` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;

分别插入一些字符串前面和后面都有空格的示例

-- 插入测试数据
INSERT INTO `str_table` (`id`, `str_char`, `str_varchar`) 
VALUES 
(null, '陈哈哈', '陈哈哈'),
(null, '  陈哈哈', '  陈哈哈'),
(null, '陈哈哈  ', '陈哈哈  ');

测试数据查询语句如下，通过拼接能更好的看出对比效果：

-- 测试数据查询
select id,concat("|",str_char,"|") as `char`,concat("|",str_varchar,"|") as `varchar` from str_table;

mysql> select id,concat("|",str_char,"|") as `char`,concat("|",str_varchar,"|") as `varchar` from str_table;
+----+---------------+---------------+
| id | char          | varchar       |
+----+---------------+---------------+
|  6 | |陈哈哈|      | |陈哈哈|      |
|  7 | |  陈哈哈|    | |  陈哈哈|    |
|  8 | |陈哈哈|      | |陈哈哈  |    |
+----+---------------+---------------+
3 rows in set (0.00 sec)

• 当检索这些值的时候，会发现id=8行中，char类型的"陈哈哈 "末尾的空格被截断了，而VARCHAR(10)字段存储相同的值时，末尾的空格被保留了。
• 另外，id=7行的数据前面空格都被保留了。

可见，CHAR会默认切掉字符串末尾的空格，如果需要保留末尾的空格，记得用varchar类型！

类似的二进制类型：VARBINARY

与CHAR和VARCHAR类似的类型还有BINARY和VARBINARY,它们存储的是二进制字符串。二进制字符串跟常规字符串非常相似，但是二进制字符串存储的是字节码而不是字符。 填充也不一样：MySQL填充BINARY采用的是\0 (零字节)而不是空格，在检索时也不会去掉填充值。

当需要存储二进制数据，并且希望MySQL使用字节码而不是字符进行比较时，这些类型是非常有用的。二进制比较的优势并不仅仅体现在大小写敏感上。MySQL比较BINARY字符串时，每次按一个字节，并且根据该字节的数值进行比较。因此，二进制比 较比字符比较简单很多，所以也就更快。

• varchar

  varchar是可变长度字符类型，如果对应的数据库排序规则是utf8_general_ci，那么查询的时候将不区分大小写。如果排序规则是utf8_bin，则会区分大小写。

• varbinary
  varbinary是二进制字符类型，在排序规则utf8_general_ci下，是可以区分大小写的。

常用存储引擎区别

存储引擎是数据库的核心，在MySQL中，存储引擎是以插件的形式运行的。支持的引擎有十几种之多，但我们实战常用到的，大概只有InnoDB、MyISAM 和 Memory 了。术业有专攻，这三种引擎各有特点和适用场景，也算是各自的生存技能了，就像各种编程语言一样。

InnoDB

为什么先说InnoDB？InnoDB 从 MySQL5.5（2010年） 版本代替 MyISAM 成为默认引擎，可以说只要玩儿过 MySQL 的，都用过InnoDB，相比MyISAM强调性能，InnoDB 侧重于提供事务支持以及外部键等高级数据库功能。在大厂面试中也频频被问到，那 InnoDB 到底有多牛B呢？先来看看它有哪些特点；

1、支持事务。默认的事务隔离级别为可重复读（REPEATABLE-READ），通过MVCC（并发版本控制）来实现。

2、使用的锁粒度默认为行级锁，可以支持更高的并发；当然，也支持表锁。

其实有这两点就足以奠定InnoDB在存储引擎中的霸主地位了。

3、支持外键约束；外键约束其实降低了表的查询速度，但是增加了表之间的耦合度。

4、可以通过自动增长列，方法是auto_increment。

5、配合一些热备工具可以支持在线热备份

6、在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度；

7、对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于B+树的叶子节点上；

8、InnoDB 表的select count(*)比 MyISAM 慢很多；当执行 select coun(星号) from t 时，会先把数据读出来，一行一行的累加，最后返回总数量。 是的，真的会很慢。需要注意的是，当count(星号) 语句包含 where 条件时，两种表的操作是一样的。

9、DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。

InnoDB的存储表和索引有下面两种形式：

• 共享表空间存储：所有的表和索引存放在同一个表空间中。
• 多表空间存储：表结构放在.frm文件，数据和索引放在.ibd文件中。分区表的话，每个分区对应单独的.ibd文件，使用分区表的好处在于提升查询效率。

对于InnoDB来说，最大的优势在于支持事务，当然这是以牺牲效率为代价的。

MyISAM

MyISAMysql的特点：

1、不支持事务。这也算特点？？？没错，就是这么不要脸。不支持事务，像是挣脱了枷锁，在读写（Insert、select）效率上，要高于InnoDB不少。场景在：日志记录、调查统计表时，绝对值得一用。对了，不支持事务，自然就不支持锁！

2、体积小，质量大。MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。同时能加载更多索引，而Innodb的索引和数据是紧密捆绑的，没有使用压缩从而会造成 Innodb 比 MyISAM 数据文件体积庞大很多。

每张MyISAM表在磁盘上会对应三个文件。

（1）.frm文件：存储表的定义数据

（2）.MYD文件：存放表具体记录的数据

（3）.MYI文件：存储索引

3、从以往经验来说，select count(*) 和 order by 大概是使用最频繁的，大概能占了整个sql总语句的60%以上的操作，而这种操作Innodb其实也是会锁表的，很多人以为Innodb是行级锁，那个只是where对它主键是有效，非主键的还是会锁全表的。

4、常常应用部门需要我给他们定期某些表的数据，MyISAM的话很方便，只要发给他们对应那表的（frm.MYD,MYI）的文件，让他们自己在对应版本的数据库启动就行，而Innodb就需要导出.sql了，因为光给别人文件，受字典数据文件的影响，对方是无法使用的。

5、如果和 MyISAM 比 Insert 写操作的话，Innodb还达不到MyISAM的写性能，如果是针对基于索引的update操作，确实MyISAM会慢与InnoDB，但在并发环境下，从库同步也是个事儿，还不如通过多实例分库分表架构来解决。

6、MyISAM表的select count() 是非常快的；在 MyISAM 存储引擎中，把表的总行数（row）存储在磁盘上，当执行 select count() from t 时，直接返回总数据。同样，当 count(*) 语句包含 where条件时，两种表的操作是一样的。

7、DELETE FROM table时，MyISAM会先将表结构备份到一张虚拟表中，然后执行drop，最后根据备份重建该表。

Memory

你可以将它理解为，临时表。

没错，Memory是将数据直接存在内存中的，特别适合数据量小的表。同时为了提高数据的访问速度，每一个表实际上和一个磁盘文件关联，文件是frm。

1、支持的数据类型有限制，比如：不支持TEXT和BLOB类型。对于字符串类型的数据，只支持固定长度的行，VARCHAR(64)会被自动存储为CHAR(64)类型；

2、只支持表级锁。所以，在访问量比较大时，表级锁会成为MEMORY存储引擎的瓶颈；

3、由于数据是存放在内存中，一旦服务器宕机，数据就会丢失；数据库主从切换的配置要设置好。

4、查询的时候，如果有用到临时表，而且临时表中有BLOB，TEXT类型的字段，那么这个临时表就会转化为MyISAM类型的表，性能会急剧降低；

5、默认使用hash索引。

3、支持外键约束；外键约束其实降低了表的查询速度，但是增加了表之间的耦合度。

4、可以通过自动增长列，方法是auto_increment。

5、配合一些热备工具可以支持在线热备份

6、在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度；

7、对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于B+树的叶子节点上；

8、InnoDB 表的select count(*)比 MyISAM 慢很多；当执行 select coun(星号) from t 时，会先把数据读出来，一行一行的累加，最后返回总数量。 是的，真的会很慢。需要注意的是，当count(星号) 语句包含 where 条件时，两种表的操作是一样的。

9、DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。

InnoDB的存储表和索引有下面两种形式：

• 共享表空间存储：所有的表和索引存放在同一个表空间中。
• 多表空间存储：表结构放在.frm文件，数据和索引放在.ibd文件中。分区表的话，每个分区对应单独的.ibd文件，使用分区表的好处在于提升查询效率。

对于InnoDB来说，最大的优势在于支持事务，当然这是以牺牲效率为代价的。

MyISAM

MyISAMysql的特点：

1、不支持事务。这也算特点？？？没错，就是这么不要脸。不支持事务，像是挣脱了枷锁，在读写（Insert、select）效率上，要高于InnoDB不少。场景在：日志记录、调查统计表时，绝对值得一用。对了，不支持事务，自然就不支持锁！

2、体积小，质量大。MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。同时能加载更多索引，而Innodb的索引和数据是紧密捆绑的，没有使用压缩从而会造成 Innodb 比 MyISAM 数据文件体积庞大很多。

每张MyISAM表在磁盘上会对应三个文件。

（1）.frm文件：存储表的定义数据

（2）.MYD文件：存放表具体记录的数据

（3）.MYI文件：存储索引

3、从以往经验来说，select count(*) 和 order by 大概是使用最频繁的，大概能占了整个sql总语句的60%以上的操作，而这种操作Innodb其实也是会锁表的，很多人以为Innodb是行级锁，那个只是where对它主键是有效，非主键的还是会锁全表的。

4、常常应用部门需要我给他们定期某些表的数据，MyISAM的话很方便，只要发给他们对应那表的（frm.MYD,MYI）的文件，让他们自己在对应版本的数据库启动就行，而Innodb就需要导出.sql了，因为光给别人文件，受字典数据文件的影响，对方是无法使用的。

5、如果和 MyISAM 比 Insert 写操作的话，Innodb还达不到MyISAM的写性能，如果是针对基于索引的update操作，确实MyISAM会慢与InnoDB，但在并发环境下，从库同步也是个事儿，还不如通过多实例分库分表架构来解决。

6、MyISAM表的select count() 是非常快的；在 MyISAM 存储引擎中，把表的总行数（row）存储在磁盘上，当执行 select count() from t 时，直接返回总数据。同样，当 count(*) 语句包含 where条件时，两种表的操作是一样的。

7、DELETE FROM table时，MyISAM会先将表结构备份到一张虚拟表中，然后执行drop，最后根据备份重建该表。

Memory

你可以将它理解为，临时表。

没错，Memory是将数据直接存在内存中的，特别适合数据量小的表。同时为了提高数据的访问速度，每一个表实际上和一个磁盘文件关联，文件是frm。

1、支持的数据类型有限制，比如：不支持TEXT和BLOB类型。对于字符串类型的数据，只支持固定长度的行，VARCHAR(64)会被自动存储为CHAR(64)类型；

2、只支持表级锁。所以，在访问量比较大时，表级锁会成为MEMORY存储引擎的瓶颈；

3、由于数据是存放在内存中，一旦服务器宕机，数据就会丢失；数据库主从切换的配置要设置好。

4、查询的时候，如果有用到临时表，而且临时表中有BLOB，TEXT类型的字段，那么这个临时表就会转化为MyISAM类型的表，性能会急剧降低；

5、默认使用hash索引。