• MySql视图及存储过程


    一、视图

    视图是虚拟的表,它只包含使用时动态检索数据的查询。

    1、使用视图的好处

    1、重用SQL语句;

    2、简化复杂的SQL操作(可以方便的重用它而不必知道它的基本查询细节);

    3、使用表的组成部分而不是整个表;

    4、保护数据(可以给用户授予表的部分访问权限而不是整个表的访问权限);

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

    PS因为视图不包含数据,所以每次使用视图,都必须处理执行所需的任何一个检索;如果使用了多个联结和过滤创建了复杂的视图或嵌套视图,性能会下降比较明显。

    2、视图的规则和限制

    1、视图必须唯一命名(视图与别的视图或表不能有相同的名字);

    2、创建的视图数目没有限制;

    3、为了创建视图,必须有足够的访问权限;

    4、视图可以嵌套;

    5、order by可用于视图中;但如果该视图中检索数据的select语句包含order by,那么该视图中的order by将被覆盖;

    6、视图不能索引,也不能有关联的触发器或默认值;

    7、视图可以和表一起使用。

    3、使用视图

    create view:创建视图;

    show create view viemname:查看创建视图的语句;

    drop view viewname:删除视图;

    PS更新视图时,可以先用drop再用create,也可以直接使用create or replace view;如果要更新的视图不存在,则第二条更新语句会创建一个视图,如果视图存在,则第二条更新语句替换原有视图。

    1、简化复杂的联结

    视图最常见应用之一就是隐藏复杂的SQL,通常会涉及联结;例如:

    create view productcustomers AS
    select cust_name,cust_conact,prod_id
    from customers,orders,orderitems
    where customers.cust_id = order.cust_id
      and orderitems.order_num = order.order_num;

    这条语句创建一个名为productomers的视图,联结三个表,以返回已订购任意产品的所有客户的列表。如果执行select * from productcustomers,将列出订购了任意产品的客户。

    为了检索出订购了产品TEST的客户,可如下进行:

    select cust_name,cust_contact
    from productcustomers
    where prod_id = 'TEST';

    这条语句通过where子句从视图productcustomers中检索特定的数据。

    PS视图可以极大的简化复杂SQL语句的使用,利用视图,可一次性编写基础的SQL(不受特定数据限制的视图),然后根据需要多次使用。

    2、重新格式化检索出的数据

    视图的另一个常用功能就是重新格式化检索出的数据,比如:

    create view vendorlocations AS
    select concat(RTrim(vend_name),'(',RTrim(vend_country),')')
        AS vend_title
    from vendors
    order by vend_name;

    这条SQL语句使用select语句创建视图,在单个组合计算列中返回供应商名和位置,以后每次需要时使用这个视图即可。

    3、使用视图过滤不想要的数据

    视图对于普通的where子句也很有用,例如:

    create view customeremaillist AS
    select cust_id,cust_name,cust_email
    from customers
    where cust_email is not NULL;

    这条SQL语句定义了customeremaillist视图,它过滤没有电子邮件地址的客户。

    PS如果从视图检索数据时使用一条where子句,则两组子句(一组在视图中,另一组是传递给视图的)将自动组合。

    4、视图与计算字段

    视图对于简化计算字段来说,也很有用,例如:

    create view orderitemsexpanded AS
    select order_num,prod_id,quantity,item_price,
    quantity*item_price AS expanded_price
    from orderitems;

    这条语句创建了orderitemsexpanded视图,它检索某个特定订单的物品以及每种物品的总价格,如果需要使用该视图,只需要执行一条select语句即可,比如:

    select * from orderitemsexpanded where order_num = 10086;

    5、更新视图

    通常来讲,视图是可更新的,更新一个视图即更新其基表(如果MySQL不能正确的确定被更新的基数据,则不允许更新,包括插入和删除);即视图如果定义如下操作,则不可更新:

    1、分组(使用group by和having);2、联结;3、子查询;4、并;5、聚集函数(min()、count()、sum()等);6、distinct;7、导出列。

    二、存储过程

    定义:为方便以后使用而保存的一条或多条MySQL语句的集合。可将其视为批文件,虽然其作用不仅限于批处理。

    1、为什么要使用存储过程(简单、安全、高性能)

    1、通过把处理封装在容易使用的单元中,简化复杂的操作;

    2、不要求反复建立一系列处理步骤,保证了数据的完整性;

    3、简化对变动的管理(如果表名、列名或业务逻辑变更,只需要更改存储过程的代码),这一点的延伸就是安全性;

    4、提高性能(使用存储过程比使用单独的SQL语句要快);

    2、使用存储过程

    1、执行存储过程

    MySQL将存储过程的执行称为调用,执行的语句用call,call接受存储过程的名字以及传递给它的任意参数;例如下面的例子:

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

    这条SQL语句中,执行名为productpricing的存储过程,并计算返回产品的最低、最高和平均价格(存储过程可以显示结果,也可以不显示)。

    2、创建存储过程

    下面创建一个返回产品平均价格的存储过程,例子如下:

    create procedure productpricing()
    begin
           select avg(prod_price) as priceaverage
           from products;
    end;

    此存储过程名为productpricing,用create procedure productpricing()语句定义;如果存储过程接受参数,它将在()中列举出来,此存储过程没有参数,但后跟的()仍然需要。

    begin和end语句用来限定存储过程体,过程本身仅是一个select语句以及avg()函数。

    存储过程在创建之后,被保存在服务器上以供使用,直至被删除。

    注意事项:默认的MySQL语句分隔符为;。如果命令行实用程序要解释存储过程自身内的;字符,则他们不会成为存储过程的部分,这样会使存储过程中的SQL出现语法错误;解决办法如下:

    delimiter//
    create procedure productpricing()
    begin
           select avg(prod_price) as priceaverage
           from products;
    end//
    delimiter;

    其中,delimiter//告诉命令行实用程序使用//作为新的语句结束分隔符;如果要恢复原来的分隔符,可使用delimiter;。除/之外,任何字符都可以用作语句分隔符。

    3、删除存储过程

    drop procedure productpricing;

    删除刚才创建的存储过程;注意,后面没有使用(),只给出存储过程名。

    PS如果指定的存储过程不存在,则drop procedure将产生一个错误。当存储过程存在,想删除他时,可以使用drop procedure if exists,如果过程不存在也不产生错误.

    4、使用参数

    一般来讲,存储过程不显示结果,而是把结果返回给指定的变量(变量内的一个特定的位置,用来临时存储数据)。

    下面是上面的存储过程的修改版本:

    create procedure productpricing(
           out p1 decimal(8,2),
           out ph decimal(8,2),
    )
    begin
           select min(pro_price) into p1
           from products;
           select max(prod_price) into ph
           from products;
    end;

    此存储过程中接受3个参数,关键字out指出相应的参数用来从存储过程传出一个值(返回给调用者);存储过程中检索出的值,通过into关键字保存到相应的变量中,

    PSMySQL支持in(传递给存储过程)、out(从存储过程传出)和inout(对存储过程传入和传出)类型的参数。

    调用上面修改过的存储过程,必须指定3个变量名,如下:

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

    PS所有存储过程的变量都必须以“@”开始。

    上面的调用语句并不显示数据,它返回以后可以显示的变量;为了显示检索出的价格,使用下面的语句:

    select @pricelow, @pricehigh, @priceaverage;

    5、检查存储过程

    显示创建存储过程的create语句,使用show create procedure语句,比如:

    show create procedure ordertotal;

    如果想获得详细的关于存储过程的信息,如创建时间、创建人等信息,使用show procedure status。

    PSshow procedure status列出所有存储过程,为限制其输出,可使用LIKE指定一个过滤模式,例如:show procedure status like 'ordertotal';

  • 相关阅读:
    List<int>转化为逗号链接的字符串
    分页的总页数算法
    高性能SQLServer分页语句
    webconfig中的&符号问题解决
    检测SQLServer复制订阅进度
    jQuery自定义数组操作类(类似于List集合的增删改查)
    解决jquery绑定click事件出现点击一次执行两次问题
    IdentityServer4登陆中心
    AES加密解密通用版Object-C / C# / JAVA
    T-SQL 基本语法
  • 原文地址:https://www.cnblogs.com/mind18/p/14332813.html
Copyright © 2020-2023  润新知