第6章數(shù)據(jù)查詢(xún)

創(chuàng)建表：CREATETABLE[database_name.[owner].|owner.]table_name<column_definition>::={column_namedata_type[NULL|notnull]}[on<filegroup>][textimage_onON<filegroup>]

[IDENTITY[(seed,increment)][...n]

修改表

ALTERTABLEtable_name

{[ALTERCOLUMNcolumn_name{new_data_type

}]|ADDcolumn_namedata_type|DROPCOLUMNcolumn_name[,...n]}刪除表

DROPTABLEtable_name

插入數(shù)據(jù)INSERT[INTO]{table_name|view_name}[column_list）]{VALUES(values_list)}修改數(shù)據(jù)

UPDATE

{table_name|view_name}SET

{column_list|variable_list}=expression[From<table_source>[,…n][WHEREsearch_conditions]

刪除數(shù)據(jù)DELETEFROM

table_name

WHERE

search_conditions功能上機(jī)實(shí)驗(yàn)注意事項(xiàng)把數(shù)據(jù)庫(kù)內(nèi)容保存到其他存儲(chǔ)介質(zhì)時(shí)，先在SQLServer中分離數(shù)據(jù)庫(kù)；下次再使用時(shí)，先在SQLServer中附加該數(shù)據(jù)庫(kù)；在表中輸入數(shù)據(jù)時(shí)，一行一行輸入。實(shí)驗(yàn)二實(shí)驗(yàn)三第六章數(shù)據(jù)查詢(xún)Chapter6Query第6章

數(shù)據(jù)查詢(xún)6.1基本查詢(xún)6.1.1選擇表中的若干列6.1.2帶條件查詢(xún)6.1.3查詢(xún)結(jié)果處理6.1.4分組統(tǒng)計(jì)與篩選6.1.5使用COMPUTE子句匯總6.2嵌套查詢(xún)

6.2.1單值嵌套查詢(xún)

6.2.2多值嵌套查詢(xún)6.3連接查詢(xún)6.3.1內(nèi)連接查詢(xún)6.3.2外連接查詢(xún)6.3.3交叉連接查詢(xún)6.4小結(jié)查詢(xún)語(yǔ)法SELECT[ALL|DISTINCT]<目標(biāo)列表達(dá)式>[，<目標(biāo)列表達(dá)式>]…FROM<表名或視圖名>[，<表名或視圖名>]…[WHERE<條件表達(dá)式>][GROUPBY<列名1>[HAVING<條件表達(dá)式>]][ORDERBY<列名2>[ASC|DESC]]；－SELECT子句：指定要顯示的屬性列－

FROM子句：指定查詢(xún)對(duì)象(基本表或視圖)－

WHERE子句：指定查詢(xún)條件－GROUPBY子句：對(duì)查詢(xún)結(jié)果按指定列的值分組，該屬性列值相等的元組為一個(gè)組。通常會(huì)在每組中使用集合函數(shù)。－ HAVING短語(yǔ)：篩選出只有滿(mǎn)足指定條件的組－

ORDERBY子句：對(duì)查詢(xún)結(jié)果按指定列值的升序或降序排序6.1基本查詢(xún)格式語(yǔ)法格式：SELECT[ALL|DISTINCT][TOPn[PERCENT]]select_listFROMtable_nameselect_list：所要查詢(xún)的選項(xiàng)的集合，多個(gè)選項(xiàng)之間用逗號(hào)分開(kāi)。算術(shù)表達(dá)式、字符串常量、函數(shù)、列別名等。table_name：要查詢(xún)的表。ALL：表示輸出所有記錄，包括重復(fù)記錄。DISTINCT：表示輸出記錄中不包括重復(fù)記錄TOPn[PERCENT]:指定返回查詢(xún)結(jié)果的前n行數(shù)據(jù)（前n%行數(shù)據(jù)）查詢(xún)選項(xiàng)可以使用聚集函數(shù)選擇若干列舉例(參考課本112頁(yè)表字段)例6-1-1查詢(xún)員工姓名及住址SELECTemployee_name,addressFROMemployee例6-1查詢(xún)員工所有信息SELECT*FROMemployee例6-1-2查詢(xún)員工姓名及年齡SELECTemployee_name,YEAR(GETDATE())-YEAR(birth_date)FROMemployee例6-1-3查詢(xún)員工姓名及年齡SELECTemployee_name,‘年齡’,YEAR(GETDATE())-YEAR(birth_date)FROMemployee基本查詢(xún)舉例(續(xù))例6-1-4查詢(xún)員工姓名及年齡,并為每列起適當(dāng)?shù)臉?biāo)題SELECTemployee_nameAS姓名,YEAR(GETDATE())-YEAR(birth_date)AS年齡FROMemployee例6-1-5查詢(xún)前兩個(gè)員工姓名及住址SELECTTOP2employee_name,addressFROMemployee例6-1-6查詢(xún)前20%員工信息SELECTTOP20PERCENT*FROMemployee基本查詢(xún)舉例(續(xù)2)例6-1-7查詢(xún)員工的姓氏SELECTALLLEFT(employee_name,1),FROMemployee例6-1-8查詢(xún)員工的姓氏(重復(fù)的只列一次)SELECTDISTINCTLEFT(employee_name,1)FROMemployee使用集函數(shù)(AggregateFunctions)7類(lèi)主要集函數(shù)：(1)計(jì)數(shù)COUNT（[DISTINCT|ALL]*）COUNT（[DISTINCT|ALL]<列名>）(2)計(jì)算總和SUM（[DISTINCT|ALL]<列名>）(3)計(jì)算平均值A(chǔ)VG（[DISTINCT|ALL]<列名>）(4)求最大值MAX（[DISTINCT|ALL]<列名>）(5)求最小值MIN（[DISTINCT|ALL]<列名>）(6)求標(biāo)準(zhǔn)差STDEV（[DISTINCT|ALL]<列名>）(7)求方差VAR（[DISTINCT|ALL]<列名>）DISTINCT：在計(jì)算時(shí)要取消指定列中的重復(fù)值A(chǔ)LL：不取消重復(fù)值A(chǔ)LL為缺省值使用集函數(shù)（續(xù)１）SnoCnoGrade20050011922005001285200500138820050022902005002380SC注：用DISTINCT以避免重復(fù)計(jì)算學(xué)生人數(shù)查詢(xún)結(jié)果：

count_Sno5查詢(xún)結(jié)果：

count_Sno２[例]查詢(xún)學(xué)生總?cè)藬?shù)。

SELECTCOUNT(*) FROMStudent；[例]查詢(xún)選修了課程的學(xué)生人數(shù)。SELECTCOUNT(Sno)FROMSC；

SELECTCOUNT(DistinctSno)FROMSC；

基本查詢(xún)舉例(續(xù))例6-3對(duì)employee表，分別查詢(xún)公司的員工總數(shù)和公司員工的平均收入。

SELECTCOUNT(*)AS總數(shù)

FROMemployee

SELECTAVG(wages)AS平均收入FROMemployee等價(jià)于SELECTCOUNT(*)AS總數(shù),AVG(wages)AS平均收入FROMemployee查詢(xún)語(yǔ)法SELECT[ALL|DISTINCT]<目標(biāo)列表達(dá)式>[，<目標(biāo)列表達(dá)式>]…FROM<表名或視圖名>[，<表名或視圖名>]…[WHERE<條件表達(dá)式>][GROUPBY<列名1>[HAVING<條件表達(dá)式>]][ORDERBY<列名2>[ASC|DESC]]；－SELECT子句：指定要顯示的屬性列－

FROM子句：指定查詢(xún)對(duì)象(基本表或視圖)－

WHERE子句：指定查詢(xún)條件－GROUPBY子句：對(duì)查詢(xún)結(jié)果按指定列的值分組，該屬性列值相等的元組為一個(gè)組。通常會(huì)在每組中使用集合函數(shù)。－ HAVING短語(yǔ)：篩選出只有滿(mǎn)足指定條件的組－

ORDERBY子句：對(duì)查詢(xún)結(jié)果按指定列值的升序或降序排序帶條件查詢(xún)語(yǔ)法格式：WHEREsearch_conditionAND，OR多重條件ISNULL，ISNOTNULL空值LIKE，NOTLIKE字符匹配IN，NOTIN確定集合BETWEENAND，NOTBETWEENAND確定范圍=，<，>，<=，>=，>，!=，!>，!<；NOT+上述比較符比較謂詞查詢(xún)條件帶條件查詢(xún)（續(xù)1）例6-4

對(duì)employee表，列出月工資在2000以上的員工記錄。SELECT*FROMemployeeWHEREwages>2000例6-5

對(duì)employee表，求出男員工的平均工資。

SELECTAVG(wages)as平均工資

FROMemployeeWHEREsex='男‘帶條件查詢(xún)（續(xù)2）使用謂詞IN<值表>,NOTIN<值表><值表>：用逗號(hào)分隔的一組取值例6-6-1對(duì)employee表，列出D001和D002部門(mén)的員工名單。SELECTdepartment_id,employee_nameFROMemployeeWHEREdepartment_idIN(‘D001',‘D002')

等價(jià)于

WHERE(department_id=‘D001')OR(department_id=‘D002')帶條件查詢(xún)（續(xù)3）確定范圍：BETWEEN…AND…NOTBETWEEN…AND…例6-7對(duì)employee表，列出月工資在2000到3000之間的員工名單。

SELECT*FROMemployeeWHEREwagesBETWEEN2000AND3000

等價(jià)于

WHEREwages>=2000ANDwages<=3000帶條件查詢(xún)（續(xù)4）[NOT]LIKE‘<匹配串>’<匹配串>：指定匹配模板。匹配模板：固定字符串或含通配符的字符串。

當(dāng)匹配模板為固定字符串時(shí)，可以用=運(yùn)算符取代LIKE謂詞，用!=或<>運(yùn)算符取代NOTLIKE謂詞。%(百分號(hào))：代表任意長(zhǎng)度（長(zhǎng)度可以為0）的字符串

_(下橫線(xiàn))：代表任意單個(gè)字符例6-8對(duì)employee表，列出所有的姓“張”的員工名單。

SELECT*FROMemployeeWHEREemployee_nameLIKE'張%'

語(yǔ)句中的WHERE子句還有等價(jià)的形式：

WHERELEFT(employee_name,1)=‘張‘帶條件查詢(xún)（續(xù)4）例6-8-1對(duì)employee表，列出所有的第二個(gè)字為’文’的員工名單。SELECT*FROMemployeeWHEREemployee_nameLIKE‘_文%'涉及空值的查詢(xún)使用謂詞ISNULL

或ISNOTNULL“ISNULL”不能用“=NULL”代替例6-9對(duì)employee表，列出所有工資為空值的員工編號(hào)和姓名。SELECTemployee_id,employee_nameFROMemployeeWHEREwagesISNULL查詢(xún)結(jié)果處理1.排序輸出(ORDERBY)語(yǔ)法格式：ORDERBYorder_by_expression1[ASC|DESC][,order_by_expression2[ASC|DESC]][,…]]例6-10-1對(duì)employee表，按性別順序列出員工的編號(hào)、姓名、性別、部門(mén)編號(hào)及工資。SELECTemployee_id,employee_name,sex,department_id,wagesFROMemployee

ORDERBYsex查詢(xún)結(jié)果處理（續(xù)1）例6-10對(duì)employee表，按性別順序列出員工的編號(hào)、姓名、性別、部門(mén)編號(hào)及工資，性別相同的再先按部門(mén)后按工資由高到低排序。SELECTemployee_id,employee_name,sex,department_id,wagesFROMemployee

ORDERBYsex,department_id,wagesDESC查詢(xún)結(jié)果處理（續(xù)2）2.重定向輸出(INTO)語(yǔ)法格式：INTOnew_table

例6-11-1

對(duì)員工表employee,查詢(xún)出“D001”部門(mén)的所有員工的信息，并將結(jié)果存入testtable表中。SELECT*INTOtesttableFROMemployeeWHEREdepartment_id=‘D001'查詢(xún)結(jié)果處理（續(xù)3）3.輸出合并(UNION)語(yǔ)法格式：

[UNION[ALL]<SELECT語(yǔ)句>]合并的規(guī)則是：

(1)不能合并子查詢(xún)的結(jié)果。

(2)兩個(gè)SELECT語(yǔ)句必須輸出同樣的列數(shù)。

(3)兩個(gè)表各相應(yīng)列的數(shù)據(jù)類(lèi)型必須相同，數(shù)字和字符不能合并。

(4)僅最后一個(gè)SELECT語(yǔ)句中可以用ORDERBY子句，且排序選項(xiàng)必須依據(jù)第一個(gè)SELECT列表中的列。查詢(xún)結(jié)果處理（續(xù)4）

例6-12對(duì)employee表，列出部門(mén)編號(hào)為“D001”或“D002”的所有員工姓名。

SELECTemployee_name,department_idFROMemployeeWHEREdepartment_id='D001'UNIONSELECTemployee_name,department_idFROMemployeeWHEREdepartment_id='D002'分組統(tǒng)計(jì)4.分組統(tǒng)計(jì)(GROUPBY)與篩選(HAVING)GROUPBYgroup_by_expression1[,group_by_expression2][,…]作用：細(xì)化集函數(shù)的作用對(duì)象

未對(duì)查詢(xún)結(jié)果分組，集函數(shù)將作用于整個(gè)查詢(xún)結(jié)果對(duì)查詢(xún)結(jié)果分組后，集函數(shù)將分別作用于每個(gè)組分組方法：按指定的一列或多列值分組，值相等的為一組使用GROUPBY子句后，SELECT子句的列名列表中只能出現(xiàn)分組屬性和集函數(shù)分組統(tǒng)計(jì)（續(xù)1）例6-13對(duì)employee表，分別統(tǒng)計(jì)男女員工人數(shù)。

SELECTsex,COUNT(sex)as人數(shù)

FROMemployeeGROUPBYsex例6-14對(duì)employee表，分別統(tǒng)計(jì)各部門(mén)男女員工的人數(shù)。

SELECTdepartment_id,sex,COUNT(*)as人數(shù)

FROMemployeeGROUPBYdepartment_id,sexHaving子句只有滿(mǎn)足HAVING短語(yǔ)指定條件的組才輸出HAVING短語(yǔ)與WHERE子句的區(qū)別：作用對(duì)象不同WHERE子句作用于基表或視圖，從中選擇滿(mǎn)足條件的元組。HAVING短語(yǔ)作用于組，從中選擇滿(mǎn)足條件的組。例6-15對(duì)employee表，列出部門(mén)平均工資大于2000的部門(mén)編號(hào)。SELECTdepartment_id,AVG(wages)AS平均工資FROMemployeeGROUPBYdepartment_idHAVINGAVG(wages)>=2000GROUPBY使用注意事項(xiàng)使用了分組的查詢(xún)語(yǔ)句，其SELECT子句中只能出現(xiàn)分組屬性和集函數(shù)，而不能有在GROUPBY沒(méi)有出現(xiàn)的屬性。 原因在于SELECT子句是對(duì)分組后的結(jié)果集進(jìn)行輸出，參與分組的屬性在同一組中取相同值，而不參與分組的屬性(不在GROUPBY子句中出現(xiàn)的屬性)則可能取不同的值，分組輸出通常是對(duì)組的整體描述，而不是組內(nèi)的詳細(xì)記錄，不參與分組的屬性取值不同，故無(wú)法輸出。SELECTsex,department_id,COUNT(sex)FROMemployeeGROUPBYsex匯總查詢(xún)5.使用COMPUTE和COMPUTEBY子句匯總語(yǔ)法格式（先排序后匯總）：COMPUTErow_aggregate(column_name)[,row_aggregate(column_name)...][BYcolumn_name[,column_name...]]使用COMPUTE子句在查詢(xún)的結(jié)果集中生成匯總行的同時(shí)，還可以生成明細(xì)行?？梢杂?jì)算子組的匯總值，也可以計(jì)算整個(gè)結(jié)果集的匯總值。使用COMPUTEBY子句可以對(duì)結(jié)果集數(shù)據(jù)進(jìn)行分組統(tǒng)計(jì)，即計(jì)算分組的匯總值。COMPUTE子句生成合計(jì)作為附加的匯總列出現(xiàn)在結(jié)果集的最后。匯總查詢(xún)（續(xù)1）例6-16對(duì)employee表中部門(mén)編號(hào)為“D001”的員工工資，按照其部門(mén)編號(hào)生成匯總行和明細(xì)行。

SELECTdepartment_id,wagesFROMemployeeWHEREdepartment_id='D001‘ORDERBYdepartment_idCOMPUTEsum(wages)例6-17對(duì)employee表中部門(mén)編號(hào)為“D001”或“D002”的員工工資，按照其部門(mén)編號(hào)生成分組匯總行和明細(xì)行。SELECTdepartment_id,wagesFROMemployeeWHEREdepartment_id='D001'ORdepartment_id='D002'ORDERBYdepartment_idCOMPUTEsum(wages)BYdepartment_id匯總查詢(xún)注意問(wèn)題DISTINCT關(guān)鍵字不能與集合函數(shù)一起使用。COMPUTE子句中指定的列必須是SELECT子句中已有的。因?yàn)镃OMPUTE子句產(chǎn)生非標(biāo)準(zhǔn)行，所以COMPUTE子句不能與SELECTINTO子句一起使用。COMPUTEBY必須與ORDERBY子句一起使用，且COMPUTEBY中指定的列必須與ORDERBY子句中指定的列相同，或者為其子集，而且兩者之間從左到右的順序也必須相同。在COMPUTE子句中，不能使用ntext、text或image數(shù)據(jù)類(lèi)型。SnoSnameSsexSageSdept2005001李勇男20CS2005002劉晨女19IS2005003王敏女18MA2005004張立男19ISSnoCnoGrade200500119220050012852005001388200500229020050052682005002380Student表SC表CnoCnameCpnoCcredit1數(shù)據(jù)庫(kù)542數(shù)學(xué)23信息系統(tǒng)144操作系統(tǒng)635數(shù)據(jù)結(jié)構(gòu)746數(shù)據(jù)處理27PASCAL語(yǔ)言64COURSE表補(bǔ)充案例（1）（實(shí)驗(yàn)數(shù)據(jù)）[例1]查詢(xún)?nèi)w學(xué)生的姓名及其所在的系SELECT

結(jié)果：

snameSdept

----------------2005001CS2005002

IS

2005003MA2005004ISSname，SdeptFROMStudentIS18男張立2005004MA18女王敏2005003IS19女劉晨2005002CS17男李勇2005001sdeptagesexsnamesnoStudent補(bǔ)充案例（2）[例2]

查詢(xún)?nèi)w學(xué)生的詳細(xì)記錄。SELECTSno，Sname，Ssex，Sage，SdeptFROMStudent；

或SELECT* //表示將表中的列全部按序輸出FROMStudent；[例3]查全體學(xué)生的姓名及其出生年份。SELECTSname,2012－SageFROMStudent；

輸出結(jié)果：

Sname2011-Sage----------------------

李勇1985劉晨1986王敏1987張立1986

補(bǔ)充案例（3）[例4]查詢(xún)?nèi)w學(xué)生的姓名、出生年份和所在系，要求用小寫(xiě)字母表示所在系名。

SELECTSname，'YearofBirth:'，2006-Sage，ISLOWER(Sdept)FROMStudent；輸出結(jié)果：

Sname'YearofBirth:'2006-SageISLOWER(Sdept)-------------------------------------------------

李勇YearofBirth:1976 cs

劉晨YearofBirth:1977 is

王敏YearofBirth:1978 ma

張立YearofBirth:1977 is補(bǔ)充案例（4）[例5]查詢(xún)?nèi)w學(xué)生的姓名、出生年份和所在系，并將輸出字段依次更名為：NAME、BIRTH、BIRTHDAY、DEPARTMENT。SELECTSnameNAME，'YearofBirth:’BIRTH，2007-SageBIRTHDAY，ISLOWER(Sdept)DEPARTMENTFROMStudent；輸出結(jié)果：

NAMEBIRTHBIRTHDAYDEPARTMENT----------------------------------------------------------

李勇YearofBirth:1986 cs

劉晨YearofBirth:1987 is

王敏YearofBirth:1988 ma

張立YearofBirth:1987 is補(bǔ)充案例（5）[例6]查詢(xún)選修了課程的學(xué)生學(xué)號(hào)。(1)SELECTSnoFROMSC;或(默認(rèn)ALL)SELECTALLSnoFROMSC;結(jié)果：Sno-------20050012005001200500120050022005002

(2)SELECTDISTINCTSnoFROMSC;結(jié)果：

Sno-------20050012005002

補(bǔ)充案例（6）[例7]查詢(xún)計(jì)算機(jī)系全體學(xué)生的信息。 SELECT*FROMStudent WHERESdept=‘CS’;

[例8]查詢(xún)所有年齡在20歲以下的學(xué)生姓名及其年齡。

SELECTSname，Sage FROMStudentWHERESage<20；或

SELECTSname,Sage FROMStudent WHERENOTSage>=20；補(bǔ)充案例（7）[例9]

查詢(xún)年齡不在20～23歲之間的學(xué)生姓名、系別和年齡。SELECTSname,Sdept,SageFROMStudentWHERESageNOTBETWEEN20AND23；[例10]

查詢(xún)不是信息系（IS）、數(shù)學(xué)系（MA）和計(jì)算機(jī)科學(xué)系（CS）學(xué)生的姓名和性別。

SELECTSname,SsexFROMStudentWHERESdeptNOTIN('IS','MA','CS')；補(bǔ)充案例（8）[例11]

查詢(xún)姓"歐陽(yáng)"且全名為三個(gè)漢字的學(xué)生的姓名。

SELECTSnameFROMStudentWHERESnameLIKE‘歐陽(yáng)__’；[例12]

查詢(xún)所有不姓劉的學(xué)生姓名。

SELECTSnameFROMStudentWHERESnameNOTLIKE‘劉%’；補(bǔ)充案例（9）

[例13]

某些學(xué)生選修課程后沒(méi)有參加考試，所以有選課記錄，但沒(méi)有考試成績(jī)。查詢(xún)?nèi)鄙俪煽?jī)的學(xué)生的學(xué)號(hào)和相應(yīng)的課程號(hào)。 SELECTSno,Cno FROMSC WHEREGradeISNULL；[例14]

查詢(xún)計(jì)算機(jī)系年齡在20歲以下的學(xué)生姓名。 SELECTSname FROMStudent WHERESdept='CS'ANDSage<20；補(bǔ)充案例（10）[例15]

查詢(xún)?nèi)w學(xué)生情況，查詢(xún)結(jié)果按所在系的系號(hào)升序排列，同一系中的學(xué)生按年齡降序排列。

查詢(xún)結(jié)果：

SnoSnameSsexSageSdept--------------------------------2005001李勇男17CS2005002劉晨女19IS2005004張立男18IS2005003王敏女18MA

查詢(xún)結(jié)果：

SnoSnameSsexSageSdept--------------------------------2005001李勇男17CS2005003王敏女18MA2005004張立男18IS2005002劉晨女19IS

SELECT*FROMStudent

ORDERBYSdept,SageDESCSELECT*FROMStudent

ORDERBYSage,SdeptDESC基本查詢(xún)回顧SELECT[ALL|DISTINCT][＊|<columns>|<arithmeticexpressions>|<AggregateFunctions>][[AS]<alias>],…FROM<table>WHERE<conditions>GROUPBY<columnN>

HAVING<inner-groupconfitions>ORDERBY<columnN>[ASC|DESC]特別注意：集函數(shù)只能用于SELECT子句和HAVING短語(yǔ)之中，而絕對(duì)不能出現(xiàn)在WHERE子句中。WHERE子句用于對(duì)查詢(xún)的表限定，而HAVING短語(yǔ)用于對(duì)結(jié)果集中的各分組進(jìn)行限定。SnoSnameSsexSageSdept2005001李勇男20CS2005002劉晨女19IS2005003王敏女18MA2005004張立男19ISSnoCnoGrade200500119220050012852005001388200500229020050052682005002380Student表SC表CnoCnameCpnoCcredit1數(shù)據(jù)庫(kù)542數(shù)學(xué)23信息系統(tǒng)144操作系統(tǒng)635數(shù)據(jù)結(jié)構(gòu)746數(shù)據(jù)處理27PASCAL語(yǔ)言64COURSE表補(bǔ)充案例（11）[例16]

求各個(gè)課程號(hào)及相應(yīng)的選課人數(shù)。

SELECTCno,COUNT(Sno) FROMSC GROUPBYCno;查詢(xún)結(jié)果：

CnoCOUNT(Sno)112232[例17]

查詢(xún)選修了3門(mén)以上課程的學(xué)生學(xué)號(hào)。

SELECTSno FROMSCGROUPBYSnoHAVINGCOUNT(*)>3；補(bǔ)充案例（12）[例18]

查詢(xún)有3門(mén)以上課程是90分以上的學(xué)生的學(xué)號(hào)及(90分以上的)課程數(shù)。 SELECTSno,COUNT(*)FROMSCWHEREGrade>90GROUPBYSno HAVINGCOUNT(*)>=3;嵌套查詢(xún)一個(gè)SELECT-FROM-WHERE語(yǔ)句稱(chēng)為一個(gè)查詢(xún)塊將一個(gè)查詢(xún)塊嵌套在另一個(gè)查詢(xún)塊的WHERE子句或HAVING短語(yǔ)的條件中的查詢(xún)稱(chēng)為嵌套查詢(xún)SELECTemployee_name//

外層查詢(xún)/父查詢(xún)

FROMemployeeWHEREdepartment_idIN(SELECTdepartment_id//

內(nèi)層查詢(xún)/子查詢(xún)

FROMdepartmentWHEREdepartment_name=‘市場(chǎng)部'）；子查詢(xún)的限制－－不能使用ORDERBY子句層層嵌套方式反映了SQL語(yǔ)言的結(jié)構(gòu)化有些嵌套查詢(xún)可以用連接運(yùn)算替代6.2.1單值嵌套查詢(xún)子查詢(xún)的返回結(jié)果是一個(gè)值的嵌套查詢(xún)稱(chēng)為單值嵌套查詢(xún)。比較運(yùn)算符：（>，<，=，>=，<=，!=或<>）。

例6-18對(duì)Sales數(shù)據(jù)庫(kù)，列出“市場(chǎng)部”的所有員工的編號(hào)。①確定“市場(chǎng)部”的部門(mén)編號(hào)

(SELECTdepartment_idFROMdepartmentWHEREdepartment_name='市場(chǎng)部')

結(jié)果為department_idD002②查找所有在D002部門(mén)的員工編號(hào)。

SELECTemployee_idFROMemployeeWHEREdepartment_id=‘D002’結(jié)果為：employee_idE0036.2.1單值嵌套查詢(xún)（續(xù)）SELECTemployee_idFROMemployeeWHEREdepartment_id=‘D002’(SELECTdepartment_idFROMdepartmentWHEREdepartment_name='市場(chǎng)部')上述分步查詢(xún)可改寫(xiě)成如下嵌套查詢(xún)單值嵌套查詢(xún)舉例[例6-18-1]查詢(xún)工資比D001部門(mén)人員最高工資還要高的職工名單SELECTemployee_nameFROMEmployeeWHEREwages>(SELECTMAX（wages）FROMemployeeWHEREdeparment_id＝‘D001’）①首先在employee關(guān)系中找出D001部門(mén)最高工資；②然后在employee關(guān)系中取出employee_name。查詢(xún)執(zhí)行過(guò)程：思考：例6-18-2查詢(xún)工資比市場(chǎng)部工資最高的人還要高的職工名單單值嵌套查詢(xún)舉例（2）[例6-18-2]查詢(xún)工資比市場(chǎng)部人員最高工資還要高的職工名單SELECTemployee_nameFROMEmployeeWHEREwages>(SELECTMAX（wages）FROMemployeeWHEREdeparment_id＝(SELECTdepartment_idFROMdepartmentWHEREdepartment_name=‘市場(chǎng)部’);①首先在department關(guān)系中找出“市場(chǎng)部”的員工號(hào)；②然后在employee關(guān)系中找出市場(chǎng)部最高工資；③最后在employee關(guān)系中取出employee_name。查詢(xún)執(zhí)行過(guò)程：6.2.2多值嵌套查詢(xún)子查詢(xún)的返回結(jié)果是一列值的嵌套查詢(xún)稱(chēng)為多值嵌套查詢(xún)。IN運(yùn)算符ALL運(yùn)算符ANY運(yùn)算符>ANY 大于子查詢(xún)結(jié)果中的某個(gè)值>ALL 大于子查詢(xún)結(jié)果中的所有值<ANY 小于子查詢(xún)結(jié)果中的某個(gè)值<ALL 小于子查詢(xún)結(jié)果中的所有值>=ANY 大于等于子查詢(xún)結(jié)果中的某個(gè)值>=ALL 大于等于子查詢(xún)結(jié)果中的所有值<=ANY 小于等于子查詢(xún)結(jié)果中的某個(gè)值<=ALL 小于等于子查詢(xún)結(jié)果中的所有值=ANY 等于子查詢(xún)結(jié)果中的某個(gè)值相當(dāng)于IN=ALL 等于子查詢(xún)結(jié)果中的所有值（通常沒(méi)有實(shí)際意義）!=（或<>）ANY 不等于子查詢(xún)結(jié)果中的某個(gè)值（相當(dāng)于NOTIN）!=（或<>）ALL 不等于子查詢(xún)結(jié)果中的任何一個(gè)值IN運(yùn)算符的用法

例6-21

對(duì)Sales數(shù)據(jù)庫(kù)，列出部門(mén)為“市場(chǎng)部”或“銷(xiāo)售部”的所有員工的編號(hào)。

SELECTemployee_idFROMemployeeWHEREdepartment_idIN

(SELECTdepartment_idFROMdepartmentWHEREdepartment_name='市場(chǎng)部'ORdepartment_name='銷(xiāo)售部')

ANY運(yùn)算符的用法

例6-19

對(duì)Sales數(shù)據(jù)庫(kù)，列出D001號(hào)部門(mén)中工資比D002號(hào)部門(mén)的員工最低工資高的員工編號(hào)和工資。

SELECTemployee_id,wagesFROMemployeeWHEREdepartment_id='D001'ANDwages>ANY(SELECTwagesFROMemployeeWHEREdepartment_id='D002')ALL運(yùn)算符的用法

例6-20

對(duì)Sales數(shù)據(jù)庫(kù)，列出部門(mén)編號(hào)為“D001”的員工，這些員工的工資比部門(mén)為“D002”的員工的最高工資還要高的員工的編號(hào)和工資。

SELECTemployee_id,wagesFROMemployeeWHEREdepartment_id=‘D001’ANDwages>ALL

(SELECTwagesFROMemployeeWHEREdepartment_id='D002')連接查詢(xún)內(nèi)連接（Innerjoin)等值連接自然連接非等值連接自身連接外連接（Outerjoin)左外連接右外連接全外連接交叉連接（Crossjoin)連接查詢(xún)連接查詢(xún)－－將兩個(gè)(以上)表連接進(jìn)行查詢(xún)同時(shí)涉及多個(gè)表的查詢(xún)一般格式：WHERE[<表名1>.]<列名1><比較運(yùn)算符>[<表名2>.]<列名2>FROM表名1<連接類(lèi)型>表名2ON[<表名1>.]<列名1><比較運(yùn)算符>[<表名2>.]<列名2>連接字段連接謂詞中的列名稱(chēng)為連接字段連接條件中的各連接字段類(lèi)型必須是可比的，但不必是相同的連接查詢(xún)(續(xù)1)連接操作的執(zhí)行過(guò)程(1)首先在表1中找到第一個(gè)元組，然后從頭開(kāi)始掃描表2，逐一查找滿(mǎn)足連接條件的元組，找到后就將表1中的第一個(gè)元組與該元組拼接起來(lái)，形成結(jié)果表中一個(gè)元組。(2)表2全部查找完后，再找表1中第二個(gè)元組，然后再?gòu)念^開(kāi)始掃描表2，逐一查找滿(mǎn)足連接條件的元組，找到后就將表1中的第二個(gè)元組與該元組拼接起來(lái)，形成結(jié)果表中一個(gè)元組。(3)重復(fù)上述操作，直到表1中的全部元組都處理完畢等值連接舉例SnoSnameSsexSageSdept2005001李勇男20CS2005002劉晨女19IS2005003王敏女18MA2005004張立男19ISSnoCnoGrade20050011922005001285200500138820050022902005002380Student表SC表Student.snoSnameSsexSageSdeptSC.SnoCnoGrade2005001李勇男20CS20050011922005001李勇男20CS20050012852005001李勇男20CS20050013882005002劉晨女19IS20050022902005002劉晨女19IS2005002380查詢(xún)結(jié)果：自然連接去掉此列等值連接與自然連接查詢(xún)等值連接:連接運(yùn)算符為=的連接操作,按對(duì)應(yīng)列的共同值將一個(gè)表中的記錄與另一個(gè)表中的記錄相連接，包括其中的重復(fù)列。自然連接:不包括重復(fù)列。[例31]

查詢(xún)每個(gè)學(xué)生及其選修課程的情況。SELECTStudent.*,SC.*FROMStudent,SCWHEREStudent.Sno=SC.Sno；//等值連接或SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudent,SCWHEREStudent.Sno=SC.Sno；//自然連接注：WHERE子句中參與比較的兩個(gè)屬性名若在另一表中存在，則要加所屬表名作前輟以區(qū)別，無(wú)同名則可省前輟。等值連接例6-23

Sales數(shù)據(jù)庫(kù)中部門(mén)表department和員工表employee的等值連接。SELECT*FROMdepartmentINNERJOINemployeeONemployee.department_id=department.department_id等值連接（續(xù)）

例6-22對(duì)Sales數(shù)據(jù)庫(kù)輸出所有員工的銷(xiāo)售單，要求給出員工編號(hào)、姓名、商品編號(hào)、商品名和銷(xiāo)售數(shù)量。

SELECTemployee.employee_id,employee.employee_name,goods.goods_id,goods.goods_name,sell_order.order_numFROMemployee,sell_order,goodsWHEREemployee.employee_id=sell_order.employee_idandsell_order.goods_id=goods.goods_id不等值連接例6-24對(duì)Sales數(shù)據(jù)庫(kù)，列出銷(xiāo)售“G00001”產(chǎn)品的員工中，銷(xiāo)售數(shù)量大于編號(hào)為“E001”的員工銷(xiāo)售該類(lèi)產(chǎn)品銷(xiāo)售數(shù)量的那些員工的編號(hào)和銷(xiāo)售數(shù)量。SELECTa.employee_id,a.order_numFROMsell_orderaINNERJOINsell_orderbONa.order_num>b.order_numANDa.goods_id=b.goods_idWHERE(b.goods_id='G00001')AND(b.employee_id='E001')自然連接例6-25Sales數(shù)據(jù)庫(kù)中部門(mén)表department和員工表employee的自然連接。

SELECTa.department_name,b.*FROMdepartmentaINNERJOINemployeebONb.department_id=a.department_id基本查詢(xún)回顧SELECT[ALL|DISTINCT][＊|<columns>|<arithmeticexpressions>|<AggregateFunctions>][[AS]<alias>],…FROM<table>WHERE<conditions>GROUPBY<columnN>

HAVING<inner-groupconfitions>ORDERBY<columnN>[ASC|DESC]特別注意：集函數(shù)只能用于SELECT子句和HAVING短語(yǔ)之中，而絕對(duì)不能出現(xiàn)在WHERE子句中。WHERE子句用于對(duì)查詢(xún)的表限定，而HAVING短語(yǔ)用于對(duì)結(jié)果集中的各分組進(jìn)行限定。嵌套查詢(xún)一個(gè)SELECT-FROM-WHERE語(yǔ)句稱(chēng)為一個(gè)查詢(xún)塊將一個(gè)查詢(xún)塊嵌套在另一個(gè)查詢(xún)塊的WHERE子句或HAVING短語(yǔ)的條件中的查詢(xún)稱(chēng)為嵌套查詢(xún)SELECTemployee_name//

外層查詢(xún)/父查詢(xún)

FROMemployeeWHEREdepartment_idIN(SELECTdepartment_id//

內(nèi)層查詢(xún)/子查詢(xún)

FROMdepartmentWHEREdepartment_name=‘市場(chǎng)部'）；子查詢(xún)的限制－－不能使用ORDERBY子句層層嵌套方式反映了SQL語(yǔ)言的結(jié)構(gòu)化有些嵌套查詢(xún)可以用連接運(yùn)算替代連接查詢(xún)連接查詢(xún)－－將兩個(gè)(以上)表連接進(jìn)行查詢(xún)同時(shí)涉及多個(gè)表的查詢(xún)一般格式：WHERE[<表名1>.]<列名1><比較運(yùn)算符>[<表名2>.]<列名2>FROM表名1<連接類(lèi)型>表名2ON[<表名1>.]<列名1><比較運(yùn)算符>[<表名2>.]<列名2>連接字段連接謂詞中的列名稱(chēng)為連接字段連接條件中的各連接字段類(lèi)型必須是可比的，但不必是相同的等值連接與自然連接查詢(xún)等值連接:連接運(yùn)算符為=的連接操作,按對(duì)應(yīng)列的共同值將一個(gè)表中的記錄與另一個(gè)表中的記錄相連接，包括其中的重復(fù)列。自然連接:不包括重復(fù)列。[例31]

查詢(xún)每個(gè)學(xué)生及其選修課程的情況。SELECTStudent.*,SC.*FROMStudent,SCWHEREStudent.Sno=SC.Sno；//等值連接或SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudent,SCWHEREStudent.Sno=SC.Sno；//自然連接注：WHERE子句中參與比較的兩個(gè)屬性名若在另一表中存在，則要加所屬表名作前輟以區(qū)別，無(wú)同名則可省前輟。自身連接－－一個(gè)表與其自己進(jìn)行連接[例32]查詢(xún)每一門(mén)課的間接先修課（即先修課的先修課）。CnoCnameCpnoCcredit1數(shù)據(jù)庫(kù)542數(shù)學(xué)23信息系統(tǒng)144操作系統(tǒng)635數(shù)據(jù)結(jié)構(gòu)746數(shù)據(jù)處理27PASCAL語(yǔ)言64CnoCnameCpnoCcredit1數(shù)據(jù)庫(kù)542數(shù)學(xué)23信息系統(tǒng)144操作系統(tǒng)635數(shù)據(jù)結(jié)構(gòu)746數(shù)據(jù)處理27PASCAL語(yǔ)言64FirstSecondcnocpno1723545667SELECTFIRST.Cno，SECOND.CpnoFROMCourseFIRST，CourseSECONDWHEREFIRST.Cpno=SECOND.Cno;自身連接(續(xù))[例33]查詢(xún)每一門(mén)課的間接先修課（即先修課的先修課）。

SELECTFIRST.Cno，SECOND.CpnoFROMCourseFIRST，CourseSECONDWHEREFIRST.Cpno=SECOND.Cno;

注：(1)一個(gè)表與其自己進(jìn)行連接，稱(chēng)為表的自身連接(2)需要給表起別名以示區(qū)別(3)由于所有屬性名都是同名屬性，因此必須使用別名前綴外連接（OuterJoin）外連接與內(nèi)連接的區(qū)別內(nèi)連接操作只輸出滿(mǎn)足連接條件的元組外連接操作以指定表為連接主體，將主體表中不滿(mǎn)足連接條件的元組一并輸出SnoSnameSsexSageSdept2005001李勇男20CS2005002劉晨女19IS2005003王敏女18MA2005004張立男19ISSnoCnoGrade20050011922005001285200500138820050022902005002380Student表SC表Student.snoSnameSsexSageSdeptCnoGrade2005001李勇男20CS1922005001李勇男20CS2852005001李勇男20CS3882005002劉晨女19IS2902005002劉晨女19IS3802005003王敏女18MANULLNULL2005004張立男19ISNULLNULL左外連接：StudentSC右外連接舉例SnoSnameSsexSageSdept2005001李勇男20CS2005002劉晨女19IS2005003王敏女18MA2005004張立男19ISSnoCnoGrade200500119220050012852005001388200500229020050052682005002380Student表SC表SnameSsexSageSdeptStudent.snoCnoGrade李勇男20CS2005001192李勇男20CS2005001285李勇男20CS2005001388劉晨女19IS2005002290NULLNULLNULLNULL2005005268劉晨女19IS2005002380右外連接：StudentSC交叉連接不帶連接謂詞的連接很少使用例：SELECTStudent.*,SC.*FROMStudent,SCE_idE_namesexBirthD_idE001李勇男20CSE002劉晨女19ISE003王敏女18MAE004張立男19ISE_idCnoGrade20050011922005001285200500138820050022902005002380employee表SC表交叉連接結(jié)果Student.snoSnameSsexSageSdeptSC.SnoCnoGrade2005001李勇男20CS20050011922005001李勇男20CS20050012852005001李勇男20CS20050013882005001李勇男20CS20050022902005001李勇男20CS20050023802005002劉晨女19IS20050011922005002劉晨女19IS20050012852005002劉晨女19IS20050013882005002劉晨女19IS20050022902005002劉晨女19IS20050023802005003王敏女18MA20050011922005003王敏女18MA20050012852005003王敏女18MA20050013882005003王敏女18MA20050022902005003王敏女18MA20050023802005004張立男19IS20050011922005004張立男19IS20050012852005004張立男19IS20050013882005004張立男19IS20050022902005004張立男19IS2005002380選擇題在SQL語(yǔ)句中，與表達(dá)式”工資BETWEEN1210AND1240“功能相同的表達(dá)式是（）。A工資>=1210AND工資<=1240B工資>1210AND工資<1240C工資<=1210AND工資>1240D工資>=1210OR工資<=1240選擇題使用SQL語(yǔ)句進(jìn)行分組檢索時(shí)，為了去掉不滿(mǎn)足條件的分組，應(yīng)當(dāng)（）。A使用WHERE子句B在GROUPBY后面使用HAVING子句C先使用WHERE子句，再使用HAVING子句D先使用HAVING子句，再使用WHERE子句對(duì)于某語(yǔ)句的條件WHERESdeptLIKE‘[CS]her%y’，將篩選出（）值。ACSherryBSherrieyCChersD[CS]Herry問(wèn)答題1在SELECT語(yǔ)句中，對(duì)查詢(xún)結(jié)果進(jìn)行排序的子句是什么？能消除重復(fù)行的關(guān)鍵字是什么？2寫(xiě)出表達(dá)式“倉(cāng)庫(kù)號(hào)NOTIN(‘wh1’,‘wh2’)”功能相同的表達(dá)式。用BETWEEN…AND形式改寫(xiě)條件子句WHEREmark>=550ANDmark<=650。3在一個(gè)包含集合函數(shù)的SELECT語(yǔ)句中，GROUPBY子句有哪些用途？4HAVING與WHERE同時(shí)用于指出查詢(xún)條件，請(qǐng)說(shuō)明各自的應(yīng)用場(chǎng)合。SnoSnameSsexSageSdept2005001李勇男20CS2005002劉晨女19IS2005003王敏女18MA2005004張立男19ISSnoCnoScore200500119220050012852005001388200500229020050052682005002380Student表SC表CnoCnameCpnoCcredit1數(shù)據(jù)庫(kù)542數(shù)學(xué)23信息系統(tǒng)144操作系統(tǒng)635數(shù)據(jù)結(jié)構(gòu)746數(shù)據(jù)處理27PASCAL語(yǔ)言64COURSE表查詢(xún)語(yǔ)句練習(xí)查課程名含有

DB_Design

課程的課程號(hào)和學(xué)分。查詢(xún)選修了3號(hào)課程的學(xué)生的學(xué)號(hào)及其成績(jī)，查詢(xún)結(jié)果按分?jǐn)?shù)降序排列。查詢(xún)學(xué)習(xí)1號(hào)課程的學(xué)生最高分?jǐn)?shù)。查詢(xún)選修2號(hào)課程且成績(jī)?cè)?0分以上的所有學(xué)生。查詢(xún)每個(gè)學(xué)生及其選修的課程名其及成績(jī)。求2課程的成績(jī)高于張三的學(xué)生學(xué)號(hào)和成績(jī)。求其他系中年齡小于計(jì)算機(jī)系年齡最大者的學(xué)生。求其他系中比計(jì)算機(jī)系學(xué)生年齡都小的學(xué)生。求選修了2號(hào)課程的學(xué)生姓名。查詢(xún)綜合舉例及注意事項(xiàng)一、別名的使用 (1)別名用于對(duì)輸出屬性列的重命名例：查詢(xún)?nèi)w學(xué)生的姓名、和所在系，并將輸出字段依次更名為：姓名、出生年份、所在系。

SELECTSname姓名，year(date())-Sage出生年份，ISLOWER(Sdept)所在系FROMStudent；(2