158、MySQL入门（八）：使用NULL

Sqlzoo习题练习：Using NULL

习题链接：<u>http://sqlzoo.net/wiki/Using_Null</u>

一所学校会包括许多部门。大多数教师只为一个部门工作。有些老师没有部门。

下面是Using NULL习题中要用到数据库中的两个表格teacher和dept（部门），分别存储着老师的信息及老师所在的部门信息。

1.表格teacher和dept.png

下面为Using NULL 习题内容：

知识点：NULL 值

NULL 值是遗漏的未知数据。

默认地，表的列可以存放 NULL 值。

如果表中的某个列是可选的，那么我们可以在不向该列添加值的情况下插入新记录或更新已有的记录。这意味着该字段将以 NULL 值保存。

NULL 值的处理方式与其他值不同。

NULL 用作未知的或不适用的值的占位符。

注释：无法比较 NULL 和 0；它们是不等价的。

测试 NULL 值时，必须使用 IS NULL 和 IS NOT NULL 操作符。

--#1
/*
List the teachers who have NULL for their department. 
*/
SELECT teacher.name
FROM teacher 
WHERE dept IS NULL;

知识点：内连接（INNER JOIN）
INNER JOIN 关键字
在表中存在至少一个匹配时，INNER JOIN 关键字返回行。
INNER JOIN 关键字语法
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
注释：INNER JOIN 与 JOIN 是相同的。

--#2
/*
Note the INNER JOIN misses the teachers with no department and the departments with no teacher. 
*/
SELECT teacher.name AS teacher_name,
       dept.name AS dept_name
FROM teacher INNER JOIN dept 
ON (teacher.dept = dept.id);

知识点：LEFT JOIN 关键字
LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。
LEFT JOIN 关键字语法
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
注释：在某些数据库中， LEFT JOIN 称为 LEFT OUTER JOIN。

--#3
/*
Use a different JOIN so that all teachers are listed. 
*/
SELECT teacher.name AS teacher_name,
       dept.name AS dept_name
FROM teacher LEFT JOIN dept
ON (teacher.dept = dept.id);

知识点：RIGHT JOIN 关键字
RIGHT JOIN 关键字会从右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中没有匹配的行。
RIGHT JOIN 关键字语法
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
注释：在某些数据库中， RIGHT JOIN 称为 RIGHT OUTER JOIN。

--#4
/*
Use a different JOIN so that all departments are listed. 
*/
SELECT teacher.name AS teacher_name,
       dept.name AS dept_name
FROM teacher RIGHT JOIN dept
ON (teacher.dept = dept.id);

知识点：COALESCE用法
COALESCE返回其参数中第一个非空表达式。
语法
COALESCE (expression [ ,...n ] )
参数
Expression表示任何类型的表达式。
返回类型
返回数据类型优先级最高的 expression 的数据类型。如果所有表达式都不可为 Null，则结果的类型也不可为 Null。
注释
如果所有参数均为 NULL，则 COALESCE 返回 NULL。

--#5
/*
Use COALESCE to print the mobile number. Use the number '07986 444 2266' if there is no number given. Show teacher name and mobile number or '07986 444 2266'
*/
SELECT name,
COALESCE(mobile,'07986 444 2266')
FROM teacher;

--#6
/*
Use the COALESCE function and a LEFT JOIN to print the teacher name and department name. Use the string 'None' where there is no department. 
*/
SELECT teacher.name,
COALESCE(dept.name,'None')
FROM teacher LEFT JOIN dept
ON (teacher.dept = dept.id);

--#7
/*
Use COUNT to show the number of teachers and the number of mobile phones. 
*/
SELECT COUNT(name),COUNT(mobile)
FROM teacher ;

--#8
/*
Use COUNT and GROUP BY dept.name to show each department and the number of staff. Use a RIGHT JOIN to ensure that the Engineering department is listed. 
*/
SELECT dept.name,COUNT(teacher.name)
FROM teacher RIGHT JOIN dept
ON (teacher.dept = dept.id)
GROUP BY dept.name;

知识点：Case When用法
Case具有两种格式。简单Case函数和Case搜索函数。
第一种 格式 : 简单Case函数 :
格式说明
　　　　case 列名
　　　　when 条件值1 then 选择项1
　　　　when 条件值2 then 选项2.......
　　　　else 默认值 end
第二种 格式 :Case搜索函数
格式说明
　　　　case
　　　　when 列名= 条件值1 then 选择项1
　　　　when 列名=条件值2 then 选项2.......
　　　　else 默认值 end

--#9
/*
Use CASE to show the name of each teacher followed by 'Sci' if the teacher is in dept 1 or 2 and 'Art' otherwise. 
*/
SELECT teacher.name,
CASE 
   WHEN teacher.dept IN (1,2) 
   THEN 'Sci'
   ELSE 'Art'
END
FROM teacher;

--#10
/*
Use CASE to show the name of each teacher followed by 'Sci' if the teacher is in dept 1 or 2, show 'Art' if the teacher's dept is 3 and 'None' otherwise. 
*/
SELECT teacher.name,
CASE 
   WHEN teacher.dept IN (1,2) 
   THEN 'Sci'
   WHEN teacher.dept = 3 
   THEN 'Art'
   ELSE 'None'
END
FROM teacher;