一、视图
视图是从一个或者几个基本表(或视图)导出的表。它与基 本表不同,是一个虚表,视图只能用来从查询,不能做增删改(虚拟的表)
1.创建视图
创建视图的语法:
create view 视图名【view_xxx / v_xxx】
as 查询语句
create view v_stu_man as
select * from student where ssex='男';
2.视图的使用
select * from v_stu_man;
create view v_vstuman_class as
select v_stu_man.* from v_stu_man
left join class on v_stu_man.classid=class.classid
select * from v_vstuman_class;
此时,若修改表中的sname='赵蕾蕾' ,查看数据显示结果
3.查看库中所有的视图
-- 查看库中所有的视图
select * from information_schema.VIEWS
WHERE table_schema = 'myschool';
4.删除视图
drop view v_stu_man;
5 视图的作用
1.简化查询
2.重写格式化数据
3.频繁访问数据库
4.过滤数据
二、存储过程
存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,存 储在数据库中,经过第一次编译后,再次调用不需要重复编译,用户通过指定存储过程的名字并给出参数 (如果该存储过程带有参数)来执行它
1.为什么使用存储过程
- 业务流程复杂:业务复杂时,SQL语句相互依赖,顺序执行;
- 频繁访问数据库:每条SQL语句都需单独连接和访问数据库;
- 先编译后执行:SQL语句的执行需要先编译。
2.创建存储过程
创建存储过程的语法:
create procedure 存储过程名字【proc_xxx】(形参列表)
begin
一组sql语句集end
3.创建最简单的存储过程
delimiter $$
create procedure proc_test()
begin
select * from student;
end $$
delimeter ;
4. 使用存储过程
call proc_test();
存储过程与函数的区别:
①语法 :关键字不同,存储过程是procedure, 函数是function;
②执行 :存储过程可以独立执行,函数必须依 赖表达式的调用;
③返回值 :存储过程可以定义多个返回结果, 函数只有一个返回值;
④功能 :函数不易做复杂的业务逻辑,但是存 储过程可以。
5. 带参数的存储过程
-- in 只入参(值传递)
-- out 只出参 (无)-- inout 出入参 (引用传递)
-- 环境变量 @ 局部环境变量 @@ 全局环境变
-- 带参数的存储过程
delimiter $$
create procedure proc_test2(
in a int, -- in 只入参(值传递)
out b int, -- out 只出参 (无)
inout c int -- inout 出入参 (引用传递)
)
begin
set a = a+1;
set b = b+100;
set c = c+1000;
end $$
delimiter ;
-- 环境变量 @ 局部环境变量 @@ 全局环境变量
set @x = 10; -- 11 10 null
set @y = 20; -- 120 20 null
set @z = 30; -- 1030 30 null
select @x, @y, @z;
call proc_test2(@x,@y,@z)
select @x, @y, @z;
6.删除存储过程
drop procedure proc_stuPage;
7.分页(面试题!)
-- 面试题
-- 分页
-- 删除存储过程
drop procedure proc_stuPage;
delimiter $$
create procedure proc_stuPage(
in curpage int,
in sizepage int,
out stucount int,
out pagecount int
)
begin
declare cp int;
set cp = (curpage-1)*sizepage;
select count(*) from student into stucount;
set pagecount = ceiling(stucount / sizepage);
select * from student limit cp,sizepage;
end $$
delimiter ;
set @a = 0;
set @b = 0;
call proc_stuPage(2,3,@a,@b);
select @a,@b
8.存储过程的缺陷
维护性 :存储过程的维护成本高,修改调试较为麻烦
移植性:大多数关系型数据库的存储过程存在细微差异。
协作性 :没有相关的版本控制或者IDE,团队中对于存储过程的使用大多是 依赖文档。
三、触发器
触发器是数据库中针对数据库表操作触发的 特殊的存储过程。
1.创建触发器
创建触发器的语法:
delimiter $$
create trigger 触发器名【trig_xxx】
before/after insert/ update / delete
on 表名 for each row
begin
触发后执行的一组sql语句
end $$
delimiter ;
注意:
• 触发器触发时间分为Before和After两种;
• 主要针对表的增删改操作,可单独指定,也可全部指定。
• 查看所有的触发器 SELECT DISTINCT EVENT_OBJECT_TABLE FROM information_schema.`TRIGGERS` WHERE EVENT_OBJECT_SCHEMA=‘数据库名'
-- 删除学生 sid 为 1 在此之前把学生成绩删除
delimiter $$
create trigger trig_delstu_delsc
before delete on student for each row
begin
-- old 已经存在的数据 、 new 还不存在的数据
delete from sc where sid = old.sid;
end $$
delimiter ;
delete from student where sid = 1;
select * from student;
select * from sc;
[SQL] delete from student where sid = 2;
受影响的行: 1
时间: 0.008ms
2.查看所有的触发器
SELECT * FROM information_schema.`TRIGGERS`
WHERE trigger_schema = 'myschool'
3.删除触发器
drop trigger trig_delstu_delsc
4.存储过程和触发器的区别
- 语法:关键字不同,存储 过程是procedure, 触发器是trigger;
- 功能:存储过程是一组特定功能的 SQL语句,触发器则是SQL语 句前后执行,本身不影响原功 能。
- 执行: 存储过程需要调用才执 行,触发器自动执行;
- 返回值 : 存储过程可以定义返回值, 但是触发器没有返回值;