MySQL-视图、存储过程和触发器

一、视图的定义和使用 

        视图是从一个或者几个基本表（或视图）导出的表。它与基本表不同，是一个虚表,视图只能用来查询。不能做增删改查(虚拟的表)

1.视图的作用

1. 简化查询
2. 重写格式化数据
3. 频繁访问数据库
4. 过滤数据

2.创建视图

-- 创建视图
-- 语法：create view 视图名 【view_xxx / v_xxx】
-- as 查询语句

CREATE VIEW v_stu_man AS
SELECT * FROM student WHERE ssex = '男';

3.视图的使用

-- 视图的使用
SELECT * FROM v_stu_man;

运用：

//创建一个新视图
CREATE VIEW v_vstuman_class AS
SELECT v_stu_man.*,classname FROM v_stu_man
LEFT JOIN class ON v_stu_man.classid = class.classid

//使用这个视图
SELECT * FROM v_vstuman_class

若修改表中的Sid = 1的Sname='赵蕾蕾'，查看数据显示结果

UPDATE student SET sname = '赵蕾蕾' WHERE sid = 1

4.查看库中所有的视图

-- 查看库中所有的视图
SELECT * FROM
information_schema.VIEWS 
WHERE table_schema = 'schoolp';

5.删除视图 

-- 删除视图
DROP VIEW v_stu_man;

二、存储过程的创建和使用

1.什么是存储过程

        存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后，再次调用不需要重复编译，用户通过指定存储过程的名字并给出参数 （如果该存储过程带有参数）来执行它。

简单来讲就是：SQL语句集+预编译+特定功能

2.为什么要使用存储过程

1. 业务流程复杂：业务复杂时，SQL语句相互依赖， 顺序执行；
2. 频繁访问数据库：每条SQL语句都需单独连接和访 问数据库；
3. 先编译后执行：SQL语句的执行需要先编译。

3.创建存储过程

语法：# 存储过程
-- create procedure 存储过程名字【proc_xxx】(形参列表)
-- begin
--     一组sql语句集
-- end

-- 创建最简单存储过程
DELIMITER $$
CREATE PROCEDURE proc_test()
BEGINSELECT * FROM student;
END  $$
DELIMITER;

4.存储过程的操作

-- 存储过程的基本用法
DELIMITER $$ -- 定制定界符
creat PROCEDURE pro_showstudent()
BEGINSELECTstudent.Sname,student.birthday,student.classid,student.Sid,student.Ssex
FROM student 
LEFT JOIN sc  ON student.Sid = sc.Sid
LEFT JOIN course ON course.Cid = sc.Cid
END$$
DELIMITER ; -- 恢复默认的定界符
CALL pro_showstudent();

-- 带参数的存储过程
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;   
SET @Y = 20;     
SET @z = 30;      SELECT @X, @Y, @z;CALL proc_test2(@X,@Y,@z)SELECT @X, @Y, @z;

（面试题）写一个分页的存储过程

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); -- ceiling 向上取整SELECT * FROM student LIMIT (curpage-1)*sizepage;,sizepage;
END $$
DELIMITER ;SET @a = 0;
SET @b = 0;
CALL proc_stuPage(2,3,@a,@b);SELECT @a,@b

5.存储过程与函数的区别

6.存储过程的缺陷

三、触发器

1.什么是触发器

.

2.创建触发器

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 ;

3.触发器的查看

SELECT * FROM information_schema.`TRIGGERS`
WHERE trigger_schema = 'schoolp'

四、存储过程和触发器的区别