《MySQL必知必会》——视图、存储过程

视图

视图是虚拟的表，只包含使用时动态检索数据的查询。
视图常见的应用：

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

在视图创建后，可以用与表基本相同的方式利用它们，可以对视图进行SELECT、过滤和排序操作，将视图联结到其他视图和表中，甚至能添加和更新数据。

但是视图仅仅是用来查看存储在别处的数据的一种设施，视图本身不包含数据，它们返回的数据都是从其他表中检索出来的，在添加或更改这些表中的数据时，视图将返回改变过的数据。

创建视图：CREATE VIEW语句
查看视图：SHOW CREAE VIEW viewname
删除视图：DROP VIEW viewname
更新视图：CREATE ORREPLACE VIEW或先用drop 再用create

CREATE VIEW product AS 
SELECT name , id 
FROM order  WHERE id='1001';

创建了一个product的视图后，可以用过select语句从视图products中检索特定数据。

SELECT name , id FROM products WHERE id = '1001'; 

使用视图的好处：

• 利用视图简化复杂的联结
• 用视图重新格式化检索出的数据
• 用视图过滤不想要的数据

更新视图：通常，视图是可更新的(可以对它们使用INSERT、UPDATE和DELETE)，更新一个视图将更新其基表，如果你对视图增加或删除行，实际上是对其基表增加或删除行。

如果视图有以下操作，则不能进行视图的更新：

• 分组(GROUP BY和HAVING)
• 联结
• 子查询
• 并
• 聚集函数
• DISTINCT
• 导出列

使用存储过程

存储过程就是为以后的使用而保存的一条或多条MySQL语句的集合。
使用存储过程的好处：

• 通过把处理封装在容易使用的单元中，简化复杂的操作
• 由于不要求反复建立一系列处理步骤，保证了数据的完整性
• 简化对变动的管理。如果表名、列名、或业务逻辑有变化，只需要更改存储过程的代码。
• 通过存储过程限制对基础数据的访问减少了数据错误的机会
• 提高性能，使用存储过程必使用单独的sql语句要快
• 存在一些只能用单个请求中MySQL元素和特性，存储过程可以使用它们来编写功能更强大更灵活的代码。

创建存储过程：

 CREATE PROCEDURE productpricing()
 BEGIN
        SELECT  avg(price)  AS priceaverge 
        FROM products;
 END;

调用存储过程：

CALL productpricing();

删除存储过程：

DROP PROCEDURE productpricing;

如果指定的过程不存在，则DROP PROCEDURE将产生一个错误。

使用有参数的存储过程

CREATE PROCEDURE productpricing(
OUT p1 DECIMAL(8,2),
OUT p2 DECIMAL(8,2),
OUT p3 DECIMAL(8,2)
)
BEGIN 
SELECT Min(price) INTO p1 FROM. products;
SELECT Max(price) INTO P2 FROM products;
SELECT Avg(price) INTO p3 FROM products;
END;

关键字OUT指出相应的参数用来从存储过程传出一个值返回给调用者，

 CALL  productpricing(@pricelow,@pricehigh,@priceaverage);

MySQL所有变量都必须以@开始

SLECT @priceaverage;

使用IN和OUT参数：

 CREATE PROCEDURE  ordertotal(
  IN number int,
  OUT total DECIMAL(2,4)
)
BEGIN
   SELECT Sum(price* quantity) FROM orderitems WHERE order_num=onumber INTO ototal;
   END;

IN是要被传入存储过程的参数，OUT是要从存储过程中返回的。

CALL ordertotal(10007,@total);

SELECT @total;

检查存储过程：

SHOW CREATE PROCEDURE ordertotal;