返回 登录
0

在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提供三种算法:MERGETEMP TABLEUNDEFINED
- 使用MERGE算法,MySQL首先将输入查询与定义视图的SELECT语句组合成单个查询。 然后MySQL执行组合查询以返回结果集。如果SELECT语句包含聚合函数,如:MIN, MAX, SUM, COUNT, AVGDISTINCT, 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做出选择使用MERGETEMP 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学习QQ群:418407075

评论