MySQL -- 02_最流行的查询需求分析（ 涉及 count(*/1/字段名)的区别 、not in 、 not exists 、显示、隐式连接、case when 的用法等）

最流行的查询需求分析

演示数据准备的SQL

演示数据准备的SQL

需求演示

6、查询 姓“李”的老师的数量–count(1)

使用到 count()函数，解释 count(*)、count(1)、count(字段名)的区别：

COUNT(1)、COUNT(*) 和 COUNT(字段名) 这三种写法都是用于计算行数的函数，它们之间有一些区别：

count( * )
这个写法会统计所有行的数量，不考虑行中具体的字段值。即使某些字段的值为 NULL，也会被计算在内。

count( 1 )
COUNT(1) 中的常数 1 实际上是一个占位符，它并不代表任何具体的数据，只是作为一个计数器存在。这样的写法是为了告诉数据库引擎，我们只是想要对行数进行计数，而不关心具体的数据内容

count( t_name ) ： 这个写法会统计指定字段非 NULL 值的行数。它会计算指定字段不为 NULL 的行的数量。如果指定的字段中有 NULL 值，这些 NULL 值将不会被计算在内。

一般来说，使用 count(1) 是效率最高的。
简单理解，就是上面三个作用都是一样的，在实际使用时，COUNT(1) 可能会更快一些，因为它不需要关心行中的具体值是什么，而 COUNT(*) 则会对所有的行进行计数，包括含有 NULL 值的行
COUNT(字段名) 则是用来统计指定字段中非空数值的数量。因为它只统计特定列的非空值，所以在某些情况下可能会略微慢一些，但一般情况下性能差异并不明显。

-- 6、查询 姓“李”的老师的数量
-- 用到的是 teacher 表

select  COUNT(t_name) from teacher where t_name like '李%'

7、查询学过“张三”老师课程的同学的信息（普通表连接）

-- 7、查询学过“张三”老师课程的同学的信息
-- 用到 student、course、score、teacher 这四张表
SELECT
	st.* 
FROM
	student st
	JOIN score sc ON sc.s_id = st.s_id
	JOIN course co ON co.c_id = sc.c_id
	JOIN teacher t ON t.t_id = co.t_id 
WHERE
	t.t_name = '张三'

8、查询没有学过“张三”老师课程的同学的信息

not in 和 not exists 的区别

简单来说：

not in 用于判断某个值是否不在一个给定的值列表中，或者子查询中。
如果子查询中有 null 值，NOT IN 就无法正常与其他值进行比较，这时候应该使用 NOT EXISTS 来避免这个问题。

not exists 通过检查子查询的结果集是否为空来进行条件判断的。
判断子查询是否存在符合条件的记录的情况，尤其适合处理子查询结果中可能包含 NULL 值的情况。

总的来说，NOT IN 适用于对某个固定值列表进行匹配判断，而 NOT EXISTS 更适用于对子查询结果集是否为空进行判断，具有更灵活和适用范围更广的特点

给定的值列表是一组明确定义的值，
而结果集是通过执行查询语句得到的数据集合，它可以包含一列或多列数据。
NOT IN 通常用于与给定的值列表进行比较，
而 NOT EXISTS 则用于判断子查询的结果集是否为空。

not in 写法

NOT IN 不是函数，而是一种条件表达式。它通常用于子查询中，用来筛选出不包含在子查询结果中的数据

如图，可以把查询出学过“张三”老师课程的数据当成一个子表

-- not in 写法

SELECT
	* 
FROM
	student st 
WHERE
	st.s_id NOT IN (
	
	SELECT
		sc.s_id 
	FROM
		course co
		JOIN score sc ON sc.c_id = co.c_id
		JOIN teacher t ON t.t_id = co.t_id 
	WHERE
		t.t_name = '张三' 
		
	)

not exists 写法

-- not exists 写法

EXPLAIN SELECT
	* 
FROM
	student st 
WHERE
	NOT EXISTS (
	SELECT
		1 
	FROM
		(
		SELECT
			sc.s_id 
		FROM
			course co
			JOIN score sc ON sc.c_id = co.c_id
			JOIN teacher t ON t.t_id = co.t_id 
		WHERE
			t.t_name = '张三' 
		) t 
	WHERE
		t.s_id = st.s_id 
	)

9、查询学过编号为’01语文’并且也学过编号‘02数学’的课程的同学的信息

显示连接和隐式连接的解释

在MySQL中，连接可以分为显式和隐式。

显式连接是指通过JOIN子句在SELECT语句中明确指定的连接。例如，使用INNER JOIN、LEFT JOIN、RIGHT JOIN等来连接表，表与表之间的连接条件写在 on 后面

隐式连接是指在没有明确指定JOIN子句的情况下，表之间的连接用逗号隔开，连接条件写在where子句后面

显示连接 – join写法

一般用这种显示连接的SQL语句，可读性比较高

-- 9、查询学过编号为'01语文'并且也学过编号‘02数学’的课程的同学的信息

-- 要查一张表内的同个字段的值的两种判断，可以连接该表2次

SELECT
	st.* 
FROM
	student st
	JOIN score s1 ON st.s_id = s1.s_id
	JOIN score s2 ON s1.s_id = s2.s_id 
WHERE
	s1.c_id = '01' 
	AND s2.c_id = '02'

隐式连接–自连接写法

这种自连接方法，没有用到join，就是隐式连接。
用到join连接表的话，就是显示连接

-- 自连接写法

SELECT
	st.* 
FROM
	student st,
	score s1,
	score s2 
WHERE
	st.s_id = s1.s_id 
	AND s1.s_id = s2.s_id 
	AND s1.c_id = '01' 
	AND s2.c_id = '02'

10、查询学过编号为‘01语文’但是没有学过编号为‘02数学’的课程的同学的信息

先通过 case when 把该score 成绩表里面的每个学生的 语文成绩和数学成绩拿出来，看有没有数据。没有数据的就是null，说明该学生没有学习该课程。
然后再连接student 表，获取学生的信息即可。

-- 10、查询学过编号为‘01语文’但是没有学过编号为‘02数学’的课程的同学的信息

SELECT
	st.* 
FROM
	student st
	LEFT JOIN
	(
	-- 子表，用来把score表里面每个学生的‘01’和‘02’课程数据拿出来
	SELECT
		s.s_id,
		max( CASE WHEN c_id = '01' THEN s_score ELSE NULL END ) s01,
		max( CASE WHEN c_id = '02' THEN s_score ELSE NULL END ) s02 
	FROM
		score s 
	GROUP BY
		s_id 
	) t ON t.s_id = st.s_id 
WHERE
	t.s01 >= 0         -- 查询学过‘01’课程的学生
	AND t.s02 IS NULL  -- 查询没有学过‘02’课程的学生