Mysql创建视图语法及其创建种类

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/qq_37022150/article/details/79034249

最近在处理视图相关的内容展示,注重研究了下视图相关的语法及其创建的种类.
CREATE VIEW语句简介

要在MySQL中创建一个新视图,可以使用CREATE VIEW语句。 在MySQL中创建视图的语法如下:

CREATE 
   [ALGORITHM = {MERGE  | TEMPTABLE | UNDEFINED}]
VIEW [database_name].[view_name] 
AS
[SELECT  statement]
SQL

下面我们来详细的查看上面的语法。

查看处理算法

算法属性允许您控制MySQL在创建视图时使用的机制,MySQL提供了三种算法:MERGETEMPTABLEUNDEFINED

  • 使用MERGE算法,MySQL首先将输入查询与定义视图的SELECT语句组合成单个查询。 然后MySQL执行组合查询返回结果集。 如果SELECT语句包含集合函数(如MINMAXSUMCOUNTAVG等)或DISTINCTGROUP BYHAVINGLIMITUNIONUNION ALL子查询,则不允许使用MERGE算法。 如果SELECT语句无引用表,则也不允许使用MERGE算法。 如果不允许MERGE算法,MySQL将算法更改为UNDEFINED。请注意,将视图定义中的输入查询和查询组合成一个查询称为视图分辨率

  • 使用TEMPTABLE算法,MySQL首先根据定义视图的SELECT语句创建一个临时表,然后针对该临时表执行输入查询。因为MySQL必须创建临时表来存储结果集并将数据从基表移动到临时表,所以TEMPTABLE算法的效率比MERGE算法效率低。 另外,使用TEMPTABLE算法的视图是不可更新的。

  • 当您创建视图而不指定显式算法时,UNDEFINED是默认算法。 UNDEFINED算法使MySQL可以选择使用MERGETEMPTABLE算法。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
SQL

这是因为视图和表共享相同的命名空间。要知道哪个对象是视图或表,请使用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
SQL

结果集中的table_type列指定哪个对象是视图,哪个对象是一个表(基表)。如上所示,saleperorder对应table_type列的值为:VIEW

如果要查询每个销售订单的总销售额,只需要对SalePerOrder视图执行一个简单的SELECT语句,如下所示:

SELECT
*
FROM
salePerOrder;

SQL

执行上面查询语句,得到以下结果 -

+-------------+----------+
| 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;
SQL

现在,我们可以从BigSalesOrder视图查询数据,如下所示:

SELECT 
    orderNumber, total
FROM
    BigSalesOrder;
SQL

执行上面查询语句,得到以下结果 -

+-------------+----------+
| orderNumber | total    |
+-------------+----------+
|       10165 | 67392.85 |
|       10287 | 61402.00 |
|       10310 | 61234.67 |
+-------------+----------+
3 rows in set
SQL

使用连接表创建视图

以下是使用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;
SQL

要查询customerOrders视图中的数据,请使用以下查询:

SELECT  * FROM customerOrders;
SQL

执行上面查询语句,得到以下结果 -

+----------------+-----------+
| 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
Shell

使用子查询创建视图

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

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

查询上述视图:aboveAvgProducts的数据简单如下:

SELECT 
    *
FROM
    aboveAvgProducts;
SQL

执行上面查询语句,得到以下结果 -

+-------------+-----------------------------------------+----------+
| 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

关注博客

关注更多文章

展开阅读全文

没有更多推荐了,返回首页