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

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

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

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

5、ELECT sNo FROM Grade WHERE grade60)AND sNo IN (SELECT sNo FROM Grade)找出至少學習一門有前導課程的學生的學號(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) 查詢與課程號(cNo)“1203”的前導課程(cPcno)相同的其它課程的課程號(cNo)、課程名(cName)。SELECT cNo,c

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

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

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

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