關(guān)系數(shù)據(jù)庫設(shè)計與應(yīng)用（工作手冊式） 課件 第6、7章 SQL查詢、SQL的高級功能

第6章SQL查詢學(xué)習(xí)目標(biāo)LEARNINGTARGET掌握查詢語句的基本結(jié)構(gòu)；掌握投影、選擇及連接等基本查詢操作。知識目標(biāo)能力目標(biāo)會用SQL語句進行基本查詢；能進行聚集查詢、連接查詢、子查詢和集合查詢等高級查詢。思政目標(biāo)養(yǎng)成嚴(yán)謹認真的學(xué)習(xí)態(tài)度；培養(yǎng)團隊協(xié)作精神；了解大數(shù)據(jù)時代的工匠精神，并將其融入學(xué)習(xí)和工作當(dāng)中。主要內(nèi)容MAINCONTENTS6.1SQL基本查詢語句6.2聚集查詢6.3連接查詢6.4子查詢6.5集合查詢6.6關(guān)于引用AS指定名字的規(guī)則SQL基本查詢語句6.1SQL基本查詢語句6.1.1查詢語句的基本結(jié)構(gòu)6.1.2投影6.1.3選擇6.1.4對查詢結(jié)構(gòu)進行排序SQL基本查詢語句數(shù)據(jù)檢索就是把數(shù)據(jù)庫中存儲的數(shù)據(jù)根據(jù)用戶的需求提取出來的過程。SQL語言中查詢語句使用SELECT語句來執(zhí)行；查詢語句是數(shù)據(jù)操作中最基本和最重要的語句之一，其功能是從數(shù)據(jù)庫中檢索滿足條件的數(shù)據(jù)；查詢的數(shù)據(jù)源可以是一張表，也可以是多張表，甚至是視圖；查詢的結(jié)果是由0行或多行記錄組成的一個記錄集合，并允許選擇一個或多個字段作為輸出字段；SELECT語句還可以對查詢的結(jié)果進行排序，匯總等。6.1.1查詢語句的基本結(jié)構(gòu)SQL基本查詢語句select語句具有數(shù)據(jù)查詢、統(tǒng)計、分組和排序的功能。查詢語句的基本格式：select<目標(biāo)列組>

--需要輸出哪些列from<數(shù)據(jù)源>--來自于哪些表[where<元組選擇條件>]--根據(jù)什么條件[groupby<分組依據(jù)列>][having<組提取條件>][orderby<排序依據(jù)列>]6.1.1查詢語句的基本結(jié)構(gòu)SQL基本查詢語句select子句：用于指明查詢結(jié)果集的目標(biāo)列。目標(biāo)列可以是直接從數(shù)據(jù)源中投影得到的屬性以及與屬性相關(guān)的表達式或數(shù)據(jù)統(tǒng)計的函數(shù)表達式，目標(biāo)列甚至還可以是常數(shù)。from子句：用于指明查詢的數(shù)據(jù)源，數(shù)據(jù)源可以是基本表或視圖。where子句：描述選擇條件。groupby子句：將查詢結(jié)果的各行按一列取值相等的原則進行分組，如果有having短語，則查詢結(jié)果只是滿足指定條件的組。orderby子句：查詢結(jié)果按一定順序排序。注意：以上子句中，select子句和from子句是必須的，其他子句都是可選的。在SQL語句中，關(guān)鍵字對大小寫不敏感；“--”表示注釋。6.1.1查詢語句的基本結(jié)構(gòu)SQL基本查詢語句檢索學(xué)生表中所有學(xué)生的全部信息檢索每個學(xué)生的姓名和班級Select

from

Select

from

*學(xué)生表注意：可以用

*

代表關(guān)系中所有的屬性；當(dāng)選擇某幾個屬性時，屬性之間用逗號(英文狀態(tài)下)分開；返回的關(guān)系中的屬性順序和Select子句列出的屬性順序相同。姓名,班級學(xué)生表1.選擇列6.1.2投影SQL基本查詢語句改變列標(biāo)題有兩種方法：使用“=”；使用AS關(guān)鍵字。①當(dāng)使用“=”時，語法形式為：新標(biāo)題=列名如：select

學(xué)號,姓名,所在班級=班級

from

學(xué)生表②當(dāng)使用“AS”時，語法形式為：列名AS新標(biāo)題如：select學(xué)號,姓名,班級AS所在班級from學(xué)生表2.改變列標(biāo)題在默認情況下，數(shù)據(jù)檢索結(jié)果集中所顯示出來的列標(biāo)題就是在創(chuàng)建表時所使用的列名。6.1.2投影SQL基本查詢語句例如：要求出成績提高20%后的成績，則可用如下表達式描述：select

學(xué)號,課程號,成績,成績*1.2AS調(diào)整后的成績成績from

選課表3.帶表達式的select子句select子句中可以包含算術(shù)表達式，允許+、-、*、/，當(dāng)然也可以是常量表達式。在查詢結(jié)果中，還可以增加一些常量，改變查詢結(jié)果的顯示格式，提高結(jié)果關(guān)系中數(shù)據(jù)的可讀性。注意：比較增加一列與改列名的區(qū)別（AS關(guān)鍵字使用）如：增加一常量列：增加‘屆別’列，并取名為“2012級學(xué)生”select

*,‘2012級學(xué)生’AS屆別

from

學(xué)生表6.1.2投影SQL基本查詢語句例如：檢索出所有的班級信息select

班級from

學(xué)生表selectdistinct

班級from

學(xué)生表4.ALL與DISTINCTSQL允許關(guān)系和查詢結(jié)果中出現(xiàn)重復(fù)行，要強制消除重復(fù)，可在select后使用關(guān)鍵字DISTINCT，而指定ALL則不消除重復(fù)。在SQLServer中，默認為ALL。注意：關(guān)鍵字DISTINCT針對的是select子句后面的所有屬性不能出現(xiàn)重復(fù)，而不是針對select后面第一個屬性不能重復(fù)。6.1.2投影SQL基本查詢語句5.用TOP關(guān)鍵字返回部分數(shù)據(jù)如果SELECT查詢的結(jié)果集非常大，可以使用TOP關(guān)鍵字限制其返回的行數(shù)。返回行數(shù)的方法有兩種：一是可以指定返回記錄的數(shù)量，二是可以指定返回記錄的比例。（1）用“TOPn”表示返回最前面的n行記錄；（2）用“TOPnpercent”表示返回最前面的n%行記錄。6.1.2投影例：查詢學(xué)生表前10行信息。SELECTTOP10*FROM學(xué)生表SQL基本查詢語句5.用TOP關(guān)鍵字返回部分數(shù)據(jù)如果SELECT查詢的結(jié)果集非常大，可以使用TOP關(guān)鍵字限制其返回的行數(shù)。返回行數(shù)的方法有兩種：一是可以指定返回記錄的數(shù)量，二是可以指定返回記錄的比例。（1）用“TOPn”表示返回最前面的n行記錄；（2）用“TOPnpercent”表示返回最前面的n%行記錄。6.1.2投影例：查詢學(xué)生表前10%的學(xué)生信息。SELECTTOP10percent*FROM學(xué)生表SQL基本查詢語句根據(jù)要求完成查詢語句：①

給學(xué)生表增加一列，標(biāo)題為“系別”，內(nèi)容為“計算機”。②將課程表中的“教師”列的列名改成“教師姓名”。（分別用兩種方法）③查詢所有已經(jīng)被學(xué)生選過的課程號。課堂練習(xí)1：15分鐘SQL基本查詢語句根據(jù)要求完成查詢語句：①給學(xué)生表增加一列，標(biāo)題為“系別”，內(nèi)容為“計算機”。select*,'計算機'as系別from學(xué)生表

將課程表中的“教師”列的列名改成“教師姓名”。（分別用兩種方法）select課程號,課程名,教師as教師姓名,周課時數(shù),備注from課程表select課程號,課程名,教師姓名=教師,周課時數(shù),備注from課程表③查詢所有已經(jīng)被學(xué)生選過的課程號。selectdistinct課程號from選課表課堂練習(xí)1參考答案SQL基本查詢語句在SELECT語句中，WHERE子句指定要檢索的數(shù)據(jù)行，只有滿足關(guān)鍵字WHERE中指定條件的元組才能出現(xiàn)在結(jié)果關(guān)系中。1.比較運算符WHERE子句的語法允許在表列的名稱之后和列值之前使用比較運算符（=、>、<、>=、<=、<>），在比較運算條件的運算項中，可以使用常量或關(guān)系中的屬性，還可以使用通用的算術(shù)運算符（+、-、*、/）。例6.6：要求檢索出選課表中所有成績在95分以上的同學(xué)的學(xué)號、課程號和成績。select*from

選課表where

成績>956.1.3選擇SQL基本查詢語句1.比較運算符在上面的查詢語句中，指定的條件是數(shù)字。如果指定的條件是字符串類型的，那么需要把字符串用單引號引起來。例6.7：要求查詢出班級為GZ02計5班的全部同學(xué)的信息。select*from

學(xué)生表where

班級='GZ02計5'6.1.3選擇SQL基本查詢語句在WHERE子句中，可使用邏輯運算符把若干查詢條件連接起來，組成復(fù)合條件。這些邏輯運算符包括AND、OR、NOT。優(yōu)先級從高到低依次是NOT、AND、OR。例6.8：檢索選修了1號課程并且成績在80分以上的同學(xué)的學(xué)號和成績。SELECT

學(xué)號,成績FROM

選課表

WHERE

課程號=’1’AND

成績>802.邏輯運算符6.1.3選擇SQL基本查詢語句BETWEEN...AND...(NOTBETWEEN...AND...)可以指定搜索范圍，可以用來查找值在（不在）指定范圍內(nèi)的元組，其中BETWEEN后邊指定范圍的下限，AND后邊指定范圍的上限，表示在這個之間。例6.9：要求檢索出學(xué)生表中年齡在20-23之間的所有男同學(xué)的信息。SELECT*FROM學(xué)生表WHERE年齡BETWEEN20AND23AND性別=‘M’3.BETWEEN...AND(NOTBETWEEN...AND)6.1.3選擇SQL基本查詢語句當(dāng)然，對于

BETWEEN關(guān)鍵字，也可以使用比較運算符來代替。WHERE年齡>=20AND年齡<=23AND性別=‘M’注意：在用BETWEEN...AND...指定查詢范圍時，如果是針對char、varchar、text、datetime、smalldatetime等數(shù)據(jù)類型的數(shù)據(jù)，則一定要用單引號引起來。3.BETWEEN...AND(NOTBETWEEN...AND)6.1.3選擇SQL基本查詢語句IN或者NOTIN關(guān)鍵字允許指定要選擇的取值表，意思是包含在由IN指定的列表之中或不包含在由NOTIN指定的列表中。select*from學(xué)生表where班級in（'GZ02計5','GZ02計6','GZ02計7'）4.IN(NOTIN)例6.10：要求檢索出學(xué)生表中GZ02計5、GZ02計6、GZ02計7三個班級的同學(xué)信息。select*from

學(xué)生表where

班級='GZ02計5'or

班級='GZ02計6'or

班級='GZ02計7'提問：此題的選擇條件如何用邏輯運算符實現(xiàn)？6.1.3選擇SQL基本查詢語句如果需要從數(shù)據(jù)庫中檢索出一批記錄，但又不能給出精確的字符查詢條件，這時就可以采用字符串的模糊匹配。使用

LIKE

關(guān)鍵字。LIKE關(guān)鍵字用于檢索與特定字符串相匹配的記錄行。LIKE關(guān)鍵字后面跟一個值段而不是一個完整的列值。列名【NOT】LIKE<匹配字符串>LIKE關(guān)鍵字使用形式如下：5.字符串模糊匹配(LIKE)6.1.3選擇SQL基本查詢語句匹配字符串是一種特殊的字符串，其特殊之處在于它不僅可以包含普通字符，還可以包含通配符。通配符用于表示任意字符或字符串。在LIKE關(guān)鍵字前面也可以使用NOT，表示對結(jié)果取反。LIKE關(guān)鍵字中，可以使用4種匹配符。%（百分號）：匹配0個或多個字符；_（下劃線）：匹配任意一個字符；[]：匹配[]中的任意一個字符；[^]：不匹配[^]中的任意一個字符。5.字符串模糊匹配(LIKE)6.1.3選擇SQL基本查詢語句示例：LIKE'BR%'返回以“BR”開始的任意字符串。LIKE'%en'返回以“en”結(jié)束的任意字符串。LIKE'%en%'返回包含“en”的任意字符串。LIKE'_en'返回以“en”結(jié)束的3個字符串。LIKE'[CK]%'返回以“C”或者

“K”開始的任意字符串。LIKE'M[^C]%'返回以“M”開始且第2個字符不是“C”的任意字符串。5.字符串模糊匹配(LIKE)6.1.3選擇SQL基本查詢語句請思考以下查詢的匹配字符串設(shè)置查詢第一個字符是“A”、中間包含“BC”的字符串。查詢以“AB”開始、第四個字符是“C”，并以“d”結(jié)束的字符串。查詢第二個字符是“A”或“B”、第四個字符不是“C”的長度為6個字符的字符串。5.字符串模糊匹配(LIKE)想一想‘A%BC%’‘AB_C%d’‘_[AB]_[^C]__’6.1.3選擇SQL基本查詢語句例6.11：檢索出學(xué)生表中所有姓“陳”的同學(xué)。select*from學(xué)生表where姓名like‘[陳李劉]%’注意：在使用關(guān)鍵字LIKE進行數(shù)據(jù)檢索時，應(yīng)注意，一個英文字母和一個漢字都被當(dāng)做一個字符。5.字符串模糊匹配(LIKE)例6.12：要求查詢學(xué)生表中姓陳或姓李或姓劉的學(xué)生情況。Selectfromwhere姓名like‘陳%'學(xué)生表*

6.1.3選擇SQL基本查詢語句空值（NULL）在數(shù)據(jù)庫中有特殊的含義?？罩堤匦裕旱葍r于沒有任何值。與0、空字符串或空格不同。排序在其他數(shù)據(jù)前面。在計算過程和大多數(shù)函數(shù)中均可使用空值。6.涉及空值的查詢6.1.3選擇SQL基本查詢語句判斷是否為空值的語句格式為：列名IS[NOT]NULL例6.13：要求查詢已經(jīng)選課，但無考試成績的學(xué)生的學(xué)號和相應(yīng)的課程號。select學(xué)號，課程號from選課表where成績ISNULL6.涉及空值的查詢?nèi)绻樵兯杏锌荚嚦煽兊膶W(xué)生的學(xué)號和課程號select學(xué)號，課程號from選課表where成績ISNOTNULL6.1.3選擇SQL基本查詢語句WHERE子句常用的查詢條件如下（P136）：總結(jié)歸納序號查詢條件謂詞1比較運算=、>、<、>=、<=、<>（或！=）2邏輯運算AND、OR、NOT3確定范圍BETWEEN...AND...、(NOTBETWEEN...AND...)4確定集合IN、NOTIN5字符匹配LIKE、NOTLIKE6空值ISNULL、ISNOTNULL6.1.3選擇SQL基本查詢語句課堂練習(xí)2：①查找出所有班級為“GZ02計6”和“GZ02計7”班年齡在18至20歲之間的女學(xué)生信息②查找班級中包含’計’的男學(xué)生的學(xué)號和姓名，并在最后插入一列，標(biāo)題為“愛好”，內(nèi)容為“運動”③查所有姓張的學(xué)生學(xué)號，姓名④查所有姓張的且為單名的學(xué)生學(xué)號，姓名⑤查詢班級包含'計'或'財'字的學(xué)生信息⑥查詢班級中不包含'計'和'財'字的學(xué)生信息20分鐘SQL基本查詢語句課堂練習(xí)2答案：①select*from學(xué)生表where年齡between18and20and性別='F'and班級in('GZ02計6','GZ02計7')②select學(xué)號,姓名,班級,'運動'as愛好from學(xué)生表where班級like'%計%'and性別='M’③select學(xué)號,姓名from學(xué)生表where姓名like‘張%'④select學(xué)號,姓名from學(xué)生表where姓名like‘張_'⑤select*from學(xué)生表where班級like'%計%'or班級like'%財%‘或者select*from學(xué)生表where班級like'%[計財]%'⑥select*from學(xué)生表where班級notlike'%計%'and班級notlike'%財%'或者select*from學(xué)生表where班級notlike'%[計財]%'或者select*from學(xué)生表where班級like'%[^計][^財]%'SQL基本查詢語句在SQL語句中，排序就是指用

ORDERBY子句排列查詢結(jié)果的順序。ORDERBY只能在最終結(jié)果上進行操作。ORDERBY語句使用升序（ASC）或降序（DESC）指定一組列。系統(tǒng)默認的排列順序是升序。6.1.4對查詢結(jié)果進行排序如：ORDERBY學(xué)號ASCORDERBY學(xué)號DESCORDERBY學(xué)號SQL基本查詢語句例1：將選課表中的所有學(xué)生成績按降序排列6.1.4對查詢結(jié)果進行排序例6.14：檢索出選課表中所有選修課程號為1并且成績在95分以上的同學(xué)的學(xué)號和成績，并要求按成績從高到低的順序排列，若成績相同再按學(xué)號升序排列。SELECT

*FROM

選課表ORDERBY成績DESCSELECT

學(xué)號，成績FROM

選課表WHERE

ORDERBY課程號=1AND成績>=95成績DESC，學(xué)號

注意：對于多個排序字段的情況，按照先后，定義為主次順序。問：求學(xué)生的總?cè)藬?shù)？SQL基本查詢語句課堂練習(xí)3：①查找選擇的課程號為’1’或’4’，并且成績在80至90之間的所有選課信息，并要求按成績升序、學(xué)號降序排序。②查詢學(xué)生表中姓“王”，且最后一個字為“豐”的學(xué)生信息，并將班級列改名為“所在班級”，且按照學(xué)號升序、性別降序排序。15分鐘聚集查詢6.2聚集查詢6.2.1聚集函數(shù)6.2.2使用groupby子句6.2.3使用having子句篩選結(jié)果集6.2.4對where、groupby、having的思考聚集查詢概念：也稱為集函數(shù)、聚合函數(shù)或計算函數(shù)。作用：是對一組值進行計算并返回一個單值。用法：集函數(shù)放在select語句中列名的位置；常與groupby子句一同使用，按照給定的條件進行分組，計算一些合計值。6.2.1聚集函數(shù)聚集查詢count函數(shù)count（*）——取行數(shù)（返回所選擇行的數(shù)量）count（列名）——求某字段不為NULL(空)的行數(shù)count（distinct列名）——不計重復(fù)的值avg()——取平均值

max()——取最大值min()——取最小值sum()——求和注意：COUNT用于統(tǒng)計數(shù)量，而SUM用于值相加求和。6.2.1聚集函數(shù)聚集查詢集函數(shù)的NULL忽略策略？討論除count(*)外，其它函數(shù)在對某個字段計算時均忽略該字段的NULL值。6.2.1聚集函數(shù)聚集查詢例6.15：要求查詢出所有選修1號課程的學(xué)生的最高分、最低分及平均分。MAX(成績)AS最高分,MIN(成績)AS最低分,AVG(成績)AS平均成績選課表課程號=1SELECTFROMWHERE選課表條件統(tǒng)計值6.2.1聚集函數(shù)聚集查詢COUNT函數(shù)的用法SELECTCOUNT(*)AS所有記錄數(shù),

COUNT(學(xué)號)AS所有學(xué)生數(shù),

COUNT(班級)AS班級列數(shù)量,

COUNT(DISTINCT班級)AS班級數(shù)FROM學(xué)生表問題1：若表中無NULL值，則上面哪幾個表達式值相同。問題2：比較SUM(DISTINCT列名)與SUM(列名)的區(qū)別。6.2.1聚集函數(shù)聚集查詢提問SELECT

COUNT(*)AS所有記錄數(shù)，

COUNT(課程名)AS所有課程數(shù)，

COUNT(教師)AS教師列數(shù)量，

COUNT(DISTINCT

教師)AS教師數(shù)FROM

課程表如果課程表中共有5條記錄，其中有一條記錄中的教師為NULL值，則查詢結(jié)果中哪幾列值相同(假設(shè)教師列的值有2項重復(fù))？6.2.1聚集函數(shù)聚集查詢①查詢選課表2號課程的最高分、最低分、總分和平均分。②列出全校男同學(xué)的人數(shù)。課堂練習(xí)4：10分鐘6.2.1聚集函數(shù)聚集查詢1.作用：將表中的數(shù)據(jù)根據(jù)某一列進行分組。2.用法格式：groupby字段名常與集函數(shù)一起使用，對分組后的每組數(shù)據(jù)進行匯總集函數(shù)本身只能產(chǎn)生一個單個的匯總數(shù)據(jù)，使用groupby子句之后，就可以生成分組的匯總數(shù)據(jù)。6.2.2使用groupby子句聚集查詢

select

<目標(biāo)列組>

from<數(shù)據(jù)源>[where<元組選擇條件>][groupby<分列組>][having<組選擇條件>組][orderby<排序列1>asc|desc[，…]]3.語法格式6.2.2使用groupby子句分組字段聚集查詢例1：求各門課的課程號，最高分，最低分，平均分。統(tǒng)計值SELECT

FROM

groupby

max(成績)AS最高分,min(成績)AS最低分,avg(成績)AS平均分

選課表課程號課程號，注意：分組查詢時一般先顯示分組字段6.2.2使用groupby子句例2：如何求出每個班級的人數(shù)及平均年齡？分組字段聚集查詢

統(tǒng)計值SELECT

FROM

groupby

count(學(xué)號)AS班級人數(shù),avg(年齡)AS平均年齡學(xué)生表班級班級，注意：所有在groupby字句中指定的字段，都必須出現(xiàn)在select語句的選擇列表中。6.2.2使用groupby子句分組字段聚集查詢例3：要求計算出各門課程的選課人數(shù)以及平均分。統(tǒng)計值SELECT

FROM

groupby

count(學(xué)號)AS選課人數(shù),avg(成績)AS平均分選課表課程號課程號，小竅門：“每個”、“按”、“各”一般是提示分組字段。6.2.2使用groupby子句聚集查詢分組小結(jié)每一個組計算得到一個匯總值，并把這個匯總值保存在一個字段中。最好不要對可能包含空值的字段使用groupby字句，因為空值也將被當(dāng)做一組。所有在groupby字句中指定的字段，都必須出現(xiàn)在select語句的選擇列表中。在Select子句中出現(xiàn)的列，要么存在于groupby子句中，要么存在于集函數(shù)中。錯例：Select班級,年齡,Count(學(xué)號)As人數(shù)From學(xué)生表Groupby班級注：對應(yīng)于所指定的每一個組，只生成一條記錄，且不返回細節(jié)信息。（這里的年齡是細節(jié)信息）6.2.2使用groupby子句聚集查詢分組小結(jié)如果使用了where子句，將只對滿足where子句的記錄進行分組和匯總。Select班級,Count(學(xué)號)As人數(shù)From學(xué)生表Where性別=‘M’Groupby班級統(tǒng)計每班男生人數(shù)如何表述該題目？6.2.2使用groupby子句聚集查詢2.用法格式：Having條件在Having子句中可以引用任何允許出現(xiàn)在Select選擇列表中的字段1.作用：對分組后的結(jié)果按某種條件再進行篩選。提問：若進行了分組，則Having子句中允許出現(xiàn)哪兩類字段？6.2.3使用HAVING子句篩選結(jié)果集聚集查詢

select

<目標(biāo)列組>

from<數(shù)據(jù)源>[where<元組選擇條件>][groupby<分列組>][having<組選擇條件>組]][orderby<排序列1>asc|desc[，…]]6.2.3使用HAVING子句篩選結(jié)果集例4：要求找出人數(shù)在40人以上的那些班級？條件聚集查詢分組字段select

from

groupbyhaving

學(xué)生表班級注意：①分組前的條件用Where，分組后的條件用Having②帶集函數(shù)的條件用Havingcount（學(xué)號）>40count(學(xué)號)AS班級人數(shù)班級，6.2.3使用HAVING子句篩選結(jié)果集例5：列出選課表里同時選修了4門課的同學(xué)的學(xué)號？條件聚集查詢分組字段select

from

groupbyhaving

選課表學(xué)號count（課程號）=4count(課程號)AS課程門數(shù)學(xué)號，6.2.3使用HAVING子句篩選結(jié)果集例6：若全校人數(shù)超過700，就顯示全校人數(shù)條件聚集查詢統(tǒng)計值select

from

having

學(xué)生表count(學(xué)號)>700count(學(xué)號)AS全校人數(shù)注意：當(dāng)條件為集函數(shù)時，也可不分組，直接用Having子句6.2.3使用HAVING子句篩選結(jié)果集聚集查詢where子句用來篩選from子句中指定的操作所產(chǎn)生的行。Groupby子句用來分組where子句的輸出。having子句用來從分組的結(jié)果中篩選行。當(dāng)條件中帶有集函數(shù)時，要用having子句。6.2.4對where、groupby、having的思考聚集查詢1、列出全校每個班的人數(shù)。2、列出全校每個班的男同學(xué)的人數(shù)（兩種方法）。3、列出全校中班級人數(shù)超過30的班級名稱及班級人數(shù)。4、列出全校中班級男生人數(shù)超過20的班級名稱及男生人數(shù)。5、求各個學(xué)生的學(xué)號及相應(yīng)的選課門數(shù)。6、查詢選了一門以上課程的學(xué)生學(xué)號。課堂練習(xí)5：25分鐘連接查詢6.3連接查詢允許同時從兩個表或者兩個以上的表中檢索數(shù)據(jù)。連接類型關(guān)鍵字交叉連接CROSSJOIN內(nèi)連接（自連接）INNERJOIN外連接左外連接LEFTOUTERJOIN右外連接RIGHTOUTERJOIN全外連接FULLOUTERJOIN連接查詢6.3.1交叉連接6.3.2內(nèi)連接查詢6.3.3自連接查詢6.3.4外連接查詢連接查詢學(xué)號姓名年齡所在系98001王平20計算機98002李麗21數(shù)學(xué)98003陳紅20計算機學(xué)號課程號成績98001C19598001C38098003C18598003C275學(xué)生.學(xué)號姓名年齡所在系選課.學(xué)號課程號成績98001王平20計算機98001C19598001王平20計算機98001C38098001王平20計算機98003C18598001王平20計算機98003C27598002李麗21數(shù)學(xué)98001C19598002李麗21數(shù)學(xué)98001C38098002李麗21數(shù)學(xué)98003C19598002李麗21數(shù)學(xué)98003C27598003陳紅20計算機98001C19598003陳紅20計算機98001C38098003陳紅20計算機98003C18598003陳紅20計算機98003C275學(xué)生選課學(xué)生╳選課

連

接連接查詢學(xué)號姓名年齡所在系98001王平20計算機98002李麗21數(shù)學(xué)98003陳紅20計算機學(xué)號課程號成績98001C19598001C38098003C18598003C275學(xué)生選課學(xué)生?選課學(xué)生.學(xué)號=選課.學(xué)號等值連接學(xué)生.學(xué)號姓名年齡所在系選課.學(xué)號課程號成績98001王平20計算機98001C19598001王平20計算機98001C38098003陳紅20計算機98003C18598003陳紅20計算機98003C275

連

接連接查詢學(xué)號姓名年齡所在系98001王平20計算機98002李麗21數(shù)學(xué)98003陳紅20計算機學(xué)號課程號成績98001C19598001C38098003C18598003C275學(xué)生選課學(xué)生?選課自然連接學(xué)號姓名年齡所在系課程號成績98001王平20計算機C19598001王平20計算機C38098003陳紅20計算機C18598003陳紅20計算機C275

連

接連接查詢交叉連接即笛卡爾乘積，是指兩個表中所有記錄的任意組合。選課表與課程表兩個關(guān)系模式的乘積表示如下：Select*From選課表,課程表注意：一般情況下，交叉查詢是沒有實際意義的。6.3.1交叉連接1.笛卡爾積課程號課程名教師周課時數(shù)備注學(xué)號課程號成績1軟件工程吳老師420111284741軟件工程吳老師420111291931軟件工程吳老師420111301741軟件工程吳老師420111312842ASP程序設(shè)計黃老師420111284742ASP程序設(shè)計黃老師420111291932ASP程序設(shè)計黃老師420111301742ASP程序設(shè)計黃老師420111312843COM技術(shù)黃老師220111284743COM技術(shù)黃老師220111291933COM技術(shù)黃老師220111301743COM技術(shù)黃老師220111312844Dephi涂老師620111284744Dephi涂老師620111291934Dephi涂老師620111301744Dephi涂老師62011131284課程號課程名教師周課時數(shù)1軟件工程吳老師42ASP程序設(shè)計黃老師43COM技術(shù)黃老師24Dephi涂老師6RS學(xué)號課程號成績2011128474201112919320111301742011131284R×S連接查詢兩種表示格式：格式1：FROM表1[INNER]JOIN表2ON

<連接條件>格式2：FROM表1，表2WHERE

<連接條件>在內(nèi)連接查詢中，只有滿足條件的記錄才能出現(xiàn)在結(jié)果關(guān)系中。目前選用方式6.3.2內(nèi)連接查詢選課表連接查詢例1：要求查詢每個已經(jīng)選課的學(xué)生情況和選課情況學(xué)生表SELECT*FROM學(xué)生表,選課表WHERE學(xué)生表.學(xué)號=選課表.學(xué)號問題：查什么屬性？查什么表？連接條件？SELECT*FROM學(xué)生表INNERJOIN選課表ON學(xué)生表.學(xué)號=選課表.學(xué)號

注意：若引用的列是多個表所共有的，需指明“表名.列名”提問：結(jié)果集中如何消除重復(fù)字段？如何消除重復(fù)記錄？6.3.2內(nèi)連接查詢選課表連接查詢例2：要求查詢已經(jīng)選修了D4課程的學(xué)生信息學(xué)生表SELECT學(xué)生表.*FROM學(xué)生表,選課表WHERE學(xué)生表.學(xué)號=選課表.學(xué)號and選課表.課程號='D4'條件可省略問題：查什么屬性？查什么表？連接條件？注意：若引用的列是多個表所共有的，需指明“表名.列名”提問：將題目改為“選修了軟件工程課程”?Select學(xué)生表.*From學(xué)生表INNERJOIN選課表On

學(xué)生表.學(xué)號=選課表.學(xué)號Where選課表.課程號=‘D4’6.3.2內(nèi)連接查詢連接查詢例3：要求查詢已經(jīng)選修了軟件工程課程的學(xué)生信息SELECT學(xué)生表.*FROM學(xué)生表,選課表,課程表WHERE學(xué)生表.學(xué)號=選課表.學(xué)號and選課表.課程號=課程表.課程號and課程表.課程名='軟件工程'可省略6.3.2內(nèi)連接查詢連接查詢例4：查詢學(xué)生的學(xué)號，姓名及所選修的課程號、成績例5：查詢學(xué)生的學(xué)號，姓名及所選修的課程名及成績select學(xué)生表.學(xué)號,姓名,課程名,成績from學(xué)生表,課程表,選課表where學(xué)生表.學(xué)號=選課表.學(xué)號and課程表.課程號=選課表.課程號select學(xué)生表.學(xué)號,姓名,選課表.課程號,成績from學(xué)生表,選課表where學(xué)生表.學(xué)號=選課表.學(xué)號可省略6.3.2內(nèi)連接查詢連接查詢例6：查詢考試成績有不及格的學(xué)生的學(xué)號、姓名例7：查詢選了三門課程的學(xué)生的學(xué)號、姓名select學(xué)生表.學(xué)號,姓名

from學(xué)生表,選課表where學(xué)生表.學(xué)號=選課表.學(xué)號and成績<60select學(xué)生表.學(xué)號,姓名from學(xué)生表,選課表where學(xué)生表.學(xué)號=選課表.學(xué)號groupby學(xué)生表.學(xué)號,姓名havingcount(課程號)=3distinct6.3.2內(nèi)連接查詢連接查詢課堂練習(xí)6學(xué)生表（學(xué)號，姓名，性別，班級，年齡，所在系）課程表（課程號，課程名，教師，周課時數(shù)，備注）選課表（學(xué)號，課程號，成績）查詢考試成績大于95分的學(xué)生的學(xué)號、姓名查詢計算機系學(xué)生的選課門數(shù)查詢平均分大于60的學(xué)生的學(xué)號、姓名查詢關(guān)系數(shù)據(jù)庫應(yīng)用考試缺考的學(xué)生學(xué)號、姓名問題：20分鐘連接查詢1：查詢考試成績大于95分的學(xué)生的學(xué)號、姓名2：查詢計算機系學(xué)生的選課門數(shù)select學(xué)生表.學(xué)號,姓名

from學(xué)生表,選課表Where學(xué)生表.學(xué)號=選課表.學(xué)號and成績>95課堂練習(xí)6答案select學(xué)生表.學(xué)號,count(課程號)as選課門數(shù)from學(xué)生表,選課表where學(xué)生表.學(xué)號=選課表.學(xué)號and所在系='計算機系'groupby學(xué)生表.學(xué)號連接查詢3：查詢平均分大于60的學(xué)生的學(xué)號、姓名select學(xué)生表.學(xué)號,姓名

from學(xué)生表,選課表where學(xué)生表.學(xué)號=選課表.學(xué)號groupby學(xué)生表.學(xué)號,姓名having

avg(成績)>60課堂練習(xí)6答案連接查詢4：查詢關(guān)系數(shù)據(jù)庫應(yīng)用考試缺考的學(xué)生學(xué)號、姓名select學(xué)生表.學(xué)號,姓名

from學(xué)生表,選課表,課程表where學(xué)生表.學(xué)號=選課表.學(xué)號and課程表.課程號=選課表.課程號and課程名='關(guān)系數(shù)據(jù)庫應(yīng)用'and成績isnull課堂練習(xí)6答案連接查詢?nèi)绻谝粋€連接查詢中，涉及到的兩個表都是同一個表，這種查詢稱為自連接查詢。為了區(qū)分同樣的兩張表，使用AS語句定義別名：FROM

表名AS

別名自連接把物理上同一張表在邏輯上區(qū)分為兩張表或多張表。注意：當(dāng)為表指定了別名以后，在查詢語句中的其它地方用到該表名時，都應(yīng)使用其別名，而不能使用其原表名。6.3.3自連接查詢連接查詢例6.18：要求檢索出學(xué)號為‘2021509’的學(xué)生的同班同學(xué)的信息。分析：第1遍查什么信息？第2遍查什么信息？SelectB.*

from

學(xué)生表

AS

A,學(xué)生表AS

B

where

A.學(xué)號=‘2021509'

AND

B.班級=A.班級SelectB.*from學(xué)生表ASA

JOIN學(xué)生表ASBONB.班級=A.班級WHEREA.學(xué)號=‘2021509'提問：如果要求查詢結(jié)果中不包括該同學(xué)本身，如何解決?6.3.3自連接查詢連接查詢例：要求檢索出學(xué)號為‘2021509’的學(xué)生同班的其他同學(xué)的信息。SelectB.*

from

學(xué)生表

AS

A,學(xué)生表AS

B

where

A.學(xué)號=‘2021509'

AND

B.班級=A.班級AndB.學(xué)號<>‘2021509’SelectB.*from學(xué)生表ASA

JOIN學(xué)生表ASBONB.班級=A.班級WHEREA.學(xué)號=‘005‘ANDB.學(xué)號<>‘2021509’6.3.3自連接查詢連接查詢課堂練習(xí)7學(xué)生表（學(xué)號，姓名，性別，班級，年齡，所在系）課程表（課程號，課程名，教師，周課時數(shù)，備注）選課表（學(xué)號，課程號，成績）要求檢索出學(xué)號為’2021626’的學(xué)生的性別和年齡均相同的同學(xué)的信息。要求檢索出學(xué)號為’2021626’的學(xué)生的性別和年齡均相同的其它同學(xué)的信息。要求檢索出學(xué)號為’2021626’的學(xué)生同年齡的學(xué)生人數(shù)。問題：20分鐘連接查詢課堂練習(xí)7答案1.要求檢索出學(xué)號為’2021626’的學(xué)生的性別和年齡均相同的同學(xué)的信息。SelectB.*from學(xué)生表ASA,學(xué)生表ASBwhereA.學(xué)號='2021626'and

B.性別=A.性別andB.年齡=A.年齡2.要求檢索出學(xué)號為’2021626’的學(xué)生的性別和年齡均相同的其它同學(xué)的信息。SelectB.*from學(xué)生表ASA,學(xué)生表ASBwhereA.學(xué)號='2021626'and

B.性別=A.性別andB.年齡=A.年齡andB.學(xué)號<>'2021626'連接查詢課堂練習(xí)7答案3.要求檢索出學(xué)號為’2021626’的學(xué)生同年齡的學(xué)生人數(shù)。Selectcount(B.學(xué)號)as學(xué)生人數(shù)from學(xué)生表ASA,學(xué)生表ASBwhereA.學(xué)號='2021626'andB.年齡=A.年齡連接查詢內(nèi)連接的查詢結(jié)果都是滿足連接條件的元組。但有時我們也希望輸出那些不滿足連接條件的元組的信息。外連接查詢的關(guān)鍵字是OUTERJOIN。外連接是只限制一張表中的數(shù)據(jù)必須滿足連接條件，而另一張表中的數(shù)據(jù)可以不滿足連接條件的連接方式。外連接的格式為：

FROM表1LEFT|RIGHT|FULLOUTERJOIN表2ON<連接條件>6.3.4外連接查詢連接查詢有三種方式的外連接：左外連接右外連接全外連接6.3.4外連接查詢連接查詢左外連接（LEFTOUTERJOIN）如果在連接查詢中，連接關(guān)鍵字左端的表（表1）中所有元組都列出來，并且能在右端的表（表2）中找到匹配的元組，那么連接成功。如果在右端的表中沒能找到匹配的元組，那么對應(yīng)的元組是空值（NULL）。這時，查詢語句使用關(guān)鍵字LEFTOUTERJOIN。也就是說，左外連接的含義是限制連接關(guān)鍵字右端的表（表2）中的數(shù)據(jù)必須滿足連接條件，而不管左端的表（表1）中的數(shù)據(jù)是否滿足連接條件，均輸出左端表（表1）中的內(nèi)容。6.3.4外連接查詢連接查詢左外連接（LEFTOUTERJOIN）6.3.4外連接查詢【例6.26】要查詢所有學(xué)生的選課情況，包括已經(jīng)選課的和還沒有選課的學(xué)生。SELECT學(xué)生表.學(xué)號,姓名,班級,課程號,成績FROM學(xué)生表LEFTOUTERJOIN選課表ON學(xué)生表.學(xué)號=選課表.學(xué)號從圖所示的查詢結(jié)果可以看出，其中有5個學(xué)生的課程號和成績?yōu)镹ULL，這說明他們沒有選課。但是，因為進行的是左外連接查詢，所以仍將他們顯示出來，并在相應(yīng)的列上放置NULL。連接查詢6.3.4外連接查詢右外連接（RIGHTOUTERJOIN）右外連接查詢的含義與左外連接的含義非常類似。只是右端表中的所有元組都被列出，使用的關(guān)鍵字是RIGHTOUTERJOIN。此時，限制左端表的數(shù)據(jù)必須滿足連接條件，而不管右端表中的數(shù)據(jù)是否滿足連接條件，均輸出右端表中的內(nèi)容。連接查詢6.3.4外連接查詢?nèi)膺B接（FULLOUTERJOIN）外連接查詢的特點是左、右兩端表中的元組都被輸出，如果沒能找到匹配的元組，就使用NULL來代替。這時使用的關(guān)鍵字是FULLOUTERJOIN。連接查詢6.3.4外連接查詢示例為了更好地說明外連接查詢，這里用另外兩張表（圖書和作者）來舉例，見表6.4。SELECT圖書.圖書號,書名,作者名,單價FROM圖書LEFTOUTERJOIN作者ON圖書.圖書號=作者.圖書號SELECT圖書.圖書號,書名,作者名,單價FROM圖書RIGHTOUTERJOIN作者ON圖書.圖書號=作者.圖書號SELECT圖書.圖書號,書名,作者名,單價FROM圖書FULLOUTERJOIN作者ON圖書.圖書號=作者.圖書號左外連接右外連接全外連接子查詢6.4子查詢6.4.1將子查詢用做派生的表6.4.2將子查詢用做表達式6.4.3相關(guān)子查詢6.4.4使用EXISTS和NOTEXISTS操作符子查詢子查詢是一系列SELECT語句的嵌套使用，嵌套的SELECT語句就稱為子查詢。使用子查詢時應(yīng)注意：子查詢要用括號（）括起來。子查詢一般出現(xiàn)在SELECT子句、WHERE子句、FROM子句中。只要需要一個值或一系列的值，就可以用子查詢代替一個表達式。子查詢不應(yīng)包含在集函數(shù)中。子查詢中不能查詢包含數(shù)據(jù)類型是text或image的字段。子查詢中不能使用compute或forbrowse語句。ORDERBY子句不能用于子查詢，但指定了TOP時則可以。子查詢中也可以再包含子查詢，但從兼容性考慮，建議嵌套不要超過32層。通常，子查詢是可以和連接查詢相互轉(zhuǎn)換的。子查詢可以用子查詢產(chǎn)生一個派生的表，用于代替FROM子句中的表。FROM子句中的子查詢將返回一個結(jié)果集，這個結(jié)果集所形成的表將被外層SELECT語句使用。派生表是FROM子句中子查詢的一個特殊用法，用一個別名或用戶自定義的名字來引用這個派生表。SELECTXS.*FROM(SELECT學(xué)號,姓名,年齡FROM學(xué)生表WHERE班級='GZ02計6')ASXS6.4.1將子查詢用做派生的表子查詢子查詢可以作為一個標(biāo)量值來使用，產(chǎn)生標(biāo)量值的含義就是把子查詢的結(jié)果作為一個常量來使用。所有使用表達式的地方，都可以用子查詢來代替，此時子查詢必須返回一個單個的值或某個字段的值。一個簡單的示例是：如果要求檢索出年齡高于所有學(xué)生平均年齡的學(xué)生信息，則可用如下語句完成。SELECT*FROM學(xué)生表WHERE年齡>（SELECTAVG（年齡）FROM學(xué)生表）6.4.2將子查詢用做表達式子查詢【例6.27】如果要求查詢GZ02計7班同學(xué)的平均年齡以及每個同學(xué)年齡與平均年齡的差。SELECT學(xué)號,姓名,年齡,(SELECTAVG(年齡)FROM學(xué)生表WHERE班級='GZ02計7')AS平均年齡，年齡-(SELECTAVG(年齡)FROM學(xué)生表WHERE班級='GZ02計7')AS年齡差FROM學(xué)生表WHERE班級='GZ02計7'在該查詢示例中，使用子查詢：(SELECTAVG（年齡）FROM學(xué)生表WHERE班級='GZ02計7')AS平均年齡該計算結(jié)果作為選擇列表中的一個輸出列，并作為下面算術(shù)表達式的一部分參與計算：年齡-（SELECTAVG（年齡）FROM學(xué)生表WHERE班級='GZ02計7'）AS年齡差6.4.2將子查詢用做表達式子查詢在SQLServer中，當(dāng)子查詢返回一個值時，關(guān)鍵字IN、ALL、ANY可以用于關(guān)系的比較以便生成邏輯值，放在查詢語句的WHERE子句中形成查詢條件。假設(shè)R為子查詢生成的一列值，S為一個標(biāo)量值，那么這些涉及關(guān)系的運算符如下表所示。6.4.2將子查詢用做表達式運算符描述SINR當(dāng)且僅當(dāng)S和R中的某一個值相等時，條件為真SNOTINR當(dāng)且僅當(dāng)S和R中的任意一個值都不相等時，條件為真S=ALLR當(dāng)且僅當(dāng)S和R中的每一個值都相等時，條件為真S>ALLRS>=ALLR當(dāng)且僅當(dāng)S比R中的每一個值都大時（大于或等于），條件為真S<ALLRS<=ALLR當(dāng)且僅當(dāng)S比R中的每一個值都小時（小于或等于），條件為真S<>ALLR當(dāng)且僅當(dāng)S和R中的任意一個值都不相等時，條件為真S=ANYR當(dāng)且僅當(dāng)S和R中的某一個值相等時，條件為真S>ANYRS>=ANYR當(dāng)且僅當(dāng)S比R中的某一個值大時（大于或等于），條件為真S<ANYRS<=ANYR當(dāng)且僅當(dāng)S比R中的某一個值小時（小于或等于），條件為真S<>ANYR當(dāng)且僅當(dāng)S和R中的任意一個值都不相等時，條件為真子查詢還可以將子查詢歸納為以下3種形式：1.帶關(guān)鍵字IN的子查詢2.帶比較運算符的子查詢3.帶關(guān)鍵字ANY或ALL的子查詢子查詢1.帶關(guān)鍵字IN的子查詢由于子查詢的結(jié)果是記錄的集合，故常使用謂詞IN來實現(xiàn)。謂詞IN用于一個定是否在子查詢的結(jié)果集中。當(dāng)父查詢表達式與子查詢的結(jié)果集中的某個值相等時，返回TRUE，否則返回FALSE。同時，也可以在IN關(guān)字之前使用NOT，表示表達式的值不在查詢結(jié)果集中。對于使用IN的子查詢的連接條件，其語法格式如下：WHERE<表達式>[NOT]IN<子查詢>

子查詢（1）同一張表的查詢例6.25：要求檢索出學(xué)號為‘2021509’的學(xué)生的同班同學(xué)的信息自連接：SELECTB.*FROM學(xué)生表ASA，學(xué)生表ASBWHEREA.學(xué)號='2021509'ANDA.班級=B.班級提問：用連接查詢怎么寫？屬于哪種連接？提問：用子查詢又該怎么寫？子查詢：SELECT*FROM學(xué)生表WHERE班級

IN（SELECT班級FROM學(xué)生表WHERE學(xué)號=‘2021509’）注意：對來自同一張表的查詢，共有的條件（如班級）作為子查詢的橋梁。1.帶關(guān)鍵字IN的子查詢子查詢例6.28：要求檢索出學(xué)號為‘2021509’的學(xué)生的同班的其他同學(xué)的信息自連接：SELECTB.*FROM學(xué)生表ASA，學(xué)生表ASBWHEREA.學(xué)號='2021509'ANDA.班級=B.班級ANDB.學(xué)號<>'2021509'提問：用連接查詢怎么寫？屬于哪種連接？提問：用子查詢又該怎么寫？子查詢：SELECT*FROM學(xué)生表WHERE班級

IN（SELECT班級FROM學(xué)生表WHERE學(xué)號=‘2021509’）AND學(xué)號<>‘2021509’總結(jié)：對來自同一張表的查詢，共有的條件作為子查詢的橋梁。（1）同一張表的查詢1.帶關(guān)鍵字IN的子查詢子查詢（2）不同表的查詢例6.28：要求檢索出GZ02計7班中所選課程中有過不及格的同學(xué)信息。內(nèi)連接：selectdistinct

學(xué)生表.*from學(xué)生表,選課表where學(xué)生表.學(xué)號=選課表.學(xué)號and班級='GZ02計7'and成績<60提問：用連接查詢怎么寫？屬于哪種連接？提問：用子查詢又該怎么寫？子查詢：SELECT*

FROM學(xué)生表WHERE學(xué)號

IN(SELECT學(xué)號FROM選課表WHERE成績<60)AND班級='GZ02計7'總結(jié)：對來自不同表的查詢，多個表之間共有的屬性作為子查詢的橋梁。1.帶關(guān)鍵字IN的子查詢子查詢例6.29：求沒有選修4課程的學(xué)生學(xué)號，姓名內(nèi)連接：selectdistinct學(xué)生表.學(xué)號,姓名from學(xué)生表,選課表where學(xué)生表.學(xué)號=選課表.學(xué)號and課程號<>'4'提問：用子查詢怎么寫？子查詢：SELECT學(xué)號,姓名FROM學(xué)生表WHERE學(xué)號NOTIN(SELECT學(xué)號FROM選課表WHERE課程號='4')解釋：只是課程號<>'4',而不能代表學(xué)生沒選4號課程思考：該題能用內(nèi)連接查詢寫嗎？不能分析？注意：子查詢和連接查詢大部分情況下可以互相轉(zhuǎn)換，但子查詢更加強大，可讀性更好。（2）不同表的查詢1.帶關(guān)鍵字IN的子查詢子查詢例6.30：求選修了“關(guān)系數(shù)據(jù)庫應(yīng)用”學(xué)生的學(xué)號，姓名。內(nèi)連接：select學(xué)生表.學(xué)號,姓名from學(xué)生表,選課表,課程表where學(xué)生表.學(xué)號=選課表.學(xué)號and選課表.課程號=課程表.課程號and課程名='關(guān)系數(shù)據(jù)庫應(yīng)用'思考：用子查詢怎么寫？子查詢：select學(xué)號,姓名from學(xué)生表where學(xué)號in(select學(xué)號from選課表where課程號in(select課程號from課程表where課程名='關(guān)系數(shù)據(jù)庫應(yīng)用'))注意：多表查詢，請注意嵌套順序，兩表有關(guān)聯(lián)的先寫。（2）不同表的查詢1.帶關(guān)鍵字IN的子查詢子查詢帶IN謂詞的子查詢課堂練習(xí)：學(xué)生表（學(xué)號，姓名，性別，班級，年齡）課程表（課程號，課程名，教師，周課時數(shù)，備注）選課表（學(xué)號，課程號，成績）1.檢索出與“關(guān)系數(shù)據(jù)庫應(yīng)用"課程周課時數(shù)相同的課程信息。2.檢索出與“關(guān)系數(shù)據(jù)庫應(yīng)用"課程周課時數(shù)相同的其它課程信息。3.請查詢4號課程成績大于80分的學(xué)生信息。4.檢索出“關(guān)系數(shù)據(jù)庫應(yīng)用”成績大于80分的學(xué)生信息。5.求沒有選修“關(guān)系數(shù)據(jù)庫應(yīng)用”的學(xué)生學(xué)號，姓名。問題：1.帶關(guān)鍵字IN的子查詢子查詢當(dāng)能確切知道內(nèi)層查詢返回單值時，可用比較運算符（>，<，=，>=，<=，!=或<>）。與ANY或ALL謂詞配合使用。當(dāng)子查詢返回0行或比較運算符對于子查詢返回的每一行都是True時，則返回True；若比較運算符對于子查詢返回的至少一行是False，則返回False。2.帶比較運算符的子查詢select學(xué)號,成績from選課表where課程號='1'and成績

>(select成績from選課表where課程號='1'and學(xué)號

=(select學(xué)號from學(xué)生表where姓名='包海明'))子查詢例6.31：要求查詢選修1號課程且成績高于“包海明”的學(xué)生學(xué)號，成績。注意：子查詢一定要跟在比較運算符之后如果上述例子中的學(xué)生姓名換成“徐陽”，查詢語句中是否還可以使用比較運算符“=”，如果不行，請說明理由，并做相應(yīng)修改？想一想2.帶比較運算符的子查詢子查詢ANY：任意一個值

ALL：所有值注意：需要配合使用比較運算符3.帶ANY或ALL的子查詢子查詢>ANY大于子查詢結(jié)果中的某個值>ALL大于子查詢結(jié)果中的所有值<ANY小于子查詢結(jié)果中的某個值<ALL小于子查詢結(jié)果中的所有值>=ANY大于等于子查詢結(jié)果中的某個值>=ALL大于等于子查詢結(jié)果中的所有值<=ANY小于等于子查詢結(jié)果中的某個值<=ALL小于等于子查詢結(jié)果中的所有值=ANY等于子查詢結(jié)果中的某個值=ALL等于子查詢結(jié)果中的所有值（通常沒有實際意義）!=（或<>）ANY不等于子查詢結(jié)果中的某個值!=（或<>）ALL不等于子查詢結(jié)果中的任何一個值3.帶ANY或ALL的子查詢子查詢例6.32：要求檢索出比所有女同學(xué)年齡都大的男同學(xué)信息。SELECT*FROM學(xué)生表WHERE年齡>ALL(SELECT年齡FROM學(xué)生表WHERE性別='F')AND性別='M'思考：該題目用any或all？查詢的表？還可以理解為：要求檢索出大于最大年齡女同學(xué)的男同學(xué)信息。SELECT*FROM學(xué)生表WHERE年齡>(SELECTMAX(年齡)FROM學(xué)生表WHERE性別='F')AND性別='M'3.帶ANY或ALL的子查詢子查詢例6.33：要求檢索出選課門數(shù)最多的學(xué)生學(xué)號和姓名。select學(xué)號,姓名from學(xué)生表where學(xué)號in(select學(xué)號from選課表groupby學(xué)號havingcount(學(xué)號)>=all(selectcount(學(xué)號)from選課表groupby學(xué)號))提問：該題目該如何寫？請同學(xué)們寫下？思考：該題目用any或all？查詢的表？注意：有集函數(shù)的與集函數(shù)的寫法一致3.帶ANY或ALL的子查詢子查詢例6.34：要求檢索出有學(xué)生的4號課程成績在95分以上的那些班級。SELECTDISTINCT班級FROM學(xué)生表WHERE學(xué)號=ANY(SELECT學(xué)號FROM選課表WHERE成績>95AND課程號=4)思考：如何解讀該題目？3.帶ANY或ALL的子查詢4.子查詢例6.35：要求檢索出其他班級中比‘GZ02計7’班某一學(xué)生年齡小的學(xué)生的信息。SELECT*FROM學(xué)生表WHERE班級<>'GZ02計7'AND年齡<ANY(SELECT年齡FROM學(xué)生表WHERE班級='GZ02計7')還可以理解為：求年齡小于‘GZ02計7’年齡最大者的其它班級的學(xué)生select*from學(xué)生表where班級<>'GZ02計7'and年齡<

(selectmax(年齡)from學(xué)生表where班級='GZ02計7')思考：如何解讀該題目？即查的是其他班級年齡小于‘GZ02計7’任一個學(xué)生的年齡思考：還可以如何理解？3.帶ANY或ALL的子查詢子查詢學(xué)生表（學(xué)號，姓名，性別，班級，年齡）課程表（課程號，課程名，教師，周課時數(shù)，備注）選課表（學(xué)號，課程號，成績）查詢平均成績最好的學(xué)生的學(xué)號。查詢2號課程成績大于全校2號課程平均成績的學(xué)生信息。列出課程號為1的最高分獲得者的信息。檢索出GZ02計6班年齡大于等于全校學(xué)生平均年齡的學(xué)生信息。問題：3.帶ANY或ALL的子查詢課堂練習(xí)：子查詢相關(guān)子查詢可被用做動態(tài)表達式，這個表達式的值相對于外層查詢的每一行而變化。查詢處理器為外層查詢的每一個記錄計算子查詢的值，一次一行，而這個子查詢每次都會被作為一個表達式而被計算并返回給外層查詢。相關(guān)子查詢是動態(tài)執(zhí)行的子查詢和外層查詢間的一個非常有效的聯(lián)合。使用相關(guān)子查詢時，內(nèi)層子查詢被反復(fù)執(zhí)行，外層查詢有多少記錄，內(nèi)層查詢就被執(zhí)行多少次。6.4.3相關(guān)子查詢子查詢【例6.36】要求查詢已選修課程號為1且成績在90分以上的同學(xué)的學(xué)號及姓名。6.4.3相關(guān)子查詢SELECT學(xué)號,姓名FROM學(xué)生表WHERE90<=(SELECT成績FROM選課表WHERE學(xué)生表.學(xué)號=選課表.學(xué)號AND課程號=1)子查詢【例6.37】如果要求查詢同時選修了4門課程的同學(xué)的學(xué)號。6.4.3相關(guān)子查詢SELECTDISTINCT學(xué)號FROM選課表ASS1WHERE4=(SELECTCOUNT(課程號)FROM選課表ASS2WHERES1.學(xué)號=S2.學(xué)號)子查詢【例6.38】檢索出每門課程的及格人數(shù)與不及格人數(shù)，要求按照：課程名、及格人數(shù)、不及格人數(shù)排列。6.4.3相關(guān)子查詢SELECT課程名，(SELECTCOUNT(學(xué)號)FROM選課表WHERE課程號=課程表.課程號AND成績>=60)AS及格人數(shù)，(SELECTCOUNT(學(xué)號)FROM選課表WHERE課程號=課程表.課程號AND成績<60)AS不及格人數(shù)FROM課程表子查詢在相關(guān)子查詢中可以使用EXISTS和NOTEXISTS操作符來判斷某個值是否存在于一系列的值中。SQLServer處理帶有EXISTS和NOTEXISTS操作符的子查詢時：外層查詢測試子查詢返回的記錄是否存在。基于查詢所指定的條件，子查詢返回TRUE或FALSE。子查詢不產(chǎn)生任何數(shù)據(jù)。6.4.4使用EXISTS和NOTEXISTS操作符子查詢【例6.39】要求查詢出同時選修了1號課程和2號課程的同學(xué)的信息。6.4.4使用EXISTS和NOTEXISTS操作符SELECT學(xué)號，姓名，班級FROM學(xué)生表WHEREEXISTS(SELECT*FROM選課表WHERE學(xué)號=學(xué)生表.學(xué)號AND課程號=1)ANDEXISTS(SELECT*F