Mysql Ver 4.1 에서 추가된 기능

1. 서브 쿼리

ex) SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);


2. ON DUPLICATE KEY UPDATE 문

ex) INSERT INTO table (a,b,c) VALUES (4,5,6) ON DUPLICATE KEY UPDATE c=9;


3. group by 구문에서 GROUP_CONCAT

GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])

ex)
SELECT student_name, GROUP_CONCAT(DISTINCT test_score ORDER BY test_score DESC SEPARATOR ' ') FROM student GROUP BY student_name;


4. group by 구문에서 WITH ROLLUP 사용 가능

5. CREATE TABLE new_tbl LIKE orig_tbl;

6. CREATE TABLE t (c CHAR(20) CHARACTER SET utf8 COLLATE utf8_bin);

7. SHOW WARNINGS

SHOW WARNINGS [LIMIT [offset,] row_count]
SHOW COUNT(*) WARNINGS
SELECT @@warning_count;

8. 기타 추가된 함수들

VARIANCE(expr)

BIT_XOR(expr)

OLD_PASSWORD(str)

CHARSET(str)

COERCIBILITY(str)

COLLATION(str)

COMPRESS(string_to_compress)

UNCOMPRESS(string_to_uncompress)

UNCOMPRESSED_LENGTH(compressed_string)

expr1 SOUNDS LIKE expr2

UNHEX(str)

SELECT 5 DIV 2;

N MOD M

CRC32(expr)

ADDTIME(expr,expr2)

CONVERT_TZ(dt,from_tz,to_tz)

DATE(expr)

DATEDIFF(expr,expr2)

DAY(date)

GET_FORMAT(DATE|TIME|DATETIME, 'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL')

LAST_DAY(date)

MAKEDATE(year,dayofyear)

MAKETIME(hour,minute,second)

MICROSECOND(expr)

STR_TO_DATE(str,format)

SUBTIME(expr,expr2)

TIME(expr)

TIMEDIFF(expr,expr2)

TIMESTAMP(expr,expr2)

UTC_DATE()

UTC_TIME()

UTC_TIMESTAMP()

WEEKOFYEAR(date)

DEFAULT(col_name)

IS_USED_LOCK(str)

UUID().