Mysql学习笔记_5

使用视图

视图是什么

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

视图的性能问题

因为视图不包含数据，所以每次使用视图时，都必须处理查询时所需的任意一个检索。若使用了多个联结和过滤创建了复杂的视图或嵌套了视图，可能性能会下降。所以在创建大量视图时，需要对性能进行测试。

视图的创建

create view productcustomers as select cust_name, cust_contact, prod_id from customers, orders, orderitems where customers.cust_id = orders.cust_id and orderitems.order_num = orders.order_num;

如上例所示，建立了一个名为productcustomers的视图，视图的检索条件为where子句后边的内容。视图的用法与表类似。

视图是可以被更新的（可用update，insert，delete）。更新视图将更新其基表（因为视图本身是没有数据的）。一般来说，将视图更广泛得应用于检索而不是更新表。

使用存储过程

创建存储过程

delimiter //

create procedure productpricing()
begin 
  select avg(prod_price) as priceaverage
end //

delimiter ;

由于Mysql本身用;作为分隔符，所以当创建的存储过程中有;时，会造成语法错误，所以使用delimiter // 将分隔符更改为//，当存储过程创建完成后，将默认的分隔符更改回来。

除了\，其他所有符号均可作为分隔符。

使用存储过程

call productpricing();

删除存储过程

drop procedure productpricing();

使用参数

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;

创建了拥有三个参数的存储过程，decimal(8,2)表示数据类型为十进制的8位精度，小数位数精度为2，

通过into 将检索结果导入参数。

调用含有参数的存储过程：

call productpricing(@pl,@ph,@pa);

由于创建的过程中含有三个参数，所以调用时，必须调用三个参数。该语句不返回任何结果，若想得到结果，需要使用select语句。

select @pl(,@ph,@pa);

使用in,out参数

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;

onumber 定义为in ， 因为订单号被传入存储过程。

调用及显示：

call ordertotal(20005, @total);
select @total;

显示订单号为20005的订单总价。该句等价于

select sum(item_price*quantity) from orderitems where order_num=20005;

不同的时，每查询一个订单号时，不用重复输入该语句，只需更改call ordertotal()中的参数即可。

创建智能存储过程

-- name: ordertotal
-- parameters: onumber = order number
--             taxable = 0 if not taxable,1if taxable
--             ototal  = order total variable
create procedure ordertotal(
in onumber int,
in taxable boolean,
out ototal decimal(8,2)
)comment 'obtain order total, optionally adding tax'
begin
declare total decimal(8,2);
declare taxrate int default 6;
select sum(item_price*quantity) from orderitems where order_num = onumber into total;
if taxable then
select total+(total/100*taxrate) into total;
end if;
select total into ototal;
end //

这是一个计算税率的存储过程。

首先是添加了注释（句首添加--）

其次是 declare关键字用于定义两个局部变量，用于方便计算。计算结果首先存储在total，如果不需要计税，则直接返回ototal，若需要计税，则再进行计算。

comment关键字将在显示存储过程中展示.

不需计税

call ordertotal(20005,0,@total);
select @total;

需要计税

call ordertotal(20005,1,@total);
select @total;

查询存储过程

show create procedure oredertotal;
show procedure stauts like 'ordertotal';

前者用于显示创捷存储过程，后者显示由谁在何时创建。