数据库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
/*order by deptno(数字)*/
select data from emp v order by replace (data,replace(translate(data,'0123456789','##########'),'#',''),'')

/*order by name(字母)*/
select data from emp order by replace(translate(data,'0123456789','##########'),'#','')

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

/*order by name(字母)*/
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 fr