1 视图（简化查询操作）
1-1 概述
视图的特点:
- 视图是SELECT语句执行后返回的结果集，是对SELECT语句的封装。
- 视图是虚拟存在的表(通过查询得到的表)，表中的行和列来自于真实存在的表。
视图相比普通表的优势
- 简单：使用视图的人只需要关注视图，无需关注视图背后真实存在的表的结构，关联条件，筛选条件。
- 安全：使用视图可以对访问用户(包括开发者)的查看权限进行限制，视图对表的权限限制能够具体到某行某列。
- 数据独立：视图与数据来源的表之间具有独立性，体现在：
- 数据来源的表可以*增添删减记录
- 数据来源的表的结构变化，只需更新视图定义所封装的SQL语句进行。
视图使用注意点
视图通常用于简化查询操作，可以通过更新视图来更新视图数据来源的表，但一般不推荐这样做。
1-2 视图的创建与修改
准备表
create database demo_01 default charset=utf8mb4;
use demo_01;
CREATE TABLE `city` (
`city_id` int(11) NOT NULL AUTO_INCREMENT,
`city_name` varchar(50) NOT NULL,
`country_id` int(11) NOT NULL,
PRIMARY KEY (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `country` (
`country_id` int(11) NOT NULL AUTO_INCREMENT,
`country_name` varchar(100) NOT NULL,
PRIMARY KEY (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `city` (`city_id`, `city_name`, `country_id`) values(1,'西安',1);
insert into `city` (`city_id`, `city_name`, `country_id`) values(2,'NewYork',2);
insert into `city` (`city_id`, `city_name`, `country_id`) values(3,'北京',1);
insert into `city` (`city_id`, `city_name`, `country_id`) values(4,'上海',1);
insert into `country` (`country_id`, `country_name`) values(1,'China');
insert into `country` (`country_id`, `country_name`) values(2,'America');
insert into `country` (`country_id`, `country_name`) values(3,'Japan');
insert into `country` (`country_id`, `country_name`) values(4,'UK');
- 注意city表中country_id是外键(外面表的主键)
进行多表查询：查询城市信息以及其关联的国家信息。
SELECT c.*,t.country_name from city c, country t WHERE c.country_id = t.country_id;
视图的创建
目标：将上面的SELECT语句封装成视图。
CREATE view view_city_country as SELECT c.*,t.country_name from city c, country t WHERE c.country_id = t.country_id;
总结：上图中创建一个虚拟表view_city_country，封装之间定义的查询语句。
命令格式：CREATE VIEW <视图名> AS <SELECT语句>
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
- [WITH [CASCADED | LOCAL] CHECK OPTION]含义
更新视图时对更新条件的限制
LOCAL：只要满足本视图的条件就可以更新。
CASCADED：必须满足所有针对该视图的所有视图的条件才可以更新。 默认值.
视图的更改
命令格式：ALTER VIEW <视图名> AS <SELECT语句>
ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
1-3 视图的查看与删除
查看
从 MySQL 5.1 版本开始，使用 SHOW TABLES 命令的时候不仅显示表的名字，同时也会显示视图的名字，而不存
在单独显示视图的 SHOW VIEWS 命令。
show tables;
SHOW CREATE VIEW 视图名 \G; # 查看视图的具体定义
删除（DROP VIEW 视图名）
DROP VIEW [IF EXISTS] view_name [, view_name] ...[RESTRICT | CASCADE]
2 存储过程与存储函数
2-1 概述
存储过程与存储函数定义：
- 事先经过编译并存储在数据库中的一段 SQL 语句的集合
存储过程与存储函数优势：
- 简化应用开发人员的很多工作
- 减少数据在数据库和应用服务器之间的传输，提高数据处理的效率
存储过程与存储函数的区分：
- 函数可以理解为带有返回值的存储过程
2-2 创建，调用，查询存储过程
创建
CREATE PROCEDURE procedure_name ([proc_parameter[,...]])
begin
-- SQL语句
end ;
实例
delimiter $
create procedure pro_test1()
begin
select 'hello mysql' ;
end$
delimiter ;
注意：由于MYSQL中 delimiter；+enter 就表示让数据库去执行命令，为了能够执行多条SQL语句，需要临时将 delimiter由；换为其他字符。
调用
call procedure_name() ;
查看创建的存储
-- 查询db_name数据库中的所有的存储过程
select name from mysql.proc where db='db_name';
-- 查询存储过程的状态信息
show procedure status;
-- 查询当前数据库中某个存储过程的定义
show create procedure 数据库名称.存储过程名称 \G;
注意：实际上创建的存储过程也是以表的形式存储在数据库中，在MYSQL中存储在mysql数据库中的proc表中。
注意：上面的命令可以查询到数据库创建的所有存储过程。
注意：只要知道存储过程的名称以及所属数据库，就可以通过show命令查看该存储过程所对应的SQL语句集合。
更加快捷的方法：通过GUI界面直接查看mysql.proc表，可以看到上面所有的信息。
删除
DROP PROCEDURE [IF EXISTS] 存储过程名称；
2-2 存储过程对应的SQL语句语法
2-2-1 变量的赋值（2种方式）
变量定义：可以采用DECLARE声明局部变量，该变量的作用范围只能在 BEGIN…END 块中 。
- 手动的定义常量（SET）
DELIMITER $
CREATE PROCEDURE pro_test3()
BEGIN
DECLARE NAME VARCHAR(20);
SET NAME = 'MYSQL';
SELECT NAME ;
END$
DELIMITER
- 从数据表中获取变量数值（select ... into ）
DELIMITER $
CREATE PROCEDURE pro_test5()
BEGIN
declare countnum int;
select count(*) into countnum from city;
select countnum;
END$
DELIMITER ;
COUNT(*) 函数返回表中的记录数
总结: 通过into能够将数据表中统计的结果写入变量
2-2-2 if语句的使用
需求
根据定义的身高变量，判定当前身高的所属的身材类型
180 及以上 ----------> 身材高挑
170 - 180 ---------> 标准身材
170 以下 ----------> 一般身材
存储过程创建
delimiter $
create procedure pro_test6()
begin
declare height int default 175;
declare description varchar(50);
if height >= 180 then
set description = 'good body';
elseif height >= 170 and height < 180 then
set description = 'standard body';
else
set description = 'common body';
end if;
select description ;
end$
delimiter ;
总结：默认身高是175，根据if语句判断为standard body.
2-2-3 存储过程 参数的传递
模板
create procedure procedure_name([in/out/inout] 参数名 参数类型)
...
IN : 该参数可以作为输入，也就是需要调用方传入值 , 默认
OUT: 该参数作为输出，也就是该参数可以作为返回值
INOUT: 既可以作为输入参数，也可以作为输出参数
需求
编写一个存储过程，将身高作为参数传递，将判定的结果通过参数返回
代码
delimiter $
create procedure pro_test7(IN height INT,OUT description VARCHAR(100))
begin
if height >= 180 then
set description = 'good body';
elseif height >= 170 and height < 180 then
set description = 'standard body';
else
set description = 'common body';
end if;
end$
delimiter ;
查看返回的变量
- @description : 变量名称前面加上“@”符号，叫做用户会话变量，代表整个会话过程他都是有作用
的，这个类似于全局变量一样。 - @@global.sort_buffer_size : 变量前加上 "@@" 符号, 叫做系统变量
call pro_test7(168, @description);
select @description;
2-2-4 CASE语句的使用
模板
方式一 :
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE;
方式二 :
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE;
实例：
delimiter $
create procedure pro_test9(month int)
begin
declare result varchar(20);
case
when month >= 1 and month <=3 then
set result = '第一季度';
when month >= 4 and month <=6 then
set result = '第二季度';
when month >= 7 and month <=9 then
set result = '第三季度';
when month >= 10 and month <=12 then
set result = '第四季度';
end case;
select concat('您输入的月份为 :', month , ' , 该月份为 : ' , result) as content;
end$
delimiter ;
执行结果：
2-2-5 WHILE语句的使用
模板
while search_condition do
statement_list
end while;
实例(计算1加到n的值)
delimiter $
create procedure pro_test8(n int)
begin
declare total int default 0;
declare num int default 1;
while num<=n do
set total = total + num;
set num = num + 1;
end while;
select total;
end$
delimiter ;
执行结果
2-2-6 REPEAT的使用
模板
REPEAT
statement_list
UNTIL search_condition
END REPEAT;
实例：计算1加到n
delimiter $
create procedure pro_test10(n int)
begin
declare total int default 0;
repeat
set total = total + n;
set n = n - 1;
until n=0
end repeat;
select total ;
end$
delimiter ;
2-2-7 LOOP与LEAVE的使用
- LOOP可以实现简单的死循环，如果要退出循环需要配合leave使用
[begin_label:] LOOP
statement_list
END LOOP [end_label]
实例：
delimiter $
CREATE PROCEDURE pro_test11(n int)
BEGIN
declare total int default 0;
ins: LOOP
IF n <= 0 then
leave ins;
END IF;
set total = total + n;
set n = n - 1;
END LOOP ins;
select total;
END$
delimiter ;
注意：上面的ins是循环的开始和结果标记。
2-2-8 游标/光标的使用
定义：
- 用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用光标对结果集进行循环的处理
- 每次提取一条记录
光标的声明、OPEN、FETCH（拿） 和 CLOSE，其语法分别如下：
DECLARE cursor_name CURSOR FOR select_statement ;
OPEN cursor_name ;
FETCH cursor_name INTO var_name [, var_name] ...
CLOSE cursor_name ;
光标的使用示例：
数据表创建
create table emp(
id int(11) not null auto_increment ,
name varchar(50) not null comment '姓名',
age int(11) comment '年龄',
salary int(11) comment '薪水',
primary key(`id`)
)engine=innodb default charset=utf8 ;
insert into emp(id,name,age,salary) values(null,'金毛狮王',55,3800),(null,'白眉鹰
王',60,4000),(null,'青翼蝠王',38,2800),(null,'紫衫龙王',42,1800);
需求1：查询emp表中数据, 并逐行获取进行展示
delimiter $
create procedure pro_test15()
begin
declare e_id int(11);
declare e_name varchar(50);
declare e_age int(11);
declare e_salary int(11);
declare emp_result cursor for select * from emp;
open emp_result;
fetch emp_result into e_id,e_name,e_age,e_salary;
select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为:
',e_salary);
fetch emp_result into e_id,e_name,e_age,e_salary;
select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为:
',e_salary);
fetch emp_result into e_id,e_name,e_age,e_salary;
select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为:
',e_salary);
fetch emp_result into e_id,e_name,e_age,e_salary;
select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为:
',e_salary);
fetch emp_result into e_id,e_name,e_age,e_salary;
select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为:
',e_salary);
close emp_result;
end$
delimiter ;
执行结果：
总结：
declare emp_result cursor for select * from emp;
- 将SQL语句select查询的结果封装到定义的游标中
open emp_result;
close emp_result;
- 游标的使用需要open与closs
fetch emp_result into e_id,e_name,e_age,e_salary;
- 使用fetch将游标中封装的结果的一条记录提取出来，然后放入到定义的变量中
- 上面的例子中如果fetch的次数 > 数据表中记录的个数，会发生error提示No data
需求2：使用循环结构通过游标查看表中所有记录。
- 下面的例子通过句柄判断记录是否读完
- 也可以通过count()确定表中的记录数
DELIMITER $
create procedure pro_test15()
begin
DECLARE id int(11);
DECLARE name varchar(50);
DECLARE age int(11);
DECLARE salary int(11);
DECLARE has_data int default 1;
DECLARE emp_result CURSOR FOR select * from emp;
DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;
open emp_result;
repeat
fetch emp_result into id , name , age , salary;
select concat('id为',id, ', name 为' ,name , ', age为 ' ,age , ', 薪水为: ',
salary);
until has_data = 0
end repeat;
close emp_result;
end$
DELIMITER ;
执行结果：
MYSQL通过提供句柄机制来判断数据表中是否有记录。
DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;
DECLARE EXIT HANDLER FOR NOT FOUND定义了找不到数据的句柄，当出现找不到数据的情况时，就会触发后面的语句set has_data = 0 执行。
注意点：句柄的声明要紧跟在游标的声明后面，否则会出现报错。
DECLARE emp_result CURSOR FOR select * from emp;
DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;
2-2-9 存储函数
存储函数与存储过程有些类似，它们在语法上的区别如下：
- PROCEDURE与FUNCTION，并且存储函数需要RETURN
- 存储函数可以看作“有返回值的存储过程”，实际上存储过程也可以在参数列表中通过OUT关键字返回数据。
- 实际上存储函数能够完成的事情使用存储过程也可以实现。
CREATE FUNCTION function_name([param type ... ])
RETURNS type
BEGIN
...
END;
示例:定义一个函数用于查询特定国家的记录个数
delimiter $
create function count_city(countryId int)
returns int
begin
declare cnum int ;
select count(*) into cnum from city where country_id = countryId;
return cnum;
end$
delimiter ;
执行结果：
上图中通过select语句调用存储函数，统计指定id的记录个数。
3 触发器
3-1 概述
定义：在 insert/update/delete 之前或之后，触发并执行的SQL语句集合
应用场景：
- 协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作
分类：
- NEW（新纪录）与OLD(被修改的老记录)是2个行记录变量
- 存储触发器中发生变化的记录
注意点：MYSQL数据库只支持行级触发器，不支持语句级触发器。Oracle数据则都支持。
3-2 触发器的创建
实例：通过触发器记录表的数据变更日志 , 包含增加, 修改 , 删除 ;
模板
create trigger trigger_name
before/after insert/update/delete # 触发器类型
on tbl_name # 数据表名称
for each row # 行级触发器
begin
trigger_stmt ; # SQL语句集合
end;
step1:创建日志表来保存操作记录
create table emp_logs(
id int(11) not null auto_increment,
operation varchar(20) not null comment '操作类型, insert/update/delete',
operate_time datetime not null comment '操作时间',
operate_id int(11) not null comment '操作表的ID',
operate_params varchar(500) comment '操作参数',
primary key(`id`)
)engine=innodb default charset=utf8;
step2:创建INSERT型触发器，记录数据表的插入操作
DELIMITER $
create trigger emp_logs_insert_trigger
after insert
on emp
for each row
begin
insert into emp_logs (id,operation,operate_time,operate_id,operate_params)
values(null,'insert',now(),new.id,concat('插入后(id:',new.id,', name:',new.name,',
age:',new.age,', salary:',new.salary,')'));
end $
DELIMITER ;
step3:创建update型触发器，记录数据表的插入操作
DELIMITER $
create trigger emp_logs_update_trigger
after update
on emp
for each row
begin
insert into emp_logs (id,operation,operate_time,operate_id,operate_params)
values(null,'update',now(),new.id,concat('修改前(id:',old.id,', name:',old.name,',
age:',old.age,', salary:',old.salary,') , 修改后(id',new.id, 'name:',new.name,',
age:',new.age,', salary:',new.salary,')'));
end $
DELIMITER ;
step4:创建delete 行的触发器 ,,记录删除数据的日志
DELIMITER $
create trigger emp_logs_delete_trigger
after delete
on emp
for each row
begin
insert into emp_logs (id,operation,operate_time,operate_id,operate_params)
values(null,'delete',now(),old.id,concat('删除前(id:',old.id,', name:',old.name,',
age:',old.age,', salary:',old.salary,')'));
end $
DELIMITER ;
总结：
- 触发器的创建必须要指定作用的数据表以及数据操作
- 触发器执行的SQL语句中可以通过new或者old获得之前的记录。
3-3 触发器的删除与查看
drop trigger [schema_name.]trigger_name # shema_name是数据库名称，没有指定则是当前数据库
show triggers; # 查看触发器
- 上图中可以看到之前创建的3个触发器，以及触发之后执行的SQL语句。
参考资料
20210228