MySQL学习（九）

使用视图

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。

视图的作用：

重用SQL语句。

    简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节。

    使用表的组成部分而不是整个表。

    保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。

    更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

视图用Create View来创建，用Drop View删除，更新视图时，可以用Create View OR Replace View

1.利用视图简化复杂联结

create view PRoductcustomers ASselect cust_name,cust_contact,prod_idfrom customers,orders,orderitemswhere customers.cust_id = orders.cust_idAND orderitems.order_num = orders.order_num;首先创建一个名为productcustomers的视图
select cust_name,cust_contactfrom productcustomerswhere prod_id = 'TNT2';之后就可以利用视图检索数据
2.用视图重新格式化检索出的数据
create view vendorlocations ASselect concat(rtrim(vend_name),'(',rtrim(vend_country),')')	   AS vend_titlefrom vendorsorder by vend_name; 创建一个名为vendorlocations的视图，里面格式化了数据
select*from vendorlocations;之后就可以方便查看此格式的数据
3.用视图过滤不想要的数据
create view customeremaillist ASselect cust_id, cust_name,cust_emailfrom customerswhere cust_email is not null;创建一个名为vcustomeremaillist的视图，里面过滤掉了一些数据
select *from customeremaillist;之后可以直接查询
4.使用视图计算字段
create view orderitemsexpanded ASselect order_num,	   prod_id,       quantity,       item_price,       quantity*item_price AS expanded_pricefrom orderitems;创建一个名为orderitemsexpanded的视图
select *from orderitemsexpandedwhere order_num = 20005;使用存储过程
存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。
创建存储过程：Create Procedure
使用存储过程：Call
删除存储过程：Drop Procedure
1.创建存储过程
DELIMITER //create procedure productpricing()begin      select AVg(prod_price) AS priceaverage      from products;end//DELIMITER ;这里需要使用 DELIMITER // 临时更改命令行实用程序的语句分隔符为//，这样，存储过程体内的;仍然保持不动
最后使用DELIMITER ; 恢复分隔符
调用存储过程
call productpricing()；2.使用参数
DELIMITER //create procedure productpricing(	   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//DELIMITER ;关键字Out支出相应的参数用来从存储过程中传出一个值，
MySQL还支持In（传递给存储过程），InOut（对存储过程传入和传出）
关键字Into用来将值保存到相应的变量
调用此存储过程，必须指定三个变量名
call productpricing(@pricelow,					@pricehigh,                    @priceavgerage);所有变量名必须以@开始，这条语句不显示任何数据，它返回以后可以显示的变量
select @priceaverage;
用于显示具体的数据
使用In，Out参数
DELIMITER //create procedure ordertotal(       In onumber int,       Out ototal decimal(8,2)       )begin     select Sum(item_price*quantity)     from orderitems     where order_num = onumber     into ototal;end//DELIMITER ;
call ordertotal(20005,@total);select @total;
建立智能存储过程
DELIMITER //create procedure ordertotal(       In onumber int,       In taxable boolean,       Out ototal decimal(8,2)       ) comment 'Obtain order total,adding tax'begin       declare total decimal(8,2);      declare taxrate int default 6;select Sum(item_price*quantity)from orderitemswhere order_num = onumberinto total;if taxable then   select total+(total/100*taxrate) into total;end if;select total into ototal;end //DELIMITER ;用DECLARE语句定义了两个局部变量。DECLARE要求指定变量名和数据类型，它也支持可选的默认值
本例子中中包含了一个COMMENT值。它不是必需的，但如果给出，将在SHOW PROCEDURE STATUS的结果中显示。
call ordertotal(20005,0,@total);select @total;call ordertotal(20005,1,@totall);select @totall;
使用游标
游标 （cursor） 是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集
(相当于一个指向检索出来的行的指针把。。。)
使用游标的步骤：
    在能够使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的SELECT语句
    一旦声明后，必须打开游标以供使用。这个过程用前面定义的SELECT语句把数据实际检索出来
    对于填有数据的游标，根据需要取出（检索）各行
    在结束游标使用时，必须关闭游标
1.创建游标：Declare ... Cursor
2.开打和关闭游标：Open Cursor  Close Cursor 
如果你不明确关闭游标，MySQL将会在到达END语句时自动关闭它。
3.使用游标数据：    在一个游标被打开后，可以使用FETCH语句分别访问它的每一行。
    FETCH指定检索什么数据（所需的列），检索出来的数据存储在什么地方。
    它还向前移动游标中的内部行指针，使下一条FETCH语句检索下一行。
DELIMITER //create procedure processorders()begin      declare done boolean default 0;      declare o int;      declare t decimal(8,2);            declare ordernumbers cursor for      select order_num       from orders;            declare continue handler for sqlstate '02000' set done = 1;      create table if not exists ordertotals           (order_num int, total decimal(8,2));                 open ordernumbers;      repeat          fetch ordernumbers into o;          call ordertotal(o,1,t);          insert into ordertotals(order_num,total)          values(o,t);	  until done end repeat;            close ordernumbers;end //DELIMITER ;FETCH是在REPEAT内，因此它反复执行直到done为真（由UNTILdone END REPEAT;规定）。为使它起作用，用一个DEFAULT 0（假，不结束）定义变量done。
done怎样才能在结束时被设置为真呢？答案是用以下语句：Declare Continue Handler for sqlstate '02000' set done = 1；
这条语句定义了一个CONTINUE HANDLER，它是在条件出现时被执行的代码。这里， 它指出当SQLSTATE '02000'出现时，SET done=1。SQLSTATE'02000'是一个未找到条件， 当REPEAT由于没有更多的行供循环而不能继续时，出现这个条件。