当前位置:Gxlcms > mysql > Mysql----浅入浅出之视图、存储过程、触发器_MySQL

Mysql----浅入浅出之视图、存储过程、触发器_MySQL

时间:2021-07-01 10:21:17 帮助过:31人阅读

一、视图 VIEW

视图是虚拟的表,本身不存储任何数据。只有运行时,才包含动态检索出来的数据。

eg:SELECT sid, name, sex, s_num FROM student, school WHERE sid = 2 AND student.sid = scholl.sid ;

这个简单的查询涉及到两个表。所以任何需要这个数据的人都必须熟悉两个表以及之间的关系。想检索其他学生信息,就必须修改WHERE子句。如果可以把整个查询包装成一个虚拟表studentinfo,那么就可以这样得到数据:

eg:SELECT sid, name, sex, s_num FROM studentinfo WHERE sid = 2;

使用视图可以重用sql语句。对于使用的人可以不必知道细节。对原始数据也有保护作用。

视图也有一些限制,比如不能索引,不能有关联的触发器。名字必须唯一。

创建视图:

eg:CREATE VIEW studentinfo AS SELECT sid name, sex, s_num FROM student, school WHERE student.sid = school.sid;

视图也可以更新,但是只在特定的情况下。如果视图有这些定义,则不能更新:分组、联结、子查询、并、聚集函数DISTINCT、计算列。

二、存储过程

当需要处理的业务逻辑很复杂的时候,可以一条条的写sql语句,而且需要考虑到所有的需要处理的细节、数据完整性。可以创建存储过程来代替。它就像一个批处理,包含预先定义好的一条或多条sql语句的集合。但它的作用可不仅限于此。

创建:

eg:CREAT PROCEDURE prostudent()

BEGIN

SELECT Max(score) AS scoremax FROM student;

END;

删除:

eg:DROP PROCEDURE prostudent;

使用参数:

eg:CREATE PROCEDURE prostudent(OUT scorehigh DECIMAL(8, 2), OUT scorelow DECIMAL(8, 2))

BEGIN

SELECT Max(score) INTO scorehigh FROM student;

SELECT Min(score) INTO scorelow FROM student;

END;

执行:

eg:CALL prostudent(@scorelow, @scorehigh);

SELECT @scorehigh, @scorelow;

执行了名为prostudent的存储过程,并返回了最高成绩和最低成绩。

此外,存储过程还可以写注释、定义临时变量、IN传入参数、流程控制语句。

SHOW CREATE PROCEDURE ****; 可以查看创建存储过程的语句。

SHOW PROCEDURE STATUS;可以查看这个存储过程是何时、由谁创建的。

三、游标

SELECT语句返回的是一个结果集,可能是满足条件的多个航。那我们想对这个结果集的每一行进行一些处理时,或者在第一行、最后一行、前一行等特殊要求时,怎么办呢?这里就用到里游标。mysql中的游标只能用于存储过程,这是与其他数据库的不同。

使用游标需要先定义DECLARE *** CURSOR FOR、然后打开OPEN ***、使用、关闭CLOSE ***。游标的生命周期只在存储过程中,也就是如果你不关闭它,当存储过程END时,会自动关闭。

游标打开后,可以用FETCH取出一行,并在内部指向下一行的位置,当再次FETCH的时候,将取出下一行。

举个例子:现在要将所有sid大于3的学生的成绩加和。

eg: DELIMITER //

CREATE PROCEDURE sumofscore(OUT sum INT)

BEGIN

DECLARE done BOOLEEAN DEFAULT 0;

DELCARE tmp INT;

DECLARE s INT DEFAULT 0;

DECLARE yb CURSOR FOR SELECT score FROM student WHERE sid > 3;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

OPEN yb;

REPEAT

FETCH yb INTO tmp;

SET s = s + tmp;

UNTIL done END REPEAT;

CLOSE yb;

SELECT s INTO sum;

END

//

DELIMITER ;

DELIMITER 重定义mysql的结束符。02000是数据未找到的错误码,利用它来判断是否遍历完所有数据。

四、触发器

在某些需求下,想要某些语句在特定事件发生时,自动执行,那么就用到了触发器。mysql触发器只能响应delete、insert和update语句。

创建触发器:

eg:CRESTE TRIGGER newstudent AFTER INSERT ON student FOR EACH ROW SELECT new.sid INTO @s;

触发器名字是newstudent,INSERT指定响应事件是插入操作。AFTER/BEFORE指定是在事件执行前还是事件执行后触发。FOR EACH ROW指定对插入的每一行都进行操作,所以每插入一行,就把这行的sid传给变量s。每个表每个事件只允许有一个触发器,所以每个表最多有6个触发器。一个触发器也只能响应一个事件。

删除触发器:

eg:DROP TRIGGER newstudent;

使用触发器:

在INSERTZHONG ,可引用一个名为NEW的虚拟表,访问被插入的行。在BEFROE INSERT中,也可以引用NEW,甚至可以更新数据,以改变插入数据的内容。

DELETE触发器中,可以引用名为OLD的虚拟表,访问被删除的行。

人气教程排行