MySQL必知必会—视图和存储过程篇

2,030 阅读5分钟

MySQL必知必会—概念篇

MySQL必知必会—安装应用篇

MySQL必知必会—检索数据篇

MySQL必知必会—联结表和高级查询篇

MySQL必知必会—文本搜索篇

MySQL必知必会—表操作篇

下面用到的数据库文件可在 mysql_scripts 找到。


使用视图

视图仅仅是用来查看存储在别处的数据的一种设施,本身不包含数据,返回的数据都是从其他表中检索出来的,视图能更改数据格式和表示,最常见的应用就是重用 SQL 语句,简化复杂的 SQL 操作。

操作视图

CREATE VIEW 创建视图

SHOW CREATE VIEW viewname 查看创建视图的语句

DROP VIEW viewname 删除视图

更新视图时,可以先 DROP 然后再 CREATE 或者使用 CREATE OR REPLACE VIEW

### 之前有用联表查询 prod_id 是 TNT2的购买用户信息,但是如果还想看 prod_id 是其他值的时,还得重新查一遍,这样的场景就能用到视图了。

### 创建一个包含 cust_name, cust_contact, prod_id 的视图
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;
+----------------+--------------+---------+
| cust_name      | cust_contact | prod_id |
+----------------+--------------+---------+
| Coyote Inc.    | Y Lee        | ANV01   |
| Coyote Inc.    | Y Lee        | ANV02   |
| Coyote Inc.    | Y Lee        | TNT2    |
| Coyote Inc.    | Y Lee        | FB      |
| Coyote Inc.    | Y Lee        | FB      |
| Coyote Inc.    | Y Lee        | OL1     |
| Coyote Inc.    | Y Lee        | SLING   |
| Coyote Inc.    | Y Lee        | ANV03   |
| Wascals        | Jim Jones    | JP2000  |
| Yosemite Place | Y Sam        | TNT2    |
| E Fudd         | E Fudd       | FC      |
+----------------+--------------+---------+

### 如果想再查询出 prod_id 为 TNT2 的客户信息就很简单了
SELECT cust_name, cust_contact FROM productcustomers WHERE prod_id = 'TNT2';
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+

注意

  • 视图必须唯一命名(不能跟别的视图和表重名)
  • 对于可以创建的视图数量没有限制。
  • 视图可以嵌套,即可以利用从其他视图中检索数据的查询来构造一个新的视图。
  • ORDER BY 可以用在视图中,但如果从该视图检索数据的 SELECT 中也含有 ORDER BY ,那么视图中的 ORDER BY 会被覆盖。
  • 视图不能索引,也不能有关联的触发器或默认值。
  • 视图可以和表一起使用。
  • 视图一般用于检索(SELECT)而不用于更新(INSERT, UPDATE, DELETE),因为更新一个视图相当于更新其基表,如果不能正确地确定被更新的基数据,则不允许更新。


使用存储过程

使用存储过程主要有三个好处,简单,安全,高性能。

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

简单例子

### 创建名为 productpricing 的存储过程 如果存储过程接受参数,它们将在 productpricing () 这个括号中列举出来。
DELIMITER //

CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price) AS priceaverage FROM products;
END//


### 调用存储过程,仿佛在调用函数
CALL productpricing;
+--------------+
| priceaverage |
+--------------+
|    16.133571 |
+--------------+


### 删除存储过程
DROP PROCEDURE productpricing;

### 检查存储过程
SHOW CREATE PROCEDURE productpricing;

有参数的例子

### 创建存储过程
DELIMITER //
CREATE PROCEDURE productpricing (
  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 //


### 调用存储过程
CALL productpricing(@pricelow,@pricehigh,@priceaverage);

### 查看3个变量
SELECT @pricelow,@pricehigh,@priceaverage;
+-----------+------------+---------------+
| @pricelow | @pricehigh | @priceaverage |
+-----------+------------+---------------+
|      2.50 |      55.00 |         16.13 |
+-----------+------------+---------------+

建立智能存储过程

需要获取订单合计,并且对某些顾客的合计增加营业税。

### 存储过程全过程
DELIMITER //

-- Name: ordertotal
-- Parameters: onumber = order number
--             taxable = 0 if not taxable, 1 if taxable
--             ototal = order total variable

CREATE PROCEDURE ordertotal (
  IN onumber INT,
  IN taxable BOOLEAN,
  OUT ototal DECIMAL(8,2)
 ) COMMENT 'Obtain 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 //


 ### 调用,看一下 order number 是 20005 订单关于加不加营业税的区别
 ### 不加营业税
 CALL ordertotal(20005, 0, @total);
 SELECT @total;
+--------+
| @total |
+--------+
| 149.87 |
+--------+

### 加营业税
CALL ordertotal(20005, 1, @total);
SELECT @total;
+--------+
| @total |
+--------+
| 158.86 |
+--------+

上面代码中做些必要的解释

  • 添加了两个 IN 类型参数,其中 taxable 为布尔值。
  • -- 添加注释,在存储过程复杂是,注释很有必要。
  • DECLARE 定义局部变量,需要指定变量名和数据类型,支持可选的默认值
  • COMMENT 关键字,不是必需的,如果添加了,在 SHOW PROCEDURE STATUS 的结果中显示。

注意

  • 如果在 mysql 命令行中创建存储过程的话,需要临时更改命令行实用程序的语句分隔符,因为创建存储过程会使用 ; 作为语句分隔符,这会导致语法报错。除了 \ 符号外,任何字符都可以用作语句分隔符。 可以使用 DELIMITER // 作为新的语句结束分隔符,但是创建完存储过程后,要记得用 DELIMITER ; 恢复为原来的语句分隔符。
  • 存储过程在创建之后,被保存在服务器上以供使用,直至被删除。
  • 如果删除不存在的存储过程时,会报错,可以使用 DROP PROCEDURE IF EXISTS ,只有当过程存在时才删除。
  • MySQL 支持 IN(传递给存储过程)、OUT(从存储过程传出)、INOUT(对存储过程传入和传出)三种类型的参数。SELECT 检索出来的值通过 INTO 保存到相应的变量。特别注意,参数的数据类型不能是一个集合,所以例子中才用了三个参数输出3个数。
  • 如果存储过程要求3个参数,就必须正好传递3个参数。
  • SHOW PROCEDURE STATUS 可以列出所有存储过程,也可以使用 LIKE 指定一个过滤模式: SHOW PROCEDURE STATUS LIKE 'ordertotal';

一个持续更新的github笔记,链接地址:Front-End-Basics,可以watch,也可以star。

此篇文章的地址:MySql必知必会