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

柔情只为你懂 2023-06-18 05:52 15阅读 0赞

## 视图 ##

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

### 为什么使用视图 ###

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触发器。