MySQL 创建视图

---

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

---

CREATE VIEW语句简介

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

CREATE 
   [ALGORITHM = {MERGE  | TEMPTABLE | UNDEFINED}]
VIEW view_name [(column_list)]
AS
select-statement;

让我们更详细地研究一下语法。

视图的处理算法

算法属性允许您控制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表。我们可以创建一个代表每个订单总销售额的视图。

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

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

SHOW TABLES;

+----------------------+
| Tables_in_mysqldemo  |
+----------------------+
| customers            |
| employees            |
| offices              |
| orderdetails         |
| orders               |
| payments             |
| productlines         |
| products             |
| saleperorder         |
...

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

+----------------------+------------+
| Tables_in_mysqldemo  | Table_type |
+----------------------+------------+
| customers            | BASE TABLE |
| employees            | BASE TABLE |
| offices              | BASE TABLE |
| orderdetails         | BASE TABLE |
| orders               | BASE TABLE |
| payments             | BASE TABLE |
| productlines         | BASE TABLE |
| products             | BASE TABLE |
| saleperorder         | VIEW       |
...

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

SELECT 
    *
FROM
    salePerOrder;

+-------------+----------+
| orderNumber | total    |
+-------------+----------+
|       10165 | 67392.85 |
|       10287 | 61402.00 |
|       10310 | 61234.67 |
|       10212 | 59830.55 |
|       10207 | 59265.14 |
|       10127 | 58841.35 |
...

基于另一个视图创建视图

MySQL允许您基于另一个视图创建视图。例如，您可以根据视图创建一个名为大销售订单的SalesPerOrder视图，以显示总计大于60,000的每个销售订单：

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

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

SELECT 
    orderNumber, total
FROM
    BigSalesOrder;

+-------------+----------+
| orderNumber | total    |
+-------------+----------+
|       10165 | 67392.85 |
|       10287 | 61402.00 |
|       10310 | 61234.67 |
+-------------+----------+
3 rows in set (0.01 sec)

使用join创建视图

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

CREATE VIEW customerOrders AS
    SELECT 
        d.orderNumber,
        customerName,
        SUM(quantityOrdered * priceEach) total
    FROM
        orderDetails d
            INNER JOIN
        orders o ON o.orderNumber = d.orderNumber
            INNER JOIN
        customers c ON c.customerNumber = c.customerNumber
    GROUP BY d.orderNumber
    ORDER BY total DESC;

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

SELECT
    *
FROM
    customerOrders;

+-------------+-------------------+------------+
| orderNumber | customerName      | total      |
+-------------+-------------------+------------+
|       10165 | Atelier graphique | 8221927.70 |
|       10287 | Atelier graphique | 7491044.00 |
|       10310 | Atelier graphique | 7470629.74 |
|       10212 | Atelier graphique | 7299327.10 |
|       10207 | Atelier graphique | 7230347.08 |
|       10127 | Atelier graphique | 7178644.70 |
|       10204 | Atelier graphique | 7172810.66 |
|       10126 | Atelier graphique | 6970094.24 |
|       10222 | Atelier graphique | 6932363.30 |
|       10142 | Atelier graphique | 6838412.32 |
...

如果在执行以上 SQL 中有如下报错：

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mysqldemo.c.customerName' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

就先执行如下脚本：

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

此脚本是去掉 sql_mode 参数中的ONLY_FULL_GROUP_BY，然后上面的脚本暂时就可以执行了

使用子查询创建视图

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

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

从中查询数据aboveAvgProducts很简单如下：

SELECT 
    *
FROM
    aboveAvgProducts;

+-------------+-----------------------------------------+----------+
| 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 |
| S24_3856    | 1956 Porsche 356A Coupe                 |    98.30 |
| S12_1108    | 2001 Ferrari Enzo                       |    95.59 |
| S12_1099    | 1968 Ford Mustang                       |    95.34 |
| S18_1984    | 1995 Honda Civic                        |    93.89 |
...

在本教程中，我们向您展示了如何使用CREATE VIEW  语句创建视图。 有关执行中的报错请参考：MySQL中sql_mode变量添加或删除单个SQL模式

---