MySQL（十一）视图及存储过程
一、视图
视图是虚拟的表，它只包含使用时动态检索数据的查询。
1、使用视图的好处
①重用SQL语句；
②简化复杂的SQL操作（可以方便的重用它而不必知道它的基本查询细节）；
③使用表的组成部分而不是整个表；
④保护数据（可以给用户授予表的部分访问权限而不是整个表的访问权限）；
⑤更改数据格式和表示（视图可返回与底层表的表示和格式不同的数据）。
PS：因为视图不包含数据，所以每次使用视图，都必须处理执行所需的任何一个检索；如果使用了多个联结和过滤创建了复杂的视图或嵌套视图，性能下降比较明显。
2、视图的规则和限制
①视图必须唯一命名（视图与别的视图或表不能有相同的名字）；
②创建的视图数目没有限制；
③为了创建视图，必须有足够的访问权限；
④视图可以嵌套；
⑤order by也可用于视图；但如果该视图中检索数据的select语句包含order by，那么该视图中的order by将被覆盖；
⑥视图不能索引，也不能有关联的触发器或默认值；
⑦视图可以好表一起使用。
3、使用视图
create view：创建视图；
show create view viemname：查看创建视图的语句；
drop view viewname:删除视图；
PS：更新视图时，可以先用drop再用create，也可以直接使用create or replace view；如果要更新的视图不存在，则第二条更新语句会创建一个视图，如果视图存在，则第二条更新语句替换原有视图。
3.1简化复杂的联结
视图最常见应用之一就是隐藏复杂的SQL，通常会设计联结；例如：
create view productcustomers AS
select cust_name,cust_conact,prod_id
from customers,orders,orderitems
where customers.cust_id = order.cust_id
and orderitems.order_num = order.order_num;
这条语句创建一个名为productomers的视图，联结三个表，以返回已订购任意产品的所有客户的列表。如果执行select * from productcustomers，将列出订购了任意产品的客户。
为了检索出订购了产品TEST的客户，可如下进行：
select cust_name,cust_contact
from productcustomers
where prod_id = 'TEST';
这条语句通过where子句从视图productcustomers中检索特定的数据。
PS：视图可以极大的简化复杂SQL语句的使用，利用视图，可一次性编写基础的SQL，然后根据需要多次使用。
3.2重新格式化检索出的数据
视图的另一个常用功能就是重新格式化检索出的数据，比如：
create view vendorlocations AS
select concat(RTrim(vend_name),'(',RTrim(vend_country),')')
AS vend_title
from vendors
order by vend_name;
这条SQL语句使用select语句创建视图，在单个组合计算列中返回供应商名和位置，以后每次需要时使用这个视图即可。
3.3使用视图过滤不想要的数据
视图对于普通的where子句也很有用，例如：
create view customeremaillist AS
select cust_id,cust_name,cust_email
from customers
where cust_email is not NULL;
这条SQL语句定义了customeremaillist视图，它过滤没有电子邮件地址的客户。
PS：如果从视图检索数据时使用一条where子句，则两组子句（一组在视图中，另一组是传递给视图的）将自动组合。
3.4视图与计算字段
视图对于简化计算字段来说，也很有用，例如：
create view orderitemsexpanded AS
select order_num,prod_id,quantity,item_price,
quantity*item_price AS expanded_price
from orderitems;
这条语句创建了orderitemsexpanded视图，它检索某个特定订单的物品以及每种物品的总价格，如果需要使用该视图，只需要执行一条select语句即可，比如：
select * from orderitemsexpanded where order_num = 10086;
3.5更新视图
通常来讲，视图是可更新的，更新一个视图即更新其基表（如果MySQL不能正确的确定被更新的基数据，则不允许更新（包括插入和删除））；即视图如果定义如下操作，则不可更新：
①分组（使用group by和having）；②联结；③子查询；④并；⑤聚集函数（min()、count()、sum()等）；⑥distinct；⑦导出列。
二、存储过程
定义：为方便以后使用而保存的一条或多条MySQL语句的集合，可将其视为批文件（虽然其作用不仅限于批处理）。
1、为什么要使用存储过程（简单、安全、高性能）
①通过把处理封装在容易使用的单元中，简化复杂的操作；
②不要求建立一些列处理步骤，保证了数据的完整性；
③简化对变动的管理（如果表名、列名或业务逻辑变更，只需要更改存储过程的代码），这一点的延伸就是安全性；
④提高性能（使用存储过程比使用单独的SQL语句要快）；
2、使用存储过程
①执行存储过程
MySQL中一般将存储过程的执行称为调用，执行的语句用call，call接受存储过程的名字以及传递给它的任意参数；例如下面的例子：
call productpricing(@pricelow,
@pricehigh,
@priceaverage);
这条SQL语句中，执行名为productpricing的存储过程，并计算返回产品的最低、最高和平均价格（存储过程可以显示结果，也可以不显示）。
②创建存储过程
下面创建一个返回产品平均价格的存储过程，例子如下：
create procedure productpricing()
begin
select avg(prod_price) as priceaverage
from products;
end;
此存储过程名为productpricing，用create procedure productpricing()语句定义；如果存储过程接受参数，它将在（）中列举出来，此存储过程没有参数，但后跟的（）仍然需要。
begin和end语句用来限定存储过程体，过程本身仅是一个select语句以及avg（）函数。
注意事项：MySQL命令行客户机的分隔符
默认的MySQL语句分隔符为；。如果命令行实用程序要解释存储过程自身内的；字符，则他们不会成为存储过程的部分，这样会使存储过程中的SQL出现语法错误；解决办法如下：
delimiter//
create procedure productpricing()
begin
select avg(prod_price) as priceaverage
from products;
end//
delimiter;
其中，delimiter//告诉命令行实用程序使用//作为新的语句结束分隔符；如果要恢复原来的分隔符，可使用delimiter（除/之外，任何字符都可以用作语句分隔符）;
③删除存储过程
比如：drop procedure productpricing;
这条语句删除刚才创建的存储过程；注意，后面没有使用（），只给出存储过程名。
PS：如果指定的存储过程不存在，则drop procedure将产生一个错误；当存储过程存在，想删除他时，可以使用drop procedure if exists.
④使用参数
一般来讲，存储过程不显示结果，而是把结果返回给指定的变量（变量内的一个特定的位置，用来临时存储数据）。
下面是上面的存储过程的修改版本：
create procedure productpricing(
out p1 decimal(8,2),
out ph decimal(8,2),
out pa decimal(8,2)
)
begin
select min(pro_price)
into p1
from products;
select max(prod_price)
into ph
from products;
select avg(prod_price)
into pa
from products;
end;
此存储过程中接受3个参数：p1存储产品最低价格，ph存储产品最高价格，pa存储产品平均价格；关键字out指出相应的参数用来从存储过程传出一个值（返回给调用者）；
PS：MySQL支持in（传递给存储过程）、out（从存储过程传出）和inout（对存储过程传入和传出）类型的参数。
为了调用上面修改过的存储过程，必须指定3个变量名，如下：
call productpricing(@pricelow,
@pricehigh,
@priceaverage);
PS：所有存储过程的变量都必须以“@”开始。
调用时候，上面的调用语句并不现实结果，它返回以后可以显示的变量；为了显示检索出的价格，使用下面的语句：
select @pricelow, @pricehigh, @priceaverage;
⑤检查存储过程
为了显示创建存储过程的create语句，使用show create procedure语句，比如：
show create procedure ordertotal;
如果想获得详细的关于存储过程的信息，比如创建时间、创建人等信息，使用show procedure status。
PS:show procedure status列出所有存储过程，为限制其输出，可使用LIKE指定一个过滤模式，例如：
show procedure status like 'ordertotal';
