2007-10-04 14:18:15

1.絕對值

S:select? ?abs(-1)? ?value

O:select? ?abs(-1)? ?value? ?from? ?dual

2.取整(大)

S:select? ?ceiling(-1.001)? ?value

O:select? ?ceil(-1.001)? ?value? ?from? ?dual

3.取整(小)

S:select? ?floor(-1.001)? ?value

O:select? ?floor(-1.001)? ?value? ?from? ?dual

4.取整(截取)

S:select? ?cast(-1.002? ?as? ?int)? ?value

O:select? ?trunc(-1.002)? ?value? ?from? ?dual

5.四舍五入

S:select? ?round(1.23456,4)? ?value? ?1.23460

O:select? ?round(1.23456,4)? ?value? ?from? ?dual? ?1.2346

6.e為底的冪

S:select? ?Exp(1)? ?value? ?2.7182818284590451

O:select? ?Exp(1)? ?value? ?from? ?dual? ?2.71828182

7.取e為底的對數

S:select? ?log(2.7182818284590451)? ?value? ?1

O:select? ?ln(2.7182818284590451)? ?value? ?from? ?dual;? ?1

8.取10為底對數

S:select? ?log10(10)? ?value? ?1

O:select? ?log(10,10)? ?value? ?from? ?dual;? ?1

9.取平方

S:select? ?SQUARE(4)? ?value? ?16

O:select? ?power(4,2)? ?value? ?from? ?dual? ?16

10.取平方根

S:select? ?SQRT(4)? ?value? ?2

O:select? ?SQRT(4)? ?value? ?from? ?dual? ?2

11.求任意數為底的冪

S:select? ?power(3,4)? ?value? ?81

O:select? ?power(3,4)? ?value? ?from? ?dual? ?81

12.取隨機數

S:select? ?rand()? ?value

O:select? ?sys.dbms_random.value(0,1)? ?value? ?from? ?dual;

13.取符號

S:select? ?sign(-8)? ?value? ?-1

O:select? ?sign(-8)? ?value? ?from? ?dual? ?-1

----------數學函數

14.圓周率

S:SELECT? ?PI()? ?value? ?3.1415926535897931

O:不知道

15.sin,cos,tan? ?參數都以弧度為單位

例如：select? ?sin(PI()/2)? ?value? ?得到1(SQLServer)

16.Asin,Acos,Atan,Atan2? ?返回弧度

17.弧度角度互換(SQLServer，Oracle不知道)

DEGREES：弧度-〉角度

RADIANS：角度-〉弧度

---------數值間比較

18.? ?求集合最大值

S:select? ?max(value)? ?value? ?from

(select? ?1? ?value

union

select? ?-2? ?value

union

select? ?4? ?value

union

select? ?3? ?value)a

O:select? ?greatest(1,-2,4,3)? ?value? ?from? ?dual

19.? ?求集合最小值

S:select? ?min(value)? ?value? ?from

(select? ?1? ?value

union

select? ?-2? ?value

union

select? ?4? ?value

union

select? ?3? ?value)a

O:select? ?least(1,-2,4,3)? ?value? ?from? ?dual

20.如何處理null值(F2中的null以10代替)

S:select? ?F1,IsNull(F2,10)? ?value? ?from? ?Tbl

O:select? ?F1,nvl(F2,10)? ?value? ?from? ?Tbl

--------數值間比較

21.求字符序號

S:select? ?ascii('a')? ?value

O:select? ?ascii('a')? ?value? ?from? ?dual

22.從序號求字符

S:select? ?char(97)? ?value

O:select? ?chr(97)? ?value? ?from? ?dual

23.連接

S:select? ?'11'+'22'+'33'? ?value

O:select? ?CONCAT('11','22')||33? ?value? ?from? ?dual

23.子串位置? ?--返回3

S:select? ?CHARINDEX('s','sdsq',2)? ?value

O:select? ?INSTR('sdsq','s',2)? ?value? ?from? ?dual

23.模糊子串的位置? ?--返回2,參數去掉中間%則返回7

S:select? ?patindex('%d%q%','sdsfasdqe')? ?value

Oracle沒發現，但是instr可以通過第四霾問刂瞥魷執問?BR>　　select? ?INSTR('sdsfasdqe','sd',1,2)? ?value? ?from? ?dual? ?返回6

24.求子串

S:select? ?substring('abcd',2,2)? ?value

O:select? ?substr('abcd',2,2)? ?value? ?from? ?dual

25.子串代替? ?返回aijklmnef

S:SELECT? ?STUFF('abcdef',? ?2,? ?3,? ?'ijklmn')? ?value

O:SELECT? ?Replace('abcdef',? ?'bcd',? ?'ijklmn')? ?value? ?from? ?dual

26.子串全部替換

S:沒發現

O:select? ?Translate('fasdbfasegas','fa','我'? ?)? ?value? ?from? ?dual

27.長度

S:len,datalength

O:length

28.大小寫轉換? ?lower,upper

29.單詞首字母大寫

S:沒發現

O:select? ?INITCAP('abcd? ?dsaf? ?df')? ?value? ?from? ?dual

30.左補空格(LPAD的第一個參數為空格則同space函數)

S:select? ?space(10)+'abcd'? ?value

O:select? ?LPAD('abcd',14)? ?value? ?from? ?dual

31.右補空格(RPAD的第一個參數為空格則同space函數)

S:select? ?'abcd'+space(10)? ?value

O:select? ?RPAD('abcd',14)? ?value? ?from? ?dual

32.刪除空格

S:ltrim,rtrim

O:ltrim,rtrim,trim

33.? ?重復字符串

S:select? ?REPLICATE('abcd',2)? ?value

O:沒發現

34.發音相似性比較(這兩個單詞返回值一樣，發音相同)

S:SELECT? ?SOUNDEX? ?('Smith'),? ?SOUNDEX? ?('Smythe')

O:SELECT? ?SOUNDEX? ?('Smith'),? ?SOUNDEX? ?('Smythe')? ?from? ?dual

SQLServer中用SELECT? ?DIFFERENCE('Smithers',? ?'Smythers')? ?比較soundex的差

返回0-4，4為同音，1最高

--------------日期函數

35.系統時間

S:select? ?getdate()? ?value

O:select? ?sysdate? ?value? ?from? ?dual

36.前后幾日

直接與整數相加減

37.求日期

S:select? ?convert(char(10),getdate(),20)? ?value

O:select? ?trunc(sysdate)? ?value? ?from? ?dual

select? ?to_char(sysdate,'yyyy-mm-dd')? ?value? ?from? ?dual

38.求時間

S:select? ?convert(char(8),getdate(),108)? ?value

O:select? ?to_char(sysdate,'hh24:mm:ss')? ?value? ?from? ?dual

39.取日期時間的其他部分

S:DATEPART? ?和? ?DATENAME? ?函數? ?(第一個參數決定)

O:to_char函數? ?第二個參數決定

參數---------------------------------下表需要補充

year? ?yy,? ?yyyy

quarter? ?qq,? ?q? ?(季度)

month? ?mm,? ?m? ?(m? ?O無效)

dayofyear? ?dy,? ?y? ?(O表星期)

day? ?dd,? ?d? ?(d? ?O無效)

week? ?wk,? ?ww? ?(wk? ?O無效)

weekday? ?dw? ?(O不清楚)

Hour? ?hh,hh12,hh24? ?(hh12,hh24? ?S無效)

minute? ?mi,? ?n? ?(n? ?O無效)

second? ?ss,? ?s? ?(s? ?O無效)

millisecond? ?ms? ?(O無效)

----------------------------------------------

40.當月最后一天

S:不知道

O:select? ?LAST_DAY(sysdate)? ?value? ?from? ?dual

41.本星期的某一天(比如星期日)

S:不知道

O:SELECT? ?Next_day(sysdate,7)? ?vaule? ?FROM? ?DUAL;

42.字符串轉時間

S:可以直接轉或者select? ?cast('2004-09-08'as? ?datetime)? ?value

O:SELECT? ?To_date('2004-01-05? ?22:09:38','yyyy-mm-dd? ?hh24-mi-ss')? ?vaule? ?FROM? ?DUAL;

43.求兩日期某一部分的差(比如秒)

S:select? ?datediff(ss,getdate(),getdate()+12.3)? ?value

O:直接用兩個日期相減(比如d1-d2=12.3)

SELECT? ?(d1-d2)*24*60*60? ?vaule? ?FROM? ?DUAL;

44.根據差值求新的日期(比如分鐘)

S:select? ?dateadd(mi,8,getdate())? ?value

O:SELECT? ?sysdate+8/60/24? ?vaule? ?FROM? ?DUAL;

45.求不同時區時間

S:不知道

O:SELECT? ?New_time(sysdate,'ydt','gmt'? ?)? ?vaule? ?FROM? ?DUAL;

-----時區參數,北京在東8區應該是Ydt-------

AST? ?ADT? ?大西洋標準時間

BST? ?BDT? ?白令海標準時間

CST? ?CDT? ?中部標準時間

EST? ?EDT? ?東部標準時間

GMT? ?格林尼治標準時間

HST? ?HDT? ?阿拉斯加—夏威夷標準時間

MST? ?MDT? ?山區標準時間

NST? ?紐芬蘭標準時間

PST? ?PDT? ?太平洋標準時間

YST? ?YDT? ?YUKON標準時間

總結

以上是生活随笔為你收集整理的mssql与oracle不同点,MySql,Mssql,Oracle的优缺点和异同（欢迎补充） *的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。