MySQL数据库---Day 04

一、视图

        视图（view ） 是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。作为一个 select 语句保存在数据字典中的。通过视图，可以展现基表( 用来创建视图的表 ) 的部分数据；视图数据来自定义视图的查询中使用的表，使用视图动态生成。

        使用视图的原因

        1）简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。

        2）安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。

        3）数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

        总而言之，使用视图的大部分情况是为了保障数据安全性，提高查询效率。

-- 创建视图
create view 视图名 as select语句;
-- 查询视图中的数据
select *|字段名 from 视图名
-- 查询视图
describe 视图名
desc 视图名
show create view 视图名;
-- 修改视图
alter view 视图名 as select语句
-- 删除视图
drop view 视图名1,视图名2

-- 基于多表创建视图
create view student_detail as select s.*,t.name tname,t.t_age,t.t_tel from
student s inner join teacher t on t.tno = s.tno
-- 查询视图中的数据
select * from student_detail
-- 像视图中插入数据
insert into student_detail (id,name,age,sex,birth,tel,tno) values
(null,'test',11,'man','2021-01-01','110',1)
select * from student
-- 基于多表创建视图
create view student_detail_info as select s.name 学生姓名 , t.name 教师姓名 from
student s inner join teacher t on t.tno = s.tno
insert into student_detail_info(学生姓名,教师姓名) values ('dongyaning','test')
select * from student_detail_info

二、触发器

        MySQL 的触发器 (trigger) 是嵌入到 MySQL 中的一段程序，是 MySQL 中管理数据的有力工具。通过对数据表的相关操作来触发、激活从而实现执行。比如当对 student 表进行（ INSERT ， DELETE 或UPDATE）时就会激活它执行。

        触发器是一种特殊的 存储过程 ，它在试图更改触发器所保护的数据时自动执行。

        在 MySQL 中，只有执行 INSERT 、 UPDATE 和 DELETE 操作时才能激活触发器，其它 SQL 语句则不会激活触发器。

        触发器的优缺点

        1.触发器的优点如下：

        触发器的执行是自动的，当对触发器相关表的数据做出相应的修改后立即执行。

        触发器可以实施比 FOREIGN KEY 约束、 CHECK 约束更为复杂的检查和操作。

        触发器可以实现表数据的级联更改，在一定程度上保证了数据的完整性。

        2.触发器的缺点如下：

        使用触发器实现的业务逻辑在出现问题时很难进行定位，特别是涉及到多个触发器的情况下，会使后期维护变得困难。

        大量使用触发器容易导致代码结构被打乱，增加了程序的复杂性，

        如果需要变动的数据量较大时，触发器的执行效率会非常低。

create trigger 触发器名 before|after
insert|update|delete
on 表名 for each row
触发器主体
eg:
create table log_info(
id int(11) primary key auto_increment,
content varchar(50),
create_date datetime
)
-- 每新增一个用户之后，像日志表中插入一条日志
create trigger trigger_user after
insert on user_info for each row
insert into log_info values(null,'新增了一个用户',now())
-- 触发触发器
insert into user_info values (null,'xxx',10)

-- new |old
new:可以获取插入后及修改后的数据
old：可以获取修改前及删除前的数据
create table log_info(
id int(11) primary key auto_increment,
content varchar(50),
create_date datetime
)
-- 每新增一个用户之后，像日志表中插入一条日志
create trigger trigger_user after
insert on user_info for each row
insert into log_info values(null,'新增了一个用户',now())
-- 触发触发器
insert into user_info values (null,'王胜伟',10)
-- 创建触发器，每修改一个用户，像日志表中插入一条日志
create trigger update_user after update on user_info for each row
insert into log_info values (null,'修改了一个用户',now())
-- 触发触发器
update user_info set age = 100 where user_name = '王胜伟'
-- 创建触发器，实现每新增一个用户，往日志表中插入一条日志，日志内容包括插入后的用户名及年龄
create trigger trigger_user2 after insert on user_info for each row
insert into log_info values(null,concat('新增了一个用户，用户名
为：',new.user_name,'年龄为',new.age),now())
-- 拼接
concat(字符串1，字符串2)
-- 触发触发器
insert into user_info values (null,'杜新春',10)
-- 查询指定的触发器
select * from information_schema.triggers where trigger_name = 'trigger_user2'
-- 查看当前库的触发器
show triggers
-- 创建触发器，实现每修改一个学生信息，就去更新的信息和更新的前的信息，存储在日志里面
create trigger update_trigger after update on student for each row
insert into log_info values(null,
concat('修改了一个学生，修改前的学生姓名为',old.name,' 修改后你的学生姓名
为',new.name),now())
-- 触发触发器
update student set name = '赵泽玉' where id in(1,3,5,7)
-- 删除触发器
drop trigger 触发器名;
eg:
-- 删除触发器
drop trigger update_trigger

三、存储过程

        存储过程（ procedure) 是一组为了完成特定功能的 SQL 语句集合。使用存储过程的目的是将常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此称为存储过程。当以后需要数据库提供与已定义好的存储过程的功能相同的服务时，只需调用“CALL存储过程名字 ” 即可自动完成。

        优点

        1.存储过程是通过处理封装在容易使用的单元中，简化了复杂的操作。

        2.简化对变动的管理。如果表名、列名、或业务逻辑有了变化。只需要更改存储过程的代码。使用它的人不用更改自己的代码。

        3.通常存储过程都是有助于提高应用程序的性能。当创建的存储过程被编译之后，就存储在数据库中。

        但是，MySQL 实现的存储过程略有所不同。

        MySQL存储过程是按需编译。在编译存储过程之后， MySQL 将其放入缓存中。

        MySQL为每个连接维护自己的存储过程高速缓存。如果应用程序在单个连接中多次使用存储过程，则使用编译版本，否则存储过程的工作方式类似于查询。

        4.存储过程有助于减少应用程序和数据库服务器之间的流量。

        因为应运程序不必发送多个冗长的SQL 语句，只用发送存储过程中的名称和参数即可。

        5.存储过程度任何应用程序都是可重用的和透明的。存储过程将数据库接口暴露给所有的应用程序，以方便开发人员不必开发存储过程中已支持的功能。

        6.存储的程序是安全的。数据库管理员是可以向访问数据库中存储过程的应用程序授予适当的权限，而不是向基础数据库表提供任何权限。

        缺点

        1.如果使用大量的存储过程，那么使用这些存储过程的每个连接的内存使用量将大大增加。

        此外，如果在存储过程中过度使用大量的逻辑操作，那么CPU 的使用率也在增加，因为 MySQL数 库最初的设计就侧重于高效的查询，而不是逻辑运算。

        2.存储过程的构造使得开发具有了复杂的业务逻辑的存储过程变得困难。

        3.很难调试存储过程。只有少数数据库管理系统允许调试存储过程。不幸的是，MySQL 不提供调试存储过程的功能。

        4.开发和维护存储过程都不容易。

        开发和维护存储过程通常需要一个不是所有应用程序开发人员拥有的专业技能。这可能导致应用程序开发和维护阶段的问题。

        5.对数据库依赖程度较高，移值性差。

DELIMITER $$
create procedure 存储过程名(in 变量名 数据类型, out 变量名 数据类型, inout 变量名 数据类
型)
begin
declare 变量名 数据类型
sql语句1;
sql语句2;
...
end $$
DELIMITER ;
-- 创建一个存储过程，实现查询所有用户
DELIMITER $$
create procedure showUser()
-- 存储过程体
begin
select * from user_info;
end$$
DELIMITER ;
-- 调用存储过程
call showUser()
-- 创建存储过程，实现查询有多少指定性别的学生
create procedure queryBySex(in s_sex varchar(5), out s_count int(11))
begin
select count(*) into s_count from student where sex = s_sex;
select s_count;
end
-- 调用存储过程
call queryBySex('woman',@s_count)
-- 查询性别为男的学生有多少个
select count(*) from student where sex = 'man'