SQL表查询与数据更新2

实验目的：熟悉运用 SQL92 标准实现表的查询、统计、增加、删除及修改等操作。

。

实验内容：

实验要求：要求每个学生独立完成实验工作，使用 SQL 语句完成单表查询、多表连接查

询、嵌套查询、分组查询、数据统计以及多表查询的数据更新操作。所有 SQL 题目必须在

pta 平台上的实验 3 题集中通过评测并提交。完成实验报告，每题仍需写出 SQL 语句（文本），

部分题目要求截图，必须截图的题目标注在各题中

实验步骤：

一、完成 PTA 上的“实验 3 SQL 查询与数据更新”题集，并在实验报告中写出每题的 SQL

语句。如果未能通 PTA 的评测，分析原因。

包括数据查询与数据更新：（除以下每题标出的要求外，每题的具体查询要求参见 PTA 上

题目的详细说明）

1、查询学生表所有学生记录。

select *from stu ;

2、查询学生表中部分信息

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 2020-year(birdate)>19 and sex=0;

5、查询年龄 18-20 之间的学生信息

select sno 学号,

sname 姓名,

sex 性别,

mno 专业,

2020-year(birdate) 年龄,

memo 备注

from stu where 2020-year(birdate) between 18 and 20;

6、查询姓‘李’的学生记录

select sno 学号,

sname 姓名,

sex 性别,

mno 专业,

birdate 出生日期,

memo 备注

from stu

where sname like ‘李%’;

7、查询部分专业的学生

select stu.sno 学号,

sname 姓名，

sex 性别,

mname 专业

from stu join major on stu.mno=major.mno

where mname=’计算机工程’or mname=’软件工程’

order by sno;

8、查询所有学生的平均成绩

select stu.sno 学号,

ifnull(avg(grade),0) 平均成绩

from stu left outer join sc on stu.sno=sc.sno

group by stu.sno;

9、查询各专业学生的平均成绩

select mname 专业,

ifnull(avg(grade),0) 平均成绩

from major

left outer join stu on major.mno=stu.mno

left outer join sc on stu.sno=sc.sno

group by major.mno

order by major.mno;

10、查询平均成绩高于 75 分的学生

select sno as '学号',

avg(grade) as '平均成绩'

from sc

group by sno

having avg(grade)>75;

11、查询未登记成绩的学生

select sno from sc where grade is null;

12、查询同专业的学生

select sno as '学号',

sname as '姓名' from stu

where

mno=(select mno from stu

    where sname='张三') and sname !='张三';

13、查询学生成绩及汇总其总学分

【要求】可使用 WITH ROLLUP 语句或 UNION 子句汇总结果。

  select sc.cno 课程号,

  cname 课程名,

  grade 成绩,

  credit 学分

  from sc,cou,stu where sname='张三'

  and grade>=60

  and sc.cno=cou.cno

  and sc.sno=stu.sno

  union

  select sname,

  '所有及格课程','合计总学分',sum(credit)

  from sc,cou,stu where sname='张三'

  and grade>=60

  and sc.cno=cou.cno

  and sc.sno=stu.sno;

14、查询选修某两门课程的学生

【要求】MySQL 没有 intersect 语句，考虑使用其它语句实现。报告中还需按照标准 SQL

写出使用 intersect 语句实现查询的 SQL 语句。

select a.sno 学号

from sc a

right join sc b on a.sno=b.sno

where a.cno='C001' and b.cno='C002'

group by a.sno;

标准sql语句

select sno 学号

from sc

where cno='C001'

intersect

select sno 学号

from sc

where cno='C002';

15、查询 S001 学生选修而 S003 学生未选修的课程

【要求】MySQL 没有 except 语句，可使用 not exist/not in 子查询语句实现。报告中还

需按照标准 SQL 写出使用 except 语句实现查询的 SQL 语句。

select cno 课程号

from cou

where exists(select *from sc where sno='S001' and sc.cno=cou.cno)

and not exists(select *from sc where sno='S003' and sc.cno=cou.cno);

标准sql语句

select cno

from sc

where sno=’S001’

except

select cno

from sc

where sno=’S003’;

16、查询学生选修的课程

【要求】使用 UNION 语句实现。

select sc.cno 课程号,

cname 课程

from sc,cou

where sc.sno='S001' and sc.cno=cou.cno

union

select sc.cno 课程号,

cname 课程

from sc,cou

where sc.sno='S003' and sc.cno=cou.cno

17、查询比“网络工程”专业所有学生年龄都小的学生姓名

select sname

from stu

where birdate>

(select max(birdate) from

stu where mno=(select mno  from major where major.mname='网络工程'));

18、查找课程选修的情况

select  cou.cno 课程号,

cname 课程名,

ifnull(count(sc.cno),0) 选课人数,

ifnull(max(grade),0) 最高成绩,

ifnull(min(grade),0) 最低成绩,

ifnull(avg(grade),0) 平均成绩

from sc

right outer join cou on sc.cno=cou.cno

group by cou.cno

order by cou.cno;

19、查询平均分高于 80 分的学生

select sname

from stu

join sc on stu.sno=sc.sno

group by stu.sno

having avg(grade)>80;

查询成绩最高的前三名同学

select stu.sname as '姓名',sc.grade as'成绩'

from sc

join stu on sc.sno =stu.sno

join cou on sc.cno=cou.cno

where cname='C语言'

order by grade desc limit 3;

20、查询平均成绩最高的前 3 名同学

select sc.sno as '学号',

stu.sname as '姓名',

stu.sex as '性别',

2020-year(stu.birdate) as '年龄',

avg (sc.grade) as '平均成绩'

from sc

join stu on sc.sno=stu.sno

group by sc.sno

order by avg(sc.grade) desc limit 3;

21、查询课程成绩最高二人

select stu.sno,sname,grade

from stu

join sc on sc.sno=stu.sno

where sc.cno='C002'

order by grade desc limit 2;

22、查询选修课程超过 2 门且成绩都在 80 分以上的学生

select sname 姓名,

mno 专业,

sum(credit) 总学分

from stu

join sc on stu.sno=sc.sno

join cou on sc.cno=cou.cno

group by sname,mno

having count(sname)>=2 and min(grade)>=80;

23、查询选修张老师讲授所有课程的学生

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、添加一条学生记录

insert into stu

values

('S012','周强','1',null,null,null);

2、添加学生成绩记录

insert into sc values

('S012','C001',90),

('S012','C002',null);

3、批量插入学生记录

insert

into softstu(sno,sname)

select sno,sname from stu

where mno='02';

4、检索部分教师并插入新表

insert into faculty(tname)

select distinct teacher from cou

where cou.teacher not in

(

    select teacher from cou

    left outer join sc

    on sc.cno=cou.cno

    group by cou.cno

    having avg(grade)<=70 or avg(grade) is null

);

5、插入学生总学分表

insert into totalcredit

select sno,sum(credit)

from(

    select stu.sno sno,

       case

       when sc.grade>=60 then credit

       else 0 end

                credit

    from stu left outer join sc on stu.sno=sc.sno

    left outer join cou on sc.cno=cou.cno

    group by stu.sno,credit,grade

) a

group by sno

order by sno;

6、修改高数不及格的学生成绩

update sc set

grade = 60

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

7、修改女生成绩

update  sc

set grade=grade*1.05 where grade<75 and sno in

(select sno from stu where sex=0);

8、修改学生选课成绩

update sc set grade=(

case

 when grade<70 and cno='C001' then grade*1.05

 when grade>=70 and cno='C001' then grade*1.04

 else grade end

);

9、 添加成绩等级 rank 字段

update sc

set rank=

case

when grade>=90  then 'A'

when grade>=80  then 'B'

when grade>=70  then 'C'

when grade>=60  then 'D'

when grade<60  then 'E'

else null end;

10、 计算并填写学生获得的总学分

update stu,(select sno,

            sum(credit)  sum

from(

    select stu.sno sno,

    case when sc.grade>=60 then credit else null end credit

    from stu left outer join sc on stu.sno=sc.sno

             left outer join cou on cou.cno=sc.cno

    group by stu.sno,credit,grade

) a          

    group by sno) b

    set stu.totalcredit =b.sum

    where stu.sno=b.sno;

11、删除成绩为空的学生选课记录

delete from sc where grade is null;

12、删除选修 C 语言课程的女生成绩记录

delete from sc where

sc.sno in(select sno from stu where sex =0) and

sc.cno =(select cno from cou where cname ='C语言');

13、 删除学生所有信息

delete from sc where

sno =(select sno from stu where sname ='周强');

delete from stu where sname='周强';

• 实验总结

     在第三次实验中发现很多聚集函数的使用需要注意的地方，例如在pta中不允许group  by 的嵌套使用，还有其他语句的用法：union 、rollup 。

      在学习过程中也有很多书上的语句看不懂的情况，这时可以翻看老师的教学ppt,或者去网上寻找相应的博客进行学习，和同学讨论也是解决问题的方法之一，不断加深我对mysql 数据库这门课程的理解，更加深入的了解这么课程，与此同时，伴随着javaweb课程与数据库的联系越来越深，也要求我更加深入的学习。