MySQL 基本查询1--7

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

需要输出Student表的全部信息和Score表中的s_score；

-- 方法一：使用join连接
SELECT
	a.*, 
    b.s_score,
	c.s_score
FROM
	Student a
JOIN Score b ON a.s_id = b.s_id
AND b.c_id = '01'
JOIN Score c ON a.s_id = c.s_id
AND c.c_id = '02'
WHERE
	b.s_score > c.s_score;

-- 方法二：采用where语句
SELECT
	a.*, 
  b.s_score,
	c.s_score
FROM
	Student a,
	Score b,
	Score c
WHERE
	a.s_id = b.s_id
AND a.s_id = c.s_id
AND b.c_id = '01'
AND c.c_id = '02'
AND b.s_score > c.s_score;

题目2：查询平均成绩大于等于60分且总分大于200分的同学且必须考3门的学生编号和学生姓名和平均成绩

SELECT
	a.s_id,
	a.s_name,
	ROUND(AVG(b.s_score), 2) AS avg_score,
	-- 求均值并保留2位小数
	ROUND(SUM(b.s_score), 0) AS sum_score
  -- 求和并保留0位小数
FROM
	Student a
JOIN Score b ON a.s_id = b.s_id
GROUP BY
	a.s_id
HAVING
	avg_score >= 60
AND sum_score > 200
AND COUNT(b.s_score)=3;

题目3：查询平均成绩小于60分的同学的学生编号、学生姓名、平均成绩（包括有成绩的和无成绩）

SELECT
	a.s_id,
	a.s_name,
	ROUND(AVG(b.s_score), 2) AS avg_score
FROM
	Student a
LEFT JOIN Score b ON a.s_id = b.s_id
GROUP BY
	a.s_id
HAVING
	avg_score < 60;

上表所示的结果中没有"王菊"，因为王菊没有成绩；
以下代码筛选出没有成绩的同学王菊；

SELECT
	a.s_id,
	a.s_name,
	0 AS avg_score -- 将avg_score的值置为0
FROM
	Student a
WHERE
	a.s_id NOT IN (
		-- 学生的学号不在给给定表的学号中
		SELECT DISTINCT
			s_id -- 查询出全部的学号
		FROM
			Score
	);

不采用union函数，采用ifnull和null函数进行筛选计算；
-- 方法2：采用ifnull函数
SELECT
	S.s_id,
	S.s_name,
	ROUND(AVG(IFNULL(C.s_score, 0)), 2) AS avg_score -- ifnull 函数：第一个参数存在则取它本身，不存在取第二个值0
FROM
	Student S
LEFT JOIN Score C ON S.s_id = C.s_id -- 一定要采用左连接
GROUP BY
	s_id
HAVING
	avg_score < 60;



-- 方法3：采用null函数
SELECT
	a.s_id,
	a.s_name,
	ROUND(AVG(b.s_score), 2) AS avg_score
FROM
	Student a
LEFT JOIN Score b ON a.s_id = b.s_id
GROUP BY
	a.s_id
HAVING
	avg_score < 60
OR avg_score IS NULL; -- 最后的NULL判断

题目4：查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

SELECT
	a.s_id,
	a.s_name,
	COUNT(b.c_id) AS count_course,
	SUM(b.s_score) AS sum_socre
FROM
	Student a
LEFT JOIN Score b ON a.s_id = b.s_id -- 采用left join，所以对于没有成绩的学生也可以筛选出来。
GROUP BY
	a.s_id;

题目5：查询“李”姓老师的数量

用通配符%和like关键字来解决。
SELECT
	COUNT(t_name) 
FROM
	Teacher 
WHERE
	t_name LIKE '李%'; -- 以李字开头的名字

题目6：查询学过张三老师授课的同学信息

-- 方法1：通过筛选出张三的id，并匹配相应的课程id，最后将学生的成绩与课程id进行匹配，最后筛选出符合条件的学生。
SELECT
	a.* 
FROM
	Student a
	JOIN Score b ON a.s_id = b.s_id 
	AND b.c_id =(
	SELECT
		c_id 
	FROM
		Course 
	WHERE
		t_id =(
		SELECT
			t_id 
		FROM
			Teacher 
		WHERE
		t_name = '张三' 
	));


-- 方法2：通过left join进行匹配
SELECT
	a.* 
FROM
	Student a
	JOIN Score b ON a.s_id = b.s_id
	JOIN Course c ON b.c_id = c.c_id
	JOIN Teacher d ON c.t_id = d.t_id
	WHERE d.t_name='张三';

题目7：找出没有学过张三老师课程的学生

和上面👆的题目是互补的，考虑取反操作。

-- 方法1：通过筛选出张三的id，并匹配相应的课程id，然后将学生的成绩与张三老师课程id进行匹配，最后筛选不在次学生id范围内的学生信息。
SELECT
	t3.*
FROM
	Student t3
WHERE
	t3.s_id NOT IN (
		SELECT
			t1.s_id
		FROM
			Score t1
		JOIN Course t2 ON t1.c_id = t2.c_id
		WHERE
			t_id = (
				SELECT
					t_id
				FROM
					Teacher
				WHERE
					t_name = '张三'
			)
	);


-- 方法2：使用join
SELECT
	a.*
FROM
	Student a
WHERE
	a.s_id NOT IN (
		SELECT
			s_id
		FROM
			Score a
		JOIN Course b ON a.c_id = b.c_id
		JOIN Teacher c ON b.t_id = c.t_id
		WHERE
			c.t_name = '张三'
	);