MySQL50题练习

1. 查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号（重点）

SELECT
	st.*,
	s1.s_score AS '01_score',
	s2.s_score AS '02_score' 
FROM
	Student AS st
	INNER JOIN ( SELECT s.* FROM Score AS s WHERE s.c_id = '01' ) AS s1 ON st.s_id = s1.s_id
	INNER JOIN ( SELECT s.* FROM Score AS s WHERE s.c_id = '02' ) AS s2 ON s1.s_id = s2.s_id 
WHERE
	s1.s_score > s2.s_score;

2. 查询平均成绩大于60分的学生的学号和平均成绩（简单，第二道重点）

-- 思路：
		学生学号来自学生表、成绩来子成绩表
		平均成绩，需要对课程成绩聚合,聚合键s_id，分组：score,再用HAVING选出大于60分的,即可

SELECT
	st.s_id,
	st.s_name,
	s1.avg_score 
FROM
	Student AS st
	INNER JOIN ( SELECT sc.s_id, AVG(sc.s_score) AS avg_score FROM Score AS sc GROUP BY sc.s_id HAVING avg_score > 60 ) AS s1 
WHERE
	st.s_id = s1.s_id;
			

3. 查询所有学生的学号、姓名、选课数、总成绩（不重要）

-- 思路:
		学号、姓名来自学生表，选课数和成绩来自成绩表
		选课数 和总成绩需要聚合
		在和第一不中的学生表聚合，并展示所需要的列即可

SELECT
	st.s_id,
	st.s_name,
	s1.no_course,
	s1.sum_score 
FROM
	Student AS st
	INNER JOIN (
	SELECT
		sc.s_id,
		COUNT( 1 ) AS no_course,
		sum( sc.s_score ) AS sum_score 
	FROM
		Score AS sc 
	GROUP BY
		sc.s_id 
	) AS s1 
WHERE
	st.s_id = s1.s_id;

4. 查询姓“张”的老师的个数（不重要）

-- 思路：
		老师来自老师表
		SELECT t.* FROM Teacher AS t;
		先用模糊查询把所有张姓老师查出来(没有这个姓的老师，结果为空表)
		SELECT t.* FROM Teacher AS t WHERE t.t_name LIKE '张%';
		对第二步中的查询结果计数，即可

SELECT
	COUNT( 1 ) AS '张姓老师数量' 
FROM
	Teacher AS t 
WHERE
	t.t_name LIKE '张%';

5. 查询没学过“张三”老师课的学生的学号、姓名（重点）## 这个题目有坑，容易写成“查找出另外两个老师，对应的学生”

-- 学生的学号和姓名来自学生表，老师来自老师表，课程来自课程表或者分数表（只有编号）
SELECT t.* FROM Teacher AS t;
SELECT st.* FROM Student AS st;
SELECT c.* FROM Course AS c;
SELECT sc.* FROM Score AS sc;

-- 把课程表和老身表内连接，找到每个课程对应的老师
SELECT	c.c_id, c.c_name, t.t_name FROM	Course AS c	INNER JOIN Teacher AS t ON c.t_id = t.t_id;

-- 把第二步中的表和分数表内连接，得到每个学生对应课程的老师，且老师是张三
SELECT	sc.s_id, sc.c_id,s1.c_name, s1.t_name FROM	Score AS sc INNER JOIN (
SELECT	c.c_id, c.c_name, t.t_name FROM	Course AS c	INNER JOIN Teacher AS t ON c.t_id = t.t_id) AS s1 ON sc.c_id = s1.c_id WHERE s1.t_name = '张三';

-- 把第三步中的查询结果和学生表内连接，即可

SELECT
	st.s_id,
	st.s_name
FROM
	Student AS st
	LEFT JOIN (
	SELECT
		sc.s_id,
		sc.c_id,
		s1.c_name,
		s1.t_name 
	FROM
		Score AS sc
		INNER JOIN (
		SELECT
			c.c_id,
			c.c_name,
			t.t_name 
		FROM
			Course AS c
			INNER JOIN Teacher AS t ON c.t_id = t.t_id 
		) AS s1 ON sc.c_id = s1.c_id 
	WHERE
		s1.t_name = '张三' 
	) AS s2 ON st.s_id = s2.s_id
	WHERE s2.t_name IS NULL;

-- 最后代码：

SELECT
	st.s_id,
	st.s_name 
FROM
	Student AS st 
WHERE
	st.s_id NOT IN (
	SELECT
		sc.s_id 
	FROM
		Score AS sc
		INNER JOIN Course AS c ON sc.c_id = c.c_id
		INNER JOIN Teacher AS t ON c.t_id = t.t_id 
	WHERE t_name = '张三' 
	);

6. 查询学过“张三”老师所教的所有课的同学的学号、姓名（重点）

SELECT
	st.s_id,
	st.s_name 
FROM
	Student AS st 
WHERE
	st.s_id IN (
	SELECT
		sc.s_id 
	FROM
		Score AS sc
		INNER JOIN Course AS c ON sc.c_id = c.c_id
		INNER JOIN Teacher AS t ON c.t_id = t.t_id 
	WHERE t_name = '张三' 
	);