| 在本教程中，您将学习如何使用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，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; |
如果使用SHOW TABLES命令来查看示例数据库(yiibaidb)中的所有表，我们还会看到SalesPerOrder视图也显示在表的列表中。如下所示
-
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 |
这是因为视图和表共享相同的命名空间。要知道哪个对象是视图或表，请使用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 |
结果集中的table_type列指定哪个对象是视图，哪个对象是一个表(基表)。如上所示，saleperorder对应table_type列的值为：VIEW。
如果要查询每个销售订单的总销售额，只需要对SalePerOrder视图执行一个简单的SELECT语句，如下所示：
SELECT
*
FROM
salePerOrder; |
执行上面查询语句，得到以下结果 -
+-------------+----------+
| 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 |
基于另一个视图创建视图
MySQL允许您基于另一个视图创建一个视图。例如，可以根据SalesPerOrder视图创建名为大销售订单(BigSalesOrder)的视图，以显示总计大于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 |
使用连接表创建视图
以下是使用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; |
要查询customerOrders视图中的数据，请使用以下查询：
| SELECT
* FROM customerOrders; |
执行上面查询语句，得到以下结果 -
+----------------+-----------+
| customerNumber | amount |
+----------------+-----------+
| 124 | 101244.59 |
| 321 | 85559.12 |
| 239 | 80375.24 |
| **** 此处省略了一大波数据 ***|
| 219 | 3452.75 |
| 216 | 3101.4 |
| 161 | 2434.25 |
| 172 | 1960.8 |
+----------------+-----------+
98 rows in set |
使用子查询创建视图
以下说明如何使用子查询创建视图，该视图包含价格高于所有产品的平均价格的产品。
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
|
|************* 此处省略了一大波数据 *********************************|
| 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 |
在本教程中，我们向您展示了如何使用CREATE VIEW语句创建视图。 |
