10-mysql_创建数据库,数据表,输入数据,这三者之间的关系是

mysql

1.数据库、数据表与数据的关系

1. MySql服务器中可以创建多个数据库
2. 每个数据库中可以包含多个数据表
3. 每个数据表中可以存储多条数据记录
4. 客户端可以通过数据库管理系统来操作MySQL数据库

2.SQL介绍

1. SQL（Structured Query Language）：结构化查询语言，其实就是定义了操作所有关系型数据库的一种规则。
2. 通用语法规则

① SQL语句可以单行或多行书写，以分号结尾；

② 可以使用空格和缩进来增强语句的可读性；

③ MySQL数据库的SQL语句不区分大小写，关键字建议使用大写；

④ 单行注释：–注释内容 #注释内容（MySQL特有）；

⑤ 多行注释：/注释内容/

3. SQL分类

① DDL（Data Definition Language）：数据定义语言。用来操作数据库、表、列等。

② DML（Data Manipulation Language）: 数据操作语言。用来对数据库中表的数据进行增删改。

③ DQL（Data Query Language）：数据查询语言。用来查询数据库中表的记录（数据）。

④ DCL（Data Control Language）：数据控制语言。用来定义数据库的访问权限和安全级别，及创建用户

3.DDL

1. 查询所有的数据库：

    SHOW DATABASES；
   1

2. 查询指定数据库的创建语句：

SHOW CREATE DATABASE 数据库名称；
1

3.创建数据库：

CREATE DATABASE 数据库名称；
1

4.创建数据库（判断，如果不存在则创建）：

CREATE DATABASE IF NOT EXISTS 数据库名称；
1

5.创建数据库（指定字符集）：

CREATE DATABASE 数据库名称 CHARACTER SET 字符集名称；
1

6.修改数据库（修改字符集）：

ALTER DATABASE 数据库名称 CHARACTER SET 字符集名称；
1

7.删除数据库 ：

DROP DATABASE 数据库名称；
1

8.删除数据库（判断，如果存在则删除） ：

DROP DATABASE IF EXISTS 数据库名称；
1

9.使用数据库

  USE 数据库名称；
1

10.查看当前使用的数据库

 SELECCT DATABASE()；
1

11.删除数据表

DROP TABLE 表名；
1

12.删除数据表（判断，如果存在则删除）

DROP TABLE IF EXISTS 表名；
1

4.DML

1.给指定列添加数据

INSERT INTO 表名（列名1，列名2，...）values （值1，值2，...）；
1

2.给全部列添加数据 （表中添加一条数据）

INSERT INTO 表名VALUES（值1，值2，...）；
1

3.批量添加数据

3.INSERT INTO 表名（列名1，列名2，...）values （值1，值2，...），（值1，值2，...），...；
INSERT INTO 表名VALUES（值1，值2，...），（值1，值2，...）...；
1
2

注：列名和值的数量以及数据类型要进行对应，除了数字类型。其他数据类型的数据都需要加单引号（单引双引都行，推荐单引）。

4.修改表中的数据

UPTATE 表名 SET 列名1=值1，列名2=值2，... [WHERE 条件]；
1

修改语句中必须加条件，如果不加条件，则会将所有数据都修改。

5.删除表中的数据

DELETE FROM表名[WHERE 条件]；
1

删除语句中必须加条件，如果不加条件，则会将所有数据都删除。

5.DQL

1.表数据查询语法

  SELECT  字段列表
  FROM 表名列表
  WHERE 条件列表
  GROUP BY 分组字段
  HAVING 分组后的过滤条件
  ORDER BY 排序
  LIMIT 分页参数
1
2
3
4
5
6
7

2.查询全部的表数据

SELECT * FROM 表名；
1

3.查询指定字段的表数据

SELECT 列名1，列名2，...FROM 表名；
1

4.去除重复查询

SELECT DISTINCT 列名1，列名2，...FROM 表名；
1

5.计算列的值（四则运算）

SELECT 列名1 运算符（+-*/）列名2 FROM表名；
1

如果某一列为null，可以进行替换

Ifnull（表达式1，表达式2）

表达式1：想替换的列

表达式2：想替换的值

-- 查询商品名称和库存，库存数量在原有的基础上加10，进行null值判断
SELECT NAME,IFNULL(stock,0) FROM product;
1
2

6.起别名查询

-- 查询商品名称和库存，库存数量在原有的基础上加10，进行null值判断  起别名为getSum
SELECT NAME,IFNULL(stock,0) AS getSum FROM product;
1
2

7.查询条件分类

8.条件查询语法

SELECT 列名列表 FROM 表名 WHERE 条件；

6.聚合函数（一列数据纵向计算）

将一列数据作为一个整体，进行纵向的计算。

分类：

语法：

SELECT 函数名（列名）FROM 表名 [WHERE 条件]；
1

7.排序查询

SELECT 列名列表 FROM 表名 [WHERE 条件] ORDER BY 列名 排序方式，列名 排序方式，...;
1

排序方式：ASC 升序 ，DESC 降序；

如果有多个排序条件，只有当前面的条件值一样时，才会判断第二条件。

8.分组查询

SELECT 列名列表 FROM 表名 [WHERE 条件]  GROUP BY 分组列名 [HAVING 分组后的条件过滤] [ORDER BY 排序列名 排序方式]；
1

对于金额大于4000元的商品，按照品牌分组。获取每组商品的总金额，只显示总金额大于7000元的，并按照总金额的降序排列。

SELECT brand，getSum(price) FROM product WHERE price > 4000 GROUP BY brand HAVING getSum > 7000 ORDERBY getSum DESC;
1

9.分页查询

SELECT 列名列表 FROM 表名
WHERE 条件
GROUP BY 分组列名
HAVING 分组后的条件过滤
ORDER BY 排序列名 排序方式
LIMIT 当前页数，每页显示的条数

# 当前页数= 当前页数-1
# 例：SELECT * FROM product LIMIT 0,3;   展示第一页的三条数据
1
2
3
4
5
6
7
8
9

10.约束

10.1 约束概念：

对标中的数据进行限定，保证数据的正确性、有效性、完整性！

10.2约束分类

加粗的是主要约束类型

还有一个约束类型是默认约束（DEFAULT ）

10.3 主键约束（PRIMARY KEY ）

10.3.1 主键约束特点

主键约束默认包含非空和唯一两个功能，

一张表只能有一个主键，

主键一般用于表中数据的唯一标识。

10.3.2 建表时添加主键约束

   CREATE  TABLE  表名（
       列名  数据类型  PRIMARY KEY。
       .......
       列名  数据类型 约束
） ；
1
2
3
4
5

10.3.3 删除主键约束

ALTER TABLE 表名 DROP  PRIMARY KEY；
1

10.3.4 建表之后单独添加主键约束

ALTER TABLE 表名 MODIFY 列名 数据类型 PRIMARY KEY； 
1

10.4 主键自增约束（PRIMARY KEY AUTO_INCREMENT）

10.4.1 特点

在主键约束的基础上增加了一个自增约束。特点如下：

在 MySQL 中，当主键定义为自增长后，这个主键的值就不再需要用户输入数据了，而由数据库系统根据定义自动赋值。每增加一条记录，主键会自动以相同的步长进行增长。

默认情况下，auto_increment的初始值是 1，每新增一条记录，字段值自动加 1。

一个表中只能有一个字段使用 auto_increment约束，且该字段必须有唯一索引，以避免序号重复（即为主键或主键的一部分）。

auto_increment约束的字段必须具备 NOT NULL 属性。

auto_increment约束的字段只能是整数类型（TINYINT、SMALLINT、INT、BIGINT 等

auto_increment约束字段的最大值受该字段的数据类型约束，如果达到上限，auto_increment就会失效。

10.4.2建表时添加主键自增约束

  CREATE TABLE 表名（
   列名 数据类型 PRIMARY KEY AUTO_INCREMENT,
   .......
   列名 数据类型 约束
）；
1
2
3
4
5

10.4.3删除主键自增约束（主键约束不删除，仅删除自增约束）

ALTER TABLE 表名 MODIFY 列名 数据类型；
# 这里用的modify，只改变数据类型，也可以用change，改变列名的同时输入新的数据类型。

# 进行下面一步删除主键约束
alter table 表名 drop primary key;
1
2
3
4
5

10.4.4 建表后单独添加主键自增约束

ALTER TABLE 表名 MODIFY 列名 数据类型 AUTO_INCREMENT；
1

MYSQL中的自增约束，必须配合键的约束一起使用。

10.5 唯一约束（UNIQUE）

10.5.1 唯一约束特点

1.唯一约束可以保证记录的唯一性，即就是同一个表中，相同字段的值不会出现重复。

2.唯一约束的字段可以为空值（NULL）。

3.每一张数据表可以存在多个唯一约束字段。

10.5.2 建表时添加唯一约束

  CREATE TABLE 表名（
列名  数据类型  UNIQUE，
...
列名 数据类型 约束
）；
1
2
3
4
5

10.5.3 删除唯一约束

 ALTER TABLE 表名 DROP INDEX 列名；
1

10.5.4 建表后单独添加唯一约束（单独添加时，对应数据要保持唯一，否则会添加失败）

ALTER TABLE 表名 MODIFY 列名 数据类型 UNIQUE；
1

10.6 非空约束（NOT NULL）

10.6.1 特点

MySQL 非空约束（not null）指字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统就会报错。

10.6.2 建表时添加非空约束

 CREATE TABLE 表名（
列名  数据类型  NOT NULL，
...
列名 数据类型 约束
）；
1
2
3
4
5

10.6.3 删除非空约束

ALTER TABLE 表名 MODIFY  列名 数据类型；
1

10.6.4 建表后单独添加非空约束（添加时，对应的数据可以为NULL，但是添加后，值为NULL的数据会变成空）

ALTER TABLE 表名 MODIFY 列名 数据类型 NOT NULL；
1

10.7 外键约束

10.7.1 外键约束出现原因

当表与表之间的数据有相关联性的时候，如果没有相关的数据约束，则无法保证数据的准确性！

让两张表或更多张表之间产生关联关系。

10.7.2 外键约束作用

让表与表之间产生关联关系，从而保证数据的准确性！

10.7.3 建表时添加外键约束

 CREATE TABLE 表名 （
  列名 数据类型 约束，
  外键列 数据类型，
 ...
  CONSTRAINT 外键名 FOREIGN KEY（本表外键列名） REFERENCES 主表名 （主表主键列名）
）；
#  外键名：两个表的首字母+“_”+“fk”+编号（从1开始）
  #      例如：order表与user表 :ou_fk1
1
2
3
4
5
6
7
8

10.7.4 删除外键约束

ALTER TABLE 表名 DROP FOREIGN KEY 外键名；
1

10.7.5 建表后单独添加外键约束

 ALTER  TABLE 表名 ADD  CONSTRAINT 外键名 FOREIGN KEY（本表外键列名） REFERENCES 主表名 （主表主键列名）；
1

11.外键的级联更新与级联删除

11.1 级联删除与级联更新

级联删除就是：当想把主表中的数据删除时，期望从表中有关联的数据也会随之删除！

级联更新：当想把主表中的数据更新时，期望从表中有关联的数据也会随之更新！

11.2 添加级联更新

ALTER TABLE 表名 CONSTRAINT 外键名 FOREIGN KEY （本表外键列名）REFERENCES 主表名 （主键列名）
  ON UPDATE CASCADE；
1
2

11.3 添加级联删除

ALTER TABLE 表名 CONSTRAINT 外键名 FOREIGN KEY （本表外键列名）REFERENCES 主表名 （主键列名）
  ON DELETE CASCADE
1
2

11.4 同时添加级联更新和级联删除

  ALTER TABLE 表名 CONSTRAINT 外键名 FOREIGN KEY （本表外键列名）REFERENCES 主表名 （主键列名）
  ON UPDATE CASCADE  ON DELETE CASCADE；
1
2

12.多表操作

12.1 多表概念

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

12.2 多表分类

一对多，一对一，多对多

12.3 一对一

适用场景：人和身份证。一个人只能有一个身份证，一个身份证只能对应一个人。

建表原则：在任何一张表建立外键，关联另外一张表的主键。

12.4 一对多

适用场景：

用户和订单，一个用户可以有多个订单。

商品分类和商品，一个分类下可以有多个商品。

建表原则：

在多的一方建立外键约束，来关联一的一方主键。

12.5 多对多

适用场景

学生和课程。一个学生可以选择多个课程，一个课程也可以被多个学生选择。

建表原则

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

-- 创建中间表
CREATE TABLE stu_course(
		id INT PRIMARY KEY AUTO_INCREMENT,   -- 主键id
    	sid INT,  -- 用于和student表中的id进行外键关联
    	cid INT,  -- 用于和course表中的id进行外键关联
    	CONSTRAINTsc_fk1 FOREIGN KEY (sid) REFERENCES student(id), -- 添加外键约束
    	CONSTRAINTsc_fk1 FOREIGN KEY (sid) REFERENCES student(id)  -- 添加外键约束
);
1
2
3
4
5
6
7
8

12.6 多表查询

12.6.1 多表查询分类

1. 内连接查询
2. 外连接查询
3. 子查询
4. 自关联查询

12.6.2 内连接查询

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

查询语法：

1. 显式内连接

SELECT 列名 FROM 表名1 [INNER] JOIN 表名2 ON 条件；（INNER可写可不写）

2. 隐式内连接

SELECT 列名 FROM 表名1，表名2 WHERE 条件；

-- 查询用户姓名和订单编号
-- 显式内连接查询
SELECT 
	u.name,
	o.number
FROM 
	USER u
INNER JOIN 
	orderlist o
ON
 	o.uid = u.id;
 	
 	
-- 隐式内连接查询
SELECT 
	u.name,
	o.number
FROM 
	USER u，
	orderlist  o
WHERE 
	 o.uid = u.id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

12.6.3 外连接查询

1. 左外连接

①查询原理

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

②查询语法

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

2. 右外连接

①查询原理

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

②查询语法

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

-- 查询所有用户信息和用户对应的订单信息
-- 左外连接查询
SELECT 
	u.*,
	o.number
FROM 
	USER u
LEFT OUTER JOIN 
	orderlist o
ON
 	o.uid = u.id;
 	
-- 右外连接查询
-- 查询所有的订单信息和订单所属的用户信息
SELECT 
	u.name,
	o.*
FROM 
	USER u
RIGHT OUTER JOIN 
	orderlist o
ON
 	o.uid = u.id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

12.6.4 子查询

1.子查询概念

查询语句中嵌套了查询语句，我们就将嵌套的查询称为子查询。

2.子查询分类

①结果是单行单列的
查询作用：
可以将查询的结果作为另一条语句的查询条件，可以使用运算符进行判断。= > >= < <= 等。
查询语法
SELECT 列名 FROM 表名 WHERE 列名=（SELECT FROM 表名 [WHERE 条件]）；

-- 查询年龄最高的用户姓名
SELECT NAME，age FROM USER WHERE age=(SELECT MAX(age) FROM USER ); 
1
2

② 结果是多行单列的
查询作用：
可以作为条件，使用运算符IN 或NOT IN 进行判断。
查询语法：
SELECT 列名 FROM 表名 WHERE 列名[NOT] IN （SELECT 列名 FROM 表名 [WHERE 条件]）；

SELECT * FROM orderlist WHERE id IN (SELECT id FROM USER WHERE NAME IN ('张三'，'李四'));
1

③结果是多行多列的
查询作用
查询的结果可以作为一张虚拟表参与查询。
查询语法
SELECT 列名 FROM 表名[别名]，（SELECT 列名 FROM [WHERE 条件]）[别名] [WHERE 条件];

-- 查询订单表中id大于4的订单信息和所属的用户信息
SELECT 
	u.name,
	o.number
FROM 
	USER u，
	(SELECT * FROM orderlist WHERE id > 4) 0
WHERE 
	 o.uid = u.id;
1
2
3
4
5
6
7
8
9

12.7 自关联查询

自关联查询：在同一张表中的数据有关联性，我们可以把这张表当成多张表来查询。

-- 查询所有员工的姓名及其直接上级的姓名，没有上级的员工也需要查询
/*
   员工信息表 employee表
   条件  employee.mgr = employee.id
   查询左表的全部数据，和左右两张表的有交集部分的数据，左外连接
*/
SELECT 
	e1.id.
	e1.name,
	e1.mgr,
	e2.id,
	e2.name
FROM 
	employee e1
LEFT OUTER JOIN 
	employee e2
WHERE 
	e1.mgr = e2.id
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

13.视图

1. 视图概念

   是一种虚拟存在的数据表，这个虚拟表并不在数据库中实际存在。

2. 视图作用

   将一些较为负载的查询语句的结果，封装到一个虚拟表中，后期再有相同需求是，直接查询该虚拟表即可。

13.1 视图的创建和查询

创建视图语法

CREATE VIEW 视图名称 [(列名列表)] AS 查询语句；

查询试图语法

SELECT * FROM 视图名称；

-- 创建city_country 视图 保存城市和国家的信息（使用指定列名）
CREATE VIEW  city_country (city_id,city_name,country_name) AS
SELECT 
	c1.id,
	c1.name,
	c2.name
FROM  
	city c1,
	country c2
WHERE 
	c1.cid = c2.id;
1
2
3
4
5
6
7
8
9
10
11

13.2 视图的修改和删除

修改视图数据语法：

UPDATE 视图名称 SET 列名=值 WHERE 条件；
1

修改视图结构语法：

ALTER VIEW 视图名称(列名列表) AS 查询语句；
1

删除试图语法：

DROP VIEW [IF EXISTS] 视图名称；
1

14.数据库的备份

14.1 命令行方式

备份：登录到MYSQL服务器，输入：

mysqldump -u root -p 数据库名称 > 文件保存路径
1

恢复：

1.登录MYSQL数据库

2.删除已经备份的数据库

3.重新创建名称相同的数据库

4.使用该数据库

5.导入文件执行：source 备份文件全路径

15.存储过程和存储函数

存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合。

15.1 存储过程和函数的好处

提高代码的复用性。

减少数据在数据库和应用服务器之间的传输，提高效率

减少代码层面的业务处理

15.2 两者区别

存储函数必须有返回值，存储过程可以没有返回值。

15.3 存储过程的创建、调用、查看和删除

创建：

-- 语法：
DELIMITER $
CREATE PROCEDURE 存储过程名称(参数列表)
BEGIN
	SQL 语句列表;
END$
-- 修改分隔符为分号
DELIMITER;

-- 创建stu_group()存储过程，封装 分组查询总成绩，并按照总成绩升序排的功能。

DELIMITER $
CREATE PROCEDURE stu_group()
BEGIN
	SELECT gender,SUM(score) getSum FROM student GROUP BY gender ORDER BY getSum ASC;
END $
DELIMITER;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

调用：

-- 语法：
CALL 存储过程名称(实际参数);

-- 调用stu_group()存储过程
CALL stu_group();
1
2
3
4
5

查看：

-- 语法：
-- 查看数据库中所有的存储过程
SELECT * FROM mysql.proc WHERE db='数据库名称';

-- 查看db6数据库中所有的存储过程
SELECT * FROM mysql.proc WHERE db='db6';
1
2
3
4
5
6

删除

-- 语法
DROP PROCEDURE [IF EXISTS] 存储过程名称

DROP PROCEDURE [IF EXISTS] stu_group
1
2
3
4

15.4存储过程语法

变量（定义、赋值、使用和删除）

-- 定义变量
DECLARE 变量名 数据类型 [DEFAULT 变量值]

-- 语法使用都在存储过程中BRGIN 与END 之间


-- 定义变量
DECLARE num INT DEFAULT 10;
-- 使用变量
SELECT num;

-- 变量赋值方式1
SET  变量名 = 变量值；

-- 定义一个varchar类型变量并赋值
DELARE NAME VARCHAR(10);
SET NAME = '字符串';
SELECT NAME;

-- 3.变量赋值方式二
SELECT 列名 INTO 变量名 FROM 表名 [WHERE 条件]；

-- 定两个变量
DECLARE men,women INT;
-- 查询男同学的总分数，为men赋值
SELECT SUM(score) INTO men FROM student WHERE gender='男';
-- 查询女同学的总分数，为women赋值
SELECT SUM(score) INTO women FROM student WHERE gender='女';
-- 使用变量
SELECT men,women;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

15.5 存储过程IF语句

-- If语句标准语法
     IF 判断语句 THEN 执行的sql语句;
     [ELSE IF 判断语句2 THEN 执行的sql语句2；]
     ...
     [ELSE 执行的sql语句n；]
     END  IF；
-- 定义一个int变量，用于存储班级总成绩
-- 定义一个varchar变量，用于存储分数秒数
-- 根据总成绩判断：
-- 380及以上 学习优秀
-- 320~380 学习不错
-- 320以下 学习一般

DELIMITER $
CREATE PROCEDURE pro_test()
BEGIN
	-- 定义变量
	DECLARE total INT;
	DECLARE info VARCHAR(10);
	-- 查询总成绩，为total赋值
	SELECT SUM(score) INTO total FROM student;
	-- 对总成绩判断，为info赋值
	IF total > 380 THEN 
		SET info = '学习优秀';
	ELSEIF total >=320 AND total <+ 380 THEN
		SET info = '学习不错';
	ELSE
		SET info = '学习一般';
	END IF;
	-- 查询总成绩和描述信息
	SELECT total,info;
END$
DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

15.6 存储过程-参数传递

存储过程中的参数的传递及返回值都需要经过参数进行传递

-- 存储过程的参数和返回值
          语法：CREATE PROCEDURE  存储过程名称（[IN][OUT][INOUT] 参数名 数据类型）
                   BEGIN
                       SQL语句列表；
                   END $
            IN：代表输入参数，需要由调用者传递实际数据（默认）；
            OUT：代表输出参数，改参数可以作为返回值；
            INOUT：代表既可以作为输入参数，也可以作为输出参数。
/*
		输入总成绩变量，代表学生总成绩
		输出分数描述变量，代表学生总成绩的描述信息
	    根据总成绩判断
	    -- 380及以上 学习优秀
	    -- 320~380 学习不错
		-- 320以下 学习一般
*/

-- 定义存储过程
DELIMITER $
CREATE PROCEDURE pro_test(IN total INt,OUT info VARCHAR(10))
BEGIN
	-- 对总成绩判断，为info赋值
	IF total > 380 THEN 
		SET info = '学习优秀';
	ELSEIF total >=320 AND total <+ 380 THEN
		SET info = '学习不错';
	ELSE
		SET info = '学习一般';
	END IF;
END$
DELIMITER ;

-- 调用带返回值存储过程
CALL pro_test(350,@info);

CALL pro_test((SELECT SUM(score) FROM student),@info);

SELECT @info;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

15.7 存储过程语法 while循环

-- 存储过程语法-while循环
     While循环语法
        初始化语句；
        WHILE 条件判断语句 DO
               循环体语句；
               条件控制语句；
        END WHILE；
        
-- 计算1-100之间的偶数和
DELIMITER $
CREATE PROCEDURE pro_test()
BEGIN
	-- 定义求和变量
	DECLARE result INT DEFAULT 0;
    -- 定义初始化变量
    DECLARE num INT DEFAULT 1;
	-- while循环
	WHILE num <=100 DO
		IF num % 2 = 0 THEN
			SET result = resuly + num;
		END IF;
		SET num = mum + 1;
	END WHILE;
	-- 查询求和结果
	SELECT result;
END$
DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

15.8 存储函数

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

**2.**创建存储函数

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

-- 定义存储函数，获取学生表中成绩大于95分的学生数量
DELIMITER $
CREATE FUNCTION fun_test（）
RETURNS INT
BEGIN 
	-- 定义变量
	DECLARE s_count INT;
	-- 查询成绩大于95分的数量，为s_count赋值
	SELECT COUNT(*) INTO s_count FROM student WHERE score > 95;
	-- 返回统计结果
	RETURN s_count;
	END $
DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

**3.**调用存储函数

SELECT 函数名称（实际参数）；
1

**4.**删除存储函数

DROP FUNCTION 函数名称；
1

16.触发器

触发器介绍

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

16.1 创建触发器语法：

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


-- 创建日志表 account_log
CREATE TABLE account_log(
		id INT PRIMARY KEY AUTO_INCREMENT; -- 日志id
    	operation VARCHAR(20),              -- 操作类型（insert，update delete）
    	operation_time DATETIME,            -- 操作时间
    	operation_id INT,                   -- 操作表的id
    	operation_params VARCHAR(200)       -- 操作参数
);

-- 创建INSERT型触发器，用于对account表新增数据进行日志的记录
DELIMITER $
CREATE TRIGGER account_insert（）
AFTER INSERT
ON account
FOR EACH ROW
BEGIN 
	INSERT INTO account_log VALUES(NULL,'INSERT',NOW(),new.id,CONCAT('插入后{id=',new.id,',name=',new.name,',money=',new.money,','));
	END $
DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

UPDATE与DELETE型触发器只需要修改对应的触发器名称及SQL语句中的某些字段即可。

16.2 查看触发器

SHOW TRIGGERS;
1

16.3 删除触发器

DROP TRIGGER 触发器名称;
1

17.事务

17.1 事务概述

事务：一条或者多条SQL语句组成一个执行单元，特点是整个单元要么同时成功要么同时失败。

单元中的每条SQL语句都相互依赖，形成一个整体。

如果某条SQL语句执行失败或者出现错误，name整个单元就会刹车会事务最初的状态。

如果单元中的所有SQL语句都执行成功，则事务就顺利进行。

17.2 事务操作

开启事务

START TRANSACTION;
1

回滚事务

ROLLBACK
1

提交事务

COMMIT
1

17.3 查看事务的提交方式及更改

查询事务的提交方式

SELECT @@autocommit;
1

修改事务的提交方式

SET @@autocommit = 数字;
1

–1 代表自动提交

– 0 代表手动提交

17.4 事务的四大特征

一、原子性（atomicity)

一个事务要么全部成功，要么全部失败回滚，不能只执行其中的一部分操作，这就是事务的原子性

因此事务的操作如果成功就必须完全应用到数据库，如果操作失败就不能对数据库有任何影响。

二、一致性（consistency)

事务的执行不能破坏数据库数据的完整性和一致性，一个事务在执行之前和执行之后，数据库都必须处于一致性状态。

如果数据库系统在运行过程中发生故障，有些事务尚未完成就被迫中断，这些未完成的事务对数据库所作的修改有一部分已写入物理数据库，此时数据库就处于一种不正确的状态，也就是不一致的状态。

三、隔离性（isolation）

事务的隔离性是指在并发环境中，并发的事务时相互隔离的，一个事务的执行不能不被其他事务干扰。不同的事务并发操作相同的数据时，每个事务都有各自完成的数据空间，即一个事务内部的操作及使用的数据对其他并发事务时隔离的，并发执行的各个事务之间不能相互干扰。

四、持久性（durability）

一旦事务提交，那么它对数据库中的对应数据的状态的变更就会永久保存到数据库中。–即使发生系统崩溃或机器宕机等故障，只要数据库能够重新启动，那么一定能够将其恢复到事务成功结束的状态

17.5 事务的隔离级别及查询和修改

在标准SQL规范中，定义了4个事务隔离级别，不同的隔离级别对事务的处理不同，分别是：读未提交、读已提交、可重复读和串行化

引发的问题：

查询数据库隔离级别

SELECT @@tx_isolation;
1

修改数据库隔离级别(修改之后需要重新连接)

SET GLOBALTRANSACTION ISOLATION LEVEL 级别字符串;
1

17.6 脏读、不可重复读、幻读问题解决

1. 脏读问题解决：设置事务的隔离级别为读READ COMMITED；
2. 不可重复读问题解决：设置事务的隔离级别为读REPEATABLE READ；
3. 幻读问题解决：设置事务的隔离级别为读SERIALIZABLE；
4. Oracle数据库默认隔离级别是read committed；mysql默认是 repeatable read；
5. 隔离级别从上到下安全性越来越高，但是效率越来越低，所以一般不建议修改数据库默认的隔离级别。

##18.MYSQL 体系结构

1.客户端连接

支持接口：支持的客户端连接，例如C、Java、PHP等语言来连接MySQL数据库

2.第一层：网络连接层

连接池：管理、缓冲用户的连接、线程处理的等需要缓存的需求

3.第二层：核心服务层

管理服务和工具：系统的管理和控制工具、例如备份恢复、复制、集群等

SQL接口：接受SQL命令，并返回查询结果

查询解析器：验证和解析SQL命令，例如过滤条件、语法结构等

查询优化器：在执行查询之前，使用默认的一套优化机制进行优化SQL语句

缓存：如果缓存中有想要查询的数据，则直接将缓存中的数据返回，没有的话再重新查询

4.第三层：存储引擎层

插件师存储引擎：管理和操作数据的一种机制，包括（存储数据、如何更新、查询数据等）

5.第四层：系统文件层

文件系统：配置文件、数据文件、日志文件、错误文件、二进制文件等等的保存

19.存储引擎

19.1 存储引擎介绍

MySQL数据库使用不同的机制存取表文件，包括存储方式、索引技巧、锁定水平等不同的功能。这些不同的技术以及配套的功能称为存储引擎。

Oracle、SqlServer等数据库只有一种存储引擎，而MySQl针对不同的需求、配置不同的存储引擎，就会让数据库采用不等处理数据的方式和扩展功能。

MySQL支持的存储引擎很多，常用的有：InnoDB，MyISAM、MEMORY

特性对比：

​ MyISAM：访问快、不支持事务和外键操作

​ InnoDB：支持事务和外键操作、支持并发控制、占用磁盘空间大、支持表锁和行锁

​ MEMORY：内存存储、速度快、不安全。适合小量快速访问的数据。

19.2 存储引擎相关操作

查询数据库支持的存储引擎

SHOW ENGINES;
1

查询某个数据库中所有数据表的存储引擎

SHOW TABLE STATUS FROM 数据库名称;
1

查询某个数据库中某个数据表的存储引擎

SHOW TABLE STATUS FROM 数据库名称 WHERE NAME = '数据表名称';
1

创建数据表，指定存储引擎

CREATE TABLE 表名(
	类名 数据类型,
	......
) ENGINE = 引擎名称;
1
2
3
4

修改数据表的存储引擎

ALTER TABLE 表名 ENGINE = 引擎名称;
1

19.3 存储引擎适用场景及选择

MyISAM：

​ 特点：不支持事务和外键操作。读取速度快，节约资源

​ 适用场景：以查询操作为主，只有很少的更新和删除操作，并且对事务的完成性、并发性要求不高。

InnoDB：

​ 特点：MySQL的默认存储引擎，支持事务和外键操作。

​ 适用场景：对事物的完整性有比较高的要求，在并发条件下要求数据的一致性，读写频繁的操作

MEMORY：

​ 特点：将所有的数据保存在内存中，在需要快速定位记录和其他类似数据环境下，可以提动更快的访问。

​ 适用场景：通常用于更新不太频繁的小表，用于快速得到访问的结果。

总结：根据使用场景选择合适的存储引擎，如果不确定，使用数据库默认的存储引擎即可。

20.索引

20.1 索引介绍

MySQL索引是磅数MySQL高效获取数据的一种数据结构。所以，索引的本质就是数据结构。

在表数据之外，数据库系统好维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

20.2 索引分类

按照功能分类：

​ 普通索引：最基本的索引，没有任何限制

​ 唯一索引：索引列的值必须唯一，但是允许有空值，如果是组合索引，则列值组合必须唯一

​ 主键索引：一种特殊的唯一索引，不允许有空值，在建表时有主键列同时创建主键索引

​ 组合索引：顾名思义就是将单列索引进行组合

​ 外键索引：只有InnoDB引擎支持外键索引，用来保证数据的一致性、完整性和实现级联操作

​ 全文索引：快速匹配全部文档的方式，InnoDB引擎5.6版本之后才支持全文索引。MEMORY引擎不支持

按照结构分类：

​ BTree索引：MySQL使用最频繁的一个索引数据结构，是InnoDB和MyISAM存储引擎默认的索引类型。底层鲫鱼B+Tree数据结构。

​ Hash索引：MySQL中MEMORY存储引擎默认支持的索引类型。

20.3 索引的查询、创建、添加和删除

创建索引：

创建索引语法： CREATE [ UNIQUE | FULLTEXT ] INDEX  索引名称 [USING 索引类型] ----默认是BTREE 类型
              ON 表名(列名...);
 ------为哪张表的哪一列创建哪种类型的哪种结构的索引

UNIQUE  ---- 唯一索引
FULLTEXT -----全文索引
不写表示为普通索引
列名为多个时为组合索引
索引名称一般以idx_开头（index缩写）

-- 为student表中的name列创建一个普通索引
CREATE INDEX idx_name ON student(name);

-- 为student表中的name列创建一个唯一索引
CREATE UNIQUE INDEX idx_age ON student(age);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

查看索引

SHOW INDEX FROM 表名;
1

注：主键列自带主键索引，外键列自带外键索引。

添加索引

普通索引：ALTER TABLE表名 ADD INDEX 索引名称(列名);
组合索引：ALTER TABLE表名 ADD INDEX 索引名称(列名1,列名2,..);
主键索引：ALTER TABLE表名 ADD PRIMARY KEY(z主键列名);  -- 添加主键自动添加主键索引
外键索引：ALTER TABLE表名 ADD CONSTRAINT 外键名 FOREIGN KEY(本表外键列名) REFERENCES 主表名(主键列名);
唯一索引：ALTER TABLE表名 ADD UNIQUE 索引名称(列名);
全文索引：ALTER TABLE表名 ADD FULLEST 索引名称(列名);
1
2
3
4
5
6

删除索引

DROP INDEX 索引名称 ON 表名;
1

20.4 索引的原理

索引是在存储引擎中实现的，不同的存储引擎支持的索引也不一样。

BTree索引类型是基于B+Tree数据结构的，而B+Tree数据结构是BTree数据结构的变种。通常使用在数据库和操作系统中的文件系统，特点是能够保持数据稳定有序。

需要理解的：

​ 磁盘存储、B+Tree、 BTree。

20.4.1 索引原理-磁盘存储

系统从磁盘读取数据到内存中时是以磁盘块（block）为及本单位的。

位于同一块磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。

InnoDB存储引擎中有页（Page）的概念，页是InnoDB存储引擎进行磁盘管理的最小单位，默认每个页的大小是16kb。

InnoDB存储引擎会将若干个地址连接磁盘块，以此来达到页的16kb大小，在查询数据是，如果一个页中的每条数据都能有助于定位数据记录的位置，将会减少磁盘IO次数，提高查询效率。

20.4.2 索引原理- BTree

BTREE数据结构索引原理：例寻找数据15，

1. 读取磁盘块1中的所有内容，与其中数据对比，得知比17小，读取指针c2；
2. C2指针只想了磁盘块2，读取磁盘块2中的所有内容，比12大，读取指针c7；
3. 读取磁盘块c7，读取所有内容，找到想要的数据15；寻找数据完毕。

BTREE数据结构查询较慢，所以有了B+TREE数据结构。

20.4.3 索引的原理-B+Tree

BTree数据结构：

每个节点不仅包含key值，还有数据，会增加查询数据时磁盘的IO次数

B+Tree数据结构

非叶子节点只存储key值，所有数据存储在叶子结点，所有叶子结点之间都有连接指针。

B+Tree好处：

提高查询速度，减少磁盘的IO数量，树形结构较小。

B+TREE数据结构寻找数据类型：非叶子节点存key值，寻找数据时，在非叶子节点上与key值进行比较，不需要读取数据，直到寻找的符合条件的叶子结点，最后在存储数据的叶子结点中读取一次数据即可，且所有的叶子节点中都有连接指针，可以更加方便的查询数据。

20.5 索引的设计与使用原则

创建索引遵循的原则：

​ 1.对查询频次较高、且数据量比较大的表建立索引

​ 2.使用唯一索引，区分度越高，使用索引的效率越高

​ 3.索引字段的选择，最佳候选列应当从where子句的条件中提取

​ 4.索引虽然可以有效的提高查询的效率，但是并不是多多益善

使用：

最左匹配原则（适用于组合索引）

例如：为user表中的name、address、phone列添加组合索引

ALTER TABLE user ADD INDEX idx_three(name,address,phone);
1

此时，组合索引id_three实际上建立了（name）（name,address）（name,address,phone）三个索引。

下面的三个SQL语句都可以命中索引

SELECT * FROM user WHERE address='北京' AND phone= '12345' AND name='张三';
SELECT * FROM user WHERE name='张三' AND address='北京';
SELECT * FROM user WHERE name='张三';
1
2
3

这三条SQL语句在检索时分别会使用以下索引进行数据匹配

（name,address,phone）（name,address） （name）

索引字段出现的顺序可以是任意的，MySQL优化器会帮助我们自动调整where条件中的顺序

如果组合索引中最左侧的列不在查询条件中吗，则不会命中索引，例：

SELECT * FROM user WHERE address='北京';
1

21.锁

21.1 锁介绍

锁机制：数据库为了保证数据的一致性，在共享的资源被并发访问时变得安全所设计的一种规则。

随机值类似多线程中的同步，作用就是可以保证数据的一致性和安全性。

21.2 锁分类

按操作分类

​ 共享锁：也叫读锁。针对同一份数据，多个市属读取操作可以同时加锁但是不会互相影响。但是不能修改数据。

​ 排它锁：也叫写锁。当前的操作没有完成时，会阻断其他操作的读取和写入

按粒度分类

​ 表级锁：会锁定整张表，开销小，加锁快。锁定粒度大，发生锁冲突的概率高，并发度低。不会出现死锁情况。

​ 行级锁：会锁定当前行。开销大，加锁慢。锁定粒度小，发生锁冲突的概率小，并发度高，会出现死锁现象。

按使用方式分类

​ 悲观锁：每次查询数据是都认为别人会修改，很悲观，所以查询时加锁。

​ 乐观锁：每次查询数据时都认为别人不会修改，很乐观，但是更新时会判断一下在此期间别人有没有更新这个数据。

21.3 不同存储引擎支持的锁

注意：使用锁之前要开启事务（START TRANSACTION），使用完之后要提交事务(COMMIT)

###21.4InnoDB读锁（共享锁）

特点：数据可以被多个事务查询，但是不能修改

创建共享锁格式

SELECT 语句 LOCK IN SHARE MODE;
1

注意：对于带索引的列，INNODB引擎添加共享锁时默认添加行锁；如果是不带索引的列加锁，加的就是表锁；

21.5 InnoDB排它锁

特点：加锁的数据，不能被其他事物加锁查询和修改，排斥其他类型的锁(包括自己)，只允许普通形式的查询和修改。

创建排它锁格式

  SELECT 语句 FOR UPDATE;
1

21.6 MyISAM读锁和写锁

21.6.1 MYISAM引擎读锁特点：

所有连接只能查询数据，不能修改。（要想修改数据，必须等待加锁的用户解锁）

（MYISAM引擎支持表锁，不支持行锁，且不支持事务）

21.6.2 读锁语法格式

 --  加锁：
  LOCK TABLE 表名 READ；
  
 --  解锁：
  UNLOCK TABLESS； 
1
2
3
4
5

21.6.3 写锁特点：

其他链接不能查询和修改数据（当前连接可以进行查询和修改数据）

21.6.4 写锁语法格式：

 -- 加锁：
  LOCK TABLE 表名 WRITE；

  -- 解锁：
  UNLOCK TABLES；
1
2
3
4
5

21.6.5 悲观锁和乐观锁

悲观锁：就是很悲观，对于数据被外界修改的操作保持保守态度，认为数据会随时修改。整个数据处理过程中需要将数据加锁，悲观锁一般都是依靠关系型数据库提供的锁机制。之前的锁机制都是悲观锁。

乐观锁：就是跟乐观，每次自己操作数据的时候人为没有人来修改它，所以不去加锁。但是在更新的时候会判断在此期间数据有没有被修改。

需要用户自己实现，不会发生并发抢占资源，只有在提交操作的时候检查是否违反数据完整性。

-- 将北京更改为北京市
-- 1.讲北京的版本号读取出来
SELECT VERSION FROM city WHERE NAME='北京';  -- 1

-- 2.修改北京为北京市，版本号 +1 ，并对比版本号是否相同
UPDATE city SET NAME='北京市',VERSION=VERSION+1 WHERE NAME='北京' AND VERSION=1;
1
2
3
4
5
6

锁。针对同一份数据，多个市属读取操作可以同时加锁但是不会互相影响。但是不能修改数据。

​ 排它锁：也叫写锁。当前的操作没有完成时，会阻断其他操作的读取和写入

按粒度分类

​ 表级锁：会锁定整张表，开销小，加锁快。锁定粒度大，发生锁冲突的概率高，并发度低。不会出现死锁情况。

​ 行级锁：会锁定当前行。开销大，加锁慢。锁定粒度小，发生锁冲突的概率小，并发度高，会出现死锁现象。

按使用方式分类

​ 悲观锁：每次查询数据是都认为别人会修改，很悲观，所以查询时加锁。

​ 乐观锁：每次查询数据时都认为别人不会修改，很乐观，但是更新时会判断一下在此期间别人有没有更新这个数据。

21.3 不同存储引擎支持的锁

注意：使用锁之前要开启事务（START TRANSACTION），使用完之后要提交事务(COMMIT)

###21.4InnoDB读锁（共享锁）

特点：数据可以被多个事务查询，但是不能修改

创建共享锁格式

SELECT 语句 LOCK IN SHARE MODE;
1

注意：对于带索引的列，INNODB引擎添加共享锁时默认添加行锁；如果是不带索引的列加锁，加的就是表锁；

21.5 InnoDB排它锁

特点：加锁的数据，不能被其他事物加锁查询和修改，排斥其他类型的锁(包括自己)，只允许普通形式的查询和修改。

创建排它锁格式

  SELECT 语句 FOR UPDATE;
1

21.6 MyISAM读锁和写锁

21.6.1 MYISAM引擎读锁特点：

所有连接只能查询数据，不能修改。（要想修改数据，必须等待加锁的用户解锁）

（MYISAM引擎支持表锁，不支持行锁，且不支持事务）

21.6.2 读锁语法格式

 --  加锁：
  LOCK TABLE 表名 READ；
  
 --  解锁：
  UNLOCK TABLESS； 
1
2
3
4
5

21.6.3 写锁特点：

其他链接不能查询和修改数据（当前连接可以进行查询和修改数据）

21.6.4 写锁语法格式：

 -- 加锁：
  LOCK TABLE 表名 WRITE；

  -- 解锁：
  UNLOCK TABLES；
1
2
3
4
5

21.6.5 悲观锁和乐观锁

悲观锁：就是很悲观，对于数据被外界修改的操作保持保守态度，认为数据会随时修改。整个数据处理过程中需要将数据加锁，悲观锁一般都是依靠关系型数据库提供的锁机制。之前的锁机制都是悲观锁。

乐观锁：就是跟乐观，每次自己操作数据的时候人为没有人来修改它，所以不去加锁。但是在更新的时候会判断在此期间数据有没有被修改。

需要用户自己实现，不会发生并发抢占资源，只有在提交操作的时候检查是否违反数据完整性。

-- 将北京更改为北京市
-- 1.讲北京的版本号读取出来
SELECT VERSION FROM city WHERE NAME='北京';  -- 1

-- 2.修改北京为北京市，版本号 +1 ，并对比版本号是否相同
UPDATE city SET NAME='北京市',VERSION=VERSION+1 WHERE NAME='北京' AND VERSION=1;
1
2
3
4
5
6