DBMSº° ÇÔ¼ö ºñ±³  MySQL 3.23.5 MSSQL 2000 Oracle 8, 8i(815), 9i(R2)  Áý°è ÇÔ¼ö  --------- °øÅë ÇÔ¼ö --------- COUNT(expr), AVG(expr), MIN(expr), MAX(expr), SUM(expr) MySQL°ú Oracle8 ¹öÀüÀº Ç¥ÁØÆíÂ÷¿¡ ´ëÇÑ Áý°èÇÔ¼ö¸¦ Á¦°øÇÏ°í ÀÖÀ¸³ª ºÐ»ê¿¡ ´ëÇÑ ÇÔ ¼ö¸¦ Á¦°øÇÏ°í ÀÖÁö ¾ÊÀ¸¸ç, MSSQL, Oracle8iÀÌ»óÀÇ ¹öÀü¿¡¼­´Â ºÐ»ê°ú Ç¥ÁØÆíÂ÷¿¡ ´ë ÇÑ ÇÔ¼ö ÀÌ¿Ü¿¡ ´Ù¸¥ Áý°èÇÔ¼ö¸¦ Á¦°øÇÏ°í ÀÖ´Ù. (ÇÔ¼ö¸íÀÌ ¾à°£¾¿ Ʋ¸®´Ù.) <Åë°èÀû Ç¥ÁØÆíÂ÷> MySQL : STD MSSQL : STDEV Oracle8 : STDDEV Oracle8i : STDDEV_SAMP Oracle9i : STDDEV_SAMP <¸ðÁý´ÜÀÇ Åë°èÀû ºÐ»ê> MySQL : STDDEV MSSQL : STDEVP Oracle8 : Oracle8i : STDDEV_POP Oracle9i : STDDEV_POP Oracle8i ÀÌ»ó, MSSQL 2000 <Åë°èÀû ºÐ»ê> MySQL : MSSQL : VAR Oracle8 : Oracle8i : VAR_SAMP Oracle9i : VAR_SAMP <¸ðÁý´ÜÀÇ Åë°èÀû ºÐ»ê> MySQL : MSSQL : VARP Oracle8 : Oracle8i : VAR_POP Oracle9i : VAR_POP ------------ MySQL 3.23.5 ------------ STD(expr) : Åë°èÀû Ç¥ÁØ ÆíÂ÷ STDDEV(expr) : ¸ðÁý´Ü¿¡ ´ëÇÑ Ç¥ÁØÆíÂ÷ BIT_OR(expr) : Ç¥Çö½ÄÀÇ ORºñÆ® ¿¬»ê BIT_AND(expr) : Ç¥Çö½ÄÀÇ ANDºñÆ® ¿¬»ê ---------- MSSQL 2OOO ---------- STDEV : Åë°èÀû Ç¥ÁØ ÆíÂ÷ STDEVP : ¸ðÁý´Ü¿¡ ´ëÇÑ Ç¥ÁØÆíÂ÷ BINARY_CHECKSUM : ÀÌÁø °Ë»ç°ª ¹Ýȯ(ÇàÀÇ º¯°æ»çÇ× °Ë»ö¿¡ »ç¿ë) CHECKSUM : ÇؽÃÀ妽º »ý¼º½Ã »ç¿ë CHECKSUM_AGG : ±×·ì¿¡¼­ÀÇ °Ë»ç°ª ¹Ýȯ COUNT_BIG : COUNT()´Â int°ª¹ÝȯÇÏ°í COUNT_BIGÀº bigint ¹Ýȯ GROUPING : ÇàÀÌ CUBE ¶Ç´Â ROLLUP ¿¬»êÀÚ¸¦ ÅëÇØ Ãß°¡µÉ ¶§ Ãß°¡ ¿­ÀÇ Ãâ·ÂÀÌ 1ÀÌ µÇ°Å³ª ÇàÀÌ CUBE ¶Ç´Â ROLLUPÀÇ °á°ú°¡ ¾Æ´Ò ¶§ Ãß°¡ ¿­ÀÇ Ãâ ·ÂÀÌ 0ÀÌ µÇµµ·Ï ÇÏ´Â Áý°è ÇÔ¼ö VAR : Åë°èÀû ºÐ»ê VARP : ¸ðÁý´ÜÀÇ Åë°èÀû ºÐ»ê USE pubs SELECT royalty, SUM(advance) 'total advance', GROUPING(royalty) 'grp' FROM titles GROUP BY royalty WITH ROLLUP ---- °á°ú ---- royalty total advance grp --------- --------------------- --- NULL NULL 0 10 57000.0000 0 12 2275.0000 0 14 4000.0000 0 16 7000.0000 0 24 25125.0000 0 NULL 95400.0000 1 -------- Oracle 8 -------- STDDEV : Ç¥ÁØÆíÂ÷ VARIANCE : Æò¹æÆíÂ÷ (Æò¹æÆíÂ÷ °ø½Ä: Âü°íÀÚ·á) --------------------------------- Oracle 8i(º¯°æµÇ°Å³ª Ãß°¡µÈ ÇÔ¼ö) --------------------------------- * 816, 817 ¹öÀü¿¡µµ º¯È­ ¾ø½¿. 816¹öÀü¿¡¼­´Â ºÐ¼®¿ë ÇÔ¼ö°¡ ¸¹ÀÌ Ãß°¡µÇ¾ú´Ù. CORR(x, y) : µÎ ¼ýÀÚ»çÀÌÀÇ »óÈ£ °ü°è¸¦ ³ªÅ¸³»´Â ÇÔ¼ö(¿¹: x:y = 10:90) VAR_POP : ¸ðÁý´ÜÀÇ Åë°èÀû ºÐ»ê VAR_SAMP : Åë°èÀû ºÐ»ê COVAR_POP : ¸ðÁý´ÜÀÇ °øºÐ»ê COVAR_SAMP : Åë°èÀû °øºÐ»ê STDDEV_POP : ¸ðÁý´ÜÀÇ Ç¥ÁØÆíÂ÷ STDDEV_SAMP : Åë°èÀû Ç¥ÁØÆíÂ÷ GROUPING : MSSQL 2000°ú ¾à°£ ´Ù¸§ (¿¹Á¦ ÂüÁ¶, ¸Þ´º¾ó ÂüÁ¶) REGR_ (linear regression) functions : ¸Þ´º¾ó ÂüÁ¶ SELECT mgr, CORR(sal, comm) FROM EMP GROUP BY mgr HAVING mgr = 7698; MGR CORR(SAL,COMM) ---------- -------------- 7698 -.69920974 SELECT DECODE(GROUPING(dname), 1, 'All Departments', dname) AS dname, DECODE(GROUPING(job), 1, 'All Jobs', job) AS job, COUNT(*) "Total Empl", AVG(sal) * 12 "Average Sal" FROM emp, dept WHERE dept.deptno = emp.deptno GROUP BY ROLLUP (dname, job); DNAME JOB Total Empl Average Sa --------------- --------- ---------- ---------- ACCOUNTING CLERK 1 15600 ACCOUNTING MANAGER 1 29400 ACCOUNTING PRESIDENT 1 60000 ACCOUNTING All Jobs 3 35000 RESEARCH ANALYST 2 36000 RESEARCH CLERK 2 11400 RESEARCH MANAGER 1 35700 RESEARCH All Jobs 5 26100 SALES CLERK 1 11400 SALES MANAGER 1 34200 SALES SALESMAN 4 16800 SALES All Jobs 6 18800 All Departments All Jobs 14 24878.5714 --------------------------------- Oracle 9i(º¯°æµÇ°Å³ª Ãß°¡µÈ ÇÔ¼ö) --------------------------------- º¯È­¾ø½¿, Oracle¿¡¼­´Â ¹öÀü 8.1.6 ÀÌ»óÀÇ ¹öÀü¿¡¼­ Àû¿ëµÇ´Â ºÐ¼®ÇÔ¼ö¸¦ Á¦°øÇÏ°í Àִµ¥ Áý°èÇÔ¼ö¿Í °ÅÀÇ °°ÀÌ »ç¿ëµÇ°í ÀÖ´Ù. ÀÌÀüÀÇ Áý°èÇÔ¼öÀÇ ¿ëµµ·Î º¸±â º¸´Ù´Â ºÐ¼®¿ë ÇÔ¼öÀÇ ¿ëµµ·Î ºÁ¾ß ÇÑ´Ù.  ¼öÄ¡ ¿¬»ê ÇÔ¼ö  -------- °øÅëÇÔ¼ö -------- ABS(X) : Àý´ë°ª SIGN(X) : À½¼ö¸é -1, 0À̸é 0, ¾ç¼ö¸é 1À» ¹Ýȯ MOD(N,M) : ³ª¸ÓÁö(MSSQL ÀÇ °æ¿ì´Â % ¿¬»êÀÚ »ç¿ë) FLOOR(X) : ±Ù»ç°ª(1.23 -> 1, -1.23 -> -2) CEILING(X) : ±Ù»ç°ª(1.23 -> 2, -1.23 -> -1)(OracleÀÇ °æ¿ì CEIL) POWER(X,Y) : °ÅµìÁ¦°ö SQRT(X) : Á¦°ö±Ù PI() : ÆÄÀÌ(Oracle Á¦°ø¾ÈÇÔ) COS(X) SIN(X) TAN(X) ACOS(X) ASIN(X) ATAN(X) ATAN(Y,X) ATN2 : źÁ¨Æ® °ªÀÌ ÁöÁ¤ÇÑ µÎ °³ÀÇ float ½Ä »çÀÌ¿¡ ¼Ó ÇÏ´Â °¢µµ¸¦ ¶óµð¾ÈÀ¸·Î ¹Ýȯ COT(X) RAND(N) : 0 ~ 1.0±îÁöÁß ·£´ýÇÑ ¼ö ¹Ýȯ OracleÁö¿ø¾ÈÇÔ select mod(abs(dbms_random.random),99) + 1 from dual; ÀÇ ½ÄÀ¸·Î Áö¿ø LEAST(X,Y,...) : Ç¥ÇöµÈ ¼ö Áß °¡Àå ÀÛÀº°ª ¹Ýȯ GREATEST(X,Y,...) : Ç¥ÇöµÈ ¼ö Áß °¡Àå Å«°ª ¹Ýȯ DEGREES(X) : ¶óµð¾È-> °¢µµ RADIANS(X) : °¢µµ -> ¶óµð¾È(¿À¶óŬ Áö¿ø ¾ÈÇÔ ATAN µîÀ¸·Î ´ëü) TRUNCATE(X,D) : ÀÚ¸§ÇÔ¼ö(Round(x,d)¿Í ºñ½ÁÇÏÁö¸¸ ¹Ý¿Ã¸²ÇÏÁö ¾ÊÀ½) M SSQL Áö¿ø ¾ÈÇÔ(MySQLÀ̳ª Oracleµµ ¿ëµµ°¡ ¾à°£¾¿ Ʋ¸²) MSSQL¿¡¼­´Â deleteÀÇ ¿ëµµ·Î Æ®·£Àè¼ÇÀ» °ÉÁö¾Ê°í µ¥ÀÌÅ͸¦ »è Á¦½Ãų ¶§ »ç¿ëÇÑ´Ù. ------------ MySQL 3.23.5 ------------ ROUND(X) : ¹Ý¿Ã¸²(-1.23 -> -1, -1.58-> -2, 1.58 ->2) LN(X) : ·Î±× (4.0.3 ¹öÀü : LOG(X), LOG(B,X),LOG2(X), LOG10(X) ) POW(X,Y) : °ÅµìÁ¦°ö RAND() RAND(N) : 0 ~ 1.0±îÁöÁß ·£´ýÇÑ ¼ö ¹Ýȯ mysql> SELECT ROUND(1.298, 1); -> 1.3 mysql> SELECT ROUND(1.298, 0); -> 1 ---------- MSSQL 2OOO ---------- °øÅëÇÔ¼ö¿¡ ¸ðµÎ Æ÷ÇÔµÊ.. ¾à°£ Ʋ¸°°ÍÀº °øÅëÇÔ¼ö ºÎºÐ¿¡ Ç¥±âÇß½¿ Âü°í.. -------- Oracle 8 -------- TRUNC : ÀÚ¸§ÇÔ¼ö(³¯Â¥¿¡¼­ ÃÊ´ÜÀ§µµ Â¥¸¦ ¼ö ÀÖÀ½) --------- Oracle 8i --------- º¯È­ ¾ø½¿ --------- Oracle 9i --------- <Ãß°¡ÇÔ¼ö> BITAND : Ç¥ÇöµÈ µÎ¼öÀÇ ºñÆ® And¿¬»ê WIDTH_BUCKET ( expr , min_value , max_value , num_buckets ) : num_bucketsÀÇ ±×·ìÀ¸·Î ³ª´«´Ù.  ¹®ÀÚ¿­ ÇÔ¼ö  ¹®ÀÚ¿­ ¿¬»ê½Ã °¢ DBMS¸¶´Ù ( +, ||ÀÇ »ç¿ëÀÌ Æ²¸®¹Ç·Î ÁÖÀÇÇؾßÇÑ´Ù.) ---- ¿¹Á¦ ---- <¿À¶óŬ> SQL> SELECT 1+1 FROM DUAL; 1+1 ---------- 2 SQL> SELECT 'A' || 'B' FROM DUAL; 'A -- AB SQL> SELECT 'A' + 'B' FROM DUAL; --> ÁÖÀÇ ±í°Ô »ìÆì º¾´Ï´Ù. SELECT 'A' + 'B' FROM DUAL * 1Çà¿¡ ¿À·ù: ORA-01722: ¼öÄ¡°¡ ºÎÀûÇÕÇÕ´Ï´Ù SQL> SELECT '1' + 1 FROM DUAL; '1'+1 ---------- 2 SQL> SELECT '1' + '1' FROM DUAL; '1'+'1' ---------- 2 SQL> SELECT '1' || '1' FROM DUAL; '1 -- 11 SQL> SELECT MOD(3,2) FROM DUAL; MOD(3,2) ---------- 1 SELECT '1' + '1' ---- 11 (1°³ Çà Àû¿ëµÊ) SELECT 3%2 AS '³ª¸ÓÁö' ³ª¸ÓÁö ----------- 1 (1°³ Çà Àû¿ëµÊ) mysql> SELECT 1+"1"; -> 2 mysql> SELECT CONCAT(2,' test'); -> '2 test' -------- °øÅëÇÔ¼ö -------- ASCII(str) : ½ºÆ®¸µ -> ASCII CHAR(N,...) : ASCIIÄڵ忡 ´ëÀÀÇÏ´Â ¹®ÀÚ·Î(MSSQL : STR()) ORD(str) : multi-byte character(MySQL, MSSQL : NCHAR()) CONCAT(str1,str2,...) : ¹®ÀÚ¿­ µ¡¼À( || ¿Í ºñ½Á, MySQL, Oracle ÇØ´ç) LENGTH(str) : ¹®ÀÚ¿­ÀÇ ±æÀÌ OCTET_LENGTH(str) : ¹®ÀÚ¿­ÀÇ ±æÀÌ CHAR_LENGTH(str) : ¹®ÀÚ¿­ÀÇ ±æÀÌ(multi-byte characters) (MSSQL : UNICODE()) CHARACTER_LENGTH(str) : ¹®ÀÚ¿­ÀÇ ±æÀÌ(multi-byte characters) LOCATE(substr,str) : substr°¡ strÀÇ ¸î¹ø°¿¡ À§Ä¡ÇÏ´ÂÁö ³ªÅ¸³¿ POSITION(substr IN str) : substr°¡ strÀÇ ¸î¹ø°¿¡ À§Ä¡ÇÏ´ÂÁö ³ªÅ¸³¿ LOCATE(substr,str,pos) : pos(Position)À» Á¤ÇØÁÖ¸é ±× À§Ä¡ ´ÙÀ½ºÎÅÍ ¸î¹ø°ÀÎÁö ¹Ýȯ INSTR(str,substr) : LOCATE()¿Í °°Áö¸¸ ¾Æ±Ô¸ÕÆ®ÀÇ À§Ä¡°¡ ¹Ý´ë (MSSQL : PATINDEX ( '%pattern%' , expression ) ¶Ç´Â CHARINDEX ÇÔ¼ö, Oracle: INSTR (char1,char2 [,n[,m]]) ) LPAD(str,len,padstr) : str¿¡ len¸¸Å­ÀÇ À̵¿ÇÏ¿© ±× À§Ä¡¿¡ padstr¸¦ ¿ÞÂʺΠÅͳִ´Ù.(MySQL, Oracle ÇØ´ç) RPAD(str,len,padstr) : LPAD¿Í ¹Ý´ë·Î ¿À¸¥ÂÊ(MySQL, Oracle ÇØ´ç) LEFT(str,len) : strÀ» ¿ÞÂÊ¿¡¼­ len¸¸Å­ÀÇ ±ÛÀÚ¸¸ ³ªÅ¸³½´Ù. (MySQL, MSSQL) RIGHT(str,len) : LEFT()¿Í ¹Ý´ë (MySQL, MSSQL) SUBSTRING(str,pos,len) : strÀ» posºÎÅÍ len°³ ¸¸Å­ ±ÛÀÚ¸¦ ¸®ÅÏ (OracleÀº substr()·Î ¸íĪÀÌ Æ²¸®´Ù.) LTRIM(str) : ¿ÞÂÊÀÇ °ø¹é Â¥¸§ RTRIM(str) : ¿À¸¥ÂÊÀÇ °ø¹é Â¥¸§ TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str) : Â¥¸§ÇÔ¼ö (¿¹Á¦ ÂüÁ¶)(MySQL, Oracle8iÀÌ»ó¸¸ ÇØ´ç) SOUNDEX(str) : µÎ ¹®ÀÚ¿­ÀÇ À¯»ç¼º Æò°¡ (mysqlÀº Àß ¸ð¸£°ÚÀ½) ¤Ñ¤Ñ; SPACE(N) : N°³ÀÇ °ø¹é ¸®ÅÏ REPLACE(str,from_str,to_str) : str¿¡¼­ from_str ¹®ÀÚ¸¦ to_str¹®ÀڷΠġȯ REPEAT(str,count) : strÀ» count ¸¸Å­ ¹Ýº¹µÈ ¹®ÀÚ¿­·Î º¯È¯ REVERSE(str) : strÀ» ¾ÕµÚ·Î µÚÁý´Â´Ù. (MySQL, MSSQL) LCASE(str) : ´ë¹®ÀÚ·Î(multi-byte)(MySQL, Oracle : NLS_UPPER) (¸Þ´º¾ó ÂüÁ¶ÇؾßÇÔ) LOWER(str) : ¼Ò¹®ÀÚ·Î UCASE(str) :´ë¹®ÀÚ·Î(multi-byte)(MySQL, Oracle : NLS_LOWER) (¸Þ´º¾ó ÂüÁ¶ÇؾßÇÔ) UPPER(str) :¼Ò¹®ÀÚ·Î(multi-byte) ------------ MySQL 3.23.5 ------------ CONV(N,from_base,to_base) : N À» from_base Áø¼ö¿¡¼­ to_baseÁø¼ö·Î º¯È¯ BIN(N) : 2Áø¼ö·Î º¯È¯ OCT(N) : 8Áø¼ö·Î º¯È¯ HEX(N_or_S) : 16Áø¼ö·Î º¯È¯ CONCAT_WS(separator, str1, str2,...) : ºÐ¸®±âÈ£¿Í ÇÔ²² »ç¿ë OCTET_LENGTH(str) : ¹®ÀÚ¿­ÀÇ ±æÀÌ CHAR_LENGTH(str) : ¹®ÀÚ¿­ÀÇ ±æÀÌ(multi-byte characters) CHARACTER_LENGTH(str) : ¹®ÀÚ¿­ÀÇ ±æÀÌ(multi-byte characters) BIT_LENGTH(str) : ¹®ÀÚ¿­ÀÇ ±æÀ̸¦ ºñÆ®·Î ³ªÅ¸³½´Ù. SUBSTRING(str FROM pos FOR len) : SUBSTRING()°ú ¿ëµµ °°À½ MID(str,pos,len) : SUBSTRING()°ú ¿ëµµ °°À½ SUBSTRING(str,pos) : strÀ» posºÎÅ͸¸ ³ªÅ¸³¿ SUBSTRING(str FROM pos) : SUBSTRING(str,pos)¿Í ¿ëµµ °°À½ SUBSTRING_INDEX(str,delim,count) : strÀ» delimÇ¥½ÃÀÇ countÀü±îÁö Ç¥½Ã(¿¹Á¦ÂüÁ¶) REPEAT(str,count) : strÀ» count ¸¸Å­ ¹Ýº¹µÈ ¹®ÀÚ¿­·Î º¯È¯ INSERT(str,pos,len,newstr) : str ¹®ÀÚ¿­À» posºÎÅÍ len ±æÀ̸¸Å« newstrÀ» »ðÀÔ ELT(N,str1,str2,str3,...) : ³ª¿­µÈ ¹®ÀÚÁß N¹ø° ³ª¿­µÈ ¹®ÀÚ¸¦ ¸®ÅÏ FIELD(str,str1,str2,str3,...) : strÀÌ ¸î¹ø°¿¡ ÀÖ´ÂÁö ³ªÅ¸³¿ FIND_IN_SET(str,strlist) : FIELD¿Í ºñ½Á (¿¹Á¦ ÂüÁ¶, ¹®ÀÚ¿­ ³ª¿­À» ÄÞ¸¶·Î) MAKE_SET(bits,str1,str2,...) : ¹®ÀÚ¿­ ¸®½ºÆ®¿¡ ´ëÇÑ ºñÆ®¿¬»êÀ¸·Î ¹®ÀÚ¿­ ¹Ýȯ°¡´É (¿¹Á¦ÂüÁ¶) EXPORT_SET(bits,on,off,[separator,[number_of_bits]]) : ¿¹Á¦ÂüÁ¶ LOAD_FILE(file_name) : ÆÄÀÏ¿¡¼­ ·Îµå (¿¹Á¦ ÂüÁ¶) QUOTE(str) : "Don't" -> 'Don\'t!' ¾ÕÀÇ ¸Å°³º¯¼ö 2°³(ÄÞ¸¶ ,)°¡ ºÐ¸®±âÈ£ mysql> SELECT CONCAT_WS(",","First name","Second name","Last Name"); -> 'First name,Second name,Last Name' mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2); -> 'www.mysql' mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2); -> 'mysql.com' mysql> SELECT TRIM(' bar '); -> 'bar' mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx'); -> 'barxxx' mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx'); -> 'bar' mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz'); -> 'barx' mysql> SELECT FIND_IN_SET('b','a,b,c,d'); -> 2 mysql> SELECT MAKE_SET(1,'a','b','c'); -> 'a' mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world'); -> 'hello,world' mysql> SELECT MAKE_SET(0,'a','b','c'); -> '' mysql> SELECT EXPORT_SET(5,'Y','N',',',4) -> Y,N,Y,N mysql> UPDATE tbl_name SET blob_column=LOAD_FILE("/tmp/picture") WHERE id=1; ---------- MSSQL 2OOO ---------- DIFFERENCE LEN : °ø¹éÀ» Á¦¿ÜÇÑ ¹®ÀÚ¼ö QUOTENAME REPLICATE STUFF UNICODE -------- Oracle8 -------- INITCAP : ´Ü¾îÀÇ Ã¹¹®ÀÚ¸¦ ´ë¹®ÀÚ·Î NLS_INITCAP : National Language INSTR (char1,char2 [,n[,m]]) : char1¿¡¼­ char2¹®ÀÚ°¡ °¡Àå ¸ÕÀú ³ªÅ¸³ª´Â ¼ýÀÚ ¸® ÅÏ( nÀÌ À½¼öÀÌ¸é ¿ÞÂʺÎÅÍ ¾ç¼öÀÌ¸é ¿À¸¥ÂÊ) INSTRB(char1,char2[,n[,m]]) : double-byte database character set TRANSLATE(char, from, to) : ¿¹Á¦ÂüÁ¶ LENGTHB : LENGTHÇÔ¼ö¿Í ºñ½ÁÇϳª ÇÑ ¹®ÀÚ¸¦ 2¹ÙÀÌÆ®·Î Ãë±Þ NLSSORT(char [, 'nlsparams']) : National Language ÇØ´ç, ¿¹Á¦ÂüÁ¶ --'NLS_SORT = sort' SELECT ename FROM emp WHERE NLSSORT (ename, 'NLS_SORT = German') > NLSSORT ('S', 'NLS_SORT = German') ORDER BY ename; ENAME ---------- SCOTT SMITH TURNER WARD SELECT TRANSLATE('2KRW229', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') "License" FROM DUAL; License -------- 9XXX999 SELECT TRANSLATE('2KRW229', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789') "Translate example" FROM DUAL; Translate example ----------------- 2229 -------- Oracle8i -------- TRIM ÇÔ¼ö Ãß°¡µÇ¾ú½¿. -------- Oracle9i -------- Ãß°¡»çÇ× ¾ø½¿  ³¯Â¥ ½Ã°£ °ü·Ã ÇÔ¼ö  -------- °øÅëÇÔ¼ö -------- ³¯Â¥ ÇÔ¼ö´Â °øÅëÇÔ¼ö°¡ ¾øÀ½. MySQLÀÇ °æ¿ì´Â ³¯Â¥¿¡ ´ëÇÑ ¼¼ºÐÈ­µÈ ÇÔ¼ö¸¦ Á¦°øÇÏ°í ÀÖ°í, MSSQL, OracleÀº ¿©·¯³¯ Â¥ Æ÷¸äÀ» ¾Æ±Ô¸ÕÆ®·Î »ç¿ëÇÏ¿© MySQL¿¡¼­ÀÇ ÇÔ¼öµéÀ» ´ëüÇÏ°í ÀÖ´Ù. ----- MySQL ----- DAYOFWEEK(date) : ¿äÀÏÀ» ¼ýÀÚ·Î ¸®ÅÏ(ÀÏ ~ Åä , 1 ~ 7) WEEKDAY(date) : ¿äÀÏÀ» ¼ýÀÚ·Î ¸®ÅÏ(¿ù ~ ÀÏ , 0 ~ 6) DAYOFMONTH(date) : ÀÏÀ» ¸®ÅÏ(1ÀÏ ~ 31ÀÏ , 1 ~ 31) DAYOFYEAR(date) : ùÇØ ½ÃÀÛÀ¸·Î ºÎÅÍ ¸îÀÏ( 1 ~ 366) MONTH(date) : ¿ù ¸®ÅÏ ( 1 ~ 12) DAYNAME(date) : ¿äÀÏ¸í ¸®ÅÏ MONTHNAME(date) : ¿ù¸í ¸®ÅÏ QUARTER(date) : ºÐ±â ¸®ÅÏ( 1 ~ 4) WEEK(date) WEEK(date,first) : 1¿ù ùÁÖºÎÅÍ 12¿ù ¸¶Áö¸·ÁÖ( 0 ~ 53) YEAR(date) : ³âµµ¼ö ¸®ÅÏ( 1000 ~ 9999) YEARWEEK(date) YEARWEEK(date,first) :³âµµ+ÁÖ ·Î ¸®ÅÏ HOUR(time) : ½Ã°£ ¸®ÅÏ MINUTE(time) : ºÐ ¸®ÅÏ SECOND(time) : ÃÊ ¸®ÅÏ PERIOD_ADD(P,N) : P³âµµ´Þ(YYMM or YYYYMM)¿¡ N´ÞÀ» ´õ ÇÑ´Ù. PERIOD_DIFF(P1,P2) : P1³âµµ´Þ(YYMM or YYYYMM)¿¡ P2³âµµ´ÞÀ» »«´Ù DATE_ADD(date,INTERVAL expr type) : ¿¹Á¦ÂüÁ¶ DATE_SUB(date,INTERVAL expr type) : ¿¹Á¦ÂüÁ¶ ADDDATE(date,INTERVAL expr type) : ¿¹Á¦ÂüÁ¶ SUBDATE(date,INTERVAL expr type) : ¿¹Á¦ÂüÁ¶ EXTRACT(type FROM date) : ¿øÇÏ´Â typeÀ¸·Î date¸¦ Ç¥Çö, Type ÂüÁ¶ TO_DAYS(date) : 0³âºÎÅÍ ¸î³âÀÌ Áö³µ´ÂÁö.. FROM_DAYS(N) : TO_DAYS()¹Ý´ë DATE_FORMAT(date,format) : Æ÷¸ä¿¡ ¸Â°Ô Format ÂüÁ¶ CURDATE() CURRENT_DATE : ÇöÀç ³¯Â¥(CURDATE() + 0 -> 19971215) CURTIME() CURRENT_TIME : ÇöÀç ½Ã°£ NOW() SYSDATE() CURRENT_TIMESTAMP : ÇöÀ糯¥½Ã°£ UNIX_TIMESTAMP() UNIX_TIMESTAMP(date) : GMT FROM_UNIXTIME(unix_timestamp,format) SEC_TO_TIME(seconds) : ÃÊ -> ½Ã°£ TIME_TO_SEC(time) : ½Ã°£-> ÃÊ < ³ëÆ® ÂüÁ¶> Note: in Version 4.0, WEEK(#,0) was changed to match the calendar in the USA. No te that if a week is the last week of the previous year, MySQL will return 0: mysql> SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0); -> 2000, 0 One could argue that MySQL should return 52 for the WEEK() function as the given date is actually the 52 second week of 1999. We decided to return 0 instead as we want the function to return 'the week number in the given year'. This makes t he usage of the WEEK() function reliable when combined with other functions that extracts a date part from a date. If you would prefer to know the correct year- week, then you should use the YEARWEEK() function instead: mysql> SELECT YEARWEEK('2000-01-01'); -> 199952 mysql> SELECT MID(YEARWEEK('2000-01-01'),5,2); -> 52 <³¯Â¥ ¿¹Á¦> mysql> SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND; -> 1998-01-01 00:00:00 mysql> SELECT INTERVAL 1 DAY + "1997-12-31"; -> 1998-01-01 mysql> SELECT "1998-01-01" - INTERVAL 1 SECOND; -> 1997-12-31 23:59:59 mysql> SELECT DATE_ADD("1997-12-31 23:59:59", -> INTERVAL 1 SECOND); -> 1998-01-01 00:00:00 mysql> SELECT DATE_ADD("1997-12-31 23:59:59", -> INTERVAL 1 DAY); -> 1998-01-01 23:59:59 mysql> SELECT DATE_ADD("1997-12-31 23:59:59", -> INTERVAL "1:1" MINUTE_SECOND); -> 1998-01-01 00:01:00 mysql> SELECT DATE_SUB("1998-01-01 00:00:00", -> INTERVAL "1 1:1:1" DAY_SECOND); -> 1997-12-30 22:58:59 mysql> SELECT DATE_ADD("1998-01-01 00:00:00", -> INTERVAL "-1 10" DAY_HOUR); -> 1997-12-30 14:00:00 mysql> SELECT DATE_SUB("1998-01-02", INTERVAL 31 DAY); -> 1997-12-02 ----- MSSQL ----- DATEADD ( datepart , number, date ) : ½Ã°£ ´õÇϱâ DATEDIFF ( datepart , startdate , enddate ) : ½Ã°£ »©±â DATENAME ( datepart , date ) : ƯÀý ³¯Â¥ÀÇ À̸§ ¹Ýȯ DATEPART ( datepart , date ) : ³¯Â¥ÀÇ Æ¯Á¤ ºÎºÐÀ» ¼ýÀÚ·Î ¹Ýȯ DAY ( date ) : ÀÏ ¹Ýȯ GETDATE() : ÇöÀ糯¥¿Í ½Ã°£ GETUTCDATE() : ÇöÀç UTC ½Ã°£(±¹Á¦ Ç¥ÁؽÃ) MONTH() : ¿ù ¹Ýȯ YEAR() : ³âµµ ¹Ýȯ ------- Oracle8 ------- ADD_MONTHS(d,n) : d³¯Â¥¿¡ n¿ùÀ» ´õÇÑ´Ù. LAST_DAY(d) : d³¯Â¥ÀÇ ¸¶Áö¸· ÀÏ ¸®ÅÏ MONTHS_BETWEEN(d1, d2) : d1³¯Â¥¿Í d2³¯Â¥ÀÇ ¿ùÀÇ Â÷ÀÌ NEW_TIME(d, z1, z2) : Âü°íÀÚ·á Âü°í NEXT_DAY(d, char) : d³¯Â¥ÀÇ ´ÙÀ½ char ¿äÀÏ(¿äÀϸí) ROUND(d[,fmt]) : d³¯Â¥ÀÇ ´ÙÀ½ ³¯Â¥Æ÷¸ä(fmt) SYSDATE : ÇöÀç ½Ã½ºÅÛÀÇ ³¯Â¥ TRUNC(d,[fmt]) : ³¯Â¥¿¡¼­ fmt¸¦ Â¥¸§( ¿¹: fmt°¡ YEAR¸é ´ÙÀ½³âµµ 1¿ù1ÀÏ) fmtÀ» »ý·«ÇÏ¸é ½Ã°£À» Â¥¸¥´Ù. -------- Oracle8i -------- º¯È­ ¾ø½¿ -------- Oracle9i -------- <Ãß°¡ÇÔ¼ö> CURRENT_DATE : SESSIONTIMEZONE¿¡ µû¸¥ ÇöÀç ½Ã°£ CURRENT_TIMESTAMP[(precision)] : SESSIONTIMEZONE¿¡ µû¸¥ ÇöÀç TIMESTAMP(Á¤¹Ðµµ) DBTIMEZONE : UTC time zone EXTRACT ( { { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND } | { TIMEZONE_HOUR | TIMEZONE_MINUTE } | { TIMEZONE_REGION | TIMEZONE_ABBR } } FROM { datetime_value_expression | interval_value_expression } ) : ³¯Â¥¸¦ Â¥¸§ (¿¹Á¦ ÂüÁ¶) FROM_TZ ( timestamp_value , time_zone_value ) : (¿¹Á¦ ÂüÁ¶) LOCALTIMESTAMP [( timestamp_precision )] : CURRENT_TIMESTAMP´Â ¿À¶óŬ¿¡¼­ ¼³Á¤µÈ °Í¿¡ µû¸¥ °ÍÀÌ°í LOCALTIMESTAMP´Â ½Ã½ºÅÛÀÇ TIMESTAMP NUMTODSINTERVAL ( n , 'char_expr' ) : n¿¡¼­ char_exprÀÇ °£°Ý 'DAY' 'HOUR' 'MINUTE' 'SECOND' NUMTOYMINTERVAL ( n , 'char_expr' ) : n¿¡¼­ char_exprÀÇ °£°Ý 'YEAR' 'MONTH' SESSIONTIMEZONE : ÇöÀç ¼¼¼ÇÀÇ Å¸ÀÓÁ¸ SYS_EXTRACT_UTC ( datetime_with_timezone ) : ½Ã½ºÅÛÀÇ ½Ã°£°ú UTC¿ÍÀÇ Â÷ÀÌ SYSTIMESTAMP : ½Ã½ºÅÛ Å¸ÀÓ½ºÅÛÇÁ TO_DSINTERVAL ( char ['nlsparam'] ) :¹®ÀÚÇü µ¥ÀÌÅ͸¦ ³¯Â¥ ~ ½Ã°£±îÁö °£°ÝÀ¸·Îº¯È¯ TO_TIMESTAMP ( char [ , fmt ['nlsparam']] ) : ¹®ÀÚÇüµ¥ÀÌÅ͸¦ ŸÀÓ½ºÅÛÇÁ·Îº¯È¯ TO_TIMESTAMP_TZ ( char [ , fmt ['nlsparam']] ) :TO_TIMESTAMP¿¡ ŸÀÓÁ¸±îÁö ¸ÂÃ纯ȯ TO_YMINTERVAL : ¹®ÀÚÇüµ¥ÀÌÅ͸¦ ³â~¿ù±îÁö º¯È¯ TZ_OFFSET ( { 'time_zone_name' | '{ + | - } hh : mi' | SESSIONTIMEZONE | DBTMEZONE } ) :ŸÀÌÁ¸ÀÇ ¿ÀÇÁ¼ÂÀ» ÀÌ¿ëÇؼ­ UTC¿ÍÀÇ Â÷¸¦ ±¸ÇÑ´Ù. SELECT EXTRACT(YEAR FROM DATE '1998-03-07') FROM DUAL; EXTRACT(YEARFROMDATE'1998-03-07') --------------------------------- 1998 SELECT FROM_TZ(TIMESTAMP '2000-03-28 08:00:00', '3:00') FROM DUAL; FROM_TZ(TIMESTAMP'2000-03-2808:00:00','3:00') --------------------------------------------------------------- 28-MAR-00 08.00.00 AM +03:00