SQL 经典实例

《SQL经典实例》

1，在WHERE子句中引用别名列
直接引用-报错
把查询包装为一个内嵌视图，这样就可以引用别名列了

select *
from (
select sal as salary, comm as commission
from emp
) x
where salary < 5000

同理：当你想在 WHERE 子句中引用下列内容时，考虑再嵌套一下
• 聚合函数
• 标量子查询
• 窗口函数
• 别名

2，想要获得如下结果集。
CLARK WORKS AS A MANAGER
KING WORKS AS A PRESIDENT
MILLER WORKS AS A CLERK
用CONCAT 函数

select concat(ename, ' WORKS AS A ',job) as msg
from emp

3，：如果员工的工资少于 2000 美元，就返回 UNDERPAID；如果超过 4000 美元就返回OVERPAID；若介于两者之间则返回 OK
使用 CASE 表达式

select ename,sal,
case when sal <= 2000 then 'UNDERPAID'
when sal >= 4000 then 'OVERPAID'
else 'OK'
end as status
from emp

4，随机返回若干行记录–order by rand() limit 5

select ename,job
from emp
order by rand() limit 5

5，查找Null值–where comm is null
where comm is not null

6,把Null值转换为实际值，两种方法

select coalesce(comm,0)
from emp

select case
when comm is not null then comm
else 0
end
from emp

7， order by 3 – 数值从 1 开始，从左向右匹配 SELECT 列表里的列

8，按照职位字段的最后两个字符对检索结果进行排序
order by substr(job,length(job)-2)

9，排序时对Null值的处理
使用 CASE 表达式标记 Null 值。该标记有两种可能的取值：一种代表 Null 值，另一种代表非 Null 值。----加一个标识列
加的标识列没法直接用，再套一次然后判断

 select ename,sal,comm
 from (
 select ename,sal,comm,
 case when comm is null then 0 else 1 end as is_null
 from emp
 )x
 order by is_nulldesc,comm

10，依据条件逻辑动态调整排序项
如果 JOB 等于 SALESMAN，就要按照 COMM 来排序；否则，按照 SAL 排序。
order by 里是可以再加一个case when 的

 select ename,sal,job,comm
 from emp
 order by case when job = 'SALESMAN' then comm else sal end

11，

 select ename as ename_and_dname, deptno
 from emp
 where deptno = 10
 union all
 select '----------', null
 from t1
 union all
 select dname, deptno
 from dept

12，查找只存在于一个表中的数据
找出在 DEPT 表中存在而在 EMP 表里却不存在的部门编号

 select deptno
 from dept
 where deptno not in (select deptno from emp)

13，从一个表检索与另一个表不相关的行
在一个表里查找与另一个表不相匹配的行。例如，你想找出哪些部门没有员工。

1 select d.*
2 from dept d left outer join emp e
3 on (d.deptno = e.deptno)
4 where e.deptno is null