MySQL基础——视图和存储过程

最新推荐文章于 2022-05-11 16:19:33 发布

话说一半的王

最新推荐文章于 2022-05-11 16:19:33 发布

阅读量157

点赞数

分类专栏：《MySQL必知必会》学习笔记文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_41966900/article/details/105292569

版权

《MySQL必知必会》学习笔记专栏收录该内容

12 篇文章 0 订阅

订阅专栏

视图

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

什么是视图

在之前的笔记中有个例子：检索订购了‘TNT2’的客户的信息：

mysql> SELECT cust_name, cust_contact
    -> FROM customers, orders, orderitems
    -> WHERE customers.cust_id = orders.cust_id
    ->   AND orders.order_num = orderitems.order_num
    ->   AND orderitems.prod_id = 'TNT2';
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+

任何需要这个数据的人必须理解相关表的结构，并且知道如何创建查询和联结表。现在，把整个查询包装秤一个名为productcustomers的虚拟表，让一切变得轻松了：

mysql> SELECT cust_name, cust_contact
    -> FROM productcustomers
    -> WHERE prod_id = 'TNT2'

使用视图的好处：

重用SQL语句
简化复杂的SQL操作
使用表的组成部分而不是整个表
保护数据
更改数据格式和表示。视图可以返回与底层表的表示和格式不同的数据

视图的规则和限制

与表一样，视图必须唯一命名
对于可以创建的视图数目没有限制
为了创建视图，必须具有足够的访问权限。
视图可以嵌套。
ORDER BY可以用在视图中，但如果从该视图检索数据SELECT中也包含有ORDER BY，那么该视图中的ORDER BY将被覆盖。
视图不能索引，也不能有关联的触发器或默认值。
视图可以和表一起使用。

使用视图

视图用CREAT VIEW 语句来创建
使用SHOW CREAT VIEW viewname; 来查看创建视图的语句。
用DROP删除视图，其语法为DROP VIEW viewname;。
更新视图时，可以先用DROP再用CREATE，也可以直接用CREAT OR REPLACE VIEW。
视图定义在创建时被“ 冻结 ”，并且不受基础表定义的后续更改的影响。

利用视图简化复杂的联结

mysql> 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的视图，它联结了三个表，以返回已订购了任意产品的所有客户的列表。此时再检索订购了TNT2的客户，将非常简单：

mysql> SELECT cust_name, cust_contact
    -> FROM productcustomers
    -> WHERE prod_id = 'TNT2';
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+

用视图重新格式化检索出的数据

视图可以重新格式化检索出的数据，避免了每次检索时需要拼接字段和联结表：

mysql> CREATE VIEW vendorlocations AS
    -> SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')')
    ->        AS vend_title
    -> FROM vendors
    -> ORDER BY vend_name;
    
mysql> SELECT * FROM vendorlocations;
+------------------------+
| vend_title             |
+------------------------+
| ACME(USA)              |
| Anvils R Us(USA)       |
| Furball Inc.(USA)      |
| Jet Set(England)       |
| Jouets Et Ours(France) |
| LT Supplies(USA)       |
+------------------------+

用视图过滤不想要的数据

视图应用于简单的WHERE语句，可以为需要的数据创建视图

使用视图与计算字段

同格式化检索的数据相同

更新视图

通常，视图是可更新的（即可以对它们使用INSERT、UPDATE和DELETE）。更新视图将更新其基表。如果对视图增删行，实际是对其基表增删。
如果视图定义有一下操作，则不能进行视图更新：

分组（使用GROUP BY和HAVING）
联结
子查询
并
聚集函数（Min()、Count()、Sum()等）
DISTINCT
导出（计算）列

听上去是个挺严重的限制，实际不是，因为视图主要用于数据检索。

存储过程

存储过程简单来说，为以后的使用而保存的一条或多条MySQL语句的集合。
存储的例程可以使过程，也可以是函数。使用CREATE PROCEDURE和CREATE FUNCTION语句创建存储的例程。
可将其视为批文件，虽然它们的作用不仅限于批处理。
存储过程三个主要的好处：简单、安全、高性能。

执行存储过程

MySQL称存储过程的执行为调用，语句为CALL。CALL接受存储过程的名字和需要传递给它的任意参数。

定义存储过程

每个存储程序都包含一个由SQL语句组成的主体。该语句可以是由多个用分号（;）字符分隔的语句组成的复合语句。例如，以下存储过程的主体由一个BEGIN … END包含一个SET 语句的块和一个REPEAT本身包含另一个SET 语句的循环组成：

mysql> CREATE PROCEDURE dorepeat(p1 INT)
    -> BEGIN
    ->   SET @x = 0;
    ->   REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
    -> END

如果使用mysql客户端程序定义包含分号字符的存储程序，则会出现问题。默认情况下，mysql本身将分号识别为语句定界符，因此必须临时重新定义定界符，以使mysql将整个存储的程序定义传递给服务器。

要重新定义mysql分隔符，使用 delimiter命令。以下示例显示了如何针对dorepeat()刚刚显示的过程执行此操作。分隔符已更改为//允许将整个定义作为单个语句传递到服务器，然后;在调用该过程之前还原到。这使; 过程主体中使用的定界符可以传递到服务器，而不是由mysql 本身解释。

mysql> delimiter //
# 所有MySQL变量都必须以@开始
mysql> CREATE PROCEDURE dorepeat(p1 INT)
    -> BEGIN
    ->   SET @x = 0;
    ->   REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> CALL dorepeat(1000);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x;
+------+
| @x   |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)

可以将定界符重新定义为以外的其他字符串 //，并且定界符可以包含一个或多个字符。但应该避免使用反斜杠（\）字符，因为这是MySQL的转义字符。

下面是一个函数示例，该函数采用参数，使用SQL函数执行操作，然后返回结果。在这种情况下，delimiter由于函数定义不包含内部;语句定界符，因此无需使用：

mysql> CREATE FUNCTION hello (s CHAR(20))
mysql> RETURNS CHAR(50) DETERMINISTIC
    -> RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world!  |
+----------------+
1 row in set (0.00 sec)

删除存储过程

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

DROP PROCEDURE dorepeat;
DROP FUNCTION hello;

建立智能存储过程

存储过程内包含业务规则和智能处理时，它们的威力才真正显现出来。
考虑这样一个场景。需要获得一个订单合计，并针对某些顾客增加营业税：

获得合计
把营业税有条件地添加到合计
返回合计（带或不带税）

存储过程的完整工作如下：

CREATE PROCEDURE ordertotal(
   IN onumber INT,
   IN taxable BOOLEAN,
   OUT ototal DECIMAL(8,2)
 ) COMMENT 'Obain order total, optionally adding tax'
BEGIN
    -- Declare variable for total
    DECLARE total DECIMAL(8,2);
    -- Declare tax percentage
    DECLARE taxrate INT DEFAULT 6;

    -- Get the order total
    SELECT Sum(item_price*quantity)
    FROM orderitems
    WHERE order_num = onumber
    INTO total;

    -- Is this taxable?
    IF taxable THEN
        -- Yes, so add taxrate to the total
        SELECT total+(total/100*taxrate) INTO total;
    END IF;

    -- And finally, save to out variable
    SELECT total INTO ototal;

END;

这个存储过程稍显复杂：

增加了注释“-- ”
用DECLARE语句定义了两个局部变量，要求指定变量名和数据类型，也支持可选的默认值
用SELECT INTO 将查询结果保存到变量
COMMENT关键字不是必需的，如果给出，将在SHOW PROCEDURE STATUS的结果中显示

为了试验它，用下面两条语句：

mysql> CALL ordertotal(20005, 0, @total);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT @total;
+--------+
| @total |
+--------+
| 149.87 |
+--------+
1 row in set (0.00 sec)

mysql> CALL ordertotal(20005, 1, @total);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @total;
+--------+
| @total |
+--------+
| 158.86 |
+--------+
1 row in set (0.00 sec)

检查存储过程

为了实现用来创建一个存储过程的CREATE语句，使用SHOW CREATE PROCEDURE语句；
为了获得包括何时，由谁创建等详细信息的存储过程列表，使用SHOW PROCEDURE STATUS语句（会列出所有存储过程，为限制输出，可使用LIKE指定一个过滤模式）：

mysql> SHOW PROCEDURE STATUS LIKE 'ordertotal';
+------+------------+-----------+----------------+---------------------+---------------------+---------------+------------------------------------------+----------------------+----------------------+--------------------+
| Db   | Name       | Type      | Definer        | Modified            | Created             | Security_type | Comment                                  | character_set_client | collation_connection | Database Collation |
+------+------------+-----------+----------------+---------------------+---------------------+---------------+------------------------------------------+----------------------+----------------------+--------------------+
| shop | ordertotal | PROCEDURE | root@localhost | 2020-04-05 16:10:03 | 2020-04-05 16:10:03 | DEFINER       | Obain order total, optionally adding tax | gbk                  | gbk_chinese_ci       | utf8mb4_0900_ai_ci |
+------+------------+-----------+----------------+---------------------+---------------------+---------------+------------------------------------------+----------------------+----------------------+--------------------+

话说一半的王

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL基础——视图和存储过程

视图视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。什么是视图在之前的笔记中有个例子：检索订购了‘TNT2’的客户的信息：mysql> SELECT cust_name, cust_contact -> FROM customers, orders, orderitems -> WHERE customers.cust_id = ...
复制链接

扫一扫