Sql Server的藝術（六） SQL 子查詢，創建使用返回多行的子查詢，子查詢創建視圖

子查詢或內部查詢或嵌套查詢在另一個SQL查詢的查詢和嵌入式WHERE子句中。子查詢用於返回將被用於在主查詢作為條件的數據，以進一步限制要檢索的數據。子查詢可以在SELECT，INSERT，UPDATE使用，而且隨著運算符如DELETE語句 =, <, >, >=, <=, IN, BETWEEN ...

子查詢或內部查詢或嵌套查詢在另一個SQL查詢的查詢和嵌入式WHERE子句中。

子查詢用於返回將被用於在主查詢作為條件的數據，以進一步限制要檢索的數據。

子查詢可以在SELECT，INSERT，UPDATE使用，而且隨著運算符如DELETE語句 =, <, >, >=, <=, IN, BETWEEN 等.

這裡有一些規則，子查詢必須遵循：

子查詢必須被圓括弧括起來。
子查詢只能在有一列的SELECT子句中，除非多個列中的子查詢，以比較其選定列主查詢。
ORDER BY不能在子查詢中使用，主查詢可以使用ORDER BY。GROUP BY可以用來在子查詢中如ORDER BY執行相同的功能。
返回多於一個行子查詢只能用於具有多個值運算符，如IN操作。
SELECT列表中不能包含到值計算到任何引用 BLOB, ARRAY, CLOB, 或NCLOB.
子查詢不能立即封閉在一組函數。
BETWEEN 操作符不能與子查詢使用;然而，操作符BETWEEN可以在子查詢中使用。

學習本節所需要的表：

CREATE TABLE TEACHER
(
    ID INT IDENTITY (1,1) PRIMARY KEY ,  --主鍵，自增長
    TNO INT NOT NULL, --教工號
    TNAME CHAR(10) NOT NULL, --教師姓名
    CNO INT NOT NULL, --課程號
    SAL INT, --工資
    DNAME CHAR(10) NOT NULL, --所在系
    TSEX CHAR(2) NOT NULL, --性別
    AGE INT NOT NULL --年齡
)
INSERT INTO dbo.TEACHER VALUES( 1,'王軍',4,400,'數學','男',32)
INSERT INTO dbo.TEACHER VALUES( 2,'李彤',5,6600,'生物','女',54)
INSERT INTO dbo.TEACHER VALUES( 3,'王永軍',1,1000,'電腦','女',45)
INSERT INTO dbo.TEACHER VALUES( 4,'劉曉婧',2,8000,'電腦','女',23)
INSERT INTO dbo.TEACHER VALUES( 5,'高維',8,6000,'電子工程','男',54)
INSERT INTO dbo.TEACHER VALUES( 6,'李偉',7,230,'機械工程','女',23)
INSERT INTO dbo.TEACHER VALUES( 7,'劉輝',3,0,'生物','女',65)
INSERT INTO dbo.TEACHER VALUES( 8,'劉偉',9,500,'電腦','男',23)
INSERT INTO dbo.TEACHER VALUES( 9,'劉靜',12,0,'經濟管理','男',45)
INSERT INTO dbo.TEACHER VALUES( 10,'劉奕鍇',13,70000,'電腦','女',65)
INSERT INTO dbo.TEACHER VALUES( 11,'高維',14,70000,'經濟管理','男',61)

CREATE TABLE COURSE
(
    ID INT IDENTITY (1,1) PRIMARY KEY ,  --主鍵，自增長
    CNO INT NOT NULL, --課程號
    CNAME CHAR(30) NOT NULL, --課程名稱
    CTIME INT NOT NULL, --學時
    SCOUNT INT NOT NULL, --容納人數
    CTEST SMALLDATETIME NOT NULL, --考試時間
)
INSERT INTO dbo.COURSE VALUES( 4,'應用數學基礎',48,120,'2006-7-10')
INSERT INTO dbo.COURSE VALUES( 5,'生物工程概論',32,80,'2006-7-8')
INSERT INTO dbo.COURSE VALUES( 1,'電腦軟體基礎',32,70,'2006-7-8')
INSERT INTO dbo.COURSE VALUES( 2,'電腦硬體基礎',24,80,'2006-6-28')
INSERT INTO dbo.COURSE VALUES( 8,'模擬電路設計',28,90,'2006-7-10')
INSERT INTO dbo.COURSE VALUES( 7,'機械設計實踐',48,68,'2006-7-14')
INSERT INTO dbo.COURSE VALUES( 3,'生物化學',32,40,'2006-7-2')
INSERT INTO dbo.COURSE VALUES( 9,'資料庫設計',16,80,'2006-7-1')
INSERT INTO dbo.COURSE VALUES( 6,'設計理論',28,45,'2006-6-30')
INSERT INTO dbo.COURSE VALUES( 10,'電腦入門',25,150,'2006-6-29')
INSERT INTO dbo.COURSE VALUES( 11,'數字電路設計基礎',30,125,'2006-6-20')
CREATE TABLE STUDENT
(
    ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
    SNO CHAR(4) NOT NULL,    --學號
    SNAME CHAR(10) NOT NULL, --姓名
    DNAME CHAR(10) NOT NULL, --系
    SSEX CHAR(2) NOT NULL,   --性別
    CNO INT ,                --課程號
    MARK DECIMAL(3,1),       --成績
    TYPE CHAR(4)             --課程類型
)
INSERT INTO dbo.STUDENT VALUES('9701','劉建國','管理工程','男',4,82.5,'必修')
INSERT INTO dbo.STUDENT VALUES('9701','劉建國','管理工程','男',10,70,'選修')
INSERT INTO dbo.STUDENT VALUES('9701','劉建國','管理工程','男',1,78.5,'選修')
INSERT INTO dbo.STUDENT VALUES('9702','李春','環境工程','女',5,63,'必修')
INSERT INTO dbo.STUDENT VALUES('9702','李春','環境工程','女',10,58,'選修')
INSERT INTO dbo.STUDENT VALUES('9703','王天','生物','男',5,48.5,'必修')
INSERT INTO dbo.STUDENT VALUES('9703','王天','生物','男',2,86,'選修')
INSERT INTO dbo.STUDENT VALUES('9704','李華','電腦','女',4,76,'必修')
INSERT INTO dbo.STUDENT VALUES('9704','李華','電腦','女',1,92,'必修')
INSERT INTO dbo.STUDENT VALUES('9704','李華','電腦','女',2,89,'必修')
INSERT INTO dbo.STUDENT VALUES('9704','李華','電腦','女',9,80,'必修')
INSERT INTO dbo.STUDENT VALUES('9704','李華','電腦','女',8,70,'選修')
INSERT INTO dbo.STUDENT VALUES('9705','孫慶','電子工程','男',8,79,'必修')
INSERT INTO dbo.STUDENT VALUES('9705','孫慶','電子工程','男',1,59,'必修')
INSERT INTO dbo.STUDENT VALUES('9705','孫慶','電子工程','男',11,52,'必修')
INSERT INTO dbo.STUDENT VALUES('9705','孫慶','電子工程','男',6,68,'必修')
INSERT INTO dbo.STUDENT VALUES('9706','高偉','機械工程','男',13,93,'必修')
INSERT INTO dbo.STUDENT VALUES('9706','高偉','機械工程','男',12,88.5,'必修')
INSERT INTO dbo.STUDENT VALUES('9706','高偉','機械工程','男',1,78,'選修')
INSERT INTO dbo.STUDENT VALUES('9706','高偉','機械工程','男',10,76,'選修')

數據表

表數據

--在多表查詢中使用子查詢
    --採用連接表的方法
    SELECT s.CNO,SNO,SNAME,DNAME FROM dbo.STUDENT AS s INNER JOIN dbo.COURSE AS c ON s.CNO = c.CNO WHERE c.CNAME='電腦入門'

    --採用子查詢
    SELECT CNO,SNO,SNAME,DNAME FROM dbo.STUDENT WHERE CNO=(SELECT CNO FROM dbo.COURSE WHERE CNAME='電腦入門')

--在子查詢中使用聚合函數
    --使用平均值函數
    SELECT * FROM dbo.TEACHER WHERE AGE>(SELECT AVG(AGE) FROM dbo.TEACHER)

    --比較判式兩邊均採用聚合分析的字查詢
　　 --TEACHER中查詢教師的教工號等信息，並且要求教師所在系的平均年齡大於所有老師的平均年齡
    SELECT t.TNO,t.TNAME,t.DNAME,t.CNO,t.AGE FROM dbo.TEACHER AS t WHERE (SELECT AVG(AGE) FROM dbo.TEACHER WHERE DNAME=t.DNAME)>(SELECT AVG(AGE) FROM dbo.TEACHER)

　　 --在where子句中使用子查詢
　　 --在TEACHER中查詢所有教師的教工號等，以及在STUDENT中修了這門課程的學生人數
    SELECT TNO,TNAME,DNAME,CNO,(SELECT COUNT(*) FROM dbo.STUDENT WHERE CNO=dbo.TEACHER.CNO) AS S_NUN FROM dbo.TEACHER ORDER BY S_NUN

　　 介紹一下代碼的執行過程
    1、首先DBMS讀取TEACHER表中的一行教師信息數據，來執行主查詢。
    SELECT TNO,TNAME,DNAME,CNO FROM dbo.TEACHER
    2、在從TEACHER表中提取了教師信息之後，DBMS將該行的TNO、TNAME、DNAME、CNO欄位信息添加到結果表中，然後執行子查詢獲取STUDENT表中修改該教師開設課程的學生人數。
    (SELECT COUNT(*) FROM dbo.STUDENT WHERE CNO=dbo.TEACHER.CNO)
    3、在子查詢的WHERE子句中，CNO為子查詢的表STUDENT表中的列，而TEACHER.CNO為TEACHER表中的CNO列.他們的列名相同,因此對於子查詢外表(即TEACHER表)中的CNO列我們一定要指明表名。
    註意：在SELECT子句中使用子查詢時，子查詢必須返回單值。

--創建和使用返回多行的子查詢
   --一個子查詢除了可以產生一個單值外，也可以產生一個關係，該關係可以包括若幹元組。SQL提供了若幹對於關係的操作符，主要包括：IN、EXISTS、SOME（ANY）、ALL、UNIQUE等。
    --IN子查詢
    用法：
    SELECT column_name
    FROM table_name
    WHERE test expression [NOT] IN (subquery)
    test expression可以是實際值、列名、表達式或是另一個返回單一值的子查詢。IN運算符前面加上NOT關鍵字，表示與集合成員不匹配時，NOT　IN判別式求值為True。

    --查詢不及格的課程的姓名、系、所有的課程及成績信息
    SELECT s1.SNAME,s1.DNAME,s1.CNO,s1.MARK,s1.SNO FROM dbo.STUDENT AS s1,dbo.STUDENT AS s2 WHERE s1.SNO=s2.SNO AND s1.MARK<60 ORDER BY s1.SNAME
    --採用IN子查詢的寫法
    SELECT SNAME,DNAME,CNO,MARK,SNO FROM dbo.STUDENT WHERE SNO IN(SELECT SNO FROM dbo.STUDENT WHERE MARK<60) ORDER BY SNAME

　　 分析代碼執行的過程
    1、執行一個查詢時，DBMS首先處理最裡面的子查詢。系統首先執行"SELECT SNO FROM dbo.STUDENT WHERE MARK<60"語句，生成STUDENT表中成績小於60的學生的學號SNO集。
    2、DBMS一次處理STUDENT表中的一行記錄，並且將每行記錄中的SNO列值與子查詢結果集中的SNO值進行比較。
    3、如果在系統子查詢結果中找到了與正在處理的記錄中的SNO值相匹配的值，WHERE子句求值為True，DBMS則將該記錄的相關信息歸入結果表。
    4、如果在子查詢結果集中沒有發現與記錄的SNO值相匹配的值，WHERE子句求值為False，DBMS轉而去處理STUDENT表中的下一行而捨棄該行數據。

--IN子查詢實現集合交和集合差運算
    --採用IN子查詢實現集合交運算
    SELECT CNO,SNO,SNAME,DNAME FROM dbo.STUDENT WHERE CNO=1 AND SNO IN(SELECT SNO FROM dbo.STUDENT WHERE CNO=10)     --查詢修過1號或者10號課程的學生信息--採用IN子查詢實現集合差運算
    SELECT CNO,SNO,SNAME,DNAME FROM dbo.STUDENT WHERE CNO=1 AND SNO NOT IN(SELECT SNO FROM dbo.STUDENT WHERE CNO=10)     --查詢修過1號但是沒有修10號課程的學生信息
　　 
    註意:由運算符IN引入的子查詢返回的列值,既可以來自主查詢的表,也可以來自其他表。SQL對查詢的唯一要求就是它必須返回單一列的數據值，並且其數據類型必須與IN前面的表達式的數據類型相相容。

--EXISTS子查詢
   --前言：在某些情況下，我們只需要子查詢返回一個True或者False，子查詢數據內容本身並不重要，這時，可使用EXISTS判式來定義子查詢。EXISTS判式用來測試集合是否為空，它總是與子查詢結合使用，而且只要子查詢中至少返回一個值，EXISTS判式的值就為True。如果子查詢的表中沒有值（表中沒有行滿足子查詢的WHERE子句的搜索條件），那麼EXISTS判式的值為False。
    用法：
    SELECT column_name
    FROM table_name
    WHERE [NOT] EXISTS (subquery)
    只要子查詢(subquery)中返回的結果表存在記錄，EXISTS (subquery)就返回True，WHERE子句的結果也就為True。如果使用[NOT] EXISTS判式，當子查詢返回的結果沒有記錄行時，其返回True。
    --使用EXISTS子查詢
    SELECT TNO,TNAME,DNAME,CNO FROM dbo.TEACHER AS t WHERE EXISTS(SELECT * FROM dbo.STUDENT WHERE CNO=t.CNO) ORDER BY t.TNO   --查詢開設的課程有學生修過的教師信息

　　 --使用NOT EXISTS子查詢
    SELECT CNO,CNAME,CTIME,CTEST FROM dbo.COURSE AS c WHERE NOT EXISTS(SELECT * FROM dbo.STUDENT WHERE CNO=c.CNO)    --查詢學生表中未被選修的課程信息
    --使用IN子查詢:
    SELECT CNO,CNAME,CTIME,CTEST FROM dbo.COURSE WHERE CNO NOT IN(SELECT CNO FROM dbo.STUDENT)

--EXISTS子查詢實現兩表交集
    SELECT CNO,TNAME,DNAME FROM dbo.TEACHER WHERE TSEX='女' AND EXISTS(SELECT * FROM dbo.COURSE WHERE MONTH(CTEST)=7 AND CNO=dbo.TEACHER.CNO) ORDER BY CNO   --女教師開設7月份課程的交集
    --也可以用INNER JOIN
    SELECT t.CNO,t.TNAME,t.DNAME FROM dbo.TEACHER AS t INNER JOIN dbo.COURSE AS c ON c.CNO = t.CNO AND t.TSEX='女' AND MONTH(c.CTEST)=7 ORDER BY c.CNO

--SOME/ALL子查詢
   --只要我們使用了SQL比較運算符（=、<、>、<=、>=）中的一個來比較兩個表達式的值，那麼運算符前後的表達式都必須為單一值。而數量詞SOME、ANY、ALL則允許使用比較運算符將單值與子查詢返回的值加以比較，這裡的子查詢返回的結果可以是多行的。
    用法：
    SELECT column_name
    FROM table_name
    WHERE test expression # SOME/ALL (subquery)
    test expression可以是實際值、列名、表達式或是另一個返回單一的子查詢。

SOME/ALL關鍵字與比較運算符的聯合使用
表達式	含義
>SOME	大於子查詢結果中的某個值
>ALL	大於子查詢結果中的所有值
<SOME	小於子查詢結果中的某個值
<ALL	小於子查詢結果中的所有值
>=SOME	大於等於子查詢結果中的某個值
>=ALL	大於等於子查詢結果中的所有值
<=SOME	小於等於子查詢結果中的某個值
<=ALL	小於等於子查詢結果中的所有值
=SOME	等於子查詢結果中的某個值
=ALL	等於子查詢結果中的所有值（通常沒有實際意義）
!=SOME	不等於子查詢結果中的某個值
!=ALL	不等於子查詢結果中的任何一個值

　　 --SOME/ALL子查詢
    SELECT SNO,AVG(MARK) AS 成績 FROM dbo.STUDENT GROUP BY SNO HAVING AVG(MARK)>=ALL(SELECT AVG(MARK) FROM dbo.STUDENT GROUP BY SNO)  --平均成績最高的學生

　　 分析執行過程：
    1、執行查詢時，DBMS首先根據學號SNO，將STUDENT表中的數據分組，得到分組後的中間表。
    2、接著執行HAVING子句，從分組後的中間表中取出一組，執行AVG(MARK)聚合函數，得到平均成績。
    3、而後系統開始執行子查詢，子查詢返回的結果為STUDENT表中每個同學的平均成績。
    4、如果2取出的這組同學的平均成績小於3得到的結果表中的所有記錄，則DBMS轉而處理下一組數據；而如果滿足條件，則DBMS將該組記錄的學號和平均成績添加到主查詢的結果表中，而後繼續處理下一組數據。
　　 註意：關鍵字SOME和關鍵字ANY具有完全相同的功能

   --SOME/ALL子查詢的使用
    --查詢其他系中比經濟管理系中所有教師年齡都小的教師信息
    SELECT TNO,TNAME,DNAME,AGE FROM dbo.TEACHER WHERE AGE<ALL(SELECT AGE FROM dbo.TEACHER WHERE DNAME='經濟管理  ') AND DNAME<>'經濟管理  ' ORDER BY AGE
    --採用聚集函數來實現
    SELECT TNO,TNAME,DNAME,AGE FROM dbo.TEACHER WHERE AGE<(SELECT MIN(AGE) FROM dbo.TEACHER WHERE DNAME='經濟管理') AND DNAME<>'經濟管理' ORDER BY AGE

　　　　實際上，聚集函數實現子查詢通常比直接用SOME或All查詢效率更高。SOME和ALL與聚集函數關係如下：

SOME和ALL與聚集函數的對應關係
表達式	對應的聚集函數
>SOME	>MIN
>ALL	>MAX
<SOME	<MIN
<ALL	<MAX
>=SOME	>=MIN
>=ALL	>=MAX
<=SOME	<=MAX
<=ALL	<=MIN
=SOME	IN
=ALL	無
!=SOME	無
!=ALL	NOT IN

--相關子查詢
   --由前面介紹的一些子查詢我們不難發現，有些複雜的子查詢需要執行若幹次，因為每次執行時，都需要來自子查詢外部的元組變數的值，也就是說，子查詢的執行要依賴於上層查詢元組的當前值，我們將這種子查詢稱之為相關子查詢。
    --使用IN引入相關子查詢
    SELECT CNO,CNAME,CTIME,CTEST FROM dbo.COURSE WHERE '李華' IN (SELECT SNAME FROM dbo.STUDENT WHERE CNO=dbo.COURSE.CNO)   --查詢李華的信息
    --採用表的連接
    SELECT c.CNO,c.CNAME,c.CTIME,c.CTEST FROM dbo.COURSE AS c INNER JOIN dbo.STUDENT AS s ON s.CNO = c.CNO WHERE s.SNAME='李華' ORDER BY c.CNO

　　 --比較運算符引入相關子查詢
    SELECT s.SNAME,s.DNAME,s.CNO,s.MARK FROM dbo.STUDENT AS s WHERE (SELECT CTEST FROM dbo.COURSE WHERE CNO=s.CNO)<CAST('2006-7-2' AS SMALLDATETIME) ORDER BY s.SNAME  --查詢時間小於2006-7-2，的信息
    --採用表的連接
    SELECT s.SNAME,s.DNAME,s.CNO,s.MARK FROM dbo.STUDENT AS s INNER JOIN dbo.COURSE AS c ON c.CNO = s.CNO WHERE CTEST<'2006-7-2'  ORDER BY s.SNAME

註意：二者實現功能一樣，採用表連接的方式對數據的查詢更加靈活。因為採用相關子查詢，所查詢的列只能是主查詢中FROM子句的列，不能查其他列，而採用多表連接則不存在這個問題。

   --在HAVING子句中使用相關子查詢
    --在老師表中查詢包含的系的名稱，要求這些系中，各系包含的教師數目要少於學生表中修改該系的教師開設課程的學生的人次。
    --假如老師表中，A系有m個教師，而在學生表中有n人次修過這m個教師開設的課程，如果n>m，則A系就符合查詢條件。
    SELECT t.DNAME FROM dbo.TEACHER AS t GROUP BY t.DNAME HAVING COUNT(*)<(SELECT COUNT(*) FROM dbo.STUDENT WHERE CNO IN(SELECT CNO FROM dbo.TEACHER AS t2 WHERE t2.DNAME=t.DNAME)) ORDER BY t.DNAME

　　 分析執行過程：
    1、執行該select語句時，DBMS首先執行FROM和GROUP BY子句，將TEACHER表中的記錄依據DNAME欄位進行分組，形成中間表（虛擬表）。該中間表從概念上可理解為圖一，其中共有6個分組。

    2、接著系統處理來自中間表的一個分組信息，即依次對每個系名DNAME執行HAVING子句，這裡假定取出第一組，即DNAME為‘電子工程’。
    3、HAVING子句中，比較運算符右邊是一個相關子查詢，而相關子查詢中又含有一個子查詢。系統將從最底層的子查詢“SELECT CNO FROM dbo.TEACHER AS t2 WHERE t2.DNAME=t.DNAME”開始執行，即查詢TEACHER表中電子工程系的所有教師所授課程的課程號，從圖一見，只有一個，即‘8’。
    4、而後系統將執行其外層的相關子查詢，即計算STUDENT表中所有修過8號課程的記錄數量。這時執行如下語句：SELECT COUNT(*) FROM dbo.STUDENT WHERE CNO IN(8),如圖結果：

     所以最終相關子查詢的結果是2，而比較運算符左邊為計算DNAME為‘電子工程’這組的記錄條目，從圖一知，只有一個記錄，結果為1。顯然1<2的判別式成立，HAVING子句返回TRUE，‘電子工程’就被寫入結果表中。接著系統按照同樣的方式依次處理圖一中的第2組、第3組......

--嵌套子查詢
   --位於子查詢內的子查詢被稱為嵌套的子查詢--在COURSE中查詢電腦系教師開設課程信息，並且要求在STUDENT中所有修過這門課程的同學的成績都及格
    SELECT * FROM dbo.COURSE WHERE CNO IN (SELECT CNO FROM dbo.TEACHER WHERE DNAME='電腦' AND NOT EXISTS(SELECT * FROM dbo.STUDENT WHERE CNO=dbo.TEACHER.CNO AND MARK<60)) ORDER BY CNO
    --通過兩個子查詢來實現
    SELECT * FROM dbo.COURSE WHERE CNO IN(SELECT CNO FROM dbo.TEACHER WHERE DNAME='電腦') AND NOT EXISTS (SELECT * FROM dbo.STUDENT WHERE CNO=dbo.COURSE.CNO AND MARK<60) ORDER BY CNO

　　分析：在主查詢WHERE子句中通過IN關鍵字引入一個子查詢，該子查詢實現的功能是：搜索那些在TEACHER表中由電腦系教師開設的、並且在STUDENT表中沒有同學不及格的課程號；而在該子查詢內SELECT語句的WHERE子句中，又通過NOT EXISTS關鍵詞引入了一個子查詢，該子查詢即為嵌套子查詢，用來判斷電腦系教師開設的課程在STUDENT中是否有同學不及格。

--使用子查詢創建視圖
   CREATE VIEW <view_name> [(column1,column2...)]
    AS
    SELECT <column_name>
    FROM <table_name>
    前面博客有講到視圖的用法，可以去看http://www.cnblogs.com/liuchenxing/p/8251932.html

　　 --創建視圖，包含學生表中課程號和課程平均成績
    CREATE VIEW VIEW_AVGMARK(CNO,AVG_MARK)
    AS
    SELECT CNO,AVG(MARK) FROM dbo.STUDENT GROUP BY CNO
    --查詢這個視圖
    SELECT * FROM VIEW_AVGMARK

　　 --在COURSE表中查詢在STUDENT中平均成績高於70的課程的課程號和信息
    SELECT CNO,CNAME,CTEST FROM dbo.COURSE WHERE CNO IN (SELECT CNO FROM VIEW_AVGMARK WHERE AVG_MARK>70) ORDER BY CNO

　　 --不採用視圖，對比結果，用視圖顯得更直觀，更簡化語句
    SELECT CNO,CNAME,CTEST FROM dbo.COURSE WHERE CNO IN (SELECT CNO FROM dbo.STUDENT GROUP BY CNO HAVING AVG(MARK)>70) ORDER BY CNO