(2) 修改数据
将张星老师数据结构课的学生成绩全部加2分。
use 实验 update SC
set Grade = Grade + 2 where Cno in
(select Cno from Course
where Cname = '数据结构' );
(3) 删除数据
删除马朝阳同学的所有选课记录。
use 实验 delete from SC where Sno in
(select Sno from Student
where Sname = '马朝阳' );
2. 查询操作
(1) 单表查询
查询所有学生的信息;
use 实验 select * from Student
查询所有女生的姓名;
use 实验
select Sname from Student where Ssex = '女'
查询成绩在80 到89 分之间的所有学生选课记录,查询结果按成绩的降序排列;
use 实验 select * from SC
where Grade between 80 and 89 order by Grade desc;
查询各个系的学生人数;
use 实验
select Sdept,count(Sdept) count from Student group by Sdept;
(2) 连接查询
查询信息系年龄在21 岁以下(含21 岁)的女生姓名及其年龄。
use 实验
select Sname, Sage from Student, Dept
where Dname = '信息' and Sage <= 21 and Dept.Deptno = Student.Sdept;
(3) 嵌套查询
查询选课总学分在10 分以下的学生姓名;
use 实验
select Sname from Student where Sno in
(select Sno from Course, SC
where Course.Cno = SC.Cno group by Sno
having sum(Credit) < 10 );
查询各门课程取得最高成绩的学生姓名及其成绩;
use 实验
select Cno, Sname, Grade from Student, SC where SC.Cno in
(select Cno from SC group by Cno ) and
(select max(Grade) from SC group by Cno ) and
Grade in
Student.Sno = SC.Sno
order by Cno;
查询选修了101 学生选修的全部课程的学生学号;
use 实验 select Cno
from Course where Ctno = 101;
use 实验 select Sno from SC where Cno = 1
and Sno in
(select Sno from SC
where Cno = 4);
查询选修了张星老师开设的全部课程的学生姓名。
use 实验 select Cno
from Teacher, Course
where Tname = '张星' and Tno = Ctno;
use 实验
select Sname from SC, Student
where SC.Sno = Student.Sno
and Cno = 1 and SC.Sno in
(select Sno from SC where Cno = 4)
实验四SQL语句
![](/skin/haowen/images/icon_star.png)
![](/skin/haowen/images/icon_star.png)
![](/skin/haowen/images/icon_star.png)
![](/skin/haowen/images/icon_star.png)