震撼分享!五种数据库SQL语句大全

一、连接列值db2/oracle/postgresqlselectname(字段)||'WorksASa'（文字）||jobasmsgfromempwheredeptno=10;mysqlselectconcat(name,'worksasa',job)asmsgfromempwheredeptno=10;sqlserverselectname+'worksasa'+jobasmsgfromempwheredeptno=10;二、使用条件逻辑selectname,salary,casewhensalary=2000then'low'casewhensalary=4000then'over'else'ok'endasstatusfromemp三、限制返回的行数db2select*fromempfetchfirst5rowsonlymysql/postgresqlselect*fromemplimit5oracleselect*fromemprownum=5sqlserverselecttop5*fromemp四、随机返回记录db2selectname,jobfromemporderbyrahnd()fetchfirst5rowsonlymysqlselectname,jobfromemporderbyrand()limit5postgresqlselect*fromemporderbyrandom()limit5oracleselect*from(selectname,jobfromemporderbydbms_random.value())whererownum=5sqlserverselecttop5name,jobfromemporderbynewid()五、将空值转换成实际值selectcoalesce(comm,0)fromemp不为空则返回comm值，空则返回0，comm类型与0类型必须一致六、按子串排序(取消后面2位)db2/mysql/oracle/postgresqlselectname,jobfromemporderbysubstr(job,length(job)-2)sqlserverselectname,jobfromemporderbysubstring(job,len(job)-2,2)七、对字母数字混合的数据排序oracle/postgresqlselectdatafromempvorderbyreplace(data,replace(translate(data,'0123456789','##########'),'#',''),'')selectdatafromemporderbyreplace(translate(data,'0123456789','##########'),'#','')db2select*from(selectename||''||cast(deptnoaschar(2))asdatafromemp)vorderbyreplace(data,replace(translate(data,'##########','0123456789'),'#',''),'')select*from(selectname||''||cast(deptnoaschar(2))asdatafromempvorderbyreplace(translate(data,'##########','0123456789'),'#','')mysql/sqlserver当前不支持translate函数，无解决方案八、处理排序空值db2/mysql/postgresql/sqlserverselectname,sal,commfrom(selectname,sal,commcasewhencommisnullthen0else1endasis_nullfromemp)xorderbyis_nulldesc,commoracleselectname,sal,commfromemporderbycommnullslast//allnullslastselectname,sal,commfromemporderbycommnullsfirst//allnullsfirst九、根据数据项的键排序selectname,sal,job,commfromemporderbycasewhenjob='salesman'thencommelsesalend十、记录集的叠加//使用union子句相当于对使用unionall子句的结果使用distinctselectenameasename_and_dname,deptnofromempwheredeptno=10unionallselect'----------',nullfromt1unionallselectdname,deptnofromdept十一、从一个表红查找另一个表没有的值db2/postgresqlselectdeptnofromdeptexceptselectdeptnofromemporacleselectdeptnofromdeptminusselectdeptnofromempmysql/sqlserverselectdeptnofromdeptwheredeptnonotin(selectdeptnofromemp)十二、在一个表中查找与其他表不匹配的记录db2/mysql/postgresql/sqlserverselectd.*fromdeptdleftouterjoinempeon(d.deptno=e.deptno)wheree.deptnoisnulloracleselectd.*fromdeptd,empewhered.deptno=e.deptno(+)ande.deptnoisnull十三、向查询中增加联接而不影响其他联接db2/mysql/postgresql/sqlserverselecte.ename,d.loc,eb.receivedfromempejoindeptdon(e.deptno=d.deptno)leftjoinemp_bonusebon(e.empno=eb.empno)orderby2oracleselecte.ename,d.loc,eb.receivedfromempe,deptd,emp_bonusebwheree.deptno=d.deptnoande.empno=eb.empno(+)orderby2selecte.ename,d.loc,(selecteb.receivedfromemp_bonusebwhereeb.empno=e.empno)asreceivedfromempe,deptdwheree.deptno=d.deptnoorderby2十四、检测两个表中是否有相同的数据解决原理：1、首先，查找处表emp中存在而视图v中没有的行2、然后合并（unionall）在视图v中存在，而在表emp中没有的行十五、识别和消除笛卡尔积在from子句对表进行联接来返回正确的结果集：selecte.ename,d.locfromempe,deptdwheree.deptno=10andd.deptno=e.deptno十六、聚集与联接mysql/postgresqlselectdeptno,sum(distinctsal)astotal_sal,sum(bonus)astotal_bonusfrom(selecte.empno,e.ename,e.sal,e.deptno,e.sal*casewheneb.type=1then.1wheneb.type=2then.2else.3endasbonusfromempe,emp_bonusebwheree.empno=eb.empnoande.deptno=1)xgroupbydeptnodb2/oracle/sqlserverselectdistinctdeptno,total_sal,total_bonusfrom(selecte.empno,e.ename,sum(distincte.sal)over(partitionbye.deptno)astotal_sal,e.deptno,sum(e.sal*casewheneb.type=1then.1wheneb.type=2then.2else.3end)over(partitionbydeptno)astotal_bonusfromempe,emp_bonusebwheree.empno=eb.empnoande.deptno=10)x十七、聚集与外联接db2/mysql/postgresql/sqlserverselectdeptno,sum(distinctsal)astotal_sal,sum(bonus)astotal_bonusfrom(selecte.empno,e.ename,e.sal,e.deptno,e.sal*casewheneb.typeisnullthen0wheneb.type=1then.1wheneb.type=2then.2else.3endasbonusfromempeleftouterjoinemp_bonusebon(e.empno=eb.empno)wheree.deptno=10)groupbydeptnoselectdistinctdeptno,total_sal,total_bonusfrom(selecte.empno,e.ename,sum(distincte.sal)over(partitionbye.deptno)astotal_sal,e.deptno,sum(e.sal*casewheneb.typeisnullthen0wheneb.type=1then.1wheneb.type=2then.2else.3end)over(partitionbydeptno)astotal_bonusfromempeleftouterjoinemp_bonusebon(e.empno=eb.empno)wheree.deptno=10)xoracleselectdeptno,sum(distinctsal)astotal_sal,sum(bonus)astotal_bonusfrom(selecte.empno,e.ename,e.sal,e.deptno,e.sal*casewheneb.typeisnullthen0wheneb.type=1then.1wheneb.type=2then.2else.3endasbonusfromempe,emp_bonusebwheree.empno=eb.empno(+)ande.deptno=10)groupbydeptno十八、从多个表中返回丢失的数据db2/mysql/postgresql/sqlserverselectd.deptno,d.dname,e.enamefromdeptdfullouterjoinempeon(d.deptno=e.deptno)selectd.deptno,d.dname,e.enamefromdeptdrightouterjoinempeon(d.deptno=e.deptno)unionselectd.deptno,d.dname,e.enamefromdeptdleftouterjoinempeon(d.deptno=e.deptno)oracleselectd.deptno,d.dname,e.enamefromdeptd,empewhered.deptno=e.deptno(+)unionselectd.deptno,d.dname,e.enamefromdeptd,empewhered.deptno(+)=e.deptno十九、在运算和比较时使用null值selectename,commfromempwherecoalesce(comm