本章目录
一.视图
二.触发器
三.事务
四.存储过程
五.函数
六.数据备份与恢复
七.流程控制(了解)
一.视图
什么是视图
视图是有一张表或多张表的查询结果构成的一张虚拟表
为什么使用视图
当我们在使用多表查询时 我们的sql语句可能会非常的复杂,如果每次都编写一遍sql'的话无疑是一件麻烦的事情,这时候就可以使用视图来避免多次编写sql的问题;
简答的说可以帮我们节省sql的编写,
视图的另一个作用是,可以不同的视图来展示开放不同数据的访问
例如,同一张工资表,老板可以查看全部,部门主管可以查看该部门所有人,员工只能看自己的一条记录
使用方法
创建视图
CREATE [OR REPLACE] VIEW view_name [(column_list)]
AS select_statement加上OR REPLACE 时如果已经存在相同视图则替换原有视图
column_list指定哪些字段要出现在视图中
注意:由于是一张虚拟表,视图中的数据实际上来源于其他其他表,所以在视图中的数据不会出现在硬盘上
使用视图
视图是一张虚拟表 所以使用方式与普通表没有任何区别
查看视图
1.desc view_name; //查看数据结构
2.show create view view_name;//查看 创建语句
修改视图
alter view view_name as select_statement
删除视图
drop view view_name
案例1: 简化多表sql语句
有哦学生表和详细信息表 每次都要写查询连接查询比较繁琐,可以使用视图来简化查询.
#准备数据
create database db02 charset utf8;
use db02
create table student(
s_id int(3),
name varchar(20),
math float,
chinese float
);
insert into student values(1,'tom',80,70),(2,'jack',80,80),(3,'rose',60,75);
create table stu_info(
s_id int(3),
class varchar(50),
addr varchar(100)
);
insert into stu_info values(1,'二班','安徽'),(2,'二班','湖南'),(3,'三班','黑龙江');
#创建视图包含 编号 学生的姓名 和班级
create view stu_v (编号,姓名,班级) as
select
student.s_id,student.name ,stu_info.class
from student,stu_info
where student.s_id=stu_info.s_id;
# 查看视图中的数据
select *from stu_v;案例2: 隔离数据
一些情况下我们可能需要对某用户开放部分数据,隐藏其他数据,可以用视图来实现;
下例中:希望市场部的员工只能看市场部的工资信息
# 创建工资表
create table salarys(
id int primary key,
name char(10),
salary double,
dept char(10)
);
insert into salarys values
(1,"刘强东",900000,"市场"),
(2,"马云",800090,"市场"),
(3,"李彦宏",989090,"财务"),
(4,"马化腾",87879999,"财务");
# 创建市场部视图
create view dept_sc as select *from salarys where dept = "市场";
# 查看市场部视图
select *from dept_sc;注意 对视图数据的insert update delete 会同步到原表中,但由于视图可能是部分字段,很多时候会失败
总结:mysql可以分担程序中的部分逻辑,但这样一来后续的维护会变得更麻烦
如果需要改表结构,那意味着视图也需要相应的修改,没有直接在程序中修改sql来的方便
二.触发器
什么是触发器
触发器是一段与表有关的mysql程序
当这个表在某个时间点发生了某种事件时 将会自动执行相应的触发器程序
何时使用触发器
当我们想要在一个表记录被更新时做一些操作时就可以使用触发器
但是我们完全可以在python中来完成这个事情,因为python的扩展性更强,语法更简单
创建触发器
语法:
CREATE TRIGGER t_name t_time t_event ON table_name FOR EACH ROW
begin
stmts.....
end支持的时间点(t_time):时间发生前和发生前后 before|after
支持的事件(t_event): update insert delete
在触发器中可以访问到将被修改的那一行数据
根据事件不同 能访问也不同
update 可用OLD访问旧数据 NEW访问新数据
insert 可用NEW访问新数据
delete 可用OLD访问旧数据
可以将NEW和OLD看做一个对象其中封装了这列数据的所有字段
案例:
有cmd表和错误日志表,需求:在cmd执行失败时自动将信息存储到错误日志表中
#准备数据
CREATE TABLE cmd (
id INT PRIMARY KEY auto_increment,
USER CHAR (32),
priv CHAR (10),
cmd CHAR (64),
sub_time datetime, #提交时间
success enum ('yes', 'no') #0代表执行失败
);
#错误日志表
CREATE TABLE errlog (
id INT PRIMARY KEY auto_increment,
err_cmd CHAR (64),
err_time datetime
);
# 创建触发器
delimiter //
create trigger trigger1 after insert on cmd for each row
begin
if new.success = "no" then
insert into errlog values(null,new.cmd,new.sub_time);
end if;
end//
delimiter ;
#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
INSERT INTO cmd (
USER,
priv,
cmd,
sub_time,
success
)
VALUES
('egon','0755','ls -l /etc',NOW(),'yes'),
('egon','0755','cat /etc/passwd',NOW(),'no'),
('egon','0755','useradd xxx',NOW(),'no'),
('egon','0755','ps aux',NOW(),'yes');
# 查看错误日志表中的记录是否有自动插入
select *from errlog;
delimiter 用于修改默认的行结束符 ,由于在触发器中有多条sql