SQL高級(jí)查詢和視圖課件

第五章高級(jí)查詢第五章高級(jí)查詢回顧指出下列語(yǔ)句的錯(cuò)誤：CREATETABLEbank(userNameVARCHAR(10),balanceMONEY)INSERTINTObank(cardNo,userName,balance)VALUES('張三',500)INSERTINTObank(cardNo,userName,balance)VALUES('李四',700)DECLAREmymoneyINT(4)mymoney=0SELECTmymoney=balanceFROMbank建表語(yǔ)句后必須添加GO標(biāo)志DECLARE@mymoneyINTSET@mymoney=0WHEREuserName='張三’2回顧指出下列語(yǔ)句的錯(cuò)誤：CREATETABLEbank建回顧IF@mymoney<100print'卡上目前余額不足100，請(qǐng)及時(shí)充值!'print'卡上余額為：'+@mymoneyprint'您的年利息為:'SELECT利息=CASEWHENbalance<100THENbalance*0.01WHENbalance>1000THENbalance*0.20WHENELSEbalance*0.10FROMbankWHEREuserName='張三‘GO多條語(yǔ)句添加BEGIN-END去掉WHEN缺少配對(duì)的END轉(zhuǎn)換:convert(varchar(5),@mymoney)3回顧IF@mymoney<100多條語(yǔ)句添加BEGIN-目標(biāo)掌握模糊查詢掌握聚合函數(shù)掌握分組匯總掌握多表聯(lián)接查詢掌握簡(jiǎn)單子查詢的用法掌握IN子查詢的用法掌握EXISTS子查詢的用法應(yīng)用T-SQL進(jìn)行綜合查詢4目標(biāo)掌握模糊查詢4SELECT語(yǔ)句的語(yǔ)法形式

SELECT[ALL|DISTINCT]字段名列表[AS標(biāo)題名][INTO[TABLE|CURSOR]新表名]FROM[數(shù)據(jù)庫(kù)名1.]<表1>[AS<表1的別名>][，[數(shù)據(jù)庫(kù)名2.]<表2>[AS<表2的別名>][,…]][WHERE篩選條件][GROUPBY分組表達(dá)式][HAVING分組條件][ORDERBY排序表達(dá)式[ASC|DESC]]命令格式：功能：對(duì)一個(gè)或多個(gè)表進(jìn)行查詢操作，按其需求將表中的記錄進(jìn)行篩選、分組、排序，從而生成一個(gè)結(jié)果集，也可以將該結(jié)果集生成新表。說(shuō)明：5SELECT語(yǔ)句的語(yǔ)法形式SELECT[ALL|DIST(1)SELECT子句列出所有要求SELECT語(yǔ)句查詢的數(shù)據(jù)項(xiàng),如指定AS，輸出以指定的標(biāo)題名作為字段名輸出。如指定INTO新表名，則將查詢的結(jié)果作為新表保存；(2)FROM子句列出包含所要查詢數(shù)據(jù)的表；(3)WHERE子句提供SQL只查詢某些行的數(shù)據(jù)，也就是執(zhí)行查詢的條件；(4)GROUPBY用以指定匯總查詢，即不是對(duì)每一行產(chǎn)生一個(gè)查詢結(jié)果，而是行記錄進(jìn)行分組，再對(duì)每一組產(chǎn)生一個(gè)匯總結(jié)果；(5)HAVING子句告訴SQL只產(chǎn)生由GROUPBY得到的某些組的結(jié)果；(6)ORDERBY子句將查詢結(jié)果按照一列或多列中的數(shù)據(jù)排序。說(shuō)明：6(1)SELECT子句列出所有要求SELECT語(yǔ)句查詢的數(shù)據(jù)5.1模糊查詢—LIKE查詢時(shí)，字段中的內(nèi)容并不一定與查詢內(nèi)容完全匹配，只要字段中含有這些內(nèi)容SELECTSNameAS姓名FROMStudentsWHERESNameLIKE'張%'姓名張果老張飛張揚(yáng)出去思考：以下的SQL語(yǔ)句：SELECT*FROM數(shù)據(jù)表WHERE編號(hào)LIKE'00[^8]%[A,C]%‘可能會(huì)查詢出的編號(hào)值為()。A、9890ACDB、007_AFFC、008&DCGD、KK8C75.1模糊查詢—LIKE查詢時(shí)，字段中的內(nèi)容并不一定與查詢內(nèi)模糊查詢—ISNULL把某一字段中內(nèi)容為空的記錄查詢出來(lái)SELECTstudent_NameAs姓名,home_addrAS地址FROMStudentWHEREhome_addrISNULL姓名地址李紅NULL左群聲NULL猜一猜：把Student表中某些行的home_addr字段值刪掉后：——使用ISNULL能查詢出來(lái)這些數(shù)據(jù)行嗎？——怎么查詢出這些行來(lái)？8模糊查詢—ISNULL把某一字段中內(nèi)容為空的記錄查詢出來(lái)S模糊查詢—BETWEEN把某一字段中內(nèi)容在特定范圍內(nèi)的記錄查詢出來(lái)SELECTStudent_ID,gradeFROMstudent_courseWHEREcourse_id='dep04_s002'andgradeBETWEEN60AND80Student_IDGradeg994020278g994020468g994020578…………9模糊查詢—BETWEEN把某一字段中內(nèi)容在特定范圍內(nèi)的記錄查模糊查詢—IN把某一字段中內(nèi)容與所列出的查詢內(nèi)容列表匹配的記錄查詢出來(lái)SELECTstudent_NameAs姓名,home_addrAS地址FROMStudentWHEREsubstring(home_addr,1,2)in('長(zhǎng)沙','南京','江蘇')學(xué)員姓名地址李揚(yáng)長(zhǎng)沙于紫電江蘇李青霜南京司馬弓上海……10模糊查詢—IN把某一字段中內(nèi)容與所列出的查詢內(nèi)容列表匹配的記課堂練習(xí)查詢教師表teacher中職稱為教授、副教授的記錄查詢student表中年齡在20到25歲之間的記錄提示：year(getdate)-year(birth)算出年齡。Betweenand查詢student表中姓‘李’的記錄11課堂練習(xí)查詢教師表teacher中職稱為教授、副教授的記錄1問(wèn)題成績(jī)表中存儲(chǔ)了所有學(xué)生的成績(jī)，我想知道：學(xué)生的總成績(jī)、平均成績(jī)、有成績(jī)的學(xué)生總共有多少名怎么辦？12問(wèn)題成績(jī)表中存儲(chǔ)了所有學(xué)生的成績(jī)，我想知道：125.2聚合函數(shù)SUMSELECTSUM(price)FROMbookAVG、MAX、MINSELECTAVG(grade)AS平均成績(jī),MAX

(grade)AS最高分,MIN(grade)AS最低分Fromstudent_courseWHEREgrade>=60COUNTSELECT

COUNT(*)

AS及格人數(shù)Fromstudent_courseWHEREgrade>=60135.2聚合函數(shù)SUMSELECTSUM(price)FRSQL語(yǔ)言支持五個(gè)集合函數(shù)：函數(shù)功能AVG（字段名）求一列數(shù)據(jù)的平均值SUM（字段名）求一列數(shù)據(jù)的和COUNT([DISTINCT]字段名)輸出查詢的行數(shù)COUNT（*）MIN（字段名）給出列中的最小值MAX（字段名）給出列中的最大值14SQL語(yǔ)言支持五個(gè)集合函數(shù)：函數(shù)功能AVG（字段名）求一列集合函數(shù)是作用于一組值的函數(shù)，而不是只作用于一個(gè)值上面的函數(shù)。所有集合函數(shù)可以操作一個(gè)變量，這個(gè)變量可以是列或表達(dá)式(惟一的例外是COUNT函數(shù)的第二種形式：COUNT(*))每個(gè)集合函數(shù)的結(jié)果是個(gè)常量，它顯示在結(jié)果中不同的列上15集合函數(shù)是作用于一組值的函數(shù)，而不是只作用于一個(gè)值上面的函數(shù)問(wèn)題如果不是統(tǒng)計(jì)所有人所有課程的總成績(jī)而是想求每一門課的平均績(jī)或者某個(gè)人的所有課的總成績(jī)?cè)趺崔k？16問(wèn)題如果不是統(tǒng)計(jì)所有人所有課程的總成績(jī)165.3分組匯總命令格式：

GROUPBY分組表達(dá)式HAVING分組條件說(shuō)明：

1)分組表達(dá)式：一般為字段名，對(duì)指定的字段進(jìn)行分組。2）分組條件:對(duì)分組匯總后數(shù)據(jù)進(jìn)入結(jié)果集的篩選條件，一般為集合函數(shù)或常量.175.3分組匯總命令格式：17①不帶HAVING的GROUPBY子句GROUPBY子句將一列或多列定義為一組，按組輸出查詢結(jié)果。GROUPBY子句可以統(tǒng)計(jì)每一門課的平均績(jī)或者某個(gè)人的所有課的總成績(jī)18①不帶HAVING的GROUPBY子句GROUPBY【例】統(tǒng)計(jì)每個(gè)人的平均成績(jī)。

SELECTstudent_id,AVG(grade)AS平均成績(jī)FROMstudent_course

GROUPBYstudent_id19【例】統(tǒng)計(jì)每個(gè)人的平均成績(jī)。SELECTstuden注意事項(xiàng)SQL為每個(gè)定義的組產(chǎn)生一個(gè)列值，每個(gè)組只返回一行，不返回詳細(xì)信息。如果包括WHERE子句，VFP只分組統(tǒng)計(jì)滿足WHERE條件的行。在包含GROUPBY子句的查詢語(yǔ)句中，SELECT子句后的所有字段列表，除集合函數(shù)外，都應(yīng)包含在GROUPBY子句中，否則將出錯(cuò)。如上例中，只能是’student_id’.否則將出錯(cuò)。不要在含有空值的列上使用GROUPBY子句，因?yàn)榭罩祵⒆鳛橐粋€(gè)組來(lái)處理。20注意事項(xiàng)SQL為每個(gè)定義的組產(chǎn)生一個(gè)列值，每個(gè)組只返回一行，分組查詢—思考SELECTstudent_id,course_id,AVG(grade)AS平均成績(jī)FROMstudent_courseGROUPBYstudent_id思考：執(zhí)行以下的T-SQL：——結(jié)果如何？21分組查詢—思考SELECTstudent_id,courHAVING子句定義應(yīng)用到分組行中的條件，HAVING子句對(duì)分組行的意義與WHERE子句對(duì)每個(gè)行的意義是相同的。②帶HAVING的GROUPBY子句22HAVING子句定義應(yīng)用到分組行中的條件，HAVING子句對(duì)【例】查詢平均成績(jī)大于80學(xué)生的學(xué)號(hào)和平均成績(jī)。

SELECTstudent_id,AVG(grade)AS平均成績(jī)FROMstudent_courseGROUPBYstudent_idHAVINGAVG(grade)>80課堂練習(xí)：查詢課程不及格的人數(shù)大于2的課程號(hào)和人數(shù)SELECTcourse_id,count(*)FROMstudent_coursegroupbycourse_idHavingcount(*)>223【例】查詢平均成績(jī)大于80學(xué)生的學(xué)號(hào)和平均成績(jī)。SELE分組查詢—對(duì)比WHEREGROUPBYHAVINGWHERE子句從數(shù)據(jù)源中去掉不符合其搜索條件的數(shù)據(jù)GROUPBY子句搜集數(shù)據(jù)行到各個(gè)組中，統(tǒng)計(jì)函數(shù)為各個(gè)組計(jì)算統(tǒng)計(jì)值HAVING子句去掉不符合其組搜索條件的各組數(shù)據(jù)行24分組查詢—對(duì)比WHEREGROUPBYHAVINGWHER分組查詢—思考SELECT

部門編號(hào),COUNT(*)FROM

員工信息表WHERE

工資>=2000GROUPBY

部門編號(hào)HAVING COUNT(*)>1思考：分析以下T-SQL的含義25分組查詢—思考SELECT 部門編號(hào),COUNT(*)思5.4多表聯(lián)結(jié)查詢—問(wèn)題每次查詢成績(jī)時(shí)顯示的都是學(xué)生的學(xué)號(hào)信息，因?yàn)槌煽?jī)表中只存儲(chǔ)了學(xué)生的學(xué)號(hào)；實(shí)際上最好顯示學(xué)生的姓名，而姓名存儲(chǔ)在student表；如何同時(shí)從這兩個(gè)表中取得數(shù)據(jù)？265.4多表聯(lián)結(jié)查詢—問(wèn)題每次查詢成績(jī)時(shí)顯示的都是學(xué)生的學(xué)號(hào)信多表聯(lián)結(jié)查詢—分類內(nèi)聯(lián)結(jié)(INNERJOIN)外聯(lián)結(jié)——左外聯(lián)結(jié)(LEFTJOIN)——右外聯(lián)結(jié)(RIGHTJOIN)——完整外聯(lián)結(jié)(FULLJOIN)交叉聯(lián)結(jié)(CROSSJOIN)使用多個(gè)表查詢來(lái)產(chǎn)生檢索結(jié)果。27多表聯(lián)結(jié)查詢—分類內(nèi)聯(lián)結(jié)(INNERJOIN)使用多個(gè)表查內(nèi)聯(lián)結(jié)內(nèi)連接(INNERJOIN)：內(nèi)連接返回的結(jié)果集中只包括滿足連接條件的行。例：查詢所有學(xué)生的姓名、課程號(hào)和成績(jī)SELECTS.student_Name,C.Course_ID,C.gradeFrom student_courseASCINNERJOINStudentASSONC.Student_ID=S.student_id28內(nèi)聯(lián)結(jié)內(nèi)連接(INNERJOIN)：內(nèi)連接返回的結(jié)果集中只SELECTS.student_Name,C.Course_ID,C.gradeFrom student_courseASCINNERJOINStudentASSONC.Student_ID=S.student_idStudent_course（成績(jī)表）Student_IDCourse_IDGrade122300100100200297896776300381猜一猜：這樣寫，返回的查詢結(jié)果是一樣的嗎？SELECT S.student_Name,C.Course_ID,C.gradeFrom StudentASSINNERJOIN student_courseASCON C.Student_ID=S.student_id再猜一猜：以下返回多少行？SELECT S.student_Name,C.Course_ID,C.gradeFrom StudentASSINNERJOIN student_courseASCON C.Student_ID<>S.student_id內(nèi)聯(lián)結(jié)-1Stundent(學(xué)生表）Student_Name梅超風(fēng)陳玄風(fēng)陸乘風(fēng)曲靈風(fēng)Student_id1234查詢結(jié)果Student_name梅超風(fēng)陳玄風(fēng)陳玄風(fēng)陸乘風(fēng)Course_IDgrade00100100200297896776陸乘風(fēng)0038129SELECTS.student_Name,C內(nèi)聯(lián)結(jié)-2SELECTS.student_Name,C.Course_ID,C.gradeFrom student_courseASC,StudentASSWhereC.Student_ID=S.student_id基于WHERE子句的內(nèi)連接語(yǔ)法形式30內(nèi)聯(lián)結(jié)-2SELECTS.student_N三表聯(lián)結(jié)方法1：SELECTS.student_Name,L.Course_Name,C.gradeFromstudent_courseASC,StudentASS,courseasLWhereC.Student_ID=S.student_idandC.course_id=L.course_id方法2:SELECTS.student_Name,l.Course_Name,C.gradeFrom student_courseASCinnerjoinStudentASSonC.Student_ID=S.student_idinnerjoincourseasLonC.course_id=L.course_id例：查詢學(xué)生的姓名、課程名、成績(jī)31三表聯(lián)結(jié)方法1：方法2:例：查詢學(xué)生的姓名、課程名、成績(jī)31ScoreStudentsIDCourseIDScore122300100100200297896776300381左外聯(lián)結(jié)StundentsSName梅超風(fēng)陳玄風(fēng)陸乘風(fēng)曲靈風(fēng)SCode1234查詢結(jié)果SName梅超風(fēng)陳玄風(fēng)陳玄風(fēng)陸乘風(fēng)CourseIDScore00100100200297896776陸乘風(fēng)00381曲靈風(fēng)NULLNULLSELECT

S.SName,C.CourseID,C.ScoreFrom

StudentsASSLEFTJOIN ScoreASCON

C.StudentID=S.SCode猜一猜：這樣寫，返回的查詢結(jié)果是一樣的嗎？SELECT

S.SName,C.CourseID,C.ScoreFrom

ScoreASCLEFTJOIN

StudentsASSON

C.StudentID=S.SCode左外連接除了包括滿足連接條件的行外，還包括其中左表的全部行。32ScoreStudentsIDCourseIDScore12右外聯(lián)結(jié)SELECTTitles.Title_id,Titles.Title,Publishers.Pub_nameFROMtitlesRIGHTOUTERJOINPublishersONTitles.Pub_id=Publishers.Pub_id右外連接除了包括滿足連接條件的行外，還包括其中右表的全部行。33右外聯(lián)結(jié)SELECTTitles.Title_id,Ti課堂練習(xí)1、查詢每個(gè)老師的部門名稱和姓名2、查詢部門名稱為‘計(jì)算機(jī)科學(xué)’的所有老師名單。提示：部門名稱在department表selectteacher_name,department_namefromteacherastinnerjoindepartmentasdont.department_id=d.department_idwhered.department_name='計(jì)算機(jī)科學(xué)'34課堂練習(xí)1、查詢每個(gè)老師的部門名稱和姓名selecttea5.5子查詢學(xué)員信息表問(wèn)題：編寫T-SQL語(yǔ)句，查看年齡比“李斯文”大的學(xué)員，要求顯示這些學(xué)員的信息？分析：

第一步：求出“李斯文”的年齡；第二步：利用WHERE語(yǔ)句，篩選年齡比“李斯文”大的學(xué)員；355.5子查詢學(xué)員信息表問(wèn)題：分析：35什么是子查詢實(shí)現(xiàn)方法一：采用T-SQL變量實(shí)現(xiàn)DECLARE@ageINT--定義變量，存放李斯文的年齡SELECT@age=stuAgeFROMstuInfoWHEREstuName=‘李斯文’--求出李斯文的年齡--篩選比李斯文年齡大的學(xué)員SELECT*FROMstuInfoWHEREstuAge>@ageGO36什么是子查詢實(shí)現(xiàn)方法一：采用T-SQL變量實(shí)現(xiàn)DEC什么是子查詢實(shí)現(xiàn)方法二：采用子查詢實(shí)現(xiàn)SELECT*FROMstuInfoWHEREstuAge>(SELECTstuAgeFROMstuInfowherestuName='李斯文')GO子查詢子查詢?cè)赪HERE語(yǔ)句中的一般用法：

SELECT…FROM<表名>WHERE字段1<運(yùn)算符>(子查詢)外面的查詢稱為父查詢，括號(hào)中嵌入的查詢稱為子查詢UPDATE、INSERT、DELETE一起使用，語(yǔ)法類似于SELECT語(yǔ)句

將子查詢和比較運(yùn)算符聯(lián)合使用，必須保證子查詢返回的值不能多于一個(gè)37什么是子查詢實(shí)現(xiàn)方法二：采用子查詢實(shí)現(xiàn)SELECT*F使用子查詢替換表連接3-1問(wèn)題：查詢筆試剛好通過(guò)（60分）的學(xué)員名單。學(xué)員信息表和成績(jī)表38使用子查詢替換表連接3-1問(wèn)題：查詢筆試剛好通過(guò)（60分）的使用子查詢替換表連接3-2實(shí)現(xiàn)方法一：采用表連接SELECTstuNameFROMstuInfo

INNERJOINstuMarksONstuInfo.stuNo=stuMarks.stuNoWHEREwrittenExam=60GO內(nèi)連接（等值連接）39使用子查詢替換表連接3-2實(shí)現(xiàn)方法一：采用表連接SELEC使用子查詢替換表連接3-3實(shí)現(xiàn)方法二：采用子查詢

SELECTstuNameFROMstuInfoWHEREstuNo=(SELECTstuNoFROMstuMarksWHEREwrittenExam=60)GO子查詢一般來(lái)說(shuō)，表連接都可以用子查詢替換，但有的子查詢卻不能用表連接替換子查詢比較靈活、方便，常作為增刪改查的篩選條件，適合于操縱一個(gè)表的數(shù)據(jù)表連接更適合于查看多表的數(shù)據(jù)40使用子查詢替換表連接3-3實(shí)現(xiàn)方法二：采用子查詢SELECIN子查詢4-1問(wèn)題：查詢筆試剛好通過(guò)的學(xué)員名單。如何解決？41IN子查詢4-1問(wèn)題：查詢筆試剛好通過(guò)的學(xué)員名單。如何解決IN子查詢4-2解決方法：采用IN子查詢SELECTstuNameFROMstuInfoWHEREstuNoIN(SELECTstuNoFROMstuMarksWHEREwrittenExam=60)GO將＝號(hào)改為ININ后面的子查詢可以返回多條記錄常用IN替換等于（＝）的比較子查詢42IN子查詢4-2解決方法：采用IN子查詢SELECTIN子查詢4-3問(wèn)題：查詢參加考試的學(xué)員名單學(xué)員信息表和成績(jī)表（重抓本圖）分析：判斷一個(gè)學(xué)員是否參加考試其實(shí)很簡(jiǎn)單，只需要查看該學(xué)員對(duì)應(yīng)的學(xué)號(hào)是否在考試成績(jī)表stuMarks中出現(xiàn)即可

43IN子查詢4-3問(wèn)題：查詢參加考試的學(xué)員名單學(xué)員信息表和IN子查詢4-4/*--采用IN子查詢參加考試的學(xué)員名單--*/SELECTstuNameFROMstuInfoWHEREstuNoIN(SELECTstuNoFROMstuMarks)GO演示：使用IN子查詢參考語(yǔ)句44IN子查詢4-4/*--采用IN子查詢參加考試的學(xué)員名單-NOTIN子查詢問(wèn)題：查詢未參加考試的學(xué)員名單分析：加上否定的NOT即可45NOTIN子查詢問(wèn)題：查詢未參加考試的學(xué)員名單分析：加上查詢未參加數(shù)據(jù)庫(kù)開(kāi)發(fā)技術(shù)考試的名單分析：1、在course表中課程名稱為數(shù)據(jù)庫(kù)技術(shù)開(kāi)發(fā)的course_idselectcourse_idfromcoursewherecourse_name='數(shù)據(jù)庫(kù)開(kāi)發(fā)技術(shù)'2、在成績(jī)表即student_course表中查詢具有數(shù)據(jù)庫(kù)開(kāi)技術(shù)課程成績(jī)的學(xué)生學(xué)號(hào)selectstudent_idfromstudent_course

wherecourse_id=(selectcourse_idfromcoursewherecourse_name='數(shù)據(jù)庫(kù)開(kāi)發(fā)技術(shù)')3、最后在student表中查詢所有在2步查詢結(jié)果中沒(méi)有的姓名。selectstudent_namefromstudentwherestudent_idnotin(selectstudent_idfromstudent_coursewherecourse_id

=(selectcourse_idfromcoursewherecourse_name='數(shù)據(jù)庫(kù)開(kāi)發(fā)技術(shù)'))46查詢未參加數(shù)據(jù)庫(kù)開(kāi)發(fā)技術(shù)考試的名單分析：46課堂練習(xí)1、用子查詢來(lái)查詢JWGL庫(kù)中部門為計(jì)算機(jī)科學(xué)的教師名單2、查詢林紅所有課程的成績(jī)3、查詢JWGL庫(kù)中數(shù)據(jù)庫(kù)開(kāi)發(fā)技術(shù)課程不及格的名單提示：第3題可以結(jié)合內(nèi)聯(lián)接和子查詢一起完成47課堂練習(xí)1、用子查詢來(lái)查詢JWGL庫(kù)中部門為計(jì)算機(jī)科學(xué)的教師EXISTS子查詢4-1例如：數(shù)據(jù)庫(kù)的存在檢測(cè)IFEXISTS（SELECT*FROMsysDatabasesWHEREname=’stuDB’）DROPDATABASEstuDBCREATEDATABASEstuDB…….—建庫(kù)代碼略48EXISTS子查詢4-1例如：數(shù)據(jù)庫(kù)的存在檢測(cè)IFEXIEXISTS子查詢4-2IFEXISTS（子查詢）語(yǔ)句EXISTS子查詢的語(yǔ)法：如果子查詢的結(jié)果非空，即記錄條數(shù)1條以上，則EXISTS（子查詢）將返回真（true），否則返回假(false)EXISTS也可以作為WHERE語(yǔ)句的子查詢，但一般都能用IN子查詢替換49EXISTS子查詢4-2IFEXISTS（子查詢）EXISTS子查詢4-3問(wèn)題：檢查本次考試，本班如果有人筆試成績(jī)達(dá)到80分以上，則每人提2分；否則，每人允許提5分分析：是否有人筆試成績(jī)達(dá)到80分以上，可以采用EXISTS檢測(cè)50EXISTS子查詢4-3問(wèn)題：分析：50EXISTS子查詢4-4/*--采用EXISTS子查詢，進(jìn)行酌情加分--*/IFEXISTS(SELECT*FROMstuMarksWHEREwrittenExam>80)BEGINprint'本班有人筆試成績(jī)高于80分，每人加2分，加分后的成績(jī)?yōu)椋?UPDATEstuMarksSETwrittenExam=writtenExam+2SELECT*FROMstumarksENDELSEBEGINprint'本班無(wú)人筆試成績(jī)高于80分，每人可以加5分，加分后的成績(jī)：'UPDATEstuMarksSETwrittenExam=writtenExam+5SELECT*FROMstumarksENDGO演示：使用EXISTS子查詢參考語(yǔ)句51EXISTS子查詢4-4/*--采用EXISTS子查詢，進(jìn)NOTEXISTS子查詢2-1問(wèn)題：檢查本次考試，本班如果沒(méi)有一人通過(guò)考試（筆試和機(jī)試成績(jī)都>60分），則試題偏難，每人加3分，否則，每人只加1分分析：沒(méi)有一人通過(guò)考試，即不存在“筆試和機(jī)試成績(jī)都>60分”，可以采用NOTEXISTS檢測(cè)52NOTEXISTS子查詢2-1問(wèn)題：分析：52NOTEXISTS子查詢2-2IFNOTEXISTS(SELECT*FROMstuMarksWHEREwrittenExam>60ANDlabExam>60)BEGINprint'本班無(wú)人通過(guò)考試，試題偏難，每人加3分，加分后的成績(jī)?yōu)椋?UPDATEstuMarksSETwrittenExam=writtenExam+3,labExam=labExam+3SELECT*FROMstuMarksENDELSEBEGINprint'本班考試成績(jī)一般，每人只加1分，加分后的成績(jī)?yōu)椋?UPDATEstuMarksSETwrittenExam=writtenExam+1,labExam=labExam+1SELECT*FROMstuMarksENDGO演示：使用NOTEXISTS子查詢參考語(yǔ)句53NOTEXISTS子查詢2-2IFNOTEXISTS5.6T-SQL語(yǔ)句的綜合應(yīng)用學(xué)員信息表和成績(jī)表應(yīng)到人數(shù)：5人實(shí)到人數(shù)4人，缺考1人545.6T-SQL語(yǔ)句的綜合應(yīng)用學(xué)員信息表和成績(jī)表應(yīng)到人數(shù)：T-SQL語(yǔ)句的綜合應(yīng)用如何實(shí)現(xiàn)？本次考試的缺考情況比較筆試平均分和機(jī)試平均分，較低者進(jìn)行循環(huán)提分，但提分后最高分不能超過(guò)97分。加分后重新統(tǒng)計(jì)通過(guò)情況統(tǒng)計(jì)通過(guò)率55T-SQL語(yǔ)句的綜合應(yīng)用如何實(shí)現(xiàn)？本次考試的缺考情況比較筆T-SQL語(yǔ)句的綜合應(yīng)用1.提示:使用子查詢統(tǒng)計(jì)缺考情況：應(yīng)到人數(shù)：SELECTcount(*)FROMstuInfo實(shí)到人數(shù)：SELECTcount(*)FROMstuMarks2.提取學(xué)員的成績(jī)信息并保存結(jié)果，包括學(xué)員姓名、學(xué)號(hào)、筆試成績(jī)、機(jī)試成績(jī)、是否通過(guò)1)提取的成績(jī)信息包含兩表的數(shù)據(jù)，所以考慮兩表連接，使用左連接（LEFTJOIN）；SELECTstuName…FROMstuInfo

LEFTJOINstuMarks…2)要求新加一列“是否通過(guò)（isPass）”，可采用CASE…END。為了便于后續(xù)的通過(guò)率統(tǒng)計(jì)，通過(guò)則為1，沒(méi)通過(guò)為0SELECT…isPass=CASEWHENwrittenExam>=60……THEN1ELSE0

END……3)要求保存提?。ú樵儯┑慕Y(jié)果，可以使用我們?cè)鴮W(xué)習(xí)過(guò)的SELECT…INTOnewTable語(yǔ)句，生成新表并保存數(shù)據(jù)56T-SQL語(yǔ)句的綜合應(yīng)用1.提示:使用子查詢統(tǒng)計(jì)缺考情況：T-SQL語(yǔ)句的綜合應(yīng)用3.比較筆試平均分和機(jī)試平均分，對(duì)較低者進(jìn)行循環(huán)提分，但提分后最高分不能超過(guò)97分：1)使用IF語(yǔ)句判斷筆試還是機(jī)試偏低，決定對(duì)筆試還是機(jī)試提分；2)使用WHILE循環(huán)給每個(gè)學(xué)員加分，缺考的除外，當(dāng)最高分超過(guò)97分時(shí)退出循環(huán)；3)因?yàn)榻o每位學(xué)員的筆試或機(jī)試提分了，有的學(xué)員可能提分后剛好通過(guò)了，所以需要更新isPass（是否通過(guò)）列。UPDATEnewTableSETisPass=CASEWHENwrittenExam>=60andlabExam>=60THEN1ELSE0

END57T-SQL語(yǔ)句的綜合應(yīng)用3.比較筆試平均分和機(jī)試平均分，對(duì)較T-SQL語(yǔ)句的綜合應(yīng)用4.提分后，統(tǒng)計(jì)學(xué)員的成績(jī)和通過(guò)情況：1）使用別名實(shí)現(xiàn)中文字段名，即SELECT姓名=stuName,學(xué)號(hào)=stuNo…2）如果某個(gè)學(xué)員的成績(jī)?yōu)镹ULL(空)，則替換為”缺考”，否則原樣顯示；3）isPass列中的1替換為是，0替換為否；SELECT……,機(jī)試成績(jī)=CASEWHENlabExamISNULLTHEN'缺考'ELSEconvert(varchar(5),labExam)END,是否通過(guò)=CASEWHENisPass=1THEN'是'ELSE'否'END……58T-SQL語(yǔ)句的綜合應(yīng)用4.提分后，統(tǒng)計(jì)學(xué)員的成績(jī)和通過(guò)情況T-SQL語(yǔ)句的綜合應(yīng)用5.提分后統(tǒng)計(jì)學(xué)員的通過(guò)率情況：1）通過(guò)人數(shù)：因?yàn)橥ㄟ^(guò)用1表示，沒(méi)通過(guò)用0表示，所以isPass列的累加和即是通過(guò)人數(shù)；2）通過(guò)率：同理，isPass列的平均值*100即是通過(guò)率；59T-SQL語(yǔ)句的綜合應(yīng)用5.提分后統(tǒng)計(jì)學(xué)員的通過(guò)率情況：59T-SQL參考語(yǔ)句/*--本次考試的原始數(shù)據(jù)--*/--SELECT*FROMstuInfo--SELECT*FROMstuMarks/*--------------統(tǒng)計(jì)考試缺考情況----------------------*/SELECT應(yīng)到人數(shù)=(SELECTcount(*)FROMstuInfo),--應(yīng)到人數(shù)為子查詢表達(dá)式的別名實(shí)到人數(shù)=(SELECTcount(*)FROMstuMarks),缺考人數(shù)=((SELECTcount(*)FROMstuInfo)-(SELECTcount(*)FROMstuMarks))60T-SQL參考語(yǔ)句/*--本次考試的原始數(shù)據(jù)--*/60T-SQL參考語(yǔ)句/*----統(tǒng)計(jì)考試通過(guò)情況，并將結(jié)果存放在新表newTable中---*/IFEXISTS(SELECT*FROMsysobjectsWHEREname='newTable')DROPTABLEnewTableSELECTstuName,stuInfo.stuNo,writtenExam,labExam,isPass=CASEWHENwrittenExam>=60andlabExam>=60THEN1ELSE0ENDINTOnewTableFROMstuInfoLEFTJOINstuMarksONstuInfo.stuNo=stuMarks.stuNo--SELECT*FROMnewTable--查看統(tǒng)計(jì)結(jié)果，可用于調(diào)試61T-SQL參考語(yǔ)句/*----統(tǒng)計(jì)考試通過(guò)情況，并將結(jié)果存放T-SQL參考語(yǔ)句/*-酌情加分：比較筆試和機(jī)試平均分，決定加哪門---*/DECLARE@avgWrittennumeric(4,1)DECLARE@avgLabnumeric(4,1)SELECT@avgWritten=AVG(writtenExam)FROMnewTableWHEREwrittenExamISNOTNULLSELECT@avgLab=AVG(labExam)FROMnewTableWHERElabExamISNOTNULLIF@avgWritten<@avgLabWHILE(1=1)--循環(huán)給筆試加分，最高分不能超過(guò)97分BEGINUPDATEnewTableSETwrittenExam=writtenExam+1IF(SELECTMAX(writtenExam)FROMnewTable)>=97BREAKENDELSE…略…--循環(huán)給筆試加分，最高分不能超過(guò)97分62T-SQL參考語(yǔ)句/*-酌情加分：比較筆試和機(jī)試平均分，決定T-SQL參考語(yǔ)句--因?yàn)樘岱?，所以需要更新isPass（是否通過(guò)）列的數(shù)據(jù)UPDATEnewTableSETisPass=CASEWHENwrittenExam>=60andlabExam>=60THEN1ELSE0END--SELECT*FROMnewTable--可用于調(diào)試/*--------------顯示考試最終通過(guò)情況----------------*/SELECT姓名=stuName,學(xué)號(hào)=stuNo,筆試成績(jī)=CASEWHENwrittenExamISNULLTHEN'缺考'ELSEconvert(varchar(5),writtenExam)END,機(jī)試成績(jī)=CASEWHENlabExamISNULLTHEN'缺考'ELSEconvert(varchar(5),labExam)END,是否通過(guò)=CASEWHENisPass=1THEN'是'ELSE'否'ENDFROMnewTable63T-SQL參考語(yǔ)句--因?yàn)樘岱郑孕枰耰sPass（T-SQL參考語(yǔ)句/*--顯示通過(guò)率及通過(guò)人數(shù)--*/SELECT總?cè)藬?shù)=count(*),通過(guò)人數(shù)=SUM(isPass),通過(guò)率=(convert(varchar(5),AVG(isPass*100))+'%')FROMnewTable64T-SQL參考語(yǔ)句/*--顯示通過(guò)率及通過(guò)人數(shù)--*/6案例分析2—要求一家銀行發(fā)行了新的信用卡，剛開(kāi)始的時(shí)候推廣得很好，但是逐漸廢卡也越來(lái)越多（卡上的余額少于2元，并且用戶長(zhǎng)時(shí)間不使用該卡），因此銀行在二月份把這些少于2元的卡從都數(shù)據(jù)庫(kù)表中刪除了，但是很快問(wèn)題就來(lái)了，用戶發(fā)現(xiàn)他的卡再也不能使用而投訴，因此只能再把這些卡恢復(fù)。編寫SQL，把CardID為23、30等在子表中沒(méi)有的記錄插入到子表中，插入的數(shù)據(jù)Score項(xiàng)值為2……65案例分析2—要求一家銀行發(fā)行了新的信用卡，剛開(kāi)始的時(shí)候推廣得案例分析2—分析這是一個(gè)數(shù)據(jù)插入的操作，因此要使用INSERT語(yǔ)句來(lái)進(jìn)行這種方式的數(shù)據(jù)插入，不是固定的數(shù)據(jù)項(xiàng)，而是從其它表中篩選數(shù)據(jù)再插入，因此要使用到INSERTINTO…SELECT結(jié)構(gòu)插入的數(shù)據(jù)項(xiàng)是子表中沒(méi)有的，需要找到子表中沒(méi)有的這些數(shù)據(jù)，我們可以把整個(gè)語(yǔ)句的INSERT（前半部分）不考慮，先只考慮SELECT（后半部分），SELECT的任務(wù)就是找出兩個(gè)表中不同的項(xiàng)在前面的聯(lián)結(jié)查詢中，使用INNERJOIN…ON可以找出相同的項(xiàng)，編寫以下T-SQL：SELECTM.CardID,2FROMMINNERJOINSON(S.CardID<>M.CardID)以上把“=”簡(jiǎn)單地改為“<>”，不能找出不同的項(xiàng)，所找到的項(xiàng)比原來(lái)的要多很多，因?yàn)閮蓚€(gè)表之間是進(jìn)行“交叉”對(duì)比的，而不是一一對(duì)比，所以會(huì)對(duì)比出很多“不同”的項(xiàng)來(lái)；因此，這種方法不可行考慮我們?cè)谏厦娴淖笸膺B接查詢，能夠查詢出左表中存在而相關(guān)表不存在的數(shù)據(jù)項(xiàng)最后，子表中不存在的數(shù)據(jù)項(xiàng)，可以再加WHERE條件來(lái)進(jìn)行篩選，S.CardIDISNULL可以判斷其是否存在子表中存在66案例分析2—分析這是一個(gè)數(shù)據(jù)插入的操作，因此要使用INSER案例分析2—T-SQLINSERTINTO S(S.CardID,S.Score)SELECT M.CardID,2FROM MLEFTJOIN SON (S.CardID=M.CardID)WHERE S.CardIDISNULL67案例分析2—T-SQLINSERTINTO S(S.C總結(jié)使用LIKE、BETWEEN、IN關(guān)鍵字，能夠進(jìn)行模糊查詢——條件不明確的查詢聚合函數(shù)能夠?qū)α猩梢粋€(gè)單一的值，對(duì)于分析和統(tǒng)計(jì)通常非常有用分組查詢是針對(duì)表中不同的組，分類統(tǒng)計(jì)和輸出，GROUPBY子句通常會(huì)結(jié)合聚合函數(shù)一起來(lái)使用HAVING子句能夠在分組的基礎(chǔ)上，再次進(jìn)行篩選多個(gè)表之間通常使用聯(lián)結(jié)查詢最常見(jiàn)的聯(lián)結(jié)查詢是內(nèi)聯(lián)結(jié)（INNERJOIN），通常會(huì)在相關(guān)表之間提取引用列的數(shù)據(jù)項(xiàng)68總結(jié)使用LIKE、BETWEEN、IN關(guān)鍵字，能夠進(jìn)行模糊查總結(jié)總結(jié)我們?cè)鴮W(xué)習(xí)過(guò)的查詢，合并多個(gè)表中的數(shù)據(jù)的方法有三種：聯(lián)合(Union)－合并多個(gè)數(shù)據(jù)表中的行子查詢－將一個(gè)查詢包含到另一個(gè)查詢中聯(lián)接－合并多個(gè)數(shù)據(jù)表中的列通過(guò)在子查詢中使用EXISTS子句，可以對(duì)子查詢中的行是否存在進(jìn)行檢查IN子查詢后面可跟隨返回多條記錄的子查詢，用于檢測(cè)某列的值是否在某個(gè)范圍69總結(jié)總結(jié)我們?cè)鴮W(xué)習(xí)過(guò)的查詢，合并多個(gè)表中的數(shù)據(jù)的方法有三作業(yè)1、將所有課堂練習(xí)的SQL代碼寫在實(shí)驗(yàn)報(bào)告上2、認(rèn)真學(xué)習(xí)課件上最后一個(gè)綜合應(yīng)用并進(jìn)行實(shí)驗(yàn)將代碼寫在作業(yè)本上70作業(yè)1、將所有課堂練習(xí)的SQL代碼寫在實(shí)驗(yàn)報(bào)告上70第五章高級(jí)查詢第五章高級(jí)查詢回顧指出下列語(yǔ)句的錯(cuò)誤：CREATETABLEbank(userNameVARCHAR(10),balanceMONEY)INSERTINTObank(cardNo,userName,balance)VALUES('張三',500)INSERTINTObank(cardNo,userName,balance)VALUES('李四',700)DECLAREmymoneyINT(4)mymoney=0SELECTmymoney=balanceFROMbank建表語(yǔ)句后必須添加GO標(biāo)志DECLARE@mymoneyINTSET@mymoney=0WHEREuserName='張三’72回顧指出下列語(yǔ)句的錯(cuò)誤：CREATETABLEbank建回顧IF@mymoney<100print'卡上目前余額不足100，請(qǐng)及時(shí)充值!'print'卡上余額為：'+@mymoneyprint'您的年利息為:'SELECT利息=CASEWHENbalance<100THENbalance*0.01WHENbalance>1000THENbalance*0.20WHENELSEbalance*0.10FROMbankWHEREuserName='張三‘GO多條語(yǔ)句添加BEGIN-END去掉WHEN缺少配對(duì)的END轉(zhuǎn)換:convert(varchar(5),@mymoney)73回顧IF@mymoney<100多條語(yǔ)句添加BEGIN-目標(biāo)掌握模糊查詢掌握聚合函數(shù)掌握分組匯總掌握多表聯(lián)接查詢掌握簡(jiǎn)單子查詢的用法掌握IN子查詢的用法掌握EXISTS子查詢的用法應(yīng)用T-SQL進(jìn)行綜合查詢74目標(biāo)掌握模糊查詢4SELECT語(yǔ)句的語(yǔ)法形式

SELECT[ALL|DISTINCT]字段名列表[AS標(biāo)題名][INTO[TABLE|CURSOR]新表名]FROM[數(shù)據(jù)庫(kù)名1.]<表1>[AS<表1的別名>][，[數(shù)據(jù)庫(kù)名2.]<表2>[AS<表2的別名>][,…]][WHERE篩選條件][GROUPBY分組表達(dá)式][HAVING分組條件][ORDERBY排序表達(dá)式[ASC|DESC]]命令格式：功能：對(duì)一個(gè)或多個(gè)表進(jìn)行查詢操作，按其需求將表中的記錄進(jìn)行篩選、分組、排序，從而生成一個(gè)結(jié)果集，也可以將該結(jié)果集生成新表。說(shuō)明：75SELECT語(yǔ)句的語(yǔ)法形式SELECT[ALL|DIST(1)SELECT子句列出所有要求SELECT語(yǔ)句查詢的數(shù)據(jù)項(xiàng),如指定AS，輸出以指定的標(biāo)題名作為字段名輸出。如指定INTO新表名，則將查詢的結(jié)果作為新表保存；(2)FROM子句列出包含所要查詢數(shù)據(jù)的表；(3)WHERE子句提供SQL只查詢某些行的數(shù)據(jù)，也就是執(zhí)行查詢的條件；(4)GROUPBY用以指定匯總查詢，即不是對(duì)每一行產(chǎn)生一個(gè)查詢結(jié)果，而是行記錄進(jìn)行分組，再對(duì)每一組產(chǎn)生一個(gè)匯總結(jié)果；(5)HAVING子句告訴SQL只產(chǎn)生由GROUPBY得到的某些組的結(jié)果；(6)ORDERBY子句將查詢結(jié)果按照一列或多列中的數(shù)據(jù)排序。說(shuō)明：76(1)SELECT子句列出所有要求SELECT語(yǔ)句查詢的數(shù)據(jù)5.1模糊查詢—LIKE查詢時(shí)，字段中的內(nèi)容并不一定與查詢內(nèi)容完全匹配，只要字段中含有這些內(nèi)容SELECTSNameAS姓名FROMStudentsWHERESNameLIKE'張%'姓名張果老張飛張揚(yáng)出去思考：以下的SQL語(yǔ)句：SELECT*FROM數(shù)據(jù)表WHERE編號(hào)LIKE'00[^8]%[A,C]%‘可能會(huì)查詢出的編號(hào)值為()。A、9890ACDB、007_AFFC、008&DCGD、KK8C775.1模糊查詢—LIKE查詢時(shí)，字段中的內(nèi)容并不一定與查詢內(nèi)模糊查詢—ISNULL把某一字段中內(nèi)容為空的記錄查詢出來(lái)SELECTstudent_NameAs姓名,home_addrAS地址FROMStudentWHEREhome_addrISNULL姓名地址李紅NULL左群聲NULL猜一猜：把Student表中某些行的home_addr字段值刪掉后：——使用ISNULL能查詢出來(lái)這些數(shù)據(jù)行嗎？——怎么查詢出這些行來(lái)？78模糊查詢—ISNULL把某一字段中內(nèi)容為空的記錄查詢出來(lái)S模糊查詢—BETWEEN把某一字段中內(nèi)容在特定范圍內(nèi)的記錄查詢出來(lái)SELECTStudent_ID,gradeFROMstudent_courseWHEREcourse_id='dep04_s002'andgradeBETWEEN60AND80Student_IDGradeg994020278g994020468g994020578…………79模糊查詢—BETWEEN把某一字段中內(nèi)容在特定范圍內(nèi)的記錄查模糊查詢—IN把某一字段中內(nèi)容與所列出的查詢內(nèi)容列表匹配的記錄查詢出來(lái)SELECTstudent_NameAs姓名,home_addrAS地址FROMStudentWHEREsubstring(home_addr,1,2)in('長(zhǎng)沙','南京','江蘇')學(xué)員姓名地址李揚(yáng)長(zhǎng)沙于紫電江蘇李青霜南京司馬弓上?！?0模糊查詢—IN把某一字段中內(nèi)容與所列出的查詢內(nèi)容列表匹配的記課堂練習(xí)查詢教師表teacher中職稱為教授、副教授的記錄查詢student表中年齡在20到25歲之間的記錄提示：year(getdate)-year(birth)算出年齡。Betweenand查詢student表中姓‘李’的記錄81課堂練習(xí)查詢教師表teacher中職稱為教授、副教授的記錄1問(wèn)題成績(jī)表中存儲(chǔ)了所有學(xué)生的成績(jī)，我想知道：學(xué)生的總成績(jī)、平均成績(jī)、有成績(jī)的學(xué)生總共有多少名怎么辦？82問(wèn)題成績(jī)表中存儲(chǔ)了所有學(xué)生的成績(jī)，我想知道：125.2聚合函數(shù)SUMSELECTSUM(price)FROMbookAVG、MAX、MINSELECTAVG(grade)AS平均成績(jī),MAX

(grade)AS最高分,MIN(grade)AS最低分Fromstudent_courseWHEREgrade>=60COUNTSELECT

COUNT(*)

AS及格人數(shù)Fromstudent_courseWHEREgrade>=60835.2聚合函數(shù)SUMSELECTSUM(price)FRSQL語(yǔ)言支持五個(gè)集合函數(shù)：函數(shù)功能AVG（字段名）求一列數(shù)據(jù)的平均值SUM（字段名）求一列數(shù)據(jù)的和COUNT([DISTINCT]字段名)輸出查詢的行數(shù)COUNT（*）MIN（字段名）給出列中的最小值MAX（字段名）給出列中的最大值84SQL語(yǔ)言支持五個(gè)集合函數(shù)：函數(shù)功能AVG（字段名）求一列集合函數(shù)是作用于一組值的函數(shù)，而不是只作用于一個(gè)值上面的函數(shù)。所有集合函數(shù)可以操作一個(gè)變量，這個(gè)變量可以是列或表達(dá)式(惟一的例外是COUNT函數(shù)的第二種形式：COUNT(*))每個(gè)集合函數(shù)的結(jié)果是個(gè)常量，它顯示在結(jié)果中不同的列上85集合函數(shù)是作用于一組值的函數(shù)，而不是只作用于一個(gè)值上面的函數(shù)問(wèn)題如果不是統(tǒng)計(jì)所有人所有課程的總成績(jī)而是想求每一門課的平均績(jī)或者某個(gè)人的所有課的總成績(jī)?cè)趺崔k？86問(wèn)題如果不是統(tǒng)計(jì)所有人所有課程的總成績(jī)165.3分組匯總命令格式：

GROUPBY分組表達(dá)式HAVING分組條件說(shuō)明：

1)分組表達(dá)式：一般為字段名，對(duì)指定的字段進(jìn)行分組。2）分組條件:對(duì)分組匯總后數(shù)據(jù)進(jìn)入結(jié)果集的篩選條件，一般為集合函數(shù)或常量.875.3分組匯總命令格式：17①不帶HAVING的GROUPBY子句GROUPBY子句將一列或多列定義為一組，按組輸出查詢結(jié)果。GROUPBY子句可以統(tǒng)計(jì)每一門課的平均績(jī)或者某個(gè)人的所有課的總成績(jī)88①不帶HAVING的GROUPBY子句GROUPBY【例】統(tǒng)計(jì)每個(gè)人的平均成績(jī)。

SELECTstudent_id,AVG(grade)AS平均成績(jī)FROMstudent_course

GROUPBYstudent_id89【例】統(tǒng)計(jì)每個(gè)人的平均成績(jī)。SELECTstuden注意事項(xiàng)SQL為每個(gè)定義的組產(chǎn)生一個(gè)列值，每個(gè)組只返回一行，不返回詳細(xì)信息。如果包括WHERE子句，VFP只分組統(tǒng)計(jì)滿足WHERE條件的行。在包含GROUPBY子句的查詢語(yǔ)句中，SELECT子句后的所有字段列表，除集合函數(shù)外，都應(yīng)包含在GROUPBY子句中，否則將出錯(cuò)。如上例中，只能是’student_id’.否則將出錯(cuò)。不要在含有空值的列上使用GROUPBY子句，因?yàn)榭罩祵⒆鳛橐粋€(gè)組來(lái)處理。90注意事項(xiàng)SQL為每個(gè)定義的組產(chǎn)生一個(gè)列值，每個(gè)組只返回一行，分組查詢—思考SELECTstudent_id,course_id,AVG(grade)AS平均成績(jī)FROMstudent_courseGROUPBYstudent_id思考：執(zhí)行以下的T-SQL：——結(jié)果如何？91分組查詢—思考SELECTstudent_id,courHAVING子句定義應(yīng)用到分組行中的條件，HAVING子句對(duì)分組行的意義與WHERE子句對(duì)每個(gè)行的意義是相同的。②帶HAVING的GROUPBY子句92HAVING子句定義應(yīng)用到分組行中的條件，HAVING子句對(duì)【例】查詢平均成績(jī)大于80學(xué)生的學(xué)號(hào)和平均成績(jī)。

SELECTstudent_id,AVG(grade)AS平均成績(jī)FROMstudent_courseGROUPBYstudent_idHAVINGAVG(grade)>80課堂練習(xí)：查詢課程不及格的人數(shù)大于2的課程號(hào)和人數(shù)SELECTcourse_id,count(*)FROMstudent_coursegroupbycourse_idHavingcount(*)>293【例】查詢平均成績(jī)大于80學(xué)生的學(xué)號(hào)和平均成績(jī)。SELE分組查詢—對(duì)比WHEREGROUPBYHAVINGWHERE子句從數(shù)據(jù)源中去掉不符合其搜索條件的數(shù)據(jù)GROUPBY子句搜集數(shù)據(jù)行到各個(gè)組中，統(tǒng)計(jì)函數(shù)為各個(gè)組計(jì)算統(tǒng)計(jì)值HAVING子句去掉不符合其組搜索條件的各組數(shù)據(jù)行94分組查詢—對(duì)比WHEREGROUPBYHAVINGWHER分組查詢—思考SELECT

部門編號(hào),COUNT(*)FROM

員工信息表WHERE

工資>=2000GROUPBY

部門編號(hào)HAVING COUNT(*)>1思考：分析以下T-SQL的含義95分組查詢—思考SELECT 部門編號(hào),COUNT(*)思5.4多表聯(lián)結(jié)查詢—問(wèn)題每次查詢成績(jī)時(shí)顯示的都是學(xué)生的學(xué)號(hào)信息，因?yàn)槌煽?jī)表中只存儲(chǔ)了學(xué)生的學(xué)號(hào)；實(shí)際上最好顯示學(xué)生的姓名，而姓名存儲(chǔ)在student表；如何同時(shí)從這兩個(gè)表中取得數(shù)據(jù)？965.4多表聯(lián)結(jié)查詢—問(wèn)題每次查詢成績(jī)時(shí)顯示的都是學(xué)生的學(xué)號(hào)信多表聯(lián)結(jié)查詢—分類內(nèi)聯(lián)結(jié)(INNERJOIN)外聯(lián)結(jié)——左外聯(lián)結(jié)(LEFTJOIN)——右外聯(lián)結(jié)(RIGHTJOIN)——完整外聯(lián)結(jié)(FULLJOIN)交叉聯(lián)結(jié)(CROSSJOIN)使用多個(gè)表查詢來(lái)產(chǎn)生檢索結(jié)果。97多表聯(lián)結(jié)查詢—分類內(nèi)聯(lián)結(jié)(INNERJOIN)使用多個(gè)表查內(nèi)聯(lián)結(jié)內(nèi)連接(INNERJOIN)：內(nèi)連接返回的結(jié)果集中只包括滿足連接條件的行。例：查詢所有學(xué)生的姓名、課程號(hào)和成績(jī)SELECTS.student_Name,C.Course_ID,C.gradeFrom student_courseASCINNERJOINStudentASSONC.Student_ID=S.student_id98內(nèi)聯(lián)結(jié)內(nèi)連接(INNERJOIN)：內(nèi)連接返回的結(jié)果集中只SELECTS.student_Name,C.Course_ID,C.gradeFrom student_courseASCINNERJOINStudentASSONC.Student_ID=S.student_idStudent_course（成績(jī)表）Student_IDCourse_IDGrade122300100100200297896776300381猜一猜：這樣寫，返回的查詢結(jié)果是一樣的嗎？SELECT S.student_Name,C.Course_ID,C.gradeFrom StudentASSINNERJOIN student_courseASCON C.Student_ID=S.student_id再猜一猜：以下返回多少行？SELECT S.student_Name,C.Course_ID,C.gradeFrom StudentASSINNERJOIN student_courseASCON C.Student_ID<>S.student_id內(nèi)聯(lián)結(jié)-1Stundent(學(xué)生表）Student_Name梅超風(fēng)陳玄風(fēng)陸乘風(fēng)曲靈風(fēng)Student_id1234查詢結(jié)果Student_name梅超風(fēng)陳玄風(fēng)陳玄風(fēng)陸乘風(fēng)Course_IDgrade00100100200297896776陸乘風(fēng)0038199SELECTS.student_Name,C內(nèi)聯(lián)結(jié)-2SELECTS.student_Name,C.Course_ID,C.gradeFrom student_courseASC,StudentASSWhereC.Student_ID=S.student_id基于WHERE子句的內(nèi)連接語(yǔ)法形式100內(nèi)聯(lián)結(jié)-2SELECTS.student_N三表聯(lián)結(jié)方法1：SELECTS.student_Name,L.Course_Name,C.gradeFromstudent_courseASC,StudentASS,courseasLWhereC.Student_ID=S.student_idandC.course_id=L.course_id方法2:SELECTS.student_Name,l.Course_Name,C.gradeFrom student_courseASCinnerjoinStudentASSonC.Student_ID=S.student_idinnerjoincourseasLonC.course_id=L.course_id例：查詢學(xué)生的姓名、課程名、成績(jī)101三表聯(lián)結(jié)方法1：方法2:例：查詢學(xué)生的姓名、課程名、成績(jī)31ScoreStudentsIDCourseIDScore122300100100200297896776300381左外聯(lián)結(jié)StundentsSName梅超風(fēng)陳玄風(fēng)陸乘風(fēng)曲靈風(fēng)SCode1234查詢結(jié)果SName梅超風(fēng)陳玄風(fēng)陳玄風(fēng)陸乘風(fēng)CourseIDScore00100100200297896776陸乘風(fēng)00381曲靈風(fēng)NULLNULLSELECT

S.SName,C.CourseID,C.ScoreFrom

StudentsASSLEFTJOIN ScoreASCON

C.StudentID=S.SCode猜一猜：這樣寫，返回的查詢結(jié)果是一樣的嗎？SELECT

S.SName,C.CourseID,C.ScoreFrom

ScoreASCLEFTJOIN

StudentsASSON

C.StudentID=S.SCode左外連接除了包括滿足連接條件的行外，還包括其中左表的全部行。102ScoreStudentsIDCourseIDScore12右外聯(lián)結(jié)SELECTTitles.Title_id,Titles.Title,Publishers.Pub_nameFROMtitlesRIGHTOUTERJOINPublishersONTitles.Pub_id=Publishers.Pub_id右外連接除了包括滿足連接條件的行外，還包括其中右表的全部行。103右外聯(lián)結(jié)SELECTTitles.Title_id,Ti課堂練習(xí)1、查詢每個(gè)老師的部門名稱和姓名2、查詢部門名稱為‘計(jì)算機(jī)科學(xué)’的所有老師名單。提示：部門名稱在department表selectteacher_name,department_namefromteacherastinnerjoindepartmentasdont.department_id=d.department_idwhered.department_name='計(jì)算機(jī)科學(xué)'104課堂練習(xí)1、查詢每個(gè)老師的部門名稱和姓名selecttea5.5子查詢學(xué)員信息表問(wèn)題：編寫T-SQL語(yǔ)句，查看年齡比“李斯文”大的學(xué)員，要求顯示這些學(xué)員的信息？分析：

第一步：求出“李斯文”的年齡；第二步：利用WHERE語(yǔ)句，篩選年齡比“李斯文”大的學(xué)員；1055.5子查詢學(xué)員信息表問(wèn)題：分析：35什么是子查詢實(shí)現(xiàn)方法一：采用T-SQL變量實(shí)現(xiàn)DECLARE@ageINT--定義變量，存放李斯文的年齡SELECT@age=stuAgeFROMstuInfoWHEREstuName=‘李斯文’--求出李斯文的年齡--篩選比李斯文年齡大的學(xué)員SELECT*FROMstuInfoWHEREstuAge>@ageGO106什么是子查詢實(shí)現(xiàn)方法一：采用T-SQL變量實(shí)現(xiàn)DEC什么是子查詢實(shí)現(xiàn)方法二：采用子查詢實(shí)現(xiàn)SELECT*FROMstuInfoWHEREstuAge>(SELECTstuAgeFROMstuInfowherestuName='李斯文')GO子查詢子查詢?cè)赪HERE語(yǔ)句中的一般用法：

SELECT…FROM<表名>WHERE字段1<運(yùn)算符>(子查詢)外面的查詢稱為父查詢，括號(hào)中嵌入的查詢稱為子查詢UPDATE、INSERT、DELETE一起使用，語(yǔ)法類似于SELECT語(yǔ)句

將子查詢和比較運(yùn)算符聯(lián)合使用，必須保證子查詢返回的值不能多于一個(gè)107什么是子查詢實(shí)現(xiàn)方法二：采用子查詢實(shí)現(xiàn)SELECT*F使用子查詢替換表連接3-1問(wèn)題：查詢筆試剛好通過(guò)（60分）的學(xué)員名單。學(xué)員信息表和成績(jī)表108使用子查詢替換表連接3-1問(wèn)題：查詢筆試剛好通過(guò)（60分）的使用子查詢替換表連接3-2實(shí)現(xiàn)方法一：采用表連接SELECTstuNameFROMstuInfo

INNERJOINstuMarksONstuInfo.stuNo=stuMarks.stuNoWHEREwrittenExam=60GO內(nèi)連接（等值連接）109使用子查詢替換表連接3-2實(shí)現(xiàn)方法一：采用表連接SELEC使用子查詢替換表連接3-3實(shí)現(xiàn)方法二：采用子查詢

SELECTstuNameFROMstuInfoWHEREstuNo=(SELECTstuNoFROMstuMarksWHEREwrittenExam=60)GO子查詢一般來(lái)說(shuō)，表連接都可以用子查詢替換，但有的子查詢卻不能用表連接替換子查詢比較靈活、方便，常作為增刪改查的篩選條件，適合于操縱一個(gè)表的數(shù)據(jù)表連接更適合于查看多表的數(shù)據(jù)110使用子查詢替換表連接3-3實(shí)現(xiàn)方法二：采用子查詢SELECIN子查詢4-1問(wèn)題：查詢筆試剛好通過(guò)的學(xué)員名單。如何解決？111IN子查詢4-1問(wèn)題：查詢筆試剛好通過(guò)的學(xué)員名單。如何解決IN子查詢4-2解決方法：采用IN子查詢SELECTstuNameFROMstuInfoWHEREstuNoIN(SELECTstuNoFROMstuMarksWHEREwrittenExam=60)GO將＝號(hào)改為ININ后面的子查詢可以返回多條記錄常用IN替換等于（＝）的比較子查詢112IN子查詢4-2解決方法：采用IN子查詢SELECTIN子查詢4-3問(wèn)題：查詢參加考試的學(xué)員名單學(xué)員信息表和成績(jī)表（重抓本圖）分析：判斷一個(gè)學(xué)員是否參加考試其實(shí)很簡(jiǎn)單，只需要查看該學(xué)員對(duì)應(yīng)的學(xué)號(hào)是否在考試成績(jī)表stuMarks中出現(xiàn)即可

113IN子查詢4-3問(wèn)題：查詢參加考試的學(xué)員名單學(xué)員信息表和IN子查詢4-4/*--采用IN子查詢參加考試的學(xué)員名單--*/SELECTstuNameFROMstuInfoWHEREstu