读书笔记 SQL cookbook 1

1. 使用select * 和select 所有field 性能相同

2. 使用内联视图处理别名
select * from (
select sal as salary,comm as commision from emp) X
where salary <5000

3. 连接列
oracle -- ||
mysql --concat(col1,' - - ',col2)
sqlserver -- +

4. 条件逻辑
select sal,
case when sal<='2000' then 'underpaid'
when sal>=4000 then 'overpaid'
else 'ok'
end as status
from emp;

5. 限制行数
mysql: limit 5
oracle: rownum <=5;
sqlserver: top 5
注:这些记录都回都是从第一条返回，固定的。
error: rownum=5

6. 随机返回n行记录
mysql: select ename,job from emp
order by rand() limit 5;
oracle: select * from (
select ename,job from emp order by dbms_random.value() )
where rownum<=5
Q：select ename,job from emp order by dbms_random.value() where rownum<=5

sqlserver: select top 5 ename,job from emp ordr by newid;

7. 空值转换：
select coalesce(col,0) from emp --通用
或者(有点繁琐)：select case
when col is null then 0
else comm
end
from emp

8. 通配符： like '%I%'