MySQL date and time format conversion Linux SCP Usage Detailedoracle/mysql/sqlserver Function Differences
2011-07-01 12:34:36| Category: MySQL technology | Tags:mysql sqlsever oracle functions | Report | Font size Big medium small subscription
SQL code
1. Type conversion
--oracle Select To_number (' 123 ') from dual; --123; Select To_char from dual; --33; Select To_date (' 2004-11-27 ', ' yyyy/mm/dd ') from dual;--2004-11-27
--mysql Select cast (' 123 ' as signed integer); --123 Select cast (as char (2)); --33; Select To_days (' 2000-01-01 '); --730485
--sqlserver Select cast (' 123 ' as Decimal (30,2)); --123.00 Select cast (as char (2)); --33; select CONVERT (varchar, GETDATE (), 120)
2. Rounding function differences
--oracle Select Round (12.86*10)/10 from dual; --12.9
--mysql Select Format (12.89,1); --12.9
--sqlserver Select round (12.89,1); --12.9
3. Date-time functions
--oracle select Sysdate from dual; --Date and time
--mysql select Sysdate (); --Date Time select Current_date (); --Date
--sqlserver select GETDATE (); --Date Time Select DateDiff (Day, ' 2010-01-01 ', cast (GETDATE () as varchar (10)));--Date difference days
4.Decode function
--oracle Select Decode (sign (in), 1,1,0,0,-1) from Dual;--1
--mysql/sqlserver Select Case [=1 then 1] sign (a) =0 then 0 else-1 end;--1
5. Empty function
--oracle Select NVL (1,0) from dual; --1
--mysql Select Ifnull (1,0); --1
--sqlserver Select IsNull (1,0); --1
6. String Join function
--oracle select ' 1 ' | | ' 2 ' from dual; --12 Select Concat (' 1 ', ' 2 '); --12
--mysql Select Concat (' 1 ', ' 2 '); --12
--sqlserver Select ' 1 ' + ' 2 '; --12
7. Record restriction functions
--oracle Select 1 from dual where rownum <= 10;
--mysql Select 1 from dual limit 10;
--sqlserver Select Top 10 1
8. String Intercept function
--oracle Select substr (' 12345 ', 1,3) from dual;
--mysql/sqlserver Select substring (' 12345 ', 1, 3);
8. Convert multiple rows into a consolidated column
--oracle Select wm_concat (column name) from dual; --Multi-line records are converted into one column for use, split
--mysql/sqlserver Select group_concat (column name);
Comparison of common functions between SQL Server and Oracle
1. Absolute S:select ABS ( -1) value O:select ABS ( -1) value from dual
2. Rounding (Large) S:select ceiling ( -1.001) value O:select ceil ( -1.001) value from dual
3. Rounding (small) s:select floor ( -1.001) value o:select floor ( -1.001) value from dual
4. Rounding (intercept) S:select cast ( -1.002 as int) value
O:select trunc ( -1.002) value from dual
5. Rounding S:select round (1.23456,4) value 1.23460 o:select round (1.23456,4) value from dual 1.2346
6.E for the bottom power s:select exp (1) Value 2.7182818284590451 o:select exp (1) value from dual 2.71828182
7. Take e as the base logarithm s:select log (2.7182818284590451) value 1 o:select ln (2.7182818284590451) value from dual; 1
8. Take 10 for the base logarithm S:select log10 (Ten) value 1 o:select log (10,10) value from dual; 1
9. Take Square S:select Square (4) Value O:select Power (4,2) value from dual 16
10. Take square root S:select SQRT (4) value 2 O:select SQRT (4) value from dual 2
11. For any number of power s:select power (3,4) value o:select power (3,4) value from dual 81
12. Take the random number S:select rand () value O:select sys.dbms_random.value (0,1) value from dual;
13. Take the symbol S:select sign ( -8) value-1 o:select signs ( -8) value from Dual-1----------mathematical function
14. Pi S:select () value 3.1415926535897931 O: Do not know
The 15.sin,cos,tan parameters are in radians, for example: Select Sin (PI ()/2) value gets 1 (SQL Server)
16.asin,acos,atan,atan2 return radians
17. Radian Angle Interchange (sqlserver,oracle not known) DEGREES: radian-〉 angle RADIANS: Angle-〉 radians
Comparison between---------values
18. Set Maximum 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. Find the Set minimum 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. How to handle null values (NULL in F2 is replaced by 10) S:select F1,isnull (f2,10) value from TBL O:select F1,NVL (f2,10) value from TBL--------values Comparison
21. Find the character ordinal s:select ASCII (' a ') value o:select ASCII (' a ') value from dual
22. Character S:select char (value o:select CHR) value from dual
23. Connect S:select ' + ' + ' + ' value o:select CONCAT (' 11 ', ' 22 ') | | Value from dual
23. Substring position--return 3 S:select CHARINDEX (' s ', ' SDSQ ', 2) value O:select INSTR (' sdsq ', ' s ', 2) value from dual
23. The position of the fuzzy substring-return 2, the parameter minus the middle% is returned 7 s:select patindex ('%d%q% ', ' sdsfasdqe ') value o:oracle not found, but InStr can through the fourth haze ask acutely a squid ask?br> se Lect INSTR (' sdsfasdqe ', ' SD ', $) value from dual return 6
24. Substring s:select substring (' ABCD ', 2,2) value
O:select substr (' ABCD ', 2,2) value from dual
25. Substring instead of return AIJKLMNEF s:select STUFF (' abcdef ', 2, 3, ' ijklmn ') value o:select replace (' abcdef ', ' BCD ', ' ijklmn ') value F Rom Dual
26. Substring replacement S: Not found O:select Translate (' Fasdbfasegas ', ' fa ', ' I ') value from dual
27. Length S:len,datalength O:length
28. Case Conversion Lower,upper
29. Initial capital letter S: Not found O:select initcap (' ABCD Dsaf df ') value from dual
30. Left fill space (Lpad the first parameter is a space function) S:select space (Ten) + ' ABCD ' value o:select lpad (' ABCD ') value from dual
31. Right fill space (Rpad the first parameter is a space function) S:select ' ABCD ' +space (x) value O:select rpad (' ABCD ') value from dual
32. Remove the space S:ltrim,rtrim O:ltrim,rtrim,trim
33. Repeating string S:select REPLICATE (' ABCD ', 2) value
O: Not Found
34. Comparison of pronunciation similarity (the two words return the same value, pronounced the same) s:select SOUNDEX (' Smith '), SOUNDEX (' Smythe ') O:select SOUNDEX (' Smith '), SOUNDEX (' Smyth E ') from dual SQL Server, compare Soundex with Select difference (' Smithers ', ' smythers ') to return 0-4,4 for the same sounds, 1 maximum
--------------Date function
35. System Time S:select getdate () value O:select sysdate value from dual
36. Direct and integer addition and subtraction
37. Date S:select Convert (char (ten), GETDATE (), Value O:select trunc (sysdate) value from dual select To_char (Sysdate, ' Yyyy-mm-dd ') value from dual
38. Find Time S:select convert (char (8), GETDATE (), 108) value O:select to_char (sysdate, ' hh24:mm:ss ') value from dual
39. Take the other parts of the DateTime S:datepart and DATENAME functions (the first parameter determines) the second parameter of the O:to_char function determines the parameter---------------------------------the following table needs to be supplemented by year YY, yyyy quarter QQ, Q (quarter) month mm, m (M o invalid) dayofyear dy, y (o table week) Day DD, D (invalid in d o) week wk, ww (wk o Invalid) Weekday DW (o unclear) Hour hh,hh12,hh24 (hh12,hh24 s invalid) minute MI, n (n o Invalid) second SS, s (S o invalid) millisecond ms (o invalid) ----------------------------------------------
40. Last day of the month S: Do not know O:select last_day (sysdate) value from dual
41. One day of the week (e.g. Sunday) S: Do not know O:select next_day (sysdate,7) vaule from DUAL;
42. String Turn time S: can go directly or 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. Find the difference between a part of two dates (e.g. seconds) S:select DateDiff (Ss,getdate (), GETDATE () +12.3) value O: Subtract directly from two dates (e.g. d1-d2=12.3) Select (D1-D2) *24* 60*60 Vaule from DUAL;
44. The date of novelty based on the difference (e.g. minutes) S:select DateAdd (Mi,8,getdate ()) value O:select sysdate+8/60/24 vaule from DUAL;
45. Ask for different time zone times S: Do not know O:select new_time (sysdate, ' ydt ', ' GMT ') vaule from DUAL;
-----Time zone parameter, Beijing in the East 8 district should be Ydt-------AST ADT Atlantic Standard Time BST BDT Bering Sea Standard Time CST CDT Central Standard Time EST EDT Eastern Standard time GMT Greenwich Mean Time HST H DT Alaska-Hawaii Standard Time MST MDT Mountain Standard Time NST Newfoundland standard Time PST PDT Pacific Standard Time YST YDT Yukon Standard Time
oracle/mysql/sqlserver function Differences