欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

数据库:增、删、改、查操作

程序员文章站 2022-05-08 17:30:31
...

数据库表详情:
学生表(STU):
数据库:增、删、改、查操作
课程表(COU):
数据库:增、删、改、查操作
学生选修课程表(SC):
数据库:增、删、改、查操作
专业表(MAJOR):
数据库:增、删、改、查操作

一、查询操作
1.查询全学院所有学生的信息

select * from stu

2.查询所有学生的学号与姓名以及性别。
要求:性别用“男”或“女”来显示,提示:使用T-SQL的case…when语句(参看联机帮助)

select sno,sname,case sex 
    when 1 then '男'
    when 0 then '女'
end
    AS 'sex'
from stu

3.查询女生的学号与姓名

select sno '学号',sname '姓名' from stu where(sex = 0) 

4.查询女生且年龄19以上学生信息

select sno '学号',sname '姓名',sex '性别',mno '专业',2020-year(birdate) '年龄',memo '备注'from stu where(sex = 0 and 2020-year(birdate)>19)

5.查询年龄18-20的学生信息

select sno '学号',sname '姓名',sex '性别',mno '专业',2020-year(birdate) '年龄',memo '备注'from stu where (2020-year(birdate)>=18 and 2020-year(birdate) <=20)

6.查询所有姓陈的学生信息

select sno '学号',sname '姓名',sex '性别',mno '专业',birdate '出生日期',memo '备注'from stu where (sname like '陈%')

7.查询计算机、软件专业的学生信息

select sno '学号',sname '姓名',sex '性别',mname '专业' from stu,major where(stu.mno = major.mno and (mname = '计算机工程'or mname = '软件工程')) order by sno 

8.查询全学院的学生成绩平均分(注:有的学生可能没有选修课程,平均分为0)

select stu.sno 学号,ifnull(avg(grade),0) 平均成绩
from stu left join sc on stu.sno = sc.sno
group by stu.sno

9.查询各专业的学生成绩平均分(注:有的学生可能没有选修课程)

select mname 专业,ifnull(avg(grade),0) 平均成绩
from major left join stu on major.mno = stu.mno left join sc on stu.sno = sc.sno 
group by mname

10.查询平均分多于75分的学生学号

select sno 学号,avg(grade) 平均成绩
from sc
group by sno
having avg(grade)>75

11.查询‘C001’课程未登记成绩的学生学号

select sno
from sc
where cno = 'C001' and grade is NULL

12.查询选修‘C语言’课程的学生的学号
1)采用连接查询

select sname 姓名,grade 成绩
from stu,cou,sc
where stu.sno = sc.sno and sc.cno = cou.cno and cname = 'C语言'
order by grade desc

2)采用嵌套查询

select SNO from dbo.SC where CNO in (select CNO from dbo.COU where (CNAME = 'C语言') )

3)采用EXIST查询

select sname 姓名,grade 成绩 from stu,sc where exists(select * from sc where sc.sno = stu.sno and sc.cno in (select cno from cou where cname = 'C语言')) order by grade,sname desc

13.查询未选修‘C语言’课程的学生的学号(not exist实现)

select sno 学号,sname 姓名
from stu
where not exists
(select * from sc,cou where stu.sno = sc.sno and sc.cno = cou.cno and cname = 'C语言')
order by stu.sno

15.查询与‘张三’在同一个专业的学生信息

select sno 学号, sname 姓名
from stu 
where mno in
(select mno from stu where sname = '张三')
and sname != '张三'

16.按各位同学自已的姓名查询选修了哪几门课程,已获得的学分。
注:至少两种命令,并且需截图。

select * from(
select cou.cno 课程号,cname 课程,grade 成绩
from cou,sc,stu
where sname = '张三' and stu.sno = sc.sno and sc.cno = cou.cno
union 
select '张三','总学分',
(select sum(credit) 
from cou,stu,sc 
where sname = '张三' and stu.sno = sc.sno and sc.cno = cou.cno and grade >= 60)
) a
order by a.课程号

17.查询至少选修的’C001’与’C002’课程的学生学号
1)使用SC表的自连接完成

select a.sno '学号' 
from (select * from sc where (cno = 'C001')) a 
inner join 
(select * from sc where (cno = 'C002')) b 
on a.sno = b.sno

2)使用INTERSECT(交)完成

select sno '学号' from sc where (cno = 'C001')
intersect
select sno '学号' from sc where (cno = 'C002')

18.查询S001学号选修而S003学号未选修的课程号
(提示:使用EXCEPT)

select a.cno '课程号'
from 
(select cno from sc where sno = 'S001') a
left outer join
(select cno from sc where sno = 'S003') b
on a.cno = b.cno
where b.cno is NULL
select cno '课程号' from sc where sno = 'S001'
except
select cno '课程号' from sc where sno = 'S003'
  1. 查询S001学号、S003学号都选修了哪些课程
    (试验:UNION)
select cou.cno 课程号,cname 课程
from cou,sc
where cou.cno = sc.cno and sc.sno = 'S001'
union
select cou.cno 课程号,cname 课程
from cou,sc
where cou.cno = sc.cno and sc.sno = 'S003'
order by 1

20.查询每个同学超过他选修的平均成绩的课程名。
1)用相关子查询实现

select sno 学号,cname 课程名,grade 成绩
from cou,sc a 
where cou.cno = a.cno and 
(a.grade > (select avg(b.grade) from sc b where a.sno = b.sno))

2)使用派生表实现。

select a.sno 学号,cname 课程名,grade 成绩
from cou,sc a,(select cno,avg(grade) x from sc group by sc.cno) as b
where a.cno = b.cno and a.grade > b.x and cou.cno = a.cno

21.查询平均分高于80分的学生姓名

select sname,avg(grade) 
from stu,sc
where sti.sno = sc.sno and sno in(select sno from sc group by (sno) 
having (avg(grade)>80))

22.查询平均分高于60分的课程的课程名。

select cno '课程号',cname '课程名' 
from cou 
where cno in (select cno from sc group by cno having (avg(grade)>60))

23.查询‘C语言’课程成绩最高的前三名同学

select sname '姓名',grade '成绩'
from stu,sc,cou 
where(sc.cno = cou.cno and cname = 'C语言' and sc.sno = stu.sno) 
order by grade desc 
limit 3

24.查询平均成绩最高的前3名同学的学号,姓名,性别及年龄。

Select top 3 stu.sno as 学号,sname as 姓名,sex as 性别,2020-year(birdate) as 年龄,平均成绩 
from stu,(select sno,avg(grade) as 平均成绩 from sc group by sno ) x 
where stu.sno=x.sno order by 平均成绩 desc

25.检索C003课程成绩最高二人学号,姓名与成绩。并将结果保存于max_C003临时表中
注:结果截图

Select top 2 stu.sno,sname,grade
from stu,sc
where cno = 'C002' and stu.sno = sc.sno
order by grade desc

26.查询选修了张老师所讲授的所有课程的学生。

select sname 
from stu 
where not exists (select * from cou where teacher = '张老师' and not exists (select * from sc where sc.sno = stu.sno and sc.cno = cou.cno))

(二)添加
1.对学生表添加一条记录,记录(SO12,周强,女)等

insert into stu values('S012','周强',0,NULL,NULL,NULL)

2.为上述学生添加二条选课记录。

insert into sc values('S012','C001',90);
insert into sc values('S012','C002',NULL);

3.为软件专业创建一个学生简表,用于点名。

insert into softstu (sno,sname) 
select sno,sname 
from stu 
where mno = '02'

4.检索所授每门课程平均成绩均大于70分的教师姓名,并将检索的值送往另一个已在的表faculty(tname)

insert into faculty
select teacher
from cou c1
where cno in(
select sc.cno
from sc
group by sc.cno
having avg(grade)>70
)
group by teacher
having count(c1.teacher)=(select count(teacher) from cou c2 where c1.teacher = c2.teacher)

5.创建表totalcredit(sno,totalcredit),为该表插入各同学当前获得总学分

CREATE TABLE totalcredit ( sno char(4) NOT NULL, totalcredit smallint DEFAULT 0 )
insert into totalcredit(sno,totalcredit) 
select distinct stu.sno,ifnull(sum(credit),0) 
from stu left join 
(select sc.sno,credit from sc left join cou on sc.cno = cou .cno and grade>60)a on stu.sno = a.sno
group by stu.sno 
order by stu.sno

(三)删除
(1)在SC中删除尚无成绩的选课元组

delete from sc where grade is null

(2)把选修’C语言’课程的女同学选课元组全部删除

delete from sc where cno in (select cno from cou where cname = 'C语言') and sno in (select sno from stu where sex = 0)

(3)删除周强的所有信息

delete from sc where sno in (select sno from stu where stu.sno = sc.sno and stu.sname = '周强');
delete from stu where sname = '周强'

(四)修改
1.将高数课不及格的成绩全改为60分

update sc set grade = 60 where grade < 60 and cno in (select cno from cou where cname = '高等数学')

2.把低于所有课程总平均成绩的女同学成绩提高5%;

update sc set grade = grade + grade * 0.05
where grade in
(select sno 学号,cname 课程名,grade 成绩
from cou,sc a 
where cou.cno = a.cno and 
(a.grade < (select avg(b.grade) from sc b where a.sno = b.sno)))

3.在SC中修改C004课程的成绩,若成绩小于70分则提高5%,若成绩大于70分则提高4%(要求用两种方法实现,一种方法是用两个UPDATE语句实现。另一种方法是用CASE 操作的一条UPDATE语句实现)

update sc set grade = grade + grade * 0.04 where cno = 'C001' and grade >= 70;
update sc set grade = grade + grade * 0.05 where cno = 'C001' and grade < 70;
update sc set grade = case
when grade >= 70 then grade + grade * 0.04
when grade < 70 then grade + grade * 0.05
end 
where cno = 'C001'

4.为SC表添加一个字段RANK。将各同学按60分以下为E,60-69为D,70-79为C,80-89为B,90及以上为A

update sc set rank = case
when grade >= 90 then 'A'
when grade >= 80 and grade < 90 then 'B'
when grade >= 70 and grade < 80 then 'C'
when grade >= 60 and grade < 70 then 'D'
when grade < 60 then 'E'
end