mysql创建视图演示_MySQL 创建视图

最新推荐文章于 2024-07-21 19:06:52 发布

子维酱

最新推荐文章于 2024-07-21 19:06:52 发布

阅读量689

点赞数

文章标签： mysql创建视图演示

本文链接：https://blog.csdn.net/weixin_42518453/article/details/113210089

版权

本文介绍了如何使用CREATE VIEW语句在MySQL中创建视图，包括视图的算法、命名规则和示例，如创建简单视图、基于已有视图创建新视图、使用JOIN和子查询创建视图等。

摘要由CSDN通过智能技术生成

MySQL 创建视图

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

CREATE VIEW语句简介

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

CREATE

[ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}]

VIEW view_name [(column_list)]

select-statement;

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

视图的处理算法

算法属性允许您控制MySQL在创建视图时使用的机制。MySQL提供三种算法： MERGE，TEMPTABLE，和UNDEFINED。

使用MERGE算法，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 |

...

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

+----------------------+------------+

| 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创建视图

以下是使用订单号，客户名称和每个订单的总销售额。

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