第4章數(shù)據(jù)庫的基本應用(4.2-4.3)

任課人：cws第4章

數(shù)據(jù)庫的基本應用章節(jié)內(nèi)容4.1管理表4.2數(shù)據(jù)查詢4.3數(shù)據(jù)查詢?nèi)蝿諏崿F(xiàn)4.4數(shù)據(jù)更新4.5數(shù)據(jù)更新任務實現(xiàn)4.6課堂實踐4.2數(shù)據(jù)查詢4.2.1單表無條件查詢4.2.2單表有條件查詢4.2.3聚集函數(shù)的使用4.2.4分組與排序4.2.5多表連接查詢4.2.6嵌套查詢4.2.7集合查詢4.2數(shù)據(jù)查詢學生成績管理系統(tǒng)中，肯定設計到包括學生基本信息瀏覽和查詢、成績查詢、各種數(shù)據(jù)的統(tǒng)計等內(nèi)容，如：這些操作應該如何實現(xiàn)呢？4.2數(shù)據(jù)查詢數(shù)據(jù)查詢是數(shù)據(jù)庫中最常見的操作。SQL語言是通過SELECT語句來實現(xiàn)數(shù)據(jù)查詢的；基本語法如下：4.2.1單表無條件查詢1.語法格式SELECT[ALL|DISTINCT][TOPN[PERCENT]]<選項>[AS<顯示列名>][,<選項>[AS<顯示列名>][,...]]FROM<表名|視圖名>2.說明(1)

ALL：表示輸出所有記錄，包括重復記錄。默認值為ALL。

DISTINCT：表示在查詢結果中去掉重復值。(2)

TOPN：返回查詢結果集中的前N行。

加[PERCENT]：返回查詢結果集中的前N%行。N的取值范圍是0～100(3)選項：查詢結果集中的輸出列?？蔀樽侄蚊?、表達式或函數(shù)。用“*”表示表中的所有字段。若選項為表達式或函數(shù)，輸出的列名系統(tǒng)自動給出，不是原字段名，故用AS重命名。(4)顯示列名：在輸出結果中，設置選項顯示的列名。用引號定界或不定界。(5)表名：要查詢的表。表不需打開，到當前路徑下尋找表所對應的文件。4.2.1單表無條件查詢1)查詢指定列【例4-6】查詢?nèi)w學生的學號和姓名?！纠?-7】查詢?nèi)w學生的姓名、學號、所在系。SELECTsno,snameFROMstudentSELECTsname,sno,sdeptFROMstudent4.2.1單表無條件查詢1)查詢指定列【例4-8】查詢選修了課程的學生學號(本例是對表4-12成績表sc的查詢）SELECTDISTINCTsnoFROMsc4.2.1單表無條件查詢2)查詢?nèi)苛小纠?-9】查詢?nèi)w學生的詳細記錄?！纠?-10】輸出學生表中的前10條記錄。SELECT*FROMstudentSELECTTOP10*FROMstudent思考：若是查詢前10%的記錄呢？4.2.1單表無條件查詢3)查詢經(jīng)過計算的列【例4-11】查詢?nèi)w學生的姓名及其年齡SELECTsname,YEAR(GETDATE())-YEAR(sbirthday)AS年齡FROMstudent4.2.1單表無條件查詢3)查詢經(jīng)過計算的列【例4-12】查詢?nèi)w學生的姓名、出生年份和所在系，要求用小寫字母表示所有系名，同時為姓名列指定別名為NAME，出生年份所在列指定別名為年份，系別所在列指定別名為系別。SELECTsnameNAME,'出生年份:'AS

生日,年份=YEAR(sbirthday),LOWER(sdept)系別FROMstudent注意：指定列名的3種方法，教材有誤！4.2.1單表無條件查詢3)查詢經(jīng)過計算的列【例4-13】將sc表中的學生成績增加20%后輸出。SELECTsno,cno,degree*1.2as成績FROMSC4.2.1單表無條件查詢4.查詢結果的輸出Transact-SQL提供了一個擴展特性，允許用戶使用SELECT語句查詢得到的結果記錄來創(chuàng)建一個新的數(shù)據(jù)表，創(chuàng)建新表使用INTO子句。INTO子句不能單獨使用，它包含在SELECT語句中。一般用于創(chuàng)建一個臨時表。INTO子句的語法格式如下：INTO<新表名>4.2.1單表無條件查詢4.查詢結果的輸出【例4-14】使用INTO子句創(chuàng)建一個新表，存放student表中的姓名和系別兩列。SELECTsname,sdept

INTOstudtempFROMStudent4.2.2單表有條件查詢1.語法格式SELECT[ALL|DISTINCT]<選項>[AS<顯示列名>][,<選項>[AS<顯示列名>][,...]]FROM<表名|視圖名>WHERE<條件表達式>4.2.2單表有條件查詢2.WHERE條件中的運算符4.2.2單表有條件查詢1)比較運算符使用比較運算符實現(xiàn)對查詢條件進行限定，其語法格式如下：WHERE

表達式1比較運算符表達式2【例4-15】查詢所有男生的信息。SELECT*FROMstudentWHEREssex='男'4.2.2單表有條件查詢1)比較運算符使用比較運算符實現(xiàn)對查詢條件進行限定，其語法格式如下：WHERE

表達式1比較運算符表達式2【例4-16】查詢所有成績大于80分的學生的學號和成績。SELECTsnoAS'學號',degree'成績'FROMscWHEREdegree>804.2.2單表有條件查詢1)比較運算符【例4-17】查詢所有男生的學號、姓名、系別及出生日期?！纠?-18】查詢計算機系全體學生的名單。SELECTsno,sname,sdept,sbirthFROMstudentWHEREssex='男'SELECTsnameFROMstudentWHEREsdept='計算機系'4.2.2單表有條件查詢1)比較運算符【例4-19】查詢考試成績不及格的學生的學號。SELECTDISTINCTsnoFROMscWHEREdegree<604.2.2單表有條件查詢2)邏輯運算符WHERE子句中可以使用邏輯運算符AND、OR和NOT，這3個邏輯運算符可以混合使用。其語法格式如下：WHERENOT邏輯表達式|邏輯表達式1邏輯運算符邏輯表達式4.2.2單表有條件查詢2)邏輯運算符【例4-20】查詢計算機系女生的信息。【例4-21】查詢成績在90分以上或不及格的學生學號和課號信息。SELECT*FROMstudentWHEREsdept='計算機系'ANDssex='女'SELECTsno,cnoFROMscWHEREdegree>90ordegree<604.2.2單表有條件查詢2)邏輯運算符【例4-22】查詢非計算機系的學生信息。SELECT*FROMstudentWHERENOTsdept='計算機系'或：SELECT*FROMstudentWHEREsdept<>'計算機系’4.2.2單表有條件查詢課堂練習：4.2.2單表有條件查詢3)范圍運算符(BETWEENAND)在WHERE子句中使用BETWEEN關鍵字查找在某一范圍內(nèi)的數(shù)據(jù)，也可以使用NOTBETWEEN關鍵字查找不在某一范圍內(nèi)的數(shù)據(jù)。其語法格式如下：WHERE

表達式[NOT]BETWEEN

初始值AND

終止值4.2.2單表有條件查詢3)范圍運算符(BETWEENAND)【例4-23】查詢成績在60～70分之間的學生學號及成績。SELECTsno,degreeFROMscWHEREdegreeBETWEEN60AND70條件表達式的另一種表示方法是：degree>=60ANDdegree<=704.2.2單表有條件查詢4)字符匹配符(LIKE)在WHERE子句中使用字符匹配符LIKE或NOTLIKE可以把表達式與字符串進行比較，從而實現(xiàn)對字符串的模糊查詢。其語法格式如下：WHERE

表達式[NOT]LIKE'字符串'[ESCAPE'換碼字符']通配符：%和_ESCAPE：字符串中本身含有%和_時，對通配符進行轉(zhuǎn)義。4.2.2單表有條件查詢4)字符匹配符(LIKE)通配符說明：4.2.2單表有條件查詢4)字符匹配符(LIKE)【例4-24】查詢所有姓李的學生的個人信息【例4-25】查詢生源地不是山東省的所有學生信息。SELECT*FROMstudentWHEREsnameLIKE'李%'SELECT*FROMstudentWHEREsaddressNOTLIKE'%山東省%'4.2.2單表有條件查詢4)字符匹配符(LIKE)【例4-26】查詢名字中第2個字為“陽”字的學生的姓名和學號?！纠?-27】查詢學號為“20050101”的學生姓名和性別。SELECTsname,snoFROMstudentWHEREsnameLIKE'_陽%‘SELECTsname,ssexFROMstudentWHEREsnoLIKE'20080322'4.2.2單表有條件查詢4)字符匹配符(LIKE)【例4-28】查詢DB_Design課程的課程號SELECTcnoFROMcourseWHEREcnameLIKE'DB\_Design'ESCAPE'\’4.2.2單表有條件查詢5)列表運算符在WHERE子句中，如果需要確定表達式的取值是否屬于某一列表值之一時，就可以使用關鍵字IN或NOTIN來限定查詢條件。其語法格式如下：WHERE

表達式[NOT]IN值列表4.2.2單表有條件查詢5)列表運算符【例4-29】查詢信息系、軟件系和計算機系學生的姓名和性別。SELECTsname,ssexFROMstudentWHEREsdeptIN('計算機系','軟件系','信息系')4.2.2單表有條件查詢6)涉及空值的查詢當數(shù)據(jù)表中的值為NULL時，可以使用ISNULL關鍵字的WHERE子句進行查詢，反之要查詢數(shù)據(jù)表的值不為NULL時，可以使用ISNOTNULL關鍵字?；菊Z法格式為：WHERE

字段IS[NOT]NULL4.2.2單表有條件查詢6)涉及空值的查詢【例4-30】某些學生選修課程后沒有參加考試，所以有選修記錄，但沒有考試成績。查詢?nèi)鄙俪煽兊膶W生的學號和相應的課程號?！纠?-31】查詢所有有成績的學生學號和課程號。SELECTsno,cnoFROMscWHEREdegreeISNULLSELECTsno,cnoFROMscWHEREdegreeISNOTNULL4.2.3聚集函數(shù)的使用SQLServer的聚集函數(shù)是綜合信息的統(tǒng)計函數(shù)，也稱為聚合函數(shù)或集函數(shù)，包括計數(shù)、求最大值、求最小值、求平均值和求和等。聚集函數(shù)可作為列標識符出現(xiàn)在SELECT子句的目標列或HAVING子句的條件中。4.2.3聚集函數(shù)的使用SQLServer的聚集函數(shù)在SQL查詢語句中，如果有GROUPBY子句，則語句中的函數(shù)為分組統(tǒng)計函數(shù)；否則，語句中的函數(shù)為全部結果集的統(tǒng)計函數(shù)。4.2.2單表有條件查詢【例4-32】查詢學生總數(shù)?！纠?-33】查詢選修了課程的學生人數(shù)。SELECTCOUNT(*)FROMstudentSELECTCOUNT(DISTINCTsno)FROMsc4.2.2單表有條件查詢【例4-34】計算C01號課程的學生平均成績。【例4-35】查詢選修了C01號課程的學生最高分和最低分。SELECTAVG(degree)FROMscWHEREcno='C01'SELECTMAX(degree)最高分,MIN(degree)最低分FROMscWHEREcno='C01'4.2.2單表有條件查詢【例4-36】查詢學號為“20050101”的學生的總成績及平均成績?！纠?-37】查詢有考試成績的學生人數(shù)。SELECTSUM(degree)AS總成績,AVG(degree)AS平均成績

FROMscWHEREsno='20050101‘SELECTCOUNT(DISTINCTsno)FROMscWHEREdegreeISNOTNULL4.2.4分組與排序1.對查詢結果集進行分組使用GROUPBY子句可以將查詢結果按照某一列或多列數(shù)據(jù)值進行分類，換句話說，就是對查詢結果的信息進行歸納，以匯總相關數(shù)據(jù)。其語法格式如下:[GROUPBY列名清單][HAVING

條件表達式]

GROUPBY子句把查詢結果集中的各行按列名清單進行分組，在這些列上，對應值都相同的記錄分在同一組。若無HAVING子句，則各組分別輸出；若有HAVING子句，只有符合HAVING條件的組才輸出4.2.4分組與排序【例4-38】統(tǒng)計各系學生數(shù)?！纠?-39】統(tǒng)計student表中男、女學生人數(shù)SELECTsdept,COUNT(*)FROMstudentGROUPBYsdeptSELECTssex,COUNT(*)FROMstudentGROUPBYssex4.2.4分組與排序【例4-40】統(tǒng)計各系男、女生人數(shù)?！纠?-41】統(tǒng)計各系女生人數(shù)。SELECTsdept，ssex，COUNT(*)FROMstudentGROUPBYsdept,ssexSELECTsdept,COUNT(*)FROMstudentWHEREssex='女'GROUPBYsdeptSELECTsdept,COUNT(*)FROMstudentGROUPBYsdept,ssexHAVINGssex=‘女’4.2.4分組與排序【例4-42】查詢選修了3門以上課程的學生學號。SELECTsnoFROMscGROUPBYsnoHAVINGCOUNT(*)>4.2.4分組與排序2.對查詢結果集進行排序用戶可以利用ORDERBY子句對查詢結果按照一個或多個字段進行升序(ASC)或降序(DESC)排序，默認值為升序。語句語法為：[ORDERBY<列名1>[ASC|DESC][,<列名2>[ASC|DESC][,…]4.2.4分組與排序2.對查詢結果集進行排序【例4-43】查詢選修了C03號課程的學生的學號及其成績，查詢結果按分數(shù)的降序排列。SELECTsno,degreeFROMscWHEREcno='C03'ORDERBYdegreeDESC4.2.4分組與排序2.對查詢結果集進行排序【例4-44】查詢?nèi)w學生情況，查詢結果按所在系升序排列，同一系中的學生按出生日期降序排列。SELECT*FROMstudentORDERBYsdeptASC,sbirthDESC4.2.5多表連接查詢多表連接查詢指查詢同時涉及兩個或兩個以上的表；分為：交叉連接、內(nèi)連接、自連接和外連接。4.2.5多表連接查詢廣義笛卡兒積：兩個分別為n目(即關系具有n個屬性)和m目的關系R和S的廣義笛卡兒積是一個(n+m)列的元組的集合。記作：R×S=｛

│trRtsS｝R×S元組的前n列是關系R的一個元組，后m列是關系S的一個元組。若R有k1個元組，S有k2個元組，則關系R和關系S的廣義笛卡兒積有kl×k2個元組。4.2.5多表連接查詢廣義笛卡兒積：

R×SR.AR.BR.CS.AS.BS.Ca1b1c1a1b2c2a1b1c1a1b3c2a1b1c1a2b2c1a1b2c2a1b2c2a1b2c2a1b3c2a1b2c2a2b2c1a2b2c1a1b2c2a2b2c1a1b3c2a2b2c1a2b2c1ABCABCa1b1c1a1b2c2a1b2c2a1b3c2a2b2c1a2b2c14.2.5多表連接查詢1.交叉連接交叉連接又稱笛卡兒連接，是指兩個表之間做笛卡兒積操作，得到結果集的行數(shù)是兩個表的行數(shù)的乘積。命令的一般格式如下:SELECT[ALL|DISTINCT][別名.]<選項1>[AS<顯示列名>][,[別名.]<選項2>[AS<顯示列名>]...]FROM<表名1>[別名1],<表名2>[別名2]需要連接查詢的表名在FROM子句中指定，表名之間用英文逗號隔開；如果表名指定了別名，在該命令中，都必須用別名代替表名。4.2.5多表連接查詢1.交叉連接【例4-45】成績表(sc)和課程名稱表(course)進行交叉連接。SELECTA.*,B.*FROMcourseA,scB4.2.5多表連接查詢2.內(nèi)連接內(nèi)連接命令的一般格式如下：SELECT[ALL|DISTINCT][別名.]<選項1>[AS<顯示列名>][,[別名.]<選項2>[AS<顯示列名>][,...]]FROM<表名1>[別名1],<表名2>[別名2][,…]WHERE<連接條件表達式>[AND<條件表達式>]或者為：SELECT[ALL|DISTINCT][別名.]<選項1>[AS<顯示列名>][,[別名.]<選項2>[AS<顯示列名>][,...]]FROM<表名1>[別名1]INNERJOIN<表名2>[別名2]ON<連接條件表達式>[WHERE<條件表達式>]4.2.5多表連接查詢2.內(nèi)連接連接條件表達式的一般格式為：[<表名1>]<別名1.列名><比較運算符><表名2><別名2.列名>比較運算符：使用等號“=”：等值連接使用不等號：不等值連接4.2.5多表連接查詢2.內(nèi)連接【例4-46】查詢每個學生及其選修課的情況學生的基本情況存放在student表中，選課情況存放在sc表中，所以查詢過程涉及上述兩個表。這兩個表是通過公共字段sno實現(xiàn)內(nèi)連接的。SELECTA.*,B.*FROMstudentA,scBWHEREA.sno=B.sno4.2.5多表連接查詢自然連接：若在等值連接中把目標列中的重復字段去掉，則稱為自然連接?！纠?-47】用自然連接完成查詢:查詢每個學生及其選修課的情況。SELECTstudent.sno,sname,ssex,sbirth,sdept,cno,degreeFROMstudent,scWHEREstudent.sno=sc.sno4.2.5多表連接查詢【例4-48】輸出所有女學生的學號、姓名、課號及成績。SELECTA.sno,sname,cno,degreeFROMstudentA,scBWHEREA.sno=B.snoANDssex='女’SELECTA.sno,sname,cno,degreeFROMstudentAINNERJOINscBONA.sno=B.snoWHEREssex='女’4.2.5多表連接查詢【例4-49】輸出計算機系學生的學號、姓名、課程號及成績。SELECTA.sno,sname,cno,degreeFROMstudentA,scBWHEREA.sno=B.snoANDsdept=‘CS‘SELECTA.sno,sname,cno,degreeFROMstudentAINNERJOINscBONA.sno=B.snoWHEREsdept=‘CS’4.2.5多表連接查詢3.自連接:將同一個表的不同行連接起來在自連接中，必須為表指定兩個別名，使之在邏輯上成為兩張表。自連接的命令的一般格式如下:SELECT[ALL|DISTINCT][別名.]<選項1>[AS<顯示列名>][,[別名.]<選項2>[AS<顯示列名>][,...]]FROM<表名1>[別名1],<表名1>[別名2][,…]WHERE<連接條件表達式>[AND<條件表達式>]4.2.5多表連接查詢3.自連接:【例4-50】查詢同時選修了C01和C04課程的學生學號?！纠?-51】查詢與劉晨在同一個系學習的學生的姓名和所在系。SELECTsnoFROMstudentA,studentBWHEREA.sno=B.snoANDA.cno='C01'ANDB.cno='C04'SELECTB.sname,B.sdeptFROMstudentA,studentBWHEREA.sdept=B.sdeptANDA.sname='劉晨'ANDB.sname!='劉晨'4.2.5多表連接查詢4.外連接在自然連接中，只有在兩個表中匹配的行才能在結果集中出現(xiàn)。而在外連接中可以只限制一個表，而對另外一個表不加限制(所有的行都出現(xiàn)在結果集中)。外連接分為左外連接、右外連接和全外連接。4.2.5多表連接查詢4.外連接外連接命令的一般格式如下:SELECT[ALL|DISTINCT][別名.]<選項1>[AS<顯示列名>][,[別名.]<選項2>[AS<顯示列名>][,...]]FROM<表名1>LEFT|RIGHT|FULL[OUTER]JOIN<表名2>ON<表名1.列1>=<表名2.列2>4.2.5多表連接查詢4.外連接【例4-52】利用左外連接查詢改寫例4-46(查詢每個學生及其選修課的情況)。SELECTstudent.sno,sname,ssex,sbirth,sdept,cno,degreeFROMstudentLEFTJOINscONstudent.sno=sc.sno4.2.6嵌套查詢在SQL語言中，一個SELECT—FROM—WHERE語句稱為一個查詢塊。將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING子句的條件中稱為嵌套查詢或子查詢。例如：SELECTsnameFROMstudentWHEREsnoIN(SELECTsnoFROMscWHEREcno='C02')4.2.6嵌套查詢注意問題：SQL語言允許多層嵌套查詢；子查詢的SQL語句必須用括號定界；子查詢不能使用ORDERBY子句，ORDERBY子句只能對最終查詢結果排序；嵌套查詢的求解方法是由內(nèi)向外處理；子查詢一般分為兩種：嵌套子查詢和相關子查詢。4.2.6嵌套查詢1.嵌套子查詢（不相關子查詢）嵌套子查詢的執(zhí)行不依賴于外部嵌套。嵌套子查詢的執(zhí)行過程為：首先執(zhí)行子查詢，子查詢得到的結果集不被顯示出來，而是傳給外部查詢，作為外部查詢的條件使用，然后執(zhí)行外部查詢，并顯示查詢結果。子查詢可以多層嵌套。嵌套子查詢一般也分為兩種：子查詢返回單個值和子查詢返回一個值列表。4.2.6嵌套查詢1）返回單個值【例4-53】查詢所有年齡大于平均年齡的學生姓名。SELECTsnameFROMstudentWHEREyear(getdate())-year(sbirthday)>(SELECTAVG(year(getdate())-year(sbirthday))FROMstudent)4.2.6嵌套查詢1）返回單個值【例4-54】查詢與劉晨在同一個系學習的學生。SELECTsno,sname,sdeptFROMstudentWHEREsdept=(SELECTsdeptFROMstudentWHEREsname='劉晨')4.2.6嵌套查詢2)返回一個值列表（1）使用IN操作符的嵌套查詢?！纠?-55】用IN操作符改寫例4-54——查詢與劉晨在同一個系學習的學生。SELECTsno,sname,sdeptFROMstudentWHEREsdeptIN(SELECTsdeptFROMstudentWHEREsname='劉晨')4.2.6嵌套查詢2)返回一個值列表（1）使用IN操作符的嵌套查詢?！纠?-56】查詢沒有選修數(shù)學的學生學號和姓名。SELECTsno,snameFROMstudentWHEREsnoNOTIN(SELECTsnoFROMscWHEREcnoIN(SELECTcnoFROMcourseWHEREcname='數(shù)學'))4.2.6嵌套查詢(2)帶有ANY或ALL操作符的子查詢。ANY和ALL操作符在使用時必須和比較運算符一起使用。其格式為：<字段><比較符>[ANY|ALL]<子查詢>。4.2.6嵌套查詢(2)帶有ANY或ALL操作符的子查詢。4.2.6嵌套查詢(2)帶有ANY或ALL操作符的子查詢?！纠?-57】查詢其他系中比計算機系某一學生年齡小的學生姓名和年齡。SELECTsname,sageFROMstudentWHEREsage<ANY(SELECTsageFROMstudentWHEREsdept='計算機系')ANDsdept<>‘計算機系’//該句為父查詢中的一個條件4.2.6嵌套查詢(2)帶有ANY或ALL操作符的子查詢。【例4-58】查詢其他系中比計算機系學生年齡都小的學生。SELECT*FROMstudentWHEREsage<ALL(SELECTsageFROMstudentWHEREsdept='計算機系')ANDsdept<>'計算機系'4.2.6嵌套查詢(2)帶有ANY或ALL操作符的子查詢?！纠?-58】查詢其他系中比計算機系學生年齡都小的學生。一般聚集函數(shù)實現(xiàn)的子查詢效率高于ANY或ALL查詢。SELECT*FROMstudentWHEREsage<(SELECTMIN(sage)FROMstudentWHEREsdept='計算機系')ANDsdept<>'計算機系'4.2.6嵌套查詢2.相關子查詢(CorrelatedSubquery)在相關子查詢中，子查詢的執(zhí)行依賴于外部查詢，即子查詢的查詢條件依賴于外部查詢的某個屬性值。相關子查詢的子查詢需要重復被執(zhí)行，而嵌套子查詢的子查詢只需被執(zhí)行一次!在相關子查詢中，經(jīng)常要用到EXISTS操作符，EXISTS只產(chǎn)生邏輯真值“true”和邏輯假值“false”,不返回任何實際數(shù)據(jù)。4.2.6嵌套查詢2.相關子查詢(CorrelatedSubquery)【例4-59】查詢所有選修了C01號課程的學生姓名。SELECTsnameFROMstudentWHEREEXISTS(SELECT*FROMscWHEREsno=student.snoANDcno='C01')4.2.6嵌套查詢2.相關子查詢(Corre