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

MySQL视图和存储过程

上一篇:Java配置环境——eclipse+jdk1.8+Mysql


下一篇:mysql 语法 数值 字段