5种数据库sql语句大全一

数据库sql语句大全

一、连接列值
db2/oracle/postgresql
select name(字段)||' Works AS a '（文字）||job as msg from emp where deptno=10;

mysql
select concat(name,' works as a',job) as msg from emp where deptno=10;

sql server
select name+' works as a '+job as msg from emp where deptno=10;

二、使用条件逻辑
select name,salary,
     case when salary<=2000 then 'low'
     case when salary>=4000 then 'over'
       else 'ok'
     end as status
from emp

三、限制返回的行数
db2
select * from emp fetch first 5 rows only

mysql/postgresql
select * from emp limit 5

oracle
select * from emp rownum<=5

sql server
select top 5 * from emp

四、随机返回记录
db2
select name,job from emp order by rahnd() fetch first 5 rows only

mysql
select name,job from emp order by rand() limit 5

postgresql
select * from emp order by random() limit 5

oracle
select * from (select name,job from emp order by dbms_random.value()) where rownum<=5

sql server
select top 5 name,job from emp order by newid()

五、将空值转换成实际值
select coalesce(comm,0) from emp
不为空则返回comm值，空则返回0，comm类型与0类型必须一致

六、按子串排序(取消后面2位)
db2/mysql/oracle/postgresql
select name,job from emp order by substr(job,length(job)-2)

sql server
select name,job from emp order by substring(job,len(job)-2,2)

七、对字母数字混合的数据排序
oracle/postgresql

select data from emp v order by replace (data,replace(translate(data,'0123456789','##########'),'#',''),'')

select data from emp order by replace(translate(data,'0123456789','##########'),'#','')

db2

select * from (select ename||' '||cast(deptno as char(2)) as data from emp ) v order by replace (data,replace(translate(data,'##########','0123456789'),'#',''),'')

select * from (select name||' '||cast(deptno as char(2)) as data from emp v order by replace(translate(data,'##########','0123456789'),'#','')

mysql/sqlserver
当前不支持translate函数，无解决方案

八、处理排序空值
db2/mysql/postgresql/sqlserver
select name,sal,comm from (select name,sal,comm case when comm is null then 0 else 1 end as is_null from emp) x order by is_null desc,comm

oracle
select name,sal,comm from emp order by comm nulls last//all nulls last
select name,sal,comm from emp order by comm nulls first//all nulls first

九、根据数据项的键排序
select name,sal,job,comm from emp order by case when job='salesman' then comm else sal end

十、记录集的叠加
//使用union子句相当于对使用union all子句的结果使用distinct
select ename as ename_and_dname,deptno from emp where deptno=10 union all select '----------',null from t1 union all select dname,deptno from dept

十一、从一个表红查找另一个表没有的值
db2/postgresql
select deptno from dept except select deptno from emp

oracle
select deptno from dept minus select deptno from emp

mysql/sqlserver
select deptno from dept where deptno not in (select deptno from emp)

十二、在一个表中查找与其他表不匹配的记录
db2/mysql/postgresql/sqlserver
select d.* from dept d left outer join emp e on(d.deptno=e.deptno) where e.deptno is null

oracle
select d.* from dept d,emp e where d.deptno=e.deptno (+) and e.deptno is null

十三、向查询中增加联接而不影响其他联接
db2/mysql/postgresql/sqlserver
select e.ename,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

oracle
select e.ename,d.loc,eb.received from emp e,dept d,emp_bonus eb where e.deptno=d.deptno and e.empno=eb.empno (+) order by 2
select e.ename,d.loc,(select eb.received from emp_bonus eb where eb.empno=e.empno) as received from emp e,dept d where e.deptno=d.deptno order by 2

十四、检测两个表中是否有相同的数据
解决原理：
1、首先，查找处表emp中存在而视图v中没有的行
2、然后合并（union all）在视图v中存在，而在表emp中没有的行

十五、识别和消除笛卡尔积
在from子句对表进行联接来返回正确的结果集：
select e.ename,d.loc from emp e,dept d where e.deptno=10 and d.deptno =e.deptno

十六、聚集与联接

mysql/postgresql
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=1) x group by deptno

db2/oracle/sqlserver
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

十七、聚集与外联接

db2/mysql/postgresql/sqlserver
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 is null then 0 when eb.type=1 then .1 when eb.type=2 then .2 else .3 end as bonus from emp e left outer join emp_bonus eb on(e.empno=eb.empno) where e.deptno=10) group by deptno

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 is null then 0 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 left outer join emp_bonus eb on (e.empno=eb.empno) where e.deptno=10) x

oracle
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 is null then 0 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) group by deptno

十八、从多个表中返回丢失的数据
db2/mysql/postgresql/sqlserver
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)

oracle
select d.deptno,d.dname,e.ename from dept d,emp e where d.deptno=e.deptno (+) union select d.deptno,d.dname,e.ename from dept d,emp e where d.deptno(+)=e.deptno

十九、在运算和比较时使用null值
select ename,comm from emp where coalesce(comm,0)<(select comm from emp where ename='WARD')

二十、从一个表向另外的表中复制行
insert into dept_east (deptno,dname,loc) select deptno,dname,loc from dept where loc in('NEW YORK','BOSTON')

二十一、复制表定义
db2
create table dept_2 like dept

oracle/mysql/postgresql
create table dept_2 as select * from dept where 1=0

sqlserver
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
insert into (select * from dept_west union all select * from dept_east union all select * from dept_mid) select * from dept

mysql/postgresql/sqlserver
不支持多表插入操作

二十三、阻止对某几列插入
在表中创建一个视图，该视图将只显示允许用户进行操作的列，强制所有的插入操作都通过该视图进行
create view new_emps as select empno,ename,job from emp