mysql必知必会（四）

一般最常用的是对表CRUD，但是视图、存储过程、触发器等也会用到。

1.视图（view）

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

• 重用SQL语句
• 简化复杂的sql操作
• 使用表的组成部分而不是整张表
• 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限
• 更改数据格式和表示
  创建视图

create view product_customers 
as
select prod_id,cust_name from product left join custumer on custumer.cust_id = product.cust_id ;

• create view 视图名
• as
• select查询语句

使用视图

select * from product_customers;

在视图中使用order by ，但是如果创建视图中的select语句中也含有order by ，那么select 中的将会被覆盖。
删除视图

drop view product_customers

一般只对视图进行查询，而不做insert 、update、delete,这样将会影响基表，并且效率比较低。如果视图定义中有以下操作，则不能对视图进行更新：

• 分组（使用group by 和having）
• 联接
• 子查询
• 并
• 聚合函数
• distinct

2.存储过程（ PROCEDURE）

创建存储过程：

delimiter//
create procedure productprice()
begin
select avg(prod_price) as avgprice from product;
end//
delimiter;

创建了productprice()存储过程。
调用时使用：

call productprice();

删除存储过程

drop procedure productprice;

注意：删除时后面没有（）。
带返回值的存储过程

delimiter//
create procedure productpricing(out pl float, out ph float)
begin
select min(prod_price) into pl from product;
select max(prod_price) into ph from product;
end//
delimiter;

 - 关键字out 指出相应的参数用来从存储过程传出一个值
 - pl、ph是变量名称
 - float是数据类型
 - into 将值赋给变量

调用待返回值的存储过程：

call  productpricing(@pricelow,@pricehigh);
select @pricelow,@pricehigh;

调用时以@开头。
带传入参数的存储过程

delimiter//
create procedure ordertotal(in ordernum int,out total float)
begin
select sum(item*quantity) into total from orderitems where order_num = ordernum;
end//
delimiter;

关键字in指给存储过程传值。
调用存储过程：

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

有选择流程的存储过程

delimiter//
create procedure ordertotaltax(in ordernum int,in tax boolean,out   total float)
begin
declare ordertotal float;
declare taxvalue float default 0.1;
select sum(item*quantity) into ordertotal from orderitems where order_num = ordernum;
if tax then
select ordertotal+(ordertotal*taxvalue) into ordertotal;
end if;
select ordertotal into total;
end//
delimiter;

declare 声明变量。
调用有选择流程的存储过程：

call ordertotaltax(20001,1,@total);
select @total;
call ordertotaltax(20001,0,@total);
select @total;

if语句可以进行多判断：

delimiter//
create procedure iftest(out testval int)
begin 
declare id int default 12;
if id = '13' then
set testval = 0;
elseif id<13 then
set testval = 1;
else 
set testval =2;
end if;
end//
delimiter;

也可以在存储过程中进行循环：

DELIMITER //
CREATE PROCEDURE whiletest()
BEGIN
DECLARE num  INT;
DECLARE total INT DEFAULT 0;
SET num = 0;
WHILE num < 10 DO
SET total = total + num;
SET num = num  + 1;
END WHILE;
SELECT total;
END//
DELIMITER ; 

3.触发器

触发器是mysql响应insert、update、delete语句时自动执行的一条sql语句，只有表支持触发器。
insert 触发器

create trigger tr_insert_tableA
after insert on tableA
for each row
insert into tableB（name,password）values(new.name,new.password);

引用new的虚拟表，访问被插入的行。
delete触发器

create trigger tr_delete_tableA
before delete on tableA
for each row 
delete from tableB where id = old.id;

引用一个old的虚拟表，访问被删除的行。
old表中的值全部是只读的，不能更新。
update触发器

create trigger tr_update_tableA
after update on tableA
for each row 
update tableB set name = new.name,password = new.password where id=new.id;