MySQL基础

1. MySQl 基本知识

1.1 MySQL语法规范

1. 不区分大小写,但建议关键字大写，表名、列名小写
2. 每条命令最好用分号结尾
3. 每条命令根据需要，可以进行缩进 或换行
4. 注释

  单行注释：#注释文字
  单行注释：-- 注释文字
  多行注释：/* 注释文字  */

5. 空值参与运算

# 空值：null
# null 不等同于 0,'','null'
# 任何空值参与的运算结果都为null

1.2 命名规则

• 数据库、表名不得超过30个字符，变量名限制为29个
• 必须只能包含A-Z, a-z,0-9,_共63个字符
• 数据库名、表名、字段名等对象名中间不要包含空格
• 同一个MySQL软件中，数据库不能同名;同一个库中，表不能重名;同一个表中，字段不能重名
• 必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用`(着重号）引起来
• 保持字段名和类型的一致性，在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了

1.3 SQL语言分类

• DQL（Data Query Language）：数据查询语言
  select
• DML(Data Manipulate Language):数据操作语言
  insert 、update、delete
• DDL（Data Define Languge）：数据定义语言
  create、drop、alter
• TCL（Transaction Control Language）：事务控制语言
  commit、rollback

导入数据

7. 方式一: source 绝对路径
8. 方式二: 图形界面下导入

2. MySQL数据库基本操作-DDL

DDL(Data Definition Language)，数据定义语言

2.1 对数据库的常用操作

功能	SQL
查看所有的数据库	show databases;
创建数据库	create database[ if not exists] mydb1 [charset=utf8];
切换（使用）数据库	use mydb1;
删除数据库	drop database [if not exists];
修改数据库编码	alter database mydab1 character set utf8

创建数据库的三种方法

• 方式1：创建数据库
  CREATE DATABASE 数据库名;
• 方式2：创建数据库并指定字符集
  CREATE DATABASE 数据库名 CHARACTER SET 字符集;
• 方式3：判断数据库是否已经存在，不存在则创建数据库 （ 推荐 ）
  CREATE DATABASE IF NOT EXISTS 数据库名;

注意：DATABASE 不能改名。一些可视化工具可以改名，它是建新库，把所有表复制到新库，再删旧库完成的。

使用数据库

• 查看当前所有的数据库
  SHOW DATABASES; #有一个S，代表多个数据库
• 查看当前正在使用的数据库
  SELECT DATABASE(); #使用的一个 mysql 中的全局函数
• 查看指定库下所有的表
  SHOW TABLES FROM 数据库名;
• 查看数据库的创建信息

SHOW CREATE DATABASE 数据库名;
或者：
SHOW CREATE DATABASE 数据库名\G

• 使用/切换数据库
  USE 数据库名;

修改数据库

• 更改数据库字符集
  ALTER DATABASE 数据库名 CHARACTER SET 字符集; #比如：gbk、utf8等
• 删除指定的数据库
  DROP DATABASE 数据库名;
  或者 (推荐)
  DROP DATABASE IF EXISTS 数据库名;

2.2 对表结构的基本操作

功能	SQL
查看当前数据库的所有表名称	show tables;
查看指定某个表的创建语句	show create table 表名
查看表结构	desc 表名;
删除表	drop table 表名

创建表

方式一： 创建一个空白的表

CREATE TABLE [IF NOT EXISTS] 表名(
字段1, 数据类型 [约束条件] [默认值],
字段2, 数据类型 [约束条件] [默认值],
字段3, 数据类型 [约束条件] [默认值],
……
[表约束条件]
);

方式二: 使用 AS subquery 选项，将创建表和插入数据结合起来

CREATE TABLE 表名 AS subquery

举例：

CREATE TABLE emp1 AS SELECT * FROM employees;
-- 创建的emp2是空表
CREATE TABLE emp2 AS SELECT * FROM employees WHERE 1=2; 

2.3 修改表结构

• 修改表添加列
  alter table 表名 add 列名 类型(长度) [约束];

#为student表添加一个新的字段为:系别dept类型为varchar(20)
alter table student add dept varchar(20);

• 修改一个列
  ALTER TABLE 表名 MODIFY [COLUMN] 字段名1 字段类型 [DEFAULT 默认值][FIRST|AFTER 字段名2];

# 将学生表的name字段的varchar长度改为30
ALTER TABLE student MODIFY name VARCHAR(30);

• 修改列名
  alter table 表名 change 旧列名 新列名 类型(长度) 约束;

#为student表的dept字段更换为department varchar(30)
ALTER TABLE student change `dept` department VARCHAR(30); 

• 修改表删除列
  alter table 表名 drop 列名;

#删除student表中department这列
ALTER TABLE student DROP department;

• 修改表名
  alter table 表名 to 新表名;

#将表student改名成 stu
rename table student to stu;

• 清空表
  删除表中所有的数据并释放表的存储空间
  TRUNCATE TABLE 表名
  注意此操作不能回滚，而DELETE语句删除的表数据可以回滚

#清空学生表
TRUNCATE TABLE student;

TRUNCATE TABLE 比 DELETE 速度快，不建议在开发中使用

3. MySQL数据库基本操作-DML

DML是指数据操作语言，英文全称是Data Manipulation Language，用来对数据库中表的数据记录进行更新。

• 插入数据

insert into 表 (列名1,列名2,列名3...) values (值1,值2,值3...)； //向表中插入某些列
insert into 表 values (值1,值2,值3...);     //向表中插入所有列

insert into student values(1001,'AmoGood','男',18,'2001-1-1','广东',22.3)

• 数据修改

update 表名 set 字段名=值,字段名=值...;
update 表名 set 字段名=值,字段名=值... where 条件;

#将所有学生的地址改为揭阳
update student set address='揭阳';

#将sid为1001的学生的地址改为惠来
update student set address='惠来' where sid=1001;

#将sid为1001的学生的生日和分数修改
update student set birth='2001-02-25',score=99.9 where sid=1001;

• 数据删除

delete from 表名 [where 条件];
truncate table  表名 或者 truncate 表名

#1.删除sid为1004的学生数据
delete from student where sid  = 1004;
#2.删除表所有数据
delete from student;
#3.清空表数据
truncate table student;
truncate student;

注意：delete和truncate原理不同，delete只删除内容，而truncate类似于drop table ，可以理解为是将整个表删除，然后再创建该表；

4. MySQL约束

作用:
表在设计的时候加入约束的目的就是为了保证表中的记录完整性和有效性，比如用户表有些列的值（手机号）不能为空，有些列的值（身份证号）不能重复。
分类:

• 主键约束(primary key) PK
• 自增长约束(auto_increment)
• 非空约束(not null)
• 唯一性约束(unique)
• 默认约束(default)
• 零填充约束(zerofill)
• 外键约束(foreign key) FK

4.1 非空约束

添加非空约束

# 建表时
CREATE TABLE 表名称(
 字段名 数据类型,
 字段名 数据类型 NOT NULL,
 字段名 数据类型 NOT NULL
  );
# 建表后
alter table 表名称 modify 字段名 数据类型 not null;

删除非空约束

#去掉not null，相当于修改某个非注解字段，该字段允许为空
alter table 表名称 modify 字段名 数据类型 NULL;
# 或者
alter table 表名称 modify 字段名 数据类型;

4.2 唯一性约束

添加唯一性约束

1. 建表时

create table 表名称( 
字段名 数据类型, 
字段名 数据类型 unique, 
字段名 数据类型 unique key, 字段名 数据类型 
);
#或者
create table 表名称( 
字段名 数据类型, 
字段名 数据类型, 
字段名 数据类型, 
#表级约束语法
[constraint 约束名] unique key(字段名)
 );

2. 建表后

#方式1
alter table 表名称 add unique key(字段列表);
#方式2
alter table 表名称 modify 字段名 字段类型 unique;

注意：字段列表中如果是一个字段，表示该列的值唯一。如果是两个或更多个字段，那么复合唯一，即多个字段的组合是唯 一的

删除唯一性约束

1. 添加唯一性约束的列上也会自动创建唯一索引。
2. 删除唯一约束只能通过删除唯一索引的方式删除。
3. 删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。
4. 如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名。

#查看都有哪些约束
SELECT * FROM information_schema.table_constraints
WHERE table_name = '表名'; 
#查看表的索引
show index from 表名;
# 删除索引
ALTER TABLE 表名 DROP INDEX 索引名;

4.3 主键约束

主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值。

添加主键约束

1. 建表前

create table 表名称( 
字段名 数据类型 primary key,
字段名 数据类型, 
字段名 数据类型 
);
#或
create table 表名称( 
字段名 数据类型,
字段名 数据类型, 
字段名 数据类型, 
[constraint 约束名] primary key(字段名) #表级模式 
);

2. 建表后

ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表); #字段列表可以是一个字段，也可以是多个字段，如果是多 个字段的话，是复合主键

删除主键约束

删除主键约束，不需要指定主键名，因为一个表只有一个主键，删除主键约束后，非空还存在。

alter table 表名称 drop primary key;

4.4 外键约束

限定某个表的某个字段的引用完整性。

添加外键约束

1. 建表时

create table 主表名称(
 字段1 数据类型 primary key,
 字段2 数据类型
  );
#(从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致，逻辑意义也一样  
create table 从表名称(
字段1 数据类型 primary key,
字段2 数据类型,
[CONSTRAINT <外键约束名称>] FOREIGN KEY（从表的某个字段) references 主表名(被参考字段) 
);

2. 建表后

ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) 
REFERENCES 主表名(被引用 字段) [on update xx][on delete xx];

约束等级

• Cascade方式 ：在父表上update/delete记录时，同步update/delete掉子表的匹配记录
• Set null方式 ：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为not null
• No action方式 ：如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作
• Restrict方式 ：同no action， 都是立即检查外键约束
• Set default方式 ：父表有变更时，子表将外键列设置成一个默认的值，但Innodb不能识别

如果没有指定等级，就相当于Restrict方式。
对于外键约束，最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式。

删除外键约束

#(1)第一步先查看约束名和删除外键约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';#查看某个表的约束名 
ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名; 
#（2）第二步查看索引名和删除索引。（注意，只能手动删除） 
SHOW INDEX FROM 表名称; #查看某个表的索引名 
ALTER TABLE 从表名 DROP INDEX 索引名;

5. MySQL数据库基本操作-DQL-基本查询

语法格式：

select 
  [all|distinct]
  <目标列的表达式1> [别名],
  <目标列的表达式2> [别名]...
from <表名或视图名> [别名],<表名或视图名> [别名]...
[where<条件表达式>]
[group by <列名> 
[having <条件表达式>]]
[order by <列名> [asc|desc]]
[limit <数字或者列表>];

SELECT 查询时的两个顺序

1. 关键字的顺序是不能颠倒的：

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...

2.SELECT 语句的执行顺序（在 MySQL 和 Oracle 中，SELECT 执行顺序基本相同）：

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

起别名

# as 可省略
# 列的别名可以使用一对" "引起来，不要使用' '

查看表结构

describe 表名或者desc 表名

5.1 简单查询

#查询所有服装信息
select * from c_clothes;

#查询所有服装的名字，价格信息
select name,price from c_clothes;

#查询所有服装的名字，价格信息(起别名)
select name as cname,price clothes_price from c_clothes;

distinct应用到多个字段的时候，其应用的范围是其后面的所有字段(只有都一致才能被过滤,一般只用于单个属性去重，多个属性没有实际意义)

#去掉重复值(distinct)
select distinct * from c_clothes;

select distinct price, name from c_clothes;

#查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示.
select id,name,price+10 new_price from c_clothes

运算符

5.2 排序查询

如果我们需要对读取的数据进行排序，我们就可以使用 MySQL 的 order by 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。

select 
 字段名1，字段名2，……
from 表名
order by 字段名1 [asc|desc]，字段名2[asc|desc]……

特点:

1. asc代表升序，desc代表降序，如果不写默认升序
2. order by用于子句中可以支持单个字段，多个字段，表达式，函数，别名
3. order by子句，放在查询语句的最后面。LIMIT子句除外
4. 列的别名只能在order by中使用，而不能在where中使用

#使用价格排序(降序)
select * from c_clothes order by price desc;

#在价格排序(降序)的基础上，以库存排序(降序)
select * from c_clothes order by price desc,stock desc;

# 使用列的别名
SELECT name,price * 10 AS "new_price" FROM c_clothes ORDER BY new_price 

# 在where中使用列的别名
SELECT name,price * 10 AS "new_price" FROM c_clothes where new_price =490

5.3 聚合查询

关于COUNT()

1. COUNT(*)返回表中记录总数，适用于任意数据类型
2. COUNT(expr) 返回expr不为空的记录总数
   推荐： 推荐使用COUNT(*) 效率高
   问题：能不能使用count(列名)替换count(*)?

不要使用 count(列名)来替代 count(*) ， count(*) 是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。

说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。

#查询商品的总条数
select count(*) from c_clothes;

#查询价格大于200商品的总条数
select count(*) from c_clothes where price>200; 

#查询商品的最大价格
select * from c_clothes where price=(select max(price) from c_clothes);

#查询商品的最小价格
select * from c_clothes where price=(select min(price) from c_clothes);

#查询所有商品的平均价格
select avg(price) from c_clothes;

对Null的处理

1. count函数对null值的处理
   如果count函数的参数为星号（*），则统计所有记录的个数。而如果参数为某字段，不统计含null值的记录个数。

2. sum和avg函数对null值的处理
   这两个函数忽略null值的存在，就好象该条记录不存在一样。

3. max和min函数对null值的处理
   max和min两个函数同样忽略null值的存在。

5.4 分组查询

分组查询是指使用group by字句对查询信息进行分组

select 字段1,字段2… from 表名 group by 分组字段 having 分组条件;

注意:如果要进行分组的话，则SELECT子句之后，只能出现分组的字段和统计函数，其他的字段不能出现

在SELECT列表中所有未包含在组函数中的列都应该包含在 GROUP BY子句中

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id ;

而包含在GROUP BY子句中的列不必包含在SELECT 列表中

HAVING 子句

1. 行已经被分组。
2. 使用了聚合函数。
3. 满足HAVING 子句中条件的分组将被显示。
4. HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。

WHERE 和HAVING 的区别

1. WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。
2. 如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选。

#统计不同价格衣服的个数
select price ,count(*) from c_clothes group by price;

分组之后的条件筛选
分组之后对统计结果进行筛选的话必须使用having，不能使用where
where子句用来筛选 FROM 子句中指定的操作所产生的行
group by 子句用来分组 WHERE 子句的输出。
having 子句用来从分组的结果中筛选行

select 字段1,字段2… from 表名 group by 分组字段 having 分组条件;

#统计不同价格衣服的个数,且商品价格少于200
select price ,count(*) from c_clothes group by price having price<200;

5.5 分页查询limit

分页查询在项目开发中常见，由于数据量很大，显示屏长度有限，因此对数据需要采取分页显示方式。例如数据共有30条，每页显示5条，第一页显示1-5条，第二页显示6-10条。

-- 方式1-显示前n条
select 字段1，字段2... from 表明 limit n
-- 方式2-分页显示
select 字段1，字段2... from 表明 limit m,n
m: 整数，表示从第几条索引开始，计算方式 （当前页-1）*每页显示条数
n: 整数，表示查询多少条数据

#查询服装表的前5条记录 
select * from c_clothes limit 5

#从第4页开始显示，显示5条 
select * from c_clothes limit 3,5

# order by 放在limit前面
SELECT name,price  FROM c_clothes ORDER BY price DESC limit 0 ,10

MySQL 8.0 中可以使用LIMIT 3 OFFSET 4,意思是获取从第5条记录开始后面的3条记录，和LIMIT 4,3 返回的结果相同

# 查询3，4，5条记录
SELECT name,price FROM c_clothes limit 2,3
# mysql 8.0
SELECT name,price FROM c_clothes limit 3 OFFSET 2

5.6 模糊查询

在模糊查询中 %表示通配符 /代表一个字符
select * from dual like '%....'

1. 查询名字包含a的员工名字
   SELECT last_name FROM employees WHERE last_name like '%a%';
   
2. 查询员工名字即包含a又包含e

• 方式一 SELECT last_name FROM employees WHERE last_name like '%a%' and last_name like '%e%';
• 方式二 SELECT last_name FROM employees WHERE last_name like '%a%e%' or last_name like '%e%a%';
  
  注意： 此处如果只有SELECT last_name FROM employees WHERE last_name like '%a%e%'则查询出来的结果为 名字是a在前e在后的
  

3. 查询员工姓名第二个字母为a的员工姓名
   SELECT last_name FROM employees WHERE last_name like '_a%';
   
4. 查询名字第二个字为_且第三个字为a的员工姓名
   注意: 这种时候就得用到转义字符\用来表示此_ 代表查询的字符
   SELECT last_name FROM employees WHERE last_name like '_\_a%';
   

5.7 将表数据导入另一个表

将一张表的数据导入到另一张表中，有两种选择 SELECT INTO 和 INSERT INTO SELECT

1. 使用INSERT INTO SELECT语句

insert into Table2(field1,field2,…) select value1,value2,… from Table1
或者：
insert into Table2 select * from Table1

注意:要求目标表Table2必须存在

2. 使用SELECT INTO

SELECT vale1, value2 into Table2 from Table1

注意: 要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中。

6. MySQL的多表操作-多表关系

多表查询就是同时查询两个或两个以上的表，因为有的时候用户在查看数据的时候,需要显示的数据来自多张表。

多表查询分为以下几类:

• 交叉连接查询 （产生笛卡尔积）:select * from A,B;

• 内连接查询(使用的关键字 inner join – inner可以省略)

  • 隐式内连接：select * from A,B where 条件;
  • 显示内连接：select * from A inner join B on 条件;

• 外连接查询(使用的关键字 outer join – outer可以省略)

  • 左外连接：select * from A left outer join B on 条件;
  • 右外连接：select * from A right outer join B on 条件;
  • 满外连接: select * from A full outer join B on 条件;

• 子查询:select的嵌套

• 表自关联： 将一张表当成多张表来用
  注意: MySQL不支持满外连接的方法，我们可以通过UNION(去重)或者UNION ALL(不去重)的方法将两个查询结果集连接，从而实现满外连接。由于UNION去重，所以查询效率会低一点，所以我们一般使用UNION ALL

6.1 交叉连接查询

语法:select * from 表1,表2,表3….;

select * from dept,emp

6.2 内连接查询

隐式内连接：select * from A,B where 条件

显示内连接：select * from A inner join B on 条件

1. 查询每个部门的所属员工

#隐式
select * from dept,emp where deptno = dept_id
#显式
-- select * from dept inner join emp on deptno = dept_id;

2. 查询研发部和销售部的所属员工

select * from dept,emp where deptno = dept_id and name in( '研发部','销售部')

3. 查询每个部门的员工数,并升序排序

select name,count(*) as empNumber from dept,emp where deptno = dept_id group by deptno order by empNumber

4. 查询人数大于等于3的部门，并按照人数降序排序

select name,count(*) as empNumber from dept,emp where deptno = dept_id group by deptno having empNumber>3 order by empNumber desc

6.3 外连接查询

外连接分为左外连接（left outer join）、右外连接(right outer join)，满外连接(full outer join)。

• 左外连接：select * from A left outer join B on 条件;
• 右外连接：select * from A right outer join B on 条件;
• 满外连接: select * from A full outer join B on 条件;

注意：oracle里面有full join,可是在mysql对full join支持的不好。我们可以使用union来达到目的。

1. 查询哪些部门有员工，哪些部门没有员工

select * from dept left outer join emp on deptno =dept_id

2. 查询哪些员工有对应的部门，哪些没有

select * from dept right outer join emp on deptno =dept_id

3. 使用union关键字实现左外连接和右外连接的并集

select * from dept left outer join emp on deptno =dept_id
union
select * from dept right outer join emp on deptno =dept_id

6.4 拓展

自然连接

SQL99 在SQL92 的基础上提供了一些特殊语法，比如 NATURAL JOIN 用来表示自然连接。我们可以把自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中 所有相同的字段 ，然后进行 等值连接 。

• 在SQL92标准中：

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;

• 在 SQL99 中你可以写成：

SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

USING连接

当我们进行连接的时候，SQL99还支持使用 USING 指定数据表里的 同名字段 进行等值连接。但是只能配合JOIN一起使用。

平常我们使用的是

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.department_id=d.department_id

现在我们可以使用USING 来简化我们ON后面的语句

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

结论: 与自然连接 NATURAL JOIN 不同的是，USING 指定了具体的相同的字段名称，你需要在 USING的括号 () 中填入要指定的同名字段。同时使用 JOIN...USING 可以简化 JOIN ON 的等值连接。

注意: 我们要 控制连接表的数量 。多表连接就相当于嵌套for循环一样，非常消耗资源，会让SQL查询性能下降得很严重，因此不要连接不必要的表。在许多 DBMS 中，也都会有最大连接表的限制。

【强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时， 保证被关联的字段需要有索引。
说明：即使双表 join 也要注意表索引、SQL 性能。

7. 子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL 4.1开始引入。
SQL 中子查询的使用大大增强了 SELECT 查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。

7.1 子查询的基本使用

• 子查询的基本语法结构：

select * 
from 表名
where 字段 =(
            select 字段
            from 表名
            where 字段=...
            )

• 子查询（内查询）在主查询之前一次执行完成。
• 子查询的结果被主查询（外查询）使用 。

注意事项：

• 子查询要包含在括号内
• 将子查询放在比较条件的右侧
• 单行操作符对应单行子查询，多行操作符对应多行子查询

7.2 子查询分类

我们按内查询的结果返回一条还是多条记录，将子查询分为 单行子查询 、 多行子查询 。

单行子查询分类符号

题目: 查询最低工资大于50号部门最低工资的部门id和其最低工资

SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) >(SELECT MIN(salary)
					FROM employees
					WHERE department_id = 50);

多行比较操作符

题目: 查询平均工资最低的部门id

#方式1：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
						SELECT MIN(avg_sal)
						FROM (
						SELECT AVG(salary) avg_sal
						FROM employees
						GROUP BY department_id
						) dept_avg_sal
						)

#方式2：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
							SELECT AVG(salary) avg_sal
							FROM employees
							GROUP BY department_id
)

#方式3
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT 1;

7.3 相关子查询

如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为 关联子查询 。

相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。

题目： 查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id

# 方式一 相关子查询
SELECT last_name,salary,department_id
FROM employees e
WHERE salary >(
SELECT AVG(salary)
FROM employees  
WHERE department_id=e.department_id
)

#方式二 在from中使用子查询
SELECT e.last_name,e.salary,e.department_id
FROM employees e,(SELECT department_id,AVG(salary) "sal"
FROM employees
GROUP BY department_id) t_avg_sal
WHERE e.department_id=t_avg_sal.department_id
AND e.salary>t_avg_sal.sal;

7.4 EXISTS 与 NOT EXISTS关键字

1. 关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。

2. 如果在子查询中不存在满足条件的行：

   • 条件返回 FALSE
   • 继续在子查询中查找

3. 如果在子查询中存在满足条件的行：

   • 不在子查询中继续查找
   • 条件返回 TRUE

4. NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。

题目： 查询公司管理者的employee_id，last_name，job_id，department_id信息

#方式一 子查询
SELECT employee_id, last_name, job_id, department_id
FROM employees e1
WHERE EXISTS ( SELECT *
FROM employees e2
WHERE e2.manager_id =
e1.employee_id);

方式二 自连接
SELECT DISTINCT e1.employee_id, e1.last_name, e1.job_id, e1.department_id
FROM employees e1 JOIN employees e2
WHERE e1.employee_id = e2.manager_id;

方式三 EXISTS
SELECT  e1.employee_id, e1.last_name, e1.job_id, e1.department_id
FROM employees e1 
WHERE EXISTS(SELECT * FROM employees WHERE e1.employee_id=manager_id)

题目： 查询departments表中，不存在于employees表中的部门的department_id和department_name

SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT *
FROM employees
WHERE department_id = d.department_id);

7.5 子查询相关的CRUD

相关更新

使用相关子查询依据一个表中的数据更新另一个表的数据。

UPDATE table1 alias1
SET column = (SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);

题目： 在employees中增加一个department_name字段，数据为员工对应的部门名称

# 1.
ALTER TABLE employees
ADD(department_name VARCHAR2(14));
# 2.
UPDATE employees e
SET department_name = (SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id);

相关删除

使用相关子查询依据一个表中的数据删除另一个表的数据。

DELETE FROM table1 alias1
WHERE column operator (SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);

题目： 删除表employees中，其与emp_history表皆有的数据

DELETE FROM employees e
WHERE employee_id in
(SELECT employee_id
FROM emp_history
WHERE employee_id = e.employee_id);

总结: 如果一个题可以用子查询或者自连接方式解决，建议使用自连接

8. 视图

• 视图是一种 虚拟表 ，本身是不具有数据 的，占用很少的内存空间，它是 SQL 中的一个重要概念。
• 视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。
• 视图的创建和删除只影响视图本身，不影响对应的基表。但是当对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化，反之亦然。

8.1 创建视图

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

简化:

CREATE VIEW 视图名称
AS 查询语句

在创建视图时，没有在视图名后面指定字段列表，则视图中字段列表默认和SELECT语句中的字段列表一致。如果SELECT句中给字段取了别名，那么视图中的字段名和别名相同。

# 创建多表联合视图
CREATE VIEW dept_sum_vu
(name, minsal, maxsal, avgsal)
AS
SELECT d.department_name, MIN(e.salary), MAX(e.salary),AVG(e.salary)
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name;

查询视图

SELECT * FROM dept_sum_vu;

8.2 查看视图

语法1：查看数据库的表对象、视图对象

SHOW TABLES;

语法2：查看视图的结构

DESC / DESCRIBE 视图名称;

语法3：查看视图的属性信息

# 查看视图信息（显示数据表的存储引擎、版本、数据行数和数据大小等）
SHOW TABLE STATUS LIKE '视图名称'\G

语法4：查看视图的详细定义信息

SHOW CREATE VIEW 视图名称;

8.3 更新视图

MySQL支持使用INSERT、UPDATE和DELETE语句对视图中的数据进行插入、更新和删除操作。当视图中的数据发生变化时，数据表中的数据也会发生变化，反之亦然。

不可更新的视图

注意： 虽然可以更新视图数据，但总的来说，视图作为 虚拟表 ，主要用于 方便查询，不建议更新视图的数据。对视图数据的更改，都是通过对实际数据表里数据的操作来完成的。

8.4 修改、删改视图

修改视图

方式1：使用CREATE OR REPLACE VIEW 子句修改视图

CREATE OR REPLACE VIEW empvu80
(id_number, name, sal, department_id)
AS
SELECT employee_id, first_name || ' ' || last_name, salary, department_id
FROM employees
WHERE department_id = 80;

方式2：ALTER VIEW

ALTER VIEW 视图名称
AS
查询语句

删除视图

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

DROP VIEW IF EXISTS 视图名称;

总结

1. 操作简单
2. 减少数据冗余
3. 数据安全
4. 适应灵活多变的需求
5. 能够分解复杂的查询逻辑

9. 存储过程与函数

9.1 存储过程

存储过程的参数类型可以是IN、OUT和INOUT。根据这点分类如下：

1. 没有参数（无参数无返回）
2. 仅仅带 IN 类型（有参数无返回）
3. 仅仅带 OUT 类型（无参数有返回）
4. 既带 IN 又带 OUT（有参数有返回）
5. 带 INOUT（有参数有返回）

注意： IN、OUT、INOUT 都可以在一个存储过程中带多个。

创建存储过程

DELIMITER 新的结束标记
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...) 
[characteristics ...] #创建存储过程时指定的对存储过程的约束条件
BEGIN
存储过程体(每个语句都以;号为结束符)
END
DELIMITER 新的结束标记

########更加通俗点##########
#修改新的结束标记
DELIMITER $
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
sql语句1;
sql语句2;
END $
#改回原来的
DELIMITER ;

创建存储过程show_someone_salary2()，查看“emps”表的某个员工的薪资，并用IN参数empname输入员工姓名，用OUT参数empsalary输出员工薪资。

DELIMITER //
CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20),OUT empsalary DOUBLE)
BEGIN
SELECT salary INTO empsalary FROM emps WHERE ename = empname;
END // 
DELIMITER ;

调用存储过程

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

CALL 存储过程名(实参列表)

格式:

1. 调用in模式的参数：

CALL sp1('值');

2. 调用out模式的参数：

#定义
SET @name;
#调用
CALL sp1(@name);
#输出
SELECT @name;

3. 调用inout模式的参数：

SET @name=值; 
CALL sp1(@name);
SELECT @name;

9.2 存储函数

MySQL支持自定义函数，定义好之后，调用方式与调用MySQL预定义的系统函数一样。

创建存储函数

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

调用存储函数

在MySQL中，存储函数的使用方法与MySQL内部函数的使用方法是一样的。换言之，用户自己定义的存储函数与MySQL内部函数是一个性质的。区别在于:存储函数是 用户自己定义 的，而内部函数是MySQL 的 开发者定义 的。

SELECT 函数名(实参列表)

注意： 存储函数可以放在查询语句中使用，存储过程不行

创建存储函数count_by_id()，参数传入dept_id，该函数查询dept_id部门的员工人数，并返回，数据类型为整型。

DELIMITER //

CREATE FUNCTION count_by_id(dept_id INT) 
RETURNS INT 
LANGUAGE SQL
NOT DETERMINISTIC 
READS SQL DATA 
SQL SECURITY DEFINER 
COMMENT '查询部门平均工资' 
BEGIN
RETURN (SELECT COUNT(*) FROM employees WHERE department_id = dept_id); END //

DELIMITER ;

SET @dept_id = 50;
SELECT count_by_id(@dept_id);

注意: 若在创建存储函数中报错 you might want to use the less safe log_bin_trust_function_creators variable ，有两种处理方法：

方式1:
加上必要的函数特性[NOT] DETERMINISTIC和{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}
方式2:

SET GLOBAL log_bin_trust_function_creators = 1;

9.3 存储过程和函数的查看、修改与删除

查看

#使用SHOW CREATE语句查看存储过程和函数的创建信息
SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名

#使用SHOW STATUS语句查看存储过程和函数的状态信息
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

修改

修改存储过程或函数，不影响存储过程或函数功能，只是修改相关特性。

ALTER {PROCEDURE | FUNCTION} 存储过程或函数的名

删除

DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程或函数的名

10. 变量

在MySQL数据库的存储过程和函数中，可以使用变量来存储查询或计算的中间结果数据，或者输出最终的结果数据。

在 MySQL 数据库中，变量分为 系统变量 以及 用户自定义变量 。

10.1 系统变量

系统变量分为全局系统变量（需要添加global关键字）以及会话系统变量（需要添加session关键字），不写默认为会话系统变量。

查看系统变量

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

#查看所有全局变量
SHOW GLOBAL VARIABLES;
#查看所有会话变量
SHOW SESSION VARIABLES;
或
SHOW VARIABLES;

#查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%标识符%'; 
#查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%标识符%';

• 查看指定系统变量

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

#查看指定的系统变量的值 
SELECT  @@global.变量名;
#查看指定的会话变量的值 
SELECT @@session.变量名;
#或者
SELECT @@变量名;

修改系统变量

方式1：修改MySQL 配置文件 ，继而修改MySQL系统变量的值（该方法需要重启MySQL服务）

方式2：在MySQL服务运行期间，使用“set”命令重新设置系统变量的值

#为某个会话变量赋值
#方式1:
SET @@global.变量名=变量值;
#方式2: 
SET GLOBAL 变量名=变量值; 
#为某个会话变量赋值 
#方式1： 
SET @@session.变量名=变量值;
#方式2： 
SET SESSION 变量名=变量值;

10.2 用户变量

用户变量是用户自己定义的，作为 MySQL 编码规范，MySQL 中的用户变量以 一个@ 开头。根据作用范围不同，又分为 会话用户变量 和 局部变量 。

会话用户变量：作用域和会话变量一样，只对 当前连接 会话有效。

局部变量：只在 BEGIN 和 END 语句块中有效。局部变量只能在 存储过程和函数 中使用。

会话用户变量

1. 定义变量

#方式1：“=”或“:=” 
SET @用户变量 = 值;
SET @用户变量 := 值;

#方式2：“:=” 或 INTO关键字
SELECT @用户变量 := 表达式 [FROM 等子句];
SELECT 表达式 INTO @用户变量 [FROM 等子句];

2. 查看用户变量的值

SELECT @用户变量

局部变量

定义：可以使用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. 定义变量

DECLARE 变量名 类型 [default 值]; # 如果没有DEFAULT子句，初始值为NULL

2. 变量赋值

#简单的赋值
SET 变量名=值;
SET 变量名:=值;

#赋值于表中的字段值
SELECT 字段名或表达式 INTO 变量名 FROM 表;

3. 使用变量

SELECT 局部变量名;

11. 流程控制

11.1 分支结构

分支结构IF

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

举例：：声明存储过程“update_salary_by_eid2”，定义IN参数emp_id，输入员工编号。判断该员工薪资如果低于9000元并且入职时间超过5年，就涨薪500元；否则就涨薪100元。

DELIMITER // 

CREATE PROCEDURE update_salary_by_eid2(IN emp_id INT) 
BEGIN

#声明变量
DECLARE emp_salary DOUBLE;
DECLARE hire_year DOUBLE;
#变量赋值
SELECT salary INTO emp_salary FROM employees WHERE employee_id = emp_id; 
SELECT DATEDIFF(CURDATE(),hire_date)/365 INTO hire_year FROM employees WHERE employee_id = emp_id;
#条件判断 
IF emp_salary < 8000 AND hire_year > 5 THEN UPDATE employees SET salary = salary + 500 WHERE employee_id = emp_id;
ELSE UPDATE employees SET salary = salary + 100 WHERE employee_id = emp_id; 
END IF;

END //

DELIMITER ; 

分支结构 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流程控制语句的第1种格式，判断val值等于1、等于2，或者两者都不等。

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流程控制语句的第2种格式，判断val是否为空、小于0、大于0或者等于0。

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;

11.2 循环结构

循环结构LOOP

LOOP循环语句用来重复执行某些语句。LOOP内的语句一直重复执行直到循环被退出（使用LEAVE子句），跳出循环过程。

#loop_label表示LOOP语句的标注名称，该参数可以省略。
[loop_label:] LOOP 
循环体 
END LOOP [loop_label];

举例：使用LOOP语句进行循环操作，id值小于10时将重复执行循环过程。

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;

循环结构WHILE

WHILE语句创建一个带条件判断的循环过程。WHILE在执行语句执行时，先对指定的表达式进行判断，如果为真，就执行循环内的语句，否则退出循环。

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

举例：当i值小于10时，将重复执行循环过程

DELIMITER // 
CREATE PROCEDURE test_while() 
BEGIN
#初始化i
DECLARE i INT DEFAULT 0; 
WHILE i < 10 DO 
SET i = i + 1; 
END WHILE;
#查询下此时i的值 
SELECT i; 
END // 
DELIMITER ; 
#调用 
CALL test_while();

循环结构REPEAT

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

[repeat_label:]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 ;

11.3 跳转语句

跳转语句LEAVE

LEAVE语句：可以用在循环语句内，或者以 BEGIN 和 END 包裹起来的程序体内，表示跳出循环或者跳出程序体的操作。可以把 LEAVE 理解为JAVA中的 break。

LEAVE 标记名

跳转语句ITERATE

ITERATE语句：只能用在循环语句（LOOP、REPEAT和WHILE语句）内，表示重新开始循环，将执行顺序转到语句段开头处。可以把 ITERATE 理解为 JAVA中continue，意思为“再次循环”。

ITERATE label

12. 游标

虽然我们也可以通过筛选条件 WHERE 和 HAVING，或者是限定返回记录的关键字 LIMIT 返回一条记录，但是，却无法在结果集中像指针一样，向前定位一条记录、向后定位一条记录，或者是 随意定位到某一条记录 ,并对记录的数据进行处理。

MySQL中游标可以在存储过程和函数中使用

12.1 声明游标

在MySQL中，使用DECLARE关键字来声明游标

DECLARE cursor_name CURSOR FOR select_statement;

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

比如:

DECLARE cur_emp CURSOR FOR SELECT employee_id,salary FROM employees;

12.2 打开游标

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

OPEN cursor_name

12.3 使用游标（从游标中获取数据）

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

FETCH cursor_name INTO var_name [, var_name] ...

注意：var_name必须在声明游标之前就定义好

举例：

FETCH cur_emp INTO emp_id, emp_sal ;

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

12.4 关闭游标

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

CLOSE cursor_name

总结:
游标是 MySQL 的一个重要的功能，为 逐条读取 结果集中的数据，提供了完美的解决方案。跟在应用层面实现相同的功能相比，游标可以在存储程序中使用，效率高，程序也更加简洁。
但同时也会带来一些性能问题，比如在使用游标的过程中，会对数据行进行 加锁 ，这样在业务并发量大的时候，不仅会影响业务之间的效率，还会 消耗系统资源 ，造成内存不足，这是因为游标是在内存中进行的处理。

12.5 练习

创建存储过程update_salary()，参数1为 IN 的INT型变量dept_id，表示部门id；参数2为 IN的INT型变量change_sal_count，表示要调整薪资的员工个数。查询指定id部门的员工信息，按照salary升序排列，根
据hire_date的情况，调整前change_sal_count个员工的薪资，详情如下。

DELIMITER // 

CREATE PROCEDURE update_salary(IN dept_id INT,IN change_sal_count INT) BEGIN
#声明变量 
DECLARE int_count INT DEFAULT 0; 
DECLARE salary_rate DOUBLE DEFAULT 0.0; 
DECLARE emp_id INT; 
DECLARE emp_hire_date DATE; 
#声明游标 
DECLARE emp_cursor CURSOR FOR SELECT employee_id,hire_date FROM employees WHERE department_id = dept_id ORDER BY salary ; 
#打开游标 
OPEN emp_cursor; 
WHILE int_count < change_sal_count DO 
#使用游标 
FETCH emp_cursor INTO emp_id,emp_hire_date; 
IF(YEAR(emp_hire_date) < 1995) THEN SET salary_rate = 1.2; ELSEIF(YEAR(emp_hire_date) <= 1998) THEN SET salary_rate = 1.15; ELSEIF(YEAR(emp_hire_date) <= 2001) THEN SET salary_rate = 1.10; 
ELSE SET salary_rate = 1.05; 
END IF; 
#更新工资 
UPDATE employees SET salary = salary * salary_rate WHERE employee_id = emp_id; 
#迭代条件 
SET int_count = int_count + 1; 
END WHILE; 
#关闭游标 
CLOSE emp_cursor; 
END // 

DELIMITER ; 
# 调用 
CALL update_salary(50,2);

13. 触发器

MySQL从 5.0.2 版本开始支持触发器。MySQL的触发器和存储过程一样，都是嵌入到MySQL服务器的一段程序。

触发器是由 事件来触发 某个操作，这些事件包括 INSERT 、 UPDATE 、 DELETE 事件。所谓事件就是指用户的动作或者触发某项行为。如果定义了触发程序，当数据库执行这些语句时候，就相当于事件发生了，就会自动激发触发器执行相应的操作。

当对数据表中的数据执行插入、更新和删除操作，需要自动执行一些数据库逻辑时，可以使用触发器来实现。

13.1 触发器的创建

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

说明:

• 表名 ：表示触发器监控的对象。
• BEFORE|AFTER ：表示触发的时间。
• BEFORE 表示在事件之前触发；
• AFTER 表示在事件之后触发。
• INSERT|UPDATE|DELETE ：表示触发的事件。
• 用户可以在触发器执行的语句块中使用NEW和OLD引用UPDATE/INSERT事件之后的新值和UPDATE/DELETE事件之前的旧值。

例子：
定义触发器“salary_check_trigger”，基于员工表“employees”的INSERT事件，在INSERT之前检查将要添加的新员工薪资是否大于他领导的薪资，如果大于领导薪资，则报sqlstate_value为’HY000’的错误，从而使得添加失败。

DELIMITER //
 
CREATE TRIGGER salary_check_trigger 
BEFORE INSERT ON employees 
FOR EACH ROW 
BEGIN
DECLARE mgrsalary DOUBLE;
SELECT salary INTO mgrsalary FROM employees WHERE employee_id = NEW.manager_id; 
IF NEW.salary > mgrsalary 
THEN SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '薪资高于领导薪资错误'; END IF; 
END //
 
DELIMITER ;

13.2 查看、删除触发器

查看触发器

查看触发器是查看数据库中已经存在的触发器的定义、状态和语法信息等。
方式1：查看当前数据库的所有触发器的定义

SHOW TRIGGERS\G

方式2：查看当前数据库中某个触发器的定义

SHOW CREATE TRIGGER 触发器名

方式3：从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息。

SELECT * FROM information_schema.TRIGGERS;

删除触发器

触发器也是数据库对象，删除触发器也用DROP语句

DROP TRIGGER IF EXISTS 触发器名称;