是空值
- is NULL,而不是=NULL
COALESCE
- COALESCE函数的作用:
①给空值置默认值
②返回第一个非空值
/* 将没有手机的老师的电话号码置为07986 444 2266 */
SELECT name, COALESCE(mobile,'07986 444 2266')
FROM teacher
- COUNT(*)是对行计数,包含NULL行,而COUNT(字段名)则不计NULL
- 特别注意:由于NULL的存在,在连接两个表时,选清楚用left join OR right join
/*筛选每个部门和员工人数。员工不一定是老师,因此有的部门可能没有老师*/
SELECT dept.name,COUNT(teacher.name) FROM teacher
RIGHT JOIN dept ON teacher.dept=dept.id/*因为部门可能没有老师,所以用右连接将部门的信息全部保留*/
GROUP BY dept.name
CASE…WHEN
CASE WHEN
THEN
THEN
ELSE
END
/*显示每位教师的姓名,如果老师在1或2系,请在后面加上“Sci”,否则显示“Art”*/
SELECT teacher.name
,CASE WHEN dept.id IN (1,2)
THEN 'Sci'
ELSE 'Art'
END
FROM teacher teacher
LEFT JOIN dept ON (teacher.dept=dept.id)/*显示所有的老师,所以teacher表的信息要全*/