本文中讲解的是当数据库的表中的数据存在缺失值NULL
的时候,该如何进行处理。下面的数据含有两个表teacher
和dept
The school includes many departments. Most teachers work exclusively for a single department. Some teachers have no department.
找出dept为空的老师
select name
from teacher
where dept in NULL; -- 注意不能使用dept=NULL
inner join能够排除老师为空的系或者没有系的老师
select teacher.name, dept.name
from teacher
inner join dept on (teacher.dept=dept.id);
select teacher.name. dept.name
from teacher
full join dept on teacher.dept=dept.id
where teacher.name is not null; -- 方法1
select teacher.name, dept.name
from teacher
left join dept on teacher.dept=detp.id; -- 方法2
-- 方法1
select teacher.name, dept.name
from dept
full join teacher on dept.id=teacher.dept
where dept.name is not null;
-- 方法2
select teacher.name, dept.name
from teacher
right join dept on teacher.dept=detp.id;
select name, coalesce(mobile, '07986 444 2266')
from teacher
select teacher.name, coalesce(dept.name, 'None')
from teacher
left join dept on teacher.dept=dept.id;
select count(name), count(mobile)
from teacher;
select dept.name, count(teacher.name)
from teacher
right join dept on dept.id=teacher.dept
group by dept.name;
select teacher.name,
case when (teacher.dept=1 or teacher.dept=2) then 'Sci'
else 'Art' end
from teacher;
select teacher.name,
case when teacher.dept=1 or teacher.dept=2 then 'Sci'
when teacher.dept=3 then 'Art'
else 'None' end
from teacher
COALESCE takes any number of arguments and returns the first value that is not null.
笔记:取第一个不是NULL值的数据
case
表达式的两种写法
case sex -- 1. 简单表达式
when '1' then '男'
when '2' then '女'
else 'other' end
case when sex='1' then '男' -- 2. 搜索表达式
when sex='2' then '女'
else 'other' end