Mysql多表联查——经典50题

---

---

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数。

// 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
	SELECT stu.s_id, stu.s_name, s1.s_score FROM student stu, score s1, score s2
	WHERE stu.s_id = s1.s_id
	AND stu.s_id = s2.s_id
	AND s1.c_id = '01'
	AND s2.c_id = '02'
	AND s1.s_score > s2.s_score

2、查询每门功成绩最好的前两名 。

// 方法一
SELECT c1.c_name, 
		( SELECT s1.s_id FROM score s1 
			WHERE s1.c_id = c1.c_id
			ORDER BY s1.s_score DESC LIMIT 0,1
		) 第一名,
		( SELECT s1.s_id FROM score s1 
			WHERE s1.c_id = c1.c_id
			ORDER BY s1.s_score DESC LIMIT 1,1
		) 第二名
	FROM course c1
// 方法二	
	SELECT s1.c_id, s1.s_id, s1.s_score
	FROM score s1 
	LEFT JOIN score s2 ON s1.c_id = s2.c_id
	AND s1.s_score < s2.s_score
	GROUP BY s1.c_id, s1.s_id
	HAVING COUNT(s1.c_id) < 2
	ORDER BY s1.c_id ASC, s1.s_score DESC
// 方法三	
SELECT sc.c_id, sc.s_score FROM score sc 
WHERE (
	SELECT COUNT(*) FROM score 
	WHERE sc.c_id = score.c_id 
	AND sc.s_score < score.s_score
) < 2 
ORDER BY sc.c_id ASC,sc.s_score DESC;

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

SELECT stu.s_id, stu.s_name, SUM(sc.s_score) / ( SELECT COUNT(*) FROM course) AS '平均分' FROM student stu, score sc
WHERE stu.s_id = sc.s_id
GROUP BY sc.s_id
HAVING SUM(sc.s_score) / ( SELECT COUNT(*) FROM course) >= 60 

-- AVG计算平均分，缺考会被忽略--
SELECT stu.s_id, stu.s_name, AVG(sc.s_score) AS '平均分' FROM student stu, score sc
WHERE stu.s_id = sc.s_id
GROUP BY sc.s_id
HAVING AVG(sc.s_score) >= 60 

-- 查询某一个学生平均分。
SELECT s_id, c_id, SUM(s_score) AS '总分', SUM(s_score) /5 AS '平均分' FROM score WHERE s_id = 3

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

SELECT stu.s_id, stu.s_name, SUM(sc.s_score) / ( SELECT COUNT(*) FROM course) AS '平均分' FROM student stu, score sc
WHERE stu.s_id = sc.s_id
GROUP BY sc.s_id
HAVING SUM(sc.s_score) / ( SELECT COUNT(*) FROM course) < 60 
UNION
SELECT stu.s_id, stu.s_name, avg(sc.s_score)
FROM student stu
LEFT JOIN score sc ON stu.s_id = sc.s_id
GROUP BY stu.s_id
HAVING avg(sc.s_score) IS NULL;

5.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩。

SELECT stu.s_id AS'学号', stu.s_name AS'姓名', COUNT(sc.c_id)AS'选课总数', SUM(sc.s_score) AS '总成绩' 
FROM student stu, score sc
WHERE stu.s_id = sc.s_id
GROUP BY stu.s_id

6.查询"李"姓老师的数量。

SELECT COUNT(t_id)AS '总数' FROM teacher WHERE t_name LIKE '李%' 

7.查询学过"张三"老师授课的同学的信息。

SELECT s_id, s_name FROM student
WHERE s_id IN (
	SELECT sc.s_id FROM score sc, course c, teacher t
	WHERE sc.c_id = c.c_id
	AND c.t_id = t.t_id
	AND t.t_name = '张三'
)

8.查询没学过"张三"老师授课的同学的信息。

SELECT s_id, s_name FROM student
WHERE s_id NOT IN (
	SELECT sc.s_id FROM score sc, course c, teacher t
	WHERE sc.c_id = c.c_id
	AND c.t_id = t.t_id
	AND t.t_name = '张三'
)

9.查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息。

/* -- EXISTS 检语法：
select 字段名|表达式 from 表名1 a
where exists (select * from 表名2 b where a. 字段名a=b.字段名b) ;
关键字EXISTS：构造子查询，子查询是否返回结果集，返回则才进行外层查询。

如果子查询的结果集不为空，则EXISTS返回的结果为TRUE，此时外层查询语句将进行查询；
如果子查询的结果集为空，则EXISTS返回的结果为FLASE，此时外层查询语句将不进行查询；
*/                 
SELECT stu.s_id, stu.s_name FROM student stu, score sc
WHERE stu.s_id = sc.s_id
AND sc.c_id = '01'
AND EXISTS (
	SELECT * FROM score sc 
	WHERE sc.s_id = stu.s_id 
	AND sc.c_id = '02'
	)

#10.查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息。

SELECT stu.s_id, stu.s_name FROM student stu, score sc
WHERE stu.s_id = sc.s_id
AND sc.c_id = '01'
AND stu.s_id NOT IN (
	SELECT s_id FROM score WHERE c_id = '02'
)

11.查询没有学全所有课程的同学的信息 。

SELECT s_id, s_name FROM student 
WHERE s_id NOT IN (
	SELECT s_id FROM score  
	GROUP BY s_id
	HAVING COUNT(c_id) = (SELECT COUNT(c_id) FROM course)
)

12.查询至少有一门课与学号为"01"的同学所学相同的同学的信息。

/**
* 关键字 DISTINCT 去重
*/
SELECT DISTINCT stu.s_id, stu.s_name FROM student stu, score sc
WHERE stu.s_id = sc.s_id
AND sc.c_id IN (
	SELECT c_id FROM score WHERE s_id = '1'
)

13.查询和"01"号的同学学习的课程完全相同的其他同学的信息。

SELECT *