在MySQL中创建视图的X种方式

在本教程中，您将学习如何使用CREATE VIEW语句在MySQL中创建视图。

CREATE VIEW语句简介

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

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

让我们更详细地查看了解以上语法。

视图处理算法

算法属性允许您控制MySQL在创建视图时使用的机制。MySQL提供三种算法：MERGE，TEMP TABLE和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。 注意，输入查询和视图定义中的查询到一个查询的组合被称为视图决议。
- 使用TEMP TABLE算法，MySQL首先根据定义视图的SELECT语句创建一个临时表，然后对该临时表执行输入查询。因为MySQL必须创建一个临时表来存储结果集，并将数据从基表移动到临时表，TEMP TABLE算法的效率低于MERGE算法。此外，使用TEMP TABLE算法的视图不可更新。

• UNDEFINED是创建视图时未指定显式算法的默认算法。UNDEFINED算法让MySQL做出选择使用MERGE或TEMP TABLE算法。MySQL优先选择MERGE算法再到TEMP TABLE算法，因为MERGE算法更有效率。

视图名称

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

SELECT语句

在SELECT语句中，可以从数据库中存在的任何表或视图查询数据。SELECT语句必须遵循几个规则：
- SELECT语句可以在WHERE子句中包含子查询，但不能在FROM子句中。
- SELECT语句不能引用任何变量，包括局部变量，用户变量和会话变量。
- SELECT语句不能引用预处理语句的参数。

注意，SELECT语句不需要引用任何表。

创建MySQL视图示例

创建简单视图

让我们来看看 order_detail 表。可以创建一个表示每个订单的总销售额的视图。

CREATE VIEW SalePerOrder AS
    SELECT 
        order_id, SUM(buy_number * price) total
    FROM
        studymysql.order_detail
    GROUP by order_id
    ORDER BY total DESC;

如果使用SHOW TABLES命令查看studymysql数据库中的所有表，我们还会看到SalesPerOrder视图显示在列表中。

SHOW TABLES;

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

结果集中的 table_type 列指定哪个对象是视图，哪个对象是表（基表）。
如果我们要查询每个销售订单的总销售额，只需要按照如下所示对SalePerOrder视图执行一个简单的SELECT语句：

SELECT 
    *
FROM
    salePerOrder;

执行上面的语句，结果如下 -

基于一个视图来创建视图

MySQL允许您基于一个视图来创建另一个视图。例如，您可以基于SalesPerOrder视图创建名为大销售订单的视图，以显示总数大于150,000的销售订单，如下所示：

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

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

SELECT 
    order_id, total
FROM
    BigSalesOrder;

执行上面的语句，结果如下 -

使用join创建视图

以下是使用INNER JOIN创建视图的示例。视图包含订单号，客户名称和每个订单的总销售额。

CREATE VIEW customerOrders AS
    SELECT 
        o.order_number,
        c.customer_name,
        SUM(buy_number * price) total
    FROM
        studymysql.order_detail d
            INNER JOIN
        studymysql.order o ON o.order_id = d.order_id
            INNER JOIN
        studymysql.customer c ON c.customer_id = o.customer_id
    GROUP BY o.order_number
    ORDER BY total DESC;

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

SELECT
    *
FROM
    customerOrders;

执行上面的语句，结果如下 -

使用子查询创建视图

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

CREATE VIEW aboveAvgProducts AS
    SELECT 
        product_code, product_name, price
    FROM
        studymysql.product
    WHERE
        price > 
 (SELECT 
                AVG(price)
            FROM
                studymysql.product)
    ORDER BY price DESC;

从上述aboveAvgProducts视图中查询数据很简单，如下所示：

SELECT 
    *
FROM
    aboveAvgProducts;

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

相关教程

• MySQL中的视图
• 数据库视图简介
• 管理MySQL中的视图

MySQL学习QQ群：418407075