數(shù)據(jù)庫應(yīng)用技術(shù)教程 課件 第4章 數(shù)據(jù)查詢

上節(jié)回顧1、什么是實體完整性？如何保證實體完整性？2、什么是參照完整性？如何保證參照完整性？任務(wù)1、設(shè)置姓名為唯一。2、使用altertable命令，為選課表添加外鍵約束。第四章數(shù)據(jù)查詢學(xué)習(xí)目標掌握SELECT語句語法；熟練進行簡單查詢；掌握排序、分組聚合和分組篩選；掌握集合查詢和連接查詢的方法；掌握嵌套查詢和窗口函數(shù)的應(yīng)用；學(xué)會在數(shù)據(jù)更新中使用查詢語句。任務(wù)在學(xué)生選課系統(tǒng)數(shù)據(jù)庫中能根據(jù)按照指定的要求靈活、快速地查詢相關(guān)信息。如：1、查詢所有學(xué)生的學(xué)號，姓名和年齡。2、查詢信息系學(xué)生的學(xué)號，姓名和出生年份。3、查詢所有學(xué)生中年齡最大、最小的學(xué)生學(xué)號和姓名。4、查詢女生人數(shù)，平均年齡。。。。4數(shù)據(jù)查詢4.1SELECT語句4.2簡單查詢4.3集合查詢4.4連接查詢4.5嵌套查詢4.6在數(shù)據(jù)更新中使用查詢語句4.7窗口函數(shù)4.1SELECT語句SELECT<目標列名序列>--需要哪些列

FROM<數(shù)據(jù)源>--來自于哪些表

[WHERE<檢索條件>]--根據(jù)什么條件

[GROUPBY<分組依據(jù)列>][HAVING<分組篩選條件>][ORDERBY<排序依據(jù)列>]INTO子句用于將查詢結(jié)果插入新表中，其語法格式如下。SELECT[ALL︱DISTINCT][TOPN[PERCENT]列名1[，列名2，…列名N]INTO新表名

FROM表名或視圖名INTO子句例：使用INTO子句創(chuàng)建一個包含學(xué)生學(xué)號、姓名和性別，并命名為new_Student的新表。在查詢編輯器中執(zhí)行如下Transact-SQL語句。USE學(xué)生選課GOSELECTSno,Sname,SsexINTOnew_StudentFROMStudentGO4.2簡單查詢選擇表中若干列

1.查詢指定的列查詢表中部分屬性列。例1：查詢?nèi)繉W(xué)生的學(xué)號與姓名。SELECTSno,SnameFROMStudent例2.查詢?nèi)w學(xué)生的姓名、學(xué)號、所在系SELECTSname,Sno,Sdept FROMStudent2.查詢?nèi)苛欣?．查詢?nèi)w學(xué)生的記錄SELECTSno,Sname,Ssex,Sage,SdeptFROMStudent等價于：

SELECT*FROMStudent

3.查詢經(jīng)過計算的列例4.查詢?nèi)w學(xué)生的姓名及其出生年份。

SELECTSname,2023-Sage

FROMStudent4.常量列例5．查詢?nèi)w學(xué)生的姓名，出生年份，所在系，并在出生年份列前加入一個列，此列每行數(shù)據(jù)均為“出生年份”常量值。SELECTSname,'出生年份',2023-Sage,sdeptFROMStudent5.顯示列標題

語法：列名|表達式[AS]列標題或：列標題＝列名|表達式例：

SELECTSname姓名,2023-Sage出生年份,所在系=sdeptFROMStudent

查詢結(jié)果：4.2簡單查詢選擇表中若干元組

1.消除重復(fù)的行例6．查詢選修了課程的學(xué)生的學(xué)號SELECTSnoFROMSC有重復(fù)行！用DISTINCT去掉結(jié)果集中的重復(fù)行SELECTDISTINCTSnoFROMSC2.查詢滿足條件的元組(where)查詢條件謂詞比較運算符=,>,>=,<,<=,<>（或!=）NOT+比較運算符確定范圍BETWEEN…AND,NOTBETWEEN…AND確定集合IN,NOTIN字符匹配LIKE,NOTLIKE空值ISNULL,ISNOTNULL邏輯謂詞AND,OR（1）比較大小例7．查詢信息系全體學(xué)生的姓名。

SELECTSnameFROMStudentWHERESdept=‘信息系'例8．查詢年齡在20歲以下的學(xué)生的姓名及年齡。

SELECTSname,SageFROMStudentWHERESage<20例9．查詢考試成績有不及格的學(xué)生的學(xué)號

SELECTDISTINCTSnoFROMSCWHEREGrade<60（2）確定范圍用BETWEEN…AND和NOTBETWEEN…ANDBETWEEN…AND…的格式為：

列名|表達式[NOT]BETWEEN下限值A(chǔ)ND上限值如果列或表達式的值在[不在]下限值和上限值范圍內(nèi)，則結(jié)果為True，表明此記錄符合查詢條件。示例例10．查詢年齡在21～23歲之間的學(xué)生的姓名、所在系和年齡。SELECTSname,Sdept,SageFROMStudent WHERESageBETWEEN21AND23例11．查詢年齡不在21～23之間的學(xué)生姓名、所在系和年齡。SELECTSname,Sdept,SageFROMStudent WHERESageNOTBETWEEN21AND23關(guān)于日期類型查詢例12．查詢2020年8月份出版的全部圖書的詳細信息。SELECT*FROM圖書表

WHERE出版日期BETWEEN'2019/8/1'AND'2019/8/31'注意：日期類型的常量要用單引號括起來，而且年、月、日之間通常用分隔符隔開，常用的分隔符有“/”和“-”（3）確定集合使用IN運算符。用來查找屬性值屬于指定集合的元組。格式為：

列名[NOT]IN(常量1,常量2,…常量n)示例例13．查詢信息系、數(shù)學(xué)系和計算機系學(xué)生的姓名和性別。

SELECTSname,SsexFROMStudent WHERESdeptIN('信息系','數(shù)學(xué)系','計算機系')例14．查詢數(shù)學(xué)系和計算機系之外的其他系的學(xué)生姓名、性別和所在系。

SELECTSname,SsexFROMStudent WHERESdeptNOTIN(‘數(shù)學(xué)系','計算機系')（4）字符串匹配使用LIKE運算符一般形式為：

列名[NOT]LIKE<匹配串>匹配串中可包含如下四種通配符：_：匹配任意一個字符；%：匹配0個或多個字符；[]：匹配[]中的任意一個字符；對于連續(xù)字母的匹配，例如匹配[abcd]，可簡寫為[a-d]，

[^]：不匹配[]中的任意一個字符。

示例例15.查詢姓‘張’的學(xué)生的詳細信息。

SELECT*FROMStudentWHERESnameLIKE

'張%'例16．查詢學(xué)生表中姓‘張’、‘王’和‘李’的學(xué)生的情況。

SELECT*FROMStudentWHERESnameLIKE

'[張王李]%'例17．查詢名字中第2個字為‘小’或‘曉’的學(xué)生的姓名和學(xué)號。

SELECTSname,SnoFROMStudentWHERESnameLIKE

'_[小曉]%'示例例18．查詢所有不姓“王”也不姓“李”的學(xué)生姓名

SELECTSnameFROMStudentWHERESnameNOTLIKE

'[王李]%'或者：

SELECTSnameFROMStudentWHERESnameLIKE'[^王李]%'或者：SELECTSnameFROMStudentWHERESnameNOTLIKE'王%'

ANDSnameNOTLIKE'李%'

示例例19．查詢姓“劉”且名字是2個字的學(xué)生姓名。

SELECTSnameFROMStudentWHERESnameLIKE'劉_'

示例例20．查詢姓劉且名字是3個字的學(xué)生姓名SELECTSnameFROMStudentWHERESnameLIKE'劉__'

注意：尾隨空格需采用如下方法：

SELECTSnameFROMStudentWHERErtrim(Sname)LIKE'劉__'例21.在Student表中查詢學(xué)號的最后一位不是1、3、5的學(xué)生信息。

SELECT*FROMStudentWHERESnoLIKE'%[^135]'轉(zhuǎn)義字符如果要查找的字符串正好含有通配符，比如下劃線或百分號，就需要使用一個特殊子句來告訴數(shù)據(jù)庫管理系統(tǒng)這里的下劃線或百分號是作為一個普通的字符，而不表示通配符，這個特殊的子句就是ESCAPE。ESCAPE的語法格式為：ESCAPE轉(zhuǎn)義字符“轉(zhuǎn)義字符”是任何一個有效的字符。在匹配串中包含轉(zhuǎn)義字符，表明位于該字符后面的第一個字符是普通字符，而不是通配符。示例例如，查找“比例”字段中包含字符串“30%”的記錄：

WHERE比例LIKE'%30!%%'ESCAPE'!'

查找“比例”字段中包含下劃線（_）的記錄：

WHERE比例LIKE'%!_%'ESCAPE'!'（5）有空值的查詢空值（NULL）在數(shù)據(jù)庫中表示不確定的值。例如，學(xué)生選修課程后還沒有考試時，這些學(xué)生有選課記錄，但沒有考試成績，因此考試成績?yōu)榭罩怠Ｅ袛嗄硞€值是否為NULL值，不能使用普通的比較運算符。判斷取值為空的語句格式為：列名ISNULL判斷取值不為空的語句格式為：列名ISNOTNULL

示例例22．查詢沒有考試成績的學(xué)生的學(xué)號和相應(yīng)的課程號。

SELECTSno,CnoFROMSCWHEREGradeISNULL例23．查詢所有有考試成績的學(xué)生的學(xué)號和課程號。

SELECTSno,CnoFROMSCWHEREGradeISNOTNULL

課前練習(xí)1、查詢所有選課學(xué)生的學(xué)號2、查詢考試成績有不及格的學(xué)生的學(xué)號3、查詢既不姓鄭又不姓王的學(xué)生學(xué)號和姓名4、查詢信息系和數(shù)學(xué)系的學(xué)生姓名和所在系（6）多重條件查詢在WHERE子句中可以使用邏輯運算符AND和OR來組成多條件查詢。用AND連接的條件表示必須全部滿足所有的條件的結(jié)果才為True；用OR連接的條件表示只要滿足其中一個條件結(jié)果即為True。例24．查詢計算機系年齡在20歲以下的學(xué)生姓名。

SELECTSnameFROMStudentWHERESdept='計算機系'

ANDSage<20

示例例25．查詢計算機系和信息系年齡大于等于21歲的學(xué)生姓名、所在系和年齡。SELECTSname,Sdept,SageFROMStudentWHERE(Sdept='計算機系'

ORSdept='信息系')ANDSage>=21或：SELECTSname,Sdept,SageFROMStudentWHERESdeptIN('計算機系','信息系')ANDSage>=21

4.2簡單查詢對查詢結(jié)果進行排序

對查詢結(jié)果進行排序(orderby)排序子句為：

ORDERBY<列名>[ASC|DESC][,<列名>…]說明：按<列名>進行升序（ASC）或降序（DESC）排序，默認是按升序進行排序。示例例26．將學(xué)生按年齡的升序排序。

SELECT*FROMStudentORDERBYSage例27．查詢選修了3號課程的學(xué)生的學(xué)號及其成績，查詢結(jié)果按成績降序排列。SELECTSno,GradeFROMSC WHERECno='3'

ORDERBYGradeDESC

例28.查詢?nèi)w學(xué)生的信息，查詢結(jié)果按所在系的系名升序排列，同一系的學(xué)生按年齡降序排列。SELECT*FROMStudent

ORDERBYSdept,SageDESC

4.2簡單查詢使用聚合函數(shù)匯總數(shù)據(jù)

使用計算函數(shù)匯總數(shù)據(jù)SQL提供的聚合函數(shù)有：COUNT（*）：統(tǒng)計表中元組個數(shù)；COUNT（[DISTINCT]<列名>）：統(tǒng)計本列列值個數(shù)；SUM（[DISTINCT]<列名>）：計算列值總和；AVG（[DISTINCT]<列名>）：計算列值平均值；MAX（[DISTINCT]<列名>）：求列值最大值；MIN（[DISTINCT]<列名>）：求列值最小值。上述函數(shù)中除COUNT（*）外，其他函數(shù)在計算過程中均忽略NULL值。示例例29．統(tǒng)計學(xué)生總?cè)藬?shù)。

SELECTCOUNT(*)FROMStudent

例30．統(tǒng)計選修了課程的學(xué)生的人數(shù)。

SELECTCOUNT(DISTINCTSno)FROMSC例31．統(tǒng)計95002號學(xué)生的考試總成績之和。SELECTSUM(Grade)FROMSCWHERESno='95002'

示例例32．計算選修1號課程學(xué)生的考試平均成績。

SELECTAVG(Grade)FROMSCWHERECno='1'例33．查詢1號課程的考試最高分和最低分。

SELECTMAX(Grade),MIN(Grade)FROMSCWHERECno='1'注意：計算函數(shù)不能出現(xiàn)在WHERE子句中示例例34．查詢“95001”學(xué)生的選課門數(shù)、考試課程門數(shù)以及考試最高分、最低分和平均分。SELECTCOUNT(*)AS選課門數(shù),

COUNT(Grade)AS考試門數(shù),MAX(Grade)AS最高分,

MIN(Grade)AS最低分,AVG(Grade)AS平均分FROMSCWHERESno='95001'4.2簡單查詢對查詢結(jié)果進行分組統(tǒng)計

對查詢結(jié)果進行分組統(tǒng)計

作用：可以控制計算的級別：對全表還是對每一分組。目的：細化計算函數(shù)的作用對象。分組語句的一般形式： GROUPBY<分組依據(jù)列>[,…n]

[HAVING<組選擇條件>]（1）使用GROUPBY例35．統(tǒng)計每門課程的選課人數(shù)，列出課程號和人數(shù)。SELECTCnoas課程號,

COUNT(Sno)as選課人數(shù)FROMSCGROUPBYCno

對查詢結(jié)果按Cno的值分組，所有具有相同Cno值的元組為一組，然后再對每一組使用COUNT計算，求得每組的學(xué)生人數(shù)。SnoCnoGrade95001187950012769500137995001480950021899500418395004256CnoCount(Sno)13223141SnoCnoGrade95001187950021899500418395001276950042569500137995001480例36．查詢每個學(xué)生的選課門數(shù)和最高分。SELECTSnoas學(xué)號,

COUNT(*)as選課門數(shù),

MAX(Grade)as最高成績FROMSCGROUPBYSno注意GROUPBY子句中的分組依據(jù)列必須是表中存在的列名，不能使用AS指派的列別名。例如，例36中不能將GROUBY子句寫成：GROUPBY學(xué)號。帶有GROUPBY子句的SELECT語句的查詢列表中只能出現(xiàn)分組依據(jù)列或聚合函數(shù)，因為分組后每個組只返回一行結(jié)果。示例例37．統(tǒng)計每個系的學(xué)生人數(shù)和平均年齡。SELECTSdept,COUNT(*)AS學(xué)生人數(shù),

AVG(Sage)AS平均年齡

FROMStudentGROUPBYSdept示例例38.帶WHERE子句的分組。統(tǒng)計每個系的男生人數(shù)。SELECTSdept,Count(*)男生人數(shù)

FROMStudentWHERESsex='男'

GROUPBYSdept示例例39.按多列分組。統(tǒng)計每個系的男生人數(shù)和女生人數(shù)，以及男生的最大年齡和女生的最大年齡。結(jié)果按系名的升序排序。SELECTSdept,Ssex,Count(*)人數(shù),

Max(Sage)最大年齡FROMStudentGROUPBYSdept,SsexORDERBYSdept（2）使用HAVINGHAVING用于對分組進行篩選，它有點象WHERE子句，但它用于分組而不是對單個記錄。如：查詢修了3門以上課程的學(xué)生的學(xué)號

SELECTSnoFROMSCGROUPBYSno

HAVINGCOUNT(*)>3

示例例40.查詢選修了3門以上課程的學(xué)生的學(xué)號和選課門數(shù)。SELECTSno,Count(*)選課門數(shù)

FROMSCGROUPBYSnoHAVINGCOUNT(*)>3處理過程：先執(zhí)行GROUPBY子句對SC表數(shù)據(jù)按Sno進行分組，然后再用統(tǒng)計函數(shù)COUNT分別對每一組進行統(tǒng)計，最后篩選出統(tǒng)計結(jié)果滿足大于3的組。分組統(tǒng)計篩選處理過程示意圖示例例41．查詢修課門數(shù)等于或大于4的學(xué)生的平均成績和選課門數(shù)。

SELECTSno,AVG(Grade)平均成績,

COUNT(*)修課門數(shù)

FROMSCGROUPBYSno

HAVINGCOUNT(*)>=4

幾個子句比較WHERE子句用來篩選FROM子句中指定的數(shù)據(jù)源所產(chǎn)生的行數(shù)據(jù)。GROUPBY子句用來對經(jīng)WHERE子句篩選后的結(jié)果數(shù)據(jù)進行分組。HAVING子句用來對分組后的結(jié)果數(shù)據(jù)再進行篩選。示例例42.查詢計算機系和數(shù)學(xué)系的學(xué)生人數(shù)。方法1：SELECTSdept,COUNT(*)FROMStudentGROUPBYSdeptHAVINGSdeptIN('計算機系','數(shù)學(xué)系')方法2：SELECTsdept,COUNT(*)FROMStudentWHERESdeptIN('計算機系','數(shù)學(xué)系')GROUPBYSdept第二種寫法先篩選數(shù)據(jù)，再分組，效率更高。示例例43.查詢每個系年齡小于等于21歲的學(xué)生人數(shù)。SELECTSdept,COUNT(*)FROMStudentWHERESage<=21GROUPBYSdept該查詢語句不能寫成：SELECTSdept,COUNT(*)FROMStudentGROUPBYSdeptHAVINGSage<=21練習(xí)1、查詢數(shù)學(xué)系女生學(xué)號和姓名，查詢結(jié)果按學(xué)號升序排列2、查詢每個系學(xué)生人數(shù)，按人數(shù)降序排列3、查詢選課人數(shù)在2人以上的課程的課程號4、查詢選課門數(shù)大于等于3門的學(xué)生的學(xué)號，和選課門數(shù)，按選課門數(shù)的降序排列上節(jié)知識回顧1、查詢選課人數(shù)大于等于3人的課程號和選課人數(shù)。4.3集合查詢?nèi)绻卸鄠€不同的查詢結(jié)果集，但又希望將它們按照一定的關(guān)系連接在一起，組成一組數(shù)據(jù)，這就可以用集合運算來實現(xiàn)。UNION（并）、INTERSECT（交）、EXCEPT（差）參加聯(lián)合查詢操作的各查詢結(jié)果的列數(shù)必須相同，對應(yīng)項的數(shù)據(jù)類型也必須相同。集合并運算（UNION）集合并運算是將來自不同查詢結(jié)果集合組合起來，形成一個查詢結(jié)果集（并集），UNION操作會自動將重復(fù)元組去除。例44：查詢選修了1號課程或選修了2號課程的學(xué)生的學(xué)號。集合交運算（INTERSECT）集合交運算是將來自不同查詢結(jié)果集合中公共的元組組合起來，形成一個查詢結(jié)果集（交集）。INTERSECT操作會自動將重復(fù)的元組去除。例45：查詢既選修了1號課程又選修了2號課程的學(xué)生的學(xué)號。集合差運算（EXCEPT）集合差運算是將屬于左查詢結(jié)果集但不屬于右查詢結(jié)果的元組組合起來，形成一個查詢查詢集（差集）。例46：查詢選修了1號課程但沒有選修2號課程的學(xué)生的學(xué)號。集合運算舉例例47：查詢學(xué)生表，列出除數(shù)學(xué)系的女生外，所有學(xué)生的學(xué)號、姓名、所在系和性別。4.4連接查詢?nèi)粢粋€查詢同時涉及兩個或兩個以上的表，則稱之為連接查詢。連接查詢是關(guān)系數(shù)據(jù)庫中最主要的查詢連接查詢包括內(nèi)連接、自身連接、外連接等。連接基礎(chǔ)知識連接查詢中用于連接兩個表的條件稱為連接條件或連接謂詞。一般格式為：

[<表名1.>][<列名1>]<比較運算符>[<表名2.>][<列名2>]必須語義相同!內(nèi)連接內(nèi)連接語法如下：

SELECT…FROM表名[INNER]JOIN被連接表

ON連接條件執(zhí)行連接操作的過程：首先取表1中的第1個元組，然后從頭開始掃描表2，逐一查找滿足連接條件的元組，找到后就將表1中的第1個元組與該元組拼接起來，形成結(jié)果表中的一個元組。表2全部查找完畢后，再取表1中的第2個元組，然后再從頭開始掃描表2，…重復(fù)這個過程，直到表1中的全部元組都處理完畢為止。

示例例48．查詢每個學(xué)生及其選課的詳細信息。SELECT*FROMStudentINNERJOINSCONStudent.Sno=SC.Sno改進例1SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudentJOINSCONStudent.Sno=SC.Sno示例例49.查詢計算機系學(xué)生的修課情況，要求列出學(xué)生的名字、所修課的課程號和成績。

SELECTSname,Cno,GradeFROMStudentJOINSCONStudent.Sno=SC.SnoWHERESdept='計算機系'

表別名可以為表提供別名，其格式如下：

FROM<源表名>[AS]<表別名>為表指定別名可以簡化表的書寫。當為表指定了別名，在查詢語句中的其他地方，所有用到表名的地方都要使用別名，而不能再使用原表名。示例例50.查詢信息系修了“數(shù)據(jù)庫”課程的學(xué)生信息，要求列出學(xué)生姓名、課程名和成績。SELECTSname,Cname,GradeFROMStudentsJOINSCONs.Sno=SC.Sno

JOINCoursecONc.Cno=SC.CnoWHERESdept='信息系'

ANDCname='數(shù)據(jù)庫'

示例例51．查詢所有修了C++課程的學(xué)生的修課情況，要求列出學(xué)生姓名、所在系和選課成績。SELECTSname,Sdept，GradeFROMStudentSJOINSCONS.Sno=SC.Sno

JOINCourseCONC.Cno=SC.cnoWHERECname='C++'示例例52.有分組的多表連接查詢。統(tǒng)計每門課（列出課程名）的學(xué)生考試平均成績。SELECTCname,AVG(grade)asAverageGradeFROMCoursecJOINSCONC.Cno=SC.CnoGROUPBYCname示例例53.有分組和行選擇條件的多表連接查詢。統(tǒng)計信息系每門課程的選課人數(shù)、平均成績、最高成績和最低成績。SELECTCno,COUNT(*)ASTotal,AVG(Grade)asAvgGrade,MAX(Grade)asMaxGrade,MIN(Grade)asMinGrade

FROMStudentSJOINSCONS.Sno=SC.SnoWHERESdept=‘信息系'

GROUPBYCno2.自身連接為特殊的內(nèi)連接相互連接的表物理上為同一張表。必須為兩個表取別名，使之在邏輯上成為兩個表。

FROM表1ASA1--在內(nèi)存中生成“A1”表

JOIN表1ASA2--在內(nèi)存中生成“A2”表示例例54.查詢與劉超華在同一個系學(xué)習(xí)的學(xué)生的姓名和所在的系。SELECTS2.Sname,S2.SdeptFROMStudentS1JOINStudentS2ONS1.Sdept=S2.SdeptWHERES1.Sname='劉超華'

ANDS2.Sname!='劉超華'示例例55.查詢與“數(shù)據(jù)庫”學(xué)分相同的課程的課程名和學(xué)分。SELECTC1.Cname,C1.CreditFROMCourseC1JOINCourseC2ONC1.Credit=C2.CreditWHEREC2.Cname='數(shù)據(jù)庫'3.外連接只限制一張表中的數(shù)據(jù)必須滿足連接條件，而另一張表中數(shù)據(jù)可以不滿足連接條件。

外連接的語法格式為：

FROM表1LEFT|RIGHT[OUTER]JOIN表2ON<連接條件>示例例56.查詢學(xué)生的修課情況，包括修了課程的學(xué)生和沒有修課的學(xué)生。SELECTStudent.Sno,Sname,Cno,GradeFROMStudentLEFTOUTERJOINSC ONStudent.Sno=SC.Sno示例例57.查詢哪些課程沒有人選，列出其課程號，課程名。SELECTC.cno,CnameFROMCourseCLEFTJOINSCONC.Cno=SC.CnoWHERESC.CnoISNULL示例例58.查詢數(shù)學(xué)系沒有選課的學(xué)生，列出學(xué)生的學(xué)號，姓名和性別。SELECTSno,Sname,SsexFROMStudentSLEFTJOINSCONS.Sno=SC.SnoWHERESdept='數(shù)學(xué)系'ANDSC.SnoISNULL示例例59.統(tǒng)計信息系每個學(xué)生的選課門數(shù)，包括沒有選課的學(xué)生，結(jié)果按選課門數(shù)遞減排序。SELECTS.Sno學(xué)號,COUNT(SC.Cno)選課門數(shù)FROMStudentSLEFTJOINSCONS.Sno=SC.SnoWHERESdept='信息系'GROUPBYS.SnoORDERBYCOUNT(SC.Cno)DESC當堂練習(xí)查詢數(shù)學(xué)系男生的選課情況。查詢選修了數(shù)據(jù)庫的學(xué)生的學(xué)號和姓名。課前回顧1.查詢和數(shù)據(jù)庫同一學(xué)期開設(shè)的其他課程的課程名和學(xué)分。2.查詢沒有選課的學(xué)生的學(xué)號和姓名。4.5子查詢（嵌套查詢）在SQL語言中，一個SELECT－FROM－WHERE語句稱為一個查詢塊；子查詢是一個SELECT查詢，它嵌套在SELECT、INSERT、UPDATE、DELETE語句的WHERE或HAVING子句內(nèi)，或其他子查詢中；子查詢的SELECT查詢總是使用圓括號括起來。1.基于集合的子查詢使用子查詢表示查詢的范圍一般格式為：列名[NOT]IN(子查詢)注意使用子查詢進行基于集合的查詢時，由子查詢返回的結(jié)果集中的列的個數(shù)、數(shù)據(jù)類型以及語義必須與表達式中的列的個數(shù)、數(shù)據(jù)類型以及語義相同。當子查詢返回結(jié)果之后，外層查詢將用這個結(jié)果作為篩選條件。示例例60.查詢與劉超華在同一個系的學(xué)生。

SELECTSno,Sname,SdeptFROMStudent WHERESdept= (SELECTSdeptFROMStudent WHERESname='劉超華'

)

ANDSname!='劉超華'

②①示例例61.查詢成績?yōu)榇笥?0分的學(xué)生的學(xué)號、姓名。

SELECTSno,SnameFROMStudent WHERESnoIN (SELECTSnoFROMSC WHEREGrade>80)①②示例例62．查詢計算機系選了“2”課程的學(xué)生，列出姓名和性別。

SELECTSname,SsexFROMStudentWHERESnoIN(SELECTSnoFROMSCWHERECno='2')ANDSdept='計算機系'用多表連接實現(xiàn)：SELECTSname,SsexFROMStudentS,ScWHERES.Sno=SC.SnoandSdept='計算機系'ANDCno='2'示例例63.查詢選修了“數(shù)據(jù)庫”課程的學(xué)生的學(xué)號和姓名。（1）在Course表中，找出“數(shù)據(jù)庫”課程名對應(yīng)的課程號；（2）根據(jù)得到的“數(shù)據(jù)庫”課程號，在SC表中找出選了該課程號的學(xué)生的學(xué)號；（3）根據(jù)得到的學(xué)號，在Student表中找出對應(yīng)的學(xué)生的學(xué)號和姓名。SELECTSno,SnameFROMStudentWHERESnoIN(SELECTSnoFROMSCWHERECnoIN(SELECTCnoFROMCourseWHERECname='數(shù)據(jù)庫'))例64.在選修了數(shù)據(jù)庫的這些學(xué)生中，統(tǒng)計他們的選課門數(shù)和平均成績。SELECTSno學(xué)號,COUNT(*)選課門數(shù),AVG(Grade)平均成績FROMSCWHERESnoIN(--選數(shù)據(jù)庫的學(xué)生SELECTSnoFROMSCJOINCourseCONC.Cno=SC.CnoWHERECname=‘數(shù)據(jù)庫')GROUPBYSno2.進行比較測試帶比較運算符的子查詢指父查詢與子查詢之間用比較運算符連接，當用戶能確切知道內(nèi)層查詢返回的是單值時，可用>、<、=、>=、<=、<>運算符。示例例65.查詢選了“3”號課程且成績高于此課程的平均成績的學(xué)生的學(xué)號和成績。首先計算“3”號課程的平均成績：

SELECTAVG(Grade)fromSCWHERECno='3'--79然后，查找“3”號課程所有的考試成績中，高于79的學(xué)生：SELECTSno,GradeFROMSCWHERECno='3'ANDGrade>79將兩個查詢語句合起來即為滿足我們要求的查詢語句：SELECTSno,GradeFROMSCWHERECno='3'ANDGrade>(SELECTAVG(Grade)FROMSCWHERECno='3')若無結(jié)果，請檢查3前面是否有空格不相關(guān)子查詢用子查詢進行基于集合查詢和比較查詢時，都是先執(zhí)行子查詢，然后再在子查詢的結(jié)果基礎(chǔ)之上執(zhí)行外層查詢。子查詢都只執(zhí)行一次，子查詢的查詢條件不依賴于外層查詢，我們將這樣的子查詢稱為不相關(guān)子查詢或嵌套子查詢。示例例66.查詢計算機系年齡最大的學(xué)生的姓名和年齡。SELECTSname,SageFROMStudentWHERESdept='計算機系'ANDSage=(SELECTMAX(Sage)FROMStudentWHERESdept='計算機系')示例例67.查詢數(shù)據(jù)庫考試成績高于數(shù)據(jù)庫平均成績的學(xué)生的姓名、所在系和數(shù)據(jù)庫成績。SELECTSname,Sdept,GradeFROMStudentSJOINSCONS.Sno=SC.SnoJOINCourseCONC.Cno=SC.CnoWHERECname='數(shù)據(jù)庫'ANDGrade>(SELECTAVG(Grade)FROMSCJOINCourseCONC.Cno=SC.CnoWHERECname='數(shù)據(jù)庫')3.存在性查詢通常使用EXISTS謂詞，其形式為： WHERE[NOT]EXISTS（子查詢）帶EXISTS謂詞的子查詢不返回查詢的數(shù)據(jù)，只產(chǎn)生邏輯真值（有數(shù)據(jù)）和假值（沒有數(shù)據(jù)）。例68.查詢選修了1課程的學(xué)生姓名。SELECTSnameFROMStudent WHEREEXISTS (SELECT*FROMSC WHERESno=Student.Sno

ANDCno='1')

注意處理過程為：先外后內(nèi)；由外層的值決定內(nèi)層的執(zhí)行；內(nèi)層執(zhí)行次數(shù)由外層結(jié)果數(shù)決定。由于EXISTS的子查詢只能返回真或假值，因此在這里給出列名無意義。所以在有EXISTS的子查詢中，其目標列表達式通常都用*。上句的處理過程

找外層表Student表的第一行，根據(jù)其Sno值處理內(nèi)層查詢由外層的值與內(nèi)層的結(jié)果比較，由此決定外層條件的真、假順序處理外層表Student表中的第2、3…行。示例例69.查詢沒有選修1號課程的學(xué)生姓名和所在系。SELECTSname,SdeptFROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno='1')或：SELECTSname,SdeptFROMStudentWHERESnoNOTIN(SELECTSnoFROMSCWHERECno='1')注意：不能用連接查詢和在子查詢中否定的形式實現(xiàn)。4.with子句WITH子句用于定義公共表表達式（CommonTableExpression,CTE），可臨時存儲查詢結(jié)果，簡化復(fù)雜查詢邏輯。CTE在單個查詢中可被多次引用，提升代碼可讀性。其語法格式如下。WITHCTE名稱AS(SELECT查詢定義)主查詢語句示例：查詢選課門數(shù)大于平均選課門數(shù)的學(xué)生學(xué)號及選課門數(shù)。with選課統(tǒng)計as(selectsno,count(*)as選課門數(shù)fromscgroupbysno)selectsno,選課門數(shù)from選課統(tǒng)計where選課門數(shù)>(selectavg(選課門數(shù))from選課統(tǒng)計)4.6在數(shù)據(jù)更新中使用查詢語句4.6.1插入數(shù)據(jù)4.6.2更新數(shù)據(jù)4.6.3刪除數(shù)據(jù)4.6.1插入數(shù)據(jù)在INSERT語句中使用SELECT子句的語法格式如下。INSERT[INTO]table_name[(column_list)]SELECTselect_listFROMtable_name[WHEREsearch_condition]一次插入多行數(shù)據(jù)例1：設(shè)stu_cs為計算機系的學(xué)生表，結(jié)構(gòu)和student一致，但無數(shù)據(jù)，請將計算機系的學(xué)生信息添加進去。Insertintostu_csSelect*fromstudentWheresdept=‘計算機系’注意被插入的表的值列表必須和查詢結(jié)果中的值列表的列按位置順序?qū)?yīng)，且數(shù)據(jù)類型一致。如果被插入的表后邊沒有指明列名，則查詢結(jié)果的列的順序必須與表中列的定義順序一致，且每一個列均有值（可以為空）。4.6.2更新數(shù)據(jù)用UPDATE語句實現(xiàn)。格式：

UPDATE<表名>

SET<列名＝表達式>[,…n][WHERE<更新條件>]基于其他表條件的數(shù)據(jù)更新例2：將計算機系全體學(xué)生的成績加5分。（1）用子查詢實現(xiàn)

UPDATESCSETGrade=Grade+5 WHERESnoIN (SELECTSnoFROMStudent WHERESdept='計算機系')（2）用多表連接實現(xiàn)UPDATESCSETGrade=Grade+5FROMSC,StudentWHERESC.Sno=Student.SnoAndSdept='計算機系'基于本表條件的數(shù)據(jù)更新例3.將學(xué)分最低的課程的學(xué)分加2分。UPDATECourseSETCredit=Credit+2WHERECredit=(SELECTMIN(Credit)FROMCourse)較復(fù)雜的數(shù)據(jù)更新例4.數(shù)學(xué)系學(xué)生的“信息系統(tǒng)”考試成績增加10分。用子查詢實現(xiàn)：UPDATESCSETGrade=Grade+10WHERECnoIN(SELECTCnoFROMCourseWHERECname='信息系統(tǒng)')ANDSnoIN(SELECTSnoFROMStudentWHERESdept='數(shù)學(xué)系')用多表連接實現(xiàn)：UPDATESCSETGrade=Grade+10FROMSCJOINCourseCONC.Cno=SC.CnoJOINStudentSONS.Sno=SC.SnoWHERECname='信息系統(tǒng)'ANDSdept='數(shù)學(xué)系'4.6.3刪除數(shù)據(jù)用DELETE語句實現(xiàn)格式：DELETE[FROM]<表名>