MySQL数据库（详解）

最新推荐文章于 2022-09-04 16:22:01 发布

辰伏

最新推荐文章于 2022-09-04 16:22:01 发布

阅读量1.7k

点赞数

分类专栏：数据库文章标签：数据库 sql mysql

本文链接：https://blog.csdn.net/m0_52627845/article/details/112293091

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

1. MySQL基础

1.1 数据库概念

学习数据库是为了更方便的对开发中的数据进行管理
用于存储和管理数据的仓库
英文单词为DataBase，简称DB
它的存储空间很大，可以存放百万条、千万条、上亿条数据
使用一种统一的方式操作数据库——SQL

1.2 MySQL介绍

MySQL是一个最流行的关系型数据库管理系统之一，由瑞典MySQL AB公司开发，后被Oracle公司收购
关系型数据库是将数据保存在不同的数据表中，而不是将所有数据放在一个大仓库内，而且表与表之间还可以有关联关系，这样就提高了访问速度以及提高了灵活性
- 常见的关系型数据库软件
  - sqlserver
  - mysql（6版本之前免费）
  - oracle（收费）
  - postqresql（免费）
MySQL所使用的SQL语句是用于访问数据库最常用的标准化语言

1.3 数据库、数据表和数据的关系

MySQL服务器中可以有多个数据库
一个数据库可以有多个数据表
一个数据表可以有多个数据
客户端–>数据库管理系统–>数据库–>数据表–>数据

1.4 SQL的介绍

SQL(Structured Query Language)：结构化查询语言，其实就是定义了操作所有关系型数据库的一种规则
通用语法规则
- SQL语句可以单行或多行书写，以分号结尾
- 可使用空格和缩进来增强语句的可读性
- MySQL数据库的SQL语句不区分大小写，关键字建议使用大写
- 单行注释：-- 注释内容 #注释内容
SQL分类
- DDL(Data Definition Language)：数据定义语言，操作数据库、表、列等
  - create alter drop
- DML(Data Manipulation Language)：数据操作语言，对数据进行增删改操作
  - insert update delete
- DQL(Data Query Language)：数据查询语言，对来查询数据库中表的记录（数据）
  - select from where group by having order by limit
- DCL(Data Control Language ):数据控制语言，用来定义数据库的访问权限和安全级别，及创建用户

2. DDL操作数据库和数据表

2.1 查询和创建数据库

查询所有数据库
- SHOW DATABASES;
查询数据库的创建语句
- SHOW CREATE DATABASE 数据库名称;
创建数据库
- CREATE DATABASE 数据库名称;
创建数据库（判断，如果不存在则创建）
- CREATE DATABASE IF NOT EXISTS 数据库名称;
创建数据库（指定字符集）
- CREATE DATABASE 数据库名称 CHARACTER SET 字符集名称;

2.2 数据库的增删改

修改数据库（修改字符集）
- ALTER DATABASE 数据库名称 CHARACTER SET 字符集名称;
删除数据库
- DROP DATABASE 数据库名称;
删除数据库（判断，如果存在则删除）
- DROP DATABASE IF EXISTS 数据库名称;
使用数据库
- USE 数据库名称

2.3 查询数据表

查询所有的数据表
- SHOW TABLES;
查询表结构
- DESC 表名;
查询表字符集
- SHOW TABLE STATUS FROM 库名 LIKE '表名';

2.4 创建数据表

创建数据表

  create table 表名(
  	字段名 数据类型（约束），
  	字段名 数据类型（约束），
  	，，，
  	字段名n 数据类型（约束）
  );

常用数据类型
- int：整数类型
- double：小数类型，在使用的时候可以指定小数的位数，也可以不指定
- date：日期类型，包含年月日，格式yyyy-MM-dd
- datetime：日期类型，包含年月日时分秒，格式yyyy-MM-dd HH:mm:ss
- timestamp：时间戳，包含年月日时分秒，格式yyyy-MM-dd HH:mm:ss，如果不给该列赋值或者赋值为null，则默认使用当前系统时间自动赋值
- varchar：字符串，在使用的时候需要指定字符的个数
datetime和timestamp的区别
- 添加数据的时候，如果不给定数据，datetime对应的字段是没有值的，而timestamp默认是当前时间
注意：除了数字类型的数据，其他数据都需要加引号

2.5 修改数据表

修改表名
- ALTER TABLE 表名 RENAME TO 新表名;
修改表的字符集
- ALTER TABLE 表名 CHARACTER SET 字符集名称;
单独添加一列
- ALTER TABLE 表名 ADD 列名数据类型（约束）;
修改某一列的数据类型
- ALTER TABLE 表名 MODIFY 列名新数据类型;
修改列名和数据类型
- ALTER TABLE 表名 CHANGE 列名新列名新数据类型;
删除某一列
- ALTER TABLE 表名 DROP 列名;

2.6 删除数据表

删除数据表
- DROP TABLE 表名;
删除数据表（判断，如果存在则删除）
- DROP TABLE IF EXISTS 表名;

3. DML对数据进行操作

3.1 新增数据

给指定列添加数据
- INSERT INTO 表名 (列名1，列名2，，，) VALUES (值1，值2，，，);
- 列名和值的数量以及数据类型要对应，除了数字类型以外，其他的类型必须要加引号，值的个数和列举的列名的个数必须一致
给全部列添加数据
- INSERT INTO 表名 VALUES (值1，值2，，，);
- 值的顺序要和创建表时列名的顺序一致
批量添加数据
- INSERT INTO 表名 (列名1，列名2，，，) VALUES (值1，值2，，，),(值1，值2，，，)，，，;
- INSERT INTO 表名 VALUES (值1，值2，，，)，(值1，值2，，，)，，，;

3.2 修改和删除数据

修改表中的数据
- UPDATE 表名 SET 列名1=值1，列名2=值2，，，[WHERE 条件];
- 必须加条件，如果不加条件，则会将所有的数据都修改
删除表中的数据
- DELETE FROM 表名 [WHERE 条件];
- 必须加条件，如果不加条件，则会将所有的数据都删除

4. DQL对数据查询(单表查询)

4.1 完整查询语法

SELECT
- 字段列表
FROM
- 表名列表
【WHERE
- 条件列表】
【GROUP BY
- 分组字段】
【HAVING
- 分组后的过滤条件】
【ORDER BY
- 字段名 ASC或者DESC(升序或降序排列)】
【LIMIT
- 分页条件】

4.2 基本查询

查询全部的表数据
- SELECT * FROM 表名;
查询指定字段的表数据
- SELECT 字段名1,字段名2，，， FROM 表名;
去除重复查询
- SELECT DISTINCT 字段名1，字段名2，，， FROM 表名;
计算列的值（四则运算）
- SELECT 列名1 ，列名2 运算符(+ - * /) FROM 表名;
- 如果某一列为null，可以进行替换
  - IFNULL(表达式1，表达式2) 表达式1：想替换的列表达式2：想替换的值
起别名查询
- SELECT 列名1,列名2，，，AS 别名 FROM 表名;

4.3 条件查询

查询条件分类

符号	功能
>	大于
<	小于
>=	大于等于
<=	小于等于
=	等于
<> 或 !=	不等于
BETWEEN … AND …	在某个范围之内(都包含)
IN(…)	多选一
LIKE 占位符	模糊查询 _单个任意字符 %多个任意字符
IS NULL	是NULL
IS NOT NULL	不是NULL
AND 或 &&	并且
OR 或 \|\|	或者
NOT 或 !	非，不是

条件查询语法
- SELECT 字段名列表 FROM 表名 WHERE 条件;

4.4 聚合函数查询

介绍
- 将一列数据作为一个整体，进行纵向的计算
聚合函数的计算
函数名功能
COUNT(字段) 统计数量
MAX(字段) 最大值
MIN(字段) 最小值
SUM(字段) 求和
AVG(字段) 求平均值
null不被统计，不被计算
聚合函数查询语法
- SELECT 函数(字段名) FROM 表名 [WHERE 条件];

函数名	功能
COUNT(字段)	统计数量
MAX(字段)	最大值
MIN(字段)	最小值
SUM(字段)	求和
AVG(字段)	求平均值

4.5 排序查询

排序查询语法
- SELECT 字段列表 FROM 表名 [WHERE 条件] ORDER BY 字段名1 排序方式，字段名2 排序方式，，，;
排序方式
- ASC-升序排序
- DESC-降序排序
- 如果有多个排序条件，只有前面的条件值是一样的时候，才会判断第二条件

4.6 分组查询

分组查询语法
- SELECT 列名列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后的条件过滤] [ORDER BY 排序列名排序方式]；
where和having的区别
- 作用一样，进行条件的过滤
- where一般是在分组前进行过滤，having是分组后进行过滤
- 如果过滤条件中有聚合函数，必须使用having

4.7 分页查询

分页查询语法
- LIMIT 参数1，参数2；
  - 参数1，起始索引；参数2，每页显示的数量；
- SELECT 列名 FROM 表名 [WHERE 条件] [GROUP BY 分组列名] [HAVING 分组后的过滤条件] [ORDER BY 排序列名排序方式] LIMIT 当前页数，每页显示的条数；

5. 约束

5.1 介绍

约束就是对表中的数据进行限定，保证数据的正确性、有效性、完整性。

5.2 约束的分类

单表约束	作用
PRIMARY KEY	主键约束
UNIQUE	唯一约束
NOT NULL	非空约束
PRIMARY KEY AUTO_INCREMENT	主键自增

多表约束	作用
FOREIGN KEY	外键约束
FOREIGN KEY ON UPDATE CASCADE	外键级联更新
FOREIGN KEY ON DELETE CASCADE	外键级联删除

5.3 主键约束

特点
- 主键约束默认包含非空和唯一两个功能
- 主键一般用于表中数据的唯一标识
注意
- 一张表中只能有一个主键
- 选择表中的唯一且不可能为null的字段，开发中一般会选择没有任何业务含义的字段作为主键
- 如果主键的类型是int类型，我们可以使用auto_increment进行主键值的自增长
添加约束
- 创建表的时候
  - CREATE TABLE 表名(列名数据类型 PRIMARY KEY，，，列名数据类型约束);
- 修改表结构添加主键约束
  - ALTER TABLE 表名 MODIFY 字段名数据类型 PRIMARY KEY;
- 删除主键约束
  - ALTER TABLE 表名 DROP PRIMARY KEY

5.4 主键自增长

建表时添加主键自增约束
- CREATE TABLE 表名(列名数据类型 PRIMARY KEY AUTO_INCREMENT，，，列名数据类型约束);
删除主键自增约束
- ALTER TABLE 表名 MODIFY 列名数据类型；
建表后单独添加自增约束
- ALTER TABLE 表名 MODIFY 列名数据类型 AUTO_INCREMENT;

5.5 唯一约束

保证数据的唯一性：UNIQUE
建表时添加唯一约束
- CREATE TABLE 表名(列名数据类型 UNIQUE，，，列名数据类型约束);
删除唯一约束
- ALTER TABLE 表名 DROP INDEX 列名;
建表后单独添加唯一约束
- ALTER TABLE 表名 MODIFY 列名数据类型 UNIQUE;

5.6 非空约束

保证数据不能为null
建表时添加非空约束
- CREATE TABLE 表名(列名数据类型 NOT NULL，，，列名数据类型约束);
删除非空约束
- ALTER TABLE 表名 MODIFY 列名数据类型;
建表后单独添加非空约束
- ALTER TABLE 表名 MODIFY 列名数据类型 NOT NULL;

5.7 外键约束

当表和表之间的数据有相关联性的时候，如果没有相关的数据约束，则无法保证数据的准确性,外键约束可以让表和表之间产生关联关系，从而保证数据的准确性
建表时添加外键约束
- CREATE TABLE 表名(列名数据类型约束，，，CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名));
删除外键约束
- ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
建表后单独添加外键约束
- ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名 (主键列名);
级联更新
- 当我想把主表中的数据进行修改时，我期望从表中有关联的数据也会随之修改
级联删除
- 当我想把主表中的数据进行删除时，我期望从表中有关联的数据也会随之删除
添加级联更新
- ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名 (主键列名) ON UPDATE CASCADE;
添加级联删除
- ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名 (主键列名) ON DELETE CASCADE;
同时添加级联更新和级联删除
- ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名 (主键列名) ON UPDATE CASCADE ON DELETE CASCADE;

6. 多表操作

6.1 概念

多张数据表，而表与表之间是可以有一定的关联关系，这种关联关系通过外键约束实现。

6.2 分类

一对一

建表原则

在任意一个表建立外键，去关联另一个表的主键

-- 创建db5数据库
CREATE DATABASE db5;
-- 使用db5数据库
USE db5;

-- 创建person表
CREATE TABLE person(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);
-- 添加数据
INSERT INTO person VALUES (NULL,'张三'),(NULL,'李四');

-- 创建card表
CREATE TABLE card(
	id INT PRIMARY KEY AUTO_INCREMENT,
	number VARCHAR(50),
	pid INT UNIQUE,
	CONSTRAINT cp_fk1 FOREIGN KEY (pid) REFERENCES person(id) -- 添加外键
);
-- 添加数据
INSERT INTO card VALUES (NULL,'12345',1),(NULL,'56789',2);

一对多

建表原则

在多的地方创建外键，来关联一的一方主键

/*
用户和订单
*/
-- 创建user表
CREATE TABLE USER(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
);
-- 添加数据
INSERT INTO USER VALUES (NULL,'张三'),(NULL,'李四');

-- 创建orderlist表
CREATE TABLE orderlist(
	id INT PRIMARY KEY AUTO_INCREMENT,
	number VARCHAR(20),
	uid INT,
	CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)  -- 添加外键约束
);
-- 添加数据
INSERT INTO orderlist VALUES (NULL,'hm001',1),(NULL,'hm002',1),(NULL,'hm003',2),(NULL,'hm004',2);

/*
	商品分类和商品
*/
-- 创建category表
CREATE TABLE category(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10)
);
-- 添加数据
INSERT INTO category VALUES (NULL,'手机数码'),(NULL,'电脑办公');

-- 创建product表
CREATE TABLE product(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(30),
	cid INT,
	CONSTRAINT pc_fk1 FOREIGN KEY (cid) REFERENCES category(id)  -- 添加外键约束
);
-- 添加数据
INSERT INTO product VALUES (NULL,'华为P30',1),(NULL,'小米note3',1),
(NULL,'联想电脑',2),(NULL,'苹果电脑',2);

多对多

建表原则

需要借助第三方中间表，中间表至少包含两个列，这两个列作为中间表的外键，分别关联两张表的主键

-- 创建student表
CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);
-- 添加数据
INSERT INTO student VALUES (NULL,'张三'),(NULL,'李四');

-- 创建course表
CREATE TABLE course(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10)
);
-- 添加数据
INSERT INTO course VALUES (NULL,'语文'),(NULL,'数学');

-- 创建中间表
CREATE TABLE stu_course(
	id INT PRIMARY KEY AUTO_INCREMENT,
	sid INT, -- 用于和student表的id进行外键关联
	cid INT, -- 用于和course表的id进行外键关联
	CONSTRAINT sc_fk1 FOREIGN KEY (sid) REFERENCES student(id), -- 添加外键约束
	CONSTRAINT sc_fk2 FOREIGN KEY (cid) REFERENCES course(id)   -- 添加外键约束
);
-- 添加数据
INSERT INTO stu_course VALUES (NULL,1,1),(NULL,1,2),(NULL,2,1),(NULL,2,2);

7. 多表关联查询(笛卡尔积查询)

7.1 分类

内连接查询
- 隐式内连接查询
- 显式内连接查询
外连接查询
- 左外连接查询
- 右外连接查询
子查询
- 子查询结果是单行单列
- 子查询结果是多行单列
- 子查询结果是多行多列
自关联查询
- 自己关联自己

7.2 内连接查询

原理
- 内连接查询的是两张表有交集的部分数据（有主外键关联的数据）

查询语法

显式内连接

SELECT 列名 FROM 表名1 [INNER] JOIN 表名2 ON 关联条件;

-- 标准语法
SELECT 列名 FROM 表名1 [INNER] JOIN 表名2 ON 条件;

-- 查询用户信息和对应的订单信息
SELECT * FROM USER INNER JOIN orderlist ON user.id=orderlist.uid;
SELECT * FROM USER JOIN orderlist ON user.id=orderlist.uid;

-- 查询用户信息和对应的订单信息，起别名
SELECT * FROM USER u JOIN orderlist o ON u.id=o.uid;

-- 查询用户姓名，年龄。和订单编号
SELECT
	u.`name`,	-- 姓名
	u.`age`,	-- 年龄
	o.`number`	-- 订单编号
FROM
	USER u          -- 用户表
JOIN
	orderlist o     -- 订单表
ON 
	u.`id` = o.`uid`;

隐式内连接

SELECT 列名 FROM 表名1，表名2 WHERE 关联条件;

-- 标准语法
SELECT 列名 FROM 表名1,表名2 WHERE 条件;

-- 查询用户姓名，年龄。和订单编号
SELECT
	u.`name`,	-- 姓名
	u.`age`,	-- 年龄
	o.`number`	-- 订单编号
FROM
	USER u,		-- 用户表
	orderlist o     -- 订单表
WHERE
	u.`id`=o.`uid`;

7.3 外连接查询

左外连接查询

查询原理
- 查询左表的全部数据，和左右两张表有交集部分的数据

查询语法

SELECT 列名 FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 条件;

-- 标准语法
SELECT 列名 FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 条件;

-- 查询所有用户信息，以及用户对应的订单信息
SELECT
	u.`name`,	-- 姓名
	u.`age`,	-- 年龄
	o.`number`	-- 订单编号
FROM
	USER u          -- 用户表
LEFT OUTER JOIN
	orderlist o     -- 订单表
ON
	u.`id`=o.`uid`;

右外连接查询

查询原理
- 查询右表的全部数据，和左右两张表有交集部分的数据

查询语法

SELECT 列名 FROM 表名1 RIGHT [OUTER] JOIN 表名2 ON 条件;

-- 查询所有订单信息，以及订单所属的用户信息
SELECT
	u.`name`,	-- 姓名
	u.`age`,	-- 年龄
	o.`number`	-- 订单编号
FROM
	USER u          -- 用户表
RIGHT OUTER JOIN
	orderlist o     -- 订单表
ON
	u.`id`=o.`uid`;

7.4 子查询

概念
- 查询语句中嵌套了查询语句，我们就将嵌套的查询称为子查询
结果是单行单列的
- 查询作用
  - 可以将查询结果当作另外一个的查询条件参与查询，使用运算符 = > >= < <= 判断
- 查询语句
  - SELECT 列名 FROM 表名 WHERE 列名判断符 (查询语句);
结果是多行单列的
- 查询作用
  - 可以将查询结果作为查询条件参与查询，使用运算符IN或NOT IN 参与查询，
- 查询语法
  - SELECT 列名 FROM 表名 WHERE 列名 [NOT] IN 查询语句;
结果是多行多列的
- 查询作用
  - 查询的结果可以作为一张虚拟表参与查询
- 查询语法
  - SELECT 列名 FROM 表名 [别名] ,查询语句 [别名] [WHERE 条件]

7.5 自关联查询

概念
- 在同一张表中，自己和自己存在关联，把这一张表当作两张表来处理

-- 创建员工表
CREATE TABLE employee(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	mgr INT,
	salary DOUBLE
);
-- 添加数据
INSERT INTO employee VALUES (1001,'孙悟空',1005,9000.00),
(1002,'猪八戒',1005,8000.00),
(1003,'沙和尚',1005,8500.00),
(1004,'小白龙',1005,7900.00),
(1005,'唐僧',NULL,15000.00),
(1006,'武松',1009,7600.00),
(1007,'李逵',1009,7400.00),
(1008,'林冲',1009,8100.00),
(1009,'宋江',NULL,16000.00);

-- 查询所有员工的姓名及其直接上级的姓名，没有上级的员工也需要查询
/*
分析：
	员工姓名 employee表        直接上级姓名 employee表
	条件：employee.mgr = employee.id
	查询左表的全部数据，和左右两张表交集部分数据，使用左外连接
*/
SELECT
	t1.name,	-- 员工姓名
	t1.mgr,		-- 上级编号
	t2.id,		-- 员工编号
	t2.name     -- 员工姓名
FROM
	employee t1  -- 员工表
LEFT OUTER JOIN
	employee t2  -- 员工表
ON
	t1.mgr = t2.id;

8. 视图

8.1 介绍

视图
- 是一种用于查询操作的特殊表，不是在数据库中实际存在的，是虚拟存在的
作用
- 对外界提供查询操作，将一些较为复杂的查询语句的结果，封装到一个虚拟表中，后期需要相同需求的时候，直接查询该虚拟表即可

8.2 视图的使用

创建视图
- 标准语法
  - CREATE VIEW 视图名称 [(列名列表)] AS 查询语句;
查询视图
- 标准语法
  - SELETE * FROM 视图名称;
修改视图
- 修改视图数据语法
  - UPDATE 视图名称 SET 列名=值 WHERE 条件;
  - 注意：修改视图数据后，源表中的数据也会随之修改
- 修改视图结构语法
  - ALTER VIEW 视图名称 (列名列表) AS 查询语句;
- 删除视图语法
  - DROP VIEW 视图名称;

9. 备份和恢复

9.1 命令行方式

备份
- 登录到MySQL服务器，输入：mysqldump -u root -p 数据库名称 > 文件保存路径
恢复
- 登录MySQL数据库
- 删除已备份的数据库
- 重新创建名称相同的数据库
- 使用该数据库
- 导入文件执行：source备份文件全路径

9.2 图形化界面

选中数据库
单击右键，选择备份/导出
进行导出
恢复

10. 存储过程和函数

10.1 介绍

存储过程和函数是事先经过编译并保存在数据库中的一段SQL语句的集合
存储过程和函数的好处
- 提高代码的复用性
- 减少数据（sql语句）在数据库和应用服务器之间的传输
- 减少代码层面的业务处理
存储过程和函数的区别
- 存储函数必须有返回值
- 存储过程可以没有返回值

10.2 存储过程的创建和调用

更改分隔符

/*
	该关键字用来声明sql语句的分隔符，告诉MySQL该段命令已经结束！
	sql语句默认的分隔符是分号，但是有的时候我们需要一条功能sql语句中包含分号，
	但是并不作为结束标识。
	这个时候就可以使用DELIMITER来指定分隔符了！
*/
-- 标准语法
DELIMITER 分隔符

创建存储过程

-- 修改结束分隔符为$
DELIMITER $

-- 创建存储过程
CREATE PROCEDURE 存储过程名称(参数列表)
BEGIN 
	SQL语句列表;
END$

-- 修改分隔符为分号
DELIMITER ;

调用存储过程
- CALL 存储过程名称(实际参数);
查看数据库中的所有的存储过程
- SELECT * FROM mysql.proc WHERE db='数据库名称';
删除存储过程
- DROP PROCEDURE [IF EXISTS] 存储过程名称;

10.3 存储过程语法

10.3.1 变量

定义变量
- DECLARE 变量名数据类型 [DEFAULT 默认值]；
变量赋值方式一
- SET 变量名 = 变量值;
变量赋值方式二
- SELECT 列名 INTO 变量名 FROM 表名 [WHERE 条件];

10.3.2 if语句

if语句标准语法

IF 判断条件1 THEN 
	执行的sql语句1;
ELSEIF 判断条件2 THEN 
	执行的sql语句2;
ELSE
	执行的sql语句3;
END IF;

10.3.3 参数传递

存储过程的参数和返回值
```
CREATE PROCEDURE 存储过程名称 ([IN|OUT|IN OUT] 参数名 数据类型)
BEGIN
	SQL语句列表
END$
```
- IN:代表输入参数，需要由调用者传递实际参数
- OUT:代表输出参数，该参数可以作为返回值
- INOUT:代表既可以作为输入参数，也可以作为输出参数

10.3.4 while循环

while循环语法

初始化语句;
WHILE 条件判断语句 DO
	循环体语句;
	条件控制语句;
END WHILE;

10.3.5 case语句

标准语法1

CASE 表达式
WHEN 值1 THEN 执行sql语句1;
[WHEN 值2 THEN 执行sql语句2;]
...
[ELSE 执行sql语句n;]
END CASE;

标准语法2

CASE
WHEN 判断条件1 THEN 执行sql语句1;
[WHEN 判断条件2 THEN 执行sql语句2;]
...
[ELSE 执行sql语句n;]
END CASE;

10.3.6 repeat循环

标准语法

初始化语句;
REPEAT
	循环体语句;
	条件控制语句;
	UNTIL 条件判断语句
END REPEAT;

注意：repeat循环是条件满足则停止。while循环是条件满足则执行

10.3.7 loop循环

标准语法

初始化语句;
[循环名称:] LOOP
	条件判断语句
		[LEAVE 循环名称;]
	循环体语句;
	条件控制语句;
END LOOP 循环名称;

注意
- loop可以实现简单的循环，但是退出循环需要使用其他的语句来定义。我们可以使用leave语句完成
- 如果不加退出循环的语句，那么就变成了死循环。

10.3.8 游标

游标的概念
- 游标可以遍历返回的多行结果，每次拿到一整行数据
- 在存储过程和函数中可以使用游标对结果集进行循环的处理
- 简单来说游标就类似于集合的迭代器遍历
- MySQL中的游标只能用在存储过程和函数中
游标的语法
- 创建游标
  - DECLARE 游标名称 CURSOR FOR 查询sql语句;
- 打开游标
  - OPEN 游标名称;
- 使用游标获取数据
  - FETCH 游标名称 INTO 变量名1,变量名2,...;
- 关闭游标
  - CLOSE 游标名称;

10.4 存储函数

存储函数和存储过程是非常相似的，区别在于存储函数必须有返回值

创建存储函数

CREATE FUNCTION 函数名称(参数列表)
RETURNS 返回值类型
BEGIN
	SQL语句列表;
	RETURN结果;
END$

调用存储函数
- SELECT 函数名称(实际参数);
删除存储函数
- DROP FUNCTION 函数名称;
案例演示

/*
	定义存储函数，获取学生表中成绩大于95分的学生数量
*/
DELIMITER $

CREATE FUNCTION fun_test1()
RETURNS INT
BEGIN
	-- 定义统计变量
	DECLARE result INT;
	-- 查询成绩大于95分的学生数量，给统计变量赋值
	SELECT COUNT(*) INTO result FROM student WHERE score > 95;
	-- 返回统计结果
	RETURN result;
END$

DELIMITER ;

-- 调用fun_test1存储函数
SELECT fun_test1();

11. 触发器

11.1 介绍

触发器是和表有关的数据库对象，可以在insert、update、delete之前或之后触发并执行触发器中定义的sql语句
这种特性可以协助应用系统在数据库端确保数据的完整性、日志记录、数据校验等操作
使用别名NEW和OLD来引用触发器中发生变化的内容记录

11.2 分类

触发器类型	OLD	NEW
INSERT型触发器	无(因为插入前状态无数据)	NEW表示将要或者已经新增的数据
UPDATE型触发器	OLD表示修改之前的数据	NEW表示修改之后的数据
DELETE型触发器	OLD表示将要或者已经删除的数据	无(因为删除后状态无数据)

11.3 创建

标准语法

DELIMITER $

CREATE TRIGGER 触发器名称
BEFORE|AFTER INSERT|UPDATE|DELETE
ON 表名
FOR EACH ROW
BEGIN
	触发器要执行的功能;
END$

DELIMITER ;

BEFORE：在添加数据之前记录；AFTER：在添加数据之后记录；

11.4 查看和删除

查看触发器
- SHOW TRIGGERS;
删除触发器
- DROP TRIGGER 触发器名称;

12. 事务

12.1 介绍

一条或多条SQL语句组成一个执行单元，其特点是这个单元要么同时成功，要么同时失败
单元中的每条SQL语句都相互依赖，形成一个整体
如果某条SQL语句执行失败或者出现错误，那么整个单元就会撤回到事务最初的状态
如果单元中所有的SQL语句都执行成功，则事务就顺利执行

12.2 操作

开启事务
- START TRANSACTION;
回滚事务
- ROLLBACK;
提交事务
- COMMIT;

12.3 提交方式

分类
- 自动提交（MySQL默认）
- 手动提交（Oracle默认）
查看事务提交方式
- SELECT @@ AUTOCOMMIT;
- 1 代表自动提交，0 代表手动提交
修改事务提交方式
- SET @@ AUTOCOMMIT=数字;

12.4 事务的四大特征(ACID，酸性)

原子性（Atomicity）
- 原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响
一致性（Consistency）
- 一致性是指事务必须使数据库从一个一致性状态转换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态
隔离性（isolcation）
- 隔离性是当等多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离
持久性（durability）
- 持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作

12.5 事务的隔离级别

多个客户端操作的时候，每一个客户端之间应该是隔离的，相互独立的，不受影响的。而如果多个事务操作同一批数据时，就会产生不同的问题，我们需要设置不同的隔离级别来解决这些问题

隔离级别分类

隔离级别	名称	会引发的问题
read uncommitted	读未提交	脏读、不可重复读、幻读
read committed	读已提交	不可重复读、幻读
repeatable read	可重复读	幻读
serializable	串行化	无

MySQL默认是repeatable read
Oracle默认是read committed

引发的问题详解

问题	现象
脏读	在一个事务处理过程中读取到了另一个未提交事务中的数据，导致两次查询结果不一致
不可重复读	在一个事务处理过程中读取到了另一个事务中修改并已提交的数据，导致两次查询结果不一致
幻读	查询某数据不存在，准备插入此记录，但执行插入时发现此记录已存在，无法插入，或查询数据不存在执行删除操作，却发现删除成功

查询数据库隔离级别
- SELECT @@ TX_ISOLATION;
修改数据库隔离级别
- SET GLOBAL TRASACTION ISOLATION LEVEL 级别字符串;
- 修改之后需要重新连接

13. 存储引擎

13.1 MySQL体系结构

客户端连接
- 支持接口：支持的客户端连接，例如C、Java、PHP等语言来连接MySQL数据库
第一层：网络连接层
- 连接池：管理、缓冲用户的连接，线程处理等需要缓存的需求
第二层：核心服务层
- 管理服务和工具：系统的管理和控制工具，例如备份恢复、复制等
- SQL接口：接受SQL命令，例如：过滤条件、语法结构等
- 查询解析器：验证和解析SQL命令，例如过滤条件、语法结构等
- 查询优化器：在执行查询之前，使用默认的一套优化机制进行优化sql语句
- 缓存：如果缓存当中有想查询的数据，则直接将缓存中的数据返回，没有的话再重新查询
第三层：存储引擎层
- 插件式存储引擎：管理和操作数据的一种机制，包括(存储数据、如何更新、查询数据等)
第四层：系统文件层
- 文件系统：配置文件、数据文件、日志文件、错误文件、二进制文件等的保存

13.2 存储引擎介绍

在生活中，引擎就是整个机器运行的核心（发动机），不同的引擎具备不同的功能，应用于不同的场景当中
MySQL数据库使用不同的机制存取表文件，包括存储方式、索引技巧、锁定水平等不同的功能，这些不同的技术以及配套的功能称之为存储引擎
Oracle、SqlServer等数据库只有一种存储引擎，而MySQL针对不同的需求，配置不同的存储引擎，就会让数据库采取不同处理数据的方式和扩展功能
MySQL支持的存储引擎很多，其中较常用的有三种：MyISAM、InnoDB、MEMORY
常用引擎特性对比
- MyISAM存储引擎：访问快，不支持事务和外键操作
- InnoDB存储引擎：支持事务和外键操作，支持并发控制，占用磁盘空间大
- MEMORY存储引擎：内存存储，速度快，不安全，适合小量快速访问的数据

特性对比

特性	MyISAM	InnoDB	MEMORY
存储限制	有(平台对文件系统大小的限制)	64TB	有(平台的内存限制)
事务安全	不支持	支持	不支持
锁机制	表锁	表锁/行锁	表锁
B+Tree索引	支持	支持	支持
哈希索引	不支持	不支持	支持
全文索引	支持	支持	不支持
集群索引	不支持	支持	不支持
数据索引	不支持	支持	支持
数据缓存	不支持	支持	N/A
索引缓存	支持	支持	N/A
数据可压缩	支持	不支持	不支持
空间使用	低	高	N/A
内存使用	低	高	中等
批量插入速度	高	低	高
外键	不支持	支持	不支持

13.3 存储引擎的操作

查询数据库支持的存储引擎
- SHOW ENGINES;
查询某个数据库中所有数据表的存储引擎
- SHOW TABLE STATUS FROM 数据库名称;
查询某个数据库中某个表的存储引擎
- SHOW TABLE STATUS FROM 数据库名称 WHERE NAME = '数据库名称';
创建数据表，指定存储引擎
- CREATE TABLE 表名(列名,数据类型，，，) ENGINE = 引擎名称;
修改数据表的存储引擎
- ALTER TABLE 表名 ENGINE = 引擎名称;

13.4 存储引擎的选择

MyISAM
- 特点：不支持事务和外键操作，读取速度快，节约资源
- 使用场景：以查询操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高
InnoDB
- 特点：MySQL的默认存储引擎，支持事务和外键操作
- 使用场景：对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，读写频繁的操作
MEMORY
- 特点：将所有数据保存在内存中，在需要快速定位记录和其他类似数据环境下，可以提供更快的访问
- 使用场景：通常用于更新不太频繁的小表，用来快速得到访问的结果
总结：针对不同的需求场景，来选择最适合的存储引擎。如果不确定，则使用数据库默认的存储引擎

14. 索引

14.1 介绍

MySQL索引：是帮助MySQL高效获取数据的一种数据结构，所以，索引的本质就是数据结构
在表数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引

14.2 分类

按照功能分类
- 普通索引：最基本的索引，没有任何限制
- 唯一索引：索引列的值必须唯一，但允许有空值，如果是组合索引，则列值组合必须唯一
- 主键索引：一种特殊的唯一索引，不允许有空值，一般在建表时同时创建主键索引
- 联合索引：顾名思义，就是将单列索引进行组合
- 外键索引：只有InnoDB引擎支持外键索引，用来保证数据的一致性、完整性和实现级联操作
- 全文索引：快速匹配全部文档的方式，InnoDB引擎的5.6版本后才支持全文索引，MEMORY引擎不支持
按照结构分类
- BTree索引：MySQL使用最频繁的一个索引数据结构，是InnoDB和MyISAM存储引擎默认的索引类型，底层基于B+Tree数据结构
- Hash索引：MySQL中Memory存储引擎默认支持的索引类型

14.3 索引的操作

创建索引

CREATE [UNIQUE|FULLTEXT] INDEX 索引名称
[USING 索引类型]    -- 默认是BTREE
ON 表名 (列名，，，);

查看索引
- SHOW INDEX FROM 表名;
添加索引
- 普通索引
  - ALTER TABLE 表名 ADD INDEX 索引名称(列名);
- 组合索引
  - ALTER TABLE 表名 ADD INDEX 索引名称(列名1，列名2，，，);
- 主键索引
  - ALTER TABLE 表名 ADD PRIMARY KEY (主键列名);
- 外键索引
  - ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREICN KEY (本表外键列名) REFERENCES 主表名 (主键列名);
- 唯一索引
  - ALTER TABLE 表名 ADD UNIQUE 索引名称(列名);
- 全文索引
  - ALTER TABLE 表名 ADD FULLTEXT 索引名称(列名);
删除索引
- DROP IDNEX 索引名称 ON 表名;

14.4 索引的原理

索引是在存储引擎中实现的，不同的存储引擎所支持的索引也不一样，这里介绍InnoDB引擎的BTree索引
BTree索引类型是基于B+Tree数据结构的，而B+Tree数据结构又是BTree数据结构的变种，通常使用在数据库和操作系统中的文件系统，特点是能够保持数据稳定有序
磁盘存储
- 系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的
- 位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么
- InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位，InnoDB存储引擎中默认每个页的大小为16KB
- InnoDB引擎将若干个地址连接磁盘块，以次来达到页的大小16KB，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高插叙效率
BTree
- 每一个节点除了键值（key），还有真实的数据，会增加查询数据时磁盘的IO次数
B+Tree
- 真实的数据只会保存在叶子节点，所有的叶子节点之间都有连接指针
- 有范围：对于主键的范围查找和分页查找
- 有顺序：从根节点开始，进行随机查找
- 相对比BTree，提高查询速度，属性结构较小，减少磁盘的IO次数

14.5 索引的设计原则

创建索引遵循的原则
- 对查询频次较高，且数据量比较大的表建立索引
- 使用唯一索引，区分度越高，使用索引的效率越高
- 索引字段的选择，最佳候选列应当从where子句的条件中提取
- 索引虽然可以有效的提升查询数据的效率，但并不是多多益善
最左匹配原则（适用组合索引）
- 索引字段出现的顺序可以是任意的，MySQL优化器会帮我们自动的调整where条件中的顺序
- 如果组合索引中最左边的列不在查询条件中，则不会命中索引

15. 锁机制

15.1 介绍

数据库为了保证数据的一致性，在共享的资源被并发访问时变得安全所设计的一种规则
锁机制类似多线程中的同步，作用就是可以保证数据的一致性和安全性
按操作分类
- 共享锁：也叫读锁，针对同一份数据，多个事务读取操作可以同时加锁而不互相影响，但是不能修改数据
- 排他锁：也叫写锁，当前的操作没有完成前，会阻断其他操作的读取和写入
按粒度分类
- 表级锁：会锁定整个表，开销小，加锁块，锁定粒度大，发生锁冲突概率高，并发度低，不会出现死锁情况
- 行级锁：会锁定当前行，开销大，加锁慢，锁定粒度小，发生锁冲突概率低，并发度高，会出现死锁情况
按使用方式分类
- 悲观锁：每次查询数据时都认为别人会修改，很悲观，所以查询时加锁
- 乐观锁：每次查询数据时都认为别人不会修改，很乐观，但是更新时会判断一下在此期间别人有没有更新这个数据
不同存储引擎支持的锁
存储引擎表锁行锁
InnoDB 支持支持
MyISAM 支持不支持
MEMORY 支持不支持

存储引擎	表锁	行锁
InnoDB	支持	支持
MyISAM	支持	不支持
MEMORY	支持	不支持

15.2 InnoDB共享锁

共享锁特点
- 数据可以被多个事务查询，但是不能修改
共享锁的创建
- SELECT语句 LOCK IN SHARE MODE;

15.3 InnoDB排他锁

排他锁特点
- 加锁的数据，不能被其他事务加锁查询或修改
排他锁创建语法
- SELECT语句 FOR UPDATE;

注意：只有共享锁和共享锁可以共享，其他都不行

15.4 MyISAM读锁

读锁特点
- 所有连接只能查询数据，不能修改
读锁语法格式
- 加锁
  - LOCK TABLE 表名 READ;
- 解锁
  - UNLOCK TABLES;

15.5 MyISAM写锁

写锁特点
- 其他连接不能查询和修改数据
写锁语法格式
- 加锁
  - LOCK TABLE 表名 WRITE;
- 解锁
  - UNLOCK TABLES;

15.6 悲观锁和乐观锁

悲观锁
- 它对于数据被外界修改的操作持保守态度，认为数据随时会修改，整个数据处理中需要将数据加锁，悲观锁一般都是依靠关系型数据库提供的锁机制
乐观锁
- 每次自己操作数据的时候都认为没有人会来修改它，所以不去加锁，但是在更新的时候会去判断在此期间数据有没有被修改，需要用户自己去实现，不会发生抢占资源，只有在提交操作的时候检查是否违反数据完整性
乐观锁添加方式一
- 给数据表中添加一个version列，每次更新后都将这个列的值加一，读取数据时，将版本号读取出来，在执行更新的时候，比较版本号，如果相同则执行更新，如果不相同，说明数据发生了变化，用户自行根据这个通知来决定怎么处理，比如重新开始或者放弃本次更新
乐观锁添加方式二
- 和版本号方式基本相同，给数据表中添加一个列，名称无所谓，数据类型需要是timestamp。每次更新后都将最新时间插入此列，读取数据时，将时间读取出来，在执行更新的时候，比较时间，如果相同则执行更新，如果不相同，说明此条数据发生了变化