标准SQL复习

本文章所有SQL均来自SQL COOKBOOK

1:别名: select * from (Select sal as salary,comm as commission from emp ) x wheresalary < 5000

2合并两个字段:select CONCAT( empNO,ename) as NO_NAME from emp  ; 简写：  oracle=> || sqlserver +。

3.条件：select case when sal < 5000 and sal >= 3000 then 'lever 1'when sal < 3000 then 'level 2' else 'no level'end as LEVEL FROM emp

4：还回指定的行数：oracle : select * from emp rownum < 5

                                      SQL SERVER : select  top 5  * from emp

                                      mysql:select * from emp limit 5

5:查找空值: select * from EMP where comm is null         select * from EMP where commis not null

6:转换空值: select coalesce(comm,"0") from emp  将NULL 转换成0

7:in: select * from emp where deptno in(20,30)

8: like select * from emp where deptno in(20,30) and (ename like '%I%' or job like '%er%'   )

9:select * from emp order by sal desc      asc(默认)

10:select * from emp order by deptno ,sal desc 先deptno升序  sal 降序

11: select substr(ename,length(ename)-2) as subname from emp

      orale , mysql 取得字符串中指定起始位置和长度的字符串 substr( string, start_position, [ length ]

     sql server substring len   sbustring (<target string>,<start>,<length>) select*fromuser wheresubstring(name,1,3)='lee'

12:对有空值的排序,select ename,sal, comm ,case when comm is null then 1 else 0 end as is_null
from emp order by is_null, comm

13:select * from emp order by case when job='SALESMAN' then sal else deptno end

14:union/union all  合并的字段可以不一样，但是必须有相同的数据类型，select comm from emp union select sal from emp

15：select sum(distinct sal ) as A, sum(boun) As b
 from( select E.empno, E.sal ,E.sal * case when B.type =1 then 0.1 when B.type = 2 then 0.2 else 0 end as boun from EMP  E
left join bouns B on E.empno = B.empno) X group by empno, 一个人有多种奖金，所以用 sum(distinct sal ) 前提每个人工资不一样