MySQL常规函数详解汇总.doc

MySQL a) CASE WHEN THEN CASE value WHEN compare-value THEN result WHEN compare-value THEN result ELSE result END CASE WHEN condition THEN result WHENcondition THEN result ELSE result END value =compare-value ELSE ELSE NULLb) IF IF(expr1,expr2,expr3) expr1 TRUE (expr1 0 and expr1 NULL) IF() expr2 ; expr3 IF() c) IFNULL IFNULL(expr1,expr2) expr1 NULL IFNULL() expr1 ; expr2 IFNULL() a) ascii(str) str str 0 str NULL NULL ASCII() 0 255 b) BIN(N) N N longlong (BIGINT) CONV(N ,10,2) N NULL NULL c) CHAR(N ,. USING charset ) CHAR() N NULL d) CHAR_LENGTH(str ) str , LENGTH() 10, CHAR_LENGTH() 5e) CHARACTER_LENGTH(str ) CHARACTER_LENGTH() CHAR_LENGTH() f) COMPRESS(string_to_compress ) COMPRESS( MySQL zlib NULL UNCOMPRESS() ) g) CONCAT(str1 ,str2 ,.) NULL NULL cast, SELECT CONCAT(CAST(int_col AS CHAR), char_col)h) CONCAT_WS(separator ,str1 ,str2 ,.) CONCAT_WS() CONCAT With Separator CONCAT() NULL NULL NULL i) CONV(N from_base, to_base) N from_base to_base NULL NULL N 2 36 If to_base N N CONV() 64 j) ELT(N ,str1 ,str2 ,str3 ,.) N = 1 str1 N = 2 str2 N 1 NULL ELT() FIELD() k) EXPORT_SET(bits ,on ,off ,separator ,number_of_bits ) bits on off bits ( ) ( ,) number_of_bits ( 64) l) FIELD(str, str1, str2, str3, .) str1 , str2 , str3 , str str 0 FIELD() str NULL 0 NULL FIELD() ELT() m) FIND_IN_SET(str, strlist) str N strlist 1 N , type SET FIND_IN_SET() str strlist strlist 0 NULL NULL (,) n) FORMAT(X ,D ) number X #,#,#.#, D , o) HEX(N_or_S )N_OR_S N N longlong (BIGINT) CONV(N,10,16) N_OR_S N_OR_S N_OR_S p) INSTR(str,substr) str LOCATE() q) LCASE(str)LCASE() LOWER() r) LEFT(str,len)str len s) LENGTH(str ) str 5 2 LENGTH() 10, CHAR_LENGTH() 5 t) LOAD_FILE(file_name) , FILE max_allowed_packet NULLu) LOCATE(substr ,str ) , LOCATE(substr ,str ,pos ) str substr str substr , pos substr str 0 v) LOWER(str ) str w) LPAD(str ,len ,padstr ) str , padstr len str len , len x) LTRIM(str ) str y) MAKE_SET(bits ,str1 ,str2 ,.) ( , ) bits str1 0, str2 1, str1 , str2 , . NULL z) MID(str ,pos ,len ) MID(str ,pos ,len ) SUBSTRING(str ,pos ,len ) aa) OCT(N ) N N longlong (BIGINT) CONV(N,10,8) N NULL NULL bb) OCTET_LENGTH(str ) OCTET_LENGTH() LENGTH() cc) ORD(str )str :(1st byte code)+ (2nd byte code 256)+ (3rd byte code 2562) . ORD() ASCII() dd) POSITION(substr IN str )POSITION(substr IN str ) LOCATE(substr ,str ) ee) QUOTE(str )SQL () () ASCII NUL Control-Z NULL, NULL ff) REPEAT(str ,count )str str count count = 0, str count NULL NULL gg) REPLACE(str ,from_str ,to_str )str to_str from_str hh) REVERSE(str ) str ii) RIGHT(str ,len )str len jj) RPAD(str ,len ,padstr )str , padstr len str len , len kk) RTRIM(str ) str ll) SOUNDEX(str )str soundex soundex soundex 4 SOUNDEX() SUBSTRING() soundex str A-Z mm) expr1 SOUNDS LIKE expr2 SOUNDEX(expr1 ) = SOUNDEX(expr2 ) nn) SPACE(N )N oo) SUBSTRING(str ,pos ) , SUBSTRING(str FROM pos ) SUBSTRING(str ,pos ,len ) , SUBSTRING(str FROM pos FOR len )len str pos len str len pos FROM SQL pos pos pos pp) SUBSTRING_INDEX(str ,delim ,count ) delim count str count , ( ) count qq) TRIM(BOTH | LEADING | TRAILING remstr FROM str ) TRIM(remstr FROM str ) str remstr / BOTH LEADIN TRAILING , BOTH remstr rr) UCASE(str)UCASE() UPPER() ss) UNCOMPRESS(string_to_uncompress )COMPRESS() NULL MySQL zlib , NULLtt) UNCOMPRESSED_LENGTH(compressed_string ) uu) UNHEX(str) HEX(str ) vv) UPPER(str ) str a) ABS(X) X b) ACOS(X ) X , , X X -1 1 NULL c) ASIN X X X X X -1 1 NULL d) ATAN(X ) X X e) ATAN(Y ,X ) , ATAN2(Y ,X ) X Y Y X , f) CEILING(X ) CEIL(X ) X g) COS(X ) X X h) COT(X ) X i) CRC32(expr ) 32 NULL NULL j) DEGREES(X ) X , k) EXP(X ) e X ( ) l) FLOOR(X ) X m) FORMAT(X ,D ) X #,#,#.# , D D n) LN(X ) X , , X e o) LOG(X ) LOG(B ,X ) X p) LOG2(X ) X 2 q) LOG10(X ) X 10 r) MOD(N ,M ) , N % M N MOD M N M s) PI() ? (pi) 7 , MySQL t) POW(X ,Y ) , POWER(X ,Y ) X Y u) RADIANS(X ) X , ( ? 180 v) RAND() RAND(N ) v 0 1 ( , 0 v 1.0) N w) ROUND(X ) ROUND(X ,D ) X , X D D X D D x) SIGN(X ) -1 0 1 X y) SIN(X ) X X z) SQRT(X ) X aa) TAN(X ) X X bb) TRUNCATE(X ,D ) D X D 0, D , ( ) X D a) ADDDATE(date ,INTERVAL expr type ) ADDDATE(expr ,days ) INTERVAL ADDDATE() DATE_ADD() SUBDATE() DATE_SUB() INTERVAL DATE_ADD() b) ADDTIME(expr ,expr2 ) ADDTIME() expr2 expr expr expr2 c) CONVERT_TZ(dt ,from_tz ,to_tz ) CONVERT_TZ() dt from_tz to_tz NULLd) CURDATE() YYYY-MM-DD YYYYMMDD e) CURRENT_DATE CURRENT_DATE() CURRENT_DATE CURRENT_DATE() .f) CURTIME() HH:MM:SS HHMMSS g) CURRENT_TIME, CURRENT_TIME() CURRENT_TIME CURRENT_TIME() CURTIME() h) CURRENT_TIMESTAMP, CURRENT_TIMESTAMP() CURRENT_TIMESTAMP CURRENT_TIMESTAMP() NOW() i) DATE(expr ) expr j) DATEDIFF(expr ,expr2 ) DATEDIFF() expr expr2 Expr expr2 date-and-time k) DATE_ADD(date ,INTERVAL expr type ) DATE_SUB(date ,INTERVAL expr type ) date DATETIME DATE expr Expr ; - type l) DATE_FORMAT(date ,format ) format date m) DAY(date ) DAY() DAYOFMONTH() n) DAYNAME(date ) date o) DAYOFMONTH(date ) date 1 31p) DAYOFWEEK(date ) date (1 = , 2 = , ., 7 = ) ODBC q) DAYOFYEAR(date )date 1 366 r) EXTRACT(type FROM date ) EXTRACT() DATE_ADD() DATE_SUB() , s) FROM_DAYS(N ) N , DATE t) FROM_UNIXTIME(unix_timestamp )ROM_UNIXTIME(unix_timestamp ,format )YYYY-MM-DD HH:MM:SS YYYYMMDDHHMMSS unix_timestamp format format format DATE_FORMAT() u) GET_FORMAT(DATE|TIME|DATETIME, EUR|USA|JIS|ISO|INTERNAL) DATE_FORMAT() STR_TO_DATE() v) HOUR(time ) time 0 23w) LAST_DAY(date ) NULL x) LOCALTIME, LOCALTIME() LOCALTIME LOCALTIME() NOW() y) LOCALTIMESTAMP, LOCALTIMESTAMP() LOCALTIMESTAMP LOCALTIMESTAMP() NOW() z) MAKEDATE(year ,dayofyear ) dayofyear 0 NULL aa) MAKETIME(hour ,minute ,second ) hour minute second bb) CROSECOND(expr ) expr 0 999999 cc)