SQL-cookbook

检索记录

1. WHERE子句中AND优先执行

2. 在WHERE子句中引用别名列

将查询结果包装为内嵌视图，这样就可以使用别名列

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

3. 串联多列的值

## DB2、Oracle、PostgreSQL
select ename||'work as a'||job as msg from emp where deptno = 10;
## MySQL
select concat(ename,'work as a',job) msg from emp where deptno = 10;
## SQL Server
select ename+'work as a'+job as msg from emp where deptno = 10;

4. SELECT语句中使用条件逻辑

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

5. 限定返回行数

## DB2 - 使用 FETCH FIRST 子句
## MySQL、PostgreSQL - 使用 LINMIT 子句
## Oracle - 在 WHERE 子句中限制 ROWNUM 的值获取指定行数的结果集
## SQL Server - 使用 TOP 关键词限定返回行数

6. 随机返回若干行记录

## DB2

## MySQL
select ename,job from emp order by rand() limit 5;
## PostgreSQL

## Oracle
select * from (select ename,job from emp order by dbms_random.value())
where rownum <= 5;
## SQL Server

7. 查找Null值

## 判断特定列是否为null 必须用 IS NULL
## 替换null值
select *,coalesce(comm,0) from emp;
## 也可以使用 case

8. 模糊匹配

LIKE '%' 匹配多个字符
LIKE '_' 匹配当个字符

查询结果排序

1. 根据子串排序

order by substr(col_name, length(col_name-2))

2. 对含有字母和数字的列排序

3. 排序时对Null值处理

MySQL默认升序排序Null值在前

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

## Oracle9i+
# NULLS FIRST/LAST

4. 依据条件逻辑动态调整排序项

CASE WHEN... THEN... ELSE... END

多表查询

插入、更新和删除

1. 复制数据到另一个表

insert into A() # 指定插入列
select * from B;

2. 复制表定义

使用 Create Table As Select(CTAS)语句时，需要为where子句指定一个不可能为真的条件，否则查询结果集会被写入新表。

# DB2
create table new_table like old_table;
# Oracle、MySQL、PostgreSQL
create table new_table
as select * from old_table where 1 = 0;
# SQL Server
select * into new_table from old_table where 1 = 0;

3. 多表插入

# Oracle
insert all|first
	where conditionA
		into table_b(...) values(...)
	where conditionB
		into table_c(...) values(...)
	else
		into table_d(...) values(...)
select (...)
	from table_a;
# DB2 需要给表加约束条件
# MySQL、PostgreSQL、SQL Server暂不支持多表插入

Oracle多表插入 insert all 和 insert first 的区别

4. 禁止插入特定列

创建一个视图，只暴露希望更改的列，强制所有的 insert语句被传送到该视图

5. 当相关行存在时更新记录

update A set ... where a in (select ... from B);

update emp set sal = sal*1.20 where exists (
	select null from emp_bonus where emp.empno = emp_bonus.empno);

6. 使用另一个表的数据更新记录

7. 合并记录

8. 删除全表记录/删除指定记录/删除单行记录

# 删除所有记录
delete from emp;
# 删除指定记录
delete from emp where deptno = 10;
# 删除单行记录 确保删除条件基于主键或者唯一键

9. 删除违反参照完整性的记录

delete from A where not exists(...);

delete from A where ... not in (...);

10. 删除重复记录

明确重复值

11. 删除被其他表参照的记录

字符串处理

1. 遍历字符串（一个字符一行显示）
2. 嵌入引号
3. 统计字符出现的次数

# 除法有小数
select (length('10,CLARK,MANAGER') - length(replace('10,CLARK,MANAGER',',',''))) / length(',') as cnt from dual;

4. 删除不需要的字符

5. 字和字符数据