视图、存储过程、触发器

视图：

1. 一种逻辑对象，不占物理存储空间，
2. 来自一个或多个表，是由查询结果形成的一张虚拟表，
3. 同一张表可以创建多个视图

创建语法：

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

例：

CREATE VIEW productcustomers AS
SELECT cust_name, cust_contact, prod_id
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND   orderitems.order_num = orders.order_num
with check option
#最后一行是检查，防止后续操作视图时添加不满足筛选条件的数据，不需要可忽略不写

查看语法：show create view viewname

删除语法：drop view 视图名

更新语法：①先drop后create           ②create or replace view create view 视图名 as SQL语句

 

视图一般服务于查询操作，不用于数据更改，当用于数据更改时：

视图d内容是单表的时候，UPDATE，DELETE可以执行成功，对于INSERT如果未设值的字段都有默认值，或者是可以为空的字段的时候可以执行成功，否则会执行失败。对

视图内容是两张或两张表以上的时候，如果使用了分组查询语句的时候，INSERT，UPDATE，DELETE都会执行失败。如果没有使用分组查询语句的时候UPDATE是可以执行成功，DELETE会失败。INSERT只能将结构插入单个表中，所有插入的字段只能都在一张表中，而且未设置的字段都有默认值，或者是可以为空的字段的时候才可以执行成功，否则会执行失败。  

 

优点：筛选数据、重用SQL语句、简化SQL操作、简化权限管理以及为向其他应用程序输出而重新组织数据等。

注：视图不能索引，也不能有关联的触发器或默认值，不能引用临时表。

 

存储过程：

一组为了完成特定功能的SQL语句集合，经预编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。

通俗来讲：存储过程其实就是能完成一定操作的一组SQL语句（类似封装的函数）。

MySQL执行存储过程的语句为CALL（命名需要带“（）”）。 sqlplus使用exec。(以下按mysql讲解）

 

1. 创建无参存储过程：

CREATE PROCEDURE productpricing()
BEGIN
   SELECT AVG(prod_price) AS priceaverage
   FROM products;
END;

注：mysql命令行客户机的分隔符

默认的 MySQL语句分隔符为 “ ; ” 。 mysql命令行实用程序也使用 “ ; ” 作为语句分隔符。如果命令行实用程序要解释存储过程自身内的“ ; ”字符，则它们最终不会成为存储过程的成分，这会使存储过程中的 SQL出现句法错误。解决办法是临时更改命令行实用程序的语句分隔符，如下所示（后面不再声明，注意）：

mysql> DELIMITER //
mysql> CREATE PROCEDURE productpricing()
    -> BEGIN
    -> SELECT AVG(prod_price) AS priceaverage
    -> FROM products;
    -> END //
Query OK, 0 rows affected (0.06 sec)

mysql> DELIMITER ;

“ DELIMITER // ” 告诉命令行实用程序使用 “ // ” 作为新的语句结束分隔符，可以看到标志存储过程结束的 END定义为 “ END // ”而不是 “ END ; ” 。这样，存储过程体内的 “ ; ”仍然保持不动，并且正确地传递给数据库引擎。最后，为恢复为原来的语句分隔符，可在执行完成后使用 “ DELIMITER ; ” 修正。除 “ \ ” 符号外，任何字符都可以用作语句分隔符。

使用存储过程：

CALL productpricing();

 

2. 创建参数存储过程

       3种参数类型：

　　      IN（输入参数）：表示调用者向过程传入值（传入值可以是字面量或变量）

　　      OUT（输出参数）：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）

　　      INOUT（输入输出参数）：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

 

① 通过存储过程查询tb_score表，查询课程号为1（cID=1）的所有学生的成绩，存储过程定义为：

DELIMITER //
CREATE PROCEDURE Proc_cID(IN classID INT) 
BEGIN 
    SELECT * FROM tb_score WHERE cID=classID; 
END //

DELIMITER ;

调用：

call Proc_cID(1);

 

② 查询课程号为1的学生的人数和平均成绩，则存储过程定义如下：

CREATE PROCEDURE Proc_AVG(IN classID INT,OUT total INT,OUT a_s FLOAT)
BEGIN 
    SELECT COUNT(*),AVG(grade) INTO total,a_s FROM tb_score WHERE cID=classID; 
END;

调用（注：所有MySQL变量都必须以 @开始）：

call Proc_AVG(1, @total, @average);

c此时还不会显示数据，可根据需要使用 " select " 显示，例如要显示总数和平均数：

select @total,@average;

 

3. 删除存储过程：

drop procedure procedurename;

 

存储过程优点：

① 模块化：一次创建，多次调用

② 速度快、效率高

③ 安全性好（权限限制）

 

触发器（尽量少使用，很消耗资源；基于mysql讲解）：

触发器是一种特殊类型的存储过程，不由用户直接调用。创建触发器时会对其进行定义，以便在对特定表或列作特定类型的数据修改时执行触发器中定义的语句集合。常常用于强制业务规则和数据完整性。

 

1. 分类：

①INSERT型触发器：插入某一行时激活触发器，可能通过INSERT、LOAD DATA、REPLACE 语句触发(LOAD DAT语句用于将一个文件装入到一个数据表中，相当与一系列的INSERT操作)；

②UPDATE型触发器：更改某一行时激活触发器，可能通过UPDATE语句触发；

③DELETE型触发器：删除某一行时激活触发器，可能通过DELETE、REPLACE语句触发。

load data语句是将文件的内容插入到表中，相当于是insert语句，而replace语句在一般的情况下和insert差不多，但是如果表中存在primary 或者unique索引的时候，如果插入的数据和原来的primary key或者unique相同的时候，会删除原来的数据，然后增加一条新的数据，所以有的时候执行一条replace语句相当于执行了一条delete和insert语句。

 

2. 创建语法：

CREATE TRIGGER trigger_name tirgger_time trigger_event
ON tb_name FOR EACH ROW
BEGIN
    执行语句列表
END;(注意mysql分隔机制，参照存储过程)

只有一个执行语句时，begin和end可省略。

trigger_name：触发器名

trigger_time：触发时机，BEFFORE | AFTER

trigger_event：触发事件，INSERT | DELETE | UPDATE

tb_name：在哪张表上建立触发器

 

3. new与old

MySQL 中定义了 NEW 和 OLD，用来表示触发器的所在表中，触发了触发器的那一行数据，来引用触发器中发生变化的记录内容，具体地：

　　①在INSERT型触发器中，NEW用来表示将要（BEFORE）或已经（AFTER）插入的新数据；

　　②在UPDATE型触发器中，OLD用来表示将要或已经被修改的原数据，NEW用来表示将要或已经修改为的新数据；

　　③在DELETE型触发器中，OLD用来表示将要或已经被删除的原数据；

另外，OLD是只读的，而NEW则可以在触发器中使用 SET 赋值，这样不会再次触发触发器，造成循环调用（如每插入一个学生前，都在其学号前加“2018”）。

例：

mysql> delimiter ||
mysql> create trigger upd_check before update on account
    -> for each row
    -> begin
    ->  if new.amount < 0 then
    ->          set new.amount = 0;
    ->  elseif new.amount > 100 then
    ->          set new.amount = 100;
    ->  end if;
    -> end||
Query OK, 0 rows affected (0.04 sec)

mysql> delimiter ;
mysql> update account set amount=-10 where num=137;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+------+--------+
| num  | amount |
+------+--------+
|  137 |   0.00 |
|  141 | 192.50 |
|   23 | -23.00 |
+------+--------+

4. 查看触发器：

1、查询所有

mysql> SHOW TRIGGERS\G;

2：查询指定

mysql> select * from information_schema.triggers 
    -> where trigger_name='upd_check'\G;

5. 删除触发器：

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name