第五章 復(fù)雜查詢

?

5.1 視圖

5.1.1 視圖和表?

? ? ? ?從SQL的角度來看，視圖和表是相同的。兩者的區(qū)別在于表中保存的是實際的數(shù)據(jù)，而視圖中保存的是SELECT語句。視圖本身并不存儲數(shù)據(jù)。

? ? ? ?視圖的優(yōu)點：1.視圖無需保存數(shù)據(jù)

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 2.將頻繁使用SELECT語句保存成視圖

?

代碼1.通過視圖等SELECT語句保存數(shù)據(jù)

SELECT shohin_bunrui,SUM(hanbai_tanka),SUM(shiire_tanka)FROM Shohin GROUP BY shohin_bunrui;

輸出結(jié)果：

　

5.1.2 創(chuàng)建視圖的方法

代碼2.創(chuàng)建視圖時使用CREATE VIEW語句，語法如下：

CREATE VIEW 視圖名稱(<視圖列名1>,<視圖列名2>,...) AS <SELECT語句>

? ? ? ?SELECT語句需要書寫在AS關(guān)鍵字之后，SELECT語句中列的排列順序和視圖中列的排列順序相同。

?

代碼3.ShohinSum視圖

CREATE VIEW ShohinSum ( shohin_bunrui, cnt_shohin) AS SELECT shohin_bunrui,COUNT(*)FROM ShohinGROUP BY shohin_bunrui;

輸出結(jié)果：

?

? ? ? 此處AS的定義與定義別名時使用的AS不相同。

?

代碼4.使用視圖

SELECT shohin_bunrui,cnt_shohinFROM ShohinSum;

?輸出結(jié)果：

?

? ? ? ? ? 定義視圖時使用任何SELECT語句，即可以使用WHERE,GROUP BY,HAVING，也可以通過SELECT *來指定全部列。

?

? ? ? ? ? 使用視圖查詢：在FROM子句中使用視圖查詢有以下兩個步驟

? ? ? ? ? ?1.首先執(zhí)行定義視圖的SELECT語句

? ? ? ? ? ?2.根據(jù)得到的結(jié)果，再執(zhí)行在FROM子句中使用視圖的SELECT語句

? ? ? ? ? 使用視圖查詢通常需要執(zhí)行2條以上的SELECT語句。這里沒有使用2條而使用了2條以上是因為還可能出現(xiàn)以視圖為基礎(chǔ)創(chuàng)建出的類似于樓中樓那樣的多重視圖。

代碼5.視圖ShohinSumJim

CREATE VIEW ShohinSumJim (shohin_bunrui,cnt_shohin) AS SELECT shohin_bunrui,cnt_shohinFROM ShohinSumWHERE shohin_bunrui='辦公用品';--確認是否創(chuàng)建出了視圖 SEELCT shohin_bunrui,cnt_shohinFROM ShohinSumnJim;

　　

? ? ? 雖然語法上沒有錯誤，但是我們還是應(yīng)該盡量避免在視圖的基礎(chǔ)上創(chuàng)建視圖。這是因為對于大多數(shù)DBMS來說，多重視圖會降低SQL的性能。

?

5.1.3 視圖的限制1——定義視圖時不能使用ORDER BY子句

? ? ?原因是視圖和表一樣，數(shù)據(jù)行都是沒有順序的。

?

5.1.4 視圖的限制2——對視圖進行更新

? ? ? 標準SQL規(guī)定：如果定義視圖的SELECT語句能夠滿足某些條件，那么這個視圖就可以被更新。下面是一些比較有代表性的條件。

? ? ? 條件：1.SELECT子句中未使用DISTINCT

? ? ? ? ? ? ? ? ?2.FROM 子句中只有一張表?

? ? ? ? ? ? ? ? ?3.未使用GROUP BY子句

? ? ? ? ? ? ? ? ?4.未使用HAVING子句

?

能夠更新視圖的情況：

代碼6.可以更新的視圖

CREATE VIEW ShohinJim (shohin_id,shohin_mei,shohin_bunrui,hanbai_tanka,shiire_tanka,torokubi) AS SELECT *FROM ShohinWHERE shohin_bunrui = '辦公用品';

輸出結(jié)果：

　　

代碼7.向視圖中添加數(shù)據(jù)行

INSERT INTO ShohinJim VALUES ('0009','印章',‘辦公用品’,95,10,'2009-11-30');

輸出結(jié)果：

? ? ? ?

注意事項：

? ? ? ? 在PostgreSQL中，執(zhí)行上面的向視圖中添加數(shù)據(jù)行代碼會出現(xiàn)錯誤，因為PostgreSQL中的視圖會被初始設(shè)定為只讀

?

代碼8.允許PostgreSQL對視圖進行更新

--PostgreSQLCREATE OR REPLACE RULE insert_rule AS ON INSERT TO ShohinJim DO INSTEAD INSERT INTO Shohin VALUES(new.shohin_id,new.shohin_mei,new.shohin_bunrui,new.hanbai_tanka,new.shiire_tanka,new.torokubi); --確認數(shù)據(jù)是否已經(jīng)添加到視圖中了 SELECT*FROM ShohinJim;

輸出結(jié)果：

?

--確認數(shù)據(jù)是否已經(jīng)添加到原表中了 SELECT*FROM Shohin;

?輸出結(jié)果：

?

?

?5.1.5 刪除視圖

代碼9.刪除視圖需要使用DROP VIEW語句，語法如下：

DROP VIEW (<視圖列名1>,<視圖列名2>,......)

　　

代碼10.刪除視圖

DROP VIEW ShohinSum;

輸出結(jié)果；

　　

? ? ? ?在PostgreSQL中，如果想要刪除以視圖為基礎(chǔ)創(chuàng)建出來的多重視圖的話，由于存在關(guān)聯(lián)的視圖，所以會發(fā)生如下錯誤：

? ? ? ? ERROR：由于存在關(guān)聯(lián)視圖，所以無法刪除視圖shohinsum

? ? ? ? DETALL：視圖shohinsumjim與視圖shohinsum相關(guān)聯(lián)

? ? ? ? HINT：刪除關(guān)聯(lián)對象請使用DROP...CASCADE

? ?

代碼11.在PostgreSQL中使用CASCADE選項來刪除關(guān)聯(lián)視圖

DROP VIEW ShohinSum CASCADE;

輸出結(jié)果：

?

　　 ? ? ? ? ? ?

5.2 子查詢

? ? ? ? ?

5.2.1 子查詢和視圖

? ? ?子查詢就是一張一次性的視圖，子查詢就是將用來定義視圖的SELECT語句直接用于FROM子句之中。

?

代碼12.視圖ShohinSun和確認用的SELCT語句

--根據(jù)商品種類統(tǒng)計商品數(shù)量的視圖 CREATE VIEW ShohinSum (shohin_bunrui,cnt_shohin) AS SELECT shohin_bunrui,COUNT(*)FROM ShohinGROUP BY Shohin_bunrui;--確認視圖是否已經(jīng)創(chuàng)建成功 SELECT shohin_bunrui,cnt_shohinFROM ShohinSum;

輸出結(jié)果：

?

代碼13.子查詢

--SQL Server、DB2、PostgreSQL、MySQL --直接在FROM子句中使用定義視圖的SELECT語句SELECT shohin_bunrui,cnt_shohinFROM (SELECT shohin_bunrui,COUNT(*) AS cnt_shohinFROM ShohinGROUP BY shohin_bunrui) AS ShohinSum;

輸出結(jié)果：

　　(在Oracle中，不能使用AS。)

? ? ? ?實際上，該SELECT語句包含嵌套結(jié)構(gòu)，首先會執(zhí)行FROM子句中的SELECT語句，然后才會執(zhí)行外層的SELECT語句。

? ? ? ?子查詢作為內(nèi)層會首先執(zhí)行。

?

? ? ? ? 增加查詢層數(shù)

? ? ? ? 子查詢在層數(shù)原則上是沒有限制的，可以無限嵌套下去。

代碼14.嘗試增加子查詢的嵌套層數(shù)

--SQL Server、DB2、PostgreSQL、MySQLSELECT shohin_bunrui,cnt_shohinFROM (SELECT * FROM ( SELECT shohin_bunrui,COUNT(*) AS cnt_shohinFROM ShohinGROUP BY shohin_bunrui) AS ShohinSumWHERE cnt_shohin = 4 ) AS ShohinSum2;

輸出結(jié)果：

　 (在Oracle中不能使用AS)

? ? ?隨著子查詢嵌套層數(shù)的增加，SQL語句變得越來越難讀懂，性能也會越來越差。因此，應(yīng)該避免使用多層嵌套的子查詢。

?

5.2.2 子查詢的名稱

? ? ? ?之前的例子中，我們給子查詢設(shè)定了“ShohinSum”等名稱。為子查詢設(shè)定名稱時需要使用AS關(guān)鍵字，該關(guān)鍵字有時可以省略。

?

?5.2.3 標量子查詢

? ? ? ?標量子查詢就是返回單一值的子查詢，必須而且只能返回一行一列的結(jié)果。

?

? ? ? ? 在WHERE子句中使用標量子查詢：

? ? ? ? 我們需要查詢出銷售單價高于平均銷售單價的商品的做法

代碼15.計算平均銷售單價的標量子查詢

SELECT AVG(hanbai_tanka)FROM Shohin;

輸出結(jié)果：

代碼16.選取出銷售單價（hanbai_tanka）高于全部商品的平均單價商品

SELECT shohin_id,shohin_mei,hanbai_tankaFROM ShohinWHERE hanbai_tanka > (SELECT AVG(hanbai_tanka)FROM Shohin);

輸出結(jié)果：

?

?5.2.4 標量子查詢的書寫位置

? ? ? 標量子查詢的書寫位置不僅局限于WHERE子句中，通常任何可以使用單一值的位置都可以使用。也就是說，能夠使用常數(shù)或者列名的地方，無論是SELECT子句、GROUP BY子句、HAVING子句、還是ORDER BY子句，幾乎所有地方都可以使用。

?

代碼17.在SELECT子句中使用標量子查詢

SELECT shohin_id,shohin_mei,hanbai_tanka,(SELECT AVG(hanbai_tanka)FROM Shohin) AS avg_tankaFROM Shohin;

輸出結(jié)果：

代碼18.在HAVING子句中使用標量子查詢

SELECT shohin_bunrui,AVG(hanbai_tanka)FROM Shohin GROUP BY shohin_bunrui HAVING AVG(hanbai_tanka) > (SELECT AVG(hanbai_tanka)FROM Shohin);

輸出結(jié)果：

?

　　

5.2.5 使用標量子查詢時的注意事項

? ? ? ? 子查詢返回了多行結(jié)果，那么它就不再是標量子查詢，而僅僅是一個普通的子查詢。因此不能被用在=或者<>等需要單一輸入值的運算符當中，也不能在SELECT等子句當中

?

5.3 關(guān)聯(lián)子查詢

5.3.1 普通子查詢和關(guān)聯(lián)子查詢的區(qū)別

代碼19.通過關(guān)聯(lián)子查詢按照商品種類對平均銷售單價進行比較

--SQL、DB2、PostgreSQL，MySQL SELECT shohin_id,shohin_mei,hanbai_tankaFROM Shohin AS S1WHERE hanbai_tanka > (SELECT AVG(hanbai_tanka)FROM Shohin AS S2WHERE S1.shohin_bunrui = S2.shohin_bunruiGROUP BY shohin_bunrui);

輸出結(jié)果：

　　這里起關(guān)鍵作用的地方就是在子查詢中添加的WHERE子句的條件

?

5.3.2 關(guān)聯(lián)子查詢也是用來對集合進行切分的

?

5.3.3 結(jié)合條件一定要寫在子查詢中

? ? ? 子查詢內(nèi)部設(shè)定的關(guān)聯(lián)名稱，只能在該子查詢內(nèi)部使用

? ? ? ? ? ? ? ? ??

? ??

? ? ? ?

?

轉(zhuǎn)載于:https://www.cnblogs.com/zsdeblog/p/9187437.html

總結(jié)

以上是生活随笔為你收集整理的SQL基础教程第五章笔记的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。