3.4　數據查詢

◆語句格式

3.4.1　單表查詢

視頻二維碼（掃碼觀看）

◆查詢僅涉及一個表

一、選擇表中的若干列

1查詢指定列

【例1】查詢全體學生的學號與姓名。

SELECT Sno，Sname FROM Student；

【例2】查詢全體學生的姓名、學號、所在系。

SELECT Sname，Sno，Sdept FROM Student；

2查詢全部列

◆選出所有屬性列：

在SELECT關鍵字后面列出所有列名，將<目標列表達式>指定為*

【例3】查詢全體學生的詳細記錄。

SELECT Sno，Sname，Ssex，Sage，Sdept FROM Student；

或

SELECT * FROM Student；

3查詢經過計算的值

◆SELECT子句的<目標列表達式>可以為：

（1）算術表達式

（2）字符串常量

（3）函數

（4）列別名

【例4】查全體學生的姓名及其出生年份。

SELECT Sname，2004－Sage　/*假定當年的年份為2004年*/

FROM Student；

輸出結果：

【例5】查詢全體學生的姓名、出生年份和所有系，要求用小寫字母表示所有系名

SELECT Sname，‘Year of Birth：’，2004－Sage，ISLOWER（Sdept） FROM Student；

輸出結果：

◆使用列別名改變查詢結果的列標題：

SELECT Sname NAME，‘Year of Birth：’BIRTH，2000－Sage BIRTHDAY，LOWER（Sdept）DEPARTMENT FROM Student；

輸出結果：

二、選擇表中的若干元組

1消除取值重復的行

如果沒有指定DISTINCT關鍵詞，則缺省為ALL。

【例6】查詢選修了課程的學生學號。

SELECT Sno FROM SC；

等價于：

SELECT ALL Sno FROM SC；

執行上面的SELECT語句后，結果為：

Sno

200215121

200215121

200215121

200215122

200215122

◆指定DISTINCT關鍵詞，去掉表中重復的行

SELECT DISTINCT Sno FROM SC；

執行結果：

Sno

200215121

200215122

2查詢滿足條件的元組

表3-4　常用的查詢條件

（1）比較大小

【例7】查詢計算機科學系全體學生的名單。

SELECT Sname FROM Student WHERE Sdept＝‘CS’；

【例8】查詢所有年齡在20歲以下的學生姓名及其年齡。

SELECT Sname，Sage FROM Student WHERE Sage＜20；

【例9】查詢考試成績有不及格的學生的學號。

SELECT DISTINCT Sno FROM SC WHERE Grade＜60；

（2）確定范圍

◆謂詞：

BETWEEN…AND…

NOT BETWEEN…AND…

【例10】查詢年齡在20～23歲（包括20歲和23歲）之間的學生的姓名、系別和年齡。

SELECT Sname，Sdept，Sage FROM Student WHERE Sage BETWEEN 20 AND 23；

【例11】查詢年齡不在20～23歲之間的學生姓名、系別和年齡。

SELECT Sname，Sdept，Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23；

（3）確定集合

◆謂詞：IN<值表>，NOT IN<值表>

【例12】查詢信息系（IS）、數學系（MA）和計算機科學系（CS）學生的姓名和性別。

SELECT Sname，Ssex FROM Student WHERE Sdept IN（‘IS’，‘MA’，‘CS’）；

【例13】查詢既不是信息系、數學系，也不是計算機科學系的學生的姓名和性別。

SELECT Sname，Ssex FROM Student WHERE Sdept NOT IN（‘IS’，‘MA’，‘CS’）；

（4）字符匹配

◆謂詞：[NOT] LIKE‘<匹配串>’[ESCAPE‘<換碼字符>’]

①匹配串為固定字符串

【例14】查詢學號為200215121的學生的詳細情況。

SELECT * FROM Student WHERE Sno LIKE‘200215121’；

等價于：

SELECT * FROM Student WHERE Sno＝‘200215121’；

②匹配串為含通配符的字符串

【例15】查詢所有姓劉學生的姓名、學號和性別。

SELECT Sname，Sno，Ssex FROM Student WHERE Sname LIKE‘劉%’；

【例16】查詢姓“歐陽”且全名為三個漢字的學生的姓名。

SELECT Sname FROM Student WHERE Sname LIKE‘歐陽__’；

【例17】查詢名字中第2個字為“陽”字的學生的姓名和學號。

SELECT Sname，Sno FROM Student WHERE Sname LIKE‘__陽%’；

【例18】查詢所有不姓劉的學生姓名。

SELECT Sname，Sno，Ssex FROM Student WHERE Sname NOT LIKE‘劉%’；

③使用換碼字符將通配符轉義為普通字符

【例19】查詢DB_Design課程的課程號和學分。

SELECT Cno，Ccredit FROM Course WHERE Cname LIKE‘DB\_Design’ESCAPE‘\’；

【例20】查詢以“DB_”開頭，且倒數第3個字符為i的課程的詳細情況。

SELECT * FROM Course WHERE Cname LIKE‘DB\_%i_ _’ESCAPE‘\’；

ESCAPE‘\’表示“\”為換碼字符。

（5）涉及空值的查詢

謂詞：IS NULL或IS NOT NULL

“IS”不能用“＝”代替

【例21】某些學生選修課程后沒有參加考試，所以有選課記錄，但沒有考試成績。查詢缺少成績的學生的學號和相應的課程號。

SELECT Sno，Cno FROM SC WHERE Grade IS NULL；

【例22】查所有有成績的學生學號和課程號。

SELECT Sno，Cno FROM SC WHERE Grade IS NOT NULL；

（6）多重條件查詢

◆邏輯運算符：AND和OR來聯結多個查詢條件

AND的優先級高于OR

可以用括號改變優先級

◆可用來實現多種其他謂詞

[NOT] IN

[NOT] BETWEEN … AND …

【例23】查詢計算機系年齡在20歲以下的學生姓名。

SELECT Sname FROM Student WHERE Sdept＝‘CS’AND Sage＜20；

【改寫例12】查詢信息系（IS）、數學系（MA）和計算機科學系（CS）學生的姓名和性別。

SELECT Sname，Ssex FROM Student WHERE Sdept IN（‘IS’，‘MA’，‘CS’）；

可改寫為：

SELECT Sname，Ssex FROM Student WHERE Sdept＝‘IS’OR Sdept＝‘MA’OR Sdept＝‘CS’；

三、ORDER BY子句

ORDER BY子句可以按一個或多個屬性列排序：

升序：ASC；

降序：DESC；

缺省值為升序。

當排序列含空值時：（控制默認為最大值）

ASC：排序列為空值的元組最后顯示

DESC：排序列為空值的元組最先顯示

【例24】查詢選修了3號課程的學生的學號及其成績，查詢結果按分數降序排列。

SELECT Sno，Grade FROM SC WHERE Cno＝‘3’ ORDER BY Grade DESC；

【例25】查詢全體學生情況，查詢結果按所在系的系號升序排列，同一系中的學生按年齡降序排列。

SELECT * FROM Student ORDER BY Sdept，Sage DESC；

四、聚集函數

1計數

COUNT（[DISTINCT|ALL] *）

COUNT（[DISTINCT|ALL] <列名>）

2計算總和

SUM（[DISTINCT|ALL] <列名>）

3計算平均值

AVG（[DISTINCT|ALL] <列名>）

4最大最小值

MAX（[DISTINCT|ALL] <列名>）

MIN（[DISTINCT|ALL] <列名>）

【例26】查詢學生總人數。

SELECT COUNT（*） FROM Student；

【例27】查詢選修了課程的學生人數。

SELECT COUNT（DISTINCT Sno） FROM SC；

【例28】計算1號課程的學生平均成績。

SELECT AVG（Grade） FROM SC WHERE Cno＝‘1’；

【例29】查詢選修1號課程的學生最高分數。

SELECT MAX（Grade） FROM SC WHERE Cno＝‘1’；

【例30】查詢學生200215012選修課程的總學分數。

SELECT SUM（Ccredit） FROM SC，Course WHERE Sno＝‘200215012’AND SC.Cno＝Course.Cno；

五、GROUP BY子句

GROUP BY子句分組：細化聚集函數的作用對象

◆未對查詢結果分組，聚集函數將作用于整個查詢結果

◆對查詢結果分組后，聚集函數將分別作用于每個組

◆作用對象是查詢的中間結果表

◆按指定的一列或多列值分組，值相等的為一組

【例31】求各個課程號及相應的選課人數。

SELECT Cno，COUNT（Sno） FROM SC GROUP BY Cno；

查詢結果：

【例32】查詢選修了3門以上課程的學生學號。

SELECT Sno FROM SC GROUP BY Sno HAVING COUNT（*）＞3；

HAVING短語與WHERE子句的區別：

◆作用對象不同；

◆WHERE子句作用于基表或視圖，從中選擇滿足條件的元組；

◆HAVING短語作用于組，從中選擇滿足條件的組。

3.4.2　連接查詢

視頻二維碼（掃碼觀看）

◆連接查詢：同時涉及多個表的查詢

◆連接條件或連接謂詞：用來連接兩個表的條件一般格式：

·[<表名1>]<列名1> <比較運算符> [<表名2>]<列名2>；

·[<表名1>]<列名1> BETWEEN [<表名2>]<列名2> AND [<表名2>]<列名3>；

◆連接字段：連接謂詞中的列名稱

·連接條件中的各連接字段類型必須是可比的，但名字不必是相同的

連接操作的執行過程

◆嵌套循環法（NESTED-LOOP）

·首先在表1中找到第一個元組，然后從頭開始掃描表2，逐一查找滿足連接件的元組，找到后就將表1中的第一個元組與該元組拼接起來，形成結果表中一個元組。

·表2全部查找完后，再找表1中第二個元組，然后再從頭開始掃描表2，逐一查找滿足連接條件的元組，找到后就將表1中的第二個元組與該元組拼接起來，形成結果表中一個元組。

·重復上述操作，直到表1中的全部元組都處理完畢。

◆排序合并法（SORT-MERGE）

常用于＝連接

·首先按連接屬性對表1和表2排序。

·對表1的第一個元組，從頭開始掃描表2，順序查找滿足連接條件的元組，找到后就將表1中的第一個元組與該元組拼接起來，形成結果表中一個元組。當遇到表2中第一條大于表1連接字段值的元組時，對表2的查詢不再繼續。

·找到表1的第二條元組，然后從剛才的中斷點處繼續順序掃描表2，查找滿足連接條件的元組，找到后就將表1中的第一個元組與該元組拼接起來，形成結果表中一個元組。直接遇到表2中大于表1連接字段值的元組時，對表2的查詢不再繼續。

·重復上述操作，直到表1或表2中的全部元組都處理完畢為止。

◆索引連接（INDEX-JOIN）

·對表2按連接字段建立索引，對表1中的每個元組，依次根據其連接字段值查詢。

·表2的索引，從中找到滿足條件的元組，找到后就將表1中的第一個元組與該元組拼接起來，形成結果表中一個元組。

一、等值與非等值連接查詢

◆等值連接

連接運算符為“＝”

【例33】查詢每個學生及其選修課程的情況

SELECT Student.*，SC.* FROM Student，SC WHERE Student.Sno＝SC.Sno；

查詢結果：

◆自然連接

【例34】對【例33】用自然連接完成。

SELECT Student.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade FROM Student，SC WHERE Student.Sno＝SC.Sno；

二、自身連接

◆自身連接：一個表與其自己進行連接

◆需要給表起別名以示區別

◆由于所有屬性名都是同名屬性，因此必須使用別名前綴

【例35】查詢每一門課的間接先修課（即先修課的先修課）

SELECT FIRST.Cno，SECOND.Cpno FROM Course FIRST，Course SECOND WHERE FIRST.Cpno＝SECOND.Cno；

FIRST表（Course表）

SECOND表（Course表）

查詢結果：

三、外連接

◆外連接與普通連接的區別

·普通連接操作只輸出滿足連接條件的元組。

·外連接操作以指定表為連接主體，將主體表中不滿足連接條件的元組一并輸出。

【例36】改寫【例33】

SELECT Student.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade FROM Student LEFT OUT JOIN SC ON（Student.Sno＝SC.Sno）；

執行結果：

左外連接

·列出左邊關系（如本例Student）中所有的元組

右外連接

·列出右邊關系中所有的元組

四、復合條件連接

復合條件連接：WHERE子句中含多個連接條件。

【例37】查詢選修2號課程且成績在90分以上的所有學生。

【例38】查詢每個學生的學號、姓名、選修的課程名及成績。

3.4.3　嵌套查詢

視頻二維碼（掃碼觀看）

◆嵌套查詢概述

·一個SELECT-FROM-WHERE語句稱為一個查詢塊。

·將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢。

·子查詢的限制：不能使用ORDER BY子句。

·層層嵌套方式反映了SQL語言的結構化。

·有些嵌套查詢可以用連接運算替代。

嵌套查詢求解方法

◆不相關子查詢：子查詢的查詢條件不依賴于父查詢。

·由里向外逐層處理。即每個子查詢在上一級查詢處理之前求解，子查詢的結果用于建立其父查詢的查找條件。

◆相關子查詢：子查詢的查詢條件依賴于父查詢。

·首先取外層查詢中表的第一個元組，根據它與內層查詢相關的屬性值處理內層查詢，若WHERE子句返回值為真，則取此元組放入結果表。

·然后再取外層表的下一個元組。

·重復這一過程，直至外層表全部檢查完為止。

一、帶有IN謂詞的子查詢

【例39】查詢與“劉晨”在同一個系學習的學生。

此查詢要求可以分步來完成

①確定“劉晨”所在系名

SELECT Sdept FROM Student WHERE Sname＝‘劉晨’；

結果為：CS

②查找所有在CS系學習的學生。

SELECT Sno，Sname，Sdept FROM Student WHERE Sdept＝‘CS’；

結果為：

將第一步查詢嵌入到第二步查詢的條件中

此查詢為不相關子查詢。

用自身連接完成【例39】查詢要求。

【例40】查詢選修了課程名為“信息系統”的學生學號和姓名。

用連接查詢實現【例40】

二、帶有比較運算符的子查詢

當能確切知道內層查詢返回單值時，可用比較運算符（＞，＜，＝，＞＝，＜＝，！＝或＜＞）。

與ANY或ALL謂詞配合使用。

例：假設一個學生只可能在一個系學習，并且必須屬于一個系，則在【例39】可以用＝代替IN：

子查詢一定要跟在比較符之后。

錯誤的例子：

【例41】找出每個學生超過他選修課程平均成績的課程號。

可能的執行過程：

1．從外層查詢中取出SC的一個元組x，將元組x的Sno值（200215121）傳送給內層查詢。

SELECT AVG（Grade） FROM SC y WHERE y.Sno＝‘200215121’；

2．執行內層查詢，得到值88（近似值），用該值代替內層查詢，得到外層查詢：

SELECT Sno，Cno FROM SC x WHERE Grade＞＝88；

3．執行這個查詢，得到：

（200215121，1）

（200215121，3）

4．外層查詢取出下一個元組重復做上述1至3步驟，直到外層的SC元組全部處理完畢。結果為：

（200215121，1）

（200215121，3）

（200215122，2）

三、帶有ANY（SOME）或ALL謂詞的子查詢

謂詞語義

·ANY：任意一個值

·ALL：所有值

需要配合使用比較運算符：

＞ANY　大于子查詢結果中的某個值；

＞ALL　大于子查詢結果中的所有值；

＜ANY　小于子查詢結果中的某個值；

＜ALL　小于子查詢結果中的所有值；

＞＝ANY　大于等于子查詢結果中的某個值；

＞＝ALL　大于等于子查詢結果中的所有值；

＜＝ANY　小于等于子查詢結果中的某個值；

＜＝ALL　小于等于子查詢結果中的所有值；

＝ANY　等于子查詢結果中的某個值；

＝ALL　等于子查詢結果中的所有值（通常沒有實際意義）；

！＝（或＜＞）ANY　不等于子查詢結果中的某個值；

！＝（或＜＞）ALL　不等于子查詢結果中的任何一個值。

【例42】查詢其他系中比計算機科學某一學生年齡小的學生姓名和年齡。

結果：

執行過程：

（1）RDBMS執行此查詢時，首先處理子查詢，找出CS系中所有學生的年齡，構成一個集合（20，19）；

（2）處理父查詢，找所有不是CS系且年齡小于20或19的學生。

用聚集函數實現【例42】

【例43】查詢其他系中比計算機科學系所有學生年齡都小的學生姓名及年齡。

方法一：用ALL謂詞

方法二：用聚集函數

表3-5　ANY（或SOME）、ALL謂詞與聚集函數、IN謂詞的等價轉換關系

四、帶有EXISTS謂詞的子查詢

1EXISTS謂詞

·存在量詞?。

·帶有EXISTS謂詞的子查詢不返回任何數據，只產生邏輯真值“true”或邏輯假值“false”。

若內層查詢結果非空，則外層的WHERE子句返回真值；

若內層查詢結果為空，則外層的WHERE子句返回假值。

·由EXISTS引出的子查詢，其目標列表達式通常都用*，因為帶EXISTS的子查詢只返回真值或假值，給出列名無實際意義。

2NOT EXISTS謂詞

若內層查詢結果非空，則外層的WHERE子句返回假值；

若內層查詢結果為空，則外層的WHERE子句返回真值。

【例44】查詢所有選修了1號課程的學生姓名。

思路分析：

·本查詢涉及Student和SC關系；

·在Student中依次取每個元組的Sno值，用此值去檢查SC關系；

·若SC中存在這樣的元組，其Sno值等于此Student.Sno值，并且其Cno＝‘1’，則取此Student.Sname送入結果關系。

用嵌套查詢：

用連接運算：

【例45】查詢沒有選修1號課程的學生姓名。

◆不同形式的查詢間的替換

·一些帶EXISTS或NOT EXISTS謂詞的子查詢不能被其他形式的子查詢等價替換；

·所有帶IN謂詞、比較運算符、ANY和ALL謂詞的子查詢都能用帶EXISTS謂詞的子查詢等價替換。

◆用EXISTS/NOT EXISTS實現全稱量詞（難點）

SQL語言中沒有全稱量詞?（For all）。

可以把帶有全稱量詞的謂詞轉換為等價的帶有存在量詞的謂詞：（?x）P≡￢（?x（￢P））。

例：【例39】查詢與“劉晨”在同一個系學習的學生。

可以用帶EXISTS謂詞的子查詢替換：

【例46】查詢選修了全部課程的學生姓名。

◆用EXISTS/NOT EXISTS實現邏輯蘊函（難點）

·SQL語言中沒有蘊函（Implication）邏輯運算。

·可以利用謂詞演算將邏輯蘊函謂詞等價轉換為：p→q≡￢p∨q。

【例47】查詢至少選修了學生200215122選修的全部課程的學生號碼。

解題思路：

·用邏輯蘊函表達：查詢學號為x的學生，對所有的課程y，只要200215122學生選修了課程y，則x也選修了y。

·形式化表示：

用P表示謂詞“學生200215122選修了課程”。

用q表示謂詞“學生x選修了課程y”。

則上述查詢為：（?y）p→q。

·等價變換：

（?y）p→q≡￢（?y（￢（p→q））≡￢（?y（￢（￢p∨q）））≡￢?y（p∧￢q）。

·變換后語義：不存在這樣的課程y，學生200215122選修了y，而學生x沒有選。

用NOT EXISTS謂詞表示：

3.4.4　集合查詢

視頻二維碼（掃碼觀看）

集合操作的種類

·并操作UNION

·交操作INTERSECT

·差操作EXCEPT

參加集合操作的各查詢結果的列數必須相同；對應項的數據類型也必須相同

【例48】查詢計算機科學系的學生及年齡不大于19歲的學生。

方法一：

SELECT * FROM Student WHERE Sdept＝‘CS’ UNION SELECT * FROM Student WHERE Sage＜＝19；

UNION：將多個查詢結果合并起來時，系統自動去掉重復元組。

UNION ALL：將多個查詢結果合并起來時，保留重復元組。

方法二：

SELECT DISTINCT * FROM Student WHERE Sdept＝‘CS’OR Sage＜＝19；

【例49】查詢選修了課程1或者選修了課程2的學生。

SELECT Sno FROM SC WHERE Cno＝‘1’ UNION SELECT Sno FROM SC WHERE Cno＝‘2’；

【例50】查詢計算機科學系的學生與年齡不大于19歲的學生的交集。

SELECT * FROM Student WHERE Sdept＝‘CS’ INTERSECT SELECT * FROM Student WHERE Sage＜＝19

上述例子實際上就是查詢計算機科學系中年齡不大于19歲的學生。

SELECT * FROM Student WHERE Sdept＝‘CS’AND Sage＜＝19；

【例51】查詢選修課程1的學生集合與選修課程2的學生集合的交集。

SELECT Sno FROM SC WHERE Cno＝‘1’ INTERSECT SELECT Sno FROM SC WHERE Cno＝‘2’；

上述例子實際上是查詢既選修了課程1又選修了課程2的學生。

SELECT Sno FROM SC WHERE Cno＝‘1’ AND Sno IN （SELECT Sno FROM SC WHERE Cno＝‘2’）；

【例52】查詢計算機科學系的學生與年齡不大于19歲的學生的差集。

SELECT * FROM Student WHERE Sdept＝‘CS’ EXCEPT SELECT * FROM Student WHERE Sage＜＝19；

上述例子實際上是查詢計算機科學系中年齡大于19歲的學生。

SELECT * FROM Student WHERE Sdept＝‘CS’AND Sage＞19；

3.4.5　SELECT語句的一般格式

視頻二維碼（掃碼觀看）