【数据库学习篇】sqlzoo之Using Null

【数据库学习篇】sqlzoo之Using Null

前情回顾

  1. INNER JOIN

内连接：A INNER JOIN B
内连接意味着仅保留A表和B表中相匹配的部分，不保留未匹配部分

  1. LEFT JOIN

左连接：A LEFT JOIN B
左连接意味着保留A表所有记录，而对于B表而言，仅保留与A表中字段能匹配的记录，而其他记录全部去除

  1. RIGHT JOIN

右连接：A RIGHT JOIN B
右连接意味着保留B表所有记录，而对于A表而言，仅保留与B表中字段能匹配的记录，而其他记录全部去除

Using Null

跟着sqlzoo的引导，了解更多JOIN操作，链接如下：sqlzoo的Using Null篇

分析和了解表

需要使用的表有两张，一张是teacher表，另一张是actors表，两个实体之间的关系是多对多关系。
teacher(id, dept, name, phone, mobile)

  • id：int类型，元组数据的唯一标识符
  • dept：char类型，教师所在部门号
  • name：char类型，教师名称
  • phone：char类型，手机短号
  • mobile：char类型，座机号码

dept(id, name)

  • id：int类型，元组的唯一标识符
  • name：char类型，部门名称

正题题解:NULL, INNER JOIN, LEFT JOIN, RIGHT JOIN

1.

List the teachers who have NULL for their department.
列举出这样一类教师，这些教师的部门为NULL

SELECT name
 FROM teacher
 WHERE dept is NULL

思考过程：
单表选择直接按照题目所给条件筛选即可；
【注意】null作为非空值，不属于数字，因此不能使用等号“=”作为对比

2.

Note the INNER JOIN misses the teachers with no department and the departments with no teacher.
INNER JOIN 排除那些没有所属部门的老师和那些没有老师任职的部门

以教师列表的dept=null为筛选条件，展示这些教师的全部信息。

SELECT teacher.name, dept.name
 FROM teacher INNER JOIN dept
 ON (teacher.dept=dept.id)

思考过程：
多表选择：建立内连接，但是保证；
需要注意的一点就是，null作为非空值，不属于数字，因此不能使用等号“=”作为对比

3.

Use a different JOIN so that all teachers are listed.
使用不同的JOIN方法，以至于展示所有教师及其所述部门名称，

SELECT teacher.name,dept.name
 FROM teacher LEFT JOIN dept
 ON (teacher.dept=dept.id)

思考过程：
多表选择：题目要求罗列所有教师，因此采用左连接，保留teacher表所有教师记录，去除dept表中未能匹配记录；
需要注意的一点就是，null作为非空值，不属于数字，因此不能使用等号“=”作为对比

4.

Use a different JOIN so that all departments are listed.
使用不同的JOIN方法，以至于罗列所有部门信息及其所属教师信息

SELECT teacher.name,dept.name
 FROM teacher RIGHT JOIN dept
 ON (teacher.dept=dept.id)

思考过程：
多表选择：题目要求罗列所有部门信息，因此采用右连接，保留dept表所有部门记录，去除teacher表中未能匹配记录；
需要注意的一点就是，null作为非空值，不属于数字，因此不能使用等号“=”作为对比

Using the COALESCE function

COALESCE函数
使用COALESCE的目的是在多个表达式中返回第一个非空值​（即非 NULL 值）。如果所有参数均为 NULL，则返回 NULL。如：

SELECT COALESCE(column1, column2, '默认值') FROM table;
  • 若 column1 非空，则返回 column1；
  • 若 column1 为空但 column2 非空，则返回 column2；
  • 若所有列均为空，则返回 ‘默认值’

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’
使用COALESCE函数输出手机号码如果查不到老师对应的手机号码，则使用默认值’07986 444 2266’输出结果，否则就给出老师名称，以及手机号码

SELECT name,COALESCE(mobile, '07986 444 2266')
 FROM teacher

思考过程：
单表选择：题目要求罗列所有老师姓名及其手机号码，简单SELECT方式即可实现
需注意：手机号码的输出使用COALESCE方式，指定当手机号码为空，使用默认指定参数输出"07986 444 2266"；

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.
使用COALESCE函数以及LEFT JOIN方法打印教师名称和部门名称，当此处没有部门时，采用’None’字符填充

SELECT teacher.name,COALESCE(dept.name, 'None')
 FROM teacher LEFT JOIN dept
 ON (teacher.dept=dept.id)

思考过程：

  1. 确定查询范围：教师信息存放在teacher表，部门名称信息存放在dept表中，所以这是一个多表查询
  2. 选择结果：题目要求输出的值为教师名称teacher.name，部门名称dept.name，并且对部门名称加上限制条件，假如部门名称为空，则以None字符填充

7.

Use COUNT to show the number of teachers and the number of mobile phones.
使用COUNT聚集函数展示教师数量和手机数量

SELECT COUNT(name),COUNT(mobile)
 FROM teacher

思考过程：

  1. 确定查询范围：教师数量和手机号码两个字段均在teacher表中存在，因此这是一个单表查询
  2. 考察内容：COUNT聚集函数的使用方法
    【tips】COUNT聚集函数在聚合内容时，自动略过空值

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.
利用COUNT和GROUP BY两个聚合函数，统计各个部门中的职工人数，即教师人数。使用RIGHT JOIN方法确保Engineering部门也被罗列。

SELECT dept.name, count(teacher.name)
 FROM teacher RIGHT JOIN dept
 ON (teacher.dept=dept.id)
 group by dept.name

思考过程：

  1. 确定查询范围：需要使用的信息包括部门名称信息以及教师信息，所以这是一个多表查询
  2. 选择结果：部门名称和员工数量，在员工数量上需要使用COUNT函数进行聚合，其次由于做了右连接并采用了COUNT函数聚合，因此需要使用group by函数对部门名称进行归类

using CASE

CASE 条件判断功能语句，根据不同条件返回不同结果

CASE 
 WHEN condition1 THEN result1 
 WHEN condition2 THEN result2 
 WHEN condition3 THEN result3 
 ELSE default_result 
 END

当指定筛选字段满足condition1条件时，输出result1结果；满足condition2条件时，输出result2结果；…

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.
使用 CASE 语句显示每位教师的姓名，并根据其所属部门（dept）附加 ‘Sci’ 或 ‘Art’：

SELECT teacher.name,
	case when teacher.dept in (1,2) then 'Sci'
	else 'Art'
	end
 FROM teacher

思考过程：

  1. 确定查询范围：根据教师所在部门信息为教师打标签，结果输出需要教师名称，条件筛选需要使用的只有部门id，这两个信息在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.
使用 CASE 语句显示每位教师的姓名，并根据其所属部门（dept）附加 ‘Sci’ 或 ‘Art’：

  • 若教师属于部门 1 或 2，显示为 ​姓名 + 'Sci’​​
  • 其他情况显示为 ​姓名 + 'Art’​
SELECT teacher.name,
	case 
	when teacher.dept in (1,2) then 'Sci'
	when teacher.dept = 3 then 'Art'
	else 'None'
	end
 FROM teacher

思考过程：

  1. 确定查询范围：根据教师所在部门信息为教师打标签，结果输出需要教师名称，条件筛选需要使用的只有部门id，这两个信息在teacher表中均以涵盖，因此这是一个涉及单表的查询
  2. 考察内容：CASE语法的使用