最近項目上有使用到數(shù)據(jù)庫視圖坪圾，覺得需要把相關(guān)的知識整理一下方便學(xué)習(xí)拯啦。今天先簡單介紹一下視圖的概念和一些基本原理。
什么是數(shù)據(jù)庫視圖
數(shù)據(jù)庫視圖的創(chuàng)建是基于SQL SELECT query和JOIN的。視圖和表很相似吵聪，它也包含行和列，所以可以直接對它進行查詢操作兼雄。另外大多數(shù)的數(shù)據(jù)庫同樣允許進行UPADTE操作吟逝，但必須滿足一定的條件。視圖的數(shù)據(jù)結(jié)構(gòu)如圖：
我們需要理解赦肋，數(shù)據(jù)庫并沒有存儲視圖所關(guān)聯(lián)的數(shù)據(jù)块攒，存儲的只是視圖的定義也就是相應(yīng)的SQL SELECT and JOIN。
那么使用數(shù)據(jù)庫視圖到底有哪些優(yōu)勢呢：
- 視圖可以簡化你的復(fù)雜查詢：視圖的定義是基于一個查詢聲明佃乘，這個查詢聲明可能關(guān)聯(lián)了很多底層表囱井。我們可以使用視圖向數(shù)據(jù)庫的使用者或者外部程序隱藏復(fù)雜的底層表關(guān)系。
- 視圖可以限制特定用戶的數(shù)據(jù)訪問權(quán)：有時我們希望隱藏某些表的一些數(shù)據(jù)對一些特定用戶趣避，這時視圖可以很好的幫助我們實現(xiàn)這個功能庞呕。
-
視圖可以使用可計算的列：我們知道表的列一般都不支持動態(tài)計算，但是視圖的列是支持的程帕。假設(shè)在有一張
order_details表住练，其中包含product_nums和price_each兩列，當(dāng)我們需要查詢order總價時我們就需要查詢出結(jié)果后在代碼中進行計算愁拭，如果我們使用視圖的話可以在視圖中添加一列total_price(product_nums*price_each)讲逛。這樣就可以直接查詢出order的總價。 - 視圖可以幫助我們兼容舊的系統(tǒng)：假設(shè)我們擁有一個數(shù)據(jù)中心岭埠，這個數(shù)據(jù)中心被很多的程序在使用妆绞。如果有一天我們決定重新設(shè)計這個數(shù)據(jù)中心以適應(yīng)一些新的業(yè)務(wù)需求，可能需要刪除一些舊的表枫攀，并且創(chuàng)建一些新的表括饶，但是我們并不希望這些變動影響到那些老的程序。那么我們可以創(chuàng)建一些視圖用來適配那些老的程序来涨。
MySQL View
MySQL從5.x版本支持視圖图焰，并且基本符合SQL: 2003標(biāo)準(zhǔn)。
MySQL中執(zhí)行查詢視圖的方式有一下兩種：
- MySQL會合并輸入的查詢語句和視圖的查詢聲明然后執(zhí)行合并后的語句并返回結(jié)果蹦掐。
- MySQL會基于視圖的查詢聲明創(chuàng)建一個temporary table, 當(dāng)執(zhí)行查詢語句時會查詢這張
temporary table
如果創(chuàng)建視圖的時候并未指定查詢方式技羔，MySQL會默認(rèn)優(yōu)先使用第一種僵闯，但如果視圖的查詢聲明中的SELECT使用了聚合函數(shù)(MIN, MAX, SUM, COUNT, AVG, etc., or DISTINCT, GROUP BY, HAVING, LIMIT, UNION, UNION ALL, subquery.)，那么視圖查詢會使用第二種方式藤滥。
Create View
創(chuàng)建MySQL視圖可以使用CREATE VIEW聲明：
CREATE
[ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}]
VIEW [database_name].[view_name]
AS
[SELECT statement]
ALGORITHM：
MySQL有三種視圖執(zhí)行策略, 分別是MERGE, TEMPTABLE, UNDEFINED.
- 使用
MERGE策略鳖粟，MySQL會先將輸入的查詢語句和視圖的聲明語句進行合并，然后執(zhí)行合并后的語句并返回拙绊。但是如果輸入的查詢語句中不允許包含一些聚合函數(shù)如:MIN, MAX, SUM, COUNT, AVG, etc., or DISTINCT, GROUP BY, HAVING, LIMIT, UNION, UNION ALL, subquery向图。同樣如果視圖聲明沒有指向任何數(shù)據(jù)表，也是不允許的标沪。如果出現(xiàn)以上任意情況, MySQL默認(rèn)會使用UNDEFINED策略榄攀。 - 使用
TEMPTABLE策略，MySQL先基于視圖的聲明創(chuàng)建一張temporary table金句，當(dāng)輸入查詢語句時會直接查詢這張temporary table檩赢。由于需要創(chuàng)建temporary table來存儲視圖的結(jié)果集,TEMPTABLE的效率要比MERGE策略低，另外使用temporary table策略的視圖是無法更新的违寞。 - 使用
UNDEFINED策略贞瞒，如果創(chuàng)建視圖的時候不指定策略，MySQL默認(rèn)使用此策略趁曼。UNDEFINED策略會自動選擇使用上述兩種策略中的一個军浆，優(yōu)先選擇MERGE策略，無法使用則轉(zhuǎn)為TEMPTABLE策略彰阴。
View Name
視圖的名稱瘾敢，在MySQL中視圖名和表名使用同一命名空間拍冠，這意味這視圖名不能和表名重復(fù)并且要符合表名的命名規(guī)范尿这。
Select Statement
在視圖的查詢聲明中你可以查詢所有數(shù)據(jù)庫中已存在的表的數(shù)據(jù)，有以下集中規(guī)則：
- 查詢聲明可以在
WHERE條件中使用子查詢但是不允許在FROM的來源中使用子查詢庆杜。 - 查詢聲明中不允許引用任何變量射众，包括
local variables, user variables, and session variables。 - 查詢聲明中不允許引用
prepare statement的參數(shù)晃财。
Note: 查詢聲明的From中可以引用其他視圖
Example
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;
- 使用子查詢
CREATE VIEW aboveAvgProducts AS
SELECT
productCode, productName, buyPrice
FROM
products
WHERE
buyPrice >
(SELECT
AVG(buyPrice)
FROM
products)
ORDER BY buyPrice DESC;
查詢視圖
SELECT * FROM customerOrders;
