數(shù)據(jù)庫數(shù)據(jù)查詢

SQLServer2008應(yīng)用實(shí)踐教程

主講：凃云杰電子工業(yè)出版社鄭阿奇主編第五章數(shù)據(jù)庫數(shù)據(jù)查詢了解T-SQL的常用函數(shù)使用T-SQL進(jìn)行單表的簡(jiǎn)單查詢使用T-SQL進(jìn)行多表的內(nèi)連接和外連接，了解多表的交叉連接使用T-SQL進(jìn)行嵌套查詢使用SSMS進(jìn)行數(shù)據(jù)查詢教學(xué)目標(biāo)3教學(xué)內(nèi)容：5.1了解T-SQL的常用函數(shù)5.2分析“教學(xué)管理系統(tǒng)”中的數(shù)據(jù)查詢5.3簡(jiǎn)單查詢5.4連接查詢5.5嵌套查詢5.6使用SSMS進(jìn)行數(shù)據(jù)查詢教學(xué)重點(diǎn)難點(diǎn)：重點(diǎn)：連接查詢，難點(diǎn)：嵌套查詢45.1了解T-SQL的常用函數(shù)

任務(wù)：了解常用的SQL函數(shù)，掌握常用集合函數(shù)、字符串函數(shù)和日期時(shí)間函數(shù)。5數(shù)學(xué)函數(shù)：實(shí)現(xiàn)各種數(shù)學(xué)運(yùn)算和三角運(yùn)算。如：ABS:返回給定數(shù)字的絕對(duì)值。ABS(-44)=44SQRT：返回給定數(shù)字的平方根。SQRT(9)=9CEILING：返回大于或等于給定數(shù)字的最小整數(shù)。

CEILING(1.5)=2FLOOR：返回小于或等于給定數(shù)字的最大整數(shù)。FLOOR(1.3)=1RAND：返回一個(gè)0到1之間的隨機(jī)數(shù)。ROUND：四舍五入。ROUND(123.567,2)=123.570ROUND(123.567,1)=123.600T-SQL的常用函數(shù)6Sign：返回正負(fù)號(hào)。Sign(12)=1，Sign(-12)=-1，Sign（0）=0power：返回給定數(shù)字的乘冪。Power(3,4)=81EXP:返回指定數(shù)字的指數(shù)值。LOG:返回指定數(shù)字的自然對(duì)數(shù)。LOG10：返回指定數(shù)字的以10為底的對(duì)數(shù)值。Pi（）：返回π的常量值3.14159265358979，精確到小數(shù)點(diǎn)后14位。數(shù)學(xué)函數(shù)數(shù)學(xué)函數(shù)用來對(duì)數(shù)值型數(shù)據(jù)進(jìn)行數(shù)學(xué)運(yùn)算。常用數(shù)學(xué)函數(shù)如表6-1所示。表5-1常用數(shù)學(xué)函數(shù)

數(shù)學(xué)函數(shù)

功能描述

ABS(數(shù)值表達(dá)式)

返回表達(dá)式的絕對(duì)值(正值)

ACOS(浮點(diǎn)表達(dá)式)

返回浮點(diǎn)表達(dá)式的反余弦值(單位為弧度)

ASIN(浮點(diǎn)表達(dá)式)

返回返回浮點(diǎn)表達(dá)式的反正弦值(單位為弧度)

ATAN(浮點(diǎn)表達(dá)式)

返回浮點(diǎn)表達(dá)式的反正切值(單位為弧度)

ATN2(浮點(diǎn)表達(dá)式1,浮點(diǎn)表達(dá)式2)

返回以弧度為單位的角度值,此值的反正切值在所給的浮點(diǎn)表達(dá)式1和浮點(diǎn)表達(dá)式2之間

CEILING(數(shù)值表達(dá)式)

返回大于或等于數(shù)值表達(dá)式值的最小整數(shù)COS(浮點(diǎn)表達(dá)式

)返回浮點(diǎn)表達(dá)式的三角余弦

COT(浮點(diǎn)表達(dá)式)

返回浮點(diǎn)表達(dá)式的三角余切

DEGREES(數(shù)值表達(dá)式)

將弧度轉(zhuǎn)換為度

EXP(浮點(diǎn)表達(dá)式)

返回?cái)?shù)值的指數(shù)形式

FLOOR(數(shù)值表達(dá)式)

返回小于或等于數(shù)值表達(dá)式值的最大整數(shù)

LOG(浮點(diǎn)表達(dá)式)

返回?cái)?shù)值的自然對(duì)數(shù)值

LOG10(浮點(diǎn)表達(dá)式)

返回以10為底的浮點(diǎn)數(shù)的對(duì)數(shù)

PI()

返回π的值3.1415926535897931POWER(數(shù)值表達(dá)式,冪)

返回?cái)?shù)值表達(dá)式值的指定次冪的值

RADIANS(數(shù)值表達(dá)式)

將度轉(zhuǎn)換為弧度,DEGREES的反函數(shù)

RAND([整型表達(dá)式])

返回0-1之間的隨機(jī)數(shù)ROUND(數(shù)值表達(dá)式,數(shù)值表達(dá)式)

將數(shù)值表達(dá)式四舍五入為整型表達(dá)式所給定的精度

SIGN(數(shù)值表達(dá)式)

符號(hào)函數(shù)，正數(shù)返回1，負(fù)數(shù)返回-1，0返回0SIN((浮點(diǎn)表達(dá)式))

返回浮點(diǎn)表達(dá)式的三角正弦值(單位為弧度)

SQUARE(浮點(diǎn)表達(dá)式)

返回浮點(diǎn)表達(dá)式的平方

SQRT(浮點(diǎn)表達(dá)式)

返回浮點(diǎn)表達(dá)式的平方根

TAN(浮點(diǎn)表達(dá)式)

返回浮點(diǎn)表達(dá)式的正切值(單位為弧度)[練習(xí)]分別輸出2的3次冪、-1的絕對(duì)值、2的平方、3.14的整數(shù)部分。在查詢分析器中運(yùn)行如下命令：PRINTPOWER(2,3)PRINTABS(-1)PRINTSQUARE(2)PRINTFLOOR(3.14)GO運(yùn)行結(jié)果為：8、1、4、3。102.字符串函數(shù)：實(shí)現(xiàn)對(duì)字符串?dāng)?shù)據(jù)的分析、查找、轉(zhuǎn)化等。ASCII：返回字符的ASCII整數(shù)值。CHAR:返回給定ASCII整數(shù)值對(duì)應(yīng)的字符。參數(shù)0-255之間的整數(shù)。SPACE：返回指定個(gè)數(shù)的空格。STR：將給定的浮點(diǎn)數(shù)轉(zhuǎn)化成字符串。Length缺省值為10，decimal缺省值為0。Str（float[,length][,decimal]]）Str(123.4567)=‘123’Str(123.4567,5)=‘123’Str(123.4567,6,1)=‘123.4’11LOWER：將字符串中的字符全部轉(zhuǎn)化成小寫。UPPER：將字符串中的字符全部轉(zhuǎn)化成大寫。LTRIM:刪除字符串前面的所有空格。RTRIM：刪除字符串后面的所有空格。LEFT：返回字符串從左邊開始的指定個(gè)數(shù)的字符。LEFT（string，integer）RIGHT：返回字符串從右邊開始的指定個(gè)數(shù)的字符。LEN:返回字符串中字符個(gè)數(shù)。12SUBSTRING：從字符串指定位置開始，返回指定個(gè)數(shù)的字符。substring('asdfggg',3,4)='dfgg',REPLACE：對(duì)字符串中的指定內(nèi)容進(jìn)行替換。replacereplace('Mydogisalovely

dog.','dog','cat')='Mycatisalovelycat.'13STUFF：將字符串插入另一字符串。STUFF（string1，start，length，string2），它在第一個(gè)字符串string1中從開始位置start處刪除指定長(zhǎng)度length的字符，然后將第二個(gè)字符串string2插入第一個(gè)字符串的start位置處。stuff('asdfg',2,4,'dd')='add‘REVERSE：將字符串逆向輸出。reverse(454676)=676454reverse('你是狼')='狼是你'14CHARINDEX（string1，string2[，start]）在string2中從start指定的字符開始搜索string1并返回其起始位置，如果沒有找到則返回0。charindex('as','aaassasas',2)=3charindex('as','asddasddas',2)=5【練習(xí)】使用LEN函數(shù)顯示字符串常量以及字符串變量的長(zhǎng)度。提示：LEN函數(shù)用于計(jì)算字符串中所包含的字符個(gè)數(shù)，如果字符串尾部含有空格則會(huì)被忽略。在查詢分析器中運(yùn)行如下命令：PRINTLEN('computerdepartment')DECLARE@s1char(10)DECLARE@s2char(10)SET@s1='welcome'SET@s2='hellow'PRINTLEN(@s1)PRINTLEN(@s2)運(yùn)行結(jié)果為：19，7，6。【練習(xí)】給定一個(gè)字符串’haveagoodtime’，判斷字符’g’在整個(gè)字符串中的位置。提示：CHARINDEX函數(shù)用于在規(guī)定字符串中對(duì)子字符串進(jìn)行查詢。當(dāng)返回值大于零時(shí)表示子字符串的起始位置，返回值為0時(shí)表明沒有查詢結(jié)果。在查詢分析器中運(yùn)行如下命令：DECLARE@sCHAR(20)SET@s='haveagoodtime'PRINTCHARINDEX('g',@s)運(yùn)行結(jié)果為：8163.日期時(shí)間函數(shù)：實(shí)現(xiàn)對(duì)日期時(shí)間類型數(shù)據(jù)的各種操作。GETDATE:返回SQLServer服務(wù)器上的當(dāng)前系統(tǒng)日期與時(shí)間DATEADD（interval，number，date）：以interval指定的方式對(duì)date加上number之后的日期。返回datetime或smalldatetime類型的數(shù)據(jù)。如果number為負(fù)數(shù)，則會(huì)將date減去指定時(shí)間。dateadd(month,4,'2012/4/24')DATEDIFF（interval，date1，date2）：以interval指定的方式，返回date2與date1之間的差值。DATEdiff(day,GETDATE(),'2014/4/24');17Datename（interval，date）:返回日期date中interval指定部分所對(duì)應(yīng)的字符串。DATEPART（interval，date）:返回日期date中interval指定部分所對(duì)應(yīng)的整數(shù)值。SELECTDATEPART(month,GETDATE());GOSELECTDATEname(year,getdate());DATEPART和DATENAME函數(shù)以整數(shù)或ASCII字符串形式生成datetime值的指定部分（例如年、季度、天或小時(shí)）。由于smalldatetime只能精確到分鐘，因此在這兩個(gè)函數(shù)中使用smalldatetime值時(shí)，返回的秒和毫秒部分總是為零。含義值縮寫取值范圍年yearyy1753-9999季度quarterqq1-4月monthmm1-12一年中的第幾日Dayofyeardy1-366日daydd1-31一周中的第幾日weekdaydw1-7一年中的第幾周weekwk0-51小時(shí)/分鐘/秒/毫秒Hour/minute/second/millisecondHh/mi/ss/ms0-23/0-59/0-59/0-999【練習(xí)】獲取系統(tǒng)時(shí)間信息，在查詢分析器中分別顯示系統(tǒng)時(shí)間中的年份、月份以及日期。提示：GETDATE函數(shù)用于返回當(dāng)前的系統(tǒng)時(shí)間，YEAR，MONTH，DAY函數(shù)可以取得時(shí)間中的年、月、日的數(shù)值。在查詢分析器中運(yùn)行如下命令：DECLARE@xtsjDATETIMESET@xtsj=GETDATE()SELECTYEAR(@xtsj)SELECTMONTH(@xtsj)SELECTDAY(@xtsj)【練習(xí)】通過對(duì)“學(xué)生基本信息表”中的“出生日期”字段進(jìn)行計(jì)算，查詢每一位學(xué)生的年齡。提示：利用DATEDIFF函數(shù)可以計(jì)算出兩個(gè)日期之間的距離，該函數(shù)含有三個(gè)參數(shù)，第一個(gè)參數(shù)通?？梢詾閥y（年）或mm（月）或dd（日），若第一個(gè)參數(shù)為yy時(shí)，該函數(shù)返回值為后兩個(gè)日期參數(shù)之間年份的差距。在本例中，當(dāng)前的日期由GETDATE函數(shù)獲得后，計(jì)算與每一位學(xué)生的出生日期之間年份的差距，從而獲得學(xué)生的年齡并在查詢結(jié)果中顯示。在查詢分析器中運(yùn)行如下命令：SELECTsnoas學(xué)號(hào),snameas姓名,DATEDIFF(yy,birthday,GETDATE())as年齡fromstudent214.集合函數(shù)用于計(jì)算SELECT語句查詢結(jié)果行的統(tǒng)計(jì)值，在查詢結(jié)果集中生成匯總值。如：

COUNT、

MAX、

MIN、

SUM等。225.系統(tǒng)函數(shù)用于獲取有關(guān)計(jì)算機(jī)系統(tǒng)、用戶、數(shù)據(jù)庫和數(shù)據(jù)庫對(duì)象等當(dāng)前運(yùn)行環(huán)境的系統(tǒng)信息。系統(tǒng)函數(shù)。①CAST和CONVERT函數(shù)。CAST、CONVERT這兩個(gè)函數(shù)都是實(shí)現(xiàn)數(shù)據(jù)類型的轉(zhuǎn)換，但CONVERT的功能更強(qiáng)一些。常用的類型轉(zhuǎn)換有以下幾種情況：日期型→字符型：如將datetime或smalldatetime數(shù)據(jù)轉(zhuǎn)換為字符數(shù)據(jù)（如nchar、nvarchar、char、varchar、nchar或nvarchar類型）。字符型→日期型：如將字符數(shù)據(jù)轉(zhuǎn)換為datetime或smalldatetime數(shù)據(jù)。數(shù)值型→字符型：如將float、real、money或smallmoney數(shù)據(jù)轉(zhuǎn)換為字符數(shù)據(jù)。語法格式：CAST(expressionASdata_type[(length)])CONVERT(data_type[(length)],expression[,style])【練習(xí)】查詢學(xué)生基本信息表中的學(xué)號(hào)、姓名、年齡，并且將這三個(gè)字段通過“+”運(yùn)算符進(jìn)行連結(jié)顯示在查詢結(jié)果中。提示：由于計(jì)算學(xué)生年齡的結(jié)果為整數(shù)，而學(xué)號(hào)、姓名均為字符串類型的值，因而在運(yùn)算之前,需要將年齡的計(jì)算結(jié)果轉(zhuǎn)化為字符串，即CAST(DATEDIFF(yy,birthday,GETDATE())ASCHAR(2))。在查詢分析器中運(yùn)行如下命令：SELECTsno+sname+'年齡：'+CAST(DATEDIFF(yy,birthday,GETDATE())ASCHAR(2))FROMstudent24T-SQL的常用函數(shù)6.

用戶自定義函數(shù)：自定義的函數(shù)包含標(biāo)量值函數(shù)、內(nèi)聯(lián)表值函數(shù)和多語句表值函數(shù)三種。標(biāo)量值函數(shù)。該函數(shù)返回單個(gè)數(shù)據(jù)值，數(shù)據(jù)類型是在RETURNS子句中指定的。內(nèi)聯(lián)表值函數(shù)。該函數(shù)指定table作為返回類型，可以通過內(nèi)聯(lián)表值函數(shù)實(shí)現(xiàn)參數(shù)化視圖功能。多語句表值函數(shù)。該函數(shù)是視圖與存儲(chǔ)過程的集合應(yīng)用，返回table類型。25【例5-1】定義一個(gè)函數(shù)stu_score，求出并返回學(xué)生成績(jī)的70%。createfunctionstu_score(@ttasnumeric(5,2))returnsnumeric(5,2)asbeginreturn(@tt*0.7)endGO26標(biāo)量值函數(shù)可以出現(xiàn)在select子句的查詢列表中。selectgrade,dbo.stu_score(grade)as'成績(jī)的%'fromstudent_teacher_coursego275.2分析“教學(xué)管理系統(tǒng)”中的數(shù)據(jù)查詢?nèi)蝿?wù)：分析“教學(xué)管理系統(tǒng)”中的數(shù)據(jù)查詢需求。28“教學(xué)管理系統(tǒng)”中的數(shù)據(jù)查詢1.從查詢類型角度分析：?jiǎn)伪聿樵?。針?duì)沒有外鍵的基本表。多表查詢。通過外鍵列將分屬于多個(gè)表的信息進(jìn)行連接查詢或嵌套查詢后放在一起顯示。數(shù)據(jù)的匯總統(tǒng)計(jì)查詢。通過單表查詢或多表的關(guān)聯(lián)查詢對(duì)相應(yīng)的數(shù)據(jù)行匯總和統(tǒng)計(jì)。2.從系統(tǒng)的管理應(yīng)用角度分析：

學(xué)生基本信息查詢。 學(xué)生成績(jī)查詢。 學(xué)生選課信息查詢。295.3簡(jiǎn)單查詢?nèi)蝿?wù)：掌握SELECT語句的基本語法，按查詢需求，應(yīng)用WHERE條件及各關(guān)鍵字實(shí)現(xiàn)對(duì)“高職院校教學(xué)管理系統(tǒng)”的各類基本數(shù)據(jù)查詢。

6.3.1認(rèn)識(shí)SELECT語句6.3.2最基本的SELECT語句6.3.3數(shù)據(jù)匯總及空值查詢6.3.4使用WHERE進(jìn)行條件查詢6.3.5使用ORDERBY排序查詢結(jié)果6.3.6使用GROUPBY分組統(tǒng)計(jì)6.3.7使用COMPUTEBY顯示子集明細(xì)6.3.8使用查詢結(jié)果創(chuàng)建新表6.3.9使用UNION聯(lián)合多個(gè)查詢305.3.1認(rèn)識(shí)SELECT語句使用T-SQL的SELECT語句從一個(gè)或多個(gè)表或視圖中，對(duì)存在于數(shù)據(jù)庫中的數(shù)據(jù)按照特定的組合、條件表達(dá)式和次序進(jìn)行查詢，并可對(duì)數(shù)據(jù)進(jìn)行篩選、分組、統(tǒng)計(jì)、排序等處理?；菊Z法:SELECT[ALL|DISTINCT|TOPn|TOPnPERCENT]select_list[INTOnew_table_name]FROMtable_list[WHEREsearch_conditions][GROUPBYgroup_by_list][HAVINGsearch_conditions][ORDERBYorder_list[ASC|DESC]][COMPUTEaggregate_functions[BYcompute_by_list]]各子句說明

SELECT：關(guān)鍵字，表示從數(shù)據(jù)庫中查詢數(shù)據(jù)。

ALL|DISTINCT|TOPn|TOPnPERCENT：關(guān)鍵字，對(duì)查詢結(jié)果集的數(shù)據(jù)行進(jìn)行控制。

select_list：需要在結(jié)果集中顯示的列，是由逗號(hào)分隔的表字段或表達(dá)式列表。

INTOnew_table_name：將查詢結(jié)果集存放到一個(gè)新創(chuàng)建的表中。

FROMtable_list：指定要查詢的表或視圖的數(shù)據(jù)源列表。

WHEREsearch_conditions：指定查詢時(shí)的篩選條件。

GROUPBYgroup_by_list：按照指定的列對(duì)結(jié)果集進(jìn)行分組。HAVINGsearch_conditions：指定用于分組后的篩選條件。

ORDERBYorder_list[ASC|DESC]：指定結(jié)果集需要依據(jù)哪些列來進(jìn)行排序后顯示。

COMPUTEaggregate_functions[BYcompute_by_list]：對(duì)查詢結(jié)果集中的所有記錄進(jìn)行匯總統(tǒng)計(jì)，并顯示所有參加匯總統(tǒng)計(jì)的詳細(xì)子集，即統(tǒng)計(jì)明細(xì)。315.3.2最基本的SELECT語句最簡(jiǎn)單的查詢是不附加任何查詢條件，即只由必須的SELECT和FROM子句組成，而不使用WHERE、GROUPBY、HAVING、ORDERBY等其他可選的子句?；菊Z法如下：SELECT*|column_name[,....n]FROMtable_name;例：從表student_info中查詢出全體學(xué)生的學(xué)號(hào)、姓名、性別和班級(jí)等基本信息。代碼如下：SELECTsno,sname,ssex,classnoFROMstudentGO需要說明的是：在SELECT關(guān)鍵字后出現(xiàn)的列名順序決定了查詢結(jié)果集中列的順序。當(dāng)SELECT關(guān)鍵字后用“*”，則顯示數(shù)據(jù)源中所有列，且顯示列序?yàn)閿?shù)據(jù)表中列的定義順序。通過命名別名的方式改變結(jié)果集中列的標(biāo)題，有“'列標(biāo)題名'=列名”和“列名AS'列標(biāo)題'”兩種方式。32【例5-2】從表student中查詢出全體學(xué)生的詳細(xì)信息。SELECT*FROMstudentGO【例5-3】從表student中查詢出全體學(xué)生的學(xué)號(hào)、姓名、性別和班級(jí)等基本信息。SELECTsno,sname,ssex,classnoFROMstudentGO【例5-4】從表student中查詢出所有學(xué)生的姓名、性別、學(xué)號(hào)、班級(jí)等基本信息，并用相應(yīng)的中文標(biāo)題顯示。SELECT'姓名'=sname,'性別'=ssex,snoAS'學(xué)號(hào)',classnoAS'班級(jí)'FROMstudentGO5.3.2最基本的SELECT語句335.3.3數(shù)據(jù)匯總及空值查詢通過使用SQL的集合函數(shù)可以對(duì)數(shù)據(jù)進(jìn)行各種統(tǒng)計(jì)，大大簡(jiǎn)化了數(shù)據(jù)的查詢?！纠?-5】

從表student中查詢出全體學(xué)生的姓名和年齡。代碼如下：SELECTsnameAS'姓名',YEAR(GETDATE())-YEAR(birthday)AS'年齡'FROMstudentGO

提示：YEAR(GETDATE())-YEAR(birthday)是一個(gè)計(jì)算列，取得系統(tǒng)日期中的年份，減去學(xué)生出生日期列中的年份，即年齡。34【例5-6】從student中統(tǒng)計(jì)當(dāng)前學(xué)生人數(shù)。Selectcount(*)Fromstudent35【例5-7】從表student中查詢出所有學(xué)生的民族信息。代碼如下：圖5-6學(xué)生的民族信息SELECTDISTINCTnationalityFROMstudentGO【例5-8】從表student_teacher_course中查詢出最高分和最低分。代碼如下：SELECTMIN(grade),MAX(grade)FROMstudent_teacher_courseGO【例5-9】從表teacher中查詢出教師及其職稱，如果職稱為空，則默認(rèn)顯示為“講師”。代碼如下：SELECTtname,ISNULL(professionaltitle,'講師')FROMteacherGO5.3.3數(shù)據(jù)匯總及空值查詢

提示：教師職稱的值為NULL時(shí)，通過ISNULL()函數(shù)進(jìn)行了特殊處理。365.3.4使用WHERE進(jìn)行條件查詢用戶可以使用WHERE子語句實(shí)現(xiàn)從表中篩選出滿足特定條件的一部分記錄。WHERE子語句中常用的查詢條件見書表5-7。1．基于比較運(yùn)算的WHERE子句【例5-10】從表student中查詢出2年前入學(xué)的所有學(xué)生的詳細(xì)信息。代碼如下：SELECT*FROMstudentWHEREentertime<=DATEADD(year,-2,GETDATE())GO

提示：①進(jìn)行條件比較的可以是表中的單個(gè)列，也可以是計(jì)算列或函數(shù)。②字符串類型或日期時(shí)間類型的列，使用比較條件時(shí)，要用單引號(hào)引起來。③

WHERE子句中使用的字段也可以出現(xiàn)在SELECT關(guān)鍵字中。374．基于LIKE關(guān)鍵字的WHERE子句當(dāng)只知道查詢對(duì)象的部分值且不知道準(zhǔn)確形式的時(shí)候，就要使用LIKE運(yùn)算符和通配符相匹配的模糊查詢，見書表5-8和5-9?！纠?-11】從表student中查詢出姓劉的學(xué)生信息。代碼如下：SELECT*FROMstudentWHEREsnameLIKE‘劉%’GO5.3.4使用WHERE進(jìn)行條件查詢385．基于空值判斷的WHERE子句空值用NULL表示，它是一個(gè)符號(hào)，既不等于0，也不是空格，不能進(jìn)行算術(shù)運(yùn)算?！纠?-12】從student中查詢沒有登記家庭住址的學(xué)生信息。Select*FromstudentWherehomeisnullgo5.3.4使用WHERE進(jìn)行條件查詢

建議：不要使用邏輯運(yùn)算符默認(rèn)的優(yōu)先級(jí)順序，而是采用括號(hào)來明確需要的執(zhí)行順序，以增強(qiáng)程序的可讀性。396．多個(gè)條件的組合查詢【例5-13】從表student中查詢出家住“濟(jì)南”或名字為“王小平”的少數(shù)民族學(xué)生的信息。代碼如下：SELECT*FROMstudentWHERE(nationality!='漢')AND(homeLIKE'%濟(jì)南%'ORsnameLIKE'王小平')GO405.3.5使用ORDERBY排序查詢結(jié)果使用ORDERBY指定對(duì)最終的查詢結(jié)果按照一個(gè)或多個(gè)列進(jìn)行升序（ASC）或降序（DESC）排列后顯示。默認(rèn)為升序，即ASC，可以缺省。語法為：ORDERBY{列名

[ASC|DESC]}[,…n]【例5-14】從表student_teacher_course中查詢成績(jī)最高的前3條記錄。代碼如下：SELECTTOP3*FROMstudent_teacher_courseORDERBYgradeDESCGO提示：本例中，TOP關(guān)鍵字可指定返回查詢結(jié)果中的前n行，或前一個(gè)百分?jǐn)?shù)的行數(shù)。415.3.6使用GROUPBY分組統(tǒng)計(jì)1．不含條件的分組統(tǒng)計(jì)GROUPBY子句與集合函數(shù)一起，將查詢結(jié)果按某一列或多列的取值進(jìn)行分組，列的取值相同的行為一組，對(duì)每一組進(jìn)行統(tǒng)計(jì)?；菊Z法為：GROUPBY列名[,…][HAVING分組條件表達(dá)式]【例5-15】統(tǒng)計(jì)各個(gè)班的學(xué)生人數(shù)。SELECTclassno,count(sno)AS人數(shù)FROMstudentGROUPBYclassnoGO42【例5-15】統(tǒng)計(jì)表major_course中各個(gè)專業(yè)的“專業(yè)核心”課門數(shù)。代碼如下：SELECTmajorno,count(cno)AS門數(shù)FROMmajor_courseWHEREtype='專業(yè)核心'GROUPBYmajornoGO435.3.6使用GROUPBY分組統(tǒng)計(jì)2．使用HAVING子句設(shè)置分組條件“HAVING分組條件表達(dá)式”用來對(duì)分組后生成的中間結(jié)果集進(jìn)行條件篩選?！纠?-16】對(duì)表student_teacher_course中各學(xué)生的考試成績(jī)進(jìn)行統(tǒng)計(jì)，查詢出平均分及格的學(xué)生的學(xué)號(hào)和平均成績(jī)。代碼如下：SELECTsno,AVG(grade)AS'平均成績(jī)'FROMstudent_teacher_courseGROUPBYsnoHAVINGAVG(grade)>=60GO44使用COMPUTEBY子句既能瀏覽到各個(gè)組子集的詳細(xì)數(shù)據(jù)，又能顯示這些詳細(xì)數(shù)據(jù)的統(tǒng)計(jì)結(jié)果?；菊Z法：COMPUTE集合函數(shù)[,…][BY列名

[,…]]5.3.7使用COMPUTEBY顯示子集明細(xì)DISTINCT關(guān)鍵字不允許同集合函數(shù)一起使用。

COMPUTEBY子句中可以使用多個(gè)集合函數(shù)，且與SELECTINTO子句不能放在一起使用。

COMPUTEBY子句中不出現(xiàn)“BY列名”，表示對(duì)全部信息進(jìn)行分組，即是對(duì)一個(gè)表數(shù)據(jù)的總計(jì)。

COMPUTEBY子句中的列必須在SELECT子句后的選擇列表中?！癇Y列名”指定的列必須與ORDERBY子句指定的列相同，或是它的子集，且必須具有相同的從左到右的順序【例5-17】從表student_teacher_course中查詢所有學(xué)生的考試成績(jī)，并統(tǒng)計(jì)每門課程的總成績(jī)和平均成績(jī)。代碼如下：SELECT*FROMstudent_teacher_courseORDERBYcnoCOMPUTESUM(grade),AVG(grade)BYcnoGO455.3.8使用查詢結(jié)果創(chuàng)建新表SELECTINTO子句的作用是在查詢的基礎(chǔ)上創(chuàng)建新表，并將查詢結(jié)果插入新表。表中的列名、列順序、列數(shù)據(jù)類型以及表中的數(shù)據(jù)行都來自于查詢結(jié)果集?？梢灾付▌?chuàng)建臨時(shí)表或永久表。若創(chuàng)建臨時(shí)表，則必須在表名前加“#”（局部臨時(shí)表）或“##”（全局臨時(shí)表），臨時(shí)表將創(chuàng)建在tempdb數(shù)據(jù)庫中。如果沒有指定“#”或“##”，則在當(dāng)前數(shù)據(jù)庫中創(chuàng)建永久表。465.3.8使用查詢結(jié)果創(chuàng)建新表【例5-18】創(chuàng)建表名為temp_ssc的臨時(shí)表，該臨時(shí)表用于存放考試成績(jī)90分以上的學(xué)生的學(xué)號(hào)、姓名、課程號(hào)和考試成績(jī)。之后從該臨時(shí)表中查詢信息。代碼如下：SELECTa.sno,a.sname,o,b.gradeINTO#temp_sscFROMstudentajoinstudent_teacher_coursebONa.sno=b.snoWHEREb.grade>90GOSELECT*FROM#temp_sscGO475.3.9使用UNION聯(lián)合多個(gè)查詢UNION運(yùn)算符將兩個(gè)或多個(gè)查詢結(jié)果組合為一個(gè)結(jié)果集，該結(jié)果集包含查詢的全部行。語法如下：select_statementUNION[ALL]selectstatement[UNION[ALL]selectstatement][…n]【例5-33】查詢選修了課程1或者選修了課程2的學(xué)生。

SELECTSnoFROMStudent_courseWHERECno='1'UNIONSELECTSnoFROMStudent_courseWHERECno='2';

使用UNION組合查詢時(shí)需要注意以下幾點(diǎn)：（1）UNION聯(lián)合的所有查詢語句必須具有相同的列，且數(shù)據(jù)類型必須兼容。（2）合并后結(jié)果集的列名使用第一個(gè)SELECT語句中的列標(biāo)題。若要定義列標(biāo)題，必須在第一個(gè)SELECT語句中定義。若要對(duì)聯(lián)合查詢結(jié)果排序，也必須使用第一個(gè)SELECT語句中的列。（3）在包括多個(gè)查詢的UNION語句中，其執(zhí)行順序是自左至右，而使用括號(hào)可以改變這一執(zhí)行順序。（4）如果要將合并后的結(jié)果集保存到一個(gè)新表中，INTO語句必須放在第一個(gè)SELECT語句中。（5）只可以在最后一條SELECT語句中使用ORDERBY和COMPUTE子句。485.4連接查詢

任務(wù)：理解內(nèi)連接、自連接、外連接和交叉連接的含義，應(yīng)用內(nèi)連接，外連接和交叉連接對(duì)“高職院校教學(xué)管理系統(tǒng)”進(jìn)行數(shù)據(jù)查詢。5.4.1使用內(nèi)連接查詢數(shù)據(jù)5.4.2使用外連接查詢數(shù)據(jù)5.4.3使用交叉連接查詢數(shù)據(jù)49連接查詢連接查詢是涉及到兩個(gè)或多個(gè)表之間的關(guān)聯(lián)查詢，連接關(guān)系的建立有以下兩種方式。①在WHERE子句中建立連接關(guān)系，基本語法如下：

FROMjoin_tablejoin_typejoin_tableWHEREjoin_condition②在FROM子句中建立連接關(guān)系，基本語法如下：

FROMjoin_tablejoin_typejoin_tableONjoin_condition其中：①join_table指定參與連接操作的表。②join_type指定連接類型，包括內(nèi)連接、外連接和交叉連接三種。③join_condition指定多個(gè)連接表之間的連接條件505.4.1使用內(nèi)連接查詢數(shù)據(jù)

內(nèi)連接是通過INNERJOIN關(guān)鍵字或者JOIN關(guān)鍵字把多表進(jìn)行連接。內(nèi)連接將多個(gè)表中都滿足條件的記錄聯(lián)合成一條新記錄。如果一個(gè)表中的某條記錄按照連接條件在另外一個(gè)表中沒有找到任何匹配的記錄，則這條記錄就不會(huì)出現(xiàn)在結(jié)果集中。內(nèi)連接查詢的基本語法如下：

SELECT<select_list>FROM<table1>[INNER]JOIN<table2>ON｛<table1>.<column_name><比較運(yùn)算符><table2>.<column_name>｝[,…]515.4.1使用內(nèi)連接查詢數(shù)據(jù)

在使用內(nèi)連接進(jìn)行數(shù)據(jù)查詢的時(shí)候，需要注意以下幾點(diǎn)：（1）可以在FROM子句指定表的同時(shí)定義表的別名，格式為“表名[AS]表別名”。（2）當(dāng)一個(gè)表與它自身進(jìn)行連接操作時(shí)，稱為表的自連接（3）如果多個(gè)表中有相同名稱的字段時(shí)，在SELECT后面指定字段名時(shí)必須加上表名（或表別名）作為前綴，即用“表名.列名”或“表別名.列名”表示。（4）如果在FROM子句中為表定義了別名后，則在SELECT關(guān)鍵字后以及WHERE、ORDERBY等各個(gè)子句中，都必須使用“表別名.列名”格式，而不允許再使用“表名.列名”格式。（5）進(jìn)行連接查詢的多表之間必然存在主鍵和外鍵關(guān)系，只要主外鍵關(guān)系列出，就可以確定連接條件。Course1表課程號(hào)Cno課程名Cname先行課Pcno1234567數(shù)據(jù)庫數(shù)學(xué)信息系統(tǒng)操作系統(tǒng)數(shù)據(jù)結(jié)構(gòu)數(shù)據(jù)處理PASCAL語言51676查詢每一門課的間接先修課（即先修課的先修課）SELECTa.Cno,b.pcnoFROMCourse1a,Course1bWHEREa.pcno=o【例5-19】在表student_teacher_course中，查詢與學(xué)號(hào)為2010010001的同學(xué)選修了相同課程的其他同學(xué)的成績(jī)信息。代碼如下：SELECTDISTINCTa.sno,o,a.gradeFROMstudent_teacher_coursea,student_teacher_coursebWHEREo=oANDa.sno!='2010010001'ANDb.sno='2010010001'GOSELECTDISTINCTa.sno,o,a.gradeFROMstudent_teacher_courseajoinstudent_teacher_o=owherea.sno!='2010010001'ANDb.sno='2010010001'GO5556SELECTDISTINCTa.sno,o,a.gradeFROM

student_teacher_courseajoinstudent_teacher_o=oanda.sno!='2010010001'andb.sno='2010010001'GO57【例5-20】查詢各個(gè)學(xué)生的學(xué)號(hào)、姓名、課號(hào)和成績(jī)。Selecta.sno,a.sname,cno,b.gradeFromstudentainnerjoinstudent_teacher_coursebOna.sno=b.snogo【例5-21】查詢各個(gè)選課學(xué)生的姓名、所選課名和成績(jī)。selectsname,cname,gradefromstudentainnerjoinstudent_teacher_coursebona.sno=o=o595.4.2使用外連接查詢數(shù)據(jù)（1）左外連接(leftouterjoin)的結(jié)果集返回了左表中的所有匹配行。（2）右外連接(rightouterjoin)和左外連接相反，它將返回右表的所有匹配行。（3）全外連接(fullouterjoin)則返回左表和右表中的所有匹配行。

【例5-22】左外連接表student和student_teacher_course，查詢各個(gè)學(xué)生的學(xué)號(hào)、姓名、課號(hào)和成績(jī)。Selecta.sno,a.sname,cno,b.gradeFromstudentaleftouterjoinstudent_teacher_coursebOna.sno=b.snogo615.4.3使用交叉連接查詢數(shù)據(jù)交叉連接（CROSSJOIN）指在進(jìn)行多表查詢時(shí)不指定任何連接條件，它返回連接表中所有數(shù)據(jù)行的笛卡爾積，結(jié)果集中的數(shù)據(jù)行數(shù)為第一個(gè)表中符合查詢條件的數(shù)據(jù)行乘以第二個(gè)表中符合查詢條件的數(shù)據(jù)行。625.5嵌套查詢

任務(wù)：理解嵌套查詢的概念及語法，使用比較運(yùn)算符及IN或NOTIN、EXISTS或NOTEXISTS謂詞對(duì)“高職院校教學(xué)管理系統(tǒng)”進(jìn)行數(shù)據(jù)查詢。

5.5.1使用比較運(yùn)算符連接的嵌套查詢5.5.2使用IN謂詞連接的嵌套查詢5.5.3使用EXISTS謂詞連接的嵌套查詢63

嵌套查詢

在SELECT語句的WHERE或HAVING子句中嵌套另一個(gè)SELECT語句的查詢稱為嵌套查詢，又稱子查詢。嵌套查詢還可以嵌套在INSERT、UPDATE、DELETE語句或其他嵌套查詢中。嵌套查詢的執(zhí)行流程是，首先執(zhí)行內(nèi)部嵌套的子查詢語句，查詢的結(jié)果并不被顯示，而是傳遞給外層的SELECT語句作為該查詢語句的查詢條件使用。在不影響理解的情況下，也可以稱子查詢形成的結(jié)果作為父查詢的條件。嵌套查詢可以最多嵌套32層。645.5.1使用比較運(yùn)算符連接的嵌套查詢使用比較運(yùn)算符連接SELECT子查詢時(shí)，子查詢只能是單列、單值子查詢語句?！纠?-23】查詢與“李麗”在同一個(gè)班級(jí)學(xué)習(xí)的學(xué)生。此查詢要求可以分步來完成：①確定“李麗”所在班號(hào)SELECTclassnoFROMStudentWHERESname='張華';查詢結(jié)果：1001②查找所有5班的學(xué)生。