3.1SQL查询语句SELECTShFROMSCWHERECh='C2'.ppt

1、第三章 SQL语言,1.检索学习C2的学生学号 2.检索学习C2的学生学号, 姓名 3.检索选修数学的学号和姓名 S (Sh,Sn,Sex,Age, Dept) Cc (Ch,Cn,Te) SC (Sh,Ch,Grade),SELECT Sh,1、检索学习C2的学生学号,FROM SC,WHERE Ch=C2,SELECT Sh, Sn FROM S,SCWHERE S.Sh=SC.Sh AND Ch=c2,2、检索学习C2的学生学号,姓名,SELECT Sh, SnWHERE Ch=c2,FROM S-SC,3、检索选修数学的学号和姓名,SELECT S.Sh,Sn FROM S-SC-Cc

2、 WHERE Cn=MATHS,SELECT S.Sh ,Sn FROM S,SC,CcWHERE S.Sh=SC.Sh AND SC.Ch=Cc.Ch AND Cn=MATHS,3-a、检索选修数学的学号和姓名,3.1 SQL查询语句3.2 子查询3.3 函数,第三章 SQL语言,1.检索学习C2的学生学号 2.检索学习C2学生学号, 姓名 3.检索选修数学的学号和姓名 S (Sh,Sn,Sex,Age, Dept) Cc (Ch,Cn,Te) SC (Sh,Ch,Grade),3.1 SQL查询语句,SELECT ShFROM SCWHERE Ch=C2,1、检索学习C2的学生学号,SEL

3、ECT Sh,SnFROM S,SC WHERE S.Sh=SC.Sh AND Ch=c2,2、检索学习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),2b、检索学习C2的学生学号,姓名 SELECT Sh, Sn FROM S WHERE Sh IN/Sh=SOME ( SELECT Sh FROM SC WHERE Ch=C2 ),SSh Sn Age Sex,s1 wang

4、 20 ms4 wu 19 ms2 liu 21 fs3 chen 22 ms8 dong 18 f,ScSh Ch Grade,s1 c1 80s3 c1 90s1 c2 70s3 c2 85s3 c3 95s4 c4 70s8 c3 90,s1 wang 20 m,s1 c1 80 s1 c2 70,s3 chen 22 m,s3 c1 90 s3 c2 85s3 c3 95,2c、检索学习C2的学生学号,姓名 SELECT Sh, Sn FROM S WHERE EXISTS ( SELECT * FROM SC WHERE S.Sh=SC.Sh AND ch=c2),练习：检索学号为S

5、3学生所学课程的课程名与任课教师名,优化,SELECTFROMWHERE,Cn,Te,Cc,Sh=S3),Ch,Ch IN,( SELECTFROMWHERE,Sc,子查询是优化查询,1、检索LIU老师所授课程的课程号、课程名,SELECTFROMWHERE,Ch,Cn,Cc,Te=LIU,2、检索年龄大于23岁的男学生的学号、姓名,SELECTFROMWHEREAND,Sh,Sn,S,Age23,Sex=m,3、检索学号为S3学生所学课程的课程名与任课教师名,SELECTFROMWHEREAND,Cn,Te,SC,Cc,Sh=S3,SC.CH=Cc.CH,4、检索至少选修LIU老师所授课程中

6、一门课的学生姓名,SELECTFROMWHEREANDAND,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. 查询成绩在7080之间的学生的学号和成绩,3.查询成绩在80分以上的学生的姓名（3种格式）,4.查询C2这门课的最高分数，平均分数,5.查询女同学的人数,3. 检索选修c2与c4的学生的学号4. 检索不学习课程号为C2的学生学号

7、(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/ShALL ( SELECT Sh FROM SC WHERE Ch=

8、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 C

9、c.Ch=SC.Ch),三、子查询应用,引题：从表SC删除课程号为C2的元组,1、从表SC删除课程名为MATHS的元组,DELETE FROM SC WHERE Ch IN ( SELECT Ch FROM Cc WHERE Cn=Maths),SELECT * FROM Sc,引题：检索课程号为C4的平均成绩,(SELECT AVG(Grade) FROM Sc WHERE Ch=c4),WHERE,Ch=c4 AND Grade ,2、检索课程号为c4且成绩低于c4平均成绩的元组,DELETE FROM Sc,2a、从表SC删除课程号为c4,成绩低于c4平均成绩的元组,(SELECT AV

10、G(Grade) FROM Sc WHERE Ch=c4),WHERE,Ch=c4 AND Grade ,UPDATE ScSET Grade=1.1*Grade,2b、将 课程号为c4，成绩低于c4平均成绩的成绩提高百分之十,(SELECT AVG(Grade) FROM Sc WHERE Ch=c4),WHERE Ch=c4 AND Grade80,1、检索平均成绩高于80分的学号和平均分,SELECT Ch FROM SCGROUP BY Ch HAVING AVG(Grade)80,2、检索平均成绩高于80分的课程号,思考： 检索C4课程的平均成绩 检索每门课的平均成绩,3、检索人数多

11、于20的各年龄段的男生的人数,SELECT COUNT(Sh) FROM S WHERE Sex=M GROUP BY Age HAVING COUNT(Sh) 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,年龄Age,SELECT Sn,Age FROM S WHERE Age ( SELECT MAX(Age) FROM S WHERE Dept=IS ) AND DeptIS,