sql 第06章章節(jié)課件

第6章

查詢管理本章導(dǎo)讀本章主要介紹SELECT語法結(jié)構(gòu)，詳細(xì)介紹了條件子句、排序子句、分組子句等各類子句，及它們的執(zhí)行規(guī)則。6.1SELECT查詢語句6.1.1SELECT語句的執(zhí)行窗口方法一：使用查詢設(shè)計(jì)器方法二：使用SQL編輯器6.1SELECT查詢語句1．SELECT-FROM-WHERE句型1）格式SELECT<列表>［INTO<新表>］FROM<基本表>(或視圖序列)WHERE<條件表達(dá)式>2）功能“<列表>”指定要選擇的屬性或表達(dá)式“INTO<新表>”將查詢結(jié)果存放到指定新表“FROM<基本表>(或視圖序列)”指定數(shù)據(jù)來源表“WHERE<條件表達(dá)式>”指定查詢出的記錄行需滿足的條件6.1.2簡單查詢2．簡單查詢示例6.1.2簡單查詢1）選擇指定的屬性列【例6.1】查詢所有學(xué)生可選的課程信息。sELECT*FROMc--“*”表示選擇當(dāng)前表的所有屬性列【例6.2】查詢數(shù)據(jù)庫studentcourse學(xué)生基本信息表s中學(xué)生的學(xué)號，姓名，所在系。SELECT學(xué)號,姓名,系FROMs2）消除結(jié)果集中的重復(fù)行格式SELECTDISTINCT[屬性列表]【例6.3】查詢所有被選修的課程。SELECTDISTINCT課程號FROMsc2．簡單查詢示例6.1.2簡單查詢3）設(shè)置屬性列的別名【例6.4】查詢表s的學(xué)號和姓名信息，其中設(shè)置學(xué)號的別名為xh，設(shè)置姓名的別名為xmSELECT學(xué)號ASxh,姓名ASxmFROMS或SELECT學(xué)號AS'xh',姓名AS'xm'FROMS或SELECT學(xué)號AS[xh],姓名AS[xm]FROMS或SELECT'xh'=學(xué)號,'xm'=姓名FROMS2．簡單查詢示例6.1.2簡單查詢【例6.5】列出表S中學(xué)生的學(xué)號和出生年份情況。SELECT學(xué)號,出生年份=year(出生日期)FROMs或SELECT學(xué)號,year(出生日期)as出生年份FROMs5）通過TOP子句限制結(jié)果集輸出的行數(shù)（1）命令格式SELECT[TOPn[PERCENT]][屬性列表]（2）功能n是一個(gè)正整數(shù)，如果指定PERCENT關(guān)鍵字，則返回前百分之n條記錄，n必須介于0到100之間的正整數(shù)。比如“SELECTTOP3”表示輸出查詢結(jié)果集的前3行，“SELECTTOP3PERCENT”表示輸出查詢結(jié)果集的前3%記錄行。4）使用查詢表達(dá)式查詢語句SELECT可直接查詢表達(dá)式的值。2．簡單查詢示例6.1.2簡單查詢【例6.6】列出表SC中前10%的記錄信息。

SELECTTOP10PERCENT*FROMSC

【例6.7】查詢學(xué)生選課數(shù)據(jù)表SC中的前三條記錄的信息。

SELECTTOP3*FROMSC2．簡單查詢示例6.1.2簡單查詢6）INTO子句的使用【例6.8】將課程成績70分以下的同學(xué)的成績提高5分，然后把結(jié)果存儲(chǔ)到新數(shù)據(jù)表“最新成績”中。SELECT成績+5AS最新成績INTO最新成績FROMscWHERE成績<70【例6.9】求管理信息系學(xué)生的詳細(xì)信息，并將這些信息另存到數(shù)據(jù)表“管理信息系”中。SELECT*INTO管理信息系FROMSWHERE系='管理信息系'【例6.10】在基本表S中檢索2002-01-06出生的學(xué)生情況。SELECT*FROMsWHERE出生日期=‘2002-01-06‘【例6.11】在基本表S中檢索2001年及以后出生的學(xué)生情況。SELECT*FROMsWHEREyear(出生日期)>=2001或SELECT*FROMsWHEREyear(出生日期)!<2001【例6.12】在基本表S中檢索2002-01-05以后出生的學(xué)生情況。SELECT*FROMsWHERE出生日期>‘2002-01-05’或SELECT*FROMsWHERE出生日期>’2002.01.05‘【例6.13】在基本表S中檢索2001-12-15以前出生的學(xué)生情況。SELECT*FROMsWHERE出生日期<‘12/15/2001‘或SELECT*FROMsWHERE出生日期<‘20011215'6.1.3表達(dá)式運(yùn)算符1）比較運(yùn)算符（1）字符串比較運(yùn)算符“LIKE”格式如下s[NOT]LIKEp[ESCAPE’通配符字符’]6.1.3表達(dá)式運(yùn)算符2）字符串運(yùn)算符【例6.14】列出表S中管理信息系年齡小于20歲的學(xué)生的學(xué)號和出生年份情況。SELECT學(xué)號,year(出生日期)as出生年份FROMsWHERE系='管理信息系'and(year(getdate())-year(出生日期))<20【例6.15】在表S中查詢管理信息系學(xué)生的姓名。SELECT姓名as姓名,系as所在院系FROMSWHERE系LIKE'管理信息系'6.1.3表達(dá)式運(yùn)算符2）字符串運(yùn)算符【例6.16】查詢陳老師所教的課程信息。SELECT*FROMcWHERE教師LIKE'陳%‘【例6.17】在基本表S中檢索出生日期包含13的學(xué)生情況。SELECT*FROMsWHERE出生日期like'%13%‘【例6.18】如果要找出其姓名中不含有“紅”的學(xué)生情況，則在LIKE前增加NOT。SELECT*FROMsWHERE姓名NOTLIKE'%紅%'【例6.19】在基本表S中檢索以“王”姓打頭，名字由1個(gè)漢字組成的學(xué)生姓名。SELECT姓名FROMsWHERE姓名LIKE‘王_’6.1.3表達(dá)式運(yùn)算符2）字符串運(yùn)算符【例6.21】在基本表S中檢索2000年出生或者姓王的學(xué)生情況。SELECT*FROMsWHEREyear(出生日期)=2000OR姓名LIKE'王%‘【例6.22】在基本表S中檢索在2000年到2002年之間出生的學(xué)生情況。SELECT*FROMsWHEREyear(出生日期)>2000andyear(出生日期)<2002【例6.23】在基本表S中檢索不在管理信息系的學(xué)生情況。SELECT*FROMsWHEREnot(系='管理信息系')或SELECT*FROMsWHERE系!='管理信息系'6.1.3表達(dá)式運(yùn)算符3）邏輯運(yùn)算符（1）格式表達(dá)式1[NOT]BETWEEN表達(dá)式2AND表達(dá)式3（2）功能注意，表達(dá)式2的值不能大于表達(dá)式3的值。表達(dá)式“成績BETWEEN65AND70”與表達(dá)式“成績>=65and成績<=70”

等價(jià)?！纠?.24】查詢成績在65到70之間的學(xué)生學(xué)號、課程號、成績。SELECT學(xué)號,課程號,成績FROMscWHERE成績BETWEEN65AND70或SELECT學(xué)號,課程號,成績FROMscWHERE成績>=65AND成績<=706.1.3表達(dá)式運(yùn)算符4）指定范圍運(yùn)算符【例6.25】在S中檢索不在2000年出生的學(xué)生學(xué)號與姓名信息。SELECT*FROMsWHERE出生日期notBETWEEN‘2000-01-01'AND‘2000-12-31‘5）集合成員資格運(yùn)算符

集合成員資格運(yùn)算符IN可以比較表達(dá)式的值與值表中的值是否匹配，如果匹配，則返回TRUE,否則返回FALSE。格式如下：表達(dá)式1[NOT]IN(表達(dá)式2[,…n])【例6.26】查詢成績?yōu)?0、80、90分的學(xué)生學(xué)號、課程號、成績。SELECT學(xué)號,課程號,成績FROMscWHERE成績IN(70,80,90)6.1.3表達(dá)式運(yùn)算符6.1.2簡單查詢【例6.27】在S中檢索學(xué)生學(xué)號不是‘G0401’,‘G0402’,‘J0403’的學(xué)生信息。SELECT*FROMsWHERE學(xué)號notin('G0401','G0402','J0403')等價(jià)于SELECT*FROMsWHERE學(xué)號<>'G0401'and學(xué)號<>'G0402'and學(xué)號<>'J0403‘【例6.28】在SC中檢索課程號是‘C01’,‘C02’,‘C03’的學(xué)生成績信息。SELECT學(xué)號,成績FROMscWHERE課程號in('c01','c02','c03')等價(jià)于SELECT學(xué)號,成績FROMscWHERE課程號='c01'or課程號='c02'or課程號='c03'6.1.2簡單查詢6）空值比較運(yùn)算符SQL中允許列值為空，空值用保留字NULL表示。查詢空值操作不是=‘null’,而是用ISNULL來測試。格式表達(dá)式IS[NOT]NULL功能缺省NOT時(shí)，當(dāng)表達(dá)式的值為空時(shí)，返回TRUE,否則返回FLASE?！纠?.29】查詢?nèi)鄙俪煽兊膶W(xué)生的學(xué)號和相應(yīng)的課程號。（某些學(xué)生選修課程后沒有參加考試，所以有選課記錄，但沒有考試成績。）SELECT學(xué)號,課程號FROMscWHERE成績ISNULL【例6.30】查詢有選修成績的學(xué)生的學(xué)號和相應(yīng)的課程號。SELECT學(xué)號,課程號FROMscWHERE成績ISNOTNULL6.2排序子句1．格式SELECT<列表>［INTO<新表>］FROM<基本表>(或視圖序列)WHERE<條件表達(dá)式>][ORDERBY屬性名[ASC|DESC][,…n]]--排序子句

2．功能

排序子句“ORDERBY[屬性名[ASC|DESC][,…n]]”設(shè)置信息輸出的排序規(guī)則，用戶可以按照一個(gè)或多個(gè)屬性列升序（ASC）或降序（DESC）排列查詢結(jié)果。缺省[ASC|DESC]選項(xiàng)時(shí)，系統(tǒng)默認(rèn)為升序排列。當(dāng)指定ASC選項(xiàng)時(shí)，將最后顯示屬性列為空值的記錄。當(dāng)指定DESC選項(xiàng)時(shí)，將最先顯示屬性列為空值的記錄。

除非同時(shí)指定了TOP，否則ORDERBY子句在視圖、內(nèi)聯(lián)函數(shù)、派生表和子查詢中無效。ORDERBY子句中不能使用ntext、text和image列。6.1.2簡單查詢【例6.31】將所有女生按年齡升序排序。SELECT*FROMsWHERE性別='女'ORDERBY出生日期ASC【例6.32】將SC數(shù)據(jù)表中的信息按學(xué)生學(xué)號升序，課程號降序排列。SELECT*FROMSCORDERBY學(xué)號,課程號DESC6.1.2簡單查詢SELECT*【例6.33】在SC表中，查詢學(xué)號為J0401的同學(xué)獲得最高成績的課程號。SELECTTOP1學(xué)號,課程號,成績FROMSCWHERE學(xué)號='J0401’ORDERBY成績DESC【例6.34】在SC表中，找出選修了課程號為C02的學(xué)生選課信息，而且課程成績最高的前兩位同學(xué)。SELECTTOP2*FROMSCWHERE課程號='c02'ORDERBY成績DESC1．條件連接在WHERE子句中使用比較運(yùn)算符給出連接條件對表進(jìn)行連接。1）格式WHERE表名1.列名1比較運(yùn)算符表名2.列名22）功能其中，各連接列名的類型必須是可比的。當(dāng)查詢的信息涉及多張數(shù)據(jù)表時(shí)，往往先讀取FORM子句中基本表或視圖的數(shù)據(jù)，執(zhí)行廣義笛卡爾積，在廣義笛卡爾積中選取滿足WHERE子句中給出的條件表達(dá)式的記錄行。當(dāng)引用一個(gè)在多張數(shù)據(jù)表中均存在的屬性時(shí)，則要明確指出這個(gè)屬性的來源表。關(guān)系中屬性的引用格式為<關(guān)系名>.<屬性名｜*>。當(dāng)比較運(yùn)算符是“=”時(shí)，就是自然連接，即按照兩個(gè)表中的相同屬性進(jìn)行等值連接，并且目標(biāo)列中去掉了重復(fù)的屬性列，保留了所有不重復(fù)的屬性列。6.3連接運(yùn)算6.3.1謂詞連接【例6.35】查詢數(shù)據(jù)庫中管理信息系學(xué)生的學(xué)號、姓名、選修的課程號及成績。

SELECTS.學(xué)號,姓名,課程號,成績FROMS,SCWHERES.學(xué)號=SC.學(xué)號AND系='管理信息系'

【例6.36】查詢所有學(xué)生的情況，以及他們選修的課程號和成績。

SELECTS.*,SC.課程號,SC.成績FROMS,SCWHERES.學(xué)號=SC.學(xué)號【例6.37】查詢所有學(xué)生的姓名，及他們選修的課程名和得分。

SELECTS.姓名,C.課程名,SC.成績FROMS,C,SCWHERES.學(xué)號=SC.學(xué)號ANDC.課程號=SC.課程號6.1.2簡單查詢2．自身連接1）格式SELECT[表名1或表名2].列名FROM表1as表名1,表1as表名2WHERE表名1.列名1比較運(yùn)算符表名2.列名22）功能自身連接時(shí),查詢涉及同一個(gè)關(guān)系數(shù)據(jù)表的兩個(gè)甚至更多個(gè)記錄，如何指定關(guān)系數(shù)據(jù)表的每一個(gè)出現(xiàn)值。也就是參與連接的兩個(gè)表都是某一基本表的副表。【例6.38】查詢與“王永明”在同一個(gè)系學(xué)習(xí)的學(xué)生。SELECTs1.學(xué)號,s1.姓名,s1.系FROMss1,ss2WHEREs1.系=s2.系A(chǔ)NDs2.姓名='王永明'

andS1.姓名!='王永明'6.1.2簡單查詢【例6.39】在數(shù)據(jù)表C中求每一門課程的間接先行課。如果Y是X的先行課程，Z是Y的先行課程，則Z是X的間接先行課程。

SELECTfirst.課程號,second.預(yù)選課程號FROMCASfirst,CASsecondWHEREfirst.預(yù)選課程號=second.課程號1．內(nèi)連接1）格式SELECT列名FROM表1INNERJOIN表2ON<連接的條件>2）功能INNERJOIN內(nèi)連接按照ON指定的連接條件合并兩個(gè)表，只返回滿足條件的行，也可用于多個(gè)表的連接。只返回符合查詢條件或連接條件的行作為結(jié)果集，即刪除所有不符合限定條件的行?！纠?.40】查詢選修了c01號課程且成績及格的學(xué)生姓名及成績。其中，INNER關(guān)鍵字可省略。SELECT姓名,成績

FROMsJOINscONs.學(xué)號=sc.學(xué)號--左表是s,右表是scWHERE課程號='c01'AND成績>=60或SELECT姓名,成績FROMS,SCWHERES.學(xué)號=SC.學(xué)號and課程號='C01'AND成績>=60

6.3.2JOIN連接2．外連接外連接不但包含滿足條件的行，還包括相應(yīng)表中的所有行，只能用于兩個(gè)表的連接。實(shí)際上基本表的外連接操作可以分為3類:1）左外連接（1）格式SELECT列名FROM表1LEFTOUTERJOIN表2ON<連接的條件>JOIN（2）功能返回滿足條件的行,及左表R中所有的行。如果左表的某條記錄在右表中沒有匹配記錄，則在查詢結(jié)果中右表的所有選擇屬性列用NULL填充。6.3.2JOIN連接2．外連接2）右外連接（1）格式SELECT列名FROM表1RIGHTOUTERJOIN表2ON<連接的條件>（2）功能返回滿足條件的行,及右表K所有的行。如果右表的某條記錄在左表中沒有匹配記錄，則在查詢結(jié)果中左表的所有選擇屬性列用NULL填充。3）全外連接（1）格式SELECT列名FROM表1FULLOUTERJOIN表2ON<連接的條件>（2）功能返回滿足條件的行,及左右表R、K所有的行。當(dāng)某條記錄在另一表中沒有匹配記錄，則在查詢結(jié)果中對應(yīng)的選擇屬性列用NULL填充。其中，OUTER關(guān)鍵字均可省略。6.3.2JOIN連接【例6.41】檢索每個(gè)學(xué)生的姓名、選修課程和成績，沒有選修的同學(xué)也列出。方法一：左連接，將左表S中的所有記錄顯示出來，不管右邊SC表中有沒有對應(yīng)記錄。SELECTS.姓名,SC.課程號,SC.成績FROMSLEFTJOINSCONS.學(xué)號=SC.學(xué)號--左表是S,右表是SC

或方法二：右連接，將右表S中的所有記錄顯示出來，不管左表SC表中有沒有對應(yīng)記錄。

SELECTS.姓名,SC.課程號,SC.成績FROMSCRIGHTJOINSONS.學(xué)號=SC.學(xué)號--左表是SC,右表是S【例6.42】檢索每個(gè)學(xué)生的姓名、選修課程，沒有選修的同學(xué)和沒有被選修的課程也列出。

SELECTSSC.姓名,C.課程號FROMCFULLJOIN(SELECTS.姓名,SC.課程號,SC.成績FROMSLEFTJOINSCONS.學(xué)號=SC.學(xué)號)ASSSC--子查詢,查詢結(jié)果為記錄集，定義結(jié)果記錄集別名為SSC。ONC.課程號=SSC.課程號

數(shù)據(jù)表C與子查詢結(jié)果集SSC完全外連接。3．交叉連接（1）格式SELECT列名FROM表1CROSSJOIN表2（2）功能相當(dāng)于廣義笛卡爾積。不能加篩選條件，即不能帶WHERE子句。結(jié)果表是第一個(gè)表的每行與第二個(gè)表的每行拼接后形成的表，結(jié)果表的行數(shù)等于兩個(gè)表行數(shù)之積?！纠?.43】列出所有學(xué)生所有可能的選課情況。SELECT學(xué)號,姓名,課程號,課程名FROMsCROSSJOINc如果有七名學(xué)生，五門課程，則交叉連接后，記錄行數(shù)為35行。6.3.2JOIN連接1．聚合函數(shù)SQL提供了許多集合函數(shù)，用來統(tǒng)計(jì)匯總信息，常用的包括以下幾種。COUNT（[DISTINCT/ALL]*）：統(tǒng)計(jì)元組的個(gè)數(shù)。COUNT（[DISTINCT/ALL]數(shù)值表達(dá)式）：統(tǒng)計(jì)一列中值的個(gè)數(shù),除非使用DISTINCT，否則，重復(fù)元組的個(gè)數(shù)也計(jì)算在內(nèi)。SUM（[DISTINCT/ALL]數(shù)值表達(dá)式）：計(jì)算一列值的總和（該列必須是數(shù)值型）AVG（[DISTINCT/ALL]數(shù)值表達(dá)式）：計(jì)算一類值的平均值（該列必須是數(shù)值型）MAX（[DISTINCT/ALL]表達(dá)式）：求一列值中的最大值MIN（[DISTINCT/ALL]表達(dá)式）：求一列值中的最小值

聚合函數(shù)對一組值執(zhí)行計(jì)算，并返回單個(gè)值。除了COUNT以外，聚合函數(shù)都會(huì)忽略空值。在SELECT子句、HAVING子句和COMPUTE子句或COMPUTEBY子句中可以使用聚合函數(shù)，在WHERE子句中不能使用聚合函數(shù)。6.4聚合函數(shù)【例6.44】求男學(xué)生的總?cè)藬?shù)和平均年齡。SELECTCOUNT(*)ASRENCOUNT,avg(year(getdate())-year(出生日期))as平均年齡FROMSWHERE性別='男'【例6.45】查詢選修了c01課程的總?cè)藬?shù)及這門課的平均成績。SELECTCOUNT(DISTINCT學(xué)號)AS選修c01課程人數(shù),AVG(成績)AS平均成績FROMscWHERE課程號='c01‘【例6.46】查詢選修了c01課程的學(xué)生的最高分和最低分SELECTMAX(成績)AS課程c01最高分,MIN(成績)AS課程c01最低分FROMscWHERE課程號='c01'6.4聚合函數(shù)1．格式SELECT列名表(逗號隔開)FROM基本表或視圖序列[WHERE條件表達(dá)式]--條件子句[GROUPBY[ALL]屬性名表]--分組子句[HAVING組條件表達(dá)式]--組條件子句2．功能根據(jù)分組子句[GROUPBY屬性名表]對表中記錄行進(jìn)行分組。使用了GROUPBY子句后，將為結(jié)果集中的每一行產(chǎn)生聚合值。6.5分組匯總與分類匯總6.5.1分組匯總6.5分組匯總與分類匯總(1) text、ntext、image類型的屬性列不能用于分組表達(dá)式。(2) SELECT子句中的列表只能包含在GROUPBY中指定的列或在聚合函數(shù)中指定的列。例如執(zhí)行如下命令。SELECT學(xué)號,課程號FROMSCGROUPBY課程號

將會(huì)出現(xiàn)“列'SC.學(xué)號'在選擇列表中無效，因?yàn)樵摿屑炔话诰酆虾瘮?shù)中，也不包含在GROUPBY子句中”的錯(cuò)誤信息。解決方法：方法一：將SELECT子句中的字段學(xué)號刪除。SELECT課程號FROMSCGROUPBY課程號方法二：在GROUPBY子句中增加學(xué)號字段。SELECT學(xué)號,課程號FROMSCGROUPBY學(xué)號,課程號【例6.47】從sc表中查詢被學(xué)生選修了的課程。SELECT課程號FROMscGROUPBY課程號【例6.48】查詢數(shù)據(jù)庫中各個(gè)系的學(xué)生人數(shù)。SELECTCOUNT(*)AS各系學(xué)生人數(shù)FROMsGROUPBY系【例6.49】查詢每個(gè)學(xué)生課程成績在90分以上的課程的學(xué)號、課程號和平均成績。SELECT學(xué)號,課程號，avg(成績)as平均成績FROMscWHERE成績>88GROUPBY學(xué)號,課程號6.5.1分組匯總【例6.50】查詢每個(gè)學(xué)生課程成績在90分以上的課程的平均成績（不滿足的成績以空值代替）。SELECT學(xué)號,avg(成績)as平均成績FROMscwhere成績>90GROUPBYall學(xué)號【例6.51】查詢選修課程4門及以上并且成績都在65分以上的學(xué)生的學(xué)號和平均成績。SELECT學(xué)號,AVG(成績)AS平均成績FROMscWHERE成績>=65GROUPBY學(xué)號HAVINGCOUNT(課程號)>=46.5.1分組匯總關(guān)鍵字功能WITHROLLUP生成的結(jié)果集顯示了所選列中值的某一層次結(jié)構(gòu)的聚合，顯示在最后一行最后一個(gè)單元格WITHCUBE用于顯示各個(gè)字段所有組合的聚合值6.5.2分類匯總1.WITHROLLUP子句1) 格式

SELECT<列表>［INTO<新表>］FROM<基本表>(或視圖序列)[WHERE<條件表達(dá)式>][ORDERBY屬性名[ASC|DESC][,…n]]--排序子句[GROUPBY[ALL]屬性名表]--分組子句[WITHROLLUP]

2) 功能ROLLUP運(yùn)算符生成的結(jié)果集類似于CUBE運(yùn)算符生成的結(jié)果集。ROLLUP子句與GROUPBY聯(lián)合使用，ROLLUP結(jié)果集中比GROUPBY語句結(jié)果集多了groupby后面按第一個(gè)字段各分類的匯總信息行以及對第一個(gè)字段分類匯總信息行的總合計(jì)信息行。同樣對【例6.49】操作：查詢每個(gè)學(xué)生的課程成績在90分以上的課程的學(xué)號、課程號和平均成績。SELECT學(xué)號,課程號,avg(成績)as平均成績

FROMSCwhere成績>90GROUPBY學(xué)號,課程號withrollup1）格式SELECT<列表>［INTO<新表>］FROM<基本表>(或視圖序列)[WHERE<條件表達(dá)式>][ORDERBY屬性名[ASC|DESC][,…n]]--排序子句[GROUPBY[ALL]屬性名表]--分組子句[withCUBE]2）功能CUBE子句與GROUPWITH聯(lián)合使用，可以按照GROUPBY中指定的字段對整個(gè)數(shù)據(jù)表進(jìn)行分類匯總。分類匯總時(shí)，會(huì)對GROUPBY中指定的所有字段進(jìn)行組合匯總。并且使用GROUPING函數(shù)將執(zhí)行匯總所得的那條記錄標(biāo)識為‘1’，可以很清楚地區(qū)分查詢結(jié)果中帶‘NULL’值的記錄，哪些是原記錄中含有NULL值字段的記錄，而哪些是新生成的匯總記錄。2.WITHCUBE子句同樣對【例6.49】操作：查詢每個(gè)學(xué)生的課程成績在90分以上的課程的學(xué)號、課程號和平均成績。SELECT學(xué)號,課程號,avg(成績)as平均成績

FROMSCwhere成績>90GROUPBY學(xué)號,課程號withcube

子查詢(也叫嵌套查詢)是一個(gè)嵌套在SELECT、INSERT、UPDATE或DELETE語句或其他子查詢中的WHERE子句或HAVING子句的條件中的SELECT查詢，它要用圓括號括起來，即在查詢條件中，可以使用另一個(gè)查詢的結(jié)果作為條件的一部分的查詢。如果子查詢返回的是單個(gè)值，則任何允許使用表達(dá)式的地方都可以使用子查詢。子查詢與外查詢的語法相同，包含子查詢的語句也稱為外部查詢。子查詢的層層嵌套方式反映了SQL語言的結(jié)構(gòu)化。一個(gè)SELECT-FROM-WHERE語句稱為一個(gè)查詢塊。子查詢不能使用ORDERBY子句，但有些子查詢可以用連接運(yùn)算替代。通常使用子查詢表示時(shí)可以使復(fù)雜的查詢分解為一系列的邏輯步驟，條理清晰，但它的查詢最終結(jié)果只能來自于一張表，而使用連接查詢時(shí)，它的查詢結(jié)果可以來自于多張表并有執(zhí)行速度快的優(yōu)點(diǎn)。6.6子查詢6.6.1子查詢的制約規(guī)則(1)子查詢的選擇列表中不允許出現(xiàn)ntext、text和image數(shù)據(jù)類型。(2)包括GROUPBY的子查詢不能使用DISTINCT關(guān)鍵字。(3)不能指定INTO子句。(4)只有在子查詢中使用TOP關(guān)鍵字，才可以指定ORDERBY子句。(5)由子查詢創(chuàng)建的視圖不能更新。(6)如果外部查詢的WHERE子句包括某個(gè)列名，則該子句必須與子查詢選擇列表中的該列在連接上兼容。(7)通過比較運(yùn)算符引入的子查詢選擇列表只能包括一個(gè)表達(dá)式或列名稱(對SELECT*執(zhí)行的EXISTS或?qū)α斜韴?zhí)行的IN子查詢除外)。(8)由于必須返回單個(gè)值，所以由未修改的比較運(yùn)算符(即后面未跟關(guān)鍵字ANY或ALL的運(yùn)算符)引入的子查詢不能包含GROUPBY和HAVING子句。(9)不能在ORDERBY、GROUPBY之后使用子查詢。(10) 子查詢中不能使用UNION關(guān)鍵字。(11) LOB(大對象數(shù)據(jù))類型的字段不能作為子查詢的比較條件。6.6子查詢6.6.2無關(guān)子查詢（不相關(guān)子查詢）

不相關(guān)子查詢的查詢條件不依賴于父查詢，是由里向外逐層處理。即每個(gè)子查詢在上一級查詢處理之前求解，子查詢的結(jié)果用于建立其父查詢的查找條件。

許多包含子查詢的T-SQL語句都可以改為用連接表示。例如，那些在語義上等效但在性能方面通常沒有區(qū)別的查詢都可以采用子查詢的語句或不包括子查詢的連接語句。但是，在一些必須檢查存在性的情況中，使用連接會(huì)產(chǎn)生更好的性能。因?yàn)榘ㄗ硬樵兊恼Z句為確保消除重復(fù)值，必須為外部查詢的每個(gè)結(jié)果都處理嵌套查詢。6.6.2無關(guān)子查詢（不相關(guān)子查詢）1．用作查詢語句中的列表達(dá)式的子查詢【例6.53】查詢學(xué)生的學(xué)號、姓名、所有學(xué)生所有課程的最高成績。

SELECTdistinctS.學(xué)號,S.姓名,(SELECTMAX(成績)FROMSC)ASmax成績FROMS,SCWHERES.學(xué)號=SC.學(xué)號6.6.2無關(guān)子查詢（不相關(guān)子查詢）【例6.54】查詢學(xué)生的學(xué)號、課程號及對應(yīng)成績與所有學(xué)生所有課程的最高成績的百分比。SELECT學(xué)號,課程號,與最高成績之百分比=成績*100/(SELECTmax(成績)fromSC)FROMSC

【例6.55】查找學(xué)號為J0401的成績、全部課程的平均成績，以及每門成績與全部課程的平均成績之間的距離。SELECT學(xué)號,成績,(SELECTAVG(成績)FROMSC)ASaverage,

成績-(SELECTAVG(成績)FROMSC)ASdifferenceFROMSCWHERE學(xué)號='J0401'6.6.2無關(guān)子查詢（不相關(guān)子查詢）【例6.56】查詢陳小紅同學(xué)的學(xué)號及所選修的課程號。方法一：SELECT子查詢。SELECT學(xué)號,課程號FROMscWHERE學(xué)號=(SELECT學(xué)號FROMsWHERE姓名='陳小紅')該示例也可以用以下SELECT連接查詢獲得相同的結(jié)果集。方法二：SELECT連接SELECT學(xué)號,課程號FROMsJOINscON(s.學(xué)號=sc.學(xué)號)WHERE姓名='陳小紅'

2．使用比較運(yùn)算符的子查詢6.6.2無關(guān)子查詢（不相關(guān)子查詢）如果上面示例的查詢結(jié)果加一個(gè)“姓名”屬性，則會(huì)出錯(cuò)。

SELECT學(xué)號,課程號,S.姓名FROMSCWHERE學(xué)號=(SELECT學(xué)號FROMSWHERE姓名='陳小紅')

【例6.57】查找所有成績高于平均成績的學(xué)生的學(xué)號。

SELECTDISTINCT學(xué)號FROMSCWHERE成績>(SELECTavg(成績)FROMSC)2．使用比較運(yùn)算符的子查詢6.6.2無關(guān)子查詢（不相關(guān)子查詢）WHERE表達(dá)式1比較運(yùn)算符[NOT]ALL

(子查詢)子查詢的結(jié)果集的列必須與表達(dá)式1有相同的數(shù)據(jù)類型。結(jié)果類型為布爾型。S＞ALLR：當(dāng)S大于子查詢R中的每一個(gè)值，該條件為真TRUE。NOTS＞ALLR：當(dāng)且僅當(dāng)S不是R中的最大值，該條件為真TRUE【例6.58】查詢比所有汽車系的學(xué)生年齡都小的學(xué)生。SELECT*FROMsWHERE出生日期>ALL(SELECT出生日期FROMsWHERE系='汽車系')3．使用謂詞ALL的子查詢6.6.2無關(guān)子查詢（不相關(guān)子查詢）【例6.59】檢索不選修C02課程的學(xué)生的姓名與年齡。SELECT姓名,year(getdate())-year(出生日期)as年齡FROMSWHERE學(xué)號<>ALL(SELECT學(xué)號FROMSCWHERE課程號='C02')或用謂詞IN的子查詢(該語句在后面介紹)：SELECT姓名,year(getdate())-year(出生日期)asageFROMSWHERE學(xué)號NOTIN(SELECT學(xué)號FROMSCWHERE課程號='C02')3．使用謂詞ALL的子查詢6.6.2無關(guān)子查詢（不相關(guān)子查詢）ANY確定給定的值是否滿足子查詢或列表中的部分值。語法如下：WHERE比較運(yùn)算符[NOT]ANY

(子查詢)S＞ANYR：當(dāng)且僅當(dāng)S至少大于子查詢R中的一個(gè)值，該條件為真TRUE。NOTS＞ANYR：當(dāng)且僅當(dāng)S是子查詢R中的最小值，該條件為真TRUE?！纠?.60】檢索成績小于任何一個(gè)選修C02課程的學(xué)生的成績的學(xué)生學(xué)號。SELECTdistinct學(xué)號FROMscWHERE成績<Any(SELECT成績FROMscWHERE課程號='C02')4．使用謂詞ANY的子查詢6.6.2無關(guān)子查詢（不相關(guān)子查詢）【例6.61】查詢選修c02課程且成績不低于選修c01課程的最低成績的學(xué)生的學(xué)號。SELECT學(xué)號FROMSCWHERE課程號='C02'AND成績!<ANY(SELECT成績FROMSCWHERE課程號='C01')4．使用謂詞ANY的子查詢6.6.2無關(guān)子查詢（不相關(guān)子查詢）IN確定給定的值是否與子查詢或列表中的值相匹配。語法如下：WHERE表達(dá)式[NOT]IN

(子查詢|表達(dá)式1[,...n])SINR：當(dāng)且僅當(dāng)S等于R中的一個(gè)值，該條件為真TRUE。SNOTINR：當(dāng)且僅當(dāng)S不屬于R中的一個(gè)值，該條件為真TRUE。5．使用謂詞IN的子查詢6.6.2無關(guān)子查詢（不相關(guān)子查詢）【例6.62】查詢選修了數(shù)據(jù)庫課程成績在80分以上的學(xué)生的學(xué)號及成績。SELECT學(xué)號,成績FROMSCWHERE課程號IN(SELECT課程號FROMCWHERE課程名='數(shù)據(jù)庫')AND成績>=80【例6.63】求選修了課程C02和C04的學(xué)生的學(xué)號和姓名。SELECTS.學(xué)號,S.姓名FROMS,SCWHERES.學(xué)號=SC.學(xué)號AND課程號='C02'ANDS.學(xué)號IN(SELECT學(xué)號FROMSCWHERE課程號='C04')5．使用謂詞IN的子查詢6.6.2無關(guān)子查詢（不相關(guān)子查詢）【例6.64】求選修了課程C02但沒有選修課程C04的學(xué)生的學(xué)號和姓名。SELECTS.學(xué)號,S.姓名FROMS,SCWHERES.學(xué)號=SC.學(xué)號AND課程號='C02'ANDS.學(xué)號notIN(SELECT學(xué)號FROMSCWHERE課程號='C04')【例6.65】查詢沒有選修C01課程的學(xué)生姓名。SELECT學(xué)號,姓名FROMSWHERE學(xué)號

NOTIN(SELECT學(xué)號FROMscWHERE課程號='C01')5．使用謂詞IN的子查詢6.6.3相關(guān)子查詢1．謂詞運(yùn)算符EXISTS使用EXISTS引入的子查詢語法如下：WHERE[NOT]EXISTS(子查詢)按約定，通過EXISTS引入的子查詢的選擇列表由星號(*)組成，而不使用單個(gè)列名。由于通過EXISTS引入的子查詢進(jìn)行了存在測試，外部查詢的WHERE子句測試子查詢返回的行是否存在。EXISTSR：當(dāng)且僅當(dāng)R非空時(shí)，該條件為真。NOTEXISTSR：當(dāng)且僅當(dāng)R為空時(shí)，該條件為真。子查詢實(shí)際上不產(chǎn)生任何數(shù)據(jù)；只返回TRUE或FALSE，所以這些子查詢的規(guī)則與標(biāo)準(zhǔn)選擇列表的規(guī)則完全相同。6.6.3相關(guān)子查詢【例6.66】查詢所有選修了C01課程的學(xué)生姓名。SELECT姓名FROMSWHEREEXISTS(SELECT*FROMSCWHERE學(xué)號=S.學(xué)號AND課程號='C01’)【例6.67】查詢沒有選修C01課程的學(xué)生姓名及性別。SELECT姓名,性別FROMSWHERENOTEXISTS(SELECT*FROMSCWHERE學(xué)號=S.學(xué)號AND課程號='C01')1．謂詞運(yùn)算符EXISTS6.6.3相關(guān)子查詢【例6.68】查找最高成績超過給定學(xué)生平均成績10分的學(xué)生。方法一:SELECTt1.學(xué)號FROMSCt1GROUPBYt1.學(xué)號

HAVINGMAX(t1.成績)>=ALL(SELECT10+AVG(t2.成績)FROMSCt2WHEREt1.學(xué)號=t2.學(xué)號)在上例中，為外部查詢中定義的每一個(gè)組各評估一次子查詢(每次一個(gè)學(xué)生)。2．HAVING子句中的相關(guān)子查詢6.6.3相關(guān)子查詢【例6.68】查找最高成績超過給定學(xué)生平均成績10分的學(xué)生。方法二:SELECTt1.學(xué)號FROMSCt1GROUPBYt1.學(xué)號

HAVINGMAX(t1.成績)>=10+MAX(t1.成績)2．HAVING子句中的相關(guān)子查詢6.6.4子查詢的多層嵌套子查詢自身可以包括一個(gè)或多個(gè)子查詢。一個(gè)語句中可以嵌套任意數(shù)量的子查詢，執(zhí)行時(shí)從里向外逐層處理?！纠?.69】求選修了“C語言”課程的學(xué)生的學(xué)號和姓名。SELECT學(xué)號,姓名FROMSWHERE學(xué)號IN(SELECT學(xué)號FROMSCWHERE課程號IN(SELECT課程號FROMCWHERE課程名='C語言'))6.6.4子查詢的多層嵌套【例6.70】檢索學(xué)習(xí)全部課程的學(xué)生姓名。方法一：SELECT姓名FROMSWHERENOTEXISTS(SELECT*FROMCWHERENOTEXISTS(SELECT*FROMSCWHERESC.學(xué)號=S.學(xué)號

ANDSC.課程號=C.課程號))6.6.4子查詢的多層嵌套【例6.70】檢索學(xué)習(xí)全部課程的學(xué)生姓名。方法二：SELECT姓名FROMSWHERE(SELECTcount(*)FROMSCWHERES.學(xué)號=SC.學(xué)號)=(SELECTcount(*)FROMC)【例6.71】陳小紅的成績加2分。該查詢更新sc表；其子查詢引用s表。UPDATESCSET成績=成績+2WHERE學(xué)號IN(SELECT學(xué)號FROMSWHERE姓名='陳小紅')下面是使用連接的等效UPDATE語句：UPDATESCSET成績=成績+2FROMSCINNERJOINSONS.學(xué)號=SC.學(xué)號AND姓名='陳小紅'goselect*fromSC6.6.5UPDATE、INSERT和DELETE語句中的子查詢【例6.72】當(dāng)C04課的成績低于該門課程平均成績時(shí)，提高5%。UPDATESCSET成績=成績*1.05WHERE課程號='C04'AND成績<(SELECTAVG(成績)FROMSCWHERE課程號='C04')【例6.73】將SC表中成績最高的減去20分。updateSCset成績=成績-20whereSC.成績=(selectmax(成績)fromSC)6.6.5UPDATE、INSERT和DELETE語句中的子查詢【例6.74】將學(xué)生成績排在前5名的成績減去20分。UpdateSCsetSC.成績=SC.成績-20from(selecttop5*fromSCorderby成績desc)ast1wheret1.成績=SC.成績【例6.75】將總分在前10%的學(xué)生的成績減去20分。updateSCsetSC.成績=SC.成績-20from(selecttop10percent學(xué)號,sum(成績)as總分fromSCgroupby學(xué)號orderby總分desc)ast1wheret1.學(xué)號=SC.學(xué)號6.6.5UPDATE、INSERT和DELETE語句中的子查詢【例6.76】把目前為止還沒有選修課程的學(xué)生自動(dòng)增加選修C01的課程的記錄并插入到SC表中。INSERTINTOSC(學(xué)號,課程號)Select學(xué)號,課程號='C01'FROMSWHERE學(xué)號NOTIN(SELECTDISTINCT學(xué)號FROMSC)【例6.77】刪除陳小紅的所有選修記錄。DELETEFROMSCWHERE學(xué)號IN(SELECT學(xué)號FROMSWHERE姓名='陳小紅')下面是使用連接的等效DELETE語句：DELETESCFROMSCINNERJOINSONS.學(xué)號=SC.學(xué)號AND姓名='陳小紅'6.6.5UPDATE、INSERT和DELETE語句中的子查詢【例6.78】把C04課程中小于該課程平均成績的成績記錄從基本表SC中刪除。DELETEFROMSCWHERE課程號='C04'AND成績<(SELECTAVG(成績)FROMSCWHERE課程號='C04')請考慮以上子查詢是否可以通過連接查詢來實(shí)現(xiàn)。6.6.5UPDATE、INSERT和DELETE語句中的子查詢1．格式SELECT查詢語句1UNION［ALL］SELECT查詢語句2［…n］2．功能

查詢結(jié)果的結(jié)構(gòu)一致時(shí)可將兩個(gè)查詢進(jìn)行并(UNION)操作。要求查詢屬性列的數(shù)目和順序都必須相同，對應(yīng)屬性列的數(shù)據(jù)類型兼容。

可使用ALL關(guān)鍵字包含重復(fù)的記錄。6.7并運(yùn)算【例6.79】查詢成績表中課程號為C02的學(xué)生學(xué)號及課程成績大于65分的學(xué)生學(xué)號。SELECT*FROMSCWHERE課程號='C02'UNIONSELECT*FROMSCWHERE成績>656.7并運(yùn)算【例6.80】求選修了課程C02或C04的學(xué)生的學(xué)號、姓名，不包含重復(fù)的記錄行。(SELECTS.學(xué)號,S.姓名FROMS,SCWHERES.學(xué)號=SC.學(xué)號AND課程號='C02')UNION(SELECTS.學(xué)號,S.姓名FROMS,SCWHERES.學(xué)號=SC.學(xué)號AND課程號='C04')6.7并運(yùn)算【例6.81】求選修了課程C02或C04的學(xué)生的學(xué)號、姓名，包含重復(fù)記錄行。(SELECTS.學(xué)號,S.姓名FROMS,SCWHERES.學(xué)號=SC.學(xué)號AND課程號='C02')UNIONall(SELECTS.學(xué)號,S.姓名FROMS,SCWHERES.學(xué)號=SC.學(xué)號AND課程號='C04')6.7并運(yùn)算【例6.82】求選修了課程C01、C02、C03的學(xué)生的學(xué)號、姓名，不包含重復(fù)記錄行。(SELECTS.學(xué)號,S.姓名FROMS,SCWHERES.學(xué)號=SC.學(xué)號AND課程號='C01')UNION(SELECTS.學(xué)號,S.姓名FROMS,SCWHERES.學(xué)號=SC.學(xué)號AND課程號='C02')UNION(SELECTS.學(xué)號,S.姓名FROMS,SCWHERES.學(xué)號=SC.學(xué)號AND課程號='C03')6.7并運(yùn)算1．格式select<屬性列表>from圖形表序列wherematch(節(jié)點(diǎn)表-(邊緣表)->節(jié)點(diǎn)表and節(jié)點(diǎn)表-(邊緣表)->節(jié)點(diǎn)表……)and條件表達(dá)式Match是圖形數(shù)據(jù)庫查詢的特有語句，使用

Node-(Edge)->Node

或

Node<-(Edge)-Node

來表示Node與Node之間有某種關(guān)聯(lián)。6.8圖形表的查詢2．功能當(dāng)查詢關(guān)系的時(shí)候，需要新的條件關(guān)鍵字MATCH，它的使用有些限制：（1）MATCH中的邊緣名不能重復(fù)（解決：gobygoby1,gobygoby2）（2）MATCH模式中不支持OR和NOT運(yùn)算符。在WHERE子句中使用AND可以將MATCH與其他表達(dá)式結(jié)合使用。但是不支持使用OR或NOT將其與其他表達(dá)式結(jié)合使用。6.8圖形表的查詢【例6.83】查詢選修c04課程的學(xué)生學(xué)號，姓名，課程名，成績信息。查詢李麗選修的課程信息，顯示學(xué)生學(xué)號，姓名，課程名，成績信息。select學(xué)號,姓名,課程名,成績froms2,c2,sc2wherematch(s2-(sc2)->c2)and課程號='C04'select學(xué)號,姓名,課程名,成績froms2,c2,sc2wherematch(s2-(sc2)->c2)and姓名='李麗'可以看到圖像表的查詢寫法更簡單一些。6.8圖形表的查詢1．語法結(jié)構(gòu)SELECT<屬性列表>--它可以是星號（*）、表達(dá)式、列表、變量等。[INTO新表]--用查詢結(jié)果集合創(chuàng)建一個(gè)新表FROM<基本表>(或視圖序列)--最多可以指定16個(gè)表或者視圖，用逗號相互隔開。

[WHERE條件表達(dá)式][GROUPBY屬性名表]--分組子句

[HAVING組條件表達(dá)式]--組條件子句

[ORDERBY屬性名[ASC|DESC]..]--排序子句2．功能SELECT子句用于指出查詢結(jié)果集合中的列數(shù)和屬性；FROM子句指出所查詢的表名以及各表之間的邏輯關(guān)系；WHERE子句：指出查詢條件，它說明將表中的哪些數(shù)據(jù)行返回到結(jié)果集合中；ORDERBY子句：說明查詢結(jié)果行的排列順序；GROUPBY、HAVING子句查詢結(jié)果集合中各行的統(tǒng)計(jì)方法。6.9SELECT查詢語句總結(jié)3．語句執(zhí)行過程1）讀取FROM子句中的基本表、視圖和數(shù)據(jù)，執(zhí)行廣義笛卡爾積操作。2）選取滿足WHERE子句中給出的條件表達(dá)式的元組。3）按GROUPBY子句中指定列的值分組，同時(shí)提取滿足HAVING子句中組條件表達(dá)式的那些組。4）按SELECT子句中給出的列名或列表達(dá)式求值輸出。5）ORDERBY子句對輸出的目標(biāo)表進(jìn)行排序，按ASC升序排列，或按DESC降序排列。6.8SELECT查詢語句總結(jié)設(shè)學(xué)生選課庫中有三個(gè)數(shù)據(jù)表：S(學(xué)號,姓名,出生日期,系,電話,性別)SC(學(xué)號,課程號,成績)C(課程號,課程名,教師,預(yù)選課程號)完成下面查詢?nèi)蝿?wù)。【例6.85】檢索學(xué)習(xí)課程號為C02的學(xué)生學(xué)號與成績。SELECT學(xué)號,成績

FROMSCWHERE課程號='C02'6.8SELECT查詢語句總結(jié)【例6.86】檢索學(xué)習(xí)課程號為C02的學(xué)生學(xué)號與姓名。SELECTS.學(xué)號,姓名

FROMS,SCWHERES.學(xué)號=SC.學(xué)號AND課程號='C02'【例6.87】在SC中檢索預(yù)選課程預(yù)選課程號為空值的課程信息。SELECT*FROMCWHERE預(yù)選課程號isNULL【例6.88】在C中檢索預(yù)選課程預(yù)選課程號不為空值的課程信息。SELECT*FROMCWHERE預(yù)選課程號isnotNULL6.8SELECT查詢語句總結(jié)【例6.89】檢索選修課程名為“數(shù)據(jù)庫”的學(xué)生學(xué)號與姓名。SELECTS.學(xué)號,姓名

FROMS,SC,CWHERES.學(xué)號=SC.學(xué)號ANDSC.課程號=C.課程號AND課程名='數(shù)據(jù)庫'【例6.90】檢索選修課程號為C02或C04的學(xué)生學(xué)號。SELECT學(xué)號

FROMSCWHERE課程號='C02'OR課程號='C04'6.8SELECT查詢語句總結(jié)【例6.91】檢索至少選修課程號為C02和C04的學(xué)生學(xué)號。方法一：SELECT自身連接。SELECTx.學(xué)號

FROMSCASx,SCASyWHEREx.學(xué)號=y.學(xué)號ANDx.課程號='C02'ANDy.課程號='C04'方法二：SELECT子查詢。SELECT學(xué)號FROMSCWHERE課程號='C02'AND學(xué)號IN(SELECT學(xué)號FROMSCWHERE課程號='C04')6.8SELECT查詢語句總結(jié)【例6.92】在SC中檢索男同學(xué)選修的課程號。SELECTDISTINCT課程號

FROMS,SCWHERES.學(xué)號=SC.學(xué)號AND性別='男'【例6.93】在S中檢索18～20歲的學(xué)生姓名。SELECT姓名

FROMSWHERE(year(getdate())-year(出生日期))>=18AND(year(getdate())-year(出生日期))<=206.8SELECT查詢語句總結(jié)【例6.94】查找每個(gè)學(xué)生的學(xué)號及選修課程的平均成績情況，按學(xué)號排序。SELECTS.學(xué)號,AVG(SC.成績)AS平均成績

FROMSCINNERJOINSONdbo.SC.學(xué)號=S.學(xué)號

GROUPBYS.學(xué)號

ORDERBYS.學(xué)號6.8SEL