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
语句不需要引用任何表。
创建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
,然后上面的脚本暂时就可以执行了
注意此方法在会话断开或重启数据库后,设置就恢复,要彻底解决就在my.cnf 配置中去掉
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模式