Mysql多表练习题30道

根据上一篇文章建立的表，我们来做一些多表练习：

没建立表的可以点击此链接去建立练习用的表：

目录

1.查询“1”号学生的姓名和各科成绩：

2.查询各个学科的平均成绩和最高成绩：

3.查询所有姓张的同学的各科成绩：

4.查询每个同学的最高成绩和科目名称

5.查询每个课程的最高分的学生信息

6.查询名字中含有'张'或'李'字的学生的信息和各科成绩。

7.查询平均成绩大于70的同学的信息。（子查询）

8.将学生按照总分数进行排名。（从高到低）

9.查询数学成绩的最高分、最低分、平均分。

10.将各科目按照平均分排序。

11.查询老师的信息和他所带的科目的平均分

12.查询被"Tom"和"Jerry"教的课程的最高分和最低分

13.查询每个学生的最好成绩的科目名称（子查询）

14.查询所有学生的课程及分数

15.查询课程编号为1且课程成绩在60分以上的学生的学号和姓名（子查询）

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

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

18.查询每门课程有成绩的学生人数

19.查询每门课程的平均成绩，结果按照平均成绩降序排列，如果平均成绩相同，再按照课程编号升序排列

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

21.查询有且仅有一门课程成绩在80分以上的学生信息

22.查询出只有三门课程的学生的学号和姓名

23.查询有不及格课程的课程信息

24.查询至少选择4门课程的学生信息

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

26.查询选全所有课程的同学的信息 

27.查询各学生都选了多少门课

28.查询课程名称为"java"，且分数低于60分的学生姓名和分数

29.查询学过"Tony"老师授课的同学的信息

30.查询没学过"Tony"老师授课的学生信息

---

1.查询“1”号学生的姓名和各科成绩：

进行student表和scores表的id相连接，course表和scores表的id相连接

SELECT
	s.id sid,
	s.`name` sname,
	c.`name` cname,
	sc.score 
FROM
	student s
	LEFT JOIN scores sc ON s.id = sc.s_id
	LEFT JOIN course c ON c.id = sc.c_id 
WHERE
	s.id = 1;

2.查询各个学科的平均成绩和最高成绩：

SELECT
	c.id,
	c.`name`,
	AVG( sc.score ),
	max( sc.score ) 
FROM
	course c
	LEFT JOIN scores sc ON c.id = sc.c_id 
GROUP BY
	c.id,
	c.`name`;

3.查询所有姓张的同学的各科成绩：

SELECT
	s.id,
	s.`name`,
	c.`name` cname,
	sc.score 
FROM
	student s
	LEFT JOIN scores sc ON sc.s_id = s.id
	LEFT JOIN course c ON c.id = sc.c_id 
WHERE
	s.`name` LIKE '张%';

4.查询每个同学的最高成绩和科目名称

SELECT
	t.id,
	t.NAME,
	c.id,
	c.NAME,
	r.score 
FROM
	(
	SELECT
		s.id,
		s.NAME,(
		SELECT
			max( score ) 
		FROM
			scores r 
		WHERE
			r.s_id = s.id 
		) score 
	FROM
		student s 
	) t
	LEFT JOIN scores r ON r.s_id = t.id 
	AND r.score = t.score
	LEFT JOIN course c ON r.c_id = c.id;

5.查询每个课程的最高分的学生信息

SELECT
	* 
FROM
	student s 
WHERE
	id IN (
	SELECT DISTINCT
		r.s_id 
	FROM
		(
		SELECT
			c.id,
			c.NAME,
			max( score ) score 
		FROM
			student s
			LEFT JOIN scores r ON r.s_id = s.id
			LEFT JOIN course c ON c.id = r.c_id 
		GROUP BY
			c.id,
			c.NAME 
		) t
		LEFT JOIN scores r ON r.c_id = t.id 
	AND t.score = r.score 
	);

6.查询名字中含有'张'或'李'字的学生的信息和各科成绩。

SELECT
	s.id,
	s.NAME sname,
	sc.score,
	c.NAME 
FROM
	student s
	LEFT JOIN scores sc ON s.id = sc.s_id
	LEFT JOIN course c ON sc.c_id = c.id 
WHERE
	s.NAME LIKE '%张%' 
	OR s.NAME LIKE '%李%';

7.查询平均成绩大于70的同学的信息。（子查询）

SELECT
	* 
FROM
	student 
WHERE
	id IN (
	SELECT
		sc.s_id 
	FROM
		scores sc 
	GROUP BY
		sc.s_id 
	HAVING
	avg( sc.score ) >= 70 
	);

8.将学生按照总分数进行排名。（从高到低）

SELECT
	s.id,
	s.NAME,
	sum( sc.score ) score 
FROM
	student s
	LEFT JOIN scores sc ON s.id = sc.s_id 
GROUP BY
	s.id,
	s.NAME 
ORDER BY
	score DESC,
	s.id ASC;

9.查询数学成绩的最高分、最低分、平均分。

SELECT
	c.NAME,
	max( sc.score ),
	min( sc.score ),
	avg( sc.score ) 
FROM
	course c
	LEFT JOIN scores sc ON c.id = sc.c_id 
WHERE
	c.NAME = '数学';

10.将各科目按照平均分排序。

SELECT
	c.id,
	c.NAME,
	avg( sc.score ) score 
FROM
	course c
	LEFT JOIN scores sc ON c.id = sc.c_id 
GROUP BY
	c.id,
	c.NAME 
ORDER BY
	score DESC;

11.查询老师的信息和他所带的科目的平均分

SELECT
	t.id,
	t.NAME,
	c.id cid,
	c.NAME cname,
	avg( r.score ) 
FROM
	teacher t
	LEFT JOIN course c ON t.id = c.t_id
	LEFT JOIN scores r ON r.c_id = c.id 
GROUP BY
	t.id,
	t.NAME,
	c.id,
	c.NAME;

12.查询被"Tom"和"Jerry"教的课程的最高分和最低分

SELECT
	t.id,
	t.NAME,
	c.id cid,
	c.NAME cname,
	max( r.score ),
	min( r.score ) 
FROM
	teacher t
	LEFT JOIN course c ON t.id = c.t_id
	LEFT JOIN scores r ON r.c_id = c.id 
GROUP BY
	t.id,
	t.NAME,
	c.id,
	c.NAME 
HAVING
	t.NAME IN ( 'Tom', 'Jerry' );

13.查询每个学生的最好成绩的科目名称（子查询）

SELECT
	t.id,
	t.sname,
	r.c_id,
	c.NAME,
	t.score 
FROM
	(
	SELECT
		s.id,
		s.NAME sname,
		max( r.score ) score 
	FROM
		student s
		LEFT JOIN scores r ON r.s_id = s.id 
	GROUP BY
		s.id,
		s.NAME 
	) t
	LEFT JOIN scores r ON r.s_id = t.id 
	AND r.score = t.score
	LEFT JOIN course c ON r.c_id = c.id;

 

14.查询所有学生的课程及分数

SELECT
	s.id,
	s.NAME,
	c.id,
	c.NAME,
	r.score 
FROM
	student s
	LEFT JOIN scores r ON s.id = r.s_id
	LEFT JOIN course c ON c.id = r.c_id;

15.查询课程编号为1且课程成绩在60分以上的学生的学号和姓名（子查询）

SELECT
	s.*,
	r.* 
FROM
	student s
	LEFT JOIN scores r ON s.id = r.s_id 
WHERE
	r.c_id = 1 
	AND r.score > 60

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

SELECT
	s.id,
	s.NAME,
	t.score 
FROM
	student s
	LEFT JOIN ( SELECT r.s_id, avg( r.score ) score FROM scores r GROUP BY r.s_id ) t ON s.id = t.s_id 
WHERE
	t.score >= 70;

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

SELECT
	* 
FROM
	student s 
WHERE
	id IN ( SELECT r.s_id FROM scores r GROUP BY r.s_id HAVING min( r.score ) < 60 );

18.查询每门课程有成绩的学生人数

SELECT
	c.id,
	c.NAME,
	count(*) 
FROM
	course c
	LEFT JOIN scores r ON c.id = r.c_id 
GROUP BY
	c.id,
	c.NAME;

19.查询每门课程的平均成绩，结果按照平均成绩降序排列，如果平均成绩相同，再按照课程编号升序排列

SELECT
	c.id,
	c.NAME,
	avg( score ) score 
FROM
	course c
	LEFT JOIN scores r ON c.id = r.c_id 
GROUP BY
	c.id,
	c.NAME 
ORDER BY
	score DESC,
	c.id ASC;

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

SELECT
	s.id,
	s.NAME sname,
	avg( r.score ) score 
FROM
	student s
	LEFT JOIN scores r ON r.s_id = s.id
	LEFT JOIN course c ON c.id = r.c_id 
GROUP BY
	s.id,
	s.NAME 
HAVING
	score > 65;

21.查询有且仅有一门课程成绩在80分以上的学生信息

SELECT
	s.id,
	s.NAME,
	s.gender 
FROM
	student s
	LEFT JOIN scores r ON s.id = r.s_id 
WHERE
	r.score > 80 
GROUP BY
	s.id,
	s.NAME,
	s.gender 
HAVING
	count(*) = 1;

22.查询出只有三门课程的学生的学号和姓名

SELECT
	s.id,
	s.NAME,
	s.gender 
FROM
	student s
	LEFT JOIN scores r ON s.id = r.s_id 
GROUP BY
	s.id,
	s.NAME,
	s.gender 
HAVING
	count(*) = 3;

23.查询有不及格课程的课程信息

SELECT
	* 
FROM
	course c 
WHERE
	id IN (
	SELECT
		r.c_id 
	FROM
		scores r 
	GROUP BY
		r.c_id 
	HAVING
	min( r.score ) < 60 
	);

24.查询至少选择4门课程的学生信息

SELECT
	s.id,
	s.NAME 
FROM
	student s
	LEFT JOIN scores r ON s.id = r.s_id 
GROUP BY
	s.id,
	s.NAME 
HAVING
	count(*) >= 4;

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

SELECT
	* 
FROM
	student 
WHERE
	id IN (
	SELECT
		r.s_id 
	FROM
		scores r 
	GROUP BY
		r.s_id 
	HAVING
	count(*) != 5
	);

26.查询选全所有课程的同学的信息 

SELECT
	s.id,
	s.NAME,
	count(*) number 
FROM
	student s
	LEFT JOIN scores r ON s.id = r.s_id 
GROUP BY
	s.id,
	s.NAME 
HAVING
	number = ( SELECT count(*) FROM course );

27.查询各学生都选了多少门课

SELECT
	s.id,
	s.NAME,
	count(*) number 
FROM
	student s
	LEFT JOIN scores r ON s.id = r.s_id 
GROUP BY
	s.id,
	s.NAME;

28.查询课程名称为"java"，且分数低于60分的学生姓名和分数

SELECT
	s.id,
	s.NAME,
	r.score 
FROM
	student s
	LEFT JOIN scores r ON s.id = r.s_id
	LEFT JOIN course c ON r.c_id = c.id 
WHERE
	c.NAME = 'java' 
	AND r.score < 60;

29.查询学过"Tony"老师授课的同学的信息

SELECT
	s.id,
	s.NAME 
FROM
	student s
	LEFT JOIN scores r ON r.s_id = s.id
	LEFT JOIN course c ON c.id = r.c_id
	LEFT JOIN teacher t ON t.id = c.t_id 
WHERE
	t.NAME = 'Tom';

30.查询没学过"Tony"老师授课的学生信息

SELECT
	* 
FROM
	student 
WHERE
	id NOT IN (
	SELECT DISTINCT
		s.id 
	FROM
		student s
		LEFT JOIN scores r ON r.s_id = s.id
		LEFT JOIN course c ON c.id = r.c_id
		LEFT JOIN teacher t ON t.id = c.t_id 
	WHERE
	t.NAME = 'Tom' 
	)