我与sql有个约会-sql总结（二）

查询结果排序

以指定顺序返回查询结果

select ename,job,sal from emp where deptno = 10 order by sal asc

注意：默认asc升序，desc降序

多字段排序

select empno,deptno,sal,ename,job from emp order by deptno, sal desc

依据子串排序

DB2、MySQL、Oracle 和 PostgreSQL

select ename,job from emp  order by substr(job,length(job)-2)

SQL Server

select ename,job from emp order by substring(job,len(job)-2,2)

排序时对Null值的处理

对于mysql，DESC 降序时候默认null值排在后面、ASC升序时默认null值排在前面

-- null将排在后面
select * from bjy_order order by order_number is null,order_number asc;

使用case when更加具有灵活性

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

orcale

-- null排在后面last
select ename,sal,comm from emp order by comm nulls last

-- null排在前面first
select ename,sal,comm from emp order by comm nulls first

多表查询

叠加两个行集

 select ename as ename_and_dname, deptno from emp where deptno = 10 
 union all 
 select dname, deptno from dept

注意：

1、union all 不会去除重复项，而union会

2、UNION ALL将多个表中的行并入一个结果集。对于所有的集合运算来说，SELECT列表里的所

有项目必须保持数目相同，且数据类型匹配

合并相关行

select e.ename, d.loc from emp e, dept d where e.deptno = d.deptno and e.deptno = 10

以上写法等同于内连接

select e.ename, d.loc 
from emp e inner join dept d 
on (e.deptno = d.deptno) 
where e.deptno = 10

查找只存在于一个表中的数据

DB2 和 PostgreSQL 使用集合运算EXCEPT。

select deptno from dept 
except 
select deptno from emp

Oracle

使用集合运算MINUS。

select deptno from dept 
minus 
select deptno from emp

MySQL 和 SQL Server

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

注意：

在 SQL 中，TRUE or NULL的运算结果是TRUE，但FALSE or NULL的运算结果却是Null！

为了避免NOT IN和Null值带来的问题，需要结合使用NOT EXISTS和关联子查询。

select d.deptno 
 from dept d 
 where not exists ( select null 
 from emp e 
 where d.deptno = e.deptno )

从一个表检索与另一个表不相关的行

-- outer可省略
select d.* 
from dept d left outer join emp e 
on (d.deptno = e.deptno) 
where e.deptno is null

组合使用连接查询与聚合函数

方案一：去重（先连接，后聚合）

MySQL 和 PostgreSQL

使用DISTINCT计算工资总额。

 select deptno, 
 sum(distinct sal) as total_sal, 
 sum(bonus) as total_bonus 
 from ( 
 select e.empno, 
 e.ename, 
 e.sal, 
 e.deptno, 
 e.sal*case when eb.type = 1 then .1 
when eb.type = 2 then .2 
else .3 
end as bonus 
from emp e, emp_bonus eb 
where e.empno = eb.empno 
and e.deptno = 10 
) x 
group by deptno 

DB2、Oracle 和 SQL Server
上述解决方案也适用于这些数据库。另外，它们还支持窗口函数SUM OVER。

 select distinct deptno,total_sal,total_bonus 
 from ( 
 select e.empno, 
 e.ename,  
 sum(distinct e.sal) over 
 (partition by e.deptno) as total_sal, 
 e.deptno, 
 sum(e.sal*case when eb.type = 1 then .1 
 when eb.type = 2 then .2 
else .3 end) over 
(partition by deptno) as total_bonus 
from emp e, emp_bonus eb 
where e.empno = eb.empno 
and e.deptno = 10 
) x

方案二：先聚合，再连接
适用于所有的关系数据库管理系统

select d.deptno, 
 d.total_sal, 
 sum(e.sal*case when eb.type = 1 then .1 
 when eb.type = 2 then .2 
 else .3 end) as total_bonus 
 from emp e, 
 emp_bonus eb, 
 ( 
select deptno, sum(sal) as total_sal 
 from emp 
 where deptno = 10 
 group by deptno 
 ) d 
 where e.deptno = d.deptno 
 and e.empno = eb.empno 
 group by d.deptno,d.total_sal

DB2、Oracle 和 SQL Server
上面的另一种解决方案利用了窗口函数SUM OVER。下面的查询语句来自该解决方案的第 3

行至第 14 行，返回的结果集如下。

select e.empno, 
 e.ename, 
 sum(distinct e.sal) over 
 (partition by e.deptno) as total_sal, 
 e.deptno, 
 sum(e.sal*case when eb.type = 1 then .1 
 when eb.type = 2 then .2 
 else .3 end) over 
 (partition by deptno) as total_bonus 
 from emp e, emp_bonus eb 
 where e.empno = eb.empno 
 and e.deptno = 10

从多个表中返回缺失值

全外连接

select d.deptno,d.dname,e.ename 
from dept d full outer join emp e 
on (d.deptno=e.deptno) 

等同于以下语句

select d.deptno,d.dname,e.ename 
from dept d right outer join emp e 
on (d.deptno=e.deptno) 
union 
select d.deptno,d.dname,e.ename 
from dept d left outer join emp e 
on (d.deptno=e.deptno)

插入、更新和删除

插入新记录

insert into dept (deptno,dname,loc) values (50,'PROGRAMMING','BALTIMORE')

对于 DB2 和 MySQL，你可以选择一次插入一行，或者通过附加多个VALUES列表来一次性
插入多行记录。

/* 多行插入 */ 
insert into dept (deptno,dname,loc) 
values (1,'A','B'), 
       (2,'B','C') 

你必须严格遵守 SELECT * 语句输出结果里各列的显示顺序。

insert into dept 
values (50,'PROGRAMMING','BALTIMORE')

插入默认值

create table D (id integer default 0)

以下语句会为id默认插入0

insert into D (name) values ('Bar')

使用Null覆盖默认值

insert into d (id, foo) values (null, 'Brighten')

复制数据到另一个表

insert into dept_east (deptno,dname,loc) 
select deptno,dname,loc 
from dept 
where loc in ( 'NEW YORK','BOSTON' )

复制表定义

DB2

使用CREATE TABLE语句和LIKE子句。

create table dept_2 like dept 

Oracle、MySQL 和 PostgreSQL
使用CREATE TABLE语句和一个不返回任何数据的子查询。

create table dept_2 
as 
select * 
from dept 
where 1 = 0 

SQL Server
使用SELECT语句和INTO子句，但要保证该查询不返回任何数据。

select *  
into dept_2  
from dept 
where 1 = 0

多表插入

Oracle

insert all  
when loc in ('NEW YORK','BOSTON') then 
into dept_east (deptno,dname,loc) values (deptno,dname,loc) 
when loc = 'CHICAGO' then 
into dept_mid (deptno,dname,loc) values (deptno,dname,loc) 
else 
into dept_west (deptno,dname,loc) values (deptno,dname,loc) 
select deptno,dname,loc 
from dept 

DB2

create table dept_east 
( deptno integer, 
 dname varchar(10), 
 loc varchar(10) check (loc in ('NEW YORK','BOSTON'))) 
 
create table dept_mid 
( deptno integer, 
 dname varchar(10), 
 loc varchar(10) check (loc = 'CHICAGO')) 
 
create table dept_west 
( deptno integer, 
 dname varchar(10), 
 loc varchar(10) check (loc = 'DALLAS')) 
 
insert into ( 
select * from dept_west union all 
select * from dept_east union all 
select * from dept_mid 
) select * from dept

MySQL、PostgreSQL 和 SQL Server

这些数据库尚不支持多表插入。

更新记录

update emp set sal = sal*1.10 where deptno = 20

当相关行存在时更新记录

update emp 
set sal=sal*1.20 
where empno in ( select empno from emp_bonus )

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

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

DB2 和 MySQL

使用关联查询来更新EMP表的SAL列和COMM列，同时也要使用另一个关联子查询来决定

EMP表里有哪些行应该被更新。

update emp e set (e.sal,e.comm) = (select ns.sal, ns.sal/2 
from new_sal ns 
where ns.deptno=e.deptno) 
where exists ( select null 
from new_sal ns 
where ns.deptno = e.deptno )

Oracle
DB2 的解决方案当然也适用于 Oracle，不过还有另外一种方法，即更新内嵌视图。

update ( 
select e.sal as emp_sal, e.comm as emp_comm, 
ns.sal as ns_sal, ns.sal/2 as ns_comm 
from emp e, new_sal ns 
where e.deptno = ns.deptno 
) set emp_sal = ns_sal, emp_comm = ns_comm 

PostgreSQL
DB2 的解决方案同样适用于 PostgreSQL，也可以在UPDATE语句里直接进行连接查询（非

常方便）。

update emp 
set sal = ns.sal, 
comm = ns.sal/2 
from new_sal ns 
where ns.deptno = emp.deptno 

SQL Server
DB2 的解决方案同样适用于 SQL Server，也可以在UPDATE语句里直接进行连接查询（类

似于 PostgreSQL 解决方案）。

update e  
set e.sal = ns.sal, 
e.comm = ns.sal/2 
from emp e, 
new_sal ns 
where ns.deptno = e.deptno

合并记录

Oracle 是目前仅有的只使用单个 SQL 语句就能解决本问题的关系数据库管理系统

merge into emp_commission ec 
using (select * from emp) emp 
on (ec.empno=emp.empno) 
when matched then 
update set ec.comm = 1000 
delete where (sal < 2000) 
when not matched then 
insert (ec.empno,ec.ename,ec.deptno,ec.comm) 
values (emp.empno,emp.ename,emp.deptno,emp.comm)

删除全表记录

delete from emp

删除重复记录

delete from dupes 
where id not in ( select min(id) 
from dupes 
group by name )

删除被其他表参照的记录

delete from emp 
where deptno in ( select deptno 
from dept_accidents 
group by deptno 
having count(*) >= 3 )