MySQL视图和存储过程
MySQL视图和存储过程
一. 视图
视图是一种特殊的表，但不包含表中应有的任何列和数据，只包含使用时动态检索数据的查询（即：sql查询语句）。
使用视图的目的，在本质上就是用来查看存储在别处的数据。
【引例】
/*使用sql查询*/
select cust_name,cust_contact from customers,orders,orderitems
where customers.cust_id=orders.cust_id
and orderitems.order_num=orders.order_num
and prod_id='TNT2';
- 查询结果
| cust_name | cust_contact | |
|---|---|---|
| 1 | Yosemite Place | Y Sam |
| 2 | Coyote Inc. | Y Lee |
/*创建视图*/
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;
/*查询结果*/
select cust_name,cust_contact from productcustomers where prod_id='TNT2';
- 查询结果
| cust_name | cust_contact | |
|---|---|---|
| 1 | Yosemite Place | Y Sam |
| 2 | Coyote Inc. | Y Lee |
从此引例中可以看出，所谓视图就是封装了一堆的sql查询语句
⭐【作用】
- 重用sql。
- 简化复杂的sql操作，封装后可以方便的使用视图，而不必知道它的基本查询细节。
- 保护数据，可以只授予表的特定部分的访问权限而不是整个表的访问权限。
⭐【规定】
- 名字唯一（不能与表名和其他视图名重名）
- 视图不能创建索引，也不能有关联的触发器
- 视图和表可以混着用
【案例一】用视图封装格式
/*
concat()函数
功能：将多个字符串连接成一个字符串。
语法：concat(str1, str2,...)
返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null。
RTRIM(str)
返回删除了后面空格字符的字符串str。
LTRIM(str)
返回删除了前面空格字符的字符串str
*/
select CONCAT(RTRIM(vend_name),'(',RTRIM(vend_country),')') AS vend_title
from vendors
order by vend_name;
- 查询结果
| vend_title | |
|---|---|
| 1 | ACME(USA) |
| 2 | Anvils R Us(USA) |
| 3 | Furball Inc.(USA) |
| 4 | Jet Set(England) |
| 5 | Jouets Et Ours(France) |
| 6 | LT Supplies(USA) |
/*创建视图*/
create view vendorlocation as
select CONCAT(RTRIM(vend_name),'(',RTRIM(vend_country),')') AS vend_title
from vendors
order by vend_name;
/*使用视图*/
select * from vendorlocation;
二. 存储过程
先考虑这么一个问题：当我们执行某个处理需要针对许多表的多条sql语句，语句执行的顺序也是不固定的，可能会随某些数据在表中存不存在而发生变化，这个时候怎么处理？
简单来说，存储过程就是为了方便以后使用而事先保存的sql语句集合。
【引例】
/*
创建存储过程
1. 如果需要参数，可以在（）中给出，即使没有参数，也要写（）。
2. 用 begin 和 end 来限制执行体，end要分号结尾。
*/
create procedure productprice()
begin
select AVG(prod_price) as priceAvg from products;
end;
/*调用存储过程*/
call productprice();
- 结果展示
| priceAvg | |
|---|---|
| 1 | 16.133571 |
🌞可以将存储过程理解成sql中的函数
【案例一】使用参数（传出）
/*
创建存储过程:
1. 此存储过程接收三个参数：pl、ph、pa
2. out:指出相应的参数是用来传出去的（返回给调用者）
3. in:调用者传递给存储过程的
4. inout：同时具备上述两者作用
*/
create procedure productprice2(
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;
/*
调用存储过程
1. @用来声明变量
2. @pricelow,@pricehigh,@priceavg在此处用来接收返回值
3. 该调用并不显示任何数据，只返回变量。
*/
call productprice2(@pricelow,@pricehigh,@priceavg);
/*查看变量*/
select @pricelow,@pricehigh,@priceavg;
- 结果展示
| @pricelow | @pricehigh | @priceavg | |
|---|---|---|---|
| 1 | 2.50 | 55.00 | 16.13 |
【案例二】（传入和传出）
/*创建存储过程*/
create procedure ordertotal(
in onnumber int,
out ototal decimal(9,2)
)
begin
select sum(item_price*quantity) from orderitems where order_num=onnumber into ototal;
end;
/*调用存储过程*/
call ordertotal(20009,@ototall);
/*查看变量*/
select @ototall;
- 结果展示
| @ototall |
|---|
| 149.87 |
【案例三】综合
/*
创建存储过程
判断逻辑：针对有些顾客要收营业税，有些则不
taxable为1则征收
taxable为0则不征收
*/
create procedure ordertotal(
in onnumber int,
in taxable boolean,
out ototal decimal(8,2)
)
begin
-- declare variable for total 声明一个总量（局部变量）
declare total decimal(8,2);
# declare tax percentage 交税的百分比
declare taxpercent int default 6;
-- 销售额=销售总量×单价
select sum(item_price*quantity) from orderitems
where order_num=onnumber into total;
-- 判断要不要交税；注意if格式
if taxable then
select total+(total/100*taxpercent) into total;
end if;
select total into ototal;
end;
/*不交税的调用过程*/
call ordertotal(20005,0,@total);
/*交税的调用过程*/
call ordertotal(20005,1,@total1);
/*展示结果*/
select @total,@total1;
- 结果展示
| @total | @total1 | |
|---|---|---|
| 1 | 149.87 | 158.86 |
