SQL Server數(shù)據(jù)庫第5章 數(shù)據(jù)查詢

第5章

數(shù)據(jù)查詢5.1投影查詢5.2選擇查詢5.3分組查詢和統(tǒng)計計算5.4排序查詢5.5連接查詢5.6子查詢5.7SELECT查詢的其他子句5.8綜合訓(xùn)練1SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

第5章

數(shù)據(jù)查詢對于數(shù)據(jù)庫應(yīng)用，最重要的操作是查詢，即從數(shù)據(jù)庫的一個或多個表中查詢出所要求的數(shù)據(jù)信息。T-SQL對數(shù)據(jù)庫的查詢使用SELECT語句，SELECT語句具有靈活的使用方式和強大的功能。語法格式：SELECTselect_list/*指定要選擇的列*/FROMtable_source/*FROM子句，指定表或視圖*/[WHEREsearch_condition]/*WHERE子句，指定查詢條件*/[GROUPBYgroup_by_expression]/*GROUPBY子句，指定分組表達(dá)式*/[HAVINGsearch_condition]/*HAVING子句，指定分組統(tǒng)計條件*/[ORDERBYorder_expression[ASC|DESC]]/*ORDER子句，指定排序表達(dá)式和順序*/2SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.1投影查詢語法格式：SELECT[ALL|DISTINCT][TOPn[PERCENT][WITHTIES]]<select_list>select_list指出了結(jié)果的形式，其格式為：{* /*選擇當(dāng)前表或視圖的所有列*/|{table_name|view_name|table_alias}.*/*選擇指定的表或視圖的所有列*/|{colume_name|expression|$IDENTITY|$ROWGUID[[AS]column_alias]/*選擇指定的列并更改列標(biāo)題，為列指定別名，還可用于為表達(dá)式結(jié)果指定名稱，*/|column_alias=expression }[,…n]3SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.1投影查詢1．投影指定的列使用SELECT語句可選擇表中的一個列或多個列，如果是多個列，各列名中間要用逗號分開。語法格式：SELECTcolumn_name[,column_name…]FROMtable_nameWHEREsearch_condition其中，F(xiàn)ROM子句用于指定表，WHERE在該表中檢索符合search_condition條件的列。4SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.1投影查詢【例5.1】查詢student表中所有學(xué)生的學(xué)號、姓名和專業(yè)。USEstscoSELECTstid,stname,specialityFROMstudent查詢結(jié)果：stidstnamespeciality------------------------------------201001羅俊杰通信201002韓紅麗通信201004馮露通信202001徐橋計算機(jī)202002袁志敏計算機(jī)202005董莎計算機(jī)5SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.1投影查詢2．投影全部列在SELECT子句指定列的位置上使用*號時，則為查詢表中所有列?！纠?.2】查詢student表中所有列。USEstscoSELECT*FROMstudent該語句與下面語句等價USEstscoSELECTstid,stname,stsex,stbirthday,speciality,tcFROMstudent6SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.1投影查詢7SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

查詢結(jié)果：stidstnamestsexstbirthdayspecialitytc-------------------------------------------------------------------------201001

羅俊杰男2000-06-15通信52201002

韓紅麗女1999-09-23通信49201004

馮露女1999-08-07通信

50202001

徐橋男2000-02-25

計算機(jī)52202002

袁志敏男1999-12-04計算機(jī)48202005

董莎

女

2000-04-19計算機(jī)503．修改查詢結(jié)果的列標(biāo)題為了改變查詢結(jié)果中顯示的列標(biāo)題，可以在列名后使用AS子句。語法格式如下：AScolumn_alias其中column_alias是指定顯示的列標(biāo)題，AS可省略。

5.1投影查詢8【例5.3】查詢student表中通信專業(yè)學(xué)生的stid、stname、tc，并將結(jié)果中各列的標(biāo)題分別修改為學(xué)號,姓名,總學(xué)分。USEstscoSELECTstidAS'學(xué)號',stnameAS'姓名',tcAS'總學(xué)分'FROMstudent查詢結(jié)果：

學(xué)號姓名總學(xué)分----------------------------------201001羅俊杰52201002韓紅麗49201004馮露50202001徐橋52202002袁志敏48202005董莎50SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.1投影查詢4．去掉重復(fù)行去掉結(jié)果集中的重復(fù)行可使用DISTINCT關(guān)鍵字，其語法格式是：SELECTDISTINCTcolumn_name[,column_name…]【例5.4】查詢student表中specialist列，消除結(jié)果中的重復(fù)行。USEstscoSELECTDISTINCTspecialistFROMstudent查詢結(jié)果：speciality------------計算機(jī)通信9SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.2選擇查詢語法格式：選擇查詢通過WHERE子句實現(xiàn)，WHERE子句給出查詢條件，該子句必須緊跟FROM子句之后。WHERE<search_condition>其中search_condition為查詢條件，<search_condition>語法格式為：{[NOT]<precdicate>|(<search_condition>)}[{AND|OR}[NOT]{<predicate>|(<search_condition>)}]}[,…n]其中predicate為判定運算，<predicate>語法格式為：{expression{=|<|<=|>|>=|<>|!=|!<|!>}expression/*比較運算*/|string_expression[NOT]LIKEstring_expression[ESCAPE'escape_character'/*字符串模式匹配*/|expression[NOT]BETWEENexpressionANDexpression/*指定范圍*/|expressionIS[NOT]NULL /*是否空值判斷*/

10SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.2選擇查詢

|CONTAINS({column|*},'<contains_search_condition>')/*包含式查詢*/|FREETEXT({column|*},'freetext_string')

/*自由式查詢*/|expression[NOT]IN(subquery|expression[,…n])

/*IN子句*/|expression{=|<|<=|>|>=|<>|!=|!<|!>}{ALL|SOME|ANY}(subquery)

/*比較子查詢*/

|EXIST(subquery)

/*EXIST子查詢*/}表5.1查詢條件11SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.4.2選擇查詢1．表達(dá)式比較比較運算符用于比較兩個表達(dá)式值，比較運算的語法格式如下：expression{=|<|<=|>|>=|<>|!=|!<|!>}expression其中expression是除text、ntext和image之外類型的表達(dá)式。12SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.2選擇查詢13【例5.5】查詢student表中專業(yè)為計算機(jī)或性別為女的學(xué)生。USEstscoSELECT*FROMstudentWHEREspecialist='計算機(jī)'orstsex='女‘查詢結(jié)果：stidstnamestsexstbirthdayspecialitytc-------------------------------------------------------------------------201002韓紅麗女1999-09-23通信49201004馮露女1999-08-07通信50202001徐橋男2000-02-25計算機(jī)52202002袁志敏男1999-12-04計算機(jī)48202005董莎女2000-04-19計算機(jī)502．范圍比較BETWEEN、NOTBETWEEN、IN是用于范圍比較的三個關(guān)鍵字，用于查找字段值在（或不在）指定范圍的行。SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.2選擇查詢【例5.6】查詢score表成績?yōu)?6、94、95的記錄。USEstscoSELECT*FROMscoreWHEREgradeIN(86,94,95)查詢結(jié)果：stidcidgrade-----------------------------2010012059420100180195201004801862020018019414SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.2選擇查詢3．模式匹配字符串模式匹配使用LIKE謂詞，LIKE謂詞表達(dá)式的語法格式如下：string_expression[NOT]LIKEstring_expression[ESCAPE'escape_character']其含義是查找指定列值與匹配串相匹配的行，匹配串(即string_expression)可以是一個完整的字符串，也可以含有通配符。通配符有以下兩種：%：代表0或多個字符。_：代表一個字符。15SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.2選擇查詢【例5.7】查詢student表中姓董的學(xué)生情況。USEstscoSELECT*FROMstudentWHEREstnameLIKE'董%'查詢結(jié)果：stidstnamestsexstbirthdayspecialitytc-------------------------------------------------------------------------202005董莎女2000-04-19計算機(jī)5016SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.2選擇查詢4．空值使用空值是未知的值，判定一個表達(dá)式的值是否為空值時，使用ISNULL關(guān)鍵字，語法格式如下：expressionIS[NOT]NULL【例5.8】查詢已選課但未參加考試的學(xué)生情況。USEstscoSELECT*FROMscoreWHEREgradeISnull查詢結(jié)果：stidcidgrade----------------------------202002801NULL17SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.3分組查詢和統(tǒng)計計算1．聚合函數(shù)SQLServer所提供常用的聚合函數(shù)如表5.3所示。聚合函數(shù)一般參數(shù)語法格式如下：([ALL|DISTINCT]expression)其中，ALL表示對所有值進(jìn)行聚合函數(shù)運算，ALL為默認(rèn)值，DISTINCT表示去除重復(fù)值，expression指定進(jìn)行聚合函數(shù)運算的表達(dá)式。表5.3聚合函數(shù)18SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.3分組查詢和統(tǒng)計計算【例5.9】查詢102課程的最高分、最低分、平均成績。

USEstscoSELECTMAX(grade)AS'最高分',MIN(grade)AS'最低分',AVG(grade)AS'平均成績'FROMscoreWHEREcid='102'查詢結(jié)果：最高分

最低分平均成績-------------------------------------------------------93

71

8419SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.3分組查詢和統(tǒng)計計算【例5.10】求學(xué)生的總?cè)藬?shù)。

USEstscoSELECTCOUNT(*)AS'總?cè)藬?shù)'FROMstudent查詢結(jié)果：總?cè)藬?shù)-----------6【例5.11】查詢計算機(jī)專業(yè)學(xué)生的總?cè)藬?shù)。USEstscoSELECTCOUNT(*)AS'總?cè)藬?shù)'FROMstudentWHEREspeciality='計算機(jī)'20SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.3分組查詢和統(tǒng)計計算查詢結(jié)果：總?cè)藬?shù)---------32.GROUPBY子句GROUPBY子句用于將查詢結(jié)果表按某一列或多列值進(jìn)行分組，其語法格式為如下：

[GROUPBY[ALL]group_by_expression[,…n][WITH{CUBE|ROLLUP}]]

其中，group_by_expression為分組表達(dá)式，通常包含字段名，ALL顯示所有分組，WITH指定CUBE或ROLLUP操作符，在查詢結(jié)果中增加匯總記錄。

21SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.3分組查詢和統(tǒng)計計算【例5.12】查詢各門課程的最高分、最低分、平均成績。

USEstscoSELECTcidAS'課程號',MAX(grade)AS'最高分',MIN(grade)AS'最低分',AVG(grade)AS'平均成績'FROMscoreWHERENOTgradeISNULLGROUPBYcid查詢結(jié)果：課程號最高分最低分平均成績------------------------------------------------------------------10293718420392708220594688380195768822SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.3分組查詢和統(tǒng)計計算【例5.13】求選修各門課程的平均成績和選修人數(shù)。USEstscoSELECTcidAS'課程號',AVG(grade)AS'平均成績',COUNT(*)AS'選修人數(shù)'FROMscoreGROUPBYcid查詢結(jié)果：課程號平均成績選修人數(shù)---------------------------------------------10284320382320583380188623SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.3分組查詢和統(tǒng)計計算3．HAVING子句HAVING子句用于對分組按指定條件進(jìn)一步進(jìn)行篩選，最后只輸出滿足指定條件的分組，HAVING子句的格式為：

[HAVING<search_condition>]

其中，search_condition為查詢條件，可以使用聚合函數(shù)。

當(dāng)WHERE子句、GROUPBY子句、HAVING子句在一個SELECT語句中時，執(zhí)行順序如下：（1）執(zhí)行WHERE子句，在表中選擇行。（2）執(zhí)行GROUPBY子句，對選取行進(jìn)行分組。（3）執(zhí)行聚合函數(shù)。（4）執(zhí)行HAVING子句，篩選滿足條件的分組。24SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.3分組查詢和統(tǒng)計計算【例5.14】查詢選修課程2門以上且成績在80分以上的學(xué)生的學(xué)號。USEstscoSELECTstidAS'學(xué)號',COUNT(cid)AS'選修課程數(shù)'FROMscoreWHEREgrade>=80GROUPBYstidHAVINGCOUNT(*)>=2查詢結(jié)果：學(xué)號選修課程數(shù)-----------------------------201001320100432020012202005225SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.3分組查詢和統(tǒng)計計算【例5.15】查詢至少有4名學(xué)生選修且以8開頭的課程號和平均分?jǐn)?shù)。

USEstscoSELECTcidAS'課程號',AVG(grade)AS'平均分?jǐn)?shù)'FROMscoreWHEREcidLIKE'8%'GROUPBYcidHAVINGCOUNT(*)>4查詢結(jié)果：課程號平均分?jǐn)?shù)------------------------8018826SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.4排序查詢

SELECT語句的ORDERBY子句用于對查詢結(jié)果按升序（默認(rèn)或ASC）或降序（DESC）排列行，ORDERBY子句的格式如下：

[ORDERBY{order_by_expression[ASC|DESC]}[,…n]

其中，order_by_expression是排序表達(dá)式?！纠?.16】將計算機(jī)專業(yè)的學(xué)生按出生時間先后排序。USEstscoSELECT*FROMstudentWHEREspeciality='計算機(jī)'ORDERBYstbirthday查詢結(jié)果：stidstnamestsexstbirthdayspecialitytc-------------------------------------------------------------------------202002袁志敏男1999-12-04計算機(jī)48202001徐橋男2000-02-25計算機(jī)52202005董莎女2000-04-19計算機(jī)5027SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.4排序查詢【例5.17】將通信專業(yè)學(xué)生按“數(shù)字電路”課程成績降序排序。USEstscoSELECTa.stname,ame,c.gradeFROMstudenta,courseb,scorecWHEREa.stid=c.stidANDb.cid=c.cidANDame='數(shù)字電路'ANDa.speciality='通信'ORDERBYc.gradeDESC查詢結(jié)果：stnamecnamegrade------------------------------------------------羅俊杰數(shù)字電路93馮露數(shù)字電路89韓紅麗數(shù)字電路7128SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.5連接查詢5.5.1

連接謂詞在SELECT語句的WHERE子句中使用比較運算符給出連接條件對表進(jìn)行連接，將這種表示形式稱為連接謂詞表示形式。其一般語法格式為：

[<表名1.>]<列名1><比較運算符>[<表名2.>]<列名2>比較運算符有：<、<=、=、>、>=、!=、<>、!<、!>連接謂詞還有以下形式：

[<表名1.>]<列名1>BETWEEN[<表名2.>]<列名2>AND[<表名2.>]<列名3>由于連接多個表存在公共列，為了區(qū)分是哪個表中的列，引入表名前綴指定連接列。例如，student.stno表示student表的stno列，score.stno表示score表的stno列經(jīng)常用到的連接如下：●等值連接：表之間通過比較運算符“=”連接起來，稱為等值連接?！穹堑戎颠B接：表之間使用非等號進(jìn)行連接，則稱為非等值連接?！?/p>

自然連接：如果在目標(biāo)列中去除相同的字段名，稱為自然連接。●

自連接：將同一個表進(jìn)行連接，稱為自連接。29SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.5連接查詢【例5.18】查詢學(xué)生的情況和選修課程的情況。

USEstscoSELECTstudent.*,score.*FROMstudent,scoreWHEREstudent.stid=score.stid查詢結(jié)果：30SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

stidstnamestsexstbirthdayspecialitytcstidcidgrade----------------------------------------------------------------------------------201001羅俊杰男2000-06-15通信5220100110293201001羅俊杰男2000-06-15通信5220100120594201001羅俊杰男2000-06-15通信5220100180195201002韓紅麗女1999-09-23通信4920100210271201002韓紅麗女1999-09-23通信4920100220568201002韓紅麗女1999-09-23通信4920100280176201004馮露女1999-08-07通信5020100410289201004馮露女1999-08-07通信5020100420587201004馮露女1999-08-07通信5020100480186202001徐橋男2000-02-25計算機(jī)5220200120392202001徐橋男2000-02-25計算機(jī)5220200180194202002袁志敏男1999-12-04計算機(jī)4820200220370202002袁志敏男1999-12-04計算機(jī)48202002801NULL202005董莎女2000-04-19計算機(jī)5020200520384202005董莎女2000-04-19計算機(jī)50202005801895.5連接查詢【例5.19】對上例進(jìn)行自然連接查詢。

USEstscoSELECTstudent.*,score.cid,score.gradeFROMstudent,scoreWHEREstudent.stid=score.stid查詢結(jié)果：31SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

stidstnamestsexstbirthdayspecialitytccidgrade------------------------------------------------------------------------201001羅俊杰男2000-06-15通信5210293201001羅俊杰男2000-06-15通信5220594201001羅俊杰男2000-06-15通信5280195201002韓紅麗女1999-09-23通信4910271201002韓紅麗女1999-09-23通信4920568201002韓紅麗女1999-09-23通信4980176201004馮露女1999-08-07通信5010289201004馮露女1999-08-07通信5020587201004馮露女1999-08-07通信5080186202001徐橋男2000-02-25計算機(jī)5220392202001徐橋男2000-02-25計算機(jī)5280194202002袁志敏男1999-12-04計算機(jī)4820370202002袁志敏男1999-12-04計算機(jī)48801NULL202005董莎女2000-04-19計算機(jī)5020384202005董莎女2000-04-19計算機(jī)50801895.5連接查詢【例5.20】查詢選修了“微機(jī)原理”且成績在80分以上的學(xué)生姓名。

USEstscoSELECTa.stid,a.stname,ame,c.gradeFROMstudenta,courseb,scorecWHEREa.stid=c.stidANDb.cid=c.cidANDame='微機(jī)原理'ANDC.grade>=80查詢結(jié)果：stidstnamecnamegrade---------------------------------------------------201001

羅俊杰微機(jī)原理

94201004

馮露微機(jī)原理

8732SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.5連接查詢【例5.21】查詢選修了“801”課程的成績高于學(xué)號為“201002”的成績的學(xué)生姓名。

USEstscoSELECTa.cid,a.stid,a.gradeFROMscorea,scorebWHEREa.cid='801'ANDa.grade>b.gradeANDb.stid='201002'ANDb.cid='801'ORDERBYa.gradeDESC查詢結(jié)果：cidstidgrade----------------------------80120100195801202001948012020058980120100486

33SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.5連接查詢5.5.2以JOIN關(guān)鍵字指定的連接JOIN連接在FROM子句的<joined_table>中指定。語法格式

：<joined_table>::={<table_source><join_type><table_source>ON<search_condition>|<table_source>CROSSJOIN<table_source>|<joined_table>}說明：<join_type>為連接類型,ON用于指定連接條件,<join_type>的格式如下:[INNER|{LEFT|RIGHT|FULL}[OUTER][<join_hint>]JOININNER表示內(nèi)連接，OUTER表示外連接，CROSS表示交叉連接，此為JOIN關(guān)鍵字指定的連接的3種類型。34SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.5連接查詢1.內(nèi)連接

內(nèi)連接按照ON所指定的連接條件合并兩個表，返回滿足條件的行。

內(nèi)連接是系統(tǒng)默認(rèn)的，可省略INNER關(guān)鍵字。【例5.22】查詢學(xué)生的情況和選修課程的情況。

USEstscoSELECT*FROMstudentINNERJOINscoreONstudent.stid=score.stid35SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.5連接查詢【例5.23】查詢選修了102課程且成績在85分以上的學(xué)生情況。

USEstscoSELECTa.stid,a.stname,b.cid,b.gradeFROMstudentaJOINscorebONa.stid=b.stidWHEREb.cid='102'ANDb.grade>=85查詢結(jié)果：

stid

stname

cidgrade----------------------------------------201001

羅俊杰

102

93201004馮露

1028936SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.5連接查詢2.外連接

在內(nèi)連接的結(jié)果表，只有滿足連接條件的行才能作為結(jié)果輸出。外連接的結(jié)果表不但包含滿足連接條件的行，還包括相應(yīng)表中的所有行。外連接有以下3種：

●

左外連接（LEFTOUTERJOIN）：結(jié)果表中除了包括滿足連接條件的行外，還包括左表的所有行；

●

右外連接（RIGHTOUTERJOIN）：結(jié)果表中除了包括滿足連接條件的行外，還包括右表的所有行；●

完全外連接（FULLOUTERJOIN）：結(jié)果表中除了包括滿足連接條件的行外，還包括兩個表的所有行。37SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.5連接查詢【例5.24】采用左外連接查詢教師任課情況。USEstscoSELECTtname,cidFROMteacherLEFTJOINlectureON(teacher.tid=lecture.tid)查詢結(jié)果：tnamecid---------------------沈思敏102傅潔NULL許強203鄭書雅205程建明80138SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.5連接查詢【例5.25】采用右外連接查詢教師任課情況。USEstscoSELECTtid,cnameFROMlectureRIGHTJOINcourseON(course.cid=lecture.cid)查詢結(jié)果：tid

cname---------------------------102104

數(shù)字電路204102

數(shù)據(jù)庫系統(tǒng)204105

微機(jī)原理NULL

計算機(jī)網(wǎng)絡(luò)801106

高等數(shù)學(xué)

39SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.5連接查詢

3.交叉連接【例5.26】采用交叉連接查詢教師和課程所有可能組合。

USEstscoSELECTteacher.tname,ameFROMteacherCROSSJOINcourse

40SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.6子查詢在SQL語言中，一個SELECT-FROM-WHERE語句稱為一個查詢塊。在WHERE子句或HAVING子句所指定條件中，可以使用另一個查詢塊的查詢的結(jié)果作為條件的一部分，這種將一個查詢塊嵌套在另一個查詢塊的子句指定條件中的查詢稱為嵌套查詢。T-SQL允許SELECT多層嵌套使用。5.6.1

IN子查詢

IN子查詢用于進(jìn)行一個給定值是否在子查詢結(jié)果集中的判斷，語法格式如下：

expression[NOT]IN(subquery)

當(dāng)表達(dá)式expression與子查詢subquery的結(jié)果集中的某個值相等時，IN謂詞返回TRUE，否則返回FALSE。若使用了NOT，則返回的值相反。41SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.6子查詢【例5.27】查詢選修了課程號為203的課程的學(xué)生情況。USEstscoSELECT*FROMstudentWHEREstidIN(SELECTstidFROMscoreWHEREcid='203')查詢結(jié)果：stidstnamestsexstbirthdayspecialitytc-------------------------------------------------------------------------202001徐橋男2000-02-25計算機(jī)52202002袁志敏男1999-12-04計算機(jī)48202005董莎女2000-04-19計算機(jī)5042SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.6子查詢【例5.28】查詢選修某課程的學(xué)生人數(shù)多于4人的教師姓名。USEstscoSELECTtnameAS'教師姓名'FROMteacherWHEREtidIN(SELECTtidFROMlectureWHEREcidIN(SELECTb.cidFROMcoursea,scorebWHEREa.cid=b.cidGROUPBYb.cidHAVINGCOUNT(b.cid)>4)

)查詢結(jié)果：教師姓名--------------程建明43SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.6子查詢

5.6.2比較子查詢語法格式：expression{<|<=|=|>|>=|!=|<>|!<|!>}{ALL|SOME|ANY}(subquery)其中，expression為要進(jìn)行比較的表達(dá)式，subquery是子查詢，ALL、SOME和ANY是對比較運算的限制。【例5.29】查詢比所有計算機(jī)專業(yè)學(xué)生年齡都小的學(xué)生。USEstscoSELECT*FROMstudentWHEREstbirthday>ALL(SELECTstbirthdayFROMstudentWHEREspeciality='計算機(jī)')查詢結(jié)果：44stidstnamestsexstbirthdayspecialitytc-------------------------------------------------------------------201001羅俊杰男2000-06-15通信52SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.6子查詢【例5.30】查詢課程號801的成績高于課程號205成績的學(xué)生。

USEstscoSELECTstidAS'學(xué)號'FROMscoreWHEREcid='801'ANDgrade>=ANY(SELECTgradeFROMscoreWHEREcid='205')

45查詢結(jié)果：學(xué)號--------------201001201002201004202001202005SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.6子查詢5.6.3

EXISTS子查詢EXISTS謂詞用于測試子查詢的結(jié)果是否為空表，若子查詢的結(jié)果集不為空，則EXISTS返回TRUE，否則返回FALSE，如果為NOTEXISTS，其返回值與EXIST相反，其語法格式如下：[NOT]EXISTS(subquery)46查詢結(jié)果：姓名------------羅俊杰韓紅麗馮露SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

【例5.31】查詢選修205課程的學(xué)生姓名。USEstscoSELECTstnameAS'姓名'FROMstudentWHEREEXISTS(SELECT*FROMscoreWHEREscore.stid=student.stidANDcid='205')5.6子查詢【例5.32】查詢所有任課教師姓名和學(xué)院。

USEstscoSELECTtnameAS'教師姓名',schoolAS'學(xué)院'FROMteacherWHEREtidIN(SELECTtidFROMlectureaWHEREEXISTS(SELECT*FROMcoursebWHEREa.cid=b.cid))47查詢結(jié)果：教師姓名學(xué)院------------------------------------沈思敏計算機(jī)學(xué)院許強

通信學(xué)院鄭書雅通信學(xué)院程建明數(shù)學(xué)學(xué)院SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.7SELECT查詢的其它子句

1.UNION

使用UNION可以將兩個或多個SELECT查詢的結(jié)果合并成一個結(jié)果集，其格式如下：

{<queryspecification>|(<queryexpression>)}UNION[ALL]<queryspecification>|(<queryexpression>)[UNION[ALL]<queryspecification>|(<queryexpression>)[…n]]

說明：

<queryspecification>和(<queryexpression>都是SELECT查詢語句。

使用UNION合并兩個查詢的結(jié)果集的基本規(guī)則是：

●

所有查詢中的列數(shù)和列的順序必須相同?！?/p>

數(shù)據(jù)類型必須兼容。48SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.7SELECT查詢的其它子句【例5.33】查詢總學(xué)分大于50及學(xué)號小于201051的學(xué)生。USEstscoSELECT*FROMstudentWHEREtc>50UNIONSELECT*FROMstudentWHEREstid<201051查詢結(jié)果：49SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

stidstnamestsexstbirthdayspecialitytc-------------------------------------------------------------------------201001羅俊杰男2000-06-15通信52201002韓紅麗女1999-09-23通信49201004馮露女1999-08-07通信50202001徐橋男2000-02-25計算機(jī)52202005董莎女2000-04-19計算機(jī)505.7SELECT查詢的其它子句2.EXCEPT和INTERSECT

語法格式：

{<query_specification>|(<query_expression>)}{EXCEPT|INTERSECT}{<query_specification>|(<query_expression>)}

說明：

<queryspecification>和<queryexpression>都是SELECT查詢語句。

使用EXCEPT或INTERSECT的兩個查詢的結(jié)果集組合起來的基本規(guī)則:

●所有查詢中的列數(shù)和列的順序必須相同。

●

數(shù)據(jù)類型必須兼容。50SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.7SELECT查詢的其它子句【例5.34】查詢學(xué)過801課程但未學(xué)過102課程的學(xué)生。USEstscoSELECTa.stidAS'學(xué)號',a.stnameAS'姓名'FROMstudenta,courseb,scorecWHEREa.stid=c.stidANDb.cid=c.cidANDc.cid='801'EXCEPTSELECTa.stidAS'學(xué)號',a.stnameAS'姓名'FROMstudenta,courseb,scorecWHEREa.stid=c.stidANDb.cid=c.cidANDc.cid='102'51學(xué)號姓名---------------------202001

徐橋202002

袁志敏202005

董莎查詢結(jié)果：SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.7SELECT查詢的其它子句【例5.35】查詢既學(xué)過801課程又學(xué)過102課程的學(xué)生。USEstscoSELECTa.stidAS'學(xué)號',a.stnameAS'姓名'FROMstudenta,courseb,scorecWHEREa.stid=c.stidANDb.cid=c.cidANDc.cid='801'INTERSECTSELECTa.stidAS'學(xué)號',a.stnameAS'姓名'FROMstudenta,courseb,scorecWHEREa.stid=c.stidANDb.cid=c.cidANDc.cid='102'52查詢結(jié)果：學(xué)號

姓名---------------------201001

羅俊杰201002

韓紅麗201004

馮露SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.7SELECT查詢的其它子句3.INTO子句

INTO子句用于創(chuàng)建新表并將查詢所得的結(jié)果插入新表中。

語法格式：

[INTOnew_table]

說明：new_table是要創(chuàng)建的新表名，創(chuàng)建的新表的結(jié)構(gòu)由SELECT所選擇的列決定，新表中的記錄由SELECT的查詢結(jié)果決定，若SELECT的查詢結(jié)果為空，則創(chuàng)建一個只有結(jié)構(gòu)而沒有記錄的空表。53SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.7SELECT查詢的其它子句【例5.36】由student表創(chuàng)建st表，包括學(xué)號、姓名、性別、專業(yè)和學(xué)分。USEstscoSELECTstid,stname,stsex,speciality,tcINTOstFROMstudent該語句通過INTO子句創(chuàng)建新表st，新表的結(jié)構(gòu)和記錄由SELECT…INTO語句決定。54SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.7SELECT查詢的其它子句4.CTE子句用于指定臨時結(jié)果集，這些結(jié)果集稱為公用表表達(dá)式(CommonTableExpression,CTE)。

語法格式：

[WITH<common_table_expression>[,...n]]<common_table_expression>::=expression_name[(column_name[,...n])]AS(CTE_query_definition)

說明：

●expression_name：CTE的名稱。

●column_name：在CTE中指定的列名，其個數(shù)要和CTE_query_definition返回的字段個數(shù)相同。

●CTE_query_definition：指定一個其結(jié)果集填充CTE的SELECT語句。CTE下方的SELECT語句可以直接查詢CTE中的數(shù)據(jù)。55SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.7SELECT查詢的其它子句【例5.37】使用CTE從score表中查詢學(xué)號、課程號和成績，并指定新列名為c_stno、c_cno、c_grade，再使用SELECT語句從CTE和student表中查詢姓名為”徐橋”的學(xué)號、課程號和成績。

USEstsco;WITHcte_st(c_stid,c_cid,c_grade)AS(SELECTstid,cid,gradeFROMscore)SELECTc_stid,c_cid,c_gradeFROMcte_st,studentWHEREstudent.stname='徐橋'ANDstudent.stid=cte_st.c_stid查詢結(jié)果：c_stidc_cidc_grade--------------------------------202001203922020018019456SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.7SELECT查詢的其它子句【例5.38】計算從1到10的階乘。WITHCfact(n,k)AS(SELECTn=1,k=1UNIONALLSELECTn=n+1,k=k*(n+1)FROMCfactWHEREn<10)SELECTn,kFROMCfact

57nk-----------------11223642451206720750408403209362880103628800SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

查詢結(jié)果：5.7SELECT查詢的其它子句5.FROM子句語法格式：

[FROM{<table_source>}[,…n]]<table_source>::={table_or_view_name[[AS]table_alias]|rowset_function[[AS]table_alias] /*行集函數(shù)*/[(bulk_column_alias[,...n])]|user_defined_function[[AS]table_alias]/*指定表值函數(shù)*/|OPENXML<openxml_clause> /*XML文檔*/|derived_table[AS]table_alias[(column_alias[,...n])]/*子查詢*/|<joined_table> /*連接表*/|<pivoted_table> /*將行轉(zhuǎn)換為列*/|<unpivoted_table> /*將列轉(zhuǎn)換為行*/}58SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.7SELECT查詢的其它子句

說明：

●table_or_view_name：指定SELECT語句要查詢的表或視圖。

●rowset_function：rowset_function是一個行集函數(shù)，行集函數(shù)通常返回一個表或視圖。●derived_table是由SELECT查詢語句的執(zhí)行而返回的表，必須使用為其指定一個別名，也可以為列指定別名。

●joined_table：joined_table為連接表。

●pivoted_table：將行轉(zhuǎn)換為列。

●<unpivoted_table>：將列轉(zhuǎn)換為行。59SQLServer數(shù)據(jù)庫技術(shù)與應(yīng)用

5.7SELECT查詢的其它子句【例5.39】查找student表中2000年4月30日以前出生的學(xué)生的姓名和性別，并列出其專業(yè)屬于通信還是計算機(jī)，1表示是，0表示否。USEstscoSELECTstname,stsex,通信,計算機(jī)FROMstudentPIVOT(COUNT(stid)FORspecialityIN(通信,計算機(jī)))ASpvtWHEREstbirthday<'2000-04-30'60stnamestsex通信計算機(jī)------------------------------------------董莎女01馮露女10韓紅麗

女

1

0徐橋男0