2022年数据库sql语句大全参照 .pdf

《2022年数据库sql语句大全参照 .pdf》由会员分享，可在线阅读，更多相关《2022年数据库sql语句大全参照 .pdf（29页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、数据库 sql 语句大全一、连接列值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 server select name+works as a+job as msg from emp where deptno=10;二、使用条件逻辑select name,salary,case when salary=4000 then

2、 over else ok end as status from emp 二、限制返回的行数db2select*from emp fetch first 5 rows only mysql/postgresqlselect*from emp limit 5 oracleselect*from emp WHERE rownum=5 名师资料总结-精品资料欢迎下载-名师精心整理-第 1 页，共 29 页 -sql serverselect top 5*from emp 三、随机返回记录db2select name,job from emp order by rand()fetch first 5

3、rows only mysqlselect name,job from emp order by rand()limit 5 postgresqlselect*from emp order by random()limit 5 oracleselect*from(select name,job from emp order by dbms_random.value()where rownum=5 sql serverselect top 5 name,job from emp order by newid()五、将空值转换成实际值select coalesce(comm,0)from emp

4、不为空则返回 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)七、对字母数字混合的数据排序名师资料总结-精品资料欢迎下载-名师精心整理-第 2 页，共 29 页 -oracle/postgresql select data from em

5、p v order by replace(data,replace(translate(data,0123456789,#),#,),)select data from emp order by replace(translate(data,0123456789,#),#,)db2 select*from(select ename|cast(deptno as char(2)as data from emp)v order by replace(data,replace(translate(data,#,0123456789),#,),)select*from(select name|cast

6、(deptno as char(2)as data from emp 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 null then 0 else 1 end as is_null from emp)x order by is_null desc,comm o

7、racleselect 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 九、根据数据项的键排序名师资料总结-精品资料欢迎下载-名师精心整理-第 3 页，共 29 页 -select name,sal,job,comm from emp order by case when job=salesman then comm else sal end 十、记录集的叠加/使用 unio

8、n 子句相当于对使用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/postgresqlselect deptno from dept except select deptno from emp oracleselect deptno from dept minus select

9、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(+)a

10、nd e.deptno is null 十三、向查询中增加联接而不影响其他联接db2/mysql/postgresql/sqlserverselect e.ename,d.loc,eb.received from emp e join dept d on 名师资料总结-精品资料欢迎下载-名师精心整理-第 4 页，共 29 页 -(e.deptno=d.deptno)left join emp_bonus eb on(e.empno=eb.empno)order by 2 oracleselect e.ename,d.loc,eb.received from emp e,dept d,emp_b

11、onus 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中没有的行

12、十五、识别和消除笛卡尔积在 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_bonus from(select e.empno,e.ename,e.sal,e.deptno,e.sal*case when eb.type=1 then.1 when eb.type=2

13、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 deptno db2/oracle/sqlserver select 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 t

14、hen.1 when eb.type=2 名师资料总结-精品资料欢迎下载-名师精心整理-第 5 页，共 29 页 -then.2 else.3 end)over(partition by deptno)as total_bonus from emp e,emp_bonus eb where e.empno=eb.empno and e.deptno=10)x 十七、聚集与外联接db2/mysql/postgresql/sqlserver select deptno,sum(distinct sal)as total_sal,sum(bonus)as total_bonus from(selec

15、t 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 left outer join emp_bonus eb on(e.empno=eb.empno)where e.deptno=10)group by deptno select distinct deptno,total_sal,total_bonus from(select e.empno,e.ena

16、me,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 eb.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)x oracle sele

17、ct 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,emp_bonus eb where e.empno=eb.empno(+)and e.deptno=10)group by deptno 十八、从多个表中

18、返回丢失的数据db2/mysql/postgresql/sqlserverselect d.deptno,d.dname,e.ename from dept d full outer join emp e on(d.deptno=e.deptno)名师资料总结-精品资料欢迎下载-名师精心整理-第 6 页，共 29 页 -select 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 l

19、eft 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.ename 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

20、 ename=WARD)二十、从一个表向另外的表中复制行insert into dept_east(deptno,dname,loc)select deptno,dname,loc from dept where loc in(NEW YORK,BOSTON)二十一、复制表定义db2create table dept_2 like dept oracle/mysql/postgresqlcreate table dept_2 as select*from dept where 1=0 sqlserverselect*into dept_2 from dept where 1=0 二十二、一次向

21、多个表中插入记录oracleinsert all when loc in(NEW YORK,BOSTON)then into dept_east(deptno,dname,loc)values(deptno,dname,loc)名师资料总结-精品资料欢迎下载-名师精心整理-第 7 页，共 29 页 -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 dept

22、no,dname,loc from dept db2insert into(select*from dept_west union all select*from dept_east union all select*from dept_mid)select*from dept mysql/postgresql/sqlserver不支持多表插入操作二十三、阻止对某几列插入在表中创建一个视图，该视图将只显示允许用户进行操作的列，强制所有的插入操作都通过该视图进行create view new_emps as select empno,ename,job from emp db2/mysqlupd

23、ate emp e set(e.sal,m)=(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)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

24、_sal,emp_comm=ns_comm postgresqlupdate emp set sal=ns.sal,comm=ns.sal/2 from new_sal ns where ns.deptno=emp.deptno 名师资料总结-精品资料欢迎下载-名师精心整理-第 8 页，共 29 页 -sqlserverupdate 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(sele

25、ct*from emp)emp on(ec.empno=emp.empno)when matched then update set m=1000 delete where(sal=3)（以下模式名 schema为 smeagol）二十九、列出模式中的表db2select tabname from syscat.table where tabschema=smeagol 名师资料总结-精品资料欢迎下载-名师精心整理-第 9 页，共 29 页 -oracleselect table_name from all_tables where owner=smeagol postgresql/mysql

26、/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 where owner=smeagol and table_name=emp postgre

27、sql/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.indexcoluse b where a.tabname=emp and a.tabschema=smeagol and a.indschema=b.indschema and a.ind

28、name=b.indname oracleselect table _name,index_name,column_name,column_position from sys.all_ind_columns where table_name=emp and table_owner=smeagol postgresqlselect a.tablename,a.indexname,b.column_name from 名师资料总结-精品资料欢迎下载-名师精心整理-第 10 页，共 29 页 -pg_catalog.pg_indexes a,information_schema.columns b

29、where a.schemaname=smeagol and a.tablename=b.table_name mysqlshow index from emp sqlserverselect 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.

30、index_id=c.index_id and c.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.tabconst a,syscat.columns b where a.tabname=emp and a.tabschema=smeagol and a.tabname=b.tabname and a.tabschema=b.tabschema oracleselect a.

31、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/sqlserverselect a.table_name,a.constraint_name,

32、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 三十三、列出没有相应索引的外键名师资料总结-精品资料欢迎下载-名

33、师精心整理-第 11 页，共 29 页 -db2select 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)f

34、keys 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

35、 is null oracleselect 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

36、 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 postgresqlselect 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 informat

37、ion_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_cata

38、log.pg_indexes a,information_schema.columns b where 名师资料总结-精品资料欢迎下载-名师精心整理-第 12 页，共 29 页 -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

39、.indexname is null mysql使用 show index 来检索索引信息，查询informationschema.key_column_usage 列出指定表的外键sqlserverselect 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 jo

40、in 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.i

41、ndexes 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_na

42、me,comments from dictionary order by table_name 名师资料总结-精品资料欢迎下载-名师精心整理-第 13 页，共 29 页 -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 ite

43、r.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,repl

44、ace(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,012345

45、6789),0,)名师资料总结-精品资料欢迎下载-名师精心整理-第 14 页，共 29 页 -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(lowe

46、r(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

47、)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 se

48、lect 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)名师资料总结-精品资料欢迎下载-名师精心整理-第 15 页，共 29 页 -sqlserver select d

49、ata 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.posx.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

50、名师资料总结-精品资料欢迎下载-名师精心整理-第 17 页，共 29 页 -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