当前位置：首页 > 文章列表 > 数据库 > MySQL > MySQL必知必会—视图和存储过程篇

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

来源：SegmentFault 2023-01-13 16:16:22 0浏览收藏

本篇文章主要是结合我之前面试的各种经历和实战开发中遇到的问题解决经验整理的，希望这篇《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必知必会

今天关于《MySQL必知必会—视图和存储过程篇》的内容就介绍到这里了，是不是学起来一目了然！想要了解更多关于mysql的内容请关注golang学习网公众号！

mysql MySQL

版本声明

本文转载于：SegmentFault 如有侵犯，请联系study_golang@163.com删除