【MySQL】MySQL 基础

发表于2021-03-29更新于2022-02-17字数统计17.5k阅读时长116分阅读次数

1. 初识 MySQL

DB：DataBase，数据库，实际上在硬盘上以文件的形式存在
SQL：Structured Query Language，结构化查询语言，是一门标准通用的语言，标准sql适用于所有的数据库产品，SQL语句在执行时，内部会先进行编译，然后再执行。（SQL语句的编译由DBMS完成）
DBMS： DataBase Management System，数据库管理系统，如MySQL，Oracle，DB2等，用来管理DB（用来管理数据库中的数据）

DBMS负责执行sql语句，其通过执行sql语句来操作DB中的数据。

1.1 启动 MySQL 服务

命令行启动MySQL服务（管理员模式）

1	net start mysql

1.2 连接数据库

命令行连接数据库（管理员模式）

1	mysql -u root -p xxxxx -- 连接数据库

1.3 SQL 命令

更改用户密码并刷新权限（sql语句需要以;结尾）

UPDATE mysql.user SET authentication_string=password('123456')
WHERE user='root' AND Host='localhost'; -- 更改用户密码

FLUSH PRIVILEGES; -- 刷新权限

基本命令

SHOW DATABASES; -- 查询所有数据库
USE xxx; -- 切换数据库 use 数据库名

SHOW TABLES; -- 查看数据库中所有的表
DESCRIBE student; -- 显示数据库中所有的表的信息

CREATE DATABASE westos; -- 创建一个数据库

EXIT; -- 退出连接

-- 单行注释
/* 多行注释
hello 
MySQL
*/

1.4 数据库语言分类

DDL： Database Definition Language 数据库定义语言。用于对表的结构进行增删改：CREATE DROP ALTER
DML：Data Manipulation Language 数据库操纵语言。用于对表的数据进行增删改：INSERT DELETE UPDATE
DQL：Data Query Language 数据库查询语言。用于查询表中的数据，基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块：
DCL：Data Control Language 数据库控制语言。用来授予或回收访问数据库的某种特权：GRANT REVOKE
TCL：Transaction Control Language 事务控制语言。控制数据库操纵事务发生的时间及效果，对数据库实行监视等：COMMIT ROLLBACK

2. 数据库基础

2.1 操作数据库

操作数据库 ——> 操作数据库中的表 ——> 操作数据库中表的数据

MySQL关键字不区分大小写

以下关键字属于MySQL命令，并非SQL语句，只有在MySQL中才能使用

创建数据库

1	CREATE DATABASE [IF NOT EXISTS] school;

删除数据库

1	DROP DATABASE [IF EXISTS] school;

使用数据库

1 2	-- 如果表名或者字符段名是一个特殊字符，需要带上 ` ` USE `school`;

查看数据库

1	SHOW DATABASES; -- 查看所有数据库

2.2 数据库的列类型

数值

tinyint：十分小的数据， 1个字节
smallint：较小的数据， 2个字节
mediumint：中等大小的数据， 3个字节
int：标准的整数， 4个字节
bigint：较大的数据， 8个字节
float：浮点数， 4个字节
double：浮点数， 8个字节
decimal：字符串形式的浮点数，金融计算时，一般使用decimal

其中浮点型数据创建时可以指定保留位数。例如double(M, D)代表小数点后保留D位小数，M代表整数部位+小数部位的总长度，如果超出范围，则插入临界值。M和D可以省略，如果是decimal，则默认M=10，D=0；如果是float和double，则会根据插入的数值的精度来决定精度。

字符串

char： 固定大小字符串 0~255（在内存中分配固定长度存储数据）
varchar：可变长度字符串 0~65535，类比Java中String（按照实际字符串大小在内存中分配空间）
tinytext：微型文本 2^8 - 1
text：文本串 2 ^16 - 1 保存大文本

char(M)表示最大长度不能超过M，其中M可以省略，默认为1；varchar(M)的M不可以省略。

时间日期

类比java.util.Date

date： YYYY-MM-DD 日期格式
time ： HH:mm::ss 时间格式
datetime： YYYY-MM-DD HH:mm::ss 最常用的时间格式
timestamp：时间戳，1970.01.01到现在的毫秒数
year：年份表示

大对象

BLOB：Binary Large Object 二进制大对象（存储图片、视频等流媒体信息）对应Java中的Object
CLOB：Character Large Object 字符大对象（存储较大文本，比如可以存储4G的字符串）对应Java中的Object

NULL

没有值，未知
注意，不要使用NULL进行运算，NULL和任何数参与运算，计算结果都为NULL
想避免NULL对运算结果产生影响，则使用IFNULL空处理函数：IFNULL(XXX, 0)

2.3 存储引擎

存储引擎是对一张表而言，而非对数据库而言，不同的表可以有不同的存储引擎（可以在 CREATE 语句中指定存储引擎类型）

存储引擎即一张表在数据库中的存储方式。

	MyISAM	InnoDB
事务支持	不支持（最新版支持）	支持
数据行表锁	不支持行锁，只支持表锁	支持行锁
外键约束	不支持	支持
全文索引	支持	不支持（最新版支持）
表空间的大小	较小	较大，约为2倍
缓存	只缓存索引	不仅缓存索引，还要缓存真实数据
关注点	性能	事务

MyISAM：可被转换为压缩、只读表来节省空间。节约空间，速度较快
InnoDB：安全性高，支持事务的处理，多表多用户操作，在MySQL服务器崩溃后提供自动恢复，支持级联删除和更新
MEMORY：查询速度最快，表数据和索引被存储在内存中，不支持事务，数据容易丢失。不能包含TEXT或BLOB字段

在物理空间存在的位置

所有数据库文件都存在data目录下，一个文件夹就是一个数据库，本质还是文件的存储。MySQL引擎在物理文件上的区别：

InnoDB：
- *.frm 表结构的定义文件
- *.ibd 数据和索引共同存储在一个文件中
MyISAM：
- *.frm 表结构的定义文件
- *.MYD 数据文件（data）
- *.MYI 索引文件（index）

其中，InnDB 采用聚集（聚数）索引（索引和真实数据存储在一起），MyISAM 采用非聚集（稀疏）索引（索引和真实数据分开存储），这才导致了二者在磁盘上存储文件类型的区别。

三种存储引擎的适用条件：

MyISAM：适用于大量的数据读而少量数据更新的混合操作，另一种适用情形是使用压缩的只读表
InnoDB：适用于查询中包含较多的数据更新操作，其行级锁机制和多版本的支持为数据读取和更新的混合操作提供了良好的并发机制
MEMORY：适用于存储非永久需要的数据，或者是能够从基于磁盘的表中重新生成的数据

2.4 数据表的类型

设置数据库表的字符集编码

1	CHARSET=utf8

不设置的话，会是MySQL默认的字符集编码Latin1（不支持中文）。

可以在my.ini中配置默认的编码：

1	character-set-server=utf8

2.5 约束

在创建表时，可以给表的字段添加相应的约束，从而保证表中数据的合法性、有效性和完整性。

非空约束（NOT NULL）：约束的字段不能为NULL
唯一约束（UNIQUE）：约束的字段不能重复
主键约束（PRIMARY KEY）：约束的字段既不能为NULL，也不能重复（简称PK）
外键约束（FOREIGN KEY)：用于限制两个表的关系，约束的字段的值必须来自于主表的关联列的值（简称FK）
无符号约束（UNSIGNED）：约束该字段为无符号
零填充约束（ZEROFILL）：限制该字段0填充
自增约束（AUTO_INCREMENT）：限制该字段自增
默认值约束（DEFAULT）：设置该字段的默认值

唯一性约束（UNIQUE）

UNIQUE：唯一性约束修饰的字段具有唯一性，不能重复，但可以设置为NULL。

若给两列同时加UNIQUE，则为表级约束，联合起来不重复即可。若在列后面直接加UNIQUE，则为列级约束，该列数据不能有重复。

主键（PRIMARY KEY）

PRIMARY KEY：主键值是这行数据在这张表中的唯一标识（第一范式要求每张表必须有主键）。

一张表的主键约束只能有一个，可以是单一主键（推荐）或复合主键（不推荐，违反三范式）。主键本身也包含了唯一性约束。主键值最好是和业务没有关系的自然数。在字段后添加AUTO_INCREMENT设置主键值自增

外键（FOREIGN KEY）

FOREIGN KEY：用于减少表的冗余，将一张表拆成两张表。

外键引用的父表的字段必须是唯一的（UNIQUE），一般情况都是子表外键引用父表主键

无符号约束（UNSIGNED）

无符号的整数
声明了该列不能声明为负数

零填充约束（ZEROFILL）

0填充的
不足的位数，使用0填充。zerofill设置为3时，5——005

自增约束（AUTO_INCREMENT）

自动在上一条记录的基础上+1（默认）
通常用来设计唯一的主键，例如Index，必须是整数类型
可以自定义设置主键自增的起始值和步长

默认值约束（DEFAULT）

设置默认的值
例如若sex设置默认值为男，如果不指定该列的值，则会设置为默认值

增加外键约束的方式：

方式一：在创建表的时候，增加约束（比较麻烦）

CREATE TABLE `grade`(
    `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
    `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
    PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 学生表的gradeid字段要去引用年级表的gradeid
-- 定义外键KEY
-- 给这个外键添加约束（执行引用） reference 引用
CREATE TABLE IF NOT EXISTS `student`(
    `id` INT(4) NOT NULL AUTO_INCREMENT  COMMENT '学号',
    `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
    `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
    `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
    `gradeid` INT(10) NOT NULL COMMENT '学生年级',
    `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
    `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
    CONSTRAINT pk PRIMARY KEY(`id`), 
    CONSTRAINT uq UNIQUE(`name`), 
    KEY `FK_gradeid`(`gradeid`),
    CONSTRAINT `FK_gradid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

删除有外键关系的表时，必须先删除引用别人的表（从表），再删除被引用的表（主表）

方式二：创建表成功后，添加外键约束

CREATE TABLE `grade`(
    `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
    `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
    PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `student`(
    `id` INT(4) NOT NULL AUTO_INCREMENT  COMMENT '学号',
    `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
    `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
    `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
    `gradeid` INT(10) NOT NULL COMMENT '学生年级',
    `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
    `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
    PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 哪个表（哪个字段)
ALTER TABLE `student` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);

以上操作都是物理外键，数据库级别的外键，不建议使用（避免数据库过多造成困扰）

数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
想使用多张表的数据，想使用外键时，使用程序实现

主键约束（PRIMARY KEY）和唯一约束（UNIQUE）对比：

	保证唯一性	是否允许为空	一个表可以有几个	是否允许组合
主键约束	√	×	最多有一个	√
唯一约束	√	√	可以有多个	√

列级约束和表级约束对比

	位置	支持的约束类型	是否可以起约束名
列级约束	列的后面	语法都支持，但外键没有效果	不可以
表级约束	所有列的下面	默认和非空约束不支持，其他支持	可以（主键没有效果）

3. 数据库定义语言（DDL）

3.1 创建表（CREATE）

-- 注意点 英文括号()，表的名称和字段尽量使用``
-- 字符串使用单引号''括起来
-- 所有语句后面加,（最后一句不用加）
-- PRIMAY KEY 一张表的主键约束只能有一个。单一主键或复合主键（不推荐，因为违反三范式）
CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4) UNSIGNED NOT NULL AUTO_INCREMENT  COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

一般格式：

CREATE TABLE [IF NOT EXISTS] `表名`(
    `字段名` 列类型 [约束] [索引] [注释],
    `字段名` 列类型 [约束] [索引] [注释],
    ...
    `字段名` 列类型 [约束] [索引] [注释],
    PRIMARY KEY(`字段名`)
)[表类型][注释][字符集设置];

约束位置处可以添加：

UNSIGNED：限制该字段为无符号
ZEROFILL：该字段0填充
AUTO_INCREMENT：该字段自增
DEFAULT ：设置该字段的默认值
NOT NULL：限制该字段不能为NULL
UNIQUE：限制该字段不能重复
PRIMARY KEY：约束该字段既不能为NULL，也不能重复
FOREIGN KEY：约束该字段的值必须来自于主表的关联列的值（外键约束在列级约束处无效果）

常用命令：

1
2
3

SHOW CREATE DATABASE `school`; -- 查看创建数据库的语句
SHOW CREATE TABLE `student`;   -- 查看创建student数据表的语句，使用Navicat生成表后，可以使用该命令查看创建表的语句
DESC `student`;                -- 显示表的结构

复制表：

-- 复制表的结构+数据
CREATE TABLE `student_2`
SELECT * FROM `student`;

-- 只复制表的结构
CREATE TABLE `student_2` LIKE `student`;

3.2 修改表（ALTER）

修改表的结构

-- 修改表名：ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE test RENAME AS teacher1;

-- 增加表的字段：ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teacher1 ADD age INT(11);

-- 修改表的字段（修改字段类型以及约束，字段重命名）
-- ALTER TABLE 表名 MODIFY 字段名 列属性[]
ALTER TABLE teacher1 MODIFY age VARCHAR(11); -- 修改字段类型以及约束
-- ALTER TABLE 表名 CHANGE 旧字段名 新字段名
ALTER TABLE teacher1 CHANGE age age1 INT(1); -- 字段重命名

删除表的结构

1 2	-- 删除表的字段：ALTER TABLE 表名 DROP 字段名 ALTER TABLE teacher1 DROP age1;

注意点：

字段名使用``包裹
注释用-- 或/**/
sql关键字大小写不敏感

3.3 删除表（DROP）

1	DROP TABLE IF EXISTS t_student;

4. 数据库操纵语言（DML）

4.1 添加数据（INSERT）

语法：INSERT INTO 表名(字段名1, 字段名2) VALUES (值11,值12), (值21,值22)

-- 插入一个数据的一个字段时
-- INSERT INTO `表名`(`字段名1`) VALUES ('值1')
INSERT INTO `student`(`name`) VALUES ('zhangsan');

-- 插入一个数据的多个字段时
-- INSERT INTO `表名`(`字段名1`, `字段名2`) VALUES ('值1', '值2')
INSERT INTO `student`(`name`, `pwd`) VALUES ('zhangsan', 'aaaaaa');

-- 插入多个数据的多个字段时
-- INSERT INTO `表名`(`字段名1`, `字段名2`) VALUES ('值11', '值12'), ('值21', '值22')
INSERT INTO `student`(`name`, `pwd`) VALUES ('zhangsan', 'aaaaaa'), ('lisi', 'bbbbbb');

注意事项：

字段和字段使用英文逗号隔开
表后括号内的字段可以省略，但是后面的值必须与字段名一一对应
可以同时插入多条数据，此时VALUES后面的值使用逗号隔开VALUES(), (), ()...

4.2 修改数据（UPDATE）

语法：UPDATE 表名 SET 字段名1=value, [字段名2=value2, …] WHERE [条件]

条件：WHERE子句，运算符

--  修改学员名字，带了条件
UPDATE `student` SET `name`='wangwu' WHERE `id`=1;

-- 不指定条件的情况下，会改动所有的表
UPDATE `student` SET `name`='wangwu';

-- 修改多个属性
UPDATE `student` SET `name`='zhaoliu', `email`='1234567@qq.com' 
WHERE `id`=1;

-- 通过多个条件定位数据，无上限
UPDATE `student` SET `name`='zhaoliu' 
WHERE `name`='wangwu' AND `sex`='女';	

-- 修改多张表的记录
UPDATE `student1` s1 
JOIN `student2` s2
ON s1.`id` = s2.`id`
SET `name`='zhaoliu' 
WHERE `name`='wangwu'

操作符	含义	范围	结果
=	等于	5=6	false
<=>	安全等于	5<=>NULL	false
<> 或 !=	不等于	5<>6	true
>，>=	大于，大于等于	5>6, 5>=6	false
<，<=	小于，小于等于	5<6, 5<=6	true
BETWEEN … AND …	闭区间[]	BETWEEN 2 AND 5	[2, 5]
AND	&&	5>1 AND 1>2	false
OR	\|\|	5>1 OR 1>2	true

注意：

字段名是数据库的列，尽量带上``
筛选的条件如果没有指定，则会修改所有的列
value是一个具体的值，也可以是一个变量（时间变量）
多个设置的属性之间使用英文逗号隔开
安全等于<=>可以判断某字段数值是否等于NULL，而普通等于=无法判断是否为NULL

4.3 删除数据（DELETE）

语法：DELETE FROM 表名 [WHERE 条件]

作用：用于删除小表，删除后可以回滚，删除速度较慢

-- 删除数据 （避免这样写，会全部删除）
DELETE FROM `student`;

-- 删除指定数据
DELETE FROM `student` WHERE `id`=1;

-- 联表删除数据
-- 将userid为Bbiri的user表和my_employees表的记录删除
DELETE u, e 
FROM users u
JOIN my_employees e
ON u.`userid` = r.`Userid`
WHERE u.`userid` = 'Bbiri';

TRUNCATE 命令

作用：用于删除大表，表被截断，不可回滚，永久消失，删除速度较快。完全清空一个数据库表，表的结构和索引约束不会变。

1 2	-- 清空表 TRUNCATE TABLE `student`;

TRUNCATE与DELETE区别

相同点：都能删除数据，都不会删除表结构
不同点：
- TRUNCATE：删除后不可回滚，永久丢失，删除速度较快。重新设置自增列，自增会归零，不会影响事务，其后不能加WHERE过滤
- DELETE：删除后可以回滚，删除速度较慢。不影响自增列，删除后自增不归零

1 2	DELETE FROM `test`; -- 不会影响自增 TRUNCATE TABLE `test`; -- 自增会归零

了解即可： DELETE删除的问题

重启数据库时：

InnoDB，自增列会从1开始（存在内存当中，断电即失）
MyISAM，继续从上一个自增量开始（存在文件中，不会丢失）

5. 数据库查询语言（DQL）

5.1 DQL

DQL：Data Query Language，数据库查询语言

所有的查询操作都使用SELECT
数据库中最核心的语言
使用频率最高

SELECT完整语法：

SELECT[ALL | DISTINCT | DISTINCTROW | TOP]
{* | talbe.* | [table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]} -- 字段
FROM tableexpression[,…]  -- 从哪张表（主表）查询
[LEFT | RIGHT | INNER JOIN externaldatabase]  -- 联合查询
[ON …]          -- 联合查询的等值判断
[WHERE …]       -- 条件过滤：可以是具体的值，也可以是子查询语句
[GROUP BY …]    -- 分组：通过某个字段进行分组
[HAVING …]  	-- 过滤分组后信息：条件和WHERE相似，只是位置必须在GROUP BY之后
[ORDER BY …]	-- 排序：通过某个字段排序
[LIMIT ]        -- 分页：指定查询记录从哪条，显示多少条

SELECT语句执行顺序：

SELECT在HAVING之后执行，因此分组函数不能在WHERE中使用

SELECT            -- 7
FROM              -- 1
[JOIN  …]         -- 2
[ON    …]         -- 3
[WHERE …]         -- 4
[GROUP BY …]      -- 5
[HAVING …]        -- 6
[ORDER BY …]      -- 8
[LIMIT ]          -- 9

5.2 指定查询字段（SELECT）

语法：SELECT 字段，… FROM 表

有时列的名字不是那么见名知意，此时可以其别名，使用AS ：字段名 AS 别名或表名 AS 别名

-- 查询全部学生 SELECT 字段 FROM 表
SELECT * FROM `student`;

-- 查询指定字段
SELECT `StudentNo`, `StudentName` FROM `student`;

-- 别名，给结果起个名字。可以给字段其别名，也可以给表名起别名
SELECT `StudentNo` AS 学号, `StudentName` AS 学生姓名 FROM `student` AS s;

-- 函数 CONCAT(a, b)，将连接后字符串显示
SELECT CONCAT('姓名： ', StudentName) AS 新名字 FROM `student`;

去重 DISTINCT

作用：去除SELECT查询出来的结果中重复的数据，重复数据只显示一条

-- 查询一下有哪些同学参加了考试
SELECT * FROM result;            -- 查询全部的考试成绩
SELECT `StudentNo` FROM result;  -- 查询有哪些同学参加了考试

-- 发现重复数据，去重
SELECT DISTINCT `StudentNo` FROM result;

数据库的列（表达式）

SELECT VERSION();                   -- 查询系统版本（函数）
SELECT 100*3-1 AS 计算结果;          -- 用来计算（表达式）
SELECT @@AUTO_INCREMENT_INCREMENT;  -- 查询自增的步长（变量）

-- 学员考试成绩+1分查看
SELECT `StudentNo`, `StudentResult`+1 AS '提分后' FROM result;

数据库中的表达式：文本值，列，NULL，函数，计算表达式，系统变量…

语法：SELECT 表达式 FROM 表

5.3 条件子句（WHERE）

作用：检索数据中符合条件的值

搜索的条件由一个或多个表达式组成，结果为布尔值

逻辑运算符

运算符	语法	描述
AND &&	a AND b a && b	逻辑与，两个都为真，结果为真
OR \|\|	a OR b a \|\| b	逻辑或，其中一个为真，结果为真
NOT !	NOT a ! a	逻辑非，真变假，假变真

-- && AND 
SELECT `StudentNo`, `StudentResult` FROM result
WHERE `StudentResult`>=95 && `StudentResult`<=100;
WHERE `StudentResult`>=95 AND `StudentResult`<=100;

-- 区间
SELECT `StudentNo`, `StudentResult` FROM result
WHERE `StudentResult` BETWEEN 95 AND 100;

-- != NOT
SELECT `StudentNo`, `StudentResult` FROM result 
WHERE `StudentNo` != 1000;
WHERE NOT `StudentNo` = 1000;

模糊查询：比较运算符

运算符	语法	描述
IS NULL	a IS NULL	如果操作符a为NULL，结果为真
IS NOT NULL	a IS NOT NULL	如果操作符a为NOT NULL，结果为真
BETWEEN	a BETWEEN b and c	若a在b和c之间，结果为真
LIKE	a b	SQL匹配，如果a匹配b，结果为真
IN	a IN (a1, a2, a3…)	假设a在a1，或者a2…其中的某一个值中，则结果为真

-- ======================= 模糊查询 =========================
-- 查询姓张的同学
-- LIKE结合 %(代表任意个字符)  _(表示一个字符)
SELECT `StudentNo`, `StudentName` FROM 	`student` 
WHERE StudentName LIKE '张%';

-- 查询姓张的同学，'张'后只有一个字的
SELECT `StudentNo`, `StudentName` FROM 	`student` 
WHERE StudentName LIKE '张_';

-- 查询姓张的同学，'张'后只有两个字的
SELECT `StudentNo`, `StudentName` FROM 	`student` 
WHERE StudentName LIKE '张__';
-- 若想匹配下划线'_'，则使用转义'\_'的方式才能查询到带有_的数据

-- 查询名字中间有伟的字的同学 %伟%
SELECT `StudentNo`, `StudentName` FROM 	`student` 
WHERE StudentName LIKE '%伟%';

-- ======================= IN =========================
-- 查询1000,1001号学员
SELECT `StudentNo`, `StudentName` FROM 	`student` 
WHERE StudentNo IN (1000, 1001);

-- 查询在北京的学生
SELECT `StudentNo`, `StudentName` FROM 	`student` 
WHERE `Address` IN ('北京朝阳');

-- ======================= NULL/NOT NULL ======================= 
-- 查询地址为空的学生，即等于 NULL 或 ''
SELECT `StudentNo`, `StudentName` FROM 	`student` 
WHERE address = '' OR adress IS NULL;

-- 查询有出生日期的同学，即不为空
SELECT `StudentNo`, `StudentName` FROM 	`student` 
WHERE `BornDate` IS NOT NULL;

注意：使用LIKE时，若想匹配下划线_，则使用转义的方式。

5.4 连接查询（JOIN )

按照年代分类：
- SQL92（旧的语法）
- SQL99（新的语法）
按照表的连接方式分类：
- 内连接（包括等值连接、非等值连接、自连接）
- 外连接（包括左外连接，右外连接）
- 全连接

笛卡尔积现象：当两张表进行连接查询时，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积。（例如第一张表有7条数据，第二张表有8条数据，则不加条件限制时，查询出56条数据）避免笛卡尔积现象的方法：使用ON关键字加条件进行过滤。但避免笛卡尔积现象并不会减少记录的匹配次数，数据库在匹配数据时仍然是搜索了56次，但是只滤过剩下了符合条件的数据

语法：FROM 要查询的表 INNER/RIGHT/LEFT JOIN 要连接的表 ON 交叉条件

七种SQL Joins 文氏图解

MySQL 不支持 FULL OUTER JOIN

INNER JOIN：内连接，又叫等值连接，只返回两个表中连接字段相等的行数据
LEFT JOIN：左连接，从左表中返回所有值，即使右表中没有匹配（没匹配上的数据显示NULL）
RIGHT JOIN：右连接，从右表中返回所有值，即使左表中没有匹配（没匹配上的数据显示NULL）

数据库在通过JOIN连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户，或与下一层的表进行连接。

在使用LEFT JOIN 时，ON和WHERE条件的区别如下：

ON条件是在生成临时表时使用的条件，它不管ON中的条件是否为真，都会返回左边表中的记录。（为真的部分返回数值，为假的部分返回NULL）
WHERE条件是在临时表生成好后，再对临时表进行过滤的条件。

ON用于判断哪个字段在两个表中相等，从而连接两个表，WHERE则是在连接后生成的临时表的基础上进行过滤。

在使用INNER JOIN 时，ON和WHERE条件产生的效果相同。

-- =========================== 连接查询 JOIN ==========================
-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
SELECT * FROM `student`;
SELECT * FROM `result`;
	
/* 思路
1. 分析需求，分析查询的表来自哪些表
2. 确定使用哪种连接查询 7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件：student表中的StudenNo = result表中的 StudentNo
*/

-- INNER JOIN 内连接，取二者共有的行，交集
SELECT s.`StudentNo`, `StudentName`, `SubjectNo`, `StudentResult`
FROM `student` AS s
INNER JOIN result AS r
WHERE s.`StudentNo` = r.`StudentNo`;

-- LEFT JOIN：左连接。以左表为主表，展示左表所有的数据、右表符合ON条件的数据、以及右表不符合条件的数据（这些不符合的数据显示为空）
SELECT s.`StudentNo`, `StudentName`, `SubjectNo`, `StudentResult`
FROM `student` AS s
LEFT JOIN result AS r
ON s.`StudentNo` = r.`StudentNo`;

-- RIGHT JOIN：右连接，以右表为主表，展示右表所有的数据、左表符合ON条件的数据、以及左表不符合条件的数据（这些不符合的数据显示为空）
SELECT s.`StudentNo`, `StudentName`, `SubjectNo`, `StudentResult`
FROM `student` AS s
RIGHT JOIN result AS r
ON s.`StudentNo` = r.`StudentNo`;

-- 查询缺考的同学
-- 增加WHERE，对查询结果进行筛选过滤
SELECT s.`StudentNo`, `StudentName`, `SubjectNo`, `StudentResult`
FROM `student` AS s
LEFT JOIN result AS r
ON s.`StudentNo` = r.`StudentNo`
WHERE `StudentResult` IS NULL;

-- =========================== 多张表连接查询 ===========================
-- 查询参加了考试的同学信息：学号，学生姓名，科目名，分数
SELECT s.`StudentNo`, `StudentName`, `SubjectName`, `StudentResult`
FROM student AS s
RIGHT JOIN result As r
ON r.`StudentNo` = s.`StudentNo`
INNER JOIN `subject` AS sub
ON r.SubjectNo = sub.SubjectName;

-- 格式：
-- SELECT 要查询哪些数据 
-- FROM 要查询的表 
-- XXX JOIN 要连接的表 
-- ON 交叉条件
-- 假设存在一种多张表查询，先查询两张表，然后再查询另一张表

-- FROM a LEFT JOIN b  a为基准
-- FROM a RIGHT JOIN b  b为基准

自连接

自己的表和自己的表连接，核心：一张表拆为两张一样的表即可

原表

categoryid	pid	categoryName
2	1	信息技术
3	1	软件开发
4	3	数据库
5	1	美术设计
6	3	Web开发
7	5	PS技术
8	2	办公信息

拆成父类表和子类表

父类

categoryid	categoryName
2	信息技术
3	软件开发
5	美术设计

子类

pid	categoryid	categoryName
3	4	数据库
2	8	办公信息
3	6	Web开发
5	7	PS技术

操作：查询父类对应的子类关系

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	Web开发
美术设计	PS技术

-- 查询父子信息： 把一张表选定为两张一样的表
SELECT a.`categoryName` AS  '父栏目', b.`categoryNAME` AS '子栏目'
FROM `category` AS a, `category` AS b
WHERE a.`categoryid` = b.`pid`;

5.5 分组和过滤（GROUP BY / HAVING）

语法：GROUP BY … HAVING …

GROUP BY：按照某个字段或者某些字段进行分组，可以进行多个字段分组
HAVING：必须放在GROUP BY后，对分组后的结果进行过滤

GROUP BY语句在WHERE语句之后执行。

若某个语句中没有显式GROUP BY，则编译时会在最后添加缺省的GROUP BY

-- 查询不同课程的平均分，最高分，最低分，平均分大于80分
-- 核心：根据不同的课程分组
SELECT ANY_VALUE(subjectName), AVG(StudentResult) AS 平均分, MAX(StudentResult) AS 最高分, MIN(StudentResult) AS 最低分
FROM result r
INNER JOIN `subject` sub
ON r.`subjectNo` = sub.`subjectNo`
GROUP BY r.SubjectNo -- 通过什么字段分组
HAVING 平均分 > 80;

-- 找出每个部门不同工作岗位的最高薪资
-- 多字段分组
SELECT deptno, job, MAX(sal) 
FROM emp
GROUP BY 
deptno, job;  -- 按照部门和岗位分组，先部门，后岗位，多字段分组

当一条SQL语句有GROUP BY时，只允许SELECT查询参加分组的字段和分组函数。

注意：在SELECT中使用到分组函数或使用GROUP BY关键字时，需要使用ANY_VALUE()修饰普通字段，其会选择被分到同一组的数据里第一条数据的指定列值作为返回数据，即只返回一个值。

因为分组函数或使用GROUP BY关键字在使用时会从多条匹配到的数据中取出某一个作为返回结果（例如MAX()，只取了最大的数据），此时，没有被分组函数修饰的字段将匹配到多条数据，因此必须从中选出某一条作为返回结果，ANY_VALUE()即从中返回了第一条数据。

为提高效率，可先使用WHERE进行筛选，再在其基础上使用GROUP BY分组。若先GROUP BY/HAVING，再WHERE，则效率低于前者。如果能在GROUP BY前使用WHERE过滤，则最好先使用WHERE。

5.6 排序（ORDER BY ）

语法：ORDER BY 字段名 DESC/ASC

升序ASC
降序DESC

注意：当指定多个字段进行排序时，越靠前的字段越起主导地位，后面的字段只会在前面的字段排序相等的情况下才会启用。例如ORDER BY XXX DESC, YYY ASC，先按照XXX进行降序排列，再在其基础上，对XXX相等的数据进行升序排列。

-- ======================== 分页LIMIT 排序ORDER BY =========================
-- 排序：升序ASC，降序DESC
-- ORDER BY 通过哪个字段排序，怎么拍
-- 查询的结果根据成绩降序排序
SELECT s.`StudentNo`, `StudentName`, `SubjectName`, `StudentResult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` AS sub
ON r.`SubjectNo` = sub.`SubjectNo`
ORDER BY StudentResult DESC;

5.7 分页（LIMIT）

语法：LIMIT 查询起始值，pagesize

-- 分页：缓解数据库压力，给人更好的体验
-- 网页应用：当前，总的页数，页面大小
-- LIMIT 0, 5   1~5       
-- LIMIT 6, 5   6~10     
-- 第一页 LIMIT 0, 5
-- 第二页 LIMIT 5, 5
-- 第三页 LIMIT 10, 5
-- 第N页 LIMIT  (N-1)* pageSize, pageSize
-- [PageSize: 页面大小, (N-1)*PageSize：起始值, N: 当前页]

SELECT s.`StudentNo`, `StudentName`, `SubjectName`, `StudentResult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` AS sub
ON r.`SubjectNo` = sub.`SubjectNo`
ORDER BY StudentResult DESC
LIMIT 0,5;

5.8 子查询

目标：WHERE（不使用固定值，而使用计算出来的值）

本质：在WHERE语句中嵌套一个子查询语句

-- 查询数据库结构-1的所有考试结果（学号，科目编号，成绩），降序排列
-- 方式一：使用连接查询
SELECT `StudentNo`, r.`SubjectNo`, `StudentResult`
FROM `result` AS r
INNER JOIN `subject` AS sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE `SubjectName` = '数据库结构-1'
ORDER BY  `StudentResult` DESC;

-- 方式二：使用子查询（由里及外）
SELECT `StudentNo`, r.`SubjectNo`, `StudentResult`
FROM `result` AS r
WHERE `SubjectNo` = ANY(
			SELECT `SubjectNo` 
			FROM `subject` 
			WHERE `SubjectName` = '数据库结构-1'
)
ORDER BY `StudentResult` DESC;

-- 查询数据库结构-1的学生的学号
SELECT `SubjectNo` FROM `subject` WHERE `SubjectName` = '数据库结构-1';

-- 分数不小于80分的学生的学号和姓名
SELECT `StudentNo`, `StudentName`
FROM `student` 
WHERE `StudentNo` = ANY(
			SELECT `StudentNo` 
			FROM `result`
			WHERE `StudentResult` >= 80
);

-- 或
SELECT `StudentNo`, `StudentName`
FROM `student` 
WHERE `StudentNo` IN (
			SELECT `StudentNo` 
			FROM `result`
			WHERE `StudentResult` >= 80
);

https://blog.csdn.net/hehexiaoxia/article/details/54599227

注意：子查询的效率不如多表连接查询（JOIN）。因为MySQL会将子查询中最左边的表作为主表，首先遍历主表，然后再将每条数据传到子查询中与子查询做关联，因此子查询的时间复杂度为两个表的行数相乘 O(N * M)，而多表连接查询的时间复杂度仅为两个表的行数相加。这样就导致了如果主表数据很多，子查询的效率会非常低。

5.9 合并查询结果（UNION）

语法：SELECT XXXXX UNION SELECT XXXXX

作用：用于将两个SELECT语句查询到的结果合并成一张表（行数据拼接起来），可以把两个不相干的表中的数据拼接起来显示（例如要查询的结果来自于多张表，并且这些表之间没有直接的连接关系）。

要求：

前后两个查询结果的列数必须一致
多条查询语句的查询的每一列的类型和顺序最好一致
UNION关键字默认去重，如果使用UNION ALL可以包含重复项

6. MySQL函数

6.1 普通函数

-- 数学运算 
SELECT ABS(-8);       -- 绝对值
SELECT CEILING(9.4);  -- 向上取整
SELECT FLOOR(9.4);    -- 向下取整
SELECT RAND();        -- 随机数
SELECT SIGN(-2);      -- 判断一个数的符号 0返回0，负数返回-1，整数返回1

-- +号运算
SELECT 100 + 90;         -- 两个操作数都为数值型，则做加法运算
SELECT '123' + 90；      -- 其中一方为字符型，试图将字符型数值转换为数值型，
                         -- 如果转换成功，则继续做加法，如果转换失败，则将字符型数值转换成0
SELECT NULL + 10;        -- 只要其中一方为NULL，则结果为NULL

-- 字符串函数
SELECT CHAR_LENGTH('helloworld');          -- 字符串长度
SELECT CONCAT('Hello', ' World');          -- 拼接字符串
SELECT INSERT('hello world', 1, 2, 'He');  -- 从某个位置开始替换某个字符串
SELECT LOWER('HELLOWORLD');                -- 变小写字母
SELECT UPPER('helloworld');                -- 变大写字母
SELECT INSTR('hello world', 'h');          -- 返回第一次出现的字符串的位置，找不到返回0
SELECT REPLACE('hello world', 'world', 'mysql');   -- 替换
SELECT SUBSTR('hello world', 4, 6);        -- 返回指定的子字符串（源字符串，截取的位置，截取的长度）从第4个字符开始取，取6个字符长度
SELECT REVERSE('hello world');             -- 反转
SELECT TRIM('   HELLO   ');                -- 将字符串前后的空格去掉
SELECT TRIM('a' FROM 'aaaHELLOaaa');       -- 将字符串前后的指定字符'a'去掉

SELECT REPLACE(studentName, '周', '邹') FROM student
WHERE studentName LIKE '周%';

-- 时间和日期函数（记住）
SELECT CURRENT_DATE();  -- 获取当前时间
SELECT CURDATE();       -- 获取当前日期
SELECT NOW();           -- 获取当前时间
SELECT LOCALTIME();     -- 本地时间
SELECT SYSDATE();       -- 本地时间

SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());

-- 系统
SELECT SYSTEM_USER();
SELECT USER();
SELECT VERSION();

-- 空处理函数，将xxx为NULL的值设置为0
IFNULL(xxx, 0)

使用 SELECT CONCAT(field1, ": ", field2) FROM table 可以将表中的两个字段拼接出来返回一个 String 对象

6.2 分组函数

分组函数都是对某一组数据进行操作的。其也被称为聚合函数、多行处理函数：输入多行，最终输出一行结果。分组函数在GROUP BY之后使用，用于统计分组后同一组内数据的最大/小值、平均值和总数。

分组函数自动忽略NULL，无需添加WHERE条件语句判断数据是否为NULL
分组函数不能直接使用在WHERE子句当中，因为WHERE语句在GROUP BY语句之前执行，而分组函数必须在分完组才能执行（即必须在GROUP BY语句之后执行）
分组函数一般都会和GROUP BY联合使用，其在GROUP BY语句执行后才执行

例如：SELECT ename, sal FROM emp WHERE sal > AVG(sal);有语法错误，因为分组函数不能在WHERE子句中使用。修改办法：使用子查询语句SELECT ename, sal FROM emp WHERE sal > (SELECT AVG(sal) FROM emp);

函数名称	描述
COUNT()	计数
SUM()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值

SELECT COUNT(`StudentName`) FROM student;  -- COUNT(字段)，指定查询某列，会忽略所有的NULL值
SELECT COUNT(*) FROM student;  -- COUNT(*)，不会忽略NULL值
SELECT COUNT(1) FROM result;   -- COUNT(1)，不会忽略NULL值，效果同COUNT(*)，本质是在表中加了一列全是1的数据，然后统计1的个数，本质还是COUNT(*)
			
SELECT SUM(`studentResult`) AS 总分 FROM result;
SELECT AVG(`studentResult`) AS 平均分 FROM result;
SELECT MAX(`studentResult`) AS 最高分 FROM result;
SELECT MIN(`studentResult`) AS 最低分 FROM result;

count(*)、count(1)、count(某索引) 和 count(某普通字段) 的区别：

count(*)：和字段内容没关系，会查询所有包含NULL在内的行数（统计总记录条数）。底层默认是通过主键索引进行快速遍历所有页结构统计总数的，大多数情况下性能最高（如果表中不止一个索引，则会自动选择一个占用空间最小的二级索引来进行统计：因为占用空间小意味着加载的页就少，磁盘 IO 就少，性能显然更高）
count(1)：结果和 count(*) 相同，也是统计总记录条数。底层也是默认通过主键索引进行快速遍历所有页结构统计总数的，性能和 count(*) 相近（也会自动选择占用空间最小的二级索引）
count(某索引)：性能略低于前面二者（多了一步返回索引值），也是统计总记录条数。底层也是通过快速遍历索引树统计总数的。
count(某普通字段)：和字段内容有关系，会查询除了NULL值以外的行数（忽略NULL值），前面三者因为都是遍历索引，而索引不可能为空，因此会把整个表的所有记录给遍历到（即使有些记录是包含NULL的，但是因为索引不为NULL，所以统计出的结果就是全部数据）。因为普通字段没有索引，所以会进行全表扫描，性能低于前面三者

只要走索引进行遍历统计，肯定会统计出总记录数（这些记录中可能某个字段为NULL）。但是如果不走索引，全表扫描普通字段，则就会排除掉该字段为NULL的记录。

但是，当表中除了主键索引外还有其他普通二级索引，那么MySQL优化器就会使得 count(*) 和 count(1)都不走主键索引，而是走占用空间最小（key_len 最小）的二级索引。这是因为普通二级索引所在的行结构大小较小（主键索引所在的行结构会把所有列信息保存到树中），同一个页结构中可以存储更多的行数据，因此遍历速度更快一些（因为与磁盘 IO 变少了）。

以上分析都是对于 InnoDB 存储引擎而言。若是 MyISAM，则因为其采用的是表级锁，因此可以维护一个 meta 信息，保存当前表中的行记录数 row_count，数据一致性由表级锁来保证。因此其查询时间复杂度为 O(1)。而 InnoDB 因为需要支持事务，采用行级锁和 MVCC 机制，所以无法维护一个 row_count（可能并发情况下数据不一致），因此每次必须全表扫描。

6.3 数据库级别的MD5加密（扩展）

MD5：Message-Digest Algorithm，信息摘要算法。

MD5主要增强算法复杂度和不可逆性。MD5破解网站的原理：背后有一个字典，一一查询匹配MD5加密后的值与加密前的值，看能否找到匹配。

-- ====================== 测试MD5 加密 ======================
CREATE TABLE `testmd5`(
	`id` INT(4) NOT NULL,
	`name` VARCHAR(20) NOT NULL,
	`pwd` VARCHAR(50) NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 明文密码
INSERT INTO `testmd5` VALUES(1, 'zhangsan', '123456'), (2, 'lisi', '123456'), (3, 'wangwu', '123456');

-- 加密
UPDATE `testmd5` SET pwd=MD5(pwd) WHERE id=1;
UPDATE `testmd5` SET pwd=MD5(pwd);

-- 插入数据时加密
INSERT INTO `testmd5` VALUES(4, 'zhaoliu', MD5('123456'));

-- 如何校验：将用户传递进来的密码进行md5加密，然后对比加密后的值
SELECT * FROM testmd5 WHERE `name`='zhaoliu' AND pwd=(MD5('123456'));

7. 事务

7.1 什么是事务

TCL: Transaction Control Language 事务控制语言。

事务（Transaction）：事务由单独单元的一个或一组sql语句组成，在这个单元中，每个MySQL语句是相互依赖的，这个执行单元 要么全部执行，要么全部不执行

SQL语句1正在执行：A给B转账200 A：1000 ——> 200 B：200
SQL语句2正在执行：B收到A的钱 B：800 ——> B：400

将一组SQL放在一个批次中去执行。上述两条语句组成一组来执行，要么都成功，要么都失败，否则钱会凭空消失。

7.2 事务原则

事务原则：ACID原则——原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)；脏读，幻读，不可重复读

1. 原子性（Atomicity）

原子性是指事务是一个不可分割的工作单位，事务中的操作要么都执行，要么都不执行，没有中间状态。对于事务在执行中发生错误，所有的操作都会被回滚，整个事务就像从没被执行过一样。

2. 一致性（Consistency）

事务必须使数据库从一个一致性状态变换到另一个一致性状态。保证数据库一致性是指当事务完成时，必须使所有数据都具有一致的状态。（例如A给B转账前后，数据库中二者余额之和相等，转账前为一个一致性状态，转账后也为一个一致性状态）。

3. 隔离性（Isolation）

隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他数据是隔离的，并发执行的各个事务之间不能互相干扰。

4. 持久性（Durability）

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

其中，原子性和持久性靠undo和redo日志来实现，隔离性通过线程的控制来实现

7.3 事务隔离级别

参考链接：https://cloud.tencent.com/developer/article/1450773

对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题：

1. 脏读（读取未提交数据）：

指一个事务读取了另一个事务未提交的数据。例如，两个事务T1和T2，T1读取了已经被T2更新但是还没有被提交的字段，之后，若T2回滚，T1读取到的内容就是临时且无效的。

2. 不可重复读（前后多次读取，数据内容不一致）：

指在一个事务内读取表中的某一行数据，过段时间，该字段数据被另一事务修改，此时第一个事务再读时读取结果不同。例如，两个事务T1和T2，T1读取了一个字段，然后T2更新了该字段，之后，T1再此读取同一字段时，值就不同了。

3. 幻读（前后多次读取，数据总量不一致）：

事务A在执行读取操作，需要两次统计数据的总量，前一次查询数据总量后，此时事务B执行了新增数据的操作并提交后，这个时候事务A读取的数据总量和之前统计的不一样，就像产生了幻觉一样，平白无故的多了几条数据。（类似班级里刚才看还是一个人，再看变成两个人，就像产生了幻觉）

不可重复读和幻读的区别:

(1) 不可重复读是读取了其他事务更改的数据，针对UPDATE操作

解决：使用行级锁，锁定该行，事务A多次读取操作完成后才释放该锁，这个时候才允许其他事务更改刚才的数据。

(2) 幻读是读取了其他事务新增的数据，针对INSERT和DELETE操作

解决：使用表级锁，锁定整张表，事务A多次读取数据总量之后才释放该锁，这个时候才允许其他事务新增数据。

MySQL的JDBC中，默认开启事务，此时每一句sql语句都会在一个单独的事务中执行，例如两次查询语句都会在不同的事务中执行，执行完该语句都会立刻提交。

MySQL支持4种事务隔离级别，默认级别为 REPEATABLE READ

READ UNCOMMITTED（读未提交数据）：允许事务读取未被其他事务提交的变更。脏读、不可重复读和幻读都可能出现
READ COMMITTED（读已提交数据）：只允许事务读取已经被其他事务提交的变更。可以避免脏读，但不可重复读和幻读仍然可能出现
REPEATABLE READ（可重复读）：确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对这个字段进行更新。此时即使其他事务修改某字段并COMMIT，本事务查询时仍是原先值。可以避免脏读和不可重复读，但幻读仍然可能出现，即其他事务若插入了新的行，本事务查询时也会多出这些行，导致看起来像幻觉一样，每次读取的数据总量不同
SERIALIZABLE（串行化）：确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表执行插入，更新和删除操作。所有并发问题都可以避免，但性能十分低下。在某个事务读取时，其他事务阻塞，无法对该表进行操作。该级别下无法进行并发

查看隔离级别：

1 2	SELECT @@TX_ISOLATION --（8.0以前） SELECT @@TRANSACTION_ISOLATION --（8.0以后）

设置当前MySQL连接的隔离级别：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED

设置全局MySQL连接的隔离级别：

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED

7.4 undo 和 redo 日志

http://www.zhdba.com/mysqlops/2012/04/06/innodb-log1/

在数据库系统中，既有存放数据的文件，也有存放日志的文件。在内存中既有日志缓存 log buffer，也有磁盘文件 log file。MySQL中的日志文件，有这么两种与事务有关：undo日志与redo日志。

7.4.1 undo 日志

https://www.bilibili.com/video/BV1n7411R7WQ?p=6&spm_id_from=333.1007.top_right_bar_window_history.content.click

undo日志的原理：为了满足事务的原子性，在操作任何数据之前，首先将数据备份到 undo log。然后进行数据的修改。如果出现了错误或者用户执行了 ROLLBACK 语句，系统可以利用 undo log 中的备份将数据恢复到事务开始之前的状态。并且数据库写入数据到磁盘之前，会把数据先缓存在内存中，事务提交时才会写入磁盘中。

用undo日志实现原子性的简化过程

假设有A、B两个数据，值分别为1,2。

A. 事务开始.
B. 记录A=1到 undo log.
C. 修改A=3.
D. 记录B=2到 undo log.
E. 修改B=4.
F. 将 undo log 写到磁盘。
G. 将数据写到磁盘。
H. 事务提交

1. 如何保证持久性？

事务提交前，会把修改后的数据保存到磁盘，也就是说只要事务提交了，数据肯定持久化了。

2. 如何保证原子性？

每次对数据库修改，都会把修改前数据记录在 undo log，那么需要回滚时，可以读取 undo log，恢复数据。
若系统在G和H之间崩溃，此时事务并未提交，需要回滚。而 undo log 已经被持久化，可以根据 undo log 来恢复数据
若系统在G之前崩溃，此时数据并未持久化到硬盘，依然保持在事务之前的状态

缺陷：每个事务提交前将数据和 undo log 写入磁盘，这样会导致大量的磁盘IO，因此性能很低。

如果能够将数据缓存一段时间，就能减少IO提高性能。但是这样就会丧失事务的持久性。因此引入了另外一种机制来实现持久化，即 redo log。

7.4.2 redo日志

和 undo log 相反，redo log 记录的是新数据的备份。在事务提交前，只要将 redo log 持久化即可，不需要将数据库中的数据持久化，减少了IO的次数。

undo + redo 事务的简化过程

假设有A、B两个数据，值分别为1,2

A. 事务开始.
B. 记录 A=1 到 undo log buffer.
C. 修改 A=3.
D. 记录 A=3 到 redo log buffer.
E. 记录 B=2 到 undo log buffer
F. 修改 B=4.
G. 记录 B=4 到 redo log buffer.
H. 将 undo log 写入磁盘
I. 将 redo log 写入磁盘
J. 事务提交

1. 如何保证原子性？

如果在事务提交前故障，通过 undo log 日志恢复数据。如果 undo log 都还没写入，那么数据就尚未持久化，无需回滚

2. 如何保证持久化？

注意，这里并没有出现数据的持久化。因为数据已经写入 redo log，而 redo log 持久化到了硬盘，因此只要到了步骤I以后，事务是可以提交的。

3. 内存中的数据库数据何时持久化到磁盘？

因为 redo log 已经持久化，因此数据库数据写入磁盘与否影响不大，不过为了避免出现脏数据（内存中与磁盘不一致），事务提交后也会将内存数据刷入磁盘（也可以按照固设定的频率刷新内存数据到磁盘中）。

4. redo log 何时写入磁盘？

redo log 会在事务提交之前，或者 redo log buffer满了的时候写入磁盘

这里存在两个问题：

问题1：之前是写 undo 和数据库数据到硬盘，现在是写 undo 和 redo 到磁盘，似乎没有减少IO次数

数据库数据写入是随机IO，性能很差
redo log 在初始化时会开辟一段连续的空间，写入是顺序IO，性能很好
实际上 undo log 并不是直接写入磁盘，而是先写入到 redo log buffer 中，当 redo log 持久化时，undo log 就同时持久化到硬盘了。

因此事务提交前，只需要对 redo log 持久化即可。另外，redo log 并不是写入一次就持久化一次， redo log 在内存中也有自己的缓冲池：redo log buffer。每次写 redo log 都是写入到 buffer，在提交时一次性持久化到磁盘，减少IO次数。

问题2：redo log 数据是写入内存buffer中，当buffer满或者事务提交时，将 buffer 数据写入磁盘。redo log 中记录的数据，有可能包含尚未提交事务，如果此时数据库崩溃，那么如何完成数据恢复？

数据恢复有两种策略：

恢复时，只重做已经提交了的事务
恢复时，重做所有事务包括未提交的事务和回滚了的事务。然后通过 undo log 回滚那些未提交的事务

Innodb 引擎采用的是第二种方案，因此 undo log 要在 redo log 前持久化

总结：

undo log 记录更新前的数据，用于保证事务原子性
redo log 记录更新后的数据，用于保证事务的持久性
redo log 有自己的内存buffer，先写入到 buffer，事务提交时写入磁盘
redo log 持久化之后，意味着事务是可提交的

7.5 悲观锁和乐观锁

参考链接：https://cloud.tencent.com/developer/article/1450773

悲观锁

正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。

悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。

在悲观锁的情况下，为了保证事务的隔离性，就需要一致性锁定读。读取数据时给加锁，其它事务无法修改这些数据。修改删除数据时也要加锁，其它事务无法读取这些数据。

乐观锁

相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。

而乐观锁机制在一定程度上解决了这个问题。乐观锁，大多是基于数据版本（ Version ）记录机制实现。

何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。

此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

7.6 执行事务顺序

关闭自动提交：SET autocommit = 0
开启一个事务：START TRANSACTION
- 提交（执行成功）：COMMIT ，获得新的数据库
- 回滚（执行失败）：ROLLBACK，返回原先数据库
开启自动提交：SET autocommit = 1

-- MySQL是默认开启事务自动提交的
SET autocommit = 0;   -- 关闭
SET autocommit = 1;	  -- 开启（默认）

-- 手动处理事务
SET autocommit = 0;   -- 关闭自动提交

-- 事务开启
START TRANSACTION;    -- 标记一个事务的开始，从这个之后的sql都在同一个事务内

INSERT xx;
INSERT xx;

-- 提交：持久化（成功）
COMMIT;

-- 回滚：回到原来的样子（失败）
ROLLBACK;

-- 事务结束
SET autocommit = 1; -- 开启自动提交

SAVEPOINT 保存点名称a;              -- 设置一个事务的保存点
ROLLBACK TO 保存点名称a;            -- 回滚到保存点 
RELEASE SAVEPOINT 保存点名称;       -- 撤销保存点

测试案例：

-- 模拟转账
SET autocommit = 0;  -- 关闭自动提交

START TRANSACTION;    -- 开启一个事务

UPDATE `account` SET `money`=`money` - 500 WHERE `name` = 'zhangsan';
UPDATE `account` SET `money`=`money` + 500 WHERE `name` = 'lisi';

COMMIT;    -- 提交事务，执行后数据库内容才会修改
ROLLBACK;  -- 回滚，数据库内容不会修改

SET autocommit = 1;  -- 恢复默认值

7.7 DELETE 和 TRUNCATE 在事务中的区别

DELETE在事务提交前使用，若回滚，则数据会恢复
TRUNCATE事务提交前使用，若回滚，则数据依旧不会恢复

8. 视图

视图（VIEW）：一种虚拟存在的表。站在不同的角度去看待同一份数据

视图是一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存了SQL逻辑，不保存查询结果。视图使用时可以像操纵表一样操纵表中的数据。

8.1 操作视图

创建视图

语法：CREATE VIEW 视图名 AS DQL语句（SELECT … FROM …)

1
2
3

CREATE VIEW emp_view 
AS 
SELECT * FROM emp; -- CREATE后只能是DQL语句

删除视图

语法：DROP VIEW 视图名

1	DROP VIEW emp_view;

修改视图

语法：ALTER VIEW 视图名

CREATE OR REPLACE VIEW emp_view  -- 如果存在则替换
AS 
SELECT * FROM emp; 

ALTER VIEW emp_view
AS
SELECT * FROM emp;

更新视图

语法同操作表时一模一样

1	UPDATE emp_view SET sal = 1000 WHERE dname = 'ACCOUNTING';

视图的更新性和视图中查询的定义有关系，以下类型的视图是不能更新的：

包含以下关键字的SQL语句：分组函数、DISTINCT、GROUP BY、HAVING、UNION或UNION ALL
常量视图
SELECT中包含子查询
JOIN
FROM一个不能更新的视图
WHERE子句的子查询引用了FROM子句中的表

8.2 视图的作用

视图的特点：通过对视图的操作会影响到原表数据。可以面向视图对象进行增删改查，对视图对象的增删改查将会导致原表被操作。

视图的作用：假设有一条非常复杂的SQL语句，而这条SQL语句需要在不同的位置上反复使用。每次使用这条SQL语句的时候都需要重新编写（很麻烦），这时可以把这条复杂的SQL语句以视图对象的形式新建。在需要编写这条SQL语句的位置直接使用视图对象，可以大大简化开发，并且有利于后期的维护，因为修改时只需要修改一个视图对象所映射的SQL语句。

8.3 视图和表的区别

	创建语法	是否占用实际物理空间	使用
视图	`CREATE VIEW`	只是保存了SQL逻辑	主要用来查询
表	`CREATE TABLE`	保存了实际数据	增删改查

9. 变量

MySQL中变量类型：

系统变量：
- 全局变量：针对数据库全局有效。服务器每次启动将为所有的全局变量赋初值，针对所有的会话（连接）有效，但是不能跨重启（数据库重启后之前设置的值会恢复）
- 会话变量：仅针对某次会话（连接）有效
自定义变量：
- 用户变量：在当前会话中有效，同与会话变量的作用域
- 局部变量：仅仅在定义它的BEGIN END中有效

	作用域	定义和使用的地方	语法
用户变量	当前会话	会话中的任何地方	`SET @a = 0;`必须加@符号，不用限定类型
局部变量	`BEGIN END`中	`BEGIN END`中，且为第一句话	`DECLARE a INT DEFAULT 0;`一般不用加@符号，需要限定类型

10. 存储过程和函数

存储过程：一组预编译好的SQL语句的集合，可以理解成批处理语句（类似于Java中的函数）。

提高代码的重用性
简化操作
减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

10.1 存储过程的语法

创建存储过程

CREATE PROCEDURE 存储过程名（参数列表）
BEGIN
	存储过程体（一组合法的SQL语句）
END

参数列表包含三部分：参数模式、参数名和参数类型。其中参数模式包含：

IN：该参数可以作为输入，也就是该参数需要调用方传入值
OUT ：该参数可以作为输出，也就是该参数可以作为返回值
INOUT：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

如果存储过程体仅仅只有一句话，则BEGIN END可以省略。存储过程体中的每句SQL语句的结尾要求必须加分号。存储过程的结尾可以使用DELIMITER重新设置。

调用存储方法

1	CALL 存储过程名（实参列表）；

删除存储过程

1	DROP PROCEDURE 存储过程名;

查看存储过程的信息

1	SHOW CREATE PROCEDURE 存储过程名;

10.2 创建和调用存储过程

空参列表

SELECT * FROM `admin`;

DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
	INSERT INTO `admin`(`username`, `password`) 
	VALUES('zhangsan', '123456');
END $

-- 调用
CALL myp1() $

带IN模式参数的存储过程

CREATE PROCEDURE myp2(IN `name` VARCHAR(20))
BEGIN
	SELECT `userid`
	FROM `student`
	WHERE `username` = `name`;
END $

-- 调用
CALL myp2('zhangsan')$

CREATE PROCEDURE myp3(IN `name` VARCHAR(20), IN `password` VARCHAR(20))
BEGIN
	DECLARE `result` INT DEFAULT 0; -- 声明局部变量并初始化
    
	SELECT COUNT(*) INTO result     -- 将查询结果赋值给变量
	FROM `admin`
	WHERE `admin`.`username` = `name`
	AND `admin`.`password` = `password`;
	
	SELECT IF(`result` > 0, '成功', '失败')  -- 变量使用
END $

-- 调用
CALL myp3('zhangsan', '123456')$

带OUT模式的存储过程

CREATE PROCEDURE myp4(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20))
BEGIN
	SELECT bo.boyName INTO boyName
	FROM boys bo
	INNER JOIN beauty b 
	ON bo.id = b.boyfriend_id
	WHERE b.name = beautyName;
END $

-- 创建用户变量并调用存储过程
SET @bName $
CALL myp5('zhangsan', @bName)$

带INOUT模式的存储过程

CREATE PROCEDURE myp5(INOUT a INT, INOUT b INT)
BEGIN
	SET a = a * 2;
	SET b = b * 2;
END $

SET @m = 10$
SET @n = 20$
CALL myp5(@m, @n)$

10.3 函数的语法

存储过程和函数的区别：

存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
函数：有且只有一个返回，适合做处理数据后返回一个结果

创建函数

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
	函数体
END

参数列表包含：参数名、参数类型。
函数体：肯定会有RETURN语句。如果RETURN语句没有放在函数体的最后也不会报错，但不建议
当函数体中只有一句话，可以省略BEGIN END
使用DELIMITER语句设置结束标记

调用函数

1	SELECT 函数名(列表参数)

查看函数

1	SHOW CREATE FUNCTION myf1;

删除函数

1	DROP FUNCTION myf1;

10.4 创建和调用函数

无参数，有返回

CREATE FUNCTION myf1() RETURNS INT
BEGIN
	DECLARE c INT DEFAULT 0;  -- 定义局部变量
	
	SELECT COUNT(*) INTO c    -- 给c赋值
	FROM employees;
	
	RETURN c;                 -- 返回值
END $

SELECT myf1()$

有参数，有返回

CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	SET @sal = 0;             -- 定义用户变量
	
	SELECT salary INTO @sal   -- 赋值
	FROM employees
	WHERE last_name = empName;
	
	RETURN @sal;
END $

SELECT myf2('king') $

10.5 应用场景：批量插入数据脚本

环境准备

1、建表SQL。

/* 1.dept表 */
CREATE TABLE `dept` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `deptno` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '部门id',
  `dname` varchar(20) NOT NULL DEFAULT '' COMMENT '部门名字',
  `loc` varchar(13) NOT NULL DEFAULT '' COMMENT '部门地址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='部门表'

/* 2.emp表 */
CREATE TABLE `emp` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `empno` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '员工编号',
  `ename` varchar(20) NOT NULL DEFAULT '' COMMENT '员工名字',
  `job` varchar(9) NOT NULL DEFAULT '' COMMENT '职位',
  `mgr` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '上级编号',
  `hiredata` date NOT NULL COMMENT '入职时间',
  `sal` decimal(7,2) NOT NULL COMMENT '薪水',
  `comm` decimal(7,2) NOT NULL COMMENT '分红',
  `deptno` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '部门id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工表'

2、由于开启过慢查询日志，开启了bin-log，我们就必须为function指定一个参数，否则使用函数会报错。

# 在mysql中设置 
# log_bin_trust_function_creators 默认是关闭的 需要手动开启
mysql> SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
1 row in set (0.00 sec)

mysql> SET GLOBAL log_bin_trust_function_creators=1;
Query OK, 0 rows affected (0.00 sec)

上述修改方式MySQL重启后会失败，在my.cnf配置文件下修改永久有效。

1 2	[mysqld] log_bin_trust_function_creators=ON

创建函数

# 1、函数：随机产生字符串
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
    DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwsyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    DECLARE return_str VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
    SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
    SET i = i + 1;
    END WHILE;
    RETURN return_str;
END $$

# 2、函数：随机产生部门编号
DELIMITER $$
CREATE FUNCTION rand_num() RETURNS INT(5)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET i = FLOOR(100 + RAND() * 10);
    RETURN i;
END $$

创建存储过程

# 1、函数：向dept表批量插入
DELIMITER $$
CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
    SET i = i + 1;
    INSERT INTO dept(deptno,dname,loc) VALUES((START + i),rand_string(10),rand_string(8));
    UNTIL i = max_num
    END REPEAT;
    COMMIT;
END $$

# 2、函数：向emp表批量插入
DELIMITER $$
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
    SET i = i + 1;
    INSERT INTO emp(empno,ename,job,mgr,hiredata,sal,comm,deptno) VALUES((START + i),rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
    UNTIL i = max_num
    END REPEAT;
    COMMIT;
END $$

调用存储过程

# 1、调用存储过程向dept表插入10个部门。
DELIMITER ;
CALL insert_dept(100,10);

# 2、调用存储过程向emp表插入50万条数据。
DELIMITER ;
CALL insert_emp(100001,500000);

11. 权限管理和备份

11.1 权限管理

SQL命令

用户表：mysql.user（存储用户信息）

本质：修改权限，添加用户等操作本质上是对这张表进行增删改查

-- 创建用户： CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER zhangsan IDENTIFIED BY '123456';

-- 修改密码（修改当前用户密码）
SET PASSWORD = PASSWORD('111111');

-- 修改密码（修改指定用户密码）
SET PASSWORD FOR zhangsan = PASSWORD('111111');

-- 重命名： RENAME USER 原名 TO 新名
RENAME USER zhangsan TO zhangsanfeng;

-- 用户授权： ALL PRIVILEGES 全部权限 TO 库.表
-- ALL PRIVILEGES 除了给别人授权，其他都权限都有
GRANT ALL PRIVILEGES ON *.* TO zhangsan;

-- 查询权限
SHOW GRANTS FOR zhangsan -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost

-- 撤销权限： REVOKE 哪些权限 ON 在哪个库 FROM 给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM zhangsan;

-- 删除用户
DROP USER zhangsan;

11.2 MySQL备份

为什么要备份：

保证重要的数据不丢失
数据转移

MySQL数据库备份的方式：

直接拷贝物理文件，data目录
在Navicat工具中手动导出
使用命令行：mysqldump

# =================================  导出 ===============================
# mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 schoool student > D:/student.sql

# mysqldump -h 主机 -u 用户名 -p 密码 数据库 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 schoool > D:/school.sql

# =================================  导入 ===============================
# 在登入的情况下，切换到指定数据库
mysql -uroot -p123456
mysql> USE school;
mysql> source D:/student.sql # 或 source D:/school.sql

12. 数据库设计的三大范式

12.1 为什么需要设计

当数据库交复杂时，就需要设计

糟糕的数据库设计：

数据冗余，浪费空间
数据库插入和删除都会麻烦/异常（屏蔽使用物理外键）
程序的性能差

良好的数据库设计：

节省内存空间
保证数据库的完整性
方便开发系统

软件开发中，关于数据库的设计：

分析需求，分析业务和需要处理的数据库需求
概要设计：设计关系图 E-R图

设计数据库的步骤：（个人博客）

收集信息，分析需求
- 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
- 分类表（文章分类，谁创建的）
- 文章表（文章的信息）
- 评论表（评论的信息）
- 友链表（友链信息）
- 自定义表（系统信息，某个关键的字，或者一些主字段）表中两列：key | value
标识实体（把需求落地到每个字段）
标识实体之间的关系
- 写博客：user --> blog
- 创建分类：user --> category
- 关注：user --> user
- 友链：links
- 评论：user --> user --> blog

12.2 三大范式

三大范式：设计表的依据，按照这个三范式设计的表不会出现数据冗余 https://www.bilibili.com/video/BV1fx411X7BD?p=74

1. 第一范式（1NF）：原子性

任何一张表都应该有主键，并且每一个字段原子性不可再分，即每一列的信息不可再分

2. 第二范式（2NF）：不可部分依赖

前提：必须满足第一范式。所有非主键字段完全依赖主键，不能产生部分依赖。即每一列都只和主键相关，而不能只与主键的一部分相关（主要针对联合主键而言），例如，某表中有联合主键，某些非主键字段只依赖联合主键中的其中一个，不依赖于另一个，就产生了冗余

多对多，三张表，关系表两个外键。若遇到不符合第二范式的表时，将一张多对多关系的表拆成三张表：

两个表分别存储独立的信息，每个表有一个主键
一张关系表，关系表里有两个外键，分别引用两个独立表

3. 第三范式（3NF）：不可传递依赖

前提：必须满足第一范式和第二范式。所有非主键字段直接依赖主键，不能产生传递依赖。

一对多，两张表，多的表加外键。若遇到不符合第二范式的表时，将一张一对多关系的表拆成两张表：

一张内容少的表，存储会产生传递依赖的信息
另一张内容多的表加外键，引用第一张表

规范性和性能的问题：在实际的开发中，以满足客户的需求位置，有时候会拿冗余换执行速度

考虑商业化的需求和目标，数据库的性能更加重要
在规范性能问题的时候，需要适当考虑一下规范性
故意给某些表增加一些冗余的字段（从多表查询边单表查询）
故意增加一些计算列（从大数据量降低为小数据量的查询：索引）