2021-10-21

数据库 分组及多表查询

create database ST
default character set=utf8
use ST
1、运行老师给出的脚本，创建实验所需的数据库ST，在ST数据库中创建教师表teacher。要求：teacher表包括tid(教师编号)、tname(教师姓名)、sex(性别)、title(职称)，其中tid为int 类型主键，自动增长，tname为非空约束,sex 只能取“男”或“女”，默认值为“男”,职称取值为“教授”、“高级工程师”、“副教授”、“工程师”、“讲师”、“助教”,并任意插入两个教师信息。
create table teacher
(tid int primary key auto_increment,
tname varchar(10) not null,
sex enum('男','女') default '男',
title enum('教授','高级工程师','副教授','工程师','讲师','助教')
)
insert into teacher values(202101,'张华','男','教授')
,(202102,'王芳','女','高级工程师')
2、数据更新：在ST数据库中按要求进行数据更新
（1）使用SQL语句向Student表中插入元组（学号：201215111，姓名：张三，性别：男，年龄：17，所在系：IS）
    insert into student values('201215111','张三','男','17','IS')
（2）主键重复插入：向Student表中插入元组('201215123', '王慧敏', '女', '17', 'MA'），注意主键重复可以用on duplicate key update  或replace into实现 。
    desc student
    replace into student values('201215123', '王慧敏', '女', '17', 'MA'）
（3）批量数据的插入：对每门课程，求学生的选课人数和学生的平均成绩，并把结果存入数据库，使用INSERT INTO实现。（提示：用INSERT INTO语句实现要求先创建一个课程选课情况表，包括课程名，选课人数，平均成绩字段，然后再用INSERT INTO语句实现数据的复制）
    create table coursegrade
    (coursename varchar(20) primary key,
      cnum int,
      avggrade int
     )
insert into coursegrade
select cname 课程名,count(*) 选课人数,avg(grade) 平均成绩
from course join sc
on course.cno=sc.cno
group by cname
select * from coursegrade
（4）在Student表中使用SQL语句将“李晨”的年龄改为20。
update student set sage=20 where sname='李晨'
select * from student where sname='李晨'
（5）在SC表中使用SQL语句将所有成绩不及格的学生成绩上浮10%。
select grade from sc where grade<60
update sc set grade=grade*1.1 
where grade<60
（6）把成绩表中成绩后5名学生成绩加上10分。
update sc 
set grade=grade+10 
order by grade asc limit 5 
（7）删除成绩后3名的学生成绩
delete from sc
order by grade asc limit 3
（8）把成绩最高的学生成绩下调5%。
update sc 
set grade=grade*0.95 
order by grade desc limit 1
3、在学生-课程数据库（ST）中用SQL语句完成以下查询：
（1）查找每个学生的学号、姓名，选修门数和平均分。
select sc.sno 学号,sname 姓名,count(*) 选修门数,avg(grade) 平均分
from student join sc
on student.sno=sc.sno
group by sc.sno,sname
（2）查找每门课程的课程号、课程名，平均分、最高分和最低分。
select sc.cno 课程号,cname 课程名,avg(grade) 平均分,Max(grade) 最高分,Min(grade) 最低分
from course join sc
on course.cno=sc.cno
group by sc.cno,cname
（3）求出至少被两名学生选修的课程名及其选修的人数；
select cname 课程名,count(*) 选修的人数
from course join sc
on course.cno=sc.cno
group by cname
having count(*)>2
（4）查询至少选修了三门课程的学生姓名及其选修的门数；
select sname 姓名, count(*) 选修的门数
from student join sc
on student.sno=sc.sno
group by sname
having count(*)>3
（5）查询学生的姓名，选修的课程名和该课程的成绩；
select sname,cname,grade
from  student join sc join course
on student.sno=sc.sno
and sc.cno=course.cno
（6）查找选修“离散数学”课程的学生姓名和离散数学的成绩
select sname,grade
from  student join sc join course
on student.sno=sc.sno
and sc.cno=course.cno
and cname='离散数学'
（7）查找数据库成绩前3名的学生姓名和数据库的成绩
select sname,grade
from  student join sc join course
on student.sno=sc.sno
and sc.cno=course.cno
and cname='数据库'
order by grade desc  limit 3
（8）查询学号为201215121的学生的姓名及其选修的课程名及成绩；
select sname,cname,grade
from  student join sc join course
on student.sno=sc.sno
and sc.cno=course.cno
and sc.sno='201215121'
（9）查询选修了“信息系统”课程的学生学号和姓名；
select sc.sno,sname
from  student join sc join course
on student.sno=sc.sno
and sc.cno=course.cno
and cname='信息系统'
（10）找出成绩表SC中成绩后10名学生的姓名和成绩
select sname,grade
from  student join sc
on student.sno=sc.sno
order by grade asc limit 10
（11）查询所选修“数学”课程成绩大于90分的学生的姓名和该生“数学”课的成绩；
select sname,grade
from  student join sc  join course
on student.sno=sc.sno
and sc.cno=course.cno
and cname='数学'
and grade>90
（12）查询数据库课程的平均成绩；
select avg(grade) 数据库平均成绩
from sc join course
on sc.cno=course.cno
and cname='数据库'
（13）查询计算机系（CS）选修了3门以上课程的学生的学号和姓名；
select sc.sno,sname
from  student join sc
on student.sno=sc.sno
and sdept='CS'
group by sc.sno,sname
having count(*)>3
（14）查询成绩第3名的学生姓名，第3名成绩对应的课程名和成绩；
select sname,cname,grade
from  student join sc  join course
on student.sno=sc.sno
and sc.cno=course.cno
order by grade desc limit 2,1
（15）查询计算机系（CS）和信息系(IS)的学生姓名、性别和年龄；
select sname,ssex,sage
from  student
where sdept='CS' or sdept='IS'
（16）查询既不是计算机系又不是信息系的学生姓名；
select sname
from  student
where sdept !='CS' and sdept !='IS'
（17）查询姓“王”且全名为3个汉字的学生姓名；
select sname
from student
where sname like '王__'
（18）查询姓名以“明”字结尾的学生信息；（用正则表达式实现）
select sname
from student
where sname regexp '明$'
（19）查询姓名中含有两个“明”字的学生信息；（用正则表达式实现）
select sname
from student
where sname regexp '明{2}'
（20）查所有不姓“张”也不姓“李”的学生的姓名；
select sname
from student
where sname  not like '张%' and sname  not like '李%'
（21）查年龄为空值的学生的学号和姓名
select sno,sname
from student
where sage is null
（22）查询平均成绩最高的学生姓名和平均成绩；
select sname,avg(grade) 平均成绩
from  student join sc
on student.sno=sc.sno
group by sname
order by avg(grade) desc limit 1
（23）查询年龄最大的学生信息；
select *
from  student 
order by sage desc limit 1
（24）找出选修课程“信息系统”成绩最高的学生的学号；
select sc.sno
from  student join sc  join course
on student.sno=sc.sno
and sc.cno=course.cno
and cname='信息系统'
order by grade desc limit 1
（25）查询数据库成绩最高的学生姓名；
select sname
from  student join sc  join course
on student.sno=sc.sno
and sc.cno=course.cno
and cname='数据库'
order by grade desc limit 1