MySQL 入门教程：使用 CREATE VIEW 语句创建视图

terry 2年前 (2023-09-26) 阅读数 48 #数据库

了解如何使用 CREATE VIEW 语句在 MySQL 中创建视图。

CREATE VIEW 语句简介

要在 MySQL 中创建新视图，可以使用 CREATE VIEW 语句。 MySQL 中创建视图的语法如下：

CREATE 
   [ALGORITHM = {MERGE  | TEMPTABLE | UNDEFINED}]
VIEW [database_name].[view_name] 
AS
[SELECT  statement]

SQL

我们来详细看看上面的语法。

视图处理算法

算法属性允许您控制MySQL在创建视图时使用的机制。 MySQL 提供三种算法：MERGE、TEMPTABLE 和 UNDEFINED 。

使用MERGE算法，MySQL 首先将输入查询与在单个查询中定义视图的 SELECT 语句结合起来。 MySQL 然后运行组合查询以返回结果集。如果 SELECT 语句中包含聚合函数（如 MIN、MAX、SUM、COUNT、AVG 等）或 DISTINCT、GROUP BY、HAVING、LIMIT、UNION、UNION ALL、子查询，则 MERGE 算法为不允许。如果 SELECT 语句不引用表，则不应使用算法MERGE。如果MERGE算法不被允许，MySQL会将算法更改为UNDEFINED。请注意，将输入查询和视图定义中的查询合并为单个查询称为视图分辨率。
采用TEMPTABLE算法，MySQL首先根据定义视图的SELECT语句创建临时表，然后根据临时表执行输入查询。由于MySQL需要创建临时表来存储结果集，并将数据从基表移动到临时表，因此TEMPTABLE算法的效率低于MERGE算法。此外，使用 TEMPTABLE 算法的视图无法更新。
当您创建视图而不指定显式算法时，UNDEFINED 是默认算法。 UNDEFINED算法使MySQL可以选择使用MERGE或TEMPTABLE算法。 MySQL更喜欢使用MERGE算法而不是TEMPTABLE算法，因为MERGE算法效率更高。

视图名称

在数据库中，视图和表共享相同的命名空间，因此视图和表不能具有相同的名称。此外，视图名称必须遵循表命名规则。

SELECT 语句

SELECT 语句允许从数据库中的任何表或视图检索数据。 SELECT 语句必须遵循以下规则：

SELECT 语句可以在 WHERE 子句中包含子查询，但FROM 子句不能包含子查询。
SELECT语句不能引用变量，包括局部变量、用户变量和会话变量。语句
SELECT 不能引用准备好的语句的参数。

请注意，SELECT 语句不必引用表。

创建示例 MySQL 视图

创建简单视图

让我们看一下 orderDetails 表。根据 orderDetails 表创建一个显示每个订单的总收入的视图。

CREATE VIEW SalePerOrder AS
    SELECT 
        orderNumber, SUM(quantityOrdered * priceEach) total
    FROM
        orderDetails
    GROUP by orderNumber
    ORDER BY total DESC;

SQL

如果我们使用 SHOW TABLES 命令查看示例数据库中的所有表 (yiibaidb)，我们还会看到 Sales PerOr der视图也出现在表格列表中。如下所示 -

mysql> SHOW TABLES;
+--------------------+
| Tables_in_yiibaidb |
+--------------------+
| article_tags       |
| contacts           |
| customers          |
| departments        |
| employees          |
| offices            |
| offices_bk         |
| offices_usa        |
| orderdetails       |
| orders             |
| payments           |
| productlines       |
| products           |
| saleperorder       |
+--------------------+
14 rows in set

SQL

这是因为视图和表共享相同的命名空间。要找出哪个对象是视图或表，请使用 SHOW FULL TABLES 命令，如下所示：

mysql> SHOW FULL TABLES;
+--------------------+------------+
| Tables_in_yiibaidb | Table_type |
+--------------------+------------+
| article_tags       | BASE TABLE |
| contacts           | BASE TABLE |
| customers          | BASE TABLE |
| departments        | BASE TABLE |
| employees          | BASE TABLE |
| offices            | BASE TABLE |
| offices_bk         | BASE TABLE |
| offices_usa        | BASE TABLE |
| orderdetails       | BASE TABLE |
| orders             | BASE TABLE |
| payments           | BASE TABLE |
| productlines       | BASE TABLE |
| products           | BASE TABLE |
| saleperorder       | VIEW       |
+--------------------+------------+
14 rows in set

SQL

结果集中的 table_type 列指示哪个对象是视图，它的对象是表（基表）。如上图，table_type列对应的saleperorder的值为：VIEW。

要获取每个销售订单的总销售额，只需在SalePerOrder视图中运行一个简单的SELECT语句，如下所示：

SELECT 
    *
FROM
    salePerOrder;

SQL

在查询语句中输入以上内容，得到以下结果结果 -

+-------------+----------+
| orderNumber | total    |
+-------------+----------+
|       10165 | 67392.85 |
|       10287 | 61402.00 |
|       10310 | 61234.67 |
|       10212 | 59830.55 |
|-- 此处省略了一大波数据-- |
|       10116 | 1627.56  |
|       10158 | 1491.38  |
|       10144 | 1128.20  |
|       10408 | 615.45   |
+-------------+----------+
327 rows in set

SQL

从另一个视图创建视图

MySQL 允许您从另一个视图创建视图。例如，可以在 SalSerSperorder 显示的基础上创建名称为大额销售订单 (bigs sales order) 的显示，以便为总金额大于的每个销售订单创建显示60,000如下所示：

CREATE VIEW BigSalesOrder AS
    SELECT 
        orderNumber, ROUND(total,2) as total
    FROM
        saleperorder
    WHERE
        total > 60000;

SQL

现在我们可以从BigSalesOrder视图中查询数据，如下所示：

SELECT 
    orderNumber, total
FROM
    BigSalesOrder;

SQL

执行上面的查询语句并得到以下结果 -

+-------------+----------+
| orderNumber | total    |
+-------------+----------+
|       10165 | 67392.85 |
|       10287 | 61402.00 |
|       10310 | 61234.67 |
+-------------+----------+
3 rows in set

SQL

使用联接表创建视图

以下是使用 INNER JOIN 创建视图的示例。在此视图中，您将找到客户编号和客户已支付的总金额。

CREATE VIEW customerOrders AS
    SELECT 
        c.customerNumber,
        p.amount
    FROM
        customers c
            INNER JOIN
        payments p ON p.customerNumber = c.customerNumber
    GROUP BY c.customerNumber
    ORDER BY p.amount DESC;

SQL

要查询 customerOrders 视图中的数据，请使用以下查询：

SELECT  * FROM customerOrders;

SQL

执行上述查询语句，得到以下结果 - 外壳

使用子查询创建视图

下面解释如何使用子查询创建包含价格高于所有产品平均价格的产品的视图。

CREATE VIEW aboveAvgProducts AS
    SELECT 
        productCode, productName, buyPrice
    FROM
        products
    WHERE
        buyPrice > 
 (SELECT 
                AVG(buyPrice)
            FROM
                products)
    ORDER BY buyPrice DESC;

SQL

在上面的视图中运行查询：aboveAvgProducts数据如下：

SELECT 
    *
FROM
    aboveAvgProducts;

SQL

运行上面的查询语句，得到以下结果 -

+-------------+-----------------------------------------+----------+
| productCode | productName                             | buyPrice |
+-------------+-----------------------------------------+----------+
| S10_4962    | 1962 LanciaA Delta 16V                  | 103.42   |
| S18_2238    | 1998 Chrysler Plymouth Prowler          | 101.51   |
| S10_1949    | 1952 Alpine Renault 1300                | 98.58    |
|************* 此处省略了一大波数据 *********************************|
| S18_3320    | 1917 Maxwell Touring Car                | 57.54    |
| S24_4258    | 1936 Chrysler Airflow                   | 57.46    |
| S18_3233    | 1985 Toyota Supra                       | 57.01    |
| S18_2870    | 1999 Indy 500 Monte Carlo SS            | 56.76    |
| S32_4485    | 1974 Ducati 350 Mk3 Desmo               | 56.13    |
| S12_4473    | 1957 Chevy Pickup                       | 55.7     |
| S700_3167   | F/A 18 Hornet 1/72                      | 54.4     |
+-------------+-----------------------------------------+----------+
54 rows in set

SQL

在本教程中，我们向您展示了如何使用语句CREATE VIEW 创建视图。