数据库实际查询操作回顾（八）

回顾之前学习数据库的相关操作，复习时顺便记录下，以便以后自己可以再次查看！！！

/*

使用视图

*/

/*

 视图的规则和限制 ：
 1.与表一样，视图必须唯一命名（不能给视图取与别的视图或表相 同的名字）。
 2.对于可以创建的视图数目没有限制。
 3.为了创建视图，必须具有足够的访问权限。这些限制通常由数据 库管理人员授予。
 4.视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造 一个视图。
 5. ORDER BY可以用在视图中，但如果从该视图检索数据SELECT中也 含有ORDER BY，那么该视图中的ORDER BY将被覆盖。
 6.视图不能索引，也不能有关联的触发器或默认值。
 7.视图可以和表一起使用。例如，编写一条联结表和视图的SELECT 语句。
 

 */

/*

视图用法：
1.视图用CREATE VIEW语句来创建。
2.使用SHOW CREATE VIEW viewname；来查看创建视图的语句。
3.用DROP删除视图，其语法为DROP VIEW viewname;。
4.更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在，则第2条更新语句会创 建一个视图；如果要更新的视图存在，则第2条更新语句会替换原 有视图。

*/

（1）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的视图，它联结三个 表，以返回已订购了任意产品的所有客户的列表。如果执行 SELECT * FROM productcustomers，将列出订购了任意产品的客户。
 

（2)select cust_name, cust_contact
        from productcustomers
        where prod_id = 'TNT2';//通过查询视图productcustomers返回了订购了TNT2的客户

(3)create view vendorloocations as  
    select concat(rtrim(vend_name),'(',rtrim(vend_country),')')
                           as vend_title
    from vendors
    order by vend_name;//创建了视图vendorloocations

(4)select * from vendorloocations;//直接搜索视图，返回了供应商和位置

(5)create view customeremaillist as
        select cust_id, cust_name, cust_email
        from customers
        where cust_email is not null;//建立视图customeremaillist，关于客户的id，客户名称，客户电子邮件

(6)select * from customeremaillist;//直接检索视图返回了客户的id，客户名称，客户电子邮件

(7)create view orderitemsexpanded as
        select order_num,
                   prod_id,
                   quantity,
                   item_price,
                   quantity*item_price as expanded_price
        from orderitems;//建立了一个视图可以检索某个特定订单中的物品，计算每种物品的总价格

(8)select * from orderitemsexpanded
    where order_num = 20005;//检索订单20005的详细内容,返回了所想要得内容，例如商品的编号，价格，数量，以及总价格

/*
注意：
1.通常，视图是可更新的（即，可以对它们使用INSERT、UPDATE和 DELETE）。更新一个视图将更新其基表（可以回忆一下，视图本身没有数 据）。如果你对视图增加或删除行，实际上是对其基表增加或删除行。
2.但是，并非所有视图都是可更新的。基本上可以说，如果MySQL不 能正确地确定被更新的基数据，则不允许更新（包括插入和删除）。这实 际上意味着，如果视图定义中有以下操作，则不能进行视图的更新： 
             (1)分组（使用GROUP BY和HAVING）；
             (2) 联结；
             (3)子查询；
             (4) 并；
             (5)聚集函数（Min()、Count()、Sum()等）
             (6)DISTINCT；
             (7)导出（计算）列。
*/

/*

使用存储过程

*/

/*

注意：我是使用mysql_front进行实验的，然后在通过命令行进行操作截图，因为这样截图出来看的比较清晰，所以在mysql_front进行存储操作是不会发生错误的，但是你如果要在命令窗体操作有可能出现错误哦，这时候的进行一些设置的改变。

mysql命令行客户机的分隔符  如果你使用的是mysql命令行 实用程序，应该仔细阅读此说明。

默认的MySQL语句分隔符为;（正如你已经在迄今为止所使用 的MySQL语句中所看到的那样）。mysql命令行实用程序也使 用;作为语句分隔符。如果命令行实用程序要解释存储过程自 身内的;字符，则它们最终不会成为存储过程的成分，这会使 存储过程中的SQL出现句法错误。

解决办法是临时更改命令行实用程序的语句分隔符，如下所示：

 delimiter //

CREATE PROCEDURE productpricing()

begin

    select avg(prod_price) as priceaverage

    from products;

end //

delimiter ;

其中，DELIMITER //告诉命令行实用程序使用//作为新的语 句结束分隔符，可以看到标志存储过程结束的END定义为END //而不是END;。这样，存储过程体内的;仍然保持不动，并且 正确地传递给数据库引擎。最后，为恢复为原来的语句分隔符，可使用DELIMITER ;。 除\符号外，任何字符都可以用作语句分隔符。 如果你使用的是mysql命令行实用程序，在阅读本章时请记住 这里的内容。

*/

 

(9)create procedure productpricing()

    begin
       select avg(prod_price) as priceaverage
       from products;
    end;//此存储过程名为 productpricing，用CREATE PROCEDURE productpricing()语 句定义。如果存储过程接受参数，它们将在()中列举出来。此存储过程没 有参数，但后跟的()仍然需要。BEGIN和END语句用来限定存储过程体，过 程体本身仅是一个简单的SELECT语句.

(10)call productpricing();//执行刚创建的存储过程并显示返回 的结果。因为存储过程实际上是一种函数，所以存储过程名后需要有()符号

(11)drop procedure productpricing;//这条语句删除刚创建的存储过程。请注意没有使用后面的(),只给出存储过程名。

/*
注意：
 如果指定的过程不存在，则DROP PROCEDURE 将产生一个错误。当过程存在想删除它时（如果过程不存在也 不产生错误）可使用DROP PROCEDURE IF EXISTS。
*/

(12)create procedure productpring(
                      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;//此存储过程接受3个参数：pl存储产品最低价格，ph存储产品 最高价格，pa存储产品平均价格。每个参数必须具有指定的类 型，这里使用十进制值。关键字OUT指出相应的参数用来从存储过程传出 一个值（返回给调用者） 。MySQL支持IN（传递给存储过程）、OUT（从存 储过程传出，如这里所用）和INOUT（对存储过程传入和传出）类型的参 数。存储过程的代码位于BEGIN和END语句内，如前所见，它们是一系列 SELECT语句，用来检索值，然后保存到相应的变量（通过指定INTO关键 字）
(13)  call productpring(@pricelow,
                                    @pricehigh,
                                    @priceaverage);//由于此存储过程要求3个参数，因此必须正好传递3个参数，不 多也不少。所以，这条CALL语句给出3个参数。它们是存储过 程将保存结果的3个变量的名字。  
(14)select @priceaverage;//检索出的产品平均价格                  

(15)select @pricelow,
                @pricehigh,
                  @priceaverage;//检索出的产品最低价格，最高价格，平均价格

(16)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，因为订单号被传入存储过程。ototal定义 为OUT，因为要从存储过程返回合计。SELECT语句使用这两个 参数，WHERE子句使用onumber选择正确的行，INTO使用ototal存储计算 出来的合计.

(17)call ordertotal(20005,@total);//必须给ordertotal传递两个参数；第一个参数为订单号，第二 个参数为包含计算出来的合计的变量名。

(18)select @total;//@total已由ordertotal的CALL语句填写，SELECT显示它包含 的值.即为订单20005的合计

(19)call ordertotal(20009,@total);
select @total;//为了得到另一个订单的合计显示，需要再次调用存储过程，然后重 新显示变量,这是20009的合计

数据库实际查询操作之相关数据库代码（MySQL必知必会数据库代码）

数据库实际查询操作回顾（七）

数据库实际查询操作回顾（六)

数据库实际查询操作回顾（五）

数据库实际查询操作回顾（四）

数据库实际查询操作回顾(三）

数据库实际查询操作回顾(二）

数据库实际查询操作回顾（一）