数据库表的复杂查询操作
一、实验目的
掌握创建数据表的查询命令
二、实验知识要点
了解和掌握实验相关知识点:
SELECT [ALL|DISTINCT] <目标列表达式>[,<目标列表达式>] …
FROM <表名或视图名>[,<表名或视图名> ]…|(SELECT 语句)
[AS]<别名>
[ WHERE <条件表达式> ]
[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
[ ORDER BY <列名2> [ ASC|DESC ] ];
三、实验内容
创建一个名为:jiaoxue的数据库,在该库下创建如下的各表以及视图等数据库对象。
STUDENT表
学 号 | 姓 名 | 性 别 | 年 龄 | 所 在 系 |
---|---|---|---|---|
Sno | Sname | Ssex | Sage | Sdept |
200215121 | 李勇 | 男 | 20 | CS |
200215122 | 刘晨 | 女 | 19 | CS |
200215123 | 王敏 | 女 | 18 | MA |
200515125 | 张立 | 男 | 19 | IS |
COURSE表 | ||||
课程号 | 课程名 | 先行课 | 学分 | |
--------------------------- | --------------- | -------------- | ------------ | |
Cno | Cname | Cpno | Ccredit | |
1 | 数据库 | 5 | 4 | |
2 | 数学 | 1 | 2 | |
3 | 信息系统 | 6 | 4 | |
4 | 操作系统 | 7 | 3 | |
5 | 数据结构 | 6 | 4 | |
6 | 数据处理 | 2 | ||
7 | PASCAL语言 | 4 | ||
SC表 | ||||
学号 | 课程号 | 成绩 | ||
--------------------------- | --------------- | -------------- | ||
Sno | Cno | Grade | ||
200215121 | 1 | 92 | ||
200215121 | 2 | 85 | ||
200215121 | 3 | 88 | ||
200215122 | 2 | 90 | ||
200215122 | 3 | 80 |
四、实验操作及过程
--1 查询每个学生及其选修课程的情况,包括学号、姓名、课程号和课程名
select student.*,sc.* from student,sc where student.sno=sc.sno
select student.sno,sname,course.cno,cname froom student,sc,course
--2 查询每一门课程的间接先修课
select first.cno,second.cpno from course first,course second where first.cpno=second.cno
--3 查询选修2号课程且成绩在90分以上的所有学生
select student.sno,sname from student,sc where student.sno=sc.sno and sc.cno='2' and sc.grade>='90'
--4 查询选修2号课程且成绩在90分以上的所有学生
select student.sno,sname,cname,grade from student,sc,course where student.sno=sc.sno and sc.cno=course.cno
--5 查询与刘晨同学在同一个系学习的学生(子查询和自身连接查询)
select sno,sname,sdept from student s1 where exists (select* from student s2 where s2.sdept=s1.sdept and s2.sname='刘晨') --自身连接查询
select sno,sname,sdept from student where sdept=(select sdept from student where sname='刘晨')--子查询
--6 查询选修了课程名为“信息系统”的学生学号和姓名
select student.sno,sname from student,sc,course where student.sno=sc.sno and sc.cno=course.cno and course.cname='信息系统'
--7 找出每个学生超过他选修课程平均成绩的课程号
select sno,cno from sc x where grade>=(select avg(grade) from sc y where y.sno=x.sno)
--8 查询其他系中比计科系某一学生年龄小的学生姓名和年龄
select sname,sage from student where sage < any(select sage from student where sdept='CS')and sdept<>'CS'
--9 查询其他系中比计科系所有同学年龄都小的学生姓名和年龄
select sname,sage from student where sage<all(select sage from student where sdept='CS')and sdept<>'CS'
--10 查询每位同学选课的门数,包括没选课的学生
select cno,count(sno) from sc group by cno
--11 查询没选3号课程的学生姓名
select sname from student where not exists(select* from sc where sno=student.sno and cno='3')
--12 查询同时选修了1号和3号课程的学生学号
select sno from sc where cno='1' intersect select sno from sc where cno='3'
--13 查询选修了全部课程的学生学号
select sno from student where not exists(select* from course where not exists(select* from sc where sno=student.sno and cno=course.cno))
--14 查询选修了1号课程但没选2号课程的学生姓名
select sname from student where sno in(select sno from sc where cno='1' except select sno from sc where cno='2')
--15 查询选修了1号或者3号课程的学生学号
select sno from sc where cno='1' union select sno from sc where cno='3'
--16 检索至少选修了两门课程的学生学号和姓名
select sno,sname from student where sno in(select sno from sc where sc.sno=student.sno group by sc.sno having count(*)>2)
--17 STUDENT表中添加一条记录学号为991110,姓名是张三,性别为男,年龄20岁,所在系是计算机
insert into student(sno,sname,ssex,sdept,sage) values('991110','张三','男','CS','20')
select* from student
--18 STUDENT表中添加学号是991111,姓名是李四的记录
insert into student(sno,sname) values('991111','李四')
select* from student
--19 把选修了“计算机基础”课程的学生分数置为0分
update sc set grade=0 where sno in(select sno from student where sdept='CS') -- cname='计算机基础'
--20 将STUDENT表中所有信息系的年龄在18-20之间的男生存入到NEW表中
create view new(nsno,name,sex,age,dept) as select * from student where sage between 18 and 20
--21 把张三的名字修改为张山
update student set sname='张山' where sname='张三'
select* from student
--22 把计算机系李四的系修改为信息系
update student set sdept='IS' where sname='李四'
select * from student
--23 将不及格的信息系的学生分数修改为60分
update sc set grade=60 where sno in (select sc.sno from sc,student where sc.sno=student.sno and grade<60 and sdept='IS')
--24 查询出成绩排前三的学生姓名
select sname,grade top 3 from student,sc where sno in(select sno from sc order by grade DESC)
--25 查询所有成绩都在70分以上的学生学号和姓名
select sno,sname from student where sno in(select sno from sc where sc.sno=student.sno and sc.grade>70)
--26 将张彬的程序设计成绩改为99分
update sc set grade=99 where sno in(select sc.sno from student,sc,course where sc.sno=course.cno and student.sname='张彬' and course.cname='程序设计')
--27 把数据库课程的学分修改为6学时
update course set ccredit='6' where cname='数据库'
select* from course
--28 将考试成绩在50-60分之间的学生分数加10分
update sc set grade=grade+10 where sno in(select sno from sc where grade between 50 and 60)
--29 查询Student表中“信息”系或性别为“女”的同学记录
select * from student where sdept='IS'or ssex='女'
五、实验小结
本次复杂查询中运用到了
GROUP BY子句:将查询结果按某一列或者多列的值分组,值相等的为一组。但是子查询的SELECT语句中不能使用ORDER BY子句,ORDER BY子句只能对最终的查询结果排序。
一道题目通过连接查询和嵌套查询等多种方法得到结果。带有EXISTS谓词的子查询不返回任何数据,只产生逻辑真值”TRUE”或逻辑假值”FALSE”。
插入语句:INSERT INTO<表名>[(<属性列1>,…)] VALUES(<常量1>,…)
修改操作:UPDATE<表名> SET<列名>=<表达式>… WHERE<条件>