學(xué)生成績管理系統(tǒng)復(fù)雜數(shù)據(jù)查詢

1、學(xué)生成績管理系統(tǒng)復(fù)雜數(shù)據(jù)查詢學(xué)生成績管理系統(tǒng)復(fù)雜數(shù)據(jù)查詢學(xué)生成績管理系統(tǒng)復(fù)雜數(shù)據(jù)查詢學(xué)生成績管理系統(tǒng)復(fù)雜數(shù)據(jù)查詢編制僅供參考審核批準(zhǔn)生效日期地址： 電話：傳真: 郵編:情境8 學(xué)生成績管理系統(tǒng)復(fù)雜數(shù)據(jù)查詢一、工作目的1掌握在一個(gè)數(shù)據(jù)表中實(shí)現(xiàn)數(shù)據(jù)的簡單查詢2掌握同時(shí)在多個(gè)數(shù)據(jù)表中實(shí)現(xiàn)數(shù)據(jù)的復(fù)合查詢3掌握實(shí)現(xiàn)分組查詢4掌握對(duì)查詢結(jié)果集排序5掌握利用庫函數(shù)進(jìn)行數(shù)據(jù)統(tǒng)計(jì)二、工作任務(wù)任務(wù)一、連接查詢 1、內(nèi)連接查詢成績表（Grade）和課程表（Course）中所有學(xué)生的成績及課程信息。 SELECT sNo,grade FROM Grade INNER JOIN Course ON = 2、外連接從學(xué)生

2、表(Student)、成績表(Grade)和課程表（Course）中查詢學(xué)生的學(xué)號(hào)(sNo)、姓名(sName)、課程名(cName)和成績(grade)（包括沒有選課的同學(xué)）。SELECT , sName, , grade FROM Student xs LEFT JOIN Grade cj ON = LEFT JOIN Course kc ON = 從學(xué)生表(Student)、成績表(Grade)和課程表（Course）中查詢學(xué)生的學(xué)號(hào)(sNo)、姓名(sName)、課程名(cName)和成績(grade)（包括還沒有講過的新課）。SELECT , sName, , gradeFROM G

3、rade cj INNER JOIN Student xs ON = RIGHT JOIN Course kc ON = 從學(xué)生表(Student)、教師表(Teacher)中查詢同姓的教師和學(xué)生的姓名。SELECT LEFT,1) 姓氏, 學(xué)生姓名, 教師姓名FROM TEACHER JS FULL JOIN STUDENT XS ON LEFT,1) = LEFT,1) 3、自連接 查詢成績表(Grade)中與學(xué)號(hào)(sNo)為“0”的學(xué)生所學(xué)的課程相同的學(xué)生的學(xué)號(hào)(sNo)、課程號(hào)(cNo)、成績(grade)。SELECT , FROM Grade cj1, Grade cj2 WHER

4、E = AND 0 AND =0 在學(xué)生表(Student)中查詢年齡相差2歲的每一對(duì)學(xué)生的學(xué)號(hào)(sNo)、出生日期(sBirthday) 。SELECT ,FROM Student xs1,Student xs2WHERE YEAR=YEAR+2 任務(wù)二、嵌套查詢 查詢哪些課程(cName)被學(xué)號(hào)(sNo)為“0”的學(xué)生所學(xué)習(xí)。SELECT cName FROM Course WHERE cNo IN（SELECT cNo FROM Grade WHERE sNo=0) 查詢所有成績(grade)都及格的學(xué)生信息。SELECT * FROM StudentWHERE sNo NOT IN(S

5、ELECT sNo FROM Grade WHERE grade60)AND sNo IN (SELECT sNo FROM Grade)找出至少學(xué)習(xí)一門有前導(dǎo)課程的學(xué)生的學(xué)號(hào)(sNo)、姓名(sName)。SELECT sNo, sName FROM Student WHERE sNo IN(SELECT sNo FROM Grade WHERE cNo IN(SELECT cNo FROM Course WHERE cPcno IS NOT NULL) 查詢與課程號(hào)(cNo)“1203”的前導(dǎo)課程(cPcno)相同的其它課程的課程號(hào)(cNo)、課程名(cName)。SELECT cNo,c

6、Name FROM Course WHERE cPcno=(SELECT cPcno FROM Course WHERE cNo=1203)AND cNo1203查詢年齡比“杜鵑”大的學(xué)生的學(xué)號(hào)(sNo)、課程號(hào)(cNo)、成績(grade)。SELECT * FROM Grade WHERE sNo IN(SELECT sNo FROM Student WHERE sBirthday=ANY(SELECT grade FROM Grade WHERE cNo=0103) 查詢有學(xué)生的成績(grade)大于“0103”課程號(hào)(cNo)中的所有學(xué)生成績的學(xué)生學(xué)號(hào)(sNo)。SELECT DIST

7、INCT sNo FROM Grade WHERE gradeALL(SELECT grade FROM Grade WHERE cNo=0103) 查詢選修了“1209”課程的學(xué)生姓名。SELECT sName FROM StudentWHERE EXISTS (SELECT * FROM GradeWHERE = AND cNo=1209) 查詢沒有選修“1209”課程的學(xué)生姓名。SELECT sName FROM StudentWHERE NOT EXISTS (SELECT * FROM GradeWHERE = AND cNo=1209) 查詢選修了全部課程的學(xué)生的姓名。SELECT

8、 sName FROM StudentWHERE NOT EXISTS (SELECT * FROM CourseWHERE NOT EXISTS (SELECT * FROM GradeWHERE = AND = 任務(wù)三、集合查詢 查詢至少有一門成績(grade)及格或性別(sSex)為“男”的學(xué)生信息。（SELECT * FROM Student WHERE sSex=男）UNION(SELECT * FROM Student WHERE EXISTS(SELECT * FROM Grade WHERE= AND grade60) 查詢成績(grade)及格并且性別(sSex)為“男”的學(xué)

9、生信息。(SELECT * FROM Student WHERE sSex=男) INTERSECT(SELECT * FROM Student WHERE EXISTS(SELECT * FROM Grade WHERE= AND grade60) 任務(wù)四、知識(shí)鏈接 復(fù)雜查詢基本語法（1）連接查詢SELECT FROM join type ON WHERE （2）嵌套查詢 使用IN操作符的子查詢使用比較運(yùn)算符的子查詢使用ANY或ALL謂詞的子查詢使用EXISTS謂詞的子查詢（3）集合查詢 UNION（并）、INTERSECT（交）和EXCEPT（差） 復(fù)雜查詢的使用技巧（1）指定別名（2）WHERE與HAVING（3）連接查詢與嵌套查詢（4）帶EXISTS的嵌套查詢（5）嵌套查詢的條件限制小結(jié)本章主要介紹了學(xué)生成績管理系統(tǒng)（SMIS）中復(fù)雜數(shù)據(jù)查詢的使用，包括各種連接查詢（等值連接、非等值連接、自連接、外部連接）、嵌套查詢（使用IN、ANY、ALL、EXISTS、各種比較符組成的嵌套）、組合查詢（UNION、INTERSECT、EXCEPT）等。通過學(xué)習(xí)，學(xué)生應(yīng)該能夠根據(jù)給定要求，學(xué)會(huì)利用SQL Server 2005的SSMS代碼編輯器實(shí)現(xiàn)連接查詢、嵌套查詢和集合查詢。本章的重點(diǎn)是嵌套查詢，其中相關(guān)子查詢又是嵌套查詢中的重點(diǎn)，它通過EXIST