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 tabschema='smeagol'
from
syscat.columns
where
tablename='emp'
and
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.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
a.tabschema='smeagol'
and
a.indschema=b.indschema
and
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
and
a.table_name=b.table_name a.owner=c.table_owner(+)
and and
a.constraint_name=b.constraint_name
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','') 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'))
as
sal from (select ename||sal data from emp)
postgresql select replace(translate(data,'0123456789','0000000000'),'0','')
ename,cast(replace(translate(lower(data),'abcdefghijklmnopqrstuvwxyz',rpad('z',26,'z')),'z','') 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
as as
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