实训二 SQL 数据查询
[实验目的]：
• 熟练掌握条件查询、分组查询以及查询的排序；
• 灵活运用库函数及统计汇总查询；
• 掌握多表之间的等值连接与非等值连接、自身连接；
• 使用多种子查询进行查询。
[实验要求]：
[实验要求]：
在 E盘建立以自己的班级 _ 姓名 _ 学号为名称的文件夹，用来保存数据库以及相关的 SQL 语句。
已知教学数据库 student 中包含六个基本表：
学生情况基本表 s (sno,sname,sex,age,dept)
教师情况基本表 t (tno,tname,sex,age,prof,sal,comm,dept)
课程基本表 c (cno,cname,ct)
选课基本表 sc (sno,cno,score)
授课基本表 tc (tno,cno)
系别基本表 d (dept,addr)
用SQL语句完成如下操作。
[实验步骤]：
一、基本查询
• 查询全体学生的学号、姓名和年龄；
select sno,sname,age
from s
• 查询学生的全部信息；
select * from s
• 查询选修了课程的学生号；
select distinct sno from sc
• 查询全体教师的教师号、姓名和年龄，并分别为三列指定别名；
select tno as ‘ 教师号 ',tname as ‘ 姓名 ',age as ‘ 年龄 'from t
二、条件查询
• 查询选修课程号为‘ C1'的学生的学号和成绩；
select sno,score
from sc
where cno='C1'
• 查询成绩高于 85分的学生的学号、课程号和成绩；
select sno,cno,score
from sc
where score>85
• 查询选修 C1或C2且分数大于等于85分学生的学号、课程号和成绩；
select sno,cno,score
from sc
where (cno='C1' or cno='C2') and (score>=85)
• 查询工资在 1000至2000元之间的教师的教师号、姓名及职称；
select tno,tname,prof
from t
where sal between 1000 and 2000
• 查询工资不在 1000至2000元之间的教师的教师号、姓名及职称；
select tno,tname,prof
from t
where sal not between 1000 and 2000
• 查询既不是女生，年龄也不是 20岁的学生；
select *
from s
where not (sex=' 女 ' or age=20)
• 查询选修 C1或C2的学生的学号、课程号和成绩；
select sno,cno,score
from sc
where cno in('C1','C2')
• 查询没有选修 C1，也没有选修C2的学生的学号、课程号和成绩；
select sno,cno,score
from sc
where cno not in('C1','C2')
或
select sno,cno,score
from sc
where cno<>'C1'and cno<>'C2'
• 查询所有姓张的教师的教师号和姓名；
select tno,tname
from t
where tname like ' 张 %'
• 查询姓名中第二个汉字是“力”的教师号和姓名；
select tno,tname
from t
where tname like '_ 力 %'
• 查询没有考试成绩的学生的学号和相应的课程号；
select sno,cno
from sc
where score is null
• 查询有考试成绩的学生的学号和相应的课程号。
select sno,cno
from sc
where score is not null
三、常用库函数及统计汇总查询
• 求学号为 S1学生的总分和平均分；
select sum(score) as TotalScore,avg(score)as AveScore
from sc
where sno='S1'
• 求选修 C1号课程的最高分、最低分及之间相差的分数；
select max(score)as MaxScore,
min(score)as MinScore, max(score)- min(score)as diff
from sc
where cno='C1'
• 求选修 C1号课程的学生人数和最高分；
select count(distinct sno),max(score)
from sc
where cno='C 1'
• 求计算机系学生的总数；
select count(sno)
from s
where dept=' 计算机 '
• 求学校中共有多少个系；
select count(distinct dept) as DeptNum
from s
• 统计有成绩同学的人数；
select count(score)
from sc
• 利用特殊函数 COUNT（*）求计算机系学生的总数；
select count(*)
from s
where dept=' 计算机 '
• 利用特殊函数 COUNT（*）求女学生总数和平均年龄；
select count(*),avg(age)
from s
where sex=' 女 '
• 利用特殊函数 COUNT（*）求计算机系女教师的总数。
select count(*)
from t
where dept=' 计算机 'and sex=' 女 '
四、分组查询及排序
• 查询各个教师的教师号及其任课门数；
select tno,count(*)as c_num
from tc
group by tno
• 按系统计女教师的人数；
select dept,count(tno)
from t
where sex=' 女 '
group by dept
• 查询选修两门以上课程的学生的学号和选课门数；
select sno,count(*)as sc_num
from sc
group by sno
having count(*)>2
• 查询平均成绩大于 70分的课程号和平均成绩；
select cno,avg(score)
from sc
group by cno
having avg(score)>70
• 查询选修 C1的学生学号和成绩，并按成绩降序排列；
select sno,score
from sc
where cno='01'
order by score desc
• 查询选修 C2、C3、C4或C5课程的学号、课程号和成绩，查询结果按学号升序排列，学号相同再按成绩降序排列；
select sno,cno,score
from sc
where cno in('C2','C3','C4','C5')
order by sno ,score desc
• 求有三门以上选课成绩及格的学生的学号及其总成绩，查询结果按总成绩降序列出。
select sno,sum(score)as TotalScore
from sc
where score>=60
group by sno
having count(*)>=3
order by sum(score) desc
五、等值连接 /非等值连接、自身连接
• 查询刘伟教师所讲授的课程，要求列出教师号、教师姓名和课程号；
select t.tno,tname,cno
from t,tc
where t.tno=tc.tno and tname=' 刘伟 '
• 查询所有选课学生的学号、姓名、选课名称及成绩；
select s.sno,sname,cname,score
from s,sc,c
where s.sno=sc.sno and c.cno=sc.cno
• 查询所有比刘伟教师工资高的教师姓名、工资和刘伟的工资；
select x.tname,x.sal as sal_a,y.sal as sal_b
from t as x,t as y
where x.sal>y.sal and y.tname=' 刘伟 '
• 检索所有学生姓名、年龄及选课名称；
select sname,age,cname
from s,sc,c
where s.sno=sc.sno and c.cno=sc.cno
• 输出学生成绩在 80—90分之间的学生名单，列出学号、姓名、分数和课程名。
select s.sno,sname,score,cname
from s,sc,c
where s.sno=sc.sno and c.cno=sc.cno
and score between 80 and 90
六、子查询
• 查询与刘伟教师职称相同的教师号、姓名；
select tno,tname
from t
where prof=
(select prof
from t
where tname=' 刘伟 ')
• 输出刘力同学所在系的学生清单；
select *
from s
where dept=
(select dept
from s
where sname=' 刘力 ')
• 使用 ANY查询讲授课程号为C5的教师姓名；
select tname
from t
where (tno=any
(select tno
from tc
where cno='C5'))
• 使用 ANY查询其他系中比计算机系某一教师工资高的教师的姓名和工资；
select tname,sal
from t
where (sal>any
(select sal
from t
where dept=' 计算机 ')) and (dept <>' 计算机 ')
• 使用 ANY输出任何成绩高于88分的学生名单，列出其学生姓名、性别、分数和课程号；
select sname,sex,score,c.cno
from s,sc
where s.sno=sc.sno and score>any
(select score
from sc
where score=88)
• 使用 IN查询讲授课程号为C5的教师姓名；
select tname
from t
where (tno in
(select tno
from tc
where cno='C5'))
• 使用 IN查询刘力同学那个系的女学生名单；
select *
from s
where dept in
(select dept
from s
where sname=' 刘力 ' and sex=' 女 ')
• 使用 ALL查询其他系中比计算机所有教师工资都高的教师的姓名和工资；
select tname,sal
from t
where (sal>all
(select sal
from t
where dept=' 计算机 '))and (dept<>' 计算机 ')
• 使用 ALL输出所有比学号20040102的学生分数高的学生名单，列出其学生姓名、性别、分数和课程名称；
select sname,s.sex,score,cname
from s,sc,c
where s.sno=sc.sno and sc.cno=c.cno and score>all
(select score
from sc
where sc.sno=' 20040102' )
• 使用 ALL查询不讲授课程号为C5的教师姓名；
select distinct tname
from t
where ('C5'<>all
(select cno
from tc
where tno=t.tno))
• 使用 EXISTS查询讲授课程号为C5的教师姓名；
select tname
from t
where exists
(select *
from tc
where tno=t.tno and cno='C5')
• 使用 EXISTS查询不讲授课程号为C5的教师姓名；
select tname
from t
where not exists
(select *
from tc
where tno=t.tno and cno='C5')
• 使用 EXISTS查询选修所有课程的学生姓名；
select sname
from s
where not exists
(select *
from c
where not exists
(select *
from sc
where sno=s.sno and cno=c.cno))
• 使用 EXISTS查询选修了C1号课程的学生姓名；
select sname
from s
where exists
(select *
from sc
where sno=s.sno and cno='C 1' )
[注意]：
• 函数 SUM和AVG只能对数值型字段进行计算；
• COUNT函数对空值不计算，但对零值计算；
• COUNT（*）用来统计元组的个数，不消除重复行，不允许使用DISTINCT关键字；
• WHERE子句作用于基本表或视图，从中选择满足条件的元组，HAVING子句作用于组，选择满足条件的组，必须用于GROUP BY子句之后；
• 子查询的返回值只有一个时，可以使用比较运算符（ =，>,<,>=,<=,!=）将父查询和子查询连接起来；
• 子查询的返回值不止一个，而是一个集合时，则不能直接使用比较运算符，可以在比较运算符和子查询之间插入 ANY或ALL。
因篇幅问题不能全部显示，请点此查看更多更全内容