第三章 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 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 Sh

FROM SC

WHERE Ch=‘C2’

1 、检索学习 C2的学生学号

SELECT 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’)

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

SSh Sn Age Sex

s1 wang 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

2—c、检索学习 C2的学生学号 ,姓名

SELECT Sh, Sn FROM S WHERE EXISTS ( SELECT * FROM SC WHERE S.Sh=SC.Sh AND ch=‘c2’)

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

))(('3',

ccscsshTecn

))(('3'Sc

sshch

)(('3'ScCc

sshch )))(((

'3',ScCc

sshTecn ch

优化

)))((('3',ScCc

sshTecn ch SELECT

FROM

WHERE

Cn,Te

Cc

Sh=‘S3’)

ChCh IN ( SELECT

FROM

WHERE

Sc

子查询是优化查询

1 、检索 LIU老师所授课程的课程号、课程名

))(('',cc

LIUTecnch

SELECT

FROM

WHERE

Ch,Cn

Cc

Te=‘LIU’

2 、检索年龄大于 23岁的男学生的学号、姓名

))(('''23',s

msexageSNSh

SELECT

FROM

WHERE

AND

Sh,Sn

S

Age>23

Sex=‘m’

3 、检索学号为 S3学生所学课程的课程名与任课教师名

))(('3',

ccscsshTecn

SELECT

FROM

WHERE

AND

Cn,Te

SC,Cc

Sh=‘S3’

SC.CH=Cc.CH

4 、检索至少选修 LIU老师所授课程中一门课的学生姓名

))(( '' ccscsLIUTesn 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’

))((11

'3'5'2'2 scscccsh

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’)

SELECT * FROM Sc

引题：检索课程号为 C4 的平均成绩

(SELECT AVG(Grade) FROM Sc WHERE Ch=’c4’)

WHERECh=‘c4’ AND Grade <

2 、检索课程号为 c4 且成绩低于 c4 平均成绩的元组

DELETE FROM Sc

2a 、从表 SC 删除课程号为 c4, 成绩低于c4 平均成绩的元组

(SELECT AVG(Grade) FROM Sc WHERE Ch=’c4’)

WHERECh=‘c4’ AND Grade <

UPDATE ScSET Grade=1.1*Grade

2b 、将 课程号为 c4 ，成绩低于 c4 平均成绩的成绩提高百分之十

(SELECT AVG(Grade) FROM Sc WHERE Ch=‘c4’)

WHERE Ch=‘c4’ AND Grade<

UPDATE Sc SET Grade=1.1*Grade

3、将 学习 c4 的女同学的成绩提高百分之十

(SELECT Sh FROM S WHERE Sex=‘f’)

WHERE Ch=‘c4’ AND Sh IN

1 、检索平均成绩高于 80分的学号和平均分

四 集聚函数

ScSh Ch Grade

s1 c1 80s3 c1 90s1 c2 70s3 c2 85s3 c3 95s4 c3 70s8 c4 88

s1 c1 80 s1 c2 70s3 c1 90 s3 c2 85s3 c3 95s4 c3 70

Sh avg(grade)Sh avg(grade)

s1 75

s3 90

s4 70

s8 88

ScSh Ch Grade

s1 c1 80s3 c1 90s1 c2 70s3 c2 85s3 c3 95s4 c3 70s8 c4 88

2 、检索平均成绩高于 80分的课程号

s1 c1 80s3 c1 90

Ch Ch avg(grade)avg(grade) c1 85

c2 77.5

c3 82.5

c4 88

s1 c2 70s3 c2 85s3 c3 95s4 c3 70

SELECT Sh， AVG （ Grade ）

FROM SC

GROUP BY Sh

HAVING AVG(Grade)>80

1 、检索平均成绩高于 80分的学号和平均分

SELECT Ch

FROM SC

GROUP BY Ch

HAVING AVG(Grade)>80

2 、检索平均成绩高于 80分的课程号

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

3 、检索人数多于 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 Dept<>’IS’