经典mysql题目练习题解

原创已于 2023-11-21 16:51:26 修改 · 646 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

标签

#mysql #数据库

于 2023-10-08 09:39:23 首次发布

文章详细介绍了在MySQL中进行数据库操作，包括求总分最高学生、单科成绩最高学生、课程关联查询等实例，展示了使用SQL查询技巧解决实际问题的方法。

数据库表

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

创建表

create table student (
    id int,
    name varchar(8),
    birth date,
    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','女');


create table course (
    id int,
    name varchar(8),
    t_id int
);

insert into course values
(1,'语文',2),
(2,'数学',1),
(3,'英语',3);

create table teacher (
    t_id int,
    t_name varchar(8)
);
insert into teacher values
(1,'张三'),
(2,'李四'),
(3,'王五');

create table score (
    sid int,
    cid int,
    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);

1、求总分最高的学生（总分最高的学生可能不止一个）

解1：使用mysql8的开窗函数，根据学生id进行分组排名，然后取出排名为1的学生信息

开窗函数详见

SELECT * FROM (
	SELECT
		s.id,
		s.NAME,
		sum(score) as ts,
		DENSE_RANK() OVER (ORDER BY sum(score) DESC ) as rk
	FROM student s
	LEFT JOIN score sc ON s.id = sc.sid 
	GROUP BY s.id 
) t WHERE rk = 1

解2：子查询，先求出总分最高的学生，然后再与score表对比

SELECT
	s.id,
	s.NAME,
	sum(score) as ts
FROM student s
LEFT JOIN score sc ON s.id = sc.sid 
GROUP BY s.id 
HAVING ts = (
	SELECT sum(score) as ts
	FROM student s
	LEFT JOIN score sc ON s.id = sc.sid 
	GROUP BY s.id
	ORDER BY sum(score) DESC
	LIMIT 1
)

2、求单科成绩最高的学生（单科成绩最高的学生可能不止一个）

解1：开窗函数

SELECT * FROM (
	SELECT
		s.id,
		s.NAME AS sname,
		c.name AS cname,
		score,
		DENSE_RANK() OVER (PARTITION BY cid ORDER BY score DESC) as rk
	FROM student s
	LEFT JOIN score sc ON s.id = sc.sid 
	LEFT JOIN course c ON c.id = sc.cid
) t WHERE rk = 1

解2：子查询group by + having

SELECT
	s.id,
	s.NAME,
	score
FROM student s
LEFT JOIN score sc ON s.id = sc.sid 
HAVING score IN (
	SELECT max(score) as ts
	FROM student s
	LEFT JOIN score sc ON s.id = sc.sid 
	GROUP BY sc.cid
)

3、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

解1：子查询

SELECT DISTINCT s.id,s.name FROM student s
INNER JOIN 
(SELECT * FROM score WHERE cid = '01'
AND sid  in(
SELECT sid FROM score WHERE cid = '02') ) sc
ON s.id = sc.sid

解2：根据学号分组统计

SELECT st.*
FROM student st
INNER JOIN score sc ON sc.`sid`=st.`id`
GROUP BY st.`id`
HAVING SUM(IF(sc.`cid`="01" OR sc.`cid`="02" ,1,0)) > 1

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

select st.* from Student st
left join Score S
on st.id = S.sid
group by st.id
having count(cid) < (select count(id) from Course)

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

SELECT s.* FROM student s
LEFT JOIN score sc ON
s.id = sc.sid
GROUP BY s.id
HAVING
GROUP_CONCAT(DISTINCT cid) = (
SELECT GROUP_CONCAT(DISTINCT cid)  FROM score WHERE sid = '01')

6、查询各科成绩前三名的记录 (与题2类似)

SELECT * FROM (
	SELECT
		s.s_id,
		s_name  ,
		c_name ,
		sc.c_id,
		s_score,
		DENSE_RANK() OVER (PARTITION BY sc.c_id ORDER BY s_score DESC) as rk
	FROM student s
	LEFT JOIN score sc ON s.s_id = sc.s_id 
	LEFT JOIN course c ON c.c_id = sc.c_id
	WHERE s_score IS NOT NULL
) t WHERE rk <= 3
ORDER BY t.c_id , rk