MySQL建立檢視 - tw511教學網

在本教學中，您將學習如何使用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語句建立檢視。