Mysql 50题练习题 完美

最新推荐文章于 2024-05-14 23:11:31 发布
最新推荐文章于 2024-05-14 23:11:31 发布
阅读量1.1k 收藏 8
点赞数 3
分类专栏： mysql 文章标签： 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_47737819/article/details/108205425
版权

创建数据库和表

数据库

drop database if exists mysql_test cascade;
create database mysql_test;
use mysql_test;

学生表 student

创建学生表 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

创建课程表 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

创建教师表 teacher

create table teacher (
	t_id int,
	t_name varchar(8)
);
4

插入教师数据

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

成绩表 score

创建成绩表 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 | 女    |
+------+--------+------------+-------+
1011

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 | 男    |
+------+--------+------------+-------+
7

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 |
+------+

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

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 | 女    |
+------+--------+------------+-------+
青橘不酸
关注
  • 3
    点赞
  • 踩
  • 8
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
mysql常见练习题45题
qq_40169177的博客
10-01 2185
create database school; use school; #学生表 create table Student ( Sno varchar(20) not null COMMENT ‘人名’, Sname varchar(20) not null COMMENT ‘姓名’, Ssex varchar(20) not null COMMENT ‘性别’, Sbirthday datet...
mysql练习题四
m0_59708269的博客
01-24 113
1.约束 #第13章_约束 /* 1. 基础知识 1.1 为什么需要约束？ 为了保证数据的完整性！ 1.2 什么叫约束？对表中字段的限制。 1.3 约束的分类： 角度1：约束的字段的个数 单列约束 vs 多列约束 角度2：约束的作用范围 列级约束：将此约束声明在对应字段的后面 表级约束：在表中所有字段都声明完，在所有字段的后面声明的约束 角度3：约束的作用（或功能） ① not null (非空约束) ② unique (唯一性约束) ③ primary key (主键约束) ④ fore
MySQL经典练习50题
08-08
MySQL经典题库50题，无答案。MySQL经典题库50题，无答案。
数据库经典题目 1-10
soobinnim的博客
03-02 554
-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数 SELECT Student.*,A.s_score AS 01score,B.s_score AS 02score FROM Student, (SELECT s_score,s_id FROM score WHERE c_id='01')A, -- 分别查找包含01，02分数作为两个表 (SELECT s_score,s_id FROM score WHERE c_id='02')B WHERE A.s_score&...
mysql练习题基础知识篇
最新发布
ambious909的博客
05-14 782
A. 编译B. 保存C. 解释D. 编写正确答案:A正确答案：(1) 参照完整性；我的答案：2.8分正确答案：答案解析：无我的答案：4.8分正确答案：DEFAULT正确答案：正确答案：DEFAULT。
mysql drop database if exists_MySQL Drop Database之后的恢复测试
weixin_33252423的博客
01-27 974
工具介绍工具的名字叫做: undrop-for-innodb当MySQL 执行 Drop Table或者 Drop Database 的时候，InnoDB并没有删除数据，数据的Page还在磁盘上。如果innodb_file_per_table被设置成了OFF,则数据库在ibdata1上，如果innodb_file_per_table被设置成了ON,则数据在.ibd文件中，MySQL已经删除了这个...
MySQL经典50道练习题及全网最详细解析
热门推荐
hujieliang123的博客
01-16 1万+
MySQL经典50道练习全网最详细解析
sql语句练习50题(Mysql版)
Coder-Wf
09-10 3881
表名和字段 –1.学生表 Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别 –2.课程表 Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号 –3.教师表 Teacher(t_id,t_name) –教师编号,教师姓名 –4.成绩表 Score(s_id,c_id,s_scor...
MySQL练习题（两份PDF，150道题，包含表结构数据），完美掌握sql增删改查
04-19
本资源提供了两份PDF练习题，总计150道题目，旨在帮助用户深入理解和熟练掌握这些基本操作。 1. **SQL基础知识**：首先，你需要了解SQL的基本语法，包括如何创建数据库和数据表。在"创建"（CREATE）部分，你将学习...
墨子题库系统 v2.5.0
03-25
墨子题库系统是一款轻量级题目管理系统，基于PHP+Mysql架构的，可运行在Linux、Windows、MacOSX、Solaris等各种平台上，系统基于Laravel，支持自定义伪静态，前台模板采用DIV+CSS设计，后台界面设计简洁明了，功能...
魔众题库系统源码 v5.1.1.zip
06-13
魔众题库系统采用PHP Mysql架构,是一款对SEO非常友好、功能全面、安全稳定、支持多终端展示并且使用起来极其简单的在线题库管理系统。 魔众题库系统，融合在线题库和在线考试。...同步登录：墨子系列产品完美搭配使用
MySQL数据库练习题(50道).pdf
04-25
MySQL数据库练习题(50道).pdf
魔众题库系统-PHP
06-20
专项练习：模块化训练，快速提高答题能力 试题解析：简单、快速、准确 同步登录：墨子系列产品完美搭配使用 运行环境： 操作系统 Linux/Unix 或 Windows 软件环境 Laravel 5.1的运行环境 Apache/Nginx，...
魔众题库系统v3.1.0
06-01
专项练习：模块化训练，快速提高答题能力 试题解析：简单、快速、准确 同步登录：墨子系列产品完美搭配使用 运行环境： 操作系统 Linux/Unix 或 Windows 软件环境 Laravel 5.1的运行环境 Apache/Nginx , ...
mysql数据库的操作大全_[数据库]mysql数据库操作语法大全
weixin_35268264的博客
02-08 211
[数据库]mysql数据库操作语法大全02016-06-15 21:00:111、连接Mysql格式： mysql -h主机地址 -u用户名 －p用户密码1、连接到本机上的MYSQL。首先打开DOS窗口，然后进入目录mysql\bin，再键入命令mysql -u root -p，回车后提示你输密码.注意用户名前可以有空格也可以没有空格，但是密码前必须没有空格，否则让你重新输入密码。如果刚安装好MY...
mysql聚合函数练习
qq_63431773的博客
11-20 310
课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率– 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90。
MySQL学习：50道经典题目（含参考答案）(更新中）
weixin_42661635的博客
04-27 8779
首先根据course_id将成绩表分为两张表，筛选出" 1 "课程比" 2 "课程成绩高的学生，再与学生表关联。1. 查询" 1 "课程比" 2 "课程成绩高的学生的信息（学号、姓名、性别、出生日期）及课程分数。利用自连接，统计比当前分数高的分数有几个，排名就是几加1。使用case when语句，多个排序条件时用逗号分隔。利用课程数量，计算出所有课程数和每个学生的选课数量。先筛选与" 1 "号同学选课数量相同的同学，再剔除。10. 查询没有学全所有课程的同学的信息。选了" 1 "号同学没选的课的同学。
MySQL基础篇 | 经典三十四道练习题
m0_61933976的博客
09-18 5983
关于MySQL的三十四道练习题，大佬们都会做吗
mysql练习：经典50道基础题
不愿意做鱼的小鲸鱼の博客
01-17 3515
mysql经典50道基础练习题（附加答案）
mysql经典50道练习题pdf
08-17
MySQL经典50道练习题pdf是一本经典的MySQL数据库练习题集合，对于想要提高MySQL数据库操作能力的人士来说是非常有价值的。 这本练习题集合涵盖了各种MySQL数据库操作方面的题目，包括数据库创建、表创建、数据插入...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值