SQL Cookbook读书笔记（更新ing）
1. 查询
1.1 获取所有数据
select * from emp对于性能而言，和指定列相同。在编程中最好显式指定列
1.2 获取满足某个条件的行
select * from emp where deptno=10where后跟条件，所有满足条件的行会被返回
1.3 获取满足多个条件的行
-- For example, if you would like to find all the employees in department 10, along with any employees who earn a commission, along with any employees in department 20 who earn at most $2,000
select * from emp where deptno=10 or comm is not null or (deptno=20 and sal >= 2000)结合where和and、or和括号。括号中的条件会被一起计算
1.4 获取特定列
select ename, deptno, sal from emp指定列名
1.5 给列一个有意义的名字
select sal as salary, comm as commission from emp使用as关键字，有些数据库不需要as，但是都允许
1.6 在where子句中使用别名
select * from (select sal as salary, comm as commission from emp) x where x.salary>=2000直接在where子句中使用别名是非法的，可以将其包在子查询中
where在select之前计算，所以不能直接用别名。from在where前计算完成，所以外层的where可以看到子查询中的别名
1.7 拼接多列的值
-- 需要的内容来自于多列的值拼接起来
-- DB2, Oracle, PostgreSQL
select ename||' WORK AS A '||job as msg from emp where deptno=10
-- MySQL
select concat(ename, ' WORK AS A ', job) as msg from emp where deptno=10
-- SQL Server
select ename + ' WORK AS A ' + job as msg from emp where deptno=101.8 在select中使用条件逻辑
-- you would like to produce a result set such that if an employee is paid $2,000 or less, a message of “UNDERPAID” is returned; if an employee is paid $4,000 or more, a message of “OVERPAID” is returned; and if they make somewhere in between, then “OK” is returned
select ename, sal,
case when sal <= 2000 then 'UNDERPAID'
when sal >= 4000 then 'OVERPAID'
else 'OK' end as status
from emp使用CASE语句，在返回的内容上做条件逻辑。else子句可以忽略，如果没有else，那么对于所有不满足其它case的结果都返回null
1.9 限制返回的行数
-- DB2
select * from emp fetch first 5 rows only; FETCH FIRST子句
-- MySQL and PostgreSQL
select * from emp limit 5
-- Oracle
select * from emp where rownum <= 5
-- 1. 执行sql
-- 2. 取第一行作为rownumber1
-- 3. 是否达到5，如果达到则不返回内容，否则返回当前记录
-- 4. 取下一条记录作为rownumber+1
-- 5. 执行3
-- 由于oracle的rownum函数机制，rownum=5的条件不会成功，因为rownumber无法增长。而rownum=1能成功是因为为了确认结果集中是否有数据，oracle至少会尝试取一条结果
-- SQL Server
select top 5 * from emp1.10 从表中返回随机结果
-- 使用dbms提供的随机函数，然后根据随机值sort，最后使用限制工具限制条数
-- DB2
select ename, job from emp order by rand() fetch first 5 rows only
-- MySQL
select ename, job from emp order by rand() limit 5
-- PostgreSQL
select ename, job from emp order by random() limit 5
-- Oracle
select * from (select ename, job from emp order by dbms_random.value()) x where rownum <= 5
-- SQL Server
select top 5 ename, job from emp order by newid()
-- order by会根据函数返回值（每一行）来进行重新排序，然后再进行select1.11 查找NULL值
-- 查找所有某列为NULL值的行
select * from emp where comm is nullNULL不能用=和<>来判断，只能用is和is not
1.12 将NULL值转化为非NULL值返回
-- 使用coalesce函数来替换NULL值
select coalesce(comm, 0) from empcoalesce函数接收一个或多个参数，返回第一个非NULL值，这个函数可以应用于所有dbms
1.13 根据模式查询
-- 只返回满足某些模式的行
-- Of the employees in departments 10 and 20, you want to return only those that have either an “I” somewhere in their name or a job title ending with “ER”
select ename, job from emp where deptno in (10, 20) and ename like '%I%' or job like '%ER'使用like结合通配符%。大多数数据库也支持_通配符匹配单个字符
2. 查询结果排序
2.1 以特定顺序返回查询结果
select ename, job, sal from emp where DEPTNO=10 order by sal
select ename, job, sal from emp where DEPTNO=10 order by 3使用order by子句。默认升序排列（SAL-ASC），指定DESC则为降序排列，order by后可以不加名称，使用数字（从1开始）
2.2 根据多个字段排序
-- sort the rows from EMP first by DEPTNO ascending, then by salary descending
select empno, deptno, sal, ename, job from emp order by 2 asc, 3 desc使用order by，多个字段用逗号分隔。优先级是从左往右。可以根据不在select列表里的列排序，但这种情况必须使用列名而不能用数字。如果使用了group by或者distinct，那么不能根据不在select选择列的字段排序
2.3 根据子串排序
-- return employee names and jobs from table EMP and sort by the last two characters in the JOB field
-- DB2, MySQL, Oracle, and PostgreSQL
select ENAME, JOB from emp order by SUBSTR(job, LENGTH(job)-1)
-- SQL Server
select ename, job from emp order by substring(job, len(job)-1, 2)2.4 排序字母-数字混合数据
-- 某一个字段中包含数字和字母，需要仅根据其中一部分来排序
-- You want to sort the results by DEPTNO or ENAME
-- Oracle, SQL Server, and PostgreSQL
/* ORDER BY DEPTNO */
select data
from V
order by replace(data,
replace(
translate(data,'0123456789','##########'),'#',''),'')
/* ORDER BY ENAME */
select data
from V
order by replace(
translate(data,'0123456789','##########'),'#','')
-- DB2（cast(deptno as char(2))进行类型转换）
/* ORDER BY DEPTNO */
select *
from (
select ename||' '||cast(deptno as char(2)) as data
from emp
order by replace(data,
replace(
translate(data,'##########','0123456789'),'#',''),'')
/* ORDER BY ENAME */
select *
from (
select ename||' '||cast(deptno as char(2)) as data
from emp
order by replace(
translate(data,'##########','0123456789'),'#','')使用translate和replace来替换数字/字母，然后再通过order by排序。目前mysql不支持translate，所以没有解决方案
2.5 在排序时处理NULL值
-- 直接用这种方法没办法控制包含NULL值的行的位置
select ename, sal, comm from emp order by 3
-- DB2, MySQL, PostgreSQL, and SQL Server
select x.ename, x.sal, x.comm from
(select ename, sal, comm,
case when comm is null then 0
when comm is not null then 1 end as is_null
from emp) x order by x.is_null desc, x.comm
-- Oracle
select ename, sal, comm from emp order by comm nulls first/last2.6 根据数据满足的条件排序
-- if JOB is SALES‐MAN, you want to sort on COMM; otherwise, you want to sort by SAL
select ename, sal, job, comm from emp order by case when job='SALESMAN' then comm else sal end在order by子句中使用case
3. 多表查询
3.1 将一个查询集放到另一个上面
-- The tables do not necessarily have a common key, but their columns do have the same data types
select ename as ename_and_dname, deptno from emp where deptno=10
union all
select '----------------', null -- (from t1)
union all
select dname, deptno from deptUNION ALL和UNION的区别在于 前者会包含重复值（在两个表中） ，和distinct相似，能不用尽量不用。UNION的要求是结果个数和类型相同
3.2 组合关联的行
-- display the names of all employees in department 10 along with the location of each employee’s department
select e.ename, d.loc from emp e, dept d where e.deptno=d.deptno and e.deptno=10这是一个内联（inner-join）的例子。默认的join操作返回的是笛卡尔积
3.3 查找两张表中相同的行
-- You want to return the EMPNO, ENAME, JOB, SAL, and DEPTNO of all employees in EMP that match the rows from view V（所有job是clerk的员工）
-- MySQL and SQL Server
select e.empno, e.ename, e.job, e.sal, e.deptno from emp e, V where e.ename=V.ename and e.job=V.job and e.sal=V.sal
select e.empno, e.ename, e.job, e.sal, e.deptno from emp e join V on (e.ename=V.ename and e.job=V.job and e.sal=V.sal)
-- DB2, Oracle, and PostgreSQL 使用集合操作（如果不需要取V中的数据）
select empno, ename, job, sal, deptno from emp where (ename, job, sal) in (select ename, job, sal from emp intersect select ename, job, sal from V)3.4 获取在一张表中的数据且不再另一张表
-- find which departments (if any) in table DEPT do not exist in table EMP. In the example data
-- DB2, PostgreSQL, and SQL Server
select deptno from dept
except
select deptno from emp
-- Oracle
select deptno from dept
minus
select deptno from emp
-- MySQL
select deptno
from dept
where deptno not in (select deptno from emp)
差集函数让这种操作变得简单。EXCEPT操作取第一个结果集的内容并移除在第二个结果集出现的内容。使用这个操作符的限制包括，数据类型和值的个数必须在两个结果集中匹配，且不会返回重复值（与子查询不同）
有NULL参与的逻辑计算规则
| or | t | f | n |
|---|---|---|---|
| t | t | t | t |
| f | t | f | n |
| n | t | n | n |
| not | |
|---|---|
| t | f |
| f | t |
| n | n |
| and | t | f | n |
|---|---|---|---|
| t | t | f | n |
| f | f | f | f |
| n | n | f | n |
可以使用关联子查询（correlated subquery）来解决NULL的问题，因为这种情况下外部查询的行在子查询中被引用
select d.deptno
from dept d
where not exists(select 1 from emp e where d.deptno = e.deptno)
select deptno
from dept
where deptno not in (select * from new_dept)
select d.deptno
from dept d
where not exists(select 1 from new_dept e where e.deptno = d.deptno)3.5 获取一张表中和另一张表没有关联的行
-- find which departments have no employees
-- DB2, MySQL, PostgreSQL, and SQL Server
select d.*
from dept d
left outer join emp e on d.deptno = e.DEPTNO
where e.DEPTNO is null3.6 添加join到一个查询且不影响其它的join
-- return all employees, the location of the department in which they work, and the date they received a bonus
-- DB2, MySQL, PostgreSQL, and SQL Server
select e.empno,
d.loc,
eb.received
from emp e
join dept d on e.DEPTNO = d.deptno
left join emp_bonus eb on e.empno = eb.empno
order by 2
-- scalar subquery（出现在select语句中的子查询），必须返回单个值
select e.empno,
d.loc,
(select eb.received from emp_bonus eb where eb.empno = e.empno) as received
from emp e
join dept d on e.DEPTNO = d.deptno
order by 2
3.7 确定两张表是否有相同的数据
-- MySQL and SQL Server
select e.empno,
e.ENAME,
e.JOB,
e.MGR,
e.HIREDATE,
e.SAL,
e.COMM,
e.DEPTNO,
e.cnt
from (select empno,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO,
count(*) as cnt
from emp
group by empno, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) e
where not exists(
select NULL
from (select empno,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO,
count(*) as cnt
from V
group by empno, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) v
where v.empno = e.empno
and v.ENAME = e.ENAME
and v.JOB = e.JOB
and coalesce(v.MGR, 0) = coalesce(e.MGR, 0)
and v.HIREDATE = e.HIREDATE
and v.SAL = e.SAL
and coalesce(v.COMM, 0) = coalesce(e.COMM, 0)
and v.DEPTNO = e.DEPTNO
and v.cnt = e.cnt
union all
select e.empno,
e.ENAME,
e.JOB,
e.MGR,
e.HIREDATE,
e.SAL,
e.COMM,
e.DEPTNO,
e.cnt
from (select empno,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO,
count(*) as cnt
from V
group by empno, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) e
where not exists(
select NULL
from (select empno,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO,
count(*) as cnt
from emp
group by empno, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) v
where v.empno = e.empno
and v.ENAME = e.ENAME
and v.JOB = e.JOB
and coalesce(v.MGR, 0) = coalesce(e.MGR, 0)
and v.HIREDATE = e.HIREDATE
and v.SAL = e.SAL
and coalesce(v.COMM, 0) = coalesce(e.COMM, 0)