第三章 SQL 语言

第三章 SQL语言

1.检索学习C2的学生学号 2.检索学习C2的学生学号, 姓名 3.检索选修数学的学号和姓名 S (Sh,Sn,Sex,Age, Dept) Cc (Ch,Cn,Te) SC (Sh,Ch,Grade)

1、检索学习C2的学生学号 SELECT Sh FROM SC WHERE Ch=‘C2’

2、检索学习C2的学生学号,姓名 SELECT Sh, Sn WHERE Ch=‘c2’ FROM S-SC SELECT Sh, Sn FROM S,SC WHERE S.Sh=SC.Sh AND Ch=‘c2’

3、检索选修数学的学号和姓名 SELECT S.Sh,Sn FROM S-SC-Cc WHERE Cn=‘MATHS’

3-a、检索选修数学的学号和姓名 SELECT S.Sh ,Sn FROM S,SC,Cc WHERE S.Sh=SC.Sh AND SC.Ch=Cc.Ch AND Cn=‘MATHS’

第三章 SQL语言 3.1 SQL查询语句 3.2 子查询 3.3 函数

3.1 SQL查询语句 1.检索学习C2的学生学号 2.检索学习C2学生学号, 姓名 3.检索选修数学的学号和姓名 S (Sh,Sn,Sex,Age, Dept) Cc (Ch,Cn,Te) SC (Sh,Ch,Grade)

1、检索学习C2的学生学号 SELECT Sh FROM SC WHERE Ch=‘C2’

2、检索学习C2的学生学号,姓名 SELECT Sh,Sn FROM S,SC WHERE S.Sh=SC.Sh AND Ch=‘c2’ 练习：P81 3.2 (1) (2)

2-a、检索学习C2的学生学号,姓名 SELECT Sh, Sn FROM s WHERE Sh=‘s1’ OR Sh=‘s3’ WHERE Sh IN (‘s1’,’s3’) WHERE Sh=SOME (‘s1’,’s3’)

2—b、检索学习C2的学生学号,姓名 SELECT Sh, Sn FROM S WHERE Sh IN/Sh=SOME ( SELECT Sh FROM SC WHERE Ch=‘C2’ )

S Sh Sn Age Sex Sc Sh Ch Grade s1 c1 80 s3 c1 90 s1 c2 70 s3 c2 85 s3 c3 95 s4 c4 70 s8 c3 90 s1 c1 80 s1 c2 70 s1 wang 20 m s4 wu 19 m s2 liu 21 f s3 chen 22 m s8 dong 18 f s1 wang 20 m s3 c1 90 s3 c2 85 s3 c3 95 s3 chen 22 m

2—c、检索学习C2的学生学号,姓名 SELECT Sh, Sn FROM S WHERE EXISTS ( SELECT * FROM SC WHERE S.Sh=SC.Sh AND ch=‘c2’)

练习：检索学号为S3学生所学课程的课程名与任课教师名练习：检索学号为S3学生所学课程的课程名与任课教师名优化

SELECT FROM WHERE Cn,Te Cc ( SELECT FROM WHERE Ch IN Ch Sc Sh=‘S3’) 子查询是优化查询

1、检索LIU老师所授课程的课程号、课程名 SELECT FROM WHERE Ch,Cn Cc Te=‘LIU’

2、检索年龄大于23岁的男学生的学号、姓名 SELECT FROM WHERE AND Sh,Sn S Age>23 Sex=‘m’

3、检索学号为S3学生所学课程的课程名与任课教师名3、检索学号为S3学生所学课程的课程名与任课教师名 SELECT FROM WHERE AND Cn,Te SC,Cc Sh=‘S3’ SC.CH=Cc.CH

4、检索至少选修LIU老师所授课程中一门课的学生姓名4、检索至少选修LIU老师所授课程中一门课的学生姓名 SELECT FROM WHERE AND AND Sn S,SC,Cc Te=‘LIU’ S.SH=SC.SH SC.CH=Cc.CH

5、检索选修数学的学号和姓名 SELECT S.Sh,S.Sn FROM S,SC,Cc WHERE S.Sh=SC.Sh AND SC.Ch=Cc.Ch AND Cc.Cn=‘MATHS’

1. 查询学生选课表中的全部数据 2. 查询成绩在70~80之间的学生的学号和成绩 3.查询成绩在80分以上的学生的姓名（3种格式） 4.查询C2这门课的最高分数，平均分数 5.查询女同学的人数

二、子查询 3. 检索选修c2与c4的学生的学号 4. 检索不学习课程号为’C2’的学生学号(Sh),姓名(Sn). 5。检索无人选修课程号(Ch), 课程名(Cn) S (Sh,Sn,Sex,Age, Dept) C (Ch,Cn,Te) SC (Sh,Ch,Gr)

3、检索选修c2与c3的学生的学号 SELECT SC.Sh FROM SC ,SC as SC_a WHERE SC.Sh = SC_a.Sh AND SC.Ch =‘C2’ AND SC_a.Ch=‘C3’

4-a、检索不学习课程号为’C2’的学生学号(Sh),姓名(Sn) SELECT Sh, Sn FROM S WHERE Sh NOT IN/Sh<>ALL ( SELECT Sh FROM SC WHERE Ch=‘C2’ )

4-b、检索不学习课程号为’C2’的学生学号(Sh),姓名(Sn) SELECT Sh, Sn FROM S WHERE NOT EXISTS ( SELECT * FROM SC WHERE S.Sh=SC.Sh AND CH=‘C2’ )

5-a、检索无人选修课程号(Ch), 课程名(Cn) SELECT Ch,Cn FROM Cc WHERE Ch NOT IN ( SELECT Ch FROM SC )

5-b、检索无人选修课程号(Ch), 课程名(Cn) SELECT Ch,Cn FROM Cc WHERE NOT EXISTS ( SELECT * FROM SC WHERE Cc.Ch=SC.Ch)

三、子查询应用 引题：从表SC删除课程号为C2的元组 1、从表SC删除课程名为MATHS的元组 DELETE FROM SC WHERE Ch IN ( SELECT Ch FROM Cc WHERE Cn=’Maths’)

引题：检索课程号为C4的平均成绩 2、检索课程号为c4且成绩低于c4平均成绩的元组 SELECT * FROM Sc WHERE Ch=‘c4’ AND Grade < (SELECT AVG(Grade) FROM Sc WHERE Ch=’c4’)

2a、从表SC删除课程号为c4,成绩低于c4平均成绩的元组 DELETE FROM Sc WHERE Ch=‘c4’AND Grade < (SELECT AVG(Grade) FROM Sc WHERE Ch=’c4’)

2b、将课程号为c4，成绩低于c4平均成绩的成绩提高百分之十2b、将课程号为c4，成绩低于c4平均成绩的成绩提高百分之十 UPDATE Sc SET Grade=1.1*Grade WHERE Ch=‘c4’ AND Grade< (SELECT AVG(Grade) FROM Sc WHERE Ch=‘c4’)

3、将学习c4的女同学的成绩提高百分之十 UPDATE Sc SET Grade=1.1*Grade WHERE Ch=‘c4’AND Sh IN (SELECT Sh FROM S WHERE Sex=‘f’)

四集聚函数 1、检索平均成绩高于80分的学号和平均分 Sc Sh Ch Grade Sh avg(grade) s1 c1 80 s1 c2 70 s1 c1 80 s3 c1 90 s1 c2 70 s3 c2 85 s3 c3 95 s4 c3 70 s8 c4 88 s1 75 s3 c1 90 s3 c2 85 s3 c3 95 s3 90 s4 70 s4 c3 70 s8 88

2、检索平均成绩高于80分的课程号 Sc Sh Ch Grade Ch avg(grade) s1 c1 80 s3 c1 90 s1 c2 70 s3 c2 85 s3 c3 95 s4 c3 70 s8 c4 88 s1 c1 80 s3 c1 90 c1 85 s1 c2 70 s3 c2 85 c2 77.5 c3 82.5 s3 c3 95 s4 c3 70 c4 88

1、检索平均成绩高于80分的学号和平均分 SELECT Sh，AVG（Grade） FROM SC GROUP BY Sh HAVING AVG(Grade)>80

2、检索平均成绩高于80分的课程号 SELECT Ch FROM SC GROUP BY Ch HAVING AVG(Grade)>80

思考： • 检索C4课程的平均成绩 • 检索每门课的平均成绩

3、检索人数多于20的各年龄段的男生的人数 SELECT COUNT(Sh) FROM S WHERE Sex=’M’ GROUP BY Age HAVING COUNT(Sh) >20

4、检索人数多于20的各年龄段的男生的年龄和人数,按人数升序排列，如果人数相同按年龄降序排列 4、检索人数多于20的各年龄段的男生的年龄和人数,按人数升序排列，如果人数相同按年龄降序排列 SELECT Age,COUNT(Sh) FROM S WHERE Sex=’M’ GROUP BY Age HAVING COUNT(Sh) >20 ORDER BY 2,Age DESC

5、检索平均成绩最高的学号,平均成绩 SELECT Sh, AVG(Grade) FROM SC GROUP BY Sh HAVING AVG(Grade)>= ( SELECT AVG(Grade) FROM SC GROUP BY Sh )

6、检索非信息系比信息系’IS’所有学生年龄都大的学生姓名Sn,年龄Age6、检索非信息系比信息系’IS’所有学生年龄都大的学生姓名Sn,年龄Age SELECT Sn,Age FROM S WHERE Age> (SELECT MAX(Age) FROM S WHERE Dept=’IS’ ) AND Dept<>’IS’

第三章 SQL 语言