MySQL数据库板书
一、课程介绍
《数据库原理和应用》是一个前导课程，也是一个基础课程。是《应用程序开发》类的课程的基础课程。
二、数据库产品介绍
目前主流的关系数据库有：
Oracle（美国甲骨文公司），大中型应用程序。（Java+Oracle）
SQL Server（美国微软公司），.NET应用程序。（C#+SQL Server）
MySQL（美国甲骨文公司），中小型网站。（Java+MySQL，PHP+MySQL）
三、MySQL基础
软件环境
mysql服务器
查看服务："我的电脑"右键->"管理"->"服务"->列表中找到mysql服务
mysql客户端
图形客户端工具navicat，MySQL Workbench，SQLyog
命令行客户端mysql
格式：mysql -u <用户名> -p<密码> --连接本地的mysql服务器
mysql常用命令
show databases; --查看所有的数据库
use <数据库名>; --切换数据库
show tables; --查看当前数据库下的所有表
desc <表名>; --查看表结构
一、查询（SELECT）
1、基本格式：select <字段列表> from <表名>
说明：
表示所有字段：*
如：select * from product;
如果查部分字段，需要指定，字段间用逗号隔开。
指定字段的别名：AS
如：select id as '编号', name as '名称' from product; -- 也可使用空格替代
去重的关键字：DISTINCT
例如：select distinct position from player;
2、条件子句where
格式：where <条件>
说明：条件是一个逻辑表达式。
（1）比较运算符：>（大于）, <（小于）, >=（大于等于）, <=（小于等于）, !=或<>（不等于），<=>（安全等于）
1）除“<=>”外，其它运算符与NULL的运算结果都是NULL。如：1 =NULL为NULL，NULL=NULL为NULL，1>NULL为NULL
2）只有NULL<=>NULL为1，其它<=>NULL都为0。
（2）逻辑运算符：not（非） and（与） or（或）
（3）范围运算符：<字段> between <值1> and <值2>
（4）in运算符：<字段> in (<值1>,<值2>,...)
（5）like运算符：like '含通配符的字符串'，主要作模糊查询
mysql的通配符：
%表示任意个字符（0~n）
_表示任意１个字符
（6）is null运算符：
<字段> is [not] null
说明：null is null结果为1,其它 is null均为0。
3、排序子句
格式：order by <字段> [desc | asc]
说明： desc表示降序（值由大到小），asc表示升序（值由小到大），默认为升序。
４、limit子句
格式：limit([位置偏移量]，<记录个数>）
说明：如果省略位置偏移量，则表示从第1条开始取，第1条记录的位置偏移量为0，第n条记录的位置偏移量为n-1.
如：limit 10 --从开始取前10条。
如：limit 20,10 --则表示从第20+1条开始取10条，即（21~30）。
5、分组子句
格式：group by <字段> [having <条件>]
说明：having <条件>表示限定分组，即对分组后的结果进行筛选。
聚合函数（统计函数）：
count(<字段>)：记录个数
sum(<数值字段>)：求和
avg(<数值字段>)：求平均值
max(<数值字段>)：求最大值
min(<数值字段>)：求最小值
6、多表的连接查询
（1）简单的连接查询（内连接）
格式：select <字段列表> from <表1>,<表2> where <表1>.<连接字段>=<表2>.<连接字段>
功能：从多张表查询数据
（2）join连接
a、内连接(inner join)
格式：select <字段列表> from <表1> t1 inner join <表2> t2 on <表1>.<连接字段>=<表2>.<连接字段>
说明：格式中t1和t2分别表示表1和表2的别名；inner join也简写为join。
b、外连接
左外连接(left join)
格式：select <字段列表> from <表1>left join <表2> on <表1>.<连接字段>=<表2>.<连接字段>
说明：左表中所有的记录都显示，左表中不符合连接条件的记录的对应的右表数据为null。
右外连接(right join)
格式：select <字段列表> from <表1>right join <表2> on <表1>.<连接字段>=<表2>.<连接字段>
说明：右表中所有的记录都显示，右表中不符合连接条件的记录的对应的左表数据为null。
满外连接（full join）
格式：select <字段列表> from <表1>full join <表2> on <表1>.<连接字段>=<表2>.<连接字段>
说明：两表符合连接条件的记录和两表不符合条件的记录，mysql不支持这种连接
c、交叉连接(cross join)
格式：select <字段列表> from <表1>cross join <表2>
说明：交叉连接有时也叫全连接
（3）子查询
概念：子查询就是一个SQL语句中嵌套的一个select语句。
a、比较子查询
格式：select <字段列表> from <表名> where <字段> <比较运算符> <子查询>
说明：
（1）如果子查询的结果是单值，则直接使用“=”、“>”等比较运算符
例：
# 找出比平均工资高的员工的姓名和工资
select last_name,salary from employees where salary > (select avg(salary) from employees)
（2）如果子查询的结果是多值，则需要使用SOME、ANY、ALL等关键字。
ALL：子查询结果所有值
例：90> ALL (70,80,85) 结果为真
例：
# 找出比60部门所有员工工资都高的员工的姓名和工资
select last_name,salary from employees where salary > ALL (select salary from employees where department_id=60)
SOME | ANY：子查询结果其中某一个值
例：75> ANY(70,80,85)结果为真
b、in子查询
格式：select <字段列表> from <表名> where <字段> in <子查询>
说明：该子查询结果应是一个单列多值的结果集。
举例：
# 查找及格的学生的学号和姓名
select distinct s_no,s_name from students st where s_no in (select s_no from score where report>=60)
c、exists子查询
格式：select <字段列表> from <表名> where exists <子查询>
说明：此内外查询需要通过连接字段关联，这种exists子查询也可通过in子查询来实现。
举例：
# 查找及格的学生的学号和姓名
select distinct s_no,s_name from students st where exists (select * from score sc where report>=60 and st.s_no=sc.s_no)
二、数据类型
1、数值型
（1）整数
tinyint，smallint，mediumint，int，bigint。占用字节数分别为1B、2B、3B、4B、8B。
如果是无符号整型，在类型后面加unsigned
（2）浮点数和定点数
a、浮点数
float(M,D) #单精度浮点数，M表示数位宽度，D表示精度（保留小数位数）
double(M,D) #双精度浮点数
b、定点数
decimal(M,D) # 定点数
说明：浮点数型在数据库中存放的是近似值，而定点数类型在数据库中存放的是精确值。
2、字符串
（1）定长字符串
char(n) # n表示字符个数，最多为255
（2）变长字符串
varchar(n) #n表示最大字符数，最多为65535
（3）text类型
tinytext # 最多255个字符
text #最多65535
mediumtext #最多2^24-1个字符
longtext #最多2^32-1个字符。
（4）枚举类型
enum('<值1>','<值2>',...) # 该类型的取值比较固定，只能单选。
举例：sex enum('男','女')
（5）集合类型
set('<值1>','<值2>',...） # 该类型的取值比较固定，可以多选。
举例：fav set('体育','音乐','游戏') # fav字段取值可以是'体育,游戏'
3、日期和时间
（1）year
'YYYY' # '1901'-'2155'
（2）time
'hh:mm:ss'
（3）date
'YYYY-MM-DD' # '1000-01-01' 到 '9999-12-31'
（4）datetime
'YYYY-MM-DD hh:mm:ss' # 年份'1000'-'9999'，不支持时区
（5）timestamp
'YYYY-MM-DD hh:mm:ss' # 年份'1970'-'2038',支持时区
TIMESTAMP类型比较特殊，如果定义一个字段的类型为TIMESTAMP，这个字段的时间会在其他字段修改的时候自动刷新。所以这个数据类型的字段可以存放这条记录最后被修改的时间，而不是真正的存放时间。
4、二进制类型
（1）binary和varbinary
以字节为存储单位，对比char和varchar
（2）blob
tinyblob,smallblob,mediumblob,longblob
255B,64KB,16MB,4GB
以字节为单位，对比text
三、DDL--数据库和表的创建
1、管理数据库
概念：数据库是一个容器，里面有表、视图、存储过程等。
（1）创建数据库
基本格式：create database [if not exists] <数据库名> [character set <字符集>]
说明：[]括起来表示可选项，表示根据需要添加。
简单格式：create database <数据库名>;
（2）查看数据库
查看所有数据库：show databases;
查看创建数据库的脚本：show create database <数据库名>
（3）使用数据库
格式：use <数据库名>;
（4）删除数据库
格式：drop database <数据库名>
2、管理表
概念：表是用来存储数据，表是数据库中最重要的一个对象。
（1）创建表
格式1：create table [if not exists] <表名>(
<字段名> <字段类型>(宽度) ,
...
);
格式2：create table <表名> as <select语句>
说明：根据查询结果创建表
拓展选项：
auto_increment：
auto_increment表示自增，即自动编号，只有整型字段才可以设置，一个表只能有一个自增字段。
示例：
create table product(
id int auto_increment primary key, --auto_increment表示自增，primary key表示主键。
name varchar(50),
price float,
image varchar(255)
);
（2）查看表
查看表结构：
desc <表名>
查看表数据（查询）：
select * from <表名>
查看创建表的脚本：
show create table <表名>
（3）修改表结构
基本格式：alter table <表名> ...
a)增加字段
格式：alter table <表名> add <字段的定义> [first | after 字段名 ]
说明：first和after表示字段添加的位置
b)删除字段
alter table <表名> drop <字段名>
c)修改字段
alter table <表名> modify <字段的定义> [first | after 字段名 ]
d)重命名字段
alter table <表名> change <旧字段名> <新字段的定义>
e)重命名表
alter table <旧表名> rename to <新表名>
（4）重命名表
基本格式：rename table <旧表名> to <新表名>
（5）删除表
基本格式：drop table [if exists]<表名>
四、DML--数据的基本操作：增、删、改
1、增（insert）
（1）指定数据添加记录
格式：insert | replace into <表名>[(<字段列表>)] values(<值列表>)
说明：
replace into ：表示如果有该主键的记录，则进行替换；没有的话，则和insert into相同，插入新的一条。
如果一次要插入多行，则数据行之间用","分隔。
举例：
# 一次添加3名学生记录
insert into student(id,name) values(1,'tom'),(2,'jack'),(3,'rose')
（2）从查询结果中添加记录
格式：insert into <表名>[(<字段列表>)] <select语句>
举例：
# 将student1表中的全部记录复制到student2表，两表结构一样
insert into student2 select * from student1
2、删（delete）
格式：delete from <表名> where <条件>
说明：还可以加上order by 子句和limit子句
举例：delete from score order by maths limit 3; --删除成绩表中数学成绩最低的3行记录。
3、改（update）
格式：update <表名> set <字段1>=<值1>,...where <条件>
说明：可以加上order by 子句和limit子句
五、约束（constraint）
1、非空
not null
只能位于字段的后面，可以在创建表时指定，也可以在表创建后添加。如果不指定非空约束，该字段默认允许为空。
举例：
create table student(id int not null,name varchar(15)); # 创建表时添加非空约束
alter table student modify name varchar(15) not null; # 表创建后添加非空约束
2、默认值
default <值>
举例：
create table student(id int not null,name varchar(15),sex char(1) default '男');
3、主键约束
Primary Key
主键是用来唯一地表示一个实体，为了防止出现重复的记录，确保实体的完整性。
主键值必须唯一，不能为空，一个表只能有一个主键。
例如：学生（学号，姓名，……）
（1）在创建表时候创建主键约束
create table <表名>(
<字段1> <类型1> primary key, --列级约束
<字段2> <类型2> ,
...
)
create table <表名>(
<字段1> <类型1>
<字段2> <类型2> ,
primay key(<字段1>) --表级约束
)
说明：如果使用复合主键时，只能采用表级约束
（2）在表创建后添加主键约束
alter table <表名> add primary key(<字段名>)
（3）删除主键约束
alter table <表名> drop primary key
4、唯一约束
unique
限制某个字段不要出现重复值，unique字段可以为空，一个表可以有多个唯一约束。
（1）在创建表时候创建唯一约束
create table <表名>(
<字段1> <类型1> unique, --列级约束
<字段2> <类型2> ,
...
)
create table <表名>(
<字段1> <类型1>
<字段2> <类型2> ,
unique(<字段1>) --表级约束
)
（2）在表创建后添加唯一约束
alter table <表名> add unique(<字段名>)
5、检查约束
check
设置某个字段取值范围，确保域的完整性。
（1）在创建表时候创建检查约束
create table <表名>(
<字段1> <类型1> check(<条件>), --列级约束
<字段2> <类型2> ,
...
)
（2）在表创建后添加检查约束
alter table <表名> add check(<条件>)
MySQL的存储引擎均能对check子句进行分析，但会忽略check子句，即check子句约束还不起作用，MySQL8.0.15以后支持check约束。
6、外键约束
Foreign Key
外键不是当前表的主键，是关联表的主键。
外键的值一定要取自于父表的主键，以保证引用完整性（参照完整性）。
（1）在创建表的时候定义外键约束
格式：
create table <表名>(
<字段1> <类型1> ,
<字段2> <类型2> ,
foreign key(<外键字段>) references <父表>(<主键字段>) --表级约束
)
说明：外键的定义应放在所有字段定义的后面，不能放在某列的定义里面
举例：
create table class(class_id int primary key,class_name varchar(20)) # 班级表
create table student(student_id int,student_name varchar(15),class_id int,foreign key(class_id) references class(class_id)); # 学生表
（2）在表创建后添加外键约束
alter table <表名> add foreign key(<外键字段>) references <父表>(<主键字段>)
说明：
在外键的定义的最后可加上以下几种选项：
on update cascade --级联更新，即更新了父表的主键值，子表中对应的外键值也会随之更新。
on delete cascade --级联删除，即删除了父表中某主键对应的记录，那么子表中外键值和主键相同的记录也会随之删除。
on update restrict --禁止更新，即如果父表中有和外键值相同的主键值，则禁止修改此外键值
on delete restrict --禁止删除，即如果父表中有和外键值相同的主键值，则禁止删除该记录
六、视图
从用户角度看，视图是从特定的角度查看数据库中的数据的窗口，从数据库系统内部看，它是由SELECT语句查询定义的虚拟表。
1、创建视图
格式：CREATE VIEW <视图名> AS <SELECT语句>
作用1：保护数据安全
隔离或屏蔽数据，对于一些用户无权查看的重要的或隐私的数据，起到了隔离和保护的作用，保证了数据安全。
-- 对60号部门的管理员，只能允许查看本部门的数据， -- 不能查看其它部门的数据，这样起到数据隔离的作用 CREATE VIEW view_employees_60 AS SELECT * FROM employees WHERE department_id=60; SHOW TABLES; -- 查看视图对象 SELECT * FROM view_employees_60; -- 查看视图数据，准确地说通过视图观察表中的数据 -- 员工的email、phone_number、salary、commission_pct这些是隐私的数据，对普通用户来说，应该屏蔽 CREATE VIEW view_employees_common AS SELECT employee_id,first_name,last_name,department_id FROM employees; SELECT * FROM view_employees_common;
作用2：简化查询
对于一些经常使用的复杂的多表连接查询语句，可以使用视图保存这些SQL语句，以后直接查询视图就可以了。
-- 查询每个部门位于的大洲、国家、城市等信息 -- 创建视图 CREATE VIEW view_departments_full AS SELECT department_id,department_name,region_name,country_name,city FROM regions r JOIN countries c ON r.region_id=c.region_id JOIN locations l ON c.country_id=l.country_id JOIN departments d ON l.location_id=d.location_id; SELECT * FROM view_departments_full; -- 查询视图
2、查看视图
SHOW TABLES -- 查看当前数据库下所有表或视图，视图是虚拟表
DESC <视图名> -- 查看视图的结构
SHOW CREATE VIEW <视图名> -- 查看视图的定义
3、修改视图
格式：ALTER VIEW <视图名> AS <SELECT语句>
4、删除视图
格式：DROP VIEW [IF EXISTS] <视图名>
5、使用视图更新数据
（1）若视图依赖于一个基本表，则直接通过更新视图来更新基本表的数据。
（2）若视图依赖于多个基本表，则一次更新（INSERT/UPDATE）只能修改一个基本表的数据，不能同时更新多个基本表的数据。
（3）若视图依赖于多个基本表，则不能使用DELETE语句。
七、索引
1、索引的分类
2、创建索引
2.1 在创建表时创建索引
格式：
CREATE TABLE <表名>(
<列1的定义>,
<列2的定义>,
...
PRIMARY KEY(<字段>), --主键索引
UNIQUE INDEX <索引名>(<字段>), -- 唯一索引
INDEX <索引名>(<字段>) -- 普通索引
）
2.2 在已存在的表上创建索引
（2）使用CREATE INDEX创建索引
格式：CREATE [UNIQUE] INDEX <索引名> ON <表>(<字段>)
说明：使用UNIQUE创建的是唯一索引，不使用则是创建普通索引，该命令无法创建主键索引
3、查看索引
格式：SHOW INDEX FROM <表名>
4、分析索引
- system：系统表，少量数据，往往不需要进行磁盘 IO
- const：常量连接，通常是主键等值扫描
- eq_ref：主键索引 (primary key) 或者非空唯一索引 (unique not null) 等值扫描
- ref：非主键非唯一索引等值扫描，即普通索引等值扫描
- range：范围扫描
- index：索引树扫描
- ALL：全表扫描 (full table scan)
type 类型从快到慢：system>const>eq_ref>ref>range>index>ALL
5、删除索引
八、存储过程和存储函数
（一）、存储过程
存储过程是将能完成特定功能的SQL语句集，经过编译后存储在数据库中，用户可以通过存储过程的名字来执行它，这样的语句集就称为存储过程。
1、创建存储过程
格式：
CREATE PROCEDURE <存储过程名>(IN|OUT|INOUT <参数名> <参数类型>,...)
BEGIN
存储过程体
END
说明：
IN：表示该参数为输入参数，简称入参，IN也可省略。
OUT：表示该参数为输出参数，简称出参，客户端调用完后，就可以读该参数的返回值。
INOUT：表示既可以为入参，也可以是出参。
局部变量的声明：
格式：DECLARE <变量名> <类型> [DEFAULT <初值>]
举例：DECLARE n INT DEFAULT 0; -- 声明整型变量n,初值为0
变量的赋值：
格式：SET <变量名>=<值>
举例：SET n=n+1;
2、查看存储过程
（1）查看创建信息
SHOW CREATE PROCEDURE <存储过程名>
（2）查看状态信息
SHOW PROCEDURE STATUS [LIKE '通配符字符串']
3、调用存储过程
格式：CALL <存储过程名>(<参数>,...)
举例：
（1）IN模式参数：CALL sp_get_salary(102);
（2）OUT模式参数：
SET @max_salary=0; -- 声明会话变量
CALL sp_max_salary(@max_salary); -- 传入会话变量
SELECT @max_salary; -- 输出调用后的会话变量
（3）INOUT模式参数：
SET @ename='Chen'; -- 声明会话变量，并指定要传入的值
CALL sp_manager(@ename); -- 传入会话变量
SELECT @ename; -- 输出调用后的会话变量
4、删除存储过程
DROP PROCEDURE <存储过程名>
（二）、存储函数
存储函数和存储过程类似，都是对预先编译的SQL语句的封装，不同点是函数必须有返回值。
1、定义存储函数
格式：
2、查看存储函数
3、调用存储函数
4、删除存储函数
九、变量与流程控制
1、变量
1.1 系统变量
系统变量是由系统定义的，以'@@'开头，可分为全局系统变量和会话系统变量。
全局系统变量针对所有的会话（连接）有效，会话系统变量仅针对当前会话有效。
（1）查看系统变量
a）查看所有或部分变量
格式：SHOW [GLOBAL | SESSION] VARIABLES [LIKE '通配符字符串'];
举例：SHOW GLOBAL VARIABLES LIKE 'char%'
说明：GLOBAL表示全局系统变量，SESSION表示会话系统变量，也可省略不写。
b）查看某一个变量
格式：SELECT @@[GLOBAL | SESSION] .<变量名>
举例：SELECT @@GLOBAL.max_connections
说明：如果是会话系统变量，SESSION可省略。
（2）修改系统变量
格式1：SET @@[GLOBAL | SESSION] .<变量名>=<值>;
举例：SET @@GLOBAL.auto_commit=0;
格式2：SET [GLOBAL | SESSION] <变量名>=<值>;
1.2 用户变量
用户变量是用户自己定义的，可分为会话用户变量和局部变量。
会话用户变量也是针对当前连接会话有效；局部变量只在BEGIN...END语句块中有效，即通常在存储过程或存储函数中用。
（1）用户变量的定义
a）会话用户变量的定义
格式：SET @<用户变量>=<值>
举例：SET @n=0;
说明：会话变量直接使用，无需声明，变量名前一定要有'@'。
b）局部变量的定义
格式：DECLARE <变量名> <类型> [DEFAULT <初始值>];
举例：DECLARE i INT DEFAULT 0;
说明：若省略[DEFAULT <初始值>]，则初始值为NULL。
（2）用户变量的赋值
方式1：SET [@]<用户变量>=<值>;
方式2：SELECT <字段或表达式> INTO [@]<用户变量> FROM <表>;
说明：会话用户变量和局部变量赋值时一样，唯一不同的是会话用户变量名前要加'@'。
（3）用户变量的查看
格式：SELECT [@]<用户变量>
2、流程控制
2.1 分支结构
（1）IF分支
格式：
IF <条件1> THEN 语句1;
[ELSEIF <条件2> THEN 语句2;]
……
[ELSE <语句n>]
END IF
-- 编程实现判断一个数是正数、负数还是0。 CREATE PROCEDURE test_if(num FLOAT) BEGIN IF num>0 THEN SELECT '正数'; ELSEIF num=0 THEN SELECT '0'; ELSE SELECT '负数'; END IF; END CALL test_if(3.25);
（2）CASE分支
格式1：
CASE <表达式>
WHEN <值1> THEN <结果1或语句1>
WHEN <值2> THEN <结果2或语句2>
...
ELSE <结果n或语句n>
END [CASE]
说明：
类似于Java或C中的switch语句。
如果THEN后面是结果，则不要加分号，如果是语句则需要加分号。
如果整个CASE分支是放在BEGIN...END中，则分支结尾需要加上CASE，如果放在SELECT后面不需要。
-- 输入四季英文单词，输出相应的中文。 CREATE PROCEDURE test_case1(season VARCHAR(10)) BEGIN CASE season WHEN 'spring' THEN SELECT '春'; WHEN 'summer' THEN SELECT '夏'; WHEN 'autumn' THEN SELECT '秋'; WHEN 'winter' THEN SELECT '冬'; ELSE SELECT '错'; END CASE; END CALL test_case1('summer');
格式2：
CASE
WHEN <条件1> THEN <结果1或语句1>
WHEN <条件2> THEN <结果2或语句2>
...
ELSE <结果n或语句n>
END [CASE]
-- 编程实现判断一个数是正数、负数还是0。 CREATE PROCEDURE test_case2(num FLOAT) BEGIN CASE WHEN num>0 THEN SELECT '正数'; WHEN num=0 THEN SELECT '0'; ELSE SELECT '负数'; END CASE; END CALL test_case2(-32.51);
2.2 循环结构
（1）LOOP循环
格式：
[loop_label:]LOOP
...
IF <条件> THEN LEAVE loop_label;
END IF;
END LOOP [loop_label];
说明：
loop_label是循环标签，方便使用LEAVE命令退出该循环。
<条件>是退出循环的条件。
-- 使用loop循环实现1+2+...+100。 CREATE PROCEDURE test_loop() BEGIN DECLARE i INT DEFAULT 1; DECLARE s INT DEFAULT 0; loop1:LOOP SET s=s+i; SET i=i+1; IF i>100 THEN LEAVE loop1; END IF; END LOOP; SELECT s; END CALL test_loop();
（2）WHILE循环
格式：
[while_label:]WHILE <条件> DO
<循环体>
END WHILE [while_label];
-- 使用while循环实现1+2+...+100。 CREATE PROCEDURE test_while() BEGIN DECLARE i INT DEFAULT 1; DECLARE s INT DEFAULT 0; WHILE i<=100 DO SET s=s+i; SET i=i+1; END WHILE; SELECT s; END CALL test_while();
（3）REPEAT循环
格式：
[repeat_label:]REPEAT
<循环体>
UNTIL <循环结束的条件>
END REPEAT [repeat_label];
-- 使用repeat循环实现1+2+...+100。 CREATE PROCEDURE test_repeat() BEGIN DECLARE i INT DEFAULT 1; DECLARE s INT DEFAULT 0; REPEAT SET s=s+i; SET i=i+1; UNTIL i>100 -- 此处不能加分号 END REPEAT; SELECT s; END CALL test_repeat();
十、触发器
触发器是一种特殊的存储过程，当对数据进行INSERT、UPDATE和DELETE事件时，数据库系统就会自动执行触发器中定义的程序语句，以进行维护数据完整性或其他一些特殊的任务。
1、创建触发器
格式：
CREATE TRIGGER <触发器名>
BEFORE | AFTER INSERT | DELETE | UPDATE
ON <表名> FOR EACH ROW
BEGIN
-- 执行程序的代码
END
举例：
-- 创建一个触发器，当更改表COURSE中某门课的课程号时，同时将SCORE表课程号全部更新。
CREATE TRIGGER CNO_UPDATE
AFTER UPDATE
ON COURSE FOR EACH ROW
BEGIN
UPDATE SCORE SET c_no=NEW.c_no WHERE c_no=OLD.c_no;
END
说明：
NEW.<列名>：表示新行的一列
OLD.<列名>：表示旧行的一列，即更新或删除之前的已有行的列。
测试：
-- 将course表的c_no由'A001'改为'A011'，观察score表的课程号是否自动更新 ？
UPDATE course SET c_no='A011' WHERE c_no='A001';
SELECT * FROM score;
2、查看触发器
（1）SHOW TRIGGERS
（2）SELECT * FROM information_schema.triggers
3、删除触发器
格式：DROP TRIGGER <触发器名>
十一、用户和权限
1、创建用户
1.1 使用CREATE USER命令创建用户
格式：CREATE USER <用户名> [IDENTIFIED BY '<密码>']
举例：CREATE USER 'mike'@'localhost' IDENTIFIED BY '123456'
说明：
1）<用户名>的格式应该为：'用户名'@'主机名'，如果是本地主机，主机名为localhost；如果是非本地的其它任意主机，主机名为'%'。
2）刚创建的用户是没有任何权限。
3）密码会自动加密存储。
1.2 向user表添加记录创建用户
格式：INSERT INTO user(host,user,authentication_string) VALUES('<主机名>','<用户名>',password('<密码>'));
举例：INSERT INTO user(host,user,authentication_string) VALUES('localhost','david',password('123456'));
说明：
1）这种方式密码不会自动加密，需要使用password()函数手动加密。
2）这种方式创建的用户，权限不会立即生效，需要使用'FLUSH PRIVILEGES;'命令刷新权限。
3）如果执行INSERT命令失败，提示ssl_cipher,x509_issuer,x509_subject这几个字段不能为空，可将这几个字段都赋值为0。
2、查看用户
2.1 查看全局权限表user
格式：SELECT * FROM mysql.user
说明：mysql5.7后，密码字段由password变为authentication_string
2.2、用户登录（命令行）
mysql -u <用户名> -p<密码> -- 本地登录
mysql -h <主机名或IP> -u <用户名> -p<密码> -- 其它主机登录
3、重命名用户
格式：RENAME USER <旧用户名> TO <新用户名>
举例：RENAME USER 'mike'@'localhost' TO 'michael'@'localhost'
4、修改密码
格式：SET PASSWORD [FOR <用户名>] =password('<新密码>')
举例：SET PASSWORD FOR 'king'@'localhost'=password('queen');
说明：如果省略[FOR <用户名>] 则表示给当前用户修改密码
5、删除用户
5.1 使用DROP USER删除
格式：DROP USER <用户名>
举例：DROP USER mike@localhost
5.2 使用DELETE命令
举例：DELETE FROM user WHERE host='localhost' AND user='mike';
说明：删除记录后，使用'FLUSH PRIVILEGES;'刷新系统权限表
6、权限
mysql权限信息存储在'mysql'数据库的授权表中。其中，user表是全局权限表；db表是数据库权限表；tables_priv表是表层级权限表；columns_priv表是列层级权限表。
6.1 授权
6.1.1 GRANT授权
格式：GRANT <权限1>,<权限2>,.. ON <数据库>.<表> TO <用户名> [IDENTIFIED BY '<密码>']
举例：GRANT SELECT,UPDATE ON jxgl.students TO mike@localhost
说明：
1）若<用户名>指定的用户不存在，使用[IDENTIFIED BY '<密码>']时，则可创建该用户并赋予权限。
2）也可指定具体到某些列权限，如：GRANT UPDATE(s_no,s_name) ON jxgl.students TO peter@localhost
3）如果授予所有权限，则可使用ALL，如：GRANT ALL ON jxgl.* TO peter@localhost -- *表示所有表
4）如果针对所有数据库的所有表（*.*）设置权限，则是全局权限，则权限信息在user表中。
5）如果针对某一数据库的所有表（如：jxgl.*）设置权限，则是库级权限，则权限信息在db表中。
6）如果针对某一数据库的某一表（如：jxgl.students）设置权限，则是表级权限，则权限信息在tables_priv表中。
7）如果针对某一数据库的某一表的某些列（如：GRANT update(s_no,s_name) on jxgl.students）设置权限，则是列级权限，则权限信息在columns_priv表中。
6.1.2 UPDATE或INSERT更改权限表
举例：授予mike对所有表的SELECT和UPDATE权限
UPDATE user SET Select_priv='Y',Upade_prive='Y' WHERE host='localhost' AND user='mike'；
FLUSH PRIVILEGES; -- 刷新权限
6.2 收回权限
格式：REVOKE <权限1>,<权限2>,.. ON <数据库>.<表> FROM <用户名>
举例：REVOKE UPDATE ON jxgl.students FROM mike@localhost
十二、事件
定时任务机制，即在指定的时间单元内完成特定的任务。
查看事件调度器是否开启：@@global.event_scheduler
1、创建事件
格式：
CREATE EVENT <事件名>
ON SCHEDULE <时间调度>
DO
BEGIN
语句;
END
说明：
<时间调度>表示事件何时发生或者每隔多久发生一次。
1）AT <时刻> [+ INTERVAL <n> <时间单位>]
例：AT CURRENT_TIMESTAMP()+INTERVAL 5 MINUTE
2) EVERY <n> <时间单位> [STARTS <起始时间>] [ENDS <终止时间>]
例：EVERY 1 DAY
2、查看事件
格式：
SHOW EVENTS --查看所有事件
SHOW CREATE EVENT <事件名> --查看事件的创建信息
3、修改事件
格式：
ALTER EVENT <事件名>
[ON SCHEDULE <时间调度>]
[RENAME TO <新事件名>]
[ENABLE | DISABLE]
举例：
ALTER EVENT e_test DISABLE; -- 临时关闭事件
4、删除事件
格式：DROP EVENT <事件名>
