MySQL数据库总结(基础篇)

前言:

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

1.Mysql服务和登入

(1) 服务的关闭和启动

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

操作	说明
net stop 服务名称	停止服务
net start 服务名称	启动服务

• 查看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语句"

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

参数	介绍
-h	后面接主机名或者主机IP，hostname为主机，hostIP为主机IP。
-P	后面接MySQL服务的端口，通过该参数连接到指定的端口。MySQL服务的默认端口是3306，不使用该参数时自动连接到3306端口，port为连接的端口号。
-u	后面接用户名，username为用户名。
-p	会提示输入密码。
DatabaseName	指明登录到哪一个数据库中。如果没有该参数，就会直接登录到MySQL数据库中，然后可以使用USE命令来选择数据库。
-e	后面可以直接加SQL语句。登录MySQL服务器以后即可执行这个SQL语句，然后退出MySQL服务器

举例:

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

运行效果:

练习:

本地登入:

• 密码可见: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) 数据库的查询,选择和退出

操作	SQL语句	描述
数据库的查询	show databses;	查看当前系统下的所有数据库
数据库的查询	show create database;	查看某个数据库的定义信息
数据库的查询	select version();	查看数据库的版本号
数据库的查询	select Database();	查看正在使用的数据库
数据库的选择	use 数据库名;	选择指定数据库
数据库的退出	exit或quit;	退出数据库

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

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

操作	SQL语句	描述
增	CREATE DATABASE 数据库名;	创建一个数据库
增	CREATE DATABASE IF NO EXISTS 数据库名;	判断数据库是否存在,不存在则创建数据库
增	CREATE DATABASE 数据库名 [DEFAULT] CHARACTER SET 字符集 [DEFAULT] COLLATE 校对规则名;	创建数据库并指定字符集,和校对规则
删	DROP DATABASE 数据库名;	删除数据库
删	DROP DATABASE IF EXISTS 数据库名;	判断数据库是否存在,存在则删除数据库
改	ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集;	修改数据库默认的字符集

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

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

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

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

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

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

数据库的备份

命令语法格式	说明
mysqldump -u用户名 -p密码 数据库名 [表1] [表2] … > 脚本名.sql | 绝对路径 \ 脚本名.sql	备份单个数据库
mysqldump -u用户名 -p密码 --database 数据库1 [数据库2] [数据库3] … > 脚本名.sql | 绝对路径 \ 脚本名.sql	备份多个数据库
mysqldump -u用户名 -p密码 --all-databases >脚本名.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文件中包含了数据库的创建语句。

数据库还原

命令格式	说明
mysql -u账号 -p密码 [dbname] < 脚本名.sql | 绝对路径\脚本名.sql	在dos下还原数据库
source 路径\xxx.sql	在mysql下还原数据库

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

练习:

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

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

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

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

(1) 表的查询

SQL语句	描述
show tables;	查看某个数据库中的所有表
des 表名;	查看表的结构
SHOW CREATE TABLE 表名;	查看创建表的 SQL 语句

(2) 表的创建

创建一张全新的表:

• 创建表的格式

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

• 表的字段数据类型

• 约束条件

约束	说明
NOT NULL	非空约束，规定某个字段不能为空
UNIQUE | UNIQUE KEY(字段1,字段2…)	唯一约束，规定某个字段在整个表中是唯一的|复合约束,多个字段一起满足约束
PRIMARY KEY | primary key(字段名1,字段名2,…)	主键(非空且唯一)约束 |复合主键,多个字段一起组成一个主键
FOREIGN KEY	外键约束
CHECK	检查约束(MySQL 8.0可以使用MySQL5.7不支持)
DEFAULT	默认值约束
auto_increment	自增约束

表级约束条件:

约束	说明
[constraint 约束名] UNIQUE(字段1,字段2,…)	唯一约束
[constraint 约束名] primary key(字段名)	主键约束
[CONSTRAINT <外键约束名称>] FOREIGN KEY（从表的某个字段) references 主表名(被参考字段)	外键约束

练习:

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

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;

运行效果:

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

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

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

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

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

练习:

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;

运行效果:

(3) 表的删除

SQL语句	描述
DROP TABLE 表名;	直接删除表
DROP TABLE IF EXISTS 表名;	判断表是否存在，如果存在则删除表

(3) 表的修改

SQL语句	描述
ALTER TABLE 表名 ADD 列名 类型;	添加表列
ALTER TABLE 表名 MODIFY 列名 新的类型;	修改列类型
ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;	修改列名
ALTER TABLE 表名 DROP 列名;	删除列
ALTER TABLE 表名 DROP 列名;	修改表名
ALTER TABLE 表名 character set 字符集;	修改字符集(gbk,utf8…)

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

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

(1) 记录的增(insert)

• 插入记录语法

• 插入记录

插入字段情况	SQL语句	备注
插入全部字段	INSERT INTO 表名 ( 字段名1, 字段名2, 字段名3…) VALUES (字段1的值, 字段2的值, 字段3的值,…);	需要的字段名都写出来,值和字段名一一对应
插入全部字段	INSERT INTO 表名 VALUES (值 1, 值 2, 值 3…);	不写字段名,值要和数据库表中的列(表结构列的顺序)相对应
插入部分字段	INSERT INTO 表名 ( 字段名 1, 字段名 2, …) VALUES (值 值 1, 值 值 2, …);	需要对哪一些字段赋值,就写相应的字段,但是如果对某个字段设置为了not null,或者相关属性如主键等,就必须对该列赋值

插入字段注意事项:

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

蠕虫复制:

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

SQL语句	描述
INSERT INTO 表名1 SELECT * FROM 表名 2;	将表名2中的所有的列复制到表名1中
INSERT INTO 表名1(列1, 列2) SELECT 列1, 列2 FROM 表名2;	将表名2中列1和列2复制到表名1中

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

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

• 使用delete删除记录

是否带条件	SQL语句	描述
不带条件删除数据	DELETE FROM 表名;	删除表中所有数据，注意表的结构并没有删除
带条件删除数据	DELETE FROM 表名 WHERE 字段名= 值	删除符合条件的数据

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

SQL语句	描述
TRUNCATE TABLE 表名;	删除表中所有数据，并且表的结构也删除

• truncate 和 delete 的区别

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

(3) 记录的改(update)

描述	SQL语句	备注
更新表记录语法	UPDATE 表名 SET 列名= 值,… [WHERE 条件表达式] ;	-
不带条件修改数据	UPDATE 表名 SET 字段名1= 值,字段名2=值…;	不按条件修改指定记录字段的值
带条件修改数据	UPDATE 表名 SET 字段名1= 值,字段名2=值… WHERE 字段名= 值;	按条件修改指定记录字段的值

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

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

(1) 简单查询

语法格式:

语法格式	说明
SELECT [DISTNCT] 列名 FROM 表名 [WHERE 条件表达式]	① SELECT 命令可以读取一行或者多行记录。 ② 你可以使用星号(*)来代替其他字段,SELECT 语句会返回表的所有字段数据。 ③ 你可以使用 WHERE 语句来包含任何条件。 ④ DISTNCT是可选参数,用于剔除查询结果中重复的数据

不带条件查询:

• 语法格式

操作描述	SQL语句	描述
查询表中的所有行和列的数据	SELECT * FROM 表名;	使用*表示所有列
查询指定列	SELECT 字段名 1, 字段名 2, 字段名 3, … FROM 表名;	查询指定列的数据,多个列之间以逗号分隔

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

操作描述	SQL语句
对列指定别名	SELECT 字段名1 AS 别名, 字段名2 AS 别名… FROM 表名;
对列和表同时指定别名	SELECT 字段名1 AS 别名, 字段名2 AS 别名… FROM 表名 AS 表别名;

• 清除重复值(DISTINCT)

操作描述	SQL语句
查询指定列并且结果不出现重复数据	SELECT DISTINCT 字段名 FROM 表名;
查询多列并且结果不出现重复数据	SELECT DISTINCT 字段1,字段2,字段3… FROM 表名;

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

操作描述	SQL语句
某列数据和固定值运算	SELECT 列名 1 + 固定值 FROM 表名;
某列数据和其他列数据参与运算	SELECT 列名 1 + 列名 2 FROM 表名;

带条件查询:

• 语法格式

操作描述	SQL语句	描述
查询表中满足条件的所有行和列的数据	SELECT * FROM 表名 where 条件;	使用*表示所有列
查询表中满足条件的数据的指定列的数据	SELECT 字段名 1, 字段名 2, 字段名 3, … FROM 表名 where 条件;	查询满足添加的数据的指定列的数据,多个列之间以逗号分隔

• 使用运算符

比较运算符	说明
> 、< 、<= 、>= 、= 、<>	<>在 SQL 中表示不等于，在 mysql 中也可以使用!=没有==
BETWEEN…AND	在一个范围之内，如：between 100 and 200相当于条件在 100 到 200 之间，包头又包尾
IN( 集合)	集合表示多个值，使用逗号分隔
LIKE ’ 张%’	模糊查询
IS NULL	查询某一列为 NULL 的值，注：不能写=NULL

逻辑运算符	说明
and 或 &&	与，SQL 中建议使用前者，后者并不通用。
or 或 ||	或
not 或 !	非

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

算术运算符：

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

位运算符：

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

运算符的优先级：

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

扩展使用正则表达式:

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

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

练习:

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

• 使用关键字

关键字	语法	描述
in关键字	SELECT 字段名 FROM 表名 WHERE 字段 in ( 数据 1, 数据 2…);	in 里面的每个数据都会作为一次条件，只要满足条件的就会显示
like关键字	SELECT * FROM 表名 WHERE 字段名 LIKE ’ 通配符字符串’;	LIKE 表示模糊查询
BETWEEN AND关键字	字段名 BETWEEN 值1 AND 值2	表示从值 1 到值 2 范围，包头又包尾

IS [NOT] NULL关键字

某字段是否为null	SQL	描述
是	SELECT * FROM 表1 WHERE 字段1 IS NULL	查询表1中字段1是null的所有记录
否	SELECT * FROM 表1 WHERE 字段1 IS NOT NULL	查询表1中字段1不是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);

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

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

select * from student3 where math>80;

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

select * from student3 where english <=80;

查询 age 等于 20 岁的学生

select * from student3 where age = 20;

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

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

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

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

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

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

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

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

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

使用关键字相关操作:

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

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

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

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

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

select * from student3 where english between 75 and 90;

使用通配符相关操作:

#查询姓马的学生

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

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

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

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

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

(2) 高级查询

语法格式:

语法格式	说明
SELECT [DISTNCT] 列名 FROM 表名 [WHERE 条件表达式1] [GROUP BY 字段名,… [HAVING 条件表达式2]] [ORDER BY 字段名 [ASC| DESC] ] [LIMIT [OFFSET] 记录数	①GROUP BY 表示分组查询,可按多个字段分组,HAVING 为可选项表示条件,当出现HAVING 时就无法使用Where作为条件 ② ORDER BY表示排序,对筛选过后的数据进行排序,默认升序ASC,降序需要加上DESC ③ LIMIT 表示分页查询,OFFSET用来设置偏移量,默认偏移量为0

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

函数分为内置函数和自定义函数,内置函数是系统内置的通用函数,而自定义函数是根据自己需求编写的函数,下面列出的函数是常用的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数据库的函数,不同数据库的函数存在一定差异,在其他数据库中可能无法使用 )。

聚合函数

函数名称	作 用
MAX	查询指定列的最大值
MIN	查询指定列的最小值
COUNT	统计查询结果的行数
SUM	求和，返回指定列的总和
AVG	求平均值，返回指定列数据的平均值

练习:

# 查询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

数值型函数

• 基本函数

函数名称	作 用
ABS(x)	返回x的绝对值
SIGN(X)	返回X的符号。正数返回1，负数返回-1，0返回0
PI()	返回圆周率的值
CEIL(x)，CEILING(x)	返回大于或等于某个值的最小整数
FLOOR(x)	返回小于或等于某个值的最大整数
LEAST(e1,e2,e3…)	返回列表中的最小值
GREATEST(e1,e2,e3…)	返回列表中的最大值
MOD(x,y)	返回X除以Y后的余数
RAND()	返回0~1的随机值
RAND(x)	返回0~1的随机值，其中x的值用作种子值，相同的X值会产生相同的随机数
ROUND(x)	返回一个对x的值进行四舍五入后，最接近于X的整数
ROUND(x,y)	返回一个对x的值进行四舍五入后最接近X的值，并保留到小数点后面Y位
TRUNCATE(x,y)	返回数字x截断为y位小数的结果
SQRT(x)	返回x的平方根。当X的值为负数时，返回NULL

• 角度与弧度互换函数

函数名称	作 用
RADIANS(x)	将角度转化为弧度，其中，参数x为角度值
DEGREES(x)	将弧度转化为角度，其中，参数x为弧度值

• 三角函数

函数名称	作 用
SIN(x)	返回x的正弦值，其中，参数x为弧度值
ASIN(x)	返回x的反正弦值，即获取正弦为x的值。如果x的值不在-1到1之间，则返回NULL
COS(x)	返回x的余弦值，其中，参数x为弧度值
ACOS(x)	返回x的反余弦值，即获取余弦为x的值。如果x的值不在-1到1之间，则返回NULL
TAN(x)	返回x的正切值，其中，参数x为弧度值
ATAN(x)	返回x的反正切值，即返回正切值为x的值
ATAN2(m,n)	返回两个参数的反正切值
COT(x)	返回x的余切值，其中，X为弧度值

• 指数与对数

函数名称	作 用
POW(x,y)，POWER(X,Y)	返回x的y次方
EXP(X)	返回e的X次方，其中e是一个常数，2.718281828459045
LN(X)，LOG(X)	返回以e为底的X的对数，当X <= 0 时，返回的结果为NULL
LOG10(X)	返回以10为底的X的对数，当X <= 0 时，返回的结果为NULL
LOG2(X)	返回以2为底的X的对数，当X <= 0 时，返回NULL

• 进制间的转换

函数名称	作 用
BIN(x)	返回x的二进制编码
HEX(x)	返回x的十六进制编码
OCT(x)	返回x的八进制编码
CONV(x,f1,f2)	返回f1进制数变成f2进制数

练习:

#返回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);

字符串函数

函数名称	作 用
LENGTH	计算字符串长度函数，返回字符串的字节长度
CONCAT	合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个
INSERT	替换字符串函数
LOWER	将字符串中的字母转换为小写
UPPER	将字符串中的字母转换为大写
LEFT	从左侧字截取符串，返回字符串左边的若干个字符
RIGHT	从右侧字截取符串，返回字符串右边的若干个字符
TRIM	删除字符串左右两侧的空格
REPLACE	字符串替换函数，返回替换后的新字符串
SUBSTRING	截取字符串，返回从指定位置开始的指定长度的字符换
REVERSE	字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串

日期和时间函数

• 获取日期、时间

函数名称	作 用
CURDATE() ，CURRENT_DATE()	返回当前日期，只包含年、月、日
CURTIME() ， CURRENT_TIME()	返回当前时间，只包含时、分、秒
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() /LOCALTIMESTAMP()	返回当前系统日期和时间
UTC_DATE()	返回UTC（世界标准时间）日期
UTC_TIME()	返回UTC（世界标准时间）时间

练习:

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

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

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

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

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

• 日期与时间戳的转换

函数名称	作 用
UNIX_TIMESTAMP()	以UNIX时间戳的形式返回当前时间。SELECT UNIX_TIMESTAMP() ->1634348884
UNIX_TIMESTAMP(date)	将时间date以UNIX时间戳的形式返回。
FROM_UNIXTIME(timestamp)	将UNIX时间戳的时间转换为普通格式的时间

练习:

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

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

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

# 获取当前时间的时间戳
SELECT FROM_UNIXTIME(CURRENT_TIME())

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

函数名称	作 用
YEAR(date) / MONTH(date) / DAY(date)	返回具体的日期值
HOUR(time) / MINUTE(time) /SECOND(time)	返回具体的时间值
MONTHNAME(date)	返回月份：January，…
DAYNAME(date)	返回星期几：MONDAY，TUESDAY…SUNDAY
WEEKDAY(date)	返回周几，注意，周1是0，周2是1，。。。周日是6
QUARTER(date)	返回日期对应的季度，范围为1～4
WEEK(date) ， WEEKOFYEAR(date)	返回一年中的第几周
DAYOFYEAR(date)	返回日期是一年中的第几天
DAYOFMONTH(date)	返回日期位于所在月份的第几天
DAYOFWEEK(date)	返回周几，注意：周日是1，周一是2，。。。周六是7

练习:

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

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

• 日期的操作函数

函数名称	作 用
EXTRACT(type FROM date)	返回指定日期中特定的部分，type指定返回的值

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

练习:

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

• 时间和秒钟转换的函数

函数名称	作 用
TIME_TO_SEC(time)	将 time 转化为秒并返回结果值。转化的公式为： 小时3600+分钟60+秒
SEC_TO_TIME(seconds)	将 seconds 描述转化为包含小时、分钟和秒的时间

练习:

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

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

• 计算日期和时间的函数

函数名称	作 用
DATE_ADD(datetime, INTERVAL expr type)，ADDDATE(date,INTERVAL expr type)	返回与给定日期时间相差INTERVAL时间段的日期时间
DATE_SUB(date,INTERVAL expr type)，SUBDATE(date,INTERVAL expr type)	返回与date相差INTERVAL时间间隔的日期

上述函数中type的取值：

函数名称	作 用
ADDTIME(time1,time2)	返回time1加上time2的时间。当time2为一个数字时，代表的是秒 ，可以为负数
SUBTIME(time1,time2)	返回time1减去time2后的时间。当time2为一个数字时，代表的是 秒 ，可以为负数
DATEDIFF(date1,date2)	返回date1 - date2的日期间隔天数
TIMEDIFF(time1, time2)	返回time1 - time2的时间间隔
FROM_DAYS(N)	返回从0000年1月1日起，N天以后的日期
TO_DAYS(date)	返回日期date距离0000年1月1日的天数
LAST_DAY(date)	返回date所在月份的最后一天的日期
MAKEDATE(year,n)	针对给定年份与所在年份中的天数返回一个日期
MAKETIME(hour,minute,second)	将给定的小时、分钟和秒组合成时间并返回
PERIOD_ADD(time,n)	返回time加上n后的时间

• 日期的格式化与解析

函数名称	作 用
DATE_FORMAT(date,fmt)	按照字符串fmt格式化日期date值
TIME_FORMAT(time,fmt)	按照字符串fmt格式化时间time值
GET_FORMAT(date_type,format_type)	返回日期字符串的显示格式
STR_TO_DATE(str, fmt)	按照字符串fmt对str进行解析，解析为一个日期

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

符号	说明	符号	说明

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

流程控制函数

函数名称	作 用
CASE	搜索语句
IF(expr,v1,v2)	如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。
IFNULL(v1,v2)	如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。
NULLIF(expr1, expr2)	比较两个字符串，如果字符串 expr1 与 expr2 相等 返回 NULL，否则返回 expr1

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

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;

运行效果:

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

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

语法格式如下:

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

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

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

练习:

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

# 按数学降序,英语降序进行排序【多列排序】
SELECT * FROM student3
ORDER BY math DESC ,english DESC

运行效果:

分组查询(作用在行上)

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

语法格式:

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

单独使用:

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

搭配聚合函数一起使用:

# 分别统计班上男生和女生的数学和英语的平均分
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

运行效果:

分页查询(作用在行上)

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

语法格式:

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

单个参数查询

在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;

运行效果:

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

#前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 条，就可以使用 LIMIT 1 ，告诉 SELECT 语句只需要返回一条记录即可。这样的好处就是 SELECT 不需 要扫描完整的表，只需要检索到一条符合条件的记录即可返回。

(3) 多表查询

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

笛卡尔积（交叉连接）

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

语法格式:

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

二张表的字段不相同

练习:

# 创建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;

运行效果:

二张表的字段相同:

练习:

#创建一张和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;

运行效果:

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

内连接

内连接(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.关系字段

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

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

等值连接

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

运行效果:

非等值连接

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

运行效果:

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

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

相同属性组:

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

运行效果:

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

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

运行效果:

外连接:

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

语法格式:

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

LEFT JOIN (左连接):

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

练习:

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

运行效果:

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

运行效果:

UNION的使用:

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

语法格式:

SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2

UNION 操作符

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

练习:

SELECT * FROM  student3

UNION 

SELECT * FROM student4

运行效果:

UNION ALL操作符

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

练习:

SELECT * FROM  student3

UNION ALL

SELECT * FROM student4

运行效果:

子查询:

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

单行子查询:

单行比较操作符	说明
> 、< 、<= 、>= 、= 、<>	<>在 SQL 中表示不等于，在 mysql 中也可以使用 !=

练习:

# 查询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班')

运行效果:


多行子查询:

多行比较操作符	说明
IN	等于列表中的任意一个
[NOT] EXISTS	子查询不产生任何数据,相当于测试,只返回TRUE和FALSE,当返回TRUE时,外层查询才执行
ANY	需要和单行比较操作符一起使用，和子查询返回的某一个值比较
ALL	需要和单行比较操作符一起使用，和子查询返回的所有值比较
SOME	实际上是ANY的别名，作用相同，一般常使用ANY

练习:

• IN

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

运行效果:

• 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)

运行效果:

备注:在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)

运行效果:

• ALL

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

运行效果:

8.数据库TCL的常用命令

(1) 事务

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

SQL语句	描述
START TRANSACTION	开启事务
COMMIT	提交事务
ROLLBACK	回滚事务(只能回滚未提交的事务)
SET SESSION TRANSACTION ISOLATION LEVEL 事务隔离级别	设置事务的隔离级别

(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);

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

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

在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;

在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;

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

执行第二次事务

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

查询第二次事务

(3) 事务隔离级别

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

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

SELECT @@tx_isolation;

运行效果:

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

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

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

运行效果:

9.存储过程与存储函数

(1) 存储过程

创建存储过程

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

说明：
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'

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语句的限制。

限制名	描述
CONTAINS SQL	表示当前存储过程的子程序包含SQL语句，但是并不包含读写数据的SQL语句；（默认情况）
NO SQL	表示当前存储过程的子程序中不包含任何SQL语句；
READS SQL DATA	表示当前存储过程的子程序中包含读数据的SQL语句；
MODIFIES SQL DATA	表示当前存储过程的子程序中包含写数据的SQL语句。

4. SQL SECURITY { DEFINER | INVOKER }

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

权限名	描述
DEFINER	表示只有当前存储过程的创建者或者定义者才能执行当前存储过程；
INVOKER	表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。

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

DELIMITER $ # 定义结束符号为$
CREATE PROCEDURE select_all_data()
DETERMINISTIC
CONTAINS SQL
BEGIN
SELECT * FROM student3;
SELECT * FROM class;
END $
DELIMITER ; # 恢复结束符号为;

定义存储过程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 ; # 恢复结束符号为;

定义存储过程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 ; # 恢复结束符号为;

定义存储过程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 ; # 恢复结束符号为;

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

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

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

调用存储过程

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

格式:

格式名称	SQL语句
调用无参	CALL 存储过程名();
调用IN模式的参数	CALL sp1(‘值’);
调用out模式的参数	SET @name; CALL sp1(@name); SELECT @name;
调用inout模式的参数	SET @name=值; CALL sp1(@name); SELECT @name;

练习:

CALL  select_all_data();

运行效果:

CALL select_name_students('五班');

运行效果:

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

运行效果:

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

运行效果:

(2) 存储函数

创建存储函数

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

说明：

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

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

SELECT 函数名(实参列表)

练习:

# 创建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 '班级';

运行效果:

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

	关键字	调用语法	返回值	应用场景
存储过程	PROCEDURE	CALL 存储过程()	理解为有0个或多个	一般用于更新
存储函数	FUNCTION	SELECT 函数()	只能是一个	一般用于查询结果为一个值并返回时

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

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

操作	语法
查看存储过程或存储函数创建信息	SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名
查看存储过程或存储函数状态信息	SHOW {PROCEDURE | FUNCTION} STATUS [LIKE ‘存储过程名或函数名’]
从information_schema.Routines表中查看存储过程和函数的信息	SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME=‘存储过程或函数的名’ [AND ROUTINE_TYPE = {‘PROCEDURE | FUNCTION’}];
修改存储过程或函数的特性	ALTER {PROCEDURE|FUNCTION} 存储过程或函数的名 [characteristic …]
删除存储过程或函数	DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程或函数的名

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

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

参数解释:

参数	解释
CONTAINS SQL	表示子程序包含SQL语句，但不包含读或写数据的语句。
NO SQL	表示子程序中不包含SQL语句。
READS SQL DATA	表示子程序中包含读数据的语句。
MODIFIES SQL DATA	表示子程序中包含写数据的语句。
SQL SECURITY { DEFINER | INVOKER }	指明谁有权限来执行。
DEFINER	表示只有定义者自己才能够执行。
INVOKER	表示调用者可以执行。
COMMENT ‘string’	表示注释信息。

练习:

# 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;

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) 查看系统变量

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

SQL命令	描述
SHOW GLOBAL VARIABLES;	查看所有全局变量
SHOW SESSION VARIABLES; |SHOW VARIABLES;	查看所有会话变量
SHOW GLOBAL VARIABLES LIKE ‘%标识符%’;	查看满足条件的部分系统变量
SHOW SESSION VARIABLES LIKE ‘%标识符%’;	查看满足条件的部分会话变量

举例:

SHOW GLOBAL VARIABLES LIKE 'admin_%';

• 查看指定系统变量

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

SQL命令	描述
SELECT @@global.变量名;	查看指定的系统变量的值
SELECT @@session.变量名;|SELECT @@变量名;	查看指定的会话变量的值

• 修改系统变量的值

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

SQL命令	描述
SET @@global.变量名=变量值;	修改全局系统变量方式1
SET GLOBAL 变量名=变量值;	修改全局系统变量方式2
SET @@session.变量名=变量值;	修改会话系统变量方式1
SET SESSION 变量名=变量值;	修改会话系统变量方式2

举例:

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;

(3) 用户变量

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

• 会话用户变量

变量的定义:

SQL命令	描述
SET @用户变量 = 值;	变量的定义
SET @用户变量 := 值;	变量的定义
SELECT @用户变量 := 表达式 [FROM 等子句];	变量的定义
SELECT 表达式 INTO @用户变量 [FROM 等子句];	变量的定义
SELECT @用户变量;	查看用户变量的值

举例:

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

• 局部变量

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

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

举例:

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 ;

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

	作用域	定义位置	语法
会话用户变量	当前会话	会话的任何地方	加@符号，不用指定类型
局部变量	定义它的BEGIN END中	BEGIN END的第一句话	一般不用加@,需要指定类型

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

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

定义条件:

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

说明:

• 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';

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

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

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

取值	描述
CONTINUE	表示遇到错误不处理，继续执行。
EXIT	表示遇到错误马上退出。
UNDO	表示遇到错误后撤回之前的操作。MySQL中暂时不支持这样的操作。

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

取值	描述
SQLSTATE ‘字符串错误码’	表示长度为5的sqlstate_value类型的错误代码；
MySQL_error_code	匹配数值类型错误代码；
错误名称	表示DECLARE … CONDITION定义的错误条件名称。
SQLWARNING	匹配所有以01开头的SQLSTATE错误代码；
NOT FOUND	匹配所有以02开头的SQLSTATE错误代码；
SQLEXCEPTION	匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码；

处理语句：如果出现上述条件之一，则采用对应的处理方式，并执行指定的处理语句。语句可以是
像“ 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';

(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

举例:

IF val IS NULL
THEN SELECT 'val is null';
ELSE SELECT 'val is not null';
END IF;

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
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;

3. 循环结构之LOOP

END LOOP [loop_label]

举例:

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;

4. 循环结构之WHILE

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

举例:

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();

5. 循环结构之REPEAT

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

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

举例:

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 ;

6. 跳转语句之LEAVE语句

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

LEAVE 标记名

举例:

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 ;

7. 跳转语句之ITERATE语句

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

ITERATE label

举例:

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 ;

(7) 游标

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

1. 第一步，声明游标

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

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

DECLARE cursor_name CURSOR FOR select_statement;

举例：

DECLARE cur_emp CURSOR FOR
SELECT employee_id,salary FROM employees;

2. 第二步，打开游标

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

打开游标的语法如下：

OPEN cursor_name

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

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

语法如下：

FETCH cursor_name INTO var_name [, var_name] ...

举例:

FETCH　cur_emp INTO emp_id, emp_sal ;

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

4. 关闭游标

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

CLOSE cursor_name

举例:

创建存储过程“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 ;

11.视图

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

(1) 创建视图

语法格式

• 详细格式:

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

• 参数解释:

参数	解释
OR REPLACE	表示该语句能够替换已有视图
ALGORITHM	表示视图选择的算法
UNDEFINED	表示Mysql将自动选择所需要的算法
MERGE	表示将使用视图的语句与视图定义合并起来,使得视图定义的某一部分取代语句对应部分
TEMPTABLE	表示将视图的结果存入临时表,然后使用临时表执行语句
column_list	表示属性清单，指定了视图中各个属性的名,默认情况与SELECT语句查询的属性相同
WITH CHECK OPTION	表示创建视图时要保证在该视图的权限范围之内
CASCADED	表示在创建视图时,需要满足跟该视图有关的所有相关的视图和表的条件,该参数为默认值
LOCAL	表示创建视图时,只要满足该视图本身定义的条件即可

• 精简格式:

CREATE VIEW 视图名称
AS 查询语句

创建视图

• 在单表上创建视图

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

• 在多表上创建视图

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

(2) 查看视图

SQL语句	描述
SHOW TABLES;	查看数据库的表对象、视图对象
DESC / DESCRIBE 视图名称;	查看视图的结构
SHOW TABLE STATUS LIKE ‘视图名称|表名’ [\G]	查看视图的属性信息(显示数据表的存储引擎、版本、数据行数和数据大小等)
SHOW CREATE VIEW 视图名称;	查看视图的详细定义信息
SELECT * FROM 视图名;	查询视图执行结果,将视图当成表来操作即可

练习:

# 查看数据库的表对象、视图对象
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;

DESC select_all_students_view; 执行结果如下:

上图列名解释:

列名	解释
NULL	表示该列是否可以存储NULL值
Key	表示该列是否已经编制索引
Default	表示该列是否为默认值
Extra	表示获得到的与给定列相关的附加信息

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]

练习:

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;

运行效果:

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

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

练习:

# 修改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;

运行效果:

(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 `姓名` = '马云';

更新前:

更新后:

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

使用INSERT语句更新视图

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

练习:

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

运行结果:

使用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;

运行效果:
更新前:

更新后:

(5) 删除视图

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

语法格式:

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

练习:

DROP VIEW IF EXISTS 
student_view,select_all_students_view

运行效果:

查询基本表:

 SELECT * from class;
 SELECT * FROM student3;

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

12.触发器

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

(1) 创建触发器

语法格式:

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

说明：

参数	解释
表名	表示触发器监控的对象
BEFORE	AFTER
INSERT | UPDATE | DELETE	表示触发的事件
INSERT	表示插入记录时触发
UPDATE	表示更新记录时触发
DELETE	表示删除记录时触发

练习:
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;

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 ;

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

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

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

SELECT * FROM test_trigger_log;

运行效果:

(2) 查看触发器

SQL语句	说明
SHOW TRIGGERS[\G] ;	查看当前数据库的所有触发器的定义
SHOW CREATE TRIGGER 触发器名;	查看当前数据库中某个触发器的定义
SELECT * FROM information_schema.TRIGGERS;	从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息。

练习:

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

(3) 删除触发器

语法格式:

DROP TRIGGER  IF EXISTS 触发器名称;

练习:

DROP TRIGGER  IF EXISTS before_insert;

运行效果:

13.用户管理

(1) 创建用户

使用CREATE USER 语句创建用户

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

语法格式:

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

参数解释:

参数	解释
用户名	表示新建用户的账户，由 用户（User） 和 主机名（Host） 构成；
“[ ]”	表示可选，也就是说，可以指定用户登录时需要密码验证，也可以不指定密码验证，这样用可以直接登录。不过，不指定密码的方式不安全，不推荐使用。如果指定密码值，这里需要使用
IDENTIFIED BY	指定明文密码值。
CREATE USER语句	表示可以同时创建多个用户。

练习:

• 创建用户zhang3 ,密码123123

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

• 创建用户lisi密码123456

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

使用INSERT语句创建用户

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

语法格式:

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

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

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

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

FLUSH PRIVILEGES;

练习:

• 创建用户wangmazhi,密码123456

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

执行结果:

• 刷新权限表

FLUSH PRIVILEGES;

运行效果:

使用GRANT语句创建用户

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

语法格式:

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

参数解释:

参数	解释
privileges	表示访用户且有的权限信息
database.table	表示新用户的权限范围表,可以在指定的数掘库.表上使用自己的权限
username	表示新用户的名称
hostname	表示主机名
password	表示新用户的密码

练习:

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

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

运行效果:

(2) 修改用户

修改root用户密码

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

语法格式:

方式1:

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

方式2( 不推荐 ):

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

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

FLUSH PRIVILEGES;

方式3:

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

练习:

• 方式1修改密码

mysqladmin -u root  -p password  123456

• 方式2修改密码

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

• 方式3修改密码

SET PASSWORD = PASSWORD('123456');

root用户普通用户修改密码

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

方式3:

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

练习:

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

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

使用DROP方式删除（推荐）

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

语法格式:

DROP USER user[,user]…;

练习:

• 删除用户zhang3

DROP USER zhang3;

使用DELETE方式删除

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

语法格式:

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

练习:

• 删除用户lisi

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

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

(1) 用户权限相关概念

权限列表

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

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

权限解释:

权限	说明
CREATE和DROP	可以创建新的数据库和表，或删除（移掉）已有的数据库和表。如果将
DROP	授予某用户，用户就可以删除MySQL访问权限保存的数据库。
SELECT、INSERT、UPDATE和DELETE	允许在一个数据库现有的表上实施操作。
SELECT	只有在它们真正从一个表中检索行时才被用到。
INDEX	允许创建或删除索引，INDEX适用于已有的表。如果具有某个表的CREATE权限，就可以在CREATE TABLE语句中包括索引定义。
ALTER	可以使用ALTER TABLE来更改表的结构和重新命名表。
CREATE ROUTINE	用来创建保存的程序（函数和程序）
ALTER ROUTINE	用来更改和删除保存的程序
EXECUTE	用来执行保存的程序。
GRANT	允许授权给其他用户，可用于数据库、表和保存的程序。
FILE	使用户可以使用LOAD DATA INFILE和SELECT … INTO OUTFILE语句读或写服务器上的文件，任何被授予FILE权限的用户都能读或写MySQL服务器上的任何文件（说明用户可以读任何数据库目录下的文件，因为服务器可以访问这些文件）。

授予权限的原则

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

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

(2) 授予权限

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

语法规则:

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

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

练习:

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

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

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

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

(3) 查看权限

查看当前用户权限

SHOW GRANTS;
# 或
SHOW GRANTS FOR CURRENT_USER;
# 或
SHOW GRANTS FOR CURRENT_USER();

查看某用户的全局权限

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

练习:

• 查看当前用户权限

SHOW GRANTS;

• 查看user用户的全局权限

SHOW GRANTS FOR 'user'@'localshot' ;

(4) 收回权限

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

语法格式:

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

练习:

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

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

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

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

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

15.角色管理

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

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

操作	语法格式	说明
创建角色	CREATE ROLE ‘role_name’[@‘host_name’] [,‘role_name’[@‘host_name’]]…	角色名称的命名规则和用户名类似。如果 host_name省略，默认为% ， role_name不可省略 ，不可为空。
给角色赋予权限	GRANT privileges ON table_name TO ‘role_name’[@‘host_name’];	语句中privileges代表权限的名称，多个权限以逗号隔开。可使用SHOW语句查询权限名称
查看角色的权限	SHOW GRANTS FOR ‘角色名’;	只要你创建了一个角色，系统就会自动给你一个“ USAGE ”权限，意思是 连接登录数据库的权限 。
回收角色的权限	REVOKE privileges ON tablename FROM ‘rolename’;	privileges 权限名称;tablename表名,需要对哪一张收回权限;rolename角色名
删除角色	DROP ROLE role [,role2]…	注意,如果你删除了角色，那么用户也就失去了通过这个角色所获得的所有权限 。
给用户赋予角色	GRANT role [,role2,…] TO user [,user2,…];	role代表角色，user代表用户。可将多个角色同时赋予多个用户，用逗号隔开即可。
激活角色	SET DEFAULT ROLE ALL TO ‘用户名’@‘localhost’;	给用户授权后,用户的权限需要激活
激活角色	SET GLOBAL activate_all_roles_on_login=ON;	当activate_all_roles_on_login为OFF时需要手动激活,当为ON时，用户赋予角色后角色就会激活
查看activate_all_roles_on_login的值	show variables like ‘activate_all_roles_on_login’;	默认为OFF
撤销用户的角色	REVOKE role FROM user;	role为角色;user为用户

注意下面练习未做测试

练习:

• 创建角色manager

CREATE ROLE 'manager'@'localhost';

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

GRANT SELECT ON test.*TO 'manager';

• 查看角色manager的权限

SHOW GRANTS FOR 'manager';

• 给user用户添加manager角色

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

• 激活用户权限

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

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

• 设置activate_all_roles_on_login设置为ON

SET GLOBAL activate_all_roles_on_login=ON;

• 查看ctivate_all_roles_on_login的值

show variables like 'activate_all_roles_on_login';

• 回收manager角色的SELECT权限

REVOKE SELECT ON test.* FROM 'manager';

• 撤销user用户的角色manager

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

• 删除角色manager

DROP ROLE manager;