五大数据库常用SQL比较(二)

二十四、用其他表中的值更新
db2/mysql
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 nul from new_sa ns where ns.deptno=e.deptno)

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

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

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

二十五、合并记录
oracle
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 where not exists(select * from dept where dept.deptno=emp.deptno)

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

二十七、删除重复记录
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)

（以下模式名schema为smeagol）
二十九、列出模式中的表
db2
select tabname from syscat.table where tabschema='smeagol'

oracle
select table_name from all_tables where owner='smeagol'

postgresql/mysql/sqlserver
select tablename from information_schema.tables where table_schema='smeagol'

三十、列出表的列
db2
select colname,typename,colno from syscat.columns where tablename='emp' and tabschema='smeagol'

oracle
select column_name,data_type,column_id from all_tab_columns where owner='smeagol' and table_name='emp'

postgresql/mysql/sqlserver
select column_name,data_type,ordinal_position from information_schema='smeagol' and table_name='emp'

三十一、列出表的索引列
db2
select a.tabname,b.indname,b.colname,b.colseq from syscat.indexes a,syscat.indexcoluse b where a.tabname='emp' and a.tabschema='smeagol' and a.indschema=b.indschema and a.indname=b.indname

oracle
select table_name,index_name,column_name,column_position from sys.all_ind_columns where table_name='emp' and table_owner='smeagol'

postgresql
select a.tablename,a.indexname,b.column_name from pg_catalog.pg_indexes a,information_schema.columns b where a.schemaname='smeagol' and a.tablename=b.table_name

mysql
show index from emp

sqlserver
select a.name table_name,b.name index_name,d.name column_name,c.index_column_id from sys.tables a,sys.indexes b,sys.index_columns c,sys.columns d where a.object_id=b.object_id and b.object_id=c.object_id and b.index_id=c.index_id and c.object_id=d.object_id and c.column_id=d.column_id and a.name='emp'

三十二、列出表约束
db2
select a.tabname,a.constname,b.colname,a.type from syscat.tabconst a,syscat.columns b where a.tabname='emp' and a.tabschema='smeagol' and a.tabname=b.tabname and a.tabschema=b.tabschema

oracle
select a.table_name,a.constraint_name,b.column_name,a.constraint_type from all_constraints a,all_cons_columns b where a.table_name='emp' and a.owner='smeagol' and a.table_name=b.table_name and a.owner=b.owner and a.constraint_name=b.constraint_name

postgresql/mysql/sqlserver
select a.table_name,a.constraint_name,b.column_name,a.constraint_type from information_schema.table_constraints a,information_schema.key_column_usage b where a.table_name='emp' and a.table_schema='smeagol' and a.table_name=b.table_name and a.table_schema=b.table_schema and a.constraint_name=b.constraint_name

三十三、列出没有相应索引的外键
db2
select fkeys.tabname,fkeys.constname,fkeys.colname,ind_cols.indname from (select a.tabschema,a.tabname,a.constname,b.colname from syscat.tabconst a,syscat.keycoluse b where a.tabname='emp' and a.tabschema='smeagol' and a.type='f' and a.tabname=b.tabname and a.tabschema=b.tabschema) fkeys left join (select a.tabschema,a.tabname,a.indname,b.colname from syscat.indexes a,syscat.indexcoluse b where a.indschema=b.indschema and a.indname=b.indname) ind_cols on(fkeys.tabschema=ind_cols.tabschema and fkeys.tabname=ind_cols.tabname and fkeys.colname=ind_cols.colname)where ind_cols.indname is null

oracle
select a.table_name,a.constraint_name,a.column_name,c.index_name from all_cons_columns a,all_constraints b,all_ind_columns c where a.table_name='emp' and a.owner='smeagol' and b.constraint_type='r' and a.owner=b.owner and a.table_name=b.table_name and a.constraint_name=b.constraint_name and a.owner=c.table_owner(+) and a.table_name=c.table_name(+) and a.column_name=c.column_name(+) and c.index_name is null

postgresql
select fkeys.table_name,fkeys.constraint_name,fkeys.column_name,ind_cols.index_name from (select a.constraint_schema,a.table_name,a.constraint_name,a.column_name from information_schema.key_column_usage a,information_schema.referential_constraints b where a.table_name='emp' and a.constraint_schema='smeagol' and a.constraint_name=b.constraint_name and a.constraint_schema=b.constraint_schema) fkeys left join (select a.schemaname,a.tablename,a.indexname,b.column_name from pg_catalog.pg_indexes a,information_schema.columns b where a.schemaname=b.table_schema and a.tablename=b.table_name) ind_cols on(fkeys.constraint_schema=ind_cols.schemaname and fkeys.table_name=ind_cols.tablename and fkeys.column_name=ind_cols.column_name)where ind_cols.indexname is null

mysql
使用show index来检索索引信息，查询information——schema.key_column_usage列出指定表的外键

sqlserver
select fkeys.table_name,fkeys.constraint_name,fkeys.column_name,ind_cols.index_name from (select a.object_id,d.column_id,a.name table_name,b.name constraint_name,d.name column_name from sys.tables a join sys.foreign_keys b on (a.name='emp' and a.object_id=b.parent_object_id) join sys.foreign_key_columns c on(b.object_id=c.constraint_object_id) join sys.columns d on (c.constraint_column_id=d.column_id and a.object_id=d.object_id) fkeys left join (select a.name index_name,b.object_id,b.column_id from sys.indexes a,sys.index_columns b where a.index_id=b.index_id) ind_cols on (fkeys.object_id=ind_cols.object_id and fkeys.column_i=ind_cols.column_id) where ind_cols.index_name is null

三十四、使用sql来生成sql
oracle
select 'select count(*) from '||table_name||';' cnts from user_tables;

三十五、在oracle中描述数据字典视图
select table_name,comments from dictionary order by table_name

select column_name,comments from dict_columns where table_name='all_tab_columns'

三十六、遍历字符串
select substr(e.ename,iter.post,1) as c from (select ename from emp where ename='king') e,(select id as pos from t10) iter where iter.pos<=length(e.ename)

三十七、计算字符在字符串中出现的次数
select (length('10,clark,manager')-length(replace('10,clark,manager',',','')))/length(',') as cnt from t1

三十八、从字符串中删除不需要的字符
db2
select ename,replace(translate(ename,'aaaaa','AEIOU'),'a','') stripped1,sal,replace(cast(sal as char(4)),'0','') stripped2 from emp

mysql/sqlserver
select ename,replace(replace(replace(replace(replace(ename,'A',''),'E',''),'I',''),'O',''),'U','') as stripped1,sal,replace(sal,0,'') stripped2 from emp

oracle/postgresql
select ename,replace(translate(ename,'AEIOU','aaaa'),'a') as stripped1,sal,replace(sal,0,'') as stripped2 from emp

三十九、将字符和数字数据分离
db2
select replace(translate(data,'0000000000','0123456789'),'0','') ename,cast(replace(translate(lower(data),repeat('z',26),'abcdefghijklmnopqrstuvwxyz'),'z','') as integer) sal from (select ename||cast(sal as char(4)) data from emp) x

oracle
select replace(translate(data,'0123456789','0000000000'),'0') ename,to_number(replace(translate(lower(data),'abcdefghijklmnopqrstuvwxyz',rpad('z',26,'z')),'z')) sal from (select ename||sal data from emp)

postgresql
select replace(translate(data,'0123456789','0000000000'),'0','') as ename,cast(replace(translate(lower(data),'abcdefghijklmnopqrstuvwxyz',rpad('z',26,'z')),'z','') as integer) as sal from (select ename||sal as data from emp) x

四十、判断字符串是不是字母数字型的
db2
select data from view where translate(lower(data),repeat('a',36),'0123456789abcdefghijklmnopqrstuvwxyz')=repeat('a',length(data))

mysql
create view V as select ename as data from emp where deptno=10 union all select concat(ename,',$',sal,'.00') as data from emp where deptno=20 union all select concat(ename,deptno) as data from emp where deptno=30
select data from V where data regexp '[^0-9a-zA-Z]'=0

oracle/postgresql
select data from V where translate(lower(data),'0123456789abcdefghijklmnopqrstuvwxyz',rpad('a',36,'a'))=rpad('a',length(data),'a')

sqlserver
select data from (select v.data,iter.pos,substring(v.data,iter.pos,1) c,ascii(substring(v.data,iter.pos,1)) val from v,(select id as pos from t100) iter where iter.pos<=len(v.data)) x group by data having min(val) between 48 and 122

四十一、提取姓名的大写首字母缩写
db2
select replace(replace(translate(replace('Stewie Griffin','.',''),repeat('#',26),'abcdefghijklmnopqrstuvwxyz'),'#',''),' ','.')||'.' from dual

mysql
select case when cnt=2 then trim(trailing '.' from concat_ws('.',substr(substring_index(name,' ',1),1,1),substr(name,length(substring_index(name,' ',1))+2,1),substr(substring_index(name,' ',-1),1,1),'.')) else trim(trailing '.' from concat_ws('.',substr(substring_index(name,' ',1),1,1),substr(substring_index(name,' ',-1),1,1))) end as initials from (select name,length(name)-length(replace(name,' ','')) as cnt from (select replace('Stewie Griffin','.','') as name from dual ) y ) x

oracle/postgresql
select replace(replace(translate(replace('Stewie Griffin','.',''),'abcdefghijklmnopqrstuvwxyz',rpad('#',26,'#')),'#',''),' ','.')||'.' from dual

四十二、按字符串中的部分内容排序
db2/oracle/mysql/postgresql
select ename from emp order by substr(ename,length(ename)-1,2)

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

四十三、按字符串中的数值排序
db2
select data from V order by cast(replace(translate(data,repeat('#',length(data)),replace(translate(data,'##########','0123456789'),'#','')),'#','') as integer)

oracle
select data from V order by to_number(replace(translate(data,replace(translate(data,'0123456789','##########'),'#'),rpad('#',20,'#')),'#'))

postgresql
select data from V order by cast(replace(translate(data,replace(translate(data,'0123456789','##########'),'#',''),rpad('#',20,'#')),'#','') as integer)

四十四、根据表中的行创建一个分割列表
db2
with x (deptno,cnt,list,empno,len) as (select deptno,count(*) over (partition by deptno), cast(ename as varchar(100)),empno,1 from emp union all select x.deptno,x.cnt,x.list||','||e.name,e.empno,x.len+1 from emp e,x where e.deptno=x.deptno and e.empno>x.empno) select deptno,list from x where len=cnt

mysql
select deptno,group_concat(ename order by empno separator,',') as emps from emp group by deptno

oracle
select deptno,ltrim(sys_connect_by_path(ename,','),',') emps from (select deptno,ename,row_number() over (partition by deptno order by empno) rn,count(*) over (partition by deptno) cnt from emp) where level=cnt start with rn=1 connect by prior deptno=deptno and prior rn=rn-1

postgresql
select deptno,rtrim(max(case when pos=1 then emps else '' end)||max(case when pos=2 then emps else '' end)||max(case when pos=3 then emps else '' end)||max(case when pos=4 then emps else '' end)||max(case when pos=5 then emps else '' end)||max(case when pos=6 then emps else '' end),',') as emps from (select a.deptno,a.ename||',' as emps,d.cnt,(select count(*) from emp b where a.deptno=b.deptno and b.empno<=empno ) as pos from emp a,(select deptno,count(ename) as cnt from emp group by deptno) d where d.deptno=a.deptno) x group by deptno order by 1

sqlserver
with x (deptno,cnt,list,empno,len) as (select deptno,count(*) over (partition by deptno), cast(ename as varchar(100)),empno,1 from emp union all select x.deptno,x.cnt,cast(x.list+','+e.name as varchar(100)),e.empno,x.len+1 from emp e,x where e.deptno=x.deptno and e.empno>x.empno) select deptno,list from x where len=cnt order by 1

四十五、将分隔数据转换为多值IN列表
db2
select empno,ename,sal,deptno from emp where empno in (select cast(substr(c,2,locate(',',c,2)-2) as integer) empno from (select substr(csv.emps,cast(iter.pos as integer)) as c from (select ','||'7654,7698,7782,7788'||',' emps from t1 ) csv,(select id as pos from t100) iter where iter.pos<=length(csv.emps)) x where length(c)>1 and substr(c,1,1)=',' ) y

mysql
select empno,ename,sal,deptno from emp where empno in (select substring_index(substring_index(list.vals,',',iter.pos),',',-1) empno from (select id pos from t10) as iter,(select '7654,7698,7782,7788' as vals from t1) list where iter.pos<=(length(list.vals)-length(replace(list.vals,',','')))+1) x

oracle
select empno,ename,sal,deptno from emp where empno in (select to_number(rtrim(substr(emps,instr(emps,',',1,iter.pos)+1,instr(emps,',',1,iter.pos+1)-instr(emps,',',1,iter.pos)),',')) emps from (select ','||'7654,7698,7782,7788'||',' emps from t1) csv,(select rownum pos from emp) iter where iter.pos<=((length(csv.emps)-length(replace(csv.emps,',')))/length(','))-1

postgresql
select empno,ename,sal,deptno from emp where empno in (select cast (empno as integer) as empno from (select split_part(list.vals,',',iter.pos) as empno from (select id as pos from t10) iter,(select ','||'7654,7698,7782,7788'||',' as vals from t1) list where iter.pos<=length(list.vals)-length(replace(ist.vals,',',''))) z where length(empno)>0) x

sqlserver
select empno,ename,sal,deptno from emp where empno in (select substring(c,2,charindex(',',c,2)-2) as empno from (select substring(csv.emps,iter.pos,len(csv.emps)) as c from (select ','+'7654,7698,7782,7788'+',' as emps from t1) csv,(select id as pos from t100) iter where iter.pos<=len(csv.emps)) x where len(c)>1 and substring(c,1,1)=',') y

四十六、按字母顺序排列字符串
db2
select ename,max(case when pos=1 then c else '' end)||max(case when pos=2 then c else '' end)||max(case when pos=3 then c else '' end)||max(case when pos=4 then c else '' end)||max(case when pos=5 then c else '' end)||max(case when pos=6 then c else '' end) from (select e.ename,cast(substr(e.ename,iter.pos,1) as varchar(100)) c,cast(row_number() over (partition by e.ename order by substr(e.ename,iter.pos,1)) as integer) pos from emp e,(select cast(row_number() over() as integer) pos from emp) iter where iter.pos<=length(e.ename)) x group by ename

mysql
select ename,group_concat(c order by c separator '') from ( select ename,substr(a.ename,iter.pos,1) c from emp a,(select id pos from t10) iter where iter.pos<=length(a.ename)) x group by ename

oracle
select old_name,new_name from (select old_name,replace(sys_connect-by_path(c,' '),' ') new_name from (select e.ename old_name,row_number() over(partition by e.ename order by substr(e.ename,iter.pos,1)) rn,substr(e.ename,iter.pos,1) c from emp e,(select rownum pos from emp) iter where iter.pos<=length(e.ename) order by 1) x start with rn=1 connect by prior rn=rn-1 and prior old_name=old_name) where length(old_name)=length(new_name)

postgresql
必须先遍历每个字符串，先要知道姓名的最大长度

sqlserver
select ename,max(case when pos=1 then c else '' end)+max(case when pos=2 then c else '' end)+max(case when pos=3 then c else '' end)+max(case when pos=4 then c else '' end)+max(case when pos=5 then c else '' end)+max(case when pos=6 then c else '' end) from (select e.ename,substring(e.ename,iter.pos,1) as c,row_number() over (partition by e.ename order by substring(e.ename,iter.pos,1)) as pos from emp e,(select row_number() over(order by ename) as pos from emp) iter where iter.pos<=len(e.ename)) x group by ename