关系数据库——视图/存储过程/触发器

视图

视图是虚拟的表，与包含数据的表不同，视图只包含使用时动态检索数据的查询,主要是用于查询。

为什么使用视图

1. 重用sql语句
2. 简化复杂的sql操作，在编写查询后，可以方便地重用它而不必知道他的基本查询细节。
3. 使用表的组成部分而不是整个表。
4. 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
5. 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

注意：

• 在视图创建之后，可以用与表基本相同的方式利用它们。可以对视图执行select操作，过滤和排序数据，将视图联结到其他视图或表，甚至能添加和更新数据。
• 重要的是知道视图仅仅是用来查看存储在别处的数据的一种设施。视图本身不包含数据，因此它们返回的数据时从其他表中检索出来的。在添加和更改这些表中的数据时，视图将返回改变过的数据。
• 因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任一检索。如果你使用多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能下降得很厉害。因此，在部署使用了大量视图的应用前，应该进行测试。

视图的规则和限制

1. 与表一样，视图必须唯一命名；
2. 可以创建任意多的视图；
3. 为了创建视图，必须具有足够的访问权限。这些限制通常由数据库管理人员授予。
4. 视图可以嵌套，可以利用从其他视图中检索数据的查询来构造一个视图。
5. Order by 可以在视图中使用，但如果从该视图检索数据select中也是含有order by，那么该视图的order by 将被覆盖。
6. 视图不能索引，也不能有关联的触发器或默认值
7. 视图可以和表一起使用

视图的创建

1. 利用create view 语句来进行创建视图
2. 使用show create view viewname；来查看创建视图的语句
3. 用drop view viewname 来删除视图
4. 更新视图可以先drop在create，也可以使用create or replace view。

视图的更新

视图是否可以更新，要视情况而定。

通常情况下视图是可以更新的，可以对他们进行insert，update和delete。更新视图就是更新其基表(视图本身没有数据)。如果你对视图进行增加或者删除行，实际上就是对基表进行增加或者删除行。

但是，如果MySQL不能正确的确定更新的基表数据，则不允许更新(包括插入和删除)，这就意味着视图中如果存在以下操作则不能对视图进行更新：(1)分组(使用group by 和 having )；(2)联结；(3)子查询；(4)并；(5)聚集函数;(6)dictinct;(7)导出(计算)列。

 

存储过程

存储过程就是为了以后的使用而保存的一条或者多条MySQL语句的集合。可将视为批文件，虽然他们的作用不仅限于批处理。

为什么使用储存过程？

1.通过把处理封装在容易使用的单元中，简化复杂的操作；

 

2.由于不要求反复建立一系列处理步骤，保证了数据的完整性。如果所有开发人员和应用程序都使用同一(实验和测试)存储过程，则所使用的代码都是相同的。这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大，防止错误保证了数据的一致性。

 

3.简化对变动的管理，如果表名。列名或者业务逻辑等有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。这一点延伸就是安全性，通过存储过程限制对基数据的访问减少了数据讹误的机会。

 

4.提高性能。因为使用存储过程比使用单独的sql语句更快。

 

5.存在一些只能用在单个请求的MySQL元素和特性，存储过程可以使用他们来编写功能更强更灵活的代码

 

综上：

三个主要的好处：简单、安全、高性能。

两个缺陷：

1、存储过程的编写更为复杂，需要更高的技能更丰富的经验。

2、可能没有创建存储过程的安全访问权限。许多数据库管理员限制存储过程的 创建权限，允许使用，不允许创建。

执行存储过程

Call关键字：Call接受存储过程的名字以及需要传递给他的任意参数。存储过程可以显示结果，也可以不显示结果。

CREATE PROCEDURE productpricing()

    BEGIN

        SELECT  AVG( prod_price)  as priceaverage FROM products;

    END;

创建名为productpricing的储存过程。如果存储过程中需要传递参数，则将他们在括号中列举出来即可。括号必须有。BEGIN和END关键字用来限制存储过程体。上述存储过程体本身是一个简单的select语句。注意这里只是创建存储过程并没有进行调用。

 

储存过程的使用：

 

Call productpring()；

 

使用参数的存储过程

一般存储过程并不显示结果，而是把结果返回给你指定的变量上。

变量：内存中一个特定的位置，用来临时存储数据。

MySQL> CREATE PROCEDURE prod(

     out pl decimal(8,2),

     out ph decimal(8,2),

     out pa decimal(8,2)

     )
begin

     select Min(prod_price) into pl from products;

     select MAx(prod_price) into ph from products;

     select avg(prod_price) into pa from products;

 end;



call PROCEDURE(@pricelow,@pricehigh,@priceaverage);

select @pricelow;

select @pricehigh;

select @pricelow,@pricehigh,@priceaverage;

 

解释：

此存储过程接受3个参数，pl存储产品最低价，ph存储产品最高价，pa存储产品平均价。每个参数必须指定类型，使用的为十进制，关键字OUT 指出相应的参数用来从存储过程传出一个值(返回给调用者)。

 

MySQL支持in(传递给存储过程)、out(从存储过程传出，这里所用)和inout(对存储过程传入和传出)类型的参数。存储过程的代码位于begin和end语句内。他们是一系列select语句，用来检索值。然后保存到相对应的变量(通过INTO关键字)。

存储过程的参数允许的数据类型与表中使用的类型相同。注意记录集是不被允许的类型，因此，不能通过一个参数返回多个行和列，这也是上面为什么要使用3个参数和3条select语句的原因。

 

调用：为调用此存储过程，必须指定3个变量名。如上所示。3个参数是存储过程保存结果的3个变量的名字。调用时，语句并不显示任何数据，它返回以后可以显示的变量(或在其他处理中使用)。

 

注意：所有的MySQL变量都是以@开头。

CREATE PROCEDURE ordertotal(

     IN innumber int,

     OUT outtotal decimal(8,2)

     )

     BEGIN

     SELECT Sum(item_price * quantity) FROM orderitems WHERE order_num = innumber INTO outtotal;

     end    //



CALL ordertotal(20005,@total);

select @total;  // 得到20005订单的合计



CALL ordertotal(20009,@total);

select @total; //得到20009订单的合计

 

带有控制语句的存储过程

 ​
CREATE PROCEDURE ordertotal(

 IN onumber INT,

 IN taxable BOOLEAN,

 OUT ototal DECIMAL(8,2)

 )COMMENT 'Obtain order total, optionally adding tax'

BEGIN

  -- declear variable for total

  DECLARE total DECIMAL(8,2);

  -- declear tax percentage

  DECLARE taxrate INT DEFAULT 6;

  -- get the order total

    SELECT Sum(item_price * quantity) FROM orderitems WHERE order_num = onumber INTO total;

-- IS this taxable?

IF taxable THEN

   -- yes ,so add taxrate to the total

   SELECT total+(total/100*taxrate)INTO total;

   END IF;

 -- finally ,save to out variable

 SELECT total INTO ototal;

END;

在存储过程中我们使用了DECLARE语句，他们表示定义两个局部变量，DECLARE要求指定变量名和数据类型。它也支持可选的默认值(taxrate默认6%)，因为后期我们还要判断要不要增加税，所以，我们把SELECT查询的结果存储到局部变量total中，然后在IF 和THEN的配合下，检查taxable是否为真，然后在真的情况下，我们利用另一条SELECT语句增加营业税到局部变量total中，然后我们再利用SELECT语句将total(增加税或者不增加税的结果)保存到总的ototal中。

COMMENT关键字 上面的COMMENT是可以给出或者不给出，如果给出，将在SHOW PROCEDURE STATUS的结果中显示。

 

触发器

在某个表发生更改时自动处理某些语句，这就是触发器。

 

触发器是MySQL响应delete 、update 、insert 、位于begin 和end语句之间的一组语句而自动执行的一条MySQL语句。其他的语句不支持触发器。

创建触发器

在创建触发器时，需要给出4条语句（规则）：

1.  唯一的触发器名；

2.  触发器关联的表；

3.  触发器应该响应的活动；

4.  触发器何时执行(处理之前或者之后)

 

Create trigger 语句创建 触发器

CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added' INTO @info;

CREATE TRIGGER用来创建名为newproduct的新触发器。触发器可以在一个操作发生前或者发生后执行，这里AFTER INSERT 是指此触发器在INSERT语句成功执行后执行。这个触发器还指定FOR EACH ROW ， 因此代码对每个插入行都会执行。文本Product added 将对每个插入的行显示一次。

 

注意：

1、触发器只有表才支持，视图，临时表都不支持触发器。

2、触发器是按照每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器，因此，每个表最多支持六个触发器(insert，update，delete的before 和after)。

3、单一触发器不能与多个事件或多个表关联，所以，你需要一个对insert和update 操作执行的触发器，则应该定义两个触发器。

4、触发器失败：如果before 触发器失败，则MySQL将不执行请求的操作，此外，如果before触发器或者语句本身失败，MySQL则将不执行after触发器。

触发器类别

INSERT触发器

是在insert语句执行之前或者执行之后被执行的触发器。

1、在insert触发器代码中，可引入一个名为new的虚拟表，访问被插入的行；

2、在before insert触发器中，new中的值也可以被更新(允许更改被插入的值)；

3、对于auto_increment列，new在insert执行之前包含0，在insert执行之后包含新的自动生成值

CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num;

创建一个名为neworder的触发器，按照AFTER INSERT ON orders 执行。在插入一个新订单到orders表时，MySQL生成一个新的订单号并保存到order_num中。触发器从NEW.order_num取得这个值并返回它。此触发器必须按照AFTER INSERT执行，因为在BEFORE INSERT语句执行之前，新order_num还没有生成。对于orders的每次插入使用这个触发器总是返回新的订单号。

DELETE触发器

Delete触发器在delete语句执行之前或者之后执行。

1、在delete触发器的代码内,可以引用一个名为OLD的虚拟表，用来访问被删除的行。

2、OLD中的值全为只读，不能更新。

CREATE TRIGGER deleteorder BEFORE DELETE ON orders FOR EACH ROW

BEGIN

INSERT INTO archive_orders(order_num,order_date,cust_id) values (OLD.order_num,OLD.order_date,OLD.cust_id);

END;



----------------------------------------------------------------



CREATE TABLE archive_orders(

  order_num int(11) NOT NULL AUTO_INCREMENT,

  order_date datetime NOT NULL,

  cust_id int(11) NOT NULL,

  PRIMARY KEY (order_num),

  KEY fk_orders1_customers1 (cust_id),

  CONSTRAINT fk_orders1_customers1 FOREIGN KEY (cust_id) REFERENCES customers

 (cust_id)

) ENGINE=InnoDB AUTO_INCREMENT=20011 DEFAULT CHARSET=utf8

在任意订单被删除前将执行此触发器，它使用一条INSERT 语句将OLD中的值(要被删除的订单) 保存到一个名为archive_orders的存档表中(为实际使用这个例子，我们需要用与orders相同的列创建一个名为archive_orders的表)

 

使用BEFORE DELETE触发器的优点(相对于AFTER DELETE触发器来说)为，如果由于某种原因，订单不能存档，delete本身将被放弃。

 

我们在这个触发器使用了BEGIN和END语句标记触发器体。这在此例子中并不是必须的，只是为了说明使用BEGIN END 块的好处是触发器能够容纳多条SQL 语句(在BEGIN END块中一条挨着一条)。

UPDATE触发器

在update语句执行之前或者之后执行

1、在update触发器的代码内,可以引用一个名为OLD的虚拟表，用来访问以前(UPDATE语句之前)的值，引用一个名为NEW的虚拟表访问新更新的值。

2、在BEFORE UPDATE触发器中，NEW中的值可能也被用于更新(允许更改将要用于UPDATE语句中的值)

3、OLD中的值全为只读，不能更新。

CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors FOR EACH ROW SET NEW.vend_state = Upper(NEW.vemd_state);

保证州名缩写总是大写(不管UPFATE语句中是否给出了大写)，每次更新一行时，NEW.vend_state中的值(将用来更新表行的值)都用Upper(NEW.vend_state)替换。

总结

1、通常before用于数据的验证和净化(为了保证插入表中的数据确实是需要的数据) 也适用于update触发器。

2、与其他DBMS相比，MySQL 5中支持的触发器相当初级，未来的MySQL版本中估计会存在一些改进和增强触发器的支持。

3、创建触发器可能需要特殊的安全访问权限，但是触发器的执行时自动的，如果insert，update，或者delete语句能够执行，则相关的触发器也能执行。

4、用触发器来保证数据的一致性(大小写，格式等)。在触发器中执行这种类型的处理的优点就是它总是进行这种处理，而且透明的进行，与客户机应用无关。

5、触发器的一种非常有意义的使用就是创建审计跟踪。使用触发器，把更改(如果需要，甚至还有之前和之后的状态)记录到另外一个表是非常容易的。

6、MySQL触发器不支持call语句，无法从触发器内调用存储过程。