震撼分享五種數(shù)據(jù)庫SQL語句大全

1、一、連接列值db2/oracle/postgresqlselect name(字段)|' Works AS a '（文字）|job as msg from emp where deptno=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, ca

2、se when salary<=2000 then 'low' case when salary>=4000 then 'over' else 'ok' end as statusfrom emp三、限制返回的行數(shù)db2select * from emp fetch first 5 rows onlymysql/postgresqlselect * from emp limit 5oracleselect * from emp rownum<=5sql serverselect top 5 * from emp四、隨機返回記錄d

3、b2select 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 fr

4、om emp order by newid()五、將空值轉(zhuǎn)換成實際值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)七、對字母數(shù)字混合的數(shù)據(jù)排序oracle/postgresq

5、lselect data from emp v order by replace (data,replace(translate(data,'0123456789','#'),'#',''),'')select data from emp order by replace(translate(data,'0123456789','#'),'#','')db2select * from (select ename|' '|cast(dep

6、tno 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'),&#

7、39;#','')mysql/sqlserver當(dāng)前不支持translate函數(shù)，無解決方案八、處理排序空值db2/mysql/postgresql/sqlserverselect 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,commoracleselect name,sal,comm from emp order by comm nulls last/all n

8、ulls lastselect name,sal,comm from emp order by comm nulls first/all nulls first九、根據(jù)數(shù)據(jù)項的鍵排序select name,sal,job,comm from emp order by case when job='salesman' then comm else sal end十、記錄集的疊加/使用union子句相當(dāng)于對使用union all子句的結(jié)果使用distinctselect ename as ename_and_dname,deptno from emp where deptno=10

9、 union all 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 f

10、rom emp)十二、在一個表中查找與其他表不匹配的記錄db2/mysql/postgresql/sqlserverselect d.* from dept d left outer join emp e on(d.deptno=e.deptno) where e.deptno is nulloracleselect d.* from dept d,emp e where d.deptno=e.deptno (+) and e.deptno is null十三、向查詢中增加聯(lián)接而不影響其他聯(lián)接db2/mysql/postgresql/sqlserverselect e.ename,d.loc,

11、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 where e.deptno=d.deptno and e.empno=eb.empno (+) order by 2select e.ename,d.loc,(select eb.received from emp_bonus eb

12、 where eb.empno=e.empno) as received from emp e,dept d where e.deptno=d.deptno order by 2十四、檢測兩個表中是否有相同的數(shù)據(jù)解決原理：1、首先，查找處表emp中存在而視圖v中沒有的行2、然后合并（union all）在視圖v中存在，而在表emp中沒有的行十五、識別和消除笛卡爾積在from子句對表進行聯(lián)接來返回正確的結(jié)果集：select e.ename,d.loc from emp e,dept d where e.deptno=10 and d.deptno =e.deptno十六、聚集與聯(lián)接mysql/p

13、ostgresqlselect 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 deptnodb2/oracle/sqlserv

14、erselect 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

15、=eb.empno and e.deptno=10) x十七、聚集與外聯(lián)接db2/mysql/postgresql/sqlserverselect 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 l

16、eft outer join emp_bonus eb on(e.empno=eb.empno) where e.deptno=10) group by deptnoselect 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 e

17、b.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) xoracleselect 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

18、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十八、從多個表中返回丟失的數(shù)據(jù)db2/mysql/postgresql/sqlserverselect d.deptno,d.dname,e.ename from dept d full outer join emp e on (d.deptno=e.deptno)se

19、lect 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)oracleselect d.deptno,d.dname,e.ename from dept d,emp e where d.deptno=e.deptno (+) union select d.deptno,d.dname,e.ena

20、me 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')二十、從一個表向另外的表中復(fù)制行insert into dept_east (deptno,dname,loc) select deptno,dname,loc from dept where loc in('NEW YORK','BOSTON

21、')二十一、復(fù)制表定義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(dep

22、tno,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_east union all select * from dep

23、t_mid) select * from deptmysql/postgresql/sqlserver不支持多表插入操作二十三、阻止對某幾列插入在表中創(chuàng)建一個視圖，該視圖將只顯示允許用戶進行操作的列，強制所有的插入操作都通過該視圖進行create view new_emps as select empno,ename,job from emp二十四、用其他表中的值更新db2/mysqlupdate emp e set(e.sal,m)=(select ns.sal,ns.sal/2 from new_sal ns where ns.deptno=e.deptno) where exists(s

24、elect nul from new_sa ns where ns.deptno=e.deptno)oracleupdate(select e.sal as emp_sal,m 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_commpostgresqlupdate emp set sal=ns.sal,comm=ns.sal/2 from new_sal ns where ns.deptn

25、o=emp.deptnosqlserverupdate e set e.sal=ns.sal,m=ns.sal/2 from emp e,new_sal ns where ns.deptno=emp.deptno二十五、合并記錄oraclemerge into emp_commission ec using(select * from emp) emp on(ec.empno=emp.empno) when matched then update set m=1000 delete where (sal<2000) when not matched then insert (ec.emp

26、no,ec.ename,ec.deptno,m) values (emp.empno,emp.ename,emp.deptno,m)二十六、刪除違反參照完整性的記錄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)二十七、刪除重復(fù)記錄delete from dupes where id not in (select min(id) from dupes group

27、by name(需要判斷重復(fù)的字段)二十八、刪除從其他表引用的記錄delete from emp where deptno in (select deptno from dept_accidents group by deptno having count(*)>=3)（以下模式名schema為smeagol）二十九、列出模式中的表db2select tabname from syscat.table where tabschema='smeagol'oracleselect table_name from all_tables where owner='smea

28、gol'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_co

29、lumns where owner='smeagol' and table_name='emp'postgresql/mysql/sqlserverselect column_name,data_type,ordinal_position from information_schema='smeagol' and table_name='emp'三十一、列出表的索引列db2select a.tabname,b.indname,b.colname,b.colseq from syscat.indexes a,syscat.index

30、coluse 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 from sys.all_ind_columns where table_name='emp' and table_owner='smeagol'postgresqlselect a.table

31、name,a.indexname,b.column_name from pg_catalog.pg_indexes a,information_schema.columns b where a.schemaname='smeagol' and a.tablename=b.table_namemysqlshow index from empsqlserverselect table_name, index_name, column_name,c.index_column_id from sys.tables a,sys.indexes b,s

32、ys.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 ='emp'三十二、列出表約束db2select a.tabname,a.constname,b.colname,a.type from syscat.tabconst a,syscat.columns b where

33、 a.tabname='emp' and a.tabschema='smeagol' and a.tabname=b.tabname and a.tabschema=b.tabschemaoracleselect 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.tab

34、le_name=b.table_name and a.owner=b.owner and a.constraint_name=b.constraint_namepostgresql/mysql/sqlserverselect 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.tab

35、le_schema='smeagol' and a.table_name=b.table_name and a.table_schema=b.table_schema and a.constraint_name=b.constraint_name三十三、列出沒有相應(yīng)索引的外鍵db2select fkeys.tabname,fkeys.constname,fkeys.colname,ind_cols.indname from (select a.tabschema,a.tabname,a.constname,b.colname from syscat.tabconst a,sys

36、cat.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

37、.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 nulloracleselect a.table_name,a.constraint_name,a.column_name,c.index_name from all_cons_columns a,all_constraints b,all_ind_columns c wh

38、ere 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 i

39、s nullpostgresqlselect 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' a

40、nd 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.t

41、ablename=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 nullmysql使用show index來檢索索引信息，查詢informationschema.key_column_usage列出指定表的外鍵sqlserverselect fkeys.table_name,fkey

42、s.constraint_name,fkeys.column_name,ind_cols.index_name from (select a.object_id,d.column_id, table_name, constraint_name, column_name from sys.tables a join sys.foreign_keys b on (='emp' and a.object_id=b.parent_object_id) join sys.foreign_key_columns c on(b.object_i

43、d=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 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 f

44、keys.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中描述數(shù)據(jù)字典視圖select table_name,comments from dictionary order by table_nameselect column_name,comments from dict_columns w

45、here 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)三十七、計算字符在字符串中出現(xiàn)的次數(shù)select (length('10,clark,manager')-length(replace('10,c

46、lark,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/sqlserverselect enam

47、e,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',&

48、#39;aaaa'),'a') as stripped1,sal,replace(sal,0,'') as stripped2 from emp三十九、將字符和數(shù)字?jǐn)?shù)據(jù)分離db2select replace(translate(data,'0000000000','0123456789'),'0','') ename,cast(replace(translate(lower(data),repeat('z',26),'abcdefghijklmnopqrstuvwxy

49、z'),'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,&#

50、39;z'),'z') sal from (select ename|sal data from emp)postgresqlselect replace(translate(data,'0123456789','0000000000'),'0','') as ename,cast(replace(translate(lower(data),'abcdefghijklmnopqrstuvwxyz',rpad('z',26,'z'),'z',&#

51、39;') as integer) as sal from (select ename|sal as data from emp) x四十、判斷字符串是不是字母數(shù)字型的db2select data from view where translate(lower(data),repeat('a',36),'0123456789abcdefghijklmnopqrstuvwxyz')=repeat('a',length(data)mysqlcreate view V as select ename as data from emp where

52、 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=30select data from V where data regexp '0-9a-zA-Z'=0oracle/postgresqlselect data from V where translate(lower(data),&#

53、39;0123456789abcdefghijklmnopqrstuvwxyz',rpad('a',36,'a')=rpad('a',length(data),'a')sqlserverselect 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<=

54、len(v.data) x group by data having min(val) between 48 and 122四十一、提取姓名的大寫首字母縮寫db2select replace(replace(translate(replace('Stewie Griffin','.',''),repeat('#',26),'abcdefghijklmnopqrstuvwxyz'),'#',''),' ','.')|'.' from du

55、almysqlselect 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

56、_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 )

57、 y ) xoracle/postgresqlselect replace(replace(translate(replace('Stewie Griffin','.',''),'abcdefghijklmnopqrstuvwxyz',rpad('#',26,'#'),'#',''),' ','.')|'.' from dual四十二、按字符串中的部分內(nèi)容排序db2/oracle/mysql/postgresqlsele

58、ct ename from emp order by substr(ename,length(ename)-1,2)sqlserverselect ename from emp order by substring(ename,len(ename)-1,2)四十三、按字符串中的數(shù)值排序db2select data from V order by cast(replace(translate(data,repeat('#',length(data),replace(translate(data,'#','0123456789'),'#

59、9;,''),'#','') as integer)oracleselect data from V order by to_number(replace(translate(data,replace(translate(data,'0123456789','#'),'#'),rpad('#',20,'#'),'#')postgresqlselect data from V order by cast(replace(translate(data,re

60、place(translate(data,'0123456789','#'),'#',''),rpad('#',20,'#'),'#','') as integer)四十四、根據(jù)表中的行創(chuàng)建一個分割列表db2with x (deptno,cnt,list,empno,len) as (select deptno,count(*) over (partition by deptno), cast(ename as varchar(100),empno,1 from em

61、p union all select x.deptno,t,x.list|','|,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=cntmysqlselect deptno,group_concat(ename order by empno separator,',') as emps from emp group by deptnooracleselect deptno,

62、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-1postgresqlselect 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