SQL常用语句及函数方法

1、通常用到的字符串转日期格式SelectCONVERT(varchar(100),GETDATE(),0):0516200610:57AMSelectCONVERT(varchar(100),GETDATE(),1):05/16/06SelectCONVERT(varchar(100),GETDATE(),2):06.05.16SelectCONVERT(varchar(100),GETDATE(),3):16/05/06SelectCONVERT(varchar(100),GETDATE(),4):16.05.06SelectCONVERT(varchar(100),GETDATE(),5):16-05-06SelectCONVERT(varchar(100),GETDATE(),6):160506SelectCONVERT(varchar(100),GETDATE(),7):0516,06SelectCONVERT(varchar(100),GETDATE(),8):10:57:46SelectCONVERT(varchar(100),GETDATE(),9):0516200610:57:46:827AMSelectCONVERT(varchar(100),GETDATE(),10):05-16-06SelectCONVERT(varchar(100),GETDATE(),11):06/05/16SelectCONVERT(varchar(100),GETDATE(),12):060516SelectCONVERT(varchar(100),GETDATE(),13):1605200610:57:46:937SelectCONVERT(varchar(100),GETDATE(),14):10:57:46:967SelectCONVERT(varchar(100),GETDATE(),20):2006-05-1610:57:47SelectCONVERT(varchar(100),GETDATE(),21):2006-05-1610:57:47.157SelectCONVERT(varchar(100),GETDATE(),22):05/16/0610:57:47AMSelectCONVERT(varchar(100),GETDATE(),23):2006-05-16SelectCONVERT(varchar(100),GETDATE(),24):10:57:47SelectCONVERT(varchar(100),GETDATE(),25):2006-05-1610:57:47.250SelectCONVERT(varchar(100),GETDATE(),100):0516200610:57AMSelectCONVERT(varchar(100),GETDATE(),101):05/16/2006SelectCONVERT(varchar(100),GETDATE(),102):2006.05.16SelectCONVERT(varchar(100),GETDATE(),103):16/05/2006SelectCONVERT(varchar(100),GETDATE(),104):16.05.2006SelectCONVERT(varchar(100),GETDATE(),105):16-05-2006SelectCONVERT(varchar(100),GETDATE(),106):16052006SelectCONVERT(varchar(100),GETDATE(),107):0516,2006SelectCONVERT(varchar(100),GETDATE(),108):10:57:49SelectCONVERT(varchar(100),GETDATE(),109):0516200610:57:49:437AMSelectCONVERT(varchar(100),GETDATE(),110):05-16-2006SelectCONVERT(varchar(100),GETDATE(),111):2006/05/16SelectCONVERT(varchar(100),GETDATE(),112):20060516SelectCONVERT(varchar(100),GETDATE(),113):1605200610:57:49:513SelectCONVERT(varchar(100),GETDATE(),114):10:57:49:547SelectCONVERT(varchar(100),GETDATE(),120):2006-05-1610:57:49SelectCONVERT(varchar(100),GETDATE(),121):2006-05-1610:57:49.700SelectCONVERT(varchar(100),GETDATE(),126):2006-05-16T10:57:49.827SelectCONVERT(varchar(100),GETDATE(),130):18??????????142710:57:49:907AMSelectCONVERT(varchar(100),GETDATE(),131):18/04/142710:57:49:920AM2、Sql与oracle集区别Sqlserver中except差集，intersect交集,union并集，select*fromaexcept(selecta.mail,a.name,a.pass,a.timefromainnerjoincona.mail=c.mailanda.name=c.nameanda.pass=c.pass)Oracle中minus差集，intersect交集，union并集select*fromaminus(selecta.mail,a.name,a.pass,a.timefromainnerjoincona.mail=c.mailanda.name=c.nameanda.pass=c.pass)merge用法：Mergeintotable1Usingtable2on(table1.id=table2.idandtable1.name=table2.name)WhenmatchedthenUpdatesettable1.name=table2.name,table1.mail=table2.mailWhennotmatchedthenInsert(id,name,mail)values(table2.id,table2.name,table2.mail)3、数学函数序号函数功能SQLServer用法Oracle用法1绝对值selectabs(-1)valueselectabs(-1)valuefromdual2取整(大)selectceiling(-1.001)valueselectceil(-1.001)valuefromdual3取整(小)selectfloor(-1.001)valueselectfloor(-1.001)valuefromdual4取整（截取）selectcast(-1.002asint)valueselecttrunc(-1.002)valuefromdual5四舍五入selectround(1.23456,4)valueselectround(1.23456,4)valuefromdual6为底的幂selectExp(1)valueselectExp(1)valuefromdual7取e为底的对数selectlog(2.7182818284590451)selectln(2.7182818284590451)8取10为底对数selectlog10(10)selectlog(10,10)9取平方selectSQUARE(4)selectpower(4,2)10取平方根selectSQRT(4)selectSQRT(4)11求任意数为底的幂selectpower(3,4)selectpower(3,4)12取随机数selectrand()selectsys.dbms_random.value(0,1)13取符号selectsign(-8)selectsign(-8)14圆周率SELECTPI()15sin,cos,tanselectsin(PI()/2)selectsin(PI()/2)16求集合最大值selectmax(value)valuefrom(select1valueunionselect-2valueunionselect4valueunionselect3value)aselectgreatest(1,-2,4,3)valuefromdual17求集合最小值selectmin(value)valuefrom(select1valueunionselect-2valueunionselect4valueunionselect3value)aselectleast(1,-2,4,3)valuefromdual18处理null值(F2中的null以10代替)selectF1,IsNull(F2,10)valuefromTblselectF1,nvl(F2,10)valuefromTbl4、数值间比较序号函数功能SQLServer用法Oracle用法1求字符序号selectascii('a')selectascii('a')valuefromdual2从序号求字符selectchar(97)valueselectchr(97)valuefromdual3连接select'11'+'22'+'33'valueselectCONCAT('11','22')||33valuefromdual4子串位置selectcharindex('s','sdsq',2)valueselectinstr('sdsq','s',2)valuefromdual5模糊子串的位(返回2,参数去掉中间%则返回7)selectpatindex('%d%q%','sdsfasdqe')valueselectINSTR('sdsfasdqe','sd',1,2)valuefromdual返回66求子串selectsubstring('abcd',2,2)valueselectsubstr('abcd',2,2)valuefromdual7子串代替(返回aijklmnef)SELECTSTUFF('abcdef',2,3,'ijklmn')valueSELECTReplace('abcdef','bcd','ijklmn')valuefromdual8子串全部替换没发现selectTranslate('fasdbfasegas','fa','我')valuefromdual9长度Len或datalengthlength10大小写转换lower,upperlower,upper11单词首字母大写没发现selectINITCAP('abcddsafdf')valuefromdual12左补空格selectspace(10)+'abcd'valueselectLPAD('abcd',14)valuefromdual13右补空格select'abcd'+space(10)valueselectRPAD('abcd',14)valuefromdual14删除空格ltrim,rtrimltrim,rtrim,trim15重复字符串selectREPLICATE('abcd',2)valuelpad('d',6,'0'),rpad(‘d’,6,’0’)16发音相似性比较(这两个单词返回值一样，发音相同)SELECTSOUNDEX('Smith'),SOUNDEX('Smythe')SELECTSOUNDEX('Smith'),SOUNDEX('Smythe')fromdual5、日期函数序号函数功能