[计算机软件及应用]5种数据库sql语句大全.doc

数据库sql语句大全一、连接列值db2/oracle/postgresqlselect name(字段)|' Works AS a '（文字）|job as msg from emp wheredeptno=10;mysqlselect concat(name,' works as a',job) as msg from emp where deptno=10;sql serverselect 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 statusfrom emp三、限制返回的行数db2select * from emp fetch first 5 rows onlymysql/postgresqlselect * from emp limit 5oracleselect * from emp rownum<=5sql serverselect top 5 * from emp四、随机返回记录db2select name,job from emp order by rahnd() fetch first 5 rows onlymysqlselect name,job from emp order by rand() limit 5postgresqlselect * from emp order by random() limit 5oracleselect * from (select name,job from emp order by dbms_random.value()where rownum<=5sql serverselect top 5 name,job from emp order by newid()五、将空值转换成实际值select coalesce(comm,0) from emp不为空则返回comm值，空则返回0，comm类型与0类型必须一致六、按子串排序(取消后面2位)db2/mysql/oracle/postgresqlselect name,job from emp order by substr(job,length(job)-2)sql serverselect 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 byreplace(translate(data,'0123456789','#'),'#','')db2/*order by deptno(数字)*/select * from (select ename|' '|cast(deptno as char(2) as data fromemp ) v order by replace(data,replace(translate(data,'#','0123456789'),'#',''),'')/*order by name(字母)*/select * from (select name|' '|cast(deptno as char(2) as data fromemp v order by replace(translate(data,'#','0123456789'),'#','')mysql/sqlserver当前不支持translate函数，无解决方案八、处理排序空值db2/mysql/postgresql/sqlserverselect name,sal,comm from (select name,sal,comm case when comm is nullthen 0 else 1 end as is_null from emp) x order by is_null desc,commoracleselect name,sal,comm from emp order by comm nulls last/all nulls lastselect 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' thencomm else sal end十、记录集的叠加/使用union子句相当于对使用union all子句的结果使用distinctselect ename as ename_and_dname,deptno from emp where deptno=10 unionall select '-',null from t1 union all select dname,deptno from dept十一、从一个表红查找另一个表没有的值db2/postgresqlselect deptno from dept except select deptno from emporacleselect deptno from dept minus select deptno from empmysql/sqlserverselect deptno from dept where deptno not in (select deptno from emp)十二、在一个表中查找与其他表不匹配的记录db2/mysql/postgresql/sqlserverselect d.* from dept d left outer join emp e on(d.deptno=e.deptno) wheree.deptno is nulloracleselect d.* from dept d,emp e where d.deptno=e.deptno (+) and e.deptno isnull十三、向查询中增加联接而不影响其他联接db2/mysql/postgresql/sqlserverselect 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 2oracleselect e.ename,d.loc,eb.received from emp e,dept d,emp_bonus eb wheree.deptno=d.deptno and e.empno=eb.empno (+) order by 2select e.ename,d.loc,(select eb.received from emp_bonus eb whereeb.empno=e.empno) as received from emp e,dept d where e.deptno=d.deptnoorder 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/postgresqlselect deptno,sum(distinct sal) as total_sal,sum(bonus) as total_bonusfrom (select e.empno,e.ename,e.sal,e.deptno,e.sal*case when eb.type=1then .1 when eb.type=2 then .2 else .3 end as bonus from emp e,emp_bonuseb where e.empno=eb.empno and e.deptno=1) x group by deptnodb2/oracle/sqlserverselect distinct deptno,total_sal,total_bonus from (selecte.empno,e.ename,sum(distinct e.sal) over (partition by e.deptno) astotal_sal,e.deptno,sum(e.sal*case when eb.type=1 then .1 when eb.type=2then .2 else .3 end) over (partition by deptno) as total_bonus from empe,emp_bonus eb where e.empno=eb.empno and e.deptno=10) x十七、聚集与外联接/*只对部门10中不同的工资进行汇总*/db2/mysql/postgresql/sqlserverselect deptno,sum(distinct sal) as total_sal,sum(bonus) as total_bonusfrom (select e.empno,e.ename,e.sal,e.deptno,e.sal*case when eb.type isnull then 0 when eb.type=1 then .1 when eb.type=2 then .2 else .3 end asbonus from emp e left outer join emp_bonus eb on(e.empno=eb.empno) wheree.deptno=10) group by deptnoselect distinct deptno,total_sal,total_bonus from (selecte.empno,e.ename,sum(distinct e.sal) over (partition by e.deptno) astotal_sal,e.deptno,sum(e.sal*case when eb.type is null then 0 wheneb.type=1 then .1 when eb.type =2 then .2 else .3 end) over (partitionby deptno) as total_bonus from emp e left outer join emp_bonus eb on(e.empno=eb.empno) where e.deptno=10) xoracleselect deptno,sum(distinct sal) as total_sal,sum(bonus) as total_bonusfrom (select e.empno,e.ename,e.sal,e.deptno,e.sal*case when eb.type isnull then 0 when eb.type=1 then .1 when eb.type=2 then .2 else .3 end asbonus from emp e,emp_bonus eb where e.empno=eb.empno (+) ande.deptno=10) group by deptno十八、从多个表中返回丢失的数据db2/mysql/postgresql/sqlserverselect 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 eon(d.deptno=e.deptno) union select d.deptno,d.dname,e.ename from dept dleft outer join emp e on (d.deptno=e.deptno)oracleselect d.deptno,d.dname,e.ename from dept d,emp e whered.deptno=e.deptno (+) union select d.deptno,d.dname,e.ename from deptd,emp e where d.deptno(+)=e.deptno十九、在运算和比较时使用null值select ename,comm from emp where coalesce(comm,0)<(select comm from empwhere ename='WARD')二十、从一个表向另外的表中复制行insert into dept_east (deptno,dname,loc) select deptno,dname,loc fromdept where loc in('NEW YORK','BOSTON')二十一、复制表定义db2create table dept_2 like deptoracle/mysql/postgresqlcreate table dept_2 as select * from dept where 1=0sqlserverselect * into dept_2 from dept where 1=0二十二、一次向多个表中插入记录oracleinsert 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 deptdb2insert into (select * from dept_west union all select * from dept_eastunion all select * from dept_mid) select * from deptmysql/postgresql/sqlserver不支持多表插入操作二十三、阻止对某几列插入在表中创建一个视图，该视图将只显示允许用户进行操作的列，强制所有的插入操作都通过该视图进行create view new_emps as select empno,ename,job from emp 二十四、用其他表中的值更新db2/mysqlupdate emp e set(e.sal,e.comm)=(select ns.sal,ns.sal/2 from new_sal nswhere ns.deptno=e.deptno) where exists(select nul from new_sa ns wherens.deptno=e.deptno)oracleupdate(select e.sal as emp_sal,e.comm as emp_comm,ns.sal asns_sal,ns.sal/2 as ns_comm frm emp e,new_sal ns wheree.deptno=ns.deptno) set emp_sal=ns_sal,emp_comm=ns_commpostgresqlupdate emp set sal=ns.sal,comm=ns.sal/2 from new_sal ns wherens.deptno=emp.deptnosqlserverupdate e set e.sal=ns.sal,e.comm=ns.sal/2 from emp e,new_sal ns wherens.deptno=emp.deptno二十五、合并记录oraclemerge into emp_commission ec using(select * from emp) empon(ec.empno=emp.empno) when matched then update set ec.comm=1000 deletewhere (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 wheredept.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 byname(需要判断重复的字段)二十八、删除从其他表引用的记录delete from emp where deptno in (select deptno from dept_accidents groupby deptno having count(*)>=3)（以下模式名schema为smeagol）二十九、列出模式中的表db2select tabname from syscat.table where tabschema='smeagol'oracleselect table_name from all_tables where owner='smeagol'postgresql/mysql/sqlserverselect tablename from information_schema.tables where table_schema='smeagol'三十、列出表的列db2select colname,typename,colno from syscat.columns where tablename='emp'and tabschema='smeagol'oracleselect column_name,data_type,column_id from all_tab_columns whereowner='smeagol' and table_name='emp'postgresql/mysql/sqlserverselect column_name,data_type,ordinal_position frominformation_schema='smeagol' and table_name='emp'三十一、列出表的索引列db2select a.tabname,b.indname,b.colname,b.colseq from syscat.indexesa,syscat.indexcoluse b where a.tabname='emp' and a.tabschema='smeagol'and a.indschema=b.indschema and a.indname=b.indnameoracleselect table_name,index_name,column_name,column_position fromsys.all_ind_columns where table_name='emp' and table_owner='smeagol'postgresqlselect a.tablename,a.indexname,b.column_name from pg_catalog.pg_indexesa,information_schema.columns b where a.schemaname='smeagol' anda.tablename=b.table_namemysqlshow index from empsqlserverselect a.name table_name,b.name index_name,d.namecolumn_name,c.index_column_id from sys.tables a,sys.indexesb,sys.index_columns c,sys.columns d where a.object_id=b.object_id andb.object_id=c.object_id and b.index_id=c.index_id andc.object_id=d.object_id and c.column_id=d.column_id and a.name='emp'三十二、列出表约束db2select a.tabname,a.constname,b.colname,a.type from syscat.tabconsta,syscat.columns b where a.tabname='emp' and a.tabschema='smeagol' anda.tabname=b.tabname and a.tabschema=b.tabschemaoracleselect a.table_name,a.constraint_name,b.column_name,a.constraint_typefrom all_constraints a,all_cons_columns b where a.table_name='emp' anda.owner='smeagol' and a.table_name=b.table_name and a.owner=b.owner anda.constraint_name=b.constraint_namepostgresql/mysql/sqlserverselect a.table_name,a.constraint_name,b.column_name,a.constraint_typefrom information_schema.table_constraintsa,information_schema.key_column_usage b where a.table_name='emp' anda.table_schema='smeagol' and a.table_name=b.table_name anda.table_schema=b.table_schema and a.constraint_name=b.constraint_name三十三、列出没有相应索引的外键db2select fkeys.tabname,fkeys.constname,fkeys.colname,ind_cols.indname from(select a.tabschema,a.tabname,a.constname,b.colname from syscat.tabconsta,syscat.keycoluse b where a.tabname='emp' and a.tabschema='smeagol' anda.type='f' and a.tabname=b.tabname and a.tabschema=b.tabschema) fkeysleft join (select a.tabschema,a.tabname,a.indname,b.colname fromsyscat.indexes a,syscat.indexcoluse b where a.indschema=b.indschema anda.indname=b.indname) ind_cols on(fkeys.tabschema=ind_cols.tabschema andfkeys.tabname=ind_cols.tabname and fkeys.colname=ind_cols.colname)whereind_cols.indname is nulloracleselect a.table_name,a.constraint_name,a.column_name,c.index_name fromall_cons_columns a,all_constraints b,all_ind_columns c wherea.table_name='emp' and a.owner='smeagol' and b.constraint_type='r' anda.owner=b.owner and a.table_name=b.table_name anda.constraint_name=b.constraint_name and a.owner=c.table_owner(+) anda.table_name=c.table_name(+) and a.column_name=c.column_name(+) andc.index_name is nullpostgresqlselectfkeys.table_name,fkeys.constraint_name,fkeys.column_name,ind_cols.index_namefrom (selecta.constraint_schema,a.table_name,a.constraint_name,a.column_name frominformation_schema.key_column_usagea,information_schema.referential_constraints b where a.table_name='emp'and a.constraint_schema='smeagol' anda.constraint_name=b.constraint_name anda.constraint_schema=b.constraint_schema) fkeys left join (selecta.schemaname,a.tablename,a.indexname,b.column_name frompg_catalog.pg_indexes a,information_schema.columns b wherea.schemaname=b.table_schema and a.tablename=b.table_name) ind_colson(fkeys.constraint_schema=ind_cols.schemaname andfkeys.table_name=ind_cols.tablename andfkeys.column_name=ind_cols.column_name)where ind_cols.indexname is nullmysql使用show index来检索索引信息，查询informationschema.key_column_usage列出指定表的外键sqlserverselectfkeys.table_name,fkeys.constraint_name,fkeys.column_name,ind_cols.index_namefrom (select a.object_id,d.column_id,a.name table_name,b.nameconstraint_name,d.name column_name from sys.tables a joinsys.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 anda.object_id=d.object_id) fkeys left join (select a.nameindex_name,b.object_id,b.column_id from sys.indexes a,sys.index_columnsb where a.index_id=b.index_id) ind_cols on(fkeys.object_id=ind_cols.object_id andfkeys.column_i=ind_cols.column_id) where ind_cols.index_name is null三十四、使用sql来生成sqloracleselect 'select count(*) from '|table_name|'' cnts from user_tables;三十五、在oracle中描述数据字典视图select table_name,comments from dictionary order by table_nameselect column_name,comments from dict_columns wheretable_name='all_tab_columns'三十六、遍历字符串select substr(e.ename,iter.post,1) as c from (select ename from empwhere ename='king') e,(select id as pos from t10) iter whereiter.pos<=length(e.ename)三十七、计算字符在字符串中出现的次数select(length('10,clark,manager')-length(replace('10,clark,manager',',','')/length(',')as cnt from t1三十八、从字符串中删除不需要的字符db2select ename,replace(translate(ename,'aaaaa','AEIOU'),'a','')stripped1,sal,replace(cast(sal as char(4),'0','') stripped2 from empmysql/sqlserverselectename,replace(replace(replace(replace(replace(ename,'A',''),'E',''),'I',''),'O',''),'U','')as stripped1,sal,replace(sal,0,'') stripped2 from emporacle/postgresqlselect ename,replace(translate(ename,'AEIOU','aaaa'),'a') asstripped1,sal,replace(sal,0,'') as stripped2 from emp三十九、将字符和数字数据分离db2select 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) xoracleselect 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)postgresqlselect replace(translate(data,'0123456789','0000000000'),'0','') asename,cast(replace(translate(lower(d