SQLZOO USING NULL

最新推荐文章于 2025-03-07 14:30:08 发布

原创最新推荐文章于 2025-03-07 14:30:08 发布 · 1.5k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #mysql #sqlzoo

MySQL 专栏收录该内容

10 篇文章

订阅专栏

本文详细介绍了使用SQL进行数据查询的各种技巧，包括如何处理空值、不同类型的JOIN操作及其应用场景、利用COALESCE函数处理缺失数据、使用CASE语句进行条件判断等。通过这些实用的SQL技巧，读者可以更加高效地管理和分析数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1:List the teachers who have NULL for their department.

SELECT name FROM teacher
WHERE dept IS NULL

2:Note the INNER JOIN misses the teachers with no department and the departments with no teacher.

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

3:Use a different JOIN so that all teachers are listed.

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

4:Use a different JOIN so that all departments are listed

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

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

这里的COALESCE函数，是在范围内取值的作用，但是他会忽视NULL的值去取第一个非NULL值

比如COALESCE(x,y,z) =x ，那就说明x不是NULL

=y , 那就说明x是NULL，而y不是NULL

=z ，那就说明x和y都是NULL，而z不是NULL

=NULL 说明xyz都是NULL

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

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 name,
CASE WHEN dept IN(1,2) THEN 'Sci'
ELSE 'Art' END
FROM teacher

注意别漏END

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 name,
CASE WHEN dept IN(1,2) THEN'Sci'
WHEN dept = 3 THEN 'Art'
ELSE 'None'