数据库sql语句大全
一、连接列值db2oraclepostgresqlselect
ame字段WorksASa（文字）jobasmsgfromempwheredept
o10mysqlselectco
cat
ameworksasajobasmsgfromempwheredept
o10sqlserverselect
ameworksasajobasmsgfromempwheredept
o10
二、使用条件逻辑select
amesalarycasewhe
salary2000the
lowcasewhe
salary4000the
overelseoke
dasstatusfromemp
三、限制返回的行数db2selectfromempfetchfirst5rowso
lymysqlpostgresqlselectfromemplimit5oracleselectfromemprow
um5sqlserverselecttop5fromemp
四、随机返回记录db2select
amejobfromemporderbyrah
dfetchfirst5rowso
lymysqlselect
amejobfromemporderbyra
dlimit5postgresqlselectfromemporderbyra
domlimit5
foracleselectfromselect
amejobfromemporderbydbms_ra
domvaluewhererow
um5sqlserverselecttop5
amejobfromemporderby
ewid
五、将空值转换成实际值selectcoalescecomm0fromemp不为空则返回comm值，空则返回0，comm类型与0类型必须一致六、按子串排序取消后面2位db2mysqloraclepostgresqlselect
amejobfromemporderbysubstrjoble
gthjob2sqlserverselect
amejobfromemporderbysubstri
gjoble
job22
七、对字母数字混合的数据排序oraclepostgresqlorderbydept
o数字selectdatafromempvorderbyreplacedatareplacetra
slatedata0123456789orderby
ame字母selectdatafromemporderbyreplacetra
slatedata0123456789db2orderbydept
o数字selectfromselecte
amecastdept
oaschar2asdatafromempvorderbyreplacedatareplacetra
slatedata0123456789orderby
ame字母selectfromselect
amecastdept
oaschar2asdatafromempvorderbyreplacetra
slatedata0123456789mysqlsqlserver当前不支持tra
slate函数，无解决方案
八、处理排序空值db2mysqlpostgresqlsqlserver
fselect
amesalcommfromselect
amesalcommcasewhe
commis
ullthe
0else1e
dasis_
ullfromempxorderbyis_
ulldesccommoracleselect
amesalcommfromemporderbycomm
ullslastall
ullslastselect
amesalcommfromemporderbycomm
ullsfirstall
ullsfirst
九、根据数据项的键排序select
amesaljobcommfromemporderbycasewhe
jobsalesma
the
commelsesale
d
十、记录集的叠加使用u
io
子句相当于对使用u
io
all子句的结果使用disti
ctselecte
amease
ame_a
d_d
amedept
ofromempwheredept
o10u
io
allselect
ullfromt1u
io
allselectd
amedept
ofromdept
十一、从一个表红查找另一个表没有的值db2postgresqlselectdept
ofromdeptexceptselectdept
ofromemporacleselectdept
ofromdeptmi
usselectdr