MySQL(六)之MySQL经典练习题及答案

题目转自他人，答案结合他人和自己的想法

目录

练习开始

查询学过1和2课程的学生

查询学过1课程但没学过2课程的学生

查询学过1课程但学过2课程的学生

查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

查询在 SC 表存在成绩的学生信息

查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

查询「李」姓老师的数量

查询学过「张三」老师授课的同学的信息

查询没学过"张三"老师讲授的任一门课程的学生姓名

查询没有学全所有课程的同学的信息

查询至少有一门课与学号为" 1 "的同学所学相同的同学的信息

查询和" 1 "号的同学学习的课程 完全相同的其他同学的信息

查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

检索" 1 "课程分数小于 60，按分数降序排列的学生信息

按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

查询各科成绩最高分、最低分和平均分：

查询男生、女生人数

查询名字中含有「风」字的学生信息

查询每门课程的平均成绩

查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

查询任何一门课程成绩在70分以上的姓名、课程名称和分数

查询有不及格的课程的学生信息

查询课程编号为'1'且课程成绩在80分以上的学生的学号和姓名

求每门课程的学生人数

查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

查询每门课程成绩最好的前两名，按照课程名升序，按照成绩升序

检索至少选修两门课程的学生姓名和选修课程个数

查询选修了全部课程的学生信息

查询各学生的年龄

统计每门课程的学生选修人数

查询本月过生日的学生

查询下月过生日的学生

查询本周过生日的学生

查询下周过生日的学生

---

建表

-- 创建学生基础信息表
create table Student
(
    id varchar(4) comment '学号',
    name varchar(10) comment '姓名',
    age datetime comment '年龄',
    sex char(1) comment '性别'
) comment '学生基础信息表';
-- 创建课程表
create table Course
(
    id varchar(4) comment '课程编号',
    name varchar(20) not null comment '课程名',
    T_id varchar(10) comment '代课老师'
) comment '课程表信息';
-- 创建教师信息表
create table Teacher
(
    id   varchar(10) comment '工号',
    name varchar(10) comment '姓名'
);
-- 创建学生和教师的中间表以及对应成绩
create table SC
(
    s_id varchar(4) comment '学生学号',
    c_id varchar(4) comment '教师工号',
    score decimal(18,1)
) comment '成绩表';

插入数据

-- 插入测试数据
insert into Student value ('1','赵雷','1990-01-01','男');
insert into Student values('2','钱电','1990-12-21','男');
insert into Student values('','孙风','1990-12-20','男');
insert into Student values('4','李云','1990-12-06','男');
insert into Student values('5','周梅','1991-12-01','女');
insert into Student values('6','吴兰','1992-01-01','女');
insert into Student values('7','郑竹','1989-01-01','女');
insert into Student values('8','林俊杰','1990-07-03','男');
insert into Student values('9','张三','2017-12-20','女');
insert into Student values('10','李四','2017-12-25','女');
insert into Student values('11','李四','2012-06-06','女');
insert into Student values('12','赵六','2013-06-13','女');
insert into Student values('13','孙七','2014-06-01','女');

insert into Course(id, name, T_id)
VALUES ('1','语文','2'),('2','数学','1'),('3','英语','3');

insert into Teacher
VALUES ('1','张三'),('2','李四'),('3','王五');

insert into SC values('1' , '1' , 80);
insert into SC values('1' , '3' , 99);
insert into SC values('2' , '1' , 70);
insert into SC values('2' , '2' , 60);
insert into SC values('2' , '3' , 80);
insert into SC values('3' , '1' , 80);
insert into SC values('3' , '2' , 80);
insert into SC values('3' , '3' , 80);
insert into SC values('4' , '1' , 50);
insert into SC values('4' , '2' , 30);
insert into SC values('4' , '3' , 20);
insert into SC values('5' , '1' , 76);
insert into SC values('5' , '2' , 87);
insert into SC values('6' , '1' , 31);
insert into SC values('6' , '3' , 34);
insert into SC values('7' , '2' , 89);
insert into SC values('7' , '3' , 98);

练习开始

查询学过1和2课程的学生

select s.*
from Student s
where s.id in (
    select sc1.s_id
    from SC sc1,SC sc2
    where sc1.c_id='1' 
    and sc2.c_id='2'
    and sc1.s_id=sc2.s_id);

查询学过1课程但没学过2课程的学生

select s.*
from Student s
where s.id in (
    select sc.s_id
    from SC sc
    where sc.c_id = '1'
    and sc.s_id not in (
        select sc.s_id
        from SC sc
        where sc.c_id = '2'
        )
    );

查询学过1课程但学过2课程的学生

查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

select s.id,s.name,avg(sc.score) as 平均成绩
from Student s
join SC sc on s.id=sc.s_id
group by s.id
having avg(sc.score>=60);

查询在 SC 表存在成绩的学生信息

select distinct s.*
from Student s
join SC sc on s.id=sc.s_id;

查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

select Student.id, Student.name,c.coursenumber 课程号,c.Scoresum 课程总分
from Student,(select SC.s_id, sum(SC.Score)
as Scoresum, count(SC.c_id) as coursenumber from SC
group by SC.s_id)c
where Student.id = c.s_id;

查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

select s.id,s.name,count(s.id) as 选课总数,sum(sc.score) as 总成绩
from Student s left join SC sc on sc.s_id=s.id
group by s.id;

查询「李」姓老师的数量

select count(*) from Teacher where name like '李%';

查询学过「张三」老师授课的同学的信息

select distinct Student.* 
from Student,SC
where SC.c_id=(select id from Teacher where name='张三');

查询没学过"张三"老师讲授的任一门课程的学生姓名

select * from Student
where Student.id not in (
    select SC.s_id
    from SC
    where SC.c_id in (
        select Course.id 
        from Course 
        where Course.T_id in (
            select id 
            from Teacher 
            where name='张三')
        )
    );

查询没有学全所有课程的同学的信息

select Student.* from Student
where Student.id not in (
    select SC.s_id from SC
    group by SC.s_id
    having count(SC.c_id)=(select count(*) from Course)
    );

查询至少有一门课与学号为" 1 "的同学所学相同的同学的信息

select * 
from Student 
where Student.id in (
    select SC.s_id 
    from SC 
    where SC.c_id in (
        select SC.c_id from SC where SC.s_id='1'
        )
    );

查询和" 1 "号的同学学习的课程 完全相同的其他同学的信息

select Student.* 
from Student ,SC
where Student.id=SC.s_id;

查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

select s_id 
from SC 
where score<60 
group by s_id 
having count(score)>1;

检索" 1 "课程分数小于 60，按分数降序排列的学生信息

select Student.*,SC.score 
from Student,SC
where Student.id=SC.s_id
and SC.score<60
and SC.c_id='1'
order by SC.score desc ;

按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select * from SC
left join (select s_id,avg(score) as avscore from SC group by s_id) c
on SC.s_id=c.s_id
order by avscore desc;

查询各科成绩最高分、最低分和平均分：

以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90，要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select SC.c_id,
max(SC.score) as 最高分,
min(SC.score) as 最低分,
AVG(SC.score) as 平均分,
count(*) as 选修人数,
sum(case when SC.score>=60 then 1 else 0 end)/count(*) as 及格率,
sum(case when SC.score>=70 then 1 else 0 end)/count(*) as 中等率,
sum(case when SC.score>=80 then 1 else 0 end)/count(*) as 优良率,
sum(case when SC.score>=90 then 1 else 0 end)/count(*) as 优秀率
from SC
group by SC.c_id
order by count(*) desc ,SC.c_id asc;

查询男生、女生人数

select sex,count(*) from Student group by sex;

查询名字中含有「风」字的学生信息

select * from Student where name like '%风%';

查询每门课程的平均成绩

结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

select sc.c_id,avg(sc.score) as 平均成绩
from SC sc
group by sc.c_id
order by avg(sc.score) desc ,sc.c_id;

查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

select s.id,s.name,avg(sc.score) as 平均成绩
from Student s
join SC sc on sc.s_id=s.id
group by s.id
having avg(sc.score>=85);

查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

-- 方法1
select s.name,sc.score
from SC sc
join Student s on s.id=sc.s_id
join Course c on c.id=sc.c_id
where c.name='数学' and sc.score<60;
-- 方法2
select s.name,Target.score
from Student s
join (select sc.s_id,sc.score
      from SC sc,Course c
      where sc.c_id=c.id
      and c.name='数学'
      and sc.score<60)
    as Target on s.id=Target.s_id;

查询任何一门课程成绩在70分以上的姓名、课程名称和分数

select s.name,c.name,sc.score
from SC sc
join Student s on s.id=sc.s_id
join Course c on c.id = sc.c_id
where sc.score>70;

查询有不及格的课程的学生信息

select sc.c_id as 课程编号,c.name as 课程名,s.id as 学号,s.name as 姓名,sc.score as 分数
from SC sc
join Student s on s.id=sc.s_id
join Course c on c.id=sc.c_id
where sc.score<60;

查询课程编号为'1'且课程成绩在80分以上的学生的学号和姓名

select distinct  s.id,s.name
from Student s
join SC sc on sc.s_id=s.id
join Course c on c.id=sc.s_id
where c.id='1' and sc.score>=80;

求每门课程的学生人数

select c.name as '课程名称',count(*) as '人数'
from SC sc
join Course c on sc.c_id=c.id
group by c.name;

查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

select s.*,c.name as 课程,sc.score
from Student s
left join SC sc on sc.s_id=s.id
left join Course c on sc.c_id=c.id
left join Teacher t on t.id=c.T_id
where t.name='张三'
order by sc.score desc
limit 1;

查询每门课程成绩最好的前两名，按照课程名升序，按照成绩升序

select c.name,s.name,sc1.score
from SC sc1
join  Course c on c.id=sc1.c_id
join Student s on s.id=sc1.s_id
where(
    select count(*)
    from SC sc2
    where sc1.c_id=sc2.c_id
    and sc1.score<=sc2.score
         )<=2
order by c.name ,sc1.score;

检索至少选修两门课程的学生姓名和选修课程个数

select s.name,count(distinct sc.c_id)
from Student s
left join SC sc on sc.s_id=s.id
group by s.id
having count(distinct sc.c_id)>1;

查询选修了全部课程的学生信息

select s.*
from Student s
left join SC sc on sc.s_id=s.id
group by s.id
having count(distinct sc.c_id)=(
    select count(distinct c.id)
    from Course c
    );

查询各学生的年龄

select s.name,Year(Now())-YEAR(s.age) as 年龄
from Student s;

统计每门课程的学生选修人数

select sc.c_id as '课程名称',count(*) as '选修人数'
from SC sc
left join Course c on c.id=sc.c_id
group by sc.c_id
order by count(*) desc;

查询本月过生日的学生

select *
from Student s
where MONTH(NOW())=MONTH(s.age);

查询下月过生日的学生

select *
from Student s
where MONTH(NOW())+1=MONTH(s.age);

查询本周过生日的学生

select *
from Student s
where WEEK(NOW())=WEEK(s.age);

查询下周过生日的学生

select *
from Student s
where WEEK(NOW())+1=WEEK(s.age);