Using Null -SQLZOO
前言
记录一下自己在https://sqlzoo.net复习sql时写的sql语句
此篇为Using Null 篇章
涉及sql知识点
- Null
- JOIN 、LEFT JOIN 、RIGHT JOIN
- COUNT
- COALESCE
- CASE
知识点讲解
想选出Null的值不能写?=NULL要写? is NULL
JOIN(INNER JOIN) 产生的结果集是两表的交集
LEFT JOIN 产生的结果集是表1的所有以及表2中与表1匹配的值
RIGHT JOIN产生的结果集是表2的所有以及表1中与表2匹配的值
COUNT(?)计算某列出现的次数
COALESCE(? ,…)从括号里面获取第一个不为NULL的值,顺序为从左到右
CASE ?1( when ?2 then ?3) … else ?4 end 类似 if else
从?1(列名) 中进行条件判断,如果?1=?2(任意值)就返回?3
所有when都不匹配则返回else后面?4的值
题目涉及的表
1.列出學系department是NULL值的老師
select name from teacher where dept is null
2.注意INNER JOIN 不理會沒有學系的老師及沒有老師的學系。
SELECT teacher.name, dept.name
FROM teacher INNER JOIN dept
ON (teacher.dept=dept.id)
3.使用不同的JOIN(外連接),來列出全部老師。
select t.name,dept.name from teacher t left join dept on t.dept=dept.id
4.使用不同的JOIN(外連接),來列出全部學系。
select t.name,d.name from dept d left join teacher t on t.dept=d.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
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 t.name,coalesce(d.name,'None') from teacher t left join dept d on t.dept=d.id
7.使用COUNT來數算老師和流動電話數目。
select count(name),count(mobile) from teacher
8.使用COUNT 和 GROUP BY dept.name來顯示每一學系的老師數目。 使用 RIGHT JOIN 以確保工程系Engineering 是在當中。
select d.name,count(teacher.name) from teacher right join dept d on teacher.dept=d.id
group by d.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 t.name ,case t.dept when 1 then 'Sci' when 2 then 'Sci' else 'Art' end from teacher t
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 t.name ,case t.dept when 1 then 'Sci' when 2 then 'Sci' when 3 then 'Art' else 'None' end from teacher t