主頁 > 知識(shí)庫 > mysql視圖之創(chuàng)建視圖(CREATE VIEW)和使用限制實(shí)例詳解

mysql視圖之創(chuàng)建視圖(CREATE VIEW)和使用限制實(shí)例詳解

熱門標(biāo)簽:走過哪個(gè)省地圖標(biāo)注 在哪里申請400電話 外呼系統(tǒng)電銷受騙 安徽ai電話電銷機(jī)器人有效果嗎 400電話申請信用卡 常州網(wǎng)絡(luò)外呼系統(tǒng)開發(fā) 萊西市地圖標(biāo)注 巫師三血與酒地圖標(biāo)注 銷售語音電話機(jī)器人

本文實(shí)例講述了mysql視圖之創(chuàng)建視圖(CREATE VIEW)和使用限制。分享給大家供大家參考,具體如下:

mysql5.x 版本之后支持?jǐn)?shù)據(jù)庫視圖,在mysql中,視圖的幾乎特征符合SQL:2003標(biāo)準(zhǔn)。 mysql以兩種方式處理對視圖的查詢:

  • 第一種方式,MySQL會(huì)根據(jù)視圖定義語句創(chuàng)建一個(gè)臨時(shí)表,并在此臨時(shí)表上執(zhí)行傳入查詢。
  • 第二種方式,MySQL將傳入查詢與查詢定義為一個(gè)查詢并執(zhí)行組合查詢。

mysql支持版本系統(tǒng)的視圖,當(dāng)每次視圖被更改或替換時(shí),視圖的副本將在駐留在特定數(shù)據(jù)庫文件夾的arc(archive)文件夾中備份。備份文件的名稱為view_name.frm-00001。如果再次更改視圖,mysql將創(chuàng)建一個(gè)名為view_name.frm-00002的新備份文件。mysql允許基于其他視圖創(chuàng)建視圖,就是在視圖定義的select語句中,可以引用另一個(gè)視圖。

好啦,多的咱就不贅述了,接下來咱們嘗試使用CREATE VIEW語句創(chuàng)建視圖,先來看下語法結(jié)構(gòu):

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

然后我們來詳細(xì)看下上面的sql中的各種詞是什么意思。首先,第一個(gè)中括號(hào)里代表的就是創(chuàng)建視圖是的算法屬性,它允許我們控制mysql在創(chuàng)建視圖時(shí)使用的機(jī)制,并且mysql提供了三種算法:MERGE,TEMPTABLE和UNDEFINED。我們來分別看下:

  • 使用MERGE算法,mysql首先將輸入查詢與定義視圖的select語句組合成單個(gè)查詢。 然后mysql執(zhí)行組合查詢返回結(jié)果集。 如果select語句包含集合函數(shù)(如min,max,sum,count,avg等)或distinct,group by,havaing,limit,union,union all,子查詢,則不允許使用MERGE算法。 如果select語句無引用表,則也不允許使用MERGE算法。 如果不允許MERGE算法,mysql將算法更改為UNDEFINED。我們要注意,將視圖定義中的輸入查詢和查詢組合成一個(gè)查詢稱為視圖分辨率。
  • 使用TEMPTABLE算法,mysql首先根據(jù)定義視圖的SELECT語句創(chuàng)建一個(gè)臨時(shí)表,然后針對該臨時(shí)表執(zhí)行輸入查詢。因?yàn)閙ysql必須創(chuàng)建臨時(shí)表來存儲(chǔ)結(jié)果集并將數(shù)據(jù)從基表移動(dòng)到臨時(shí)表,所以TEMPTABLE算法的效率比MERGE算法效率低。 另外,使用TEMPTABLE算法的視圖是不可更新的。
  • 當(dāng)我們創(chuàng)建視圖而不指定顯式算法時(shí),UNDEFINED是默認(rèn)算法。 UNDEFINED算法使mysql可以選擇使用MERGE或TEMPTABLE算法。mysql優(yōu)先使用MERGE算法進(jìn)行TEMPTABLE算法,因?yàn)镸ERGE算法效率更高。

然后就是view后面的詞組了,它就是名稱的意思,在數(shù)據(jù)庫中,視圖和表共享相同的命名空間,因此視圖和表不能具有相同的名稱。 另外,視圖的名稱必須遵循表的命名規(guī)則。

最后就是SELECT語句了。在SELECT語句中,可以從數(shù)據(jù)庫中存在的任何表或視圖查詢數(shù)據(jù),同時(shí)SELECT語句必須遵循以下幾個(gè)規(guī)則:

  • SELECT語句可以在where 語句中包含子查詢,但FROM子句中的不能包含子查詢。
  • SELECT語句不能引用任何變量,包括局部變量,用戶變量和會(huì)話變量。
  • SELECT語句不能引用準(zhǔn)備語句的參數(shù)。

在這里我們得稍稍注意下,SELECT語句不需要引用任何表。完事呢,我們來嘗試基于orderDetails表來創(chuàng)建一個(gè)表示每個(gè)訂單的總銷售額的視圖:

CREATE VIEW SalePerOrder AS
  SELECT 
    orderNumber, SUM(quantityOrdered * priceEach) total
  FROM
    orderDetails
  GROUP by orderNumber
  ORDER BY total DESC;

我們?nèi)绻褂肧HOW TABLES命令來查看示例數(shù)據(jù)庫(yiibaidb)中的所有表,還會(huì)看到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

這是因?yàn)橐晥D和表共享相同的命名空間。要知道哪個(gè)對象是視圖或表,就得使用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

結(jié)果集中的table_type列指定哪個(gè)對象是視圖,哪個(gè)對象是一個(gè)表(基表)。如上所示,saleperorder對應(yīng)table_type列的值為:VIEW。然而,如果要查詢每個(gè)銷售訂單的總銷售額,只需要對SalePerOrder視圖執(zhí)行一個(gè)簡單的SELECT語句,如下所示:

SELECT 
  *
FROM
  salePerOrder;

執(zhí)行上面查詢語句,得到以下結(jié)果:

+-------------+----------+
| orderNumber | total  |
+-------------+----------+
|    10165 | 67392.85 |
|    10287 | 61402.00 |
|    10310 | 61234.67 |
|    10212 | 59830.55 |
|-- 此處省略了many many數(shù)據(jù)-- |
|    10116 | 1627.56 |
|    10158 | 1491.38 |
|    10144 | 1128.20 |
|    10408 | 615.45  |
+-------------+----------+
327 rows in set

我們再來基于另一個(gè)視圖創(chuàng)建一個(gè)視圖,比如,根據(jù)SalesPerOrder視圖創(chuàng)建名為大銷售訂單(BigSalesOrder)的視圖,以顯示總計(jì)大于60,000的每個(gè)銷售訂單,如下所示:

CREATE VIEW BigSalesOrder AS
  SELECT 
    orderNumber, ROUND(total,2) as total
  FROM
    saleperorder
  WHERE
    total > 60000;

現(xiàn)在,我們可以從BigSalesOrder視圖查詢數(shù)據(jù),如下所示:

SELECT 
  orderNumber, total
FROM
  BigSalesOrder;

執(zhí)行上面查詢語句,得到以下結(jié)果:

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

完事我們再來嘗試使用inner join創(chuàng)建包含客戶編號(hào)和客戶支付的總金額的視圖,如下所示:

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視圖中的數(shù)據(jù):

+----------------+-----------+
| customerNumber | amount  |
+----------------+-----------+
|      124 | 101244.59 |
|      321 | 85559.12 |
|      239 | 80375.24 |
| **** 此處省略了many many數(shù)據(jù) ***|
|      219 | 3452.75  |
|      216 | 3101.4  |
|      161 | 2434.25  |
|      172 | 1960.8  |
+----------------+-----------+
98 rows in set

再來嘗試使用子查詢創(chuàng)建包含價(jià)格高于所有產(chǎn)品的平均價(jià)格的產(chǎn)品的視圖,如下所示:

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

我們來查詢aboveAvgProducts視圖的數(shù)據(jù):

SELECT 
  *
FROM
  aboveAvgProducts;

執(zhí)行上面查詢語句,得到以下結(jié)果:

+-------------+-----------------------------------------+----------+
| 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  |
|************* 此處省略了many many數(shù)據(jù) *********************************|
| 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

好啦,到這里了,視圖的創(chuàng)建和使用已經(jīng)介紹的差不多了。但是,視圖就沒有什么使用的限制么?答案當(dāng)然是有的,我們來分別看下。

首先,我們不能在視圖上創(chuàng)建索引,再來就是當(dāng)使用合并算法的視圖查詢數(shù)據(jù)時(shí),mysql會(huì)使用底層表的索引,還有就是對于使用誘惑算法的視圖,當(dāng)我們針對視圖查詢數(shù)據(jù)時(shí),不會(huì)使用索引。

還有就是要注意在mysql5.7.7之前版本,是不能在SELECT語句的FROM子句中使用子查詢來定義視圖的。

再來就是如果刪除或重命名視圖所基于的表,則mysql不會(huì)發(fā)出任何錯(cuò)誤。但是,mysql會(huì)使視圖無效,我們可以使用CHECK TABLE語句來檢查視圖是否有效。

一個(gè)簡單的視圖可以更新表中數(shù)據(jù),但是基于具有連接,子查詢等的復(fù)雜select語句創(chuàng)建的視圖無法更新。

mysql不像Oracle、PostgreSQL等其他數(shù)據(jù)庫系統(tǒng)那樣支持物理視圖,mysql是不支持物理視圖的。

好啦,關(guān)于視圖本次就記錄到這里了。

更多關(guān)于MySQL相關(guān)內(nèi)容感興趣的讀者可查看本站專題:《MySQL查詢技巧大全》、《MySQL事務(wù)操作技巧匯總》、《MySQL存儲(chǔ)過程技巧大全》、《MySQL數(shù)據(jù)庫鎖相關(guān)技巧匯總》及《MySQL常用函數(shù)大匯總》

希望本文所述對大家MySQL數(shù)據(jù)庫計(jì)有所幫助。

您可能感興趣的文章:
  • MySQL如何創(chuàng)建視圖
  • 詳細(xì)分析mysql視圖的原理及使用方法
  • MySQL的視圖和索引用法與區(qū)別詳解
  • 淺談MySql 視圖、觸發(fā)器以及存儲(chǔ)過程
  • MySql視圖觸發(fā)器存儲(chǔ)過程詳解
  • mysql視圖原理與用法實(shí)例詳解
  • mysql視圖之管理視圖實(shí)例詳解【增刪改查操作】
  • mysql視圖之創(chuàng)建可更新視圖的方法詳解
  • MySQL中Update、select聯(lián)用操作單表、多表,及視圖與臨時(shí)表的區(qū)別
  • mysql三張表連接建立視圖
  • MySQL 視圖(View)原理解析

標(biāo)簽:黃石 河北 果洛 煙臺(tái) 鞍山 來賓 赤峰 陽江

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《mysql視圖之創(chuàng)建視圖(CREATE VIEW)和使用限制實(shí)例詳解》,本文關(guān)鍵詞  mysql,視圖,之,創(chuàng)建,CREATE,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《mysql視圖之創(chuàng)建視圖(CREATE VIEW)和使用限制實(shí)例詳解》相關(guān)的同類信息!
  • 本頁收集關(guān)于mysql視圖之創(chuàng)建視圖(CREATE VIEW)和使用限制實(shí)例詳解的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章