程序员经验分享-hwhale

MySQL 经典练习 50 题（完美解答版）

2023-11-03

一、创建数据库和表

        数据库
                学生表 student
                课程表 course
                教师表 teacher
                成绩表 score

        表关系

        创建数据库和表
        创建数据库
drop database if exists mysql_test cascade;
create database mysql_test;
use mysql_test;
        创建学生表 student
create table student (
    s_id int,
    s_name varchar(8),
    s_birth date,
    s_sex varchar(4)
);
        插入学生数据
insert into student values
(1,'赵雷','1990-01-01','男'),
(2,'钱电','1990-12-21','男'),
(3,'孙风','1990-05-20','男'),
(4,'李云','1990-08-06','男'),
(5,'周梅','1991-12-01','女'),
(6,'吴兰','1992-03-01','女'),
(7,'郑竹','1989-07-01','女'),
(8,'王菊','1990-01-20','女');
        创建课程表 course
create table course (
    c_id int,
    c_name varchar(8),
    t_id int
);
        插入课程数据
insert into course values
(1,'语文',2),
(2,'数学',1),
(3,'英语',3);
        创建教师表 teacher
create table teacher (
    t_id int,
    t_name varchar(8)
);
        插入教师数据
insert into teacher values
(1,'张三'),
(2,'李四'),
(3,'王五');
        创建成绩表 score
create table score (
    s_id int,
    c_id int,
    s_score int
);
        插入成绩数据
insert into score values
(1,1,80),
(1,2,90),
(1,3,99),
(2,1,70),
(2,2,60),
(2,3,65),
(3,1,80),
(3,2,80),
(3,3,80),
(4,1,50),
(4,2,30),
(4,3,40),
(5,1,76),
(5,2,87),
(6,1,31),
(6,3,34),
(7,2,89),
(7,3,98);

二、经典练习 50 题
 

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select s.*,sc1.s_score as score_01,sc2.s_score as score_02
from student s
inner join (
    select * from score where c_id = 1
) sc1
on s.s_id = sc1.s_id
inner join (
    select * from score where c_id = 2
) sc2
on s.s_id = sc2.s_id
where sc1.s_score > sc2.s_score;

+------+--------+------------+-------+----------+----------+
| s_id | s_name | s_birth    | s_sex | score_01 | score_02 |
+------+--------+------------+-------+----------+----------+
|    2 | 钱电   | 1990-12-21 | 男    |       70 |       60 |
|    4 | 李云   | 1990-08-06 | 男    |       50 |       30 |
+------+--------+------------+-------+----------+----------+

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
 
select s.*,sc1.s_score as score_01,sc2.s_score as score_02
from student s
inner join (
    select * from score where c_id = 1
) sc1
on s.s_id = sc1.s_id
inner join (
    select * from score where c_id = 2
) sc2
on s.s_id = sc2.s_id
where sc1.s_score < sc2.s_score;

+------+--------+------------+-------+----------+----------+
| s_id | s_name | s_birth    | s_sex | score_01 | score_02 |
+------+--------+------------+-------+----------+----------+
|    1 | 赵雷   | 1990-01-01 | 男    |       80 |       90 |
|    5 | 周梅   | 1991-12-01 | 女    |       76 |       87 |
+------+--------+------------+-------+----------+----------+

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select s.s_id,s.s_name,round(avg_score, 2) as avg_score
from student s
inner join (
    select s_id,avg(s_score) as avg_score
    from score
    group by s_id
    having avg_score >= 60
) t1
on s.s_id = t1.s_id;

+------+--------+-----------+
| s_id | s_name | avg_score |
+------+--------+-----------+
|    1 | 赵雷   | 89.67     |
|    2 | 钱电   | 65.00     |
|    3 | 孙风   | 80.00     |
|    5 | 周梅   | 81.50     |
|    7 | 郑竹   | 93.50     |
+------+--------+-----------+


4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)

select s.s_id,s.s_name,ifnull(round(avg_score, 2), 0) as avg_score
from student s
left join (
    select s_id,avg(s_score) as avg_score
    from score
    group by s_id
) t1
on s.s_id = t1.s_id
where avg_score is null or avg_score < 60;

+------+--------+-----------+
| s_id | s_name | avg_score |
+------+--------+-----------+
|    4 | 李云   | 40.00     |
|    6 | 吴兰   | 32.50     |
|    8 | 王菊   | 0.00      |
+------+--------+-----------+

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select s.s_id,s.s_name,ifnull(cnt_course, 0) as cnt_course,ifnull(sum_score, 0) as sum_score
from student s
left join (
    select s_id,count(*) as cnt_course,sum(s_score) as sum_score
    from score
    group by s_id
) t1
on s.s_id = t1.s_id;

+------+--------+------------+-----------+
| s_id | s_name | cnt_course | sum_score |
+------+--------+------------+-----------+
|    1 | 赵雷   |          3 | 269       |
|    2 | 钱电   |          3 | 195       |
|    3 | 孙风   |          3 | 240       |
|    4 | 李云   |          3 | 120       |
|    5 | 周梅   |          2 | 163       |
|    6 | 吴兰   |          2 | 65        |
|    7 | 郑竹   |          2 | 187       |
|    8 | 王菊   |          0 | 0         |
+------+--------+------------+-----------+

6、查询"李"姓老师的数量
select count(*) as cnt_name_li
from teacher
where t_name like '李%';

+-------------+
| cnt_name_li |
+-------------+
|           1 |
+-------------+

7、询学过"张三"老师授课的同学的信息
select s.*
from student s
where s_id in (
    select distinct s_id
    from score sc
    inner join (
        select c_id
        from course c
        inner join teacher t on c.t_id = t.t_id
        where t_name = '张三'
    ) t1
    on sc.c_id = t1.c_id
);

+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
|    1 | 赵雷   | 1990-01-01 | 男    |
|    2 | 钱电   | 1990-12-21 | 男    |
|    3 | 孙风   | 1990-05-20 | 男    |
|    4 | 李云   | 1990-08-06 | 男    |
|    5 | 周梅   | 1991-12-01 | 女    |
|    7 | 郑竹   | 1989-07-01 | 女    |
+------+--------+------------+-------+

8、查询没学过"张三"老师授课的同学的信息
select s.*
from student s
where s_id not in (
    select distinct s_id
    from score sc
    inner join (
        select c_id
        from course c
        inner join teacher t on c.t_id = t.t_id
        where t_name = '张三'
    ) t1
    on sc.c_id = t1.c_id
);

+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
|    6 | 吴兰   | 1992-03-01 | 女    |
|    8 | 王菊   | 1990-01-20 | 女    |
+------+--------+------------+-------+

9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select s.*
from student s
where s_id in (
    select s_id
    from score
    where c_id in (1,2)
    group by s_id
    having count(*) = 2
);

+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
|    1 | 赵雷   | 1990-01-01 | 男    |
|    2 | 钱电   | 1990-12-21 | 男    |
|    3 | 孙风   | 1990-05-20 | 男    |
|    4 | 李云   | 1990-08-06 | 男    |
|    5 | 周梅   | 1991-12-01 | 女    |
+------+--------+------------+-------+

10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select s.*
from student s
where s_id in (
    select s_id
    from score
    where c_id = 1
    and s_id not in (
        select s_id from score where c_id = 2
    )
);

+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
|    6 | 吴兰   | 1992-03-01 | 女    |
+------+--------+------------+-------+

11、查询没有学全所有课程的同学的信息
select *
from student
where s_id not in (
    select s_id
    from score
    group by s_id
    having count(*) = (
        select count(*) from course
    )
);

+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
|    5 | 周梅   | 1991-12-01 | 女    |
|    6 | 吴兰   | 1992-03-01 | 女    |
|    7 | 郑竹   | 1989-07-01 | 女    |
|    8 | 王菊   | 1990-01-20 | 女    |
+------+--------+------------+-------+

12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
select *
from student
where s_id in (
    select distinct s_id
    from score s
    inner join (
        select c_id from score where s_id = 1
    ) t1
    on s.c_id = t1.c_id
);

+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
|    1 | 赵雷   | 1990-01-01 | 男    |
|    2 | 钱电   | 1990-12-21 | 男    |
|    3 | 孙风   | 1990-05-20 | 男    |
|    4 | 李云   | 1990-08-06 | 男    |
|    5 | 周梅   | 1991-12-01 | 女    |
|    6 | 吴兰   | 1992-03-01 | 女    |
|    7 | 郑竹   | 1989-07-01 | 女    |
+------+--------+------------+-------+


 

13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
select *
from student
where s_id in (
     select s_id
     from score s
     inner join (
        select c_id from score where s_id = 1
     ) t1
     on s.c_id = t1.c_id
     where s_id != 1
     group by s_id having count(*) = (
        select count(*) from score where s_id = 1
     )
);

+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
|    2 | 钱电   | 1990-12-21 | 男    |
|    3 | 孙风   | 1990-05-20 | 男    |
|    4 | 李云   | 1990-08-06 | 男    |
+------+--------+------------+-------+

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

select s_name
from student
where s_id not in (
    select s_id
    from score sc
    inner join (
        select c_id
        from course c
        inner join teacher t on c.t_id = t.t_id
        where t_name = '张三'
    ) t1
    on sc.c_id = t1.c_id
);

+--------+
| s_name |
+--------+
| 吴兰   |
| 王菊   |
+--------+

15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select s.s_id,s.s_name,round(avg(s_score), 2) as avg_score
from student s
inner join (
    select s_id
    from score
    where s_score < 60
    group by s_id
    having count(*) >= 2
) t1
on s.s_id = t1.s_id
inner join score sc on s.s_id = sc.s_id
group by s.s_id;

+------+--------+-----------+
| s_id | s_name | avg_score |
+------+--------+-----------+
|    4 | 李云   | 40.00     |
|    6 | 吴兰   | 32.50     |
+------+--------+-----------+

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

select s.*,s_score as score_01
from student s
inner join (
    select s_id,s_score
    from score
    where c_id = 1
    and s_score < 60
) t1
on s.s_id = t1.s_id
order by score_01 desc;

+------+--------+------------+-------+----------+
| s_id | s_name | s_birth    | s_sex | score_01 |
+------+--------+------------+-------+----------+
|    4 | 李云   | 1990-08-06 | 男    |       50 |
|    6 | 吴兰   | 1992-03-01 | 女    |       31 |
+------+--------+------------+-------+----------+

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select
    s.s_id as 学号,s.s_name as 姓名,
    sum(case c_id when 1 then s_score else 0 end) as 语文,
    sum(case c_id when 2 then s_score else 0 end) as 数学,
    sum(case c_id when 3 then s_score else 0 end) as 英语,
    ifnull(round(avg(s_score), 2), 0) as 平均成绩
from student s
left join score sc on s.s_id = sc.s_id
group by s.s_id
order by 平均成绩 desc;

+------+------+------+------+------+----------+
| 学号 | 姓名 | 语文 | 数学 | 英语 | 平均成绩 |
+------+------+------+------+------+----------+
|    7 | 郑竹 | 0    | 89   | 98   | 93.50    |
|    1 | 赵雷 | 80   | 90   | 99   | 89.67    |
|    5 | 周梅 | 76   | 87   | 0    | 81.50    |
|    3 | 孙风 | 80   | 80   | 80   | 80.00    |
|    2 | 钱电 | 70   | 60   | 65   | 65.00    |
|    4 | 李云 | 50   | 30   | 40   | 40.00    |
|    6 | 吴兰 | 31   | 0    | 34   | 32.50    |
|    8 | 王菊 | 0    | 0    | 0    | 0.00     |
+------+------+------+------+------+----------+

18、查询各科成绩最高分、最低分和平均分，以如下形式显示：
课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
– 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
select
    c.c_id as 课程ID,c.c_name as 课程name,
    max(s_score) as 最高分,
    min(s_score) as 最低分,
    round(avg(s_score), 2) as 平均分,
    concat(round(sum(case when s_score >= 60 then 1 else 0 end) / count(*) * 100, 2), '%') as 及格率,
    concat(round(sum(case when s_score between 70 and 80 then 1 else 0 end) / count(*) * 100, 2), '%') as 中等率,
    concat(round(sum(case when s_score between 80 and 90 then 1 else 0 end) / count(*) * 100, 2), '%') as 优良率,
    concat(round(sum(case when s_score >= 90 then 1 else 0 end) / count(*) * 100, 2), '%') as 优秀率
from course c
inner join score s on c.c_id = s.c_id
group by c.c_id;

+--------+----------+--------+--------+--------+--------+--------+--------+--------+
| 课程ID | 课程name | 最高分 | 最低分 | 平均分 | 及格率 | 中等率 | 优良率 | 优秀率 |
+--------+----------+--------+--------+--------+--------+--------+--------+--------+
|      1 | 语文     |     80 |     31 | 64.50  | 66.67% | 66.67% | 33.33% | 0.00%  |
|      2 | 数学     |     90 |     30 | 72.67  | 83.33% | 16.67% | 66.67% | 16.67% |
|      3 | 英语     |     99 |     34 | 69.33  | 66.67% | 16.67% | 16.67% | 33.33% |
+--------+----------+--------+--------+--------+--------+--------+--------+--------+


 

19、按各科成绩进行排序，并显示排名
select
    (@i := case when @pre_group_id = c_id then @i + 1 else 1 end) as rank,
    (@pre_group_id := c_id) as c_id,
    c_name,s_id,s_name,s_score
from (select @i := 0, @pre_group_id := 1) var
cross join (
    select c.c_id,c.c_name,s.s_id,s.s_name,s_score
    from score sc
    inner join student s on sc.s_id = s.s_id
    inner join course c on sc.c_id = c.c_id
    group by c.c_id,s.s_id
    order by c.c_id,s_score desc
) t1;

+------+------+--------+------+--------+---------+
| rank | c_id | c_name | s_id | s_name | s_score |
+------+------+--------+------+--------+---------+
|    1 |    1 | 语文   |    1 | 赵雷   |      80 |
|    2 |    1 | 语文   |    3 | 孙风   |      80 |
|    3 |    1 | 语文   |    5 | 周梅   |      76 |
|    4 |    1 | 语文   |    2 | 钱电   |      70 |
|    5 |    1 | 语文   |    4 | 李云   |      50 |
|    6 |    1 | 语文   |    6 | 吴兰   |      31 |
|    1 |    2 | 数学   |    1 | 赵雷   |      90 |
|    2 |    2 | 数学   |    7 | 郑竹   |      89 |
|    3 |    2 | 数学   |    5 | 周梅   |      87 |
|    4 |    2 | 数学   |    3 | 孙风   |      80 |
|    5 |    2 | 数学   |    2 | 钱电   |      60 |
|    6 |    2 | 数学   |    4 | 李云   |      30 |
|    1 |    3 | 英语   |    1 | 赵雷   |      99 |
|    2 |    3 | 英语   |    7 | 郑竹   |      98 |
|    3 |    3 | 英语   |    3 | 孙风   |      80 |
|    4 |    3 | 英语   |    2 | 钱电   |      65 |
|    5 |    3 | 英语   |    4 | 李云   |      40 |
|    6 |    3 | 英语   |    6 | 吴兰   |      34 |
+------+------+--------+------+--------+---------+

20、查询学生的总成绩并进行排名
select (@i := @i + 1) as rank,t2.*
from (select  @i := 0) var
cross join (
    select s.s_id,s.s_name,sum_score
    from student s
    inner join (
        select s_id,sum(s_score) as sum_score
        from score
        group by s_id
    ) t1
    on s.s_id = t1.s_id
    order by sum_score desc
) t2;

+------+------+--------+-----------+
| rank | s_id | s_name | sum_score |
+------+------+--------+-----------+
|    1 |    1 | 赵雷   | 269       |
|    2 |    3 | 孙风   | 240       |
|    3 |    2 | 钱电   | 195       |
|    4 |    7 | 郑竹   | 187       |
|    5 |    5 | 周梅   | 163       |
|    6 |    4 | 李云   | 120       |
|    7 |    6 | 吴兰   | 65        |
+------+------+--------+-----------+

21、查询不同老师所教不同课程平均分从高到低显示
select t.t_id,t.t_name,c.c_id,c.c_name,round(avg(s_score), 2) as avg_score
from score sc
inner join course c on sc.c_id = c.c_id
inner join teacher t on t.t_id = c.t_id
group by t.t_id,c.c_id
order by t.t_id,avg_score desc;

+------+--------+------+--------+-----------+
| t_id | t_name | c_id | c_name | avg_score |
+------+--------+------+--------+-----------+
|    1 | 张三   |    2 | 数学   | 72.67     |
|    2 | 李四   |    1 | 语文   | 64.50     |
|    3 | 王五   |    3 | 英语   | 69.33     |
+------+--------+------+--------+-----------+

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
select *
from (
    select
        (@i := case when @pre_group_id = c_id then @i + 1 else 1 end) as rank,
        (@pre_group_id := c_id) as c_id,
        c_name,s_id,s_name,s_birth,s_sex,s_score
    from (select @i := 0, @pre_group_id := 1) var
    cross join (
        select c.c_id,c.c_name,s.*,s_score
        from score sc
        inner join student s on sc.s_id = s.s_id
        inner join course c on sc.c_id = c.c_id
        group by c.c_id,s.s_id
        order by c.c_id,s_score desc
    ) t1
) t2
where rank in (2,3);

+------+------+--------+------+--------+------------+-------+---------+
| rank | c_id | c_name | s_id | s_name | s_birth    | s_sex | s_score |
+------+------+--------+------+--------+------------+-------+---------+
|    2 |    1 | 语文   |    3 | 孙风   | 1990-05-20 | 男    |      80 |
|    3 |    1 | 语文   |    5 | 周梅   | 1991-12-01 | 女    |      76 |
|    2 |    2 | 数学   |    7 | 郑竹   | 1989-07-01 | 女    |      89 |
|    3 |    2 | 数学   |    5 | 周梅   | 1991-12-01 | 女    |      87 |
|    2 |    3 | 英语   |    7 | 郑竹   | 1989-07-01 | 女    |      98 |
|    3 |    3 | 英语   |    3 | 孙风   | 1990-05-20 | 男    |      80 |
+------+------+--------+------+--------+------------+-------+---------+

23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
select
    c.c_id as 课程编号,c.c_name as 课程名称,
    sum(case when s_score between 85 and 100 then 1 else 0 end) as '[100-85]',
    concat(round(sum(case when s_score between 85 and 100 then 1 else 0 end) / count(*) * 100, 2), '%') as 百分比,
    sum(case when s_score between 70 and 85 then 1 else 0 end) as '[85-70]',
    concat(round(sum(case when s_score between 70 and 85 then 1 else 0 end) / count(*) * 100, 2), '%') as 百分比,
    sum(case when s_score between 60 and 70 then 1 else 0 end) as '[70-60]',
    concat(round(sum(case when s_score between 60 and 70 then 1 else 0 end) / count(*) * 100, 2), '%') as 百分比,
    sum(case when s_score between 0 and 60 then 1 else 0 end) as '[0-60]',
    concat(round(sum(case when s_score between 0 and 60 then 1 else 0 end) / count(*) * 100, 2), '%') as 百分比
from course c
inner join score sc
on c.c_id = sc.c_id
group by c.c_id;

+----------+----------+----------+--------+---------+--------+---------+--------+--------+--------+
| 课程编号 | 课程名称 | [100-85] | 百分比 | [85-70] | 百分比 | [70-60] | 百分比 | [0-60] | 百分比 |
+----------+----------+----------+--------+---------+--------+---------+--------+--------+--------+
|        1 | 语文     | 0        | 0.00%  | 4       | 66.67% | 1       | 16.67% | 2      | 33.33% |
|        2 | 数学     | 3        | 50.00% | 1       | 16.67% | 1       | 16.67% | 2      | 33.33% |
|        3 | 英语     | 2        | 33.33% | 1       | 16.67% | 1       | 16.67% | 2      | 33.33% |
+----------+----------+----------+--------+---------+--------+---------+--------+--------+--------+

24、查询学生平均成绩及其名次
select (@i := @i + 1) as rank,t2.*
from (select  @i := 0) var
cross join (
    select s.s_id,s.s_name,avg_score
    from student s
    inner join (
        select s_id,round(avg(s_score), 2) as avg_score
        from score
        group by s_id
    ) t1
    on s.s_id = t1.s_id
    order by avg_score desc
) t2;

+------+------+--------+-----------+
| rank | s_id | s_name | avg_score |
+------+------+--------+-----------+
|    1 |    7 | 郑竹   | 93.50     |
|    2 |    1 | 赵雷   | 89.67     |
|    3 |    5 | 周梅   | 81.50     |
|    4 |    3 | 孙风   | 80.00     |
|    5 |    2 | 钱电   | 65.00     |
|    6 |    4 | 李云   | 40.00     |
|    7 |    6 | 吴兰   | 32.50     |
+------+------+--------+-----------+

25、查询各科成绩前三名的记录

方法 1：内外关联

select c.c_id,c.c_name,s.s_id,s.s_name,s_score
from (
    select *
    from score sc
    where (
        select count(*)
        from score sc1
        where sc.c_id = sc1.c_id
        and sc.s_score < sc1.s_score
    ) < 3
)
t1
inner join student s on t1.s_id = s.s_id 
inner join course c on t1.c_id = c.c_id 
order by c.c_id,s_score desc;

+------+--------+------+--------+---------+
| c_id | c_name | s_id | s_name | s_score |
+------+--------+------+--------+---------+
|    1 | 语文   |    1 | 赵雷   |      80 |
|    1 | 语文   |    3 | 孙风   |      80 |
|    1 | 语文   |    5 | 周梅   |      76 |
|    2 | 数学   |    1 | 赵雷   |      90 |
|    2 | 数学   |    7 | 郑竹   |      89 |
|    2 | 数学   |    5 | 周梅   |      87 |
|    3 | 英语   |    1 | 赵雷   |      99 |
|    3 | 英语   |    7 | 郑竹   |      98 |
|    3 | 英语   |    3 | 孙风   |      80 |
+------+--------+------+--------+---------+

方法 2：序号筛选

select *
from (
    select
        (@i := case when @pre_group_id = c_id then @i + 1 else 1 end) as rank,
        (@pre_group_id := c_id) as c_id,
        c_name,s_id,s_name,s_score
    from (select @i := 0, @pre_group_id := 1) var
    cross join (
        select c.c_id,c.c_name,s.s_id,s.s_name,s_score
        from score sc
        inner join student s on sc.s_id = s.s_id
        inner join course c on sc.c_id = c.c_id
        group by c.c_id,s.s_id
        order by c.c_id,s_score desc
    ) t1
) t2
where rank <= 3;

+------+------+--------+------+--------+---------+
| rank | c_id | c_name | s_id | s_name | s_score |
+------+------+--------+------+--------+---------+
|    1 |    1 | 语文   |    1 | 赵雷   |      80 |
|    2 |    1 | 语文   |    3 | 孙风   |      80 |
|    3 |    1 | 语文   |    5 | 周梅   |      76 |
|    1 |    2 | 数学   |    1 | 赵雷   |      90 |
|    2 |    2 | 数学   |    7 | 郑竹   |      89 |
|    3 |    2 | 数学   |    5 | 周梅   |      87 |
|    1 |    3 | 英语   |    1 | 赵雷   |      99 |
|    2 |    3 | 英语   |    7 | 郑竹   |      98 |
|    3 |    3 | 英语   |    3 | 孙风   |      80 |
+------+------+--------+------+--------+---------+

26、查询每门课程被选修的学生数
select c.c_id,c.c_name,cnt_student
from course c
inner join (
    select c_id,count(*) as cnt_student
    from score
    group by c_id
) t1
on c.c_id = t1.c_id;

+------+--------+-------------+
| c_id | c_name | cnt_student |
+------+--------+-------------+
|    1 | 语文   |           6 |
|    2 | 数学   |           6 |
|    3 | 英语   |           6 |
+------+--------+-------------+

27、查询出只有两门课程的全部学生的学号和姓名
select s_id,s_name
from student
where s_id in (
    select s_id
    from score
    group by s_id
    having count(*) = 2
);

+------+--------+
| s_id | s_name |
+------+--------+
|    5 | 周梅   |
|    6 | 吴兰   |
|    7 | 郑竹   |
+------+--------+

28、查询男生、女生人数
select s_sex,count(*) as cnt_sex
from student
group by s_sex;

+-------+---------+
| s_sex | cnt_sex |
+-------+---------+
| 女    |       4 |
| 男    |       4 |
+-------+---------+

29、查询名字中含有"风"字的学生信息
select *
from student
where s_name like '%风%';

+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
|    3 | 孙风   | 1990-05-20 | 男    |
+------+--------+------------+-------+

30、查询同名同性学生名单，并统计同名人数
select *,count(*) as cnt_student
from student
group by s_name,s_sex
having cnt_student > 1;

+-----------+
| Empty set |
+-----------+

31、查询1990年出生的学生名单
select *
from student
where year(s_birth) = '1990';

+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
|    1 | 赵雷   | 1990-01-01 | 男    |
|    2 | 钱电   | 1990-12-21 | 男    |
|    3 | 孙风   | 1990-05-20 | 男    |
|    4 | 李云   | 1990-08-06 | 男    |
|    8 | 王菊   | 1990-01-20 | 女    |
+------+--------+------------+-------+

32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select c.c_id,c.c_name,avg_score
from course c
inner join (
    select c_id,round(avg(s_score), 2) as avg_score
    from score
    group by c_id
) t1
on c.c_id = t1.c_id
order by avg_score desc,c.c_id;

+------+--------+-----------+
| c_id | c_name | avg_score |
+------+--------+-----------+
|    2 | 数学   | 72.67     |
|    3 | 英语   | 69.33     |
|    1 | 语文   | 64.50     |
+------+--------+-----------+

33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select s.s_id,s.s_name,avg_score
from student s
inner join (
    select s_id,round(avg(s_score), 2) as avg_score
    from score
    group by s_id
    having avg_score >= 85
) t1
on s.s_id = t1.s_id;

+------+--------+-----------+
| s_id | s_name | avg_score |
+------+--------+-----------+
|    1 | 赵雷   | 89.67     |
|    7 | 郑竹   | 93.50     |
+------+--------+-----------+


 

34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
select s_name,s_score
from student s
inner join (
    select s_id,s_score
    from score sc
    inner join course c on sc.c_id = c.c_id
    where c_name = '数学'
    and s_score < 60
) t1
on s.s_id = t1.s_id;

+--------+---------+
| s_name | s_score |
+--------+---------+
| 李云   |      30 |
+--------+---------+

35、查询所有学生的课程及分数情况
select
    s.s_id,s.s_name,
    sum(case c_id when 1 then s_score else 0 end) as 语文,
    sum(case c_id when 2 then s_score else 0 end) as 数学,
    sum(case c_id when 3 then s_score else 0 end) as 英语
from student s
left join score sc on s.s_id = sc.s_id
group by s.s_id;

+------+--------+------+------+------+
| s_id | s_name | 语文 | 数学 | 英语 |
+------+--------+------+------+------+
|    1 | 赵雷   | 80   | 90   | 99   |
|    2 | 钱电   | 70   | 60   | 65   |
|    3 | 孙风   | 80   | 80   | 80   |
|    4 | 李云   | 50   | 30   | 40   |
|    5 | 周梅   | 76   | 87   | 0    |
|    6 | 吴兰   | 31   | 0    | 34   |
|    7 | 郑竹   | 0    | 89   | 98   |
|    8 | 王菊   | 0    | 0    | 0    |
+------+--------+------+------+------+

36、查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数
select s_name,c_name,s_score
from score sc
inner join student s on sc.s_id =  s.s_id
inner join course c on sc.c_id = c.c_id
where s_score > 70
group by s.s_id;

+--------+--------+---------+
| s_name | c_name | s_score |
+--------+--------+---------+
| 赵雷   | 语文   |      80 |
| 孙风   | 语文   |      80 |
| 周梅   | 语文   |      76 |
| 郑竹   | 数学   |      89 |
+--------+--------+---------+

37、查询课程不及格的学生
select s.s_id,s.s_name,c.c_id,c.c_name,s_score
from score sc
inner join student s on sc.s_id =  s.s_id
inner join course c on sc.c_id = c.c_id
where s_score < 60;

+------+--------+------+--------+---------+
| s_id | s_name | c_id | c_name | s_score |
+------+--------+------+--------+---------+
|    4 | 李云   |    1 | 语文   |      50 |
|    4 | 李云   |    2 | 数学   |      30 |
|    4 | 李云   |    3 | 英语   |      40 |
|    6 | 吴兰   |    1 | 语文   |      31 |
|    6 | 吴兰   |    3 | 英语   |      34 |
+------+--------+------+--------+---------+

38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
select s.s_id,s.s_name
from student s
where s_id in (
    select s_id
    from score
    where c_id = 1
    and s_score >= 80
);

+------+--------+
| s_id | s_name |
+------+--------+
|    1 | 赵雷   |
|    3 | 孙风   |
+------+--------+

39、求每门课程的学生人数
select c.c_id,c.c_name,cnt_student
from course c
inner join (
    select c_id,count(*) as cnt_student
    from score
    group by c_id
) t1
on c.c_id = t1.c_id;

+------+--------+-------------+
| c_id | c_name | cnt_student |
+------+--------+-------------+
|    1 | 语文   |           6 |
|    2 | 数学   |           6 |
|    3 | 英语   |           6 |
+------+--------+-------------+

40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
select s.*,s_score as max_score
from student s
inner join (
    select s_id,s_score
    from score sc
    inner join (
        select c_id
        from course c
        inner join teacher t on c.t_id = t.t_id
        where t_name = '张三'
    ) t1
    on sc.c_id = t1.c_id
    order by s_score desc
    limit 1
) t2
on s.s_id = t2.s_id;

+------+--------+------------+-------+-----------+
| s_id | s_name | s_birth    | s_sex | max_score |
+------+--------+------------+-------+-----------+
|    1 | 赵雷   | 1990-01-01 | 男    |        90 |
+------+--------+------------+-------+-----------+

41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select *
from score
where s_score in (
    select s_score
    from score
    group by s_score
    having count(*) > 1
);

+------+------+---------+
| s_id | c_id | s_score |
+------+------+---------+
|    1 |    1 |      80 |
|    3 |    1 |      80 |
|    3 |    2 |      80 |
|    3 |    3 |      80 |
+------+------+---------+

42、查询每门课程成绩最好的前三名

方法 1：内外关联

select c.c_id,c.c_name,s.s_id,s.s_name,s_score
from (
    select *
    from score sc
    where (
        select count(*)
        from score sc1
        where sc.c_id = sc1.c_id
        and sc.s_score < sc1.s_score
    ) < 3
) t1
inner join student s on t1.s_id = s.s_id
inner join course c on t1.c_id = c.c_id
order by c.c_id,s_score desc;

+------+--------+------+--------+---------+
| c_id | c_name | s_id | s_name | s_score |
+------+--------+------+--------+---------+
|    1 | 语文   |    1 | 赵雷   |      80 |
|    1 | 语文   |    3 | 孙风   |      80 |
|    1 | 语文   |    5 | 周梅   |      76 |
|    2 | 数学   |    1 | 赵雷   |      90 |
|    2 | 数学   |    7 | 郑竹   |      89 |
|    2 | 数学   |    5 | 周梅   |      87 |
|    3 | 英语   |    1 | 赵雷   |      99 |
|    3 | 英语   |    7 | 郑竹   |      98 |
|    3 | 英语   |    3 | 孙风   |      80 |
+------+--------+------+--------+---------+

方法 2：序号筛选

select *
from (
    select
        (@i := case when @pre_group_id = c_id then @i + 1 else 1 end) as rank,
        (@pre_group_id := c_id) as c_id,
        c_name,s_id,s_name,s_score
    from (select @i := 0, @pre_group_id := 1) var
    cross join (
        select c.c_id,c.c_name,s.s_id,s.s_name,s_score
        from score sc
        inner join student s on sc.s_id = s.s_id
        inner join course c on sc.c_id = c.c_id
        group by c.c_id,s.s_id
        order by c.c_id,s_score desc
    ) t1
) t2
where rank <= 3;

+------+------+--------+------+--------+---------+
| rank | c_id | c_name | s_id | s_name | s_score |
+------+------+--------+------+--------+---------+
|    1 |    1 | 语文   |    1 | 赵雷   |      80 |
|    2 |    1 | 语文   |    3 | 孙风   |      80 |
|    3 |    1 | 语文   |    5 | 周梅   |      76 |
|    1 |    2 | 数学   |    1 | 赵雷   |      90 |
|    2 |    2 | 数学   |    7 | 郑竹   |      89 |
|    3 |    2 | 数学   |    5 | 周梅   |      87 |
|    1 |    3 | 英语   |    1 | 赵雷   |      99 |
|    2 |    3 | 英语   |    7 | 郑竹   |      98 |
|    3 |    3 | 英语   |    3 | 孙风   |      80 |
+------+------+--------+------+--------+---------+

43、统计每门课程的学生选修人数（超过5人的课程才统计）

– 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select c.c_id,cnt_student
from course c
inner join (
    select c_id,count(*) as cnt_student
    from score
    group by c_id
    having cnt_student > 5
) t1
on c.c_id = t1.c_id
order by cnt_student desc,c.c_id;

+------+-------------+
| c_id | cnt_student |
+------+-------------+
|    1 |           6 |
|    2 |           6 |
|    3 |           6 |
+------+-------------+

44、检索至少选修两门课程的学生学号
select s_id
from score
group by s_id
having count(*) >= 2;

+------+
| s_id |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
+------+

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

select *
from student
where s_id in (
    select s_id
    from score
    group by s_id
    having count(*) = (
        select count(*) from course
    )
);

+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
|    1 | 赵雷   | 1990-01-01 | 男    |
|    2 | 钱电   | 1990-12-21 | 男    |
|    3 | 孙风   | 1990-05-20 | 男    |
|    4 | 李云   | 1990-08-06 | 男    |
+------+--------+------------+-------+

46、查询各学生的年龄(周岁)

– 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

select
    s_id,s_name,s_birth,
    if (
        month(current_date()) < month(s_birth) or (month(current_date()) = month(s_birth) and day(current_date()) < day(s_birth)),
        year(current_date()) - year(s_birth) -1,
        year(current_date()) - year(s_birth)
    )
    as s_age
from student;
47、查询本周过生日的学生
select *
from student
where
    datediff(concat(year(current_date()), date_format(s_birth, '-%m-%d')), current_date())
    between 0 and 7
or
    datediff(concat(year(current_date()) + 1, date_format(s_birth, '-%m-%d')), current_date())
    between 0 and 7;
48、查询下周过生日的学生
select *
from student
where
    datediff(concat(year(current_date()), date_format(s_birth, '-%m-%d')), current_date())
    between 7 and 14
or
    datediff(concat(year(current_date()) + 1, date_format(s_birth, '-%m-%d')), current_date())
    between 7 and 14;

49、查询本月过生日的学生

select *
from student
where month(s_birth) = month(current_date());

50、查询12月份过生日的学生

select *
from student
where month(s_birth) = 12;

+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
|    2 | 钱电   | 1990-12-21 | 男    |
|    5 | 周梅   | 1991-12-01 | 女    |
+------+--------+------------+-------+

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

数据库

MySQL 经典练习 50 题（完美解答版） 的相关文章

  • Java 根据经纬度 角度 距离求另一个点坐标

    度换成弧度 param Float d 度 return Float 弧度 private static double rad double d return d Math PI 180 0 弧度换成度 param Float x 弧度 r

  • file_include(攻防世界)

    使用php filter 发现不行 猜测应该被过滤了 继续尝试 发现read base64 encode等关键字符被过滤了 了解到php中有两种转换器 发现string被过滤 只能使用convert了 convert 过滤器支持conver

  • Android异常:android.os.NetworkOnMainThreadException

    Android 4 1项目 使用新浪微博分享时报 android os NetworkOnMainThreadException 网上搜索后知道是因为版本问题 在4 0之后在主线程里面执行Http请求都会报这个错 也许是怕Http请求时间太

  • ReferenceError: fetch is not defined

    在使用fetch时 报错fetch is not find 根据https stackoverflow com questions 48433783 referenceerror fetch is not defined的回答 通过安装 使

随机推荐

  • 开源介绍

    一 什么是开源 开源 Open Source 开放源码 被非赢利软件组织 美国的Open Source Initiative协会 注册为认证标记 并对其进行了正式的定义 用于描述那些源码可以被公众使用的软件 并且此软件的使用 修改和发行也不

  • HDU - 1024 Max Sum Plus Plus（区间dp）

    区间dp 题意 在n个数里选出连续的m组数使其和最大 思路 dp i j 表示分i个组时前j个数的最大值 所以有递推方程dp i j max dp i 1 k w j dp i j 1 w j 其中k取1 2 3 j 1 把第j个数当做新的

  • 目标检测（三）损失函数

    目标检测 三 损失函数 开始 一 匹配策略 二 损失函数 三 Hard negative mining 总结 开始 内容参考 Datawhale Task03 化劲儿 损失函数设计 一 匹配策略 我们要想让其预测类别和目标框信息 我们先要知

  • 如何使用multipart/form-data格式上传文件

    有时 在网络编程过程中需要向服务器上传文件 Multipart form data是上传文件的一种方式 Multipart form data其实就是浏览器用表单上传文件的方式 最常见的情境是 在写邮件时 向邮件后添加附件 附件通常使用表单

  • Django安装操作教程

    一 环境准备 确保已安装好python和pycharm工具 二 django安装并配置环境变量 方法一 cmd中命令安装 pip install i https pypi douban com simple django 或 指定相应的dj

  • tr字符使用

    当我们把文件从Windows传到Linux环境时候 常常在每一行的末尾 会出现一些 M的字符 而这些字符会影响文件的正常读写和执行 要去掉这些 M 字符 有很多种办法 比如直接dox2unix 也可以使用一些命令去处理 比如 删除Windo

  • 406. Queue Reconstruction by Height

    class Solution public vector

  • c++学习笔记二十——派生类的构造函数，复制构造函数和析构函数

    在讲派生类的构造和析构函数时候我们先介绍类的兼容性 类的兼容性 类的兼容性是指在需要基类对象的任何地方都可以使用派生类来替代 通过继承 派生类得到了除了基类构造函数 复制函数中的所有成员 这样公有派生类实际具备了基类所有的功能 凡是基类所能

  • 基于 BEM 规范实现简单的全局 scss

    该文章是在学习 小满vue3 课程的随堂记录 示例均采用

  • One PUNCH Man——变量显著性检验

    文章目录 显著性检验简介 t检验 单侧检验与双侧检验 区别在于是否知道标准 确定P值和做出推断结论 T检验例子 栗子no 1 栗子No 2 F检验 判断一个变量是否显著 我们一般采用T检验和F检验的方式 显著性检验简介 假设检验也叫显著性检

  • STM32单片机颜色识别分拣系统颜色名称显示2路舵机分拣

    实践制作DIY GC0120 颜色识别分拣系统 一 功能说明 基于STM32单片机设计 颜色识别分拣系统 功能介绍 硬件组成 STM32F103C系列最小系统单片机 颜色识别模块 2路舵机 2个按键 LCD1602显示器 1 可以识别颜色

  • Python 字符串

    原始字符串 print r n t n t 续行符 name woshi abc print name name woshi abc print name woshiabc 三引号 可直接跨行书写 用于注释文档 字符串拼接 str1 str

  • lintcode 1692. 组队打怪

    你现在有n个英雄 每个英雄的战斗力为atk1 你要用这些英雄去对付n个怪物 每个怪物的战斗力为atk2 在一场战斗中 你需要安排每个英雄分别与一个怪兽战斗 如果英雄战斗力高于怪兽 那个怪兽就会被击杀 问最多能击杀几个怪兽 给定atk1 6

  • excel二进制移位运算_Excel揭秘13：在Excel中实现位运算

    我们知道 计算机使用的是二进制计数法 也就是说 在计算机中的所有信息都是使用二进制来存储和处理的 下表列出了我们熟悉的十进制数及与其相对应的二进制数 位运算规则 在位运算中 按位与 运算 AND运算 分别按位比较两个相应的数字 0或1 当且

  • centos7搭建svn服务器

    一 安装svn服务器 root svnserver yum y install subversion 查看svn 安装位置 可以用以下命令 root svnserver rpm ql subversion etc subversion et

  • Java中String类的使用

    目录 1 MS String 类中两种对象实例化的区别 1 1 直接赋值 1 2 构造方法 2 字符 字节与字符串 2 1 字符与字符串转换 2 2 字节与字符串转换 3 字符串常见操作 3 1 字符串比较 3 2 字符串查找 3 3 字符

  • HS BDC 【HDU - 3472】【混合半欧拉图构建欧拉图+最大流】

    题目链接 有N个字符 如果字符可以首尾相同字符相接组成一条链的话 那么就是说明是well done的 不然 就不是 所以考虑成一条边 我们把每个字符串考虑成有向边 又有些字符串是可以反转的 实际上可以把它当成是无向边来考虑 现在 就是要知道

  • idea 生成项目结构图

    Terminal中输入tree D mybatis plus generator demo gt tree 文件夹 PATH 列表 卷序列号为 ECE0 24D1 D idea inspectionProfiles libraries mv

  • Java如何将一个对象的所有字段值赋值给另一个对象?

    我们开发的时候可能需要进行对象值的复用 下面给大家介绍一个方法 就是使用BeanUtils public static void main String args throws Exception Student student new S

  • MySQL 经典练习 50 题（完美解答版）

    一 创建数据库和表 数据库 学生表 student 课程表 course 教师表 teacher 成绩表 score 表关系 创建数据库和表 创建数据库 drop database if exists mysql test cascade

热门标签