MySQL详细讲解(基础篇)

前言:

📣📣📣 花了几天时间整理了一下MySQL基础篇的知识,全文4万字左右,涉及MySQL基础篇所有知识点,整理图书<>,<<尚硅谷MySQL数据库基础篇笔记>>,<<黑马程序员MySQL 基础语法>>,如果觉得对你有帮助请给博主一个三连 []_(￣▽￣)* 。

1.Mysql服务和登入

(1) 服务的关闭和启动

在Windows下启动Mysql本质就是在启动服务。

• 查看Windows当前所有服务: services.msc

• 关闭Mysql服务

使用 net stop MySQL56 命令

执行后结果如下:

• 启动Mysql服务

使用 net start MySQL56 启动Mysql服务

执行后结果如下:

(2) 登入Mysql(命名行模式下)

登入方式分为本地登入和远程登入,其中远程登入需要在数据库中开启远程访问权限,如何开启权限请查看权限章节的内容。

语法格式:

mysql –h hostname|hostIP –P port –u username –p DatabaseName –e "SQL语句"
1

下面详细介绍命令中的参数：

举例:

mysql -uroot -p -hlocalhost -P3306 mysql -e "select host,user from user"
1

运行效果:

练习:

本地登入:

• 密码可见:mysql -u 账号 -p 密码

• 密码不可见方式:mysql -u 账号 -p

远程登入:

• mysql -h Ip地址 -p 端口号 -u 账号 -p 密码

2. Mysql语法规则

• 每条语句以分号结尾 ";"，如果在 SQLyog 中不是必须加的。
• SQL 中不区分大小写，关键字中认为大写和小写是一样的
• 3 种注释：

3.SQL语句五种分类

• DQL:数据库查询语言(SELECT…)
• DML:数据库操作语言(insert,delete,update…)
• DDL:数据库定义语言(create,drop,alter…)
• TCL:事务控制语言(commit,rollback…)
• DCL:数据控制语言(grant,revoke…)

4. 数据库DDL的常用命令(操作数据库)

(1) 数据库的查询,选择和退出

(2) 数据库的增,删,改

下表中提到的数据库字符集如gbk,utf8,校对规则如utf8_chinese_ci等,至于其他更多字符集编码你可使用 SHOW CHARACTER SET; 进行查询,更多校对规则你可以使用 SHOW COLLATION; 进行查询,更详细的操作请查询官方文档https://dev.mysql.com/doc/refman/5.7/en/charset-mysql.html。

• 使用 SHOW CHARACTER SET; 查询字符集

下图中的 Charset列为字符集,Default collation为默认字符集的校对规则。

• 使用 SHOW COLLATION; 查询校对规则

下图中的Collation为字符集的校对规则: Charset列为对应字符集。

(3) 数据库的备份与还原

数据库备份文件类型以.sql为后缀进行保存简称sql脚本

数据库的备份

练习:

• mysqldump -uroot -proot test>C:\Users\Administrator\Desktop\db\test.sql
• mysqldump -uroot -proot --database test user>C:\Users\Administrator\Desktop\db\test2.sql
• mysqldump -uroot -proot --all-databases>C:\Users\Administrator\Desktop\db\test3.sql
  

导出结果:

备注:使用 --all-databases参数备份了所有数据库,那么在还原数据库时,不需要创建数据库或指定数据库,因为在生成的SQL文件中包含了数据库的创建语句。

数据库还原

备注:dbname表示还原数据库名,在脚本中不包含创建数据库的语句时使用,包含时则不需要指定数据库名。

练习:

• source 路径\xxx.sql
  
  导入结果:
  

备注:数据库的还原是还原数据库中的数据,而不是数据库,所以在还原的时候先删除原数据库然后再新建数据库,最后在从备份脚本中还原数据。

5. 数据库DDL的常用命令(操作表)

表的操作需要在具体的数据库下才可以进行操作,使用 use 数据库名; 选择数据库。

(1) 表的查询

(2) 表的创建

创建一张全新的表:

• 创建表的格式

CREATE TABLE [IF NOT EXISTS] 表名(
字段1, 数据类型 [约束条件] [默认值],
字段2, 数据类型 [约束条件] [默认值],
字段3, 数据类型 [约束条件] [默认值],
……
[表约束条件]
)ENGINE = 引擎 DEFAULT CHARACTER SET 编码;
1
2
3
4
5
6
7

• 表的字段数据类型

• 约束条件

表级约束条件:

练习:

创建一张部门表和一张员工表

DROP TABLE If EXISTS dept;
CREATE TABLE IF NOT EXISTS dept( #主表
did int primary key, #部门编号
dname varchar(50) NOT NULL #部门名称
)ENGINE = INNODB DEFAULT CHARACTER SET utf8;



DROP TABLE If EXISTS emp;
CREATE TABLE IF NOT EXISTS emp(#从表
eid int primary key,  #员工编号
ename varchar(5),   #员工姓名
deptid int, #员工所在的部门
  foreign key (deptid) references dept(did)  #在从表中指定外键约束
  #emp表的deptid和和dept表的did的数据类型一致，意义都是表示部门的编号
)ENGINE = INNODB DEFAULT CHARACTER SET utf8;

desc dept;
desc emp;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

运行效果:

根据旧表快速创建一张新表:

只复制表结构,不复制表数据

CREATE TABLE 新表名 LIKE 旧表名;# 快速创建一个表结构相同的表
1

可选择字段,并且复制表数据

CREATE TABLE 新表名 AS SELECT 字段1,字段2,...  FROM 旧表名
1

练习:

CREATE TABLE class_new_like LIKE class;
CREATE TABLE class_new_as AS SELECT id,name FROM class;
SELECT * FROM class_new_like;
SELECT * FROM class_new_as;

1
2
3
4
5

运行效果:

(3) 表的删除

(3) 表的修改

6. 数据库DML的常用命令(操作表记录)

DML用于对表中的记录进行增删改操作,需要在相应的数据库下,使用 use 数据库名; 选择数据库。

(1) 记录的增(insert)

• 插入记录语法

• 插入记录

插入字段注意事项:

• 插入的数据应与字段的数据类型相同
• 数据的大小应在列的规定范围内，例如：不能将一个长度为 80 的字符串加入到长度为 40 的列中。
• 在values中列出的数据位置必须与被加入的列的排列位置相对应。在mysql中可以使用value，但不建议使
  用，功能与 values 相同。
• 字符和日期型数据应包含在单引号中。MySQL 中也可以使用双引号做为分隔符。
• 不指定列或使用 null，表示插入空值

蠕虫复制:

将一张已经存在的表中的数据复制到另一张表中,操作语法如下。

备注:表1为新表,表2为提供数据的表

(2) 记录的删(delete,truncate)

• 使用delete删除记录

• 使用 truncate 删除表中所有记录

• truncate 和 delete 的区别

truncate 相当于删除表的结构，再创建一张表,而delete只删除数据,并不删除结构。

(3) 记录的改(update)

7.数据库DQL的常用命令(查询表记录)

查询不会对数据库中的数据进行修改.只是一种显示数据的方式。

(1) 简单查询

语法格式:

不带条件查询:

• 语法格式

• 指定列的别名进行查询( 显示的时候使用新的名字，并不修改表的结构)

• 清除重复值(DISTINCT)

• 查询结果参与运算(参与运算的必须是数值类型)

带条件查询:

• 语法格式

• 使用运算符

备注:如果and和or同时使用的时候,按优先级高的先进行运算,即应该先运算AND二边的条件表达式。

算术运算符：

算术运算符主要用于数学运算，其可以连接运算符前后的两个数值或表达式，对数值或表达式进行加（+）、减（-）、乘（*）、除（/）和取模（%）运算。

位运算符：

位运算符是在二进制数上进行计算的运算符。位运算符会先将操作数变成二进制数，然后进行位运算，最后将计算结果从二进制变回十进制数。

运算符的优先级：

数字编号越大，优先级越高，优先级高的运算符先进行计算。可以看到，赋值运算符的优先级最低，使用“()”括起来的表达式的优先级最高。

扩展使用正则表达式:

正则表达式通常被用来检索或替换那些符合某个模式的文本内容，根据指定的匹配模式匹配文本中符合要求的特殊字符串。例如，从一个文本文件中提取电话号码，查找一篇文章中重复的单词或者替换用户输入的某些敏感词语等，这些地方都可以使用正则表达式。正则表达式强大而且灵活，可以应用于非常复杂的查询。

MySQL中使用REGEXP关键字指定正则表达式的字符匹配模式。下表列出了REGEXP操作符中常用字符匹配列表

练习:

#在fruits表中，查询f_name字段以字母‘b’开头的记录:
SELECT * FROM fruits WHERE f_name REGEXP '^b';
1
2

• 使用关键字

IS [NOT] NULL关键字

备注:在 MySQL 里面,空值不等于空字符串,一个空字符串的长度是 0,而一个空值的长度是空.而且,在 MySQL 里面,空值是占用空间的。

• 使用通配符

备注:如果查询的数据中有字符%或_,需要使用转义字符,如"/%" 表示"%","/_" 表示_。

练习:

创建一个学生表student3 ，并插入几条记录

CREATE TABLE student3 (
id int, -- 编号
name varchar(20), -- 姓名
age int, -- 年龄
sex varchar(5), -- 性别
address varchar(100), -- 地址
math int, -- 数学
english int -- 英语
);

INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES (1,'马云',55,'男','
杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),(3,'马景涛',55,'男','香港',56,77),(4,'柳岩
',20,'女','湖南',76,65),(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港
',99,99),(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

使用逻辑运算符查询相关操作:

查询 math 分数大于 80 分的学生

select * from student3 where math>80;
1

查询 english 分数小于或等于 80 分的学生

select * from student3 where english <=80;
1

查询 age 等于 20 岁的学生

select * from student3 where age = 20;
1

查询 age 不等于 20 岁的学生，注：不等于有两种写法

select * from student3 where age <> 20;
select * from student3 where age != 20;
1
2

使用相关逻辑运算符相关操作:

查询 age 大于 35 且性别为男的学生(两个条件同时满足)

select * from student3 where age>35 and sex='男';
1

查询 age 大于 35 或性别为男的学生(两个条件其中一个满足)

select * from student3 where age>35 or sex='男';
1

查询 id 是 1 或 3 或 5 的学生

select * from student3 where id=1 or id=3 or id=5;
1

使用关键字相关操作:

查询 id 是 1 或 3 或 5 的学生

select * from student3 where id in(1,3,5);
1

查询 id 不是 1 或 3 或 5 的学生

select * from student3 where id not in(1,3,5);`
1

查询 english 成绩大于等于 75，且小于等于 90 的学生

select * from student3 where english between 75 and 90;
1

使用通配符相关操作:

#查询姓马的学生

select * from student3 where name like '马%';
select * from student3 where name like '马';
1
2

#查询姓名中包含’德’字的学生

select * from student3 where name like '%德%';
1

#查询姓马，且姓名有两个字的学生

select * from student3 where name like '马_';
1

(2) 高级查询

语法格式:

使用函数进行查询(作用在列上)

函数分为内置函数和自定义函数,内置函数是系统内置的通用函数,而自定义函数是根据自己需求编写的函数,下面列出的函数是常用的Mysql内置函数,来源于尚硅谷Mysql视频https://www.bilibili.com/video/BV1iq4y1u7vj?p=32,当然更全更详细的查询还是要通过Mysql官网进行查询https://dev.mysql.com/doc/refman/5.7/en/built-in-function-reference.html( 注意这里给出的是mysql数据库的函数,不同数据库的函数存在一定差异,在其他数据库中可能无法使用 )。

聚合函数

练习:

# 查询student3中平均年龄
SELECT AVG(age)  as '平均年龄'
FROM student3;

# 查询年龄最小的学生信息
use test;
SELECT MIN(age) as '最小年龄'
FROM student3

# 查询年龄最大的学生信息
SELECT MAX(age) as '最大年龄'
FROM student3

# 一共有多少条记录
SELECT Count(*) as '总人数' FROM student3
# 学生英语总分
SELECT SUM(english) as '全班英语总共获得分数' FROM student3
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

数值型函数

• 基本函数

• 角度与弧度互换函数

• 三角函数

• 指数与对数

• 进制间的转换

练习:

#返回10的二进制编码
SELECT BIN(10);

#返回10的十六进制编码
SELECT HEX(10);

#返回10的八进制编码
SELECT OCT(10);

#返回2进制数变成10进制数
SELECT CONV(1010,2,10);

#返回10进制数变成2进制数
SELECT CONV(10,10,2);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

字符串函数

日期和时间函数

• 获取日期、时间

练习:

#返回当前日期，只包含年、月、日
SELECT CURDATE()  as '当前日期' 

#返回当前时间，只包含时、分、秒
SELECT CURTIME() as '当前日期' 

#返回当前系统日期和时间
SELECT NOW() as '当前系统时间'

#返回UTC（世界标准时间）日期
SELECT UTC_DATE();

#返回UTC（世界标准时间）时间
SELECT UTC_TIME();

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

• 日期与时间戳的转换

练习:

# 获取当前时间的时间戳
SELECT UNIX_TIMESTAMP();

# 获取当前时间的时间戳
SELECT UNIX_TIMESTAMP(NOW());

# 获取当前时间的时间戳
SELECT UNIX_TIMESTAMP(CURRENT_DATE());

# 获取当前时间的时间戳
SELECT FROM_UNIXTIME(CURRENT_TIME())
1
2
3
4
5
6
7
8
9
10
11

• 获取月份、星期、星期数、天数等函数

练习:

#获取当前年月日
SELECT YEAR(NOW()) as '年' ,MONTH(NOW()) as '月' , DAY(NOW()) as '日';

#获取当前时分秒
SELECT HOUR(NOW()) as '时' , MINUTE(NOW()) as '分' , SECOND(NOW()) as '秒';

1
2
3
4
5
6

• 日期的操作函数

EXTRACT(type FROM date)函数中type的取值与含义：

练习:

#获取NOW()中的日
SELECT EXTRACT(DAY FROM NOW()) as '获取当前的日';
#获取NOW()中的年月
SELECT EXTRACT(YEAR_MONTH FROM NOW()) as '获取当前的年和月';
1
2
3
4

• 时间和秒钟转换的函数

练习:

#将 time 转化为秒并返回结果值
SELECT TIME_TO_SEC(NOW());

#将 seconds 描述转化为包含小时、分钟和秒的时间
SELECT SEC_TO_TIME(85577);
1
2
3
4
5

• 计算日期和时间的函数

上述函数中type的取值：

• 日期的格式化与解析

上述 非GET_FORMAT 函数中fmt参数常用的格式符：

GET_FORMAT函数中date_type和format_type参数取值如下：

流程控制函数

IF操作练习:

SELECT IFNULL(NULL,'值为NULL');#值为NUL
SELECT IFNULL(1,'值不为NULL');#1
SELECT NULLIF(1,1);#null 
SELECT NULLIF(1,2); # 1
SELECT NULLIF(2,1); # 2
1
2
3
4
5

CASE操作练习:

语法方式1:
CASE WHEN condition THEN result [WHEN condition THEN result …] [ELSE result] END ( 相当于Java中的if...else if...else... )
语法方式2:
CASE value WHEN compare_value THEN result [WHEN compare_value THEN result …] [ELSE result] END ( 相当于Java的switch...case... )

#查询学生信息,但是学生的性别按1,0和-1进行显示,即1:男,0:女,-1:未知
SELECT id,name,
CASE sex
WHEN '男'  THEN 1 
WHEN '女' THEN 0 
ELSE -1
END as 'sex',
age,address,math,english
FROM student3;

#查询学生信息,但是学生的英语列显示合格(>=60 && <85),不合格(<60),优秀(>=85)
SELECT
id,name,age,sex,address,math,
CASE 
	WHEN english >= 60  && english <85 
	THEN '合格'
	WHEN english >= 85 
	THEN '优秀'		
	ELSE
		'不合格'
	END as '英语情况'
FROM student3;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

运行效果:

对查询结果进行排序(作用在行上)

从表中查询到的数据可能是无序的,或者不满足用户期望的,这时就可以使用 ORDER BY 子句进行排序,其中ASC（ascend）表示升序,DESC（descend）表示降序,mysql默认采用ASC升序方式。

语法格式如下:

SELECT 字段名1,字段名2,...
FROM 表名
ORDER BY 字段名1 [ASC| DESC] ,字段名2  [ASC| DESC]...
1
2
3

按排序的字段数量可分为单列排序和多列排序,单列排序表示按单个字段进行排序,多列排序表示按多个字段进行排序,相关规则如下:

• 可以使用不在SELECT列表中的列排序。
• 在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第
  一列数据中所有值都是唯一的，将不再对第二列进行排序。

练习:

# 按数学升序ASC进行排序【单列排序】
SELECT * FROM student3
ORDER BY math

# 按数学降序,英语降序进行排序【多列排序】
SELECT * FROM student3
ORDER BY math DESC ,english DESC
1
2
3
4
5
6
7

运行效果:

分组查询(作用在行上)

在表中数据进行统计时,有时需要按照一定的类别进行统计,如按性别统计班上男,女,null(性别可能的值为男,女,null)的学科成绩的平均分，这时候就可以使用GROUP BY语句进行分组查询(一般会和聚合函数搭配使用),如果分组查询需要相应条件使用HAVING，并且HAVING语句后面可以跟内置函数。

语法格式:

SELECT 字段名1,字段名2,...
FROM 表名
GROUP BY 字段1,字段2..  [HAVING 条件表达式]
1
2
3

单独使用:

# 按性别进行分组,查询用户信息,会按sex的值进行分组查询
INSERT INTO student3(id,name,age,sex,address,math,english) VALUES (9,'黑户',99,NULL,'NULL',99,99);
SELECT * FROM student3
GROUP BY SEX
1
2
3
4

搭配聚合函数一起使用:

# 分别统计班上男生和女生的数学和英语的平均分
SELECT sex,AVG(math) as '数学平均分', AVG(english) as '英语平均分'
FROM student3
GROUP BY SEX  
ORDER BY math DESC ,english DESC

# 分别统计班上男生和女生的数学和英语的平均分,并且均在80及以上
SELECT sex,AVG(math) as '数学平均分', AVG(english) as '英语平均分'
FROM student3
GROUP BY SEX  HAVING AVG(math) >= 80  and  AVG(english) >= 80
ORDER BY math DESC ,english DESC
1
2
3
4
5
6
7
8
9
10
11

运行效果:

分页查询(作用在行上)

查询数据时,可能会返回很多条记录,而用户需要记录可能只是其中的一条或者几条,这时就可以通过LIMIT进行分页查询,其中OFFSET表示设置偏移量,偏移量决定记录从第几条开始,默认偏移量为0。所谓分页显示，就是将数据库中的结果集，一段一段显示出来需要的条件。

语法格式:

SELECT 字段名1,字段名2,...
FROM 表名
LIMIT [OFFSET,] 记录数
1
2
3

单个参数查询

在mysql 5以后支持这种写法

#前3条记录[1,3]：
SELECT * FROM student3 LIMIT 3;
#第4至6条记录 [4,6]：
SELECT * FROM student3 LIMIT 3 OFFSET 3;
#第7至9条记录 [7,9]：
SELECT * FROM student3 LIMIT 3 OFFSET 6;
1
2
3
4
5
6

运行效果:

二个参数的查询(运行结果一致):

#前3条记录[1,3]：
SELECT * FROM student3 LIMIT 0,3;
#第4至6条记录 [4,6]：
SELECT * FROM student3 LIMIT 3,3;
#第7至9条记录 [7,9]：
SELECT * FROM student3 LIMIT 6,3;
1
2
3
4
5
6

备注: 第一个参数表示偏移量,第二个参数表示记录数

使用分页查询的好处:

约束返回结果的数量可以 减少数据表的网络传输量 ，也可以 提升查询效率 。如果我们知道返回结果只有 1 条，就可以使用 LIMIT 1 ，告诉 SELECT 语句只需要返回一条记录即可。这样的好处就是 SELECT 不需 要扫描完整的表，只需要检索到一条符合条件的记录即可返回。

(3) 多表查询

多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。
前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联。

笛卡尔积（交叉连接）

笛卡尔乘积是一个数学运算,可以把任意表进行连接,即使二张表不相关( 实际开发中很少使用 )。假设有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数。

语法格式:

SELECT * FROM 表1 CROSS JOIN 表2
# 或
SELECT * FROM 表1,表2
1
2
3

二张表的字段不相同

练习:

# 创建class表
use test;
DROP TABLE IF EXISTS class;
CREATE TABLE class(
id int, -- 编号
name varchar(20), -- 编辑名称
count int -- 班级人数
)DEFAULT CHARACTER SET utf8;

INSERT INTO class(id,name,count) VALUES (1,'1班',45),(2,'2班',50),(3,'3班',42),(4,'4班',36),(5,'5班',43)


# 使用笛卡尔积查询
SELECT *
FROM student3 CROSS JOIN class;
# 使用笛卡尔积查询(还上面的运行效果一致)
SELECT *
FROM student3,class;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

运行效果:

二张表的字段相同:

练习:

#创建一张和student3结构一样的表
USE test;
DROP TABLE IF EXISTS student4;
CREATE TABLE student4 (
id int, -- 编号
name varchar(20), -- 姓名
age int, -- 年龄
sex varchar(5), -- 性别
address varchar(100), -- 地址
math int, -- 数学
english int -- 英语
) DEFAULT CHARACTER SET utf8;

INSERT INTO student4(id,name,age,sex,address,math,english) VALUES (1,'马云',55,'男','
杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),(3,'马景涛',55,'男','香港',56,77),(4,'柳岩
',20,'女','湖南',76,65),(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港
',99,99),(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65);

# 使用笛卡尔积查询
SELECT * FROM student3,student4;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

运行效果:

接下里的所有操作都是在笛卡尔积的基础上进行进一步赛选,赛选出符合自己需求的查询。

内连接

内连接(Inner Join) 又称简单连接或自然连接（特殊的等值连接）,是一种常见的连接查询,内连接使用比较运算符对二张表的数据进行比较,并列出与连接条件匹配的数据行,组合成新的记录,

语法格式:
.

# 等值连接
SELECT 查询字段 FROM 表1 [INNER] JOIN 表2 ON 表1.关系字段 = 表2.关系字段

# 特殊等值连接(自然连接)
SELECT 查询字段 FROM 表1 NATURAL JOIN 表2 WHERE 表1.关系字段 = 表2.关系字段

# 非等值连接
SELECT 查询字段 FROM 表1 [INNER] JOIN 表2 ON 表1.关系字段 >或<或<=或>=或<> 表2.关系字段
1
2
3
4
5
6
7
8

备注:相同字段,使用 表名.字段名 进行区分。

内连接可简单分为等值连接和非等值连接,等值连接就是采用比较运算符里的 '=' 进行连接 ,非等值连接就是不采用 '=' 进行连接的连接,如(> 、< 、<= 、>=,<> )。

等值连接

# 查询表student3和student4交集部分的记录
SELECT *
FROM student3  s3 JOIN student3  s4
ON s3.id = s4.id
1
2
3
4

运行效果:

非等值连接

# 查询表student3和student4除了交集部分的记录
SELECT *
FROM student3  s3 JOIN student3  s4
ON s3.id <> s4.id
1
2
3
4

运行效果:

特殊的等值连接(自然连接)

自然连接(Naturaljoin)是一种特殊的等值连接，它要求两个关系中进行比较的分量必须是相同的属性组，并且在结果中把重复的属性列去掉。

相同属性组:

SELECT *
FROM student3  s3 NATURAL JOIN student4 s4
WHERE s3.id = s4.id
1
2
3

运行效果:

非相同属性组( 查询不出任何数据 ):

SELECT *
FROM student3  s3 NATURAL JOIN class c
WHERE s3.id = c.id
1
2
3

运行效果:

外连接:

内连接返回结果只包含符合查询添加和连接条件的数据,然而有时还需要包含没有关联的数据,即返回查询结果中不仅包含符合条件的数据,而且还包括左表(左连接或左外连接),右表(右连接或左外连接)或二个表(全外连接)中的所有数据,此时就需要使用外连接查询,在Mysql中外连接分为左连接,右连接(mysql不支持全外连接)。

语法格式:

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

LEFT JOIN (左连接):

左连接结果 = 左表中所有记录 + 右表中符合连接条件的记录

练习:

# 查询所有学生信息和满足条件的班级信息
SELECT *
FROM student3 s
LEFT JOIN class c
ON s.id = c.id
1
2
3
4
5

运行效果:

RIGHT JOIN(右连接):

右连接返回结果 = 左表中符合连接条件的记录 + 右表中所有记录

练习:

全连接 FULL JOIN(全外连接):

全外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。

由于mysql不支持FULL JOIN 但是可以使用 LEFT JOIN UNION RIGHT JOIN 来代替:

练习:

# 返回结果 = 左表中的所有记录 + 右表中符合条件的记录
SELECT *
FROM student3 s
LEFT JOIN class c
ON s.id = c.id

UNION # 返回结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据

# 返回结果 =左表中符合条件的记录 + 右表中的所有记录 
SELECT *
FROM student3 s
RIGHT  JOIN class c
ON s.id = c.id
1
2
3
4
5
6
7
8
9
10
11
12
13

运行效果:

UNION的使用:

合并查询结果:利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

语法格式:

SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2
1
2
3

UNION 操作符

返回两个查询的结果集的并集，去除重复记录。

练习:

SELECT * FROM  student3

UNION 

SELECT * FROM student4
1
2
3
4
5

运行效果:

UNION ALL操作符

返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。

练习:

SELECT * FROM  student3

UNION ALL

SELECT * FROM student4
1
2
3
4
5

运行效果:

子查询:

子查询是指一个查询语句嵌套在另一个查询语句内部的查询,在执行查询语句时,首先会执行子查询中的语句,然后将返回结果作为外层查询的过滤条件,按内查询的结果返回一条还是多条记录，将子查询分为 单行子查询和多行子查询。

单行子查询:

练习:

# 查询5班中所有学生信息
SELECT *
FROM student3
WHERE id = (SELECT id FROM class WHERE name = '5班')

# 查询除了5班外所有学生信息
SELECT *
FROM student3
WHERE id <> (SELECT id FROM class WHERE name = '5班')
1
2
3
4
5
6
7
8
9

运行效果:


多行子查询:

练习:

• IN

# 查询班级表中所有学生信息
SELECT *
FROM student3
WHERE id in (SELECT id FROM class ) # 子查询,查出的id分别为[1,5]
1
2
3
4

运行效果:

• EXISTS

# 没有记录返回
SELECT *
FROM student3
WHERE EXISTS (SELECT id FROM class WHERE id >5)

# 有记录返回
SELECT *
FROM student3
WHERE EXISTS (SELECT id FROM class WHERE id >= 5)

# 有记录返回
SELECT *
FROM student3
WHERE NOT EXISTS (SELECT id FROM class WHERE id >5)

# 没有记录返回
SELECT *
FROM student3
WHERE NOT EXISTS (SELECT id FROM class WHERE id >= 5)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

运行效果:

备注:在EXISTS 中的子查询只要查询到一条记录就会返回TRUE,如果一条都没有就返回FALSE,如果前面有NOT的话,可以假设为取反,子查询返回TRUE取反就为FALSE如果为FALSE取反就为TRUE,最后返回的结果为TRUE时外层查询就会执行,并且EXISTS要比IN的执行效率更高。

• ANY 和SOME

# student3中的id只要满足大于class中任一一条记录的id
SELECT *
FROM student3
WHERE id > ANY (SELECT id FROM class)

# 效果和前面一条查询一致
SELECT *
FROM student3
WHERE id > SOME (SELECT id FROM class)
1
2
3
4
5
6
7
8
9

运行效果:

• ALL

# student3中的id必须满足大于class中所有记录的id
SELECT *
FROM student3
WHERE id > ALL (SELECT id FROM class)
1
2
3
4

运行效果:

8.数据库TCL的常用命令

(1) 事务

事务处理机制在程序开发过程中有着非常重要的作用,它可以使整个系统更加安全,保证在同一个事务中的操作具有同步性。

(2) 事务的开启,提交,回滚

练习:
创建相应表格:

#账户
USE test;
DROP TABLE IF EXISTS account;
CREATE TABLE account (
id int PRIMARY KEY auto_increment, -- 账户
name varchar(20), -- 账户名
money FLOAT -- 金额
) DEFAULT CHARACTER SET utf8;

INSERT INTO account(name,money) VALUES('a',1000),('b',1000);
1
2
3
4
5
6
7
8
9
10

开启事务,未提交,a向b转100元:

# 开启事务
START TRANSACTION;
UPDATE account SET money = money -100 WHERE name = 'a';
UPDATE account SET money = money + 100 WHERE name = 'b';
1
2
3
4

在Navicat中执行:

在DOS中进行查询:

开启事务,并提交,a向b转100元:

```sql
# 开启事务
START TRANSACTION;
UPDATE account SET money = money -100 WHERE name = 'a';
UPDATE account SET money = money + 100 WHERE name = 'b';
COMMIT;
1
2
3
4
5
6

在Navicat中执行:

在DOS中进行查询:

备注:在MYSQL中直接书写的SQL语句都是自动提交的,而事务中的操作语句都需要COMMIT语句手动提交，只有事务提交后其中的操作才会生效。

事务1:开启事务,a向b转100元,再提交
事务2:开启事务a向b转900,回滚事务,再提交

# a向b转100元,a:800 b:1200 
START TRANSACTION;
UPDATE account SET money = money - 100 WHERE name = 'a';
UPDATE account SET money = money + 100 WHERE name = 'b';
COMMIT;
# a向b转900元 800-900 余额不足,回滚 a:800 b:1200 
START TRANSACTION;
UPDATE account SET money = money - 900 WHERE name = 'a';
UPDATE account SET money = money + 900 WHERE name = 'b';
ROLLBACK;
COMMIT;

1
2
3
4
5
6
7
8
9
10
11
12

在Navicat中执行:
执行第一次事务

执行第二次事务

在DOS中进行查询:
查询第一次事务

查询第二次事务

(3) 事务隔离级别

• READ UNCOMMITTED(没提交)
• READ COMMITTED(读提交)
• REPEATABLE READ(可重复读,mysql默认事务隔离级别)
• SERIALIZABLE(可串行化)

使用下面的SQL语句查询事务隔离级别:

SELECT @@tx_isolation;
1

运行效果:

设置事务隔离级别为READ COMMITTED(读提交):

SET SESSION TRANSACTION  ISOLATION LEVEL READ COMMITTED;
SELECT @@tx_isolation;
1
2

备注:SESSION 表示当前会话,TRANSACTION 表示事务,ISOLATION 表示隔离,LEVEL 表示级别

运行效果:

9.存储过程与存储函数

(1) 存储过程

创建存储过程

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
存储过程体
END
1
2
3
4
5

说明：
1、参数前面的符号的意思

• IN ：当前参数为输入参数，也就是表示入参；存储过程只是读取这个参数的值。如果没有定义参数种类， 默认就是 IN ，表示输入参数。
• OUT ：当前参数为输出参数，也就是表示出参；执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。
• INOUT ：当前参数既可以为输入参数，也可以为输出参数。

2、形参类型可以是 MySQL数据库中的任意类型。
3、 characteristics 表示创建存储过程时指定的对存储过程的约束条件，其取值信息如下：

LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
1
2
3
4
5

characteristics 参数解释:

1. LANGUAGE SQL

说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL。

2. [NOT] DETERMINISTIC

指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT
DETERMINISTIC。

3. { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

指明子程序使用SQL语句的限制。

4. SQL SECURITY { DEFINER | INVOKER }

执行当前存储过程的权限，即指明哪些用户能够执行当前存储过程。

练习:
定义存储过程select_all_data()

DELIMITER $ # 定义结束符号为$
CREATE PROCEDURE select_all_data()
DETERMINISTIC
CONTAINS SQL
BEGIN
SELECT * FROM student3;
SELECT * FROM class;
END $
DELIMITER ; # 恢复结束符号为;
1
2
3
4
5
6
7
8
9

定义存储过程select_max_english(OUT num DOUBLE)

DELIMITER $ # 定义结束符号为$
CREATE PROCEDURE select_max_english(OUT num DOUBLE)
DETERMINISTIC
CONTAINS SQL
BEGIN
SELECT MAX(english) INTO num FROM student3 ;
END $
DELIMITER ; # 恢复结束符号为;
1
2
3
4
5
6
7
8

定义存储过程select_name_students(IN name VARCHAR(20))

DELIMITER $ # 定义结束符号为$
CREATE PROCEDURE select_name_students(IN inname VARCHAR(20) CHARACTER SET utf8)
DETERMINISTIC
CONTAINS SQL
BEGIN
SELECT s.id as '学号',s.name as '姓名' , sex as '性别',address as '地址' , c.name as '班级名' ,count as '班级人数'  FROM student3 s,class c
 WHERE s.id = c.id and c.name = inname;
END $
DELIMITER ; # 恢复结束符号为;
1
2
3
4
5
6
7
8
9

定义存储过程select_name_class(INOUIT name VARCHAR(20))

DELIMITER $ # 定义结束符号为$
CREATE PROCEDURE select_name_class(INOUT stuname VARCHAR(20) CHARACTER SET utf8)
DETERMINISTIC
CONTAINS SQL
BEGIN
SELECT  name INTO stuname FROM class WHERE id = (SELECT id FROM student3 WHERE name = stuname);
END $
DELIMITER ; # 恢复结束符号为;
1
2
3
4
5
6
7
8

存储过程中复杂的SQL语句:

1. BEGIN…END：BEGIN…END 中间包含了多个语句，每个语句都以（;）号为结束符。
2. DECLARE：DECLARE 用来声明变量，使用的位置在于 BEGIN…END 语句中间，而且需要在其他语句使用之前进
行变量的声明。
3. SET：赋值语句，用于对变量进行赋值。
4. SELECT… INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值。
5. DELIMITER : 新的结束标记
1
2
3
4
5
6

备注:存储过程体中可以有多条 SQL 语句，如果仅仅一条SQL 语句，则可以省略 BEGIN 和 END 编写存储过程并不是一件简单的事情，可能存储过程中需要复杂的 SQL 语句。

调用存储过程

存储过程有多种调用方法。存储过程必须使用CALL语句调用，并且存储过程和数据库相关，如果要执行
其他数据库中的存储过程，需要指定数据库名称，例如CALL dbname.procname。

格式:

练习:

CALL  select_all_data();
1

运行效果:

CALL select_name_students('五班');
1

运行效果:

SET @num = 0.0;# 声明变量@num
CALL select_max_english(@num);# 传参@num
SELECT @num as '英语最高分';# 输出@num
1
2
3

运行效果:

SET @stuname = '马云';
CALL select_name_class(@stuname);
SELECT @stuname as '班级名' ;
1
2
3

运行效果:

(2) 存储函数

创建存储函数

CREATE FUNCTION 函数名(参数名 参数类型,...)
RETURNS 返回值类型
[characteristics ...]
BEGIN
函数体  #函数体中肯定有 RETURN 语句
END
1
2
3
4
5
6

说明：

1. 参数列表：指定参数为IN、OUT或INOUT只对PROCEDURE是合法的，FUNCTION中总是默认为IN参 数。
2. RETURNS type 语句表示函数返回数据的类型；RETURNS子句只能对FUNCTION做指定，对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个 RETURN value 语句。
3. characteristic 创建函数时指定的对函数的约束。取值与创建存储过程时相同，这里不再赘述。
4. 函数体也可以用BEGIN…END来表示SQL代码的开始和结束。如果函数体只有一条语句，也可以省略 BEGIN…END。

调用存储函数(自定义函数)

SELECT 函数名(实参列表)
1

练习:

# 创建get_name(num INT)函数
DELIMITER $ # 定义结束符号为$
CREATE FUNCTION get_name(num INT)
RETURNS VARCHAR(20) CHARACTER SET utf8
DETERMINISTIC
CONTAINS SQL
BEGIN
RETURN (SELECT name FROM class WHERE id = num);
END $
DELIMITER ; # 恢复结束符号为;

#调用函数
SELECT get_name(1) AS '班级';
1
2
3
4
5
6
7
8
9
10
11
12
13

运行效果:

(3) 存储过程与存储函数的对比

备注:存储函数可以放在查询语句中使用，存储过程不行。反之，存储过程的功能更加强大，包括能够执行对表的操作（比如创建表，删除表等）和事务操作，这些功能是存储函数不具备的。

(4) 存储过程与存储函数的查看,修改和删除

characteristic指定存储过程或函数的特性，其取值信息与创建存储过程、函数时的取值信息略有不同:

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
1
2
3

参数解释:

练习:

# select_all_dat:前面创建的存储过程,get_name:前面创建的存储函数
#查看存储过程或存储函数创建信息
SHOW CREATE PROCEDURE select_all_data;
SHOW CREATE FUNCTION get_name;

#查看存储过程或存储函数状态信息
SHOW PROCEDURE  STATUS LIKE 'select_all_data' ;
SHOW FUNCTION  STATUS LIKE 'get_name' ;

#从information_schema.Routines表中查看存储过程和函数的信息
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='select_all_data' AND ROUTINE_TYPE = 'PROCEDURE';

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='get_name' AND ROUTINE_TYPE = 'FUNCTION';

#修改存储过程或函数的特性
ALTER PROCEDURE select_all_data  
MODIFIES SQL DATA
SQL SECURITY INVOKER ;

ALTER FUNCTION get_name  
MODIFIES SQL DATA
SQL SECURITY INVOKER ;

# 删除存储过程或函数
DROP PROCEDURE IF EXISTS select_all_data;
DROP FUNCTION IF EXISTS get_name;
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

10.变量、流程控制与游标

(1) 变量

• 变量

在MySQL数据库的存储过程和函数中，可以使用变量来存储查询或计算的中间结果数据，或者输出最终的结果数据。 在 MySQL 数据库中，变量分为 系统变量 以及 用户自定义变量 。

• 系统变量

变量由系统定义，不是用户定义，属于 服务器 层面。启动MySQL服务，生成MySQL服务实例期间，MySQL将为MySQL服务器内存中的系统变量赋值，这些系统变量定义了当前MySQL服务实例的属性、特征。这些系统变量的值要么是 编译MySQL时参数 的默认值，要么是 配置文件 （例如my.ini等）中的参数值,在MySQL数据库中,数据库可分为全局系统变量以及会话系统变量。大家可以通过网址https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html 查看MySQL文档的系统变量。

• 全局系统变量与会话系统变量

系统变量分为全局系统变量（需要添加 global 关键字）以及会话系统变量（需要添加 session 关键字），有时也把全局系统变量简称为全局变量，有时也把会话系统变量称为local变量。如果不写，默认会话级别。静态变量（在 MySQL 服务实例运行期间它们的值不能使用 set 动态修改）属于特殊的全局系统变量。

• 会话系统变量

每一个MySQL客户机成功连接MySQL服务器后，都会产生与之对应的会话。会话期间，MySQL服务实例
会在MySQL服务器内存中生成与该会话对应的会话系统变量，这些会话系统变量的初始值是全局系统变
量值的复制,如下图:

重点：

• 全局系统变量针对于所有会话（连接）有效，但 不能跨重启
• 会话1对某个全局系统变量值的修改会导致会话2中同一个全局系统变量值的修改。
• 有些系统变量作用域既可以是全局又可以是会话，例如 character_set_client 用于设置客户端的字符集；

(2) 查看系统变量

• 查看所有或部分系统变量

举例:

SHOW GLOBAL VARIABLES LIKE 'admin_%';
1

• 查看指定系统变量

作为 MySQL 编码规范，MySQL 中的系统变量以 两个“@” 开头，其中“@@global”仅用于标记全局系统变量，“@@session”仅用于标记会话系统变量。“@@”首先标记会话系统变量，如果会话系统变量不存在，则标记全局系统变量。

• 修改系统变量的值

有些时候，数据库管理员需要修改系统变量的默认值，以便修改当前会话或者MySQL服务实例的属性、特征。具体方法：
方式1：修改MySQL 配置文件 ，继而修改MySQL系统变量的值（该方法需要重启MySQL服务）
方式2：在MySQL服务运行期间，使用“set”命令重新设置系统变量的值

举例:

SELECT @@global.autocommit;
SET GLOBAL autocommit=0;
SELECT @@session.tx_isolation;
SET @@session.tx_isolation='read-uncommitted';
SET GLOBAL max_connections = 1000;
SELECT @@global.max_connections;
1
2
3
4
5
6

(3) 用户变量

用户变量是用户自己定义的，作为 MySQL 编码规范，MySQL 中的用户变量以 一个“@” 开头。根据作用范围不同，又分为 会话用户变量 和 局部变量 。
会话用户变量：作用域和会话变量一样，只对 当前连接 会话有效。
局部变量：只在 BEGIN 和 END 语句块中有效。局部变量只能在 存储过程和函数 中使用。

• 会话用户变量

变量的定义:

举例:

SELECT @a;
SELECT @num := COUNT(*) FROM employees;
SELECT @num;
SELECT AVG(salary) INTO @avgsalary FROM employees;
SELECT @avgsalary;
SELECT @big;  #查看某个未声明的变量时，将得到NULL值
1
2
3
4
5
6

• 局部变量

定义：可以使用 DECLARE 语句定义一个局部变量
作用域：仅仅在定义它的 BEGIN … END 中有效 位置：只能放在 BEGIN … END 中，而且只能放在第一句

BEGIN
#声明局部变量
DECLARE 变量名1 变量数据类型 [DEFAULT 变量默认值];
DECLARE 变量名2,变量名3,... 变量数据类型 [DEFAULT 变量默认值];
#为局部变量赋值
SET 变量名1 = 值;
SELECT 值 INTO 变量名2 [FROM 子句];
#查看局部变量的值
SELECT 变量1,变量2,变量3;
END
1
2
3
4
5
6
7
8
9
10

举例:

DELIMITER //
CREATE PROCEDURE set_value()
BEGIN
DECLARE emp_name VARCHAR(25);
DECLARE sal DOUBLE(10,2);
SELECT last_name,salary INTO emp_name,sal
FROM employees
WHERE employee_id = 102;
SELECT emp_name,sal;
END //
DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11

(4) 对比会话用户变量与局部变量

(5) 定义条件与处理程序

定义条件 是事先定义程序执行过程中可能遇到的问题， 处理程序 定义了在遇到问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。这样可以增强存储程序处理问题的能力，避免程序异常停止运行。
说明：定义条件和处理程序在存储过程、存储函数中都是支持的。

定义条件:

DECLARE 错误名称 CONDITION FOR 错误码（或错误条件）
1

说明:

• MySQL_error_code 和 sqlstate_value 都可以表示MySQL的错误。(MySQL_error_code是数值类型错误代码。sqlstate_value是长度为5的字符串类型错误代码。)
• 例如:在ERROR 1418 (HY000)中，1418是MySQL_error_code，'HY000’是sqlstate_value

举例:

定义"ERROR 1148(42000)"错误，名称为command_not_allowed。

#使用MySQL_error_code
DECLARE command_not_allowed CONDITION FOR 1148;
#使用sqlstate_value
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000';
1
2
3
4

可以为SQL执行过程中发生的某种类型的错误定义特殊的处理程序。定义处理程序时，使用DECLARE语句
的语法如下：

DECLARE 处理方式 HANDLER FOR 错误类型 处理语句
1

处理方式：处理方式有3个取值：CONTINUE、EXIT、UNDO:

错误类型（即条件）可以有如下取值：

处理语句：如果出现上述条件之一，则采用对应的处理方式，并执行指定的处理语句。语句可以是
像“ SET 变量 = 值 ”这样的简单语句，也可以是使用 BEGIN … END 编写的复合语句。

定义处理程序的几种方式，代码如下：

#方法1：捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE';
#方法2：捕获mysql_error_value
DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE';
#方法3：先定义条件，再调用
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info = 'NO_SUCH_TABLE';
#方法4：使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR';
#方法5：使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE';
#方法6：使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR';
1
2
3
4
5
6
7
8
9
10
11
12
13

(6) 流程控制

解决复杂问题不可能通过一个 SQL 语句完成，我们需要执行多个 SQL 操作。流程控制语句的作用就是控制存储过程中 SQL 语句的执行顺序，是我们完成复杂操作必不可少的一部分。只要是执行的程序，流程就分为三大类：
顺序结构 ：程序从上往下依次执行;
分支结构 ：程序按条件进行选择执行，从两条或多条路径中选择一条执行
循环结构 ：程序满足一定条件下，重复执行一组语句

针对于MySQL 的流程控制语句主要有 3 类。注意：只能用于存储程序。

• 条件判断语句 ：IF 语句和 CASE 语句
• 循环语句 ：LOOP、WHILE 和 REPEAT 语句
• 跳转语句 ：ITERATE 和 LEAVE 语句

---

1. IF 语句的语法结构是

IF 表达式1 THEN 操作1
[ELSEIF 表达式2 THEN 操作2]……
[ELSE 操作N]
END IF
1
2
3
4

举例:

IF val IS NULL
THEN SELECT 'val is null';
ELSE SELECT 'val is not null';
END IF;
1
2
3
4

2. 分支结构之 CASE

#情况一：类似于switch
CASE 表达式
WHEN 值1 THEN 结果1或语句1(如果是语句，需要加分号)
WHEN 值2 THEN 结果2或语句2(如果是语句，需要加分号)
...
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）


#情况二：类似于多重if
CASE
WHEN 条件1 THEN 结果1或语句1(如果是语句，需要加分号)
WHEN 条件2 THEN 结果2或语句2(如果是语句，需要加分号)
...
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

举例:

# 举例1
CASE val
　　　WHEN 1 THEN SELECT 'val is 1';
　　　WHEN 2 THEN SELECT 'val is 2';
　　　ELSE SELECT 'val is not 1 or 2';
END CASE;

# 举例2
CASE
WHEN val IS NULL THEN SELECT 'val is null';
WHEN val < 0 THEN SELECT 'val is less than 0';
WHEN val > 0 THEN SELECT 'val is greater than 0';
ELSE SELECT 'val is 0';
END CASE;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

3. 循环结构之LOOP

END LOOP [loop_label]
1

举例:

DECLARE id INT DEFAULT 0;
add_loop:LOOP
SET id = id +1;
IF id >= 10 THEN LEAVE add_loop;
END IF;
END LOOP add_loop;
1
2
3
4
5
6

4. 循环结构之WHILE

[while_label:] WHILE 循环条件  DO
循环体
END WHILE [while_label];
1
2
3

举例:

DELIMITER //
CREATE PROCEDURE test_while()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 10 DO
SET i = i + 1;
END WHILE;
SELECT i;
END //
DELIMITER ;
#调用
CALL test_while();
1
2
3
4
5
6
7
8
9
10
11
12

5. 循环结构之REPEAT

EPEAT语句创建一个带条件判断的循环过程。与WHILE循环不同的是，REPEAT 循环首先会执行一次循环，然后在 UNTIL 中进行表达式的判断，如果满足条件就退出，即 END REPEAT；如果条件不满足，则会就继续执行循环，直到满足退出条件为止。

UNTIL 结束循环的条件表达式
END REPEAT [repeat_label]
1
2

举例:

DELIMITER //
CREATE PROCEDURE test_repeat()
BEGIN
DECLARE i INT DEFAULT 0;
REPEAT
SET i = i + 1;
UNTIL i >= 10
END REPEAT;
SELECT i;
END //
DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11

6. 跳转语句之LEAVE语句

LEAVE语句：可以用在循环语句内，或者以 BEGIN 和 END 包裹起来的程序体内，表示跳出循环或者跳出程序体的操作。如果你有面向过程的编程语言的使用经验，你可以把 LEAVE 理解为 break。

LEAVE 标记名
1

举例:

DELIMITER //
CREATE PROCEDURE leave_begin(IN num INT)
begin_label: BEGIN
IF num<=0
THEN LEAVE begin_label;
ELSEIF num=1
THEN SELECT AVG(salary) FROM employees;
ELSEIF num=2
THEN SELECT MIN(salary) FROM employees;
ELSE
SELECT MAX(salary) FROM employees;
END IF;
SELECT COUNT(*) FROM employees;
END //
DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

7. 跳转语句之ITERATE语句

ITERATE语句：只能用在循环语句（LOOP、REPEAT和WHILE语句）内，表示重新开始循环，将执行顺序转到语句段开头处。如果你有面向过程的编程语言的使用经验，你可以把 ITERATE 理解为 continue，意思为“再次循环”。

ITERATE label
1

举例:

DELIMITER //
CREATE PROCEDURE test_iterate()
BEGIN
DECLARE num INT DEFAULT 0;
my_loop:LOOP
SET num = num + 1;
IF num < 10
THEN ITERATE my_loop;
ELSEIF num > 15
SELECT '尚硅谷：让天下没有难学的技术';
END LOOP my_loop;
END //
DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12
13

(7) 游标

游标，提供了一种灵活的操作方式，让我们能够对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作的数据结构。游标让 SQL 这种面向集合的语言有了面向过程开发的能力。如果我们想要使用游标，一般需要经历四个步骤。不同的 DBMS 中，使用游标的语法可能略有不同。

1. 第一步，声明游标

要使用 SELECT 语句来获取数据结果集，而此时还没有开始遍历数据，这里 select_statement 代表的是SELECT 语句，返回一个用于创建游标的结果集。

在MySQL中，使用DECLARE关键字来声明游标，其语法的基本形式如下：

DECLARE cursor_name CURSOR FOR select_statement;
1

举例：

DECLARE cur_emp CURSOR FOR
SELECT employee_id,salary FROM employees;
1
2

2. 第二步，打开游标

当我们定义好游标之后，如果想要使用游标，必须先打开游标。打开游标的时候 SELECT 语句的查询结果集就会送到游标工作区，为后面游标的 逐条读取 结果集中的记录做准备。

打开游标的语法如下：

OPEN cursor_name
1

3. 使用游标（从游标中取得数据）

这句的作用是使用 cursor_name 这个游标来读取当前行，并且将数据保存到 var_name 这个变量中，游
标指针指到下一行。如果游标读取的数据行有多个列名，则在 INTO 关键字后面赋值给多个变量名即可。

语法如下：

FETCH cursor_name INTO var_name [, var_name] ...
1

举例:

FETCH　cur_emp INTO emp_id, emp_sal ;
1

注意：游标的查询结果集中的字段数，必须跟 INTO 后面的变量数一致，否则，在存储过程执行的时
候，MySQL 会提示错误。

4. 关闭游标

有 OPEN 就会有 CLOSE，也就是打开和关闭游标。当我们使用完游标后需要关闭掉该游标。因为游标会占用系统资源 ，如果不及时关闭，游标会一直保持到存储过程结束，影响系统运行的效率。

CLOSE cursor_name
1

举例:

创建存储过程“get_count_by_limit_total_salary()”，声明IN参数 limit_total_salary，DOUBLE类型；声明OUT参数total_count，INT类型。函数的功能可以实现累加薪资最高的几个员工的薪资值，直到薪资总和 达到limit_total_salary参数的值，返回累加的人数给total_count。

DELIMITER //
CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE,OUT
total_count INT)
BEGIN
DECLARE sum_salary DOUBLE DEFAULT 0;  #记录累加的总工资
DECLARE cursor_salary DOUBLE DEFAULT 0; #记录某一个工资值
DECLARE emp_count INT DEFAULT 0; #记录循环个数
#定义游标
DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;
#打开游标
OPEN emp_cursor;
REPEAT
#使用游标（从游标中获取数据）
FETCH emp_cursor INTO cursor_salary;
SET sum_salary = sum_salary + cursor_salary;
SET emp_count = emp_count + 1;
UNTIL sum_salary >= limit_total_salary
END REPEAT;
SET total_count = emp_count;
#关闭游标
CLOSE emp_cursor;
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

11.视图

视图是从一个或多个表中导出来的表,它是一种虚拟存在的表,并且表结构和数据都依赖与基本表。通过视图不仅可以看到存放在基本表中的数据,并且还可以像操作基本表一样,对视图中存放的数据进行查询,修改和删除。

(1) 创建视图

语法格式

• 详细格式:

CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW 视图名称 [(column_list)]
AS 查询语句
[WITH [CASCADED|LOCAL] CHECK OPTION]
1
2
3
4
5

• 参数解释:

• 精简格式:

CREATE VIEW 视图名称
AS 查询语句
1
2

创建视图

• 在单表上创建视图

CREATE VIEW select_all_data_view(id,name,sex)
AS
SELECT id,name,sex FROM student3;
1
2
3

• 在多表上创建视图

CREATE VIEW select_all_students_view
AS
SELECT s.id as '学号',s.name as '姓名' , sex as '性别',
address as '地址' , c.name as '班级名' ,count as '班级人数'  
FROM student3 s,class c
WHERE s.id = c.id

1
2
3
4
5
6
7

(2) 查看视图

练习:

# 查看数据库的表对象、视图对象
SHOW TABLES;
# 查看视图的结构
DESC select_all_students_view;
DESCRIBE select_all_students_view;
# 查看视图的属性信息
SHOW TABLE STATUS LIKE 'select_all_students_view' \G;
SHOW TABLE STATUS LIKE 'student3' \G;
# 查看视图的详细定义信息
SHOW CREATE VIEW select_all_students_view;
1
2
3
4
5
6
7
8
9
10

DESC select_all_students_view; 执行结果如下:

上图列名解释:

SHOW TABLE STATUS LIKE 'select_all_students_view' \G; 执行结果如下(Navicat不支持/G):

使用DOS进行查询,执行结果如下:

执行结果解释:

执行结果显示，Comment为VIEW，说明该表为视图，其他的信息为NULL，说明这是一个虚表,如果Comment为空说明是表不是视图。

SHOW TABLE STATUS LIKE 'student3' \G; 执行结果如下:

(3) 修改视图

使用 CREATE OR REPLACE VIEW 语句修改视图

如果视图存在,更新视图,如果视图不存在创建一个视图。

语法格式:

CREATE OR REPLACE VIEW
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW 视图名称 [(column_list)]
AS 查询语句
[WITH [CASCADED|LOCAL] CHECK OPTION]
1
2
3
4
5

练习:

CREATE OR REPLACE 
VIEW select_all_students_view
AS
SELECT s.id as '学号',s.name as '姓名' , sex as '性别'
FROM student3 s,class c
WHERE s.id = c.id;


SELECT * FROM select_all_students_view;
1
2
3
4
5
6
7
8
9

运行效果:

使用ALTER语句修改视图
语法格式:

ALTER
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW 视图名称 [(column_list)]
AS 查询语句
[WITH [CASCADED|LOCAL] CHECK OPTION]
1
2
3
4
5

练习:

# 修改select_all_students_view视图内容
ALTER
VIEW select_all_students_view
AS
SELECT s.id as '学号',s.name as '姓名' , sex as '性别',address as '地址' , c.name as '班级名' ,count as '班级人数'  
FROM student3 s,class c
WHERE s.id = c.id
 
 # 查询select_all_students_view视图内容
 SELECT * FROM select_all_students_view;
1
2
3
4
5
6
7
8
9
10

运行效果:

(4) 更新视图(把视图当成表来处理)

更新视图,视图中的基本表也会发生改变。

当视图包含如下内容时,视图的更新操作将不能被执行:

1. 视图中包含基本表中被定义为非空的列。
2. 在定义视图的SELECT语句后的字段列表中使用了数学表达式。
3. 在定义视图的SELECT语句后的字段列表中使用了聚合函数。
4. 在定义视图的SELECT语句中使用了 DISTINCT,UNION,TOP,GROUP BY或HAVING字句

使用UPDATE语句更新视图

使用UPDATE语句更新视图更新后相关的基本表也会发生变化。

练习:

 # 查询select_all_students_view视图内容【修改前】
 SELECT * FROM select_all_students_view WHERE `姓名` = '马云';
 
 # 修改马云班级的人数为1人
 UPDATE select_all_students_view SET `班级人数` = 1 WHERE 	 `姓名` = '马云';
 
 # 查询select_all_students_view视图内容【修改后】
 SELECT * FROM select_all_students_view WHERE `姓名` = '马云';
1
2
3
4
5
6
7
8

更新前:

更新后:

备注:基本表中的数据也发生了变化。如下所示

使用INSERT语句更新视图

使用INSERT语句，主要作用在基本表上,往基本表中插入数据,因为基本表中数据发生变化视图中也会发生变化,从而更新了视图中的数据。

练习:

 INSERT INTO class VALUES(6,'6班',30);
 SELECT * FROM select_all_students_view;
1
2

运行结果:

使用DELETE语句更新视图

使用DELETE语句删除视图中部分记录从而更新视图,并且基本表也会更新数据。

练习:

 # 创建视图student_view
 CREATE VIEW student_view
 AS
 SELECT * FROM student3;
 
 # 更新前查询视图
 SELECT * FROM student_view;
 
 DELETE FROM student_view WHERE id = 8;
 
  # 更新后查询视图
 SELECT * FROM student_view;
1
2
3
4
5
6
7
8
9
10
11
12

运行效果:
更新前:

更新后:

(5) 删除视图

删除视图只是删除视图的定义，并不会删除基表的数据。

语法格式:

DROP VIEW [IF EXISTS]
视图名,[视图名]....

1
2
3

练习:

DROP VIEW IF EXISTS 
student_view,select_all_students_view
1
2

运行效果:

查询基本表:

 SELECT * from class;
 SELECT * FROM student3;
1
2

运行效果( 删除视图并不影响视图中的基本表 ):

12.触发器

MySQL从 5.0.2 版本开始支持触发器。MySQL的触发器和存储过程一样，都是嵌入到MySQL服务器的一段程序。触发器是由 事件来触发 某个操作，这些事件包括 INSERT 、 UPDATE 、 DELETE 事件。所谓事件就是指用户的动作或者触发某项行为。如果定义了触发程序，当数据库执行这些语句时候，就相当于事件发生了，就会 自动 激发触发器执行相应的操作。当对数据表中的数据执行插入、更新和删除操作，需要自动执行一些数据库逻辑时，可以使用触发器来实现。

(1) 创建触发器

语法格式:

CREATE TRIGGER 触发器名称
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名
FOR EACH ROW
触发器执行的语句块;
1
2
3
4

说明：

练习:
1、创建数据表：

DROP TABLE IF EXISTS test_trigger;
 CREATE TABLE test_trigger (
id INT PRIMARY KEY AUTO_INCREMENT,
t_note VARCHAR(30)
)DEFAULT CHARACTER SET utf8;

DROP TABLE IF EXISTS test_trigger_log;
CREATE TABLE test_trigger_log (
id INT PRIMARY KEY AUTO_INCREMENT,
t_log VARCHAR(30)
)DEFAULT CHARACTER SET utf8;
1
2
3
4
5
6
7
8
9
10
11

2、创建触发器：创建名称为before_insert的触发器，向test_trigger数据表插入数据之前，向
test_trigger_log数据表中插入before_insert的日志信息。

DELIMITER //
CREATE TRIGGER before_insert
BEFORE INSERT ON test_trigger
FOR EACH ROW
BEGIN
INSERT INTO test_trigger_log (t_log)
VALUES('执行了INSERT INTO语句');
END //
DELIMITER ;
1
2
3
4
5
6
7
8
9

3、向test_trigger数据表中插入数据

INSERT INTO test_trigger (t_note) VALUES ('测试 BEFORE INSERT 触发器');
1

4、查看test_trigger_log数据表中的数据

SELECT * FROM test_trigger_log;
1

运行效果:

(2) 查看触发器

练习:

 # 查看当前数据库的所有触发器的定义
 SHOW TRIGGERS;
 # 查看当前数据库中某个触发器的定义
 SHOW CREATE TRIGGER before_insert;
 #系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息
 SELECT * FROM information_schema.TRIGGERS;
1
2
3
4
5
6

(3) 删除触发器

语法格式:

DROP TRIGGER  IF EXISTS 触发器名称;
1

练习:

DROP TRIGGER  IF EXISTS before_insert;
1

运行效果:

13.用户管理

(1) 创建用户

使用CREATE USER 语句创建用户

使用CREATE USER 语句需要在mysql数据库下，才能够创建用户,并且创建的新用户没有任何权限。

语法格式:

CREATE USER 用户名 [IDENTIFIED BY '密码'][,用户名 [IDENTIFIED BY '密码']];
1

参数解释:

练习:

• 创建用户zhang3 ,密码123123

CREATE USER zhang3 IDENTIFIED BY '123123';  # 默认host是 %
1

• 创建用户lisi密码123456

CREATE USER 'lisi'@'localhost' IDENTIFIED BY '123456';
1

使用INSERT语句创建用户

创建用户实际上都是在往mysql数据库中的user表中添加记录,所以使用INSERT语句也可以用来创建用户。

语法格式:

INSERT INTO mysql.user(Host,User,Password, ssl_cipher,x509_issuer,x509_subject)
VALUES('ip地址','账号',PASSWORD('密码'),'','','');
1
2

备注:PASSWORD()函数是加密函数,对明码进行加密操作。

如下图所示, ssl_cipher,x509_issuer,x509_subject都没有默认值所以插入记录的时候需要给初始值:

注意:数据插入后还需要刷新权限表

FLUSH PRIVILEGES;
1

练习:

• 创建用户wangmazhi,密码123456

INSERT INTO mysql.user(Host,User,Password, ssl_cipher,x509_issuer,x509_subject)
VALUES('localhost','wangmazhi',PASSWORD('123456'),'','','');
1
2

执行结果:

• 刷新权限表

FLUSH PRIVILEGES;
1

运行效果:

使用GRANT语句创建用户

GRANT语句创建用户，还可以对用户进行授权,该语句会自动加载权限表,不需要手动刷新，而且安全,准确,错误少,因此,使用使用GRANT语句创建用户是最常见的方法。

语法格式:

GRANT privileges ON database.table
TO 'username'@'hostname' [IDENTIFIED BY [PASSWORD] 'password']
[,'username'@'hostname' [IDENTIFIED BY [PASSWORD] 'password']]....
1
2
3

参数解释:

练习:

创建用户user,密码123456,并且对test数据库中的test表有查询权限

GRANT SELECT ON test.test
TO 'user'@'localhost' IDENTIFIED BY  '123456';
1
2

运行效果:

(2) 修改用户

修改root用户密码

修改root用户密码有3种方式,方式1使用mysqladmin命令修改root用户密码,mysqladmin命令通常用于执行一些管理性的工作,以及显示服务器状态等,方式2使用UPDATE语句修改密码,前面说到用户信息存放在mysql.user下,在root用户情况下直接通过UPDATE语句对密码进行修改,方式3使用SET语句进行修改。

语法格式:

方式1:

mysqladmin -u 账号[-h 主机] -p password 新密码; 
1

方式2( 不推荐 ):

UPDATE mysql.user set Password=PASSWORD('新密码') WHERE User = '用户名';
1

注意:数据修改后还需要刷新权限表

FLUSH PRIVILEGES;
1

方式3:

SET PASSWORD = PASSWORD('新密码'); # 修改root用户
SET PASSWORD FOR  'username'@'hostname'= PASSWORD('新密码'); # 修改指定用户
1
2

练习:

• 方式1修改密码

mysqladmin -u root  -p password  123456
1

• 方式2修改密码

UPDATE mysql.user set Password=PASSWORD('root') WHERE User = 'root'
FLUSH PRIVILEGES;	
1
2

• 方式3修改密码

SET PASSWORD = PASSWORD('123456');
1

root用户普通用户修改密码

root用户修改普通密码有三种方式,前2种方式分别是UPDATE语句和SET语句,由于前面已经讲解了,这里就不在讲解了,接下来主要说明第三种方式使用GRANT语句。

方式3:

GRANT USAGE ON *.* TO 'username'@'localhost' IDENTIFIED BY [PASSWORD] '新密码';
1

练习:

 GRANT USAGE ON *.* TO 'lisi'@'localhost' IDENTIFIED BY  '654321';
1

(3) 删除用户(普通用户)

使用DROP方式删除（推荐）

使用DROP USER语句来删除用户时，必须用于DROP USER权限。

语法格式:

DROP USER user[,user]…;
1

练习:

• 删除用户zhang3

DROP USER zhang3;
1

使用DELETE方式删除

使用DELETE方式删除就是将mysql.user表中记录移除,所以这种方式需要刷新权限表。

语法格式:

DELETE FROM mysql.user WHERE Host=’hostname’ AND User=’username’;
FLUSH PRIVILEGES;
1
2

练习:

• 删除用户lisi

DELETE FROM mysql.user WHERE  User='lisi';
FLUSH PRIVILEGES;
1
2

14.授权管理(数据库DCL常用命令)

(1) 用户权限相关概念

权限列表

用户权限可通过 show privileges; 命名进行查询。

使用 show privileges; 命令查询权限列表:

权限解释:

授予权限的原则

权限控制主要是出于安全因素，因此需要遵循以下几个 经验原则 ：

1. 只授予能 满足需要的最小权限 ，防止用户干坏事。比如用户只是需要查询，那就只给select权限就可
   以了，不要给用户赋予update、insert或者delete权限。
2. 创建用户的时候 限制用户的登录主机 ，一般是限制成指定IP或者内网IP段。
3. 为每个用户 设置满足密码复杂度的密码 。
4. 定期清理不需要的用户 ，回收权限或者删除用户

(2) 授予权限

用户是数据库的使用者，我们可以通过给用户授予访问数据库中资源的权限，来控制使用者对数据库的访问，消除安全
隐患。

语法规则:

GRANT 权限1,权限2,…权限n ON 数据库名称.表名称 TO 用户名@用户地址 [IDENTIFIED BY ‘密码口令’];
1

备注:该权限如果发现没有该用户，则会直接新建一个用户。

练习:

• 给user用户用本地命令行方式，授予test这个库下的所有表的插删改查的权限

GRANT SELECT,INSERT,DELETE,UPDATE ON test.* TO user@localhost ;
1

• 授予通过网络方式登录的joe用户 ，对所有库所有表的全部权限，密码设为123。注意这里唯独不包括grant的权限

 GRANT ALL PRIVILEGES ON *.* TO joe@'%' IDENTIFIED BY '123';
1

(3) 查看权限

查看当前用户权限

SHOW GRANTS;
# 或
SHOW GRANTS FOR CURRENT_USER;
# 或
SHOW GRANTS FOR CURRENT_USER();
1
2
3
4
5

查看某用户的全局权限

SHOW GRANTS FOR 'user'@'主机地址' ;
1

练习:

• 查看当前用户权限

SHOW GRANTS;
1

• 查看user用户的全局权限

SHOW GRANTS FOR 'user'@'localshot' ;
1

(4) 收回权限

收回权限就是取消已经赋予用户的某些权限。收回用户不必要的权限可以在一定程度上保证系统的安全性。MySQL中使用 REVOKE语句 取消用户的某些权限。使用REVOKE收回权限之后，用户账户的记录将从db,host,tables_priv和columns_priv表中删除，但是用户账户记录仍然在user表中保存（删除user表中的账户记录使用DROP USER语句,但是在将用户账户从user表删除之前，应该收回相应用户的所有权限。）。

语法格式:

REVOKE 权限1,权限2,…权限n ON 数据库名称.表名称 FROM 用户名@用户地址;
1

练习:

• 收回joe用户全库全表的所有权限

REVOKE ALL PRIVILEGES ON *.* FROM joe@'%';
1

• 收回user用户test库下的所有表的插删改查权限

REVOKE SELECT,INSERT,UPDATE,DELETE ON test.* FROM user@localhost;
1

注意： 须用户重新登录后才能生效

15.角色管理

在 Mysql8.0 引入角色管理的目的是方便管理拥有相同权限的用户 。恰当的权限设定，可以确保数据的安全性，这是至关重要的。

角色管理部分的操作如下:

注意下面练习未做测试

练习:

• 创建角色manager

CREATE ROLE 'manager'@'localhost';
1

• 给角色manager赋予SELECT权限,对test.*(test数据库下所有表有效)

GRANT SELECT ON test.*TO 'manager';
1

• 查看角色manager的权限

SHOW GRANTS FOR 'manager';
1

• 给user用户添加manager角色

GRANT 'manager' TO 'user'@'localhost';
1

• 激活用户权限

如果用户将activate_all_roles_on_login设置为ON,可以不用激活。

SET DEFAULT ROLE ALL TO 'user'@'localhost';
1

• 设置activate_all_roles_on_login设置为ON

SET GLOBAL activate_all_roles_on_login=ON;
1

• 查看ctivate_all_roles_on_login的值

show variables like 'activate_all_roles_on_login';
1

• 回收manager角色的SELECT权限

REVOKE SELECT ON test.* FROM 'manager';
1

• 撤销user用户的角色manager

REVOKE 'manager' FROM 'user'@'localhost';
1

• 删除角色manager

DROP ROLE manager;
1