目录
视图
概述
视图:从一个或者几个基本表(或视图)导出的表,它与基本表不同,是一个虚表。
操作
-- 创建视图
/*
CREATE view 视图名 AS 查询语句
注:起名一般是view/v_xxx
*/
-- 1.查询所以学生中的男同学
CREATE VIEW v_stu_man as
SELECT * FROM student WHERE ssex = '男'
-- 视图使用
/*
视图只能用于查询
不能做增删改【因为它是一张虚拟表】
*/
SELECT * FROM v_stu_man
-- 给视图起别名
SELECT * FROM v_stu_man vsm
LEFT JOIN class
ON vsm.classid = class.classid
CREATE VIEW v_vsm_class AS
SELECT vsm.*,classname FROM v_stu_man vsm
LEFT JOIN class
ON vsm.classid = class.classid
SELECT * FROM v_vsm_class
-- 改变表中的数据,视图会变
UPDATE student SET sname = '赵磊磊' WHERE sid = 1
SELECT * FROM student
SELECT * FROM v_vsm_class
-- 查看库中所有的视图
SELECT * FROM information_schema.VIEWS
WHERE table_schema - 'myschool';
-- 删除视图
DROP VIEW v_stu_man
作用
- 简化查询
- 重写格式化数据
- 频繁访问数据库
- 过滤数据
存储过程
概述
存储过程:是在大型数据库系统中,一组为了完成特定功能的SQL语句集,存储在数据库中,经过第一次编译后,再次调用不需要重复编译,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它
操作
# 存储过程
-- 存储过程的创建
/*
CREATE procedure 存储过程名 (形参列表)
BEGIN
一组SQL语句集
END
注:存储过程名一般命名为【proc_xxx】
*/
-- 创建最简单的存储过程
delimiter $$ -- 设置定界符:delimiter $$
CREATE PROCEDURE proc_test()
BEGIN
SELECT * FROM student;
END $$
delimiter ; -- 恢复默认的定界符
-- 使用存储过程
call proc_test();
-- 删除存储过程
drop procedure proc_stuPage
-- 带参的存储过程
delimiter $$
CREATE PROCEDURE proc_test1(
in a int , -- 只入参(值传递)
out b int, -- 只出参(无)
inout c int) -- 出入参(引用传递)
BEGIN
set a = a+1;
set b = b+100;
set c = c+1000;
END $$
delimiter ;
-- 环境变量 @:局部环境变量 @@:全局环境变量
set @x = 10;
set @y = 20;
set @z = 30;
select @x, @y, @z;
call proc_test1(@x,@y,@z)
select @x, @y, @z; -- 10 null 1030
存储过程与函数的区别
- 语法:关键字不同,存储过程是procedure;函数是function
- 返回值:存储过程可以定义多个返回结果;函数只有一个返回值
- 执行:存储过程可以独立执行;函数必须依赖表达式的调用
- 功能:函数不易做复杂的业务逻辑,但存储过程可以
存储过程的缺陷
- 维护性:存储过程的维护成本高,修改调试较为麻烦
- 移植性:大多数关系型数据库的存储过程存在细微差异
- 协作性:没有相关的版本控制或者IDE,团队中对于存储过程的使用大多是依赖文档
案例
-- 创建存储过程(分页)
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; -- (当前页码-1)*页面容量
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
-- 删除存储过程
drop procedure proc_stuPage;
触发器
概述
触发器:是数据库针对数据库表操作触发的特殊的存储过程
操作
# 触发器——满足条件后,按照写的内容去执行——隐式执行
-- 创建触发器
/*
delimiter $$
CREATE triger 触发器名【trig_xxx】
before/ after
insert/ update/ delete
on 表名 for each row
begin
出发后执行的一组SQL语句
end $$
delimiter ;
*/
delimiter $$
CREATE trigger trig_destu_desc
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 information_schema.`TRIGGERS`
WHERE TRIGGER_SCHEMA = 'myschool'
-- 删除触发器
drop trigger trig_destu_desc
存储过程和触发器的区别
- 语法:关键字不同,存储过程是procedure;触发器是trigger
- 返回值:存储过程可以定义返回值;触发器没有返回值
- 执行:存储过程需要调用才执行;触发器自动执行
- 功能:存储过程是一组特定功能的SQL语句,触发器则是SQL语句前后执行,本身不影响原功能