???????????mysql?е?????????????????????????????????????ο???
??????????????
????ABS(x) ????x???
????BIN(x) ????x????????OCT?????????HEX????????????
????CEILING(x) ???????x??С?????
????EXP(x) ?????e???????????????x?η?
????FLOOR(x) ????С??x????????
????GREATEST(x1??x2??...??xn)????????д???
????LEAST(x1??x2??...??xn) ?????????С???
????LN(x) ????x?????????
????LOG(x??y)????x????y???????
????MOD(x??y) ????x/y???????????
????PI()????pi???????????
????RAND()??????????????????????????????????(????)?RAND()??????????????????????????
????ROUND(x??y)???????x?????????????yλС?????
????SIGN(x) ???????????x???????
????SQRT(x) ????????????????
????TRUNCATE(x??y) ????????x????yλС??????
??????????????(??????GROUP BY????SELECT?????)
????AVG(col)????????е?????
????COUNT(col)??????????з?NULL??????
????MIN(col)????????е?С?
????MAX(col)????????е???
????SUM(col)????????е?????????
????GROUP_CONCAT(col) ??????????????????????????????
?????????????????
????ASCII(char)?????????ASCII???
????BIT_LENGTH(str)?????????????????
????CONCAT(s1??s2...??sn)??s1??s2...??sn??????????
????CONCAT_WS(sep??s1??s2...??sn)??s1??s2...??sn????????????????sep??????
????INSERT(str??x??y??instr) ???????str???xλ??????y?????????????滻??????instr????????
????FIND_IN_SET(str??list)???????????list?б?????????str??????str??list?е?λ??
????LCASE(str)??LOWER(str) ??????????str?????????????Сд?????
????LEFT(str??x)?????????str??????x?????
????LENGTH(s)?????????str?е??????
????LTRIM(str) ???????str???е???????
????POSITION(substr??str) ???????substr???????str?е???γ????λ??
????QUOTE(str) ?÷?б?????str?е??????
????REPEAT(str??srchstr??rplcstr)?????????str???x?ε???
????REVERSE(str) ???????????str????
????RIGHT(str??x) ?????????str??????x?????
????RTRIM(str) ?????????strβ??????
????STRCMP(s1??s2)????????s1??s2
????TRIM(str)?????????????β???????п??
????UCASE(str)??UPPER(str) ??????????str???????????????д?????
??????????????亯??
????CURDATE()??CURRENT_DATE() ????????????
????CURTIME()??CURRENT_TIME() ???????????
????DATE_ADD(date??INTERVAL int keyword)????????date?????????int????(int????????????и????)???磺SELECTDATE_ADD(CURRENT_DATE??INTERVAL 6 MONTH);
????DATE_FORMAT(date??fmt) ?????????fmt????????????date?
????DATE_SUB(date??INTERVAL int keyword)????????date?????????int????(int????????????и????)???磺SELECTDATE_SUB(CURRENT_DATE??INTERVAL 6 MONTH);
????DAYOFWEEK(date) ????date?????????????е?????(1~7)
????DAYOFMONTH(date) ????date??????μ?????(1~31)
????DAYOFYEAR(date) ????date??????????(1~366)
????DAYNAME(date) ????date???????????磺SELECT DAYNAME(CURRENT_DATE);
????FROM_UNIXTIME(ts??fmt) ?????????fmt??????????UNIX????ts
????HOUR(time) ????time??С??(0~23)
????MINUTE(time) ????time??????(0~59)
????MONTH(date) ????date???·??(1~12)
????MONTHNAME(date) ????date???·??????磺SELECT MONTHNAME(CURRENT_DATE);
????NOW() ????????????????
????QUARTER(date) ????date??????е????(1~4)????SELECT QUARTER(CURRENT_DATE);
????WEEK(date) ????????date?????е????(0~53)
????YEAR(date) ????????date?????(1000~9999)
?????Щ?????
????????????:
???????????????SELECT FROM_UNIXTIME(UNIX_TIMESTAMP());
????SELECT EXTRACT(YEAR_MONTH FROM CURRENT_DATE);
????SELECT EXTRACT(DAY_SECOND FROM CURRENT_DATE);
????SELECT EXTRACT(HOUR_MINUTE FROM CURRENT_DATE);
???????????????????????(????)??SELECT PERIOD_DIFF(200302??199802);
??????Mysql?м???????
????SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birthday))??'%Y')+0 AS age FROM employee;
?????????????Brithday??δ?????????????????????0??
?????????SQL???????????????????Birthday??δ?????????????????????
????SELECT DATE_FORMAT(NOW()?? '%Y') - DATE_FORMAT(birthday?? '%Y') -(DATE_FORMAT(NOW()?? '00-%m-%d') <DATE_FORMAT(birthday?? '00-%m-%d')) AS age from employee
?????塢???????
????AES_ENCRYPT(str??key) ?????????key???????str????????????????????????????AES_ENCRYPT????????????????????????BLOB????洢
????AES_DECRYPT(str??key) ?????????key???????str??????????????????????
????DECODE(str??key) ???key??????????????????str
????ENCRYPT(str??salt) ???UNIXcrypt()???????ù????salt(???????Ω????????????????????????)?????????str
????ENCODE(str??key) ???key???????????????str??????ENCODE()??????????????????????????BLOB????洢
????MD5() ?????????str??MD5У???
????PASSWORD(str) ?????????str?????汾?????????????????????????UNIX????????????ò????????
????SHA() ?????????str?????????(SHA)У???
?????????
????????????:
????SELECT ENCRYPT('root'??'salt');
????SELECT ENCODE('xufeng'??'key');
????SELECT DECODE(ENCODE('xufeng'??'key')??'key');#???????????
????SELECT AES_ENCRYPT('root'??'key');
????SELECT AES_DECRYPT(AES_ENCRYPT('root'??'key')??'key');
????SELECT MD5('123456');
????SELECT SHA('123456');
??????????????????
????MySQL??4?????????????????????????????Щ???????????SQL??????????????????????Щ??ó???????????????????????
????MySQL????????????
????CASE WHEN[test1] THEN [result1]...ELSE [default] END???testN???棬????resultN????????default
????CASE [test] WHEN[val1] THEN [result]...ELSE [default]END ???test??valN????????resultN????????default
????IF(test??t??f) ???test???棬????t????????f
????IFNULL(arg1??arg2) ???arg1??????????arg1????????arg2
????NULLIF(arg1??arg2) ???arg1=arg2????NULL????????arg1
??????Щ????????????IFNULL()??????????????????????????????????ж??????????????????NULL?????????????????????????????????NULL??????????????????
?????磺SELECT IFNULL(1??2)?? IFNULL(NULL??10)??IFNULL(4*NULL??'false');
????NULLIF()?????????????????????????????????????????NULL?????????????????????????
?????磺SELECT NULLIF(1??1)??NULLIF('A'??'B')??NULLIF(2+3??4+1);
???????????????????IF()?????????MySQL??IF()??????????????????????????????????????????????????????????ж????????????????棬IF()???????????????????????IF()?????????????????
?????磺SELECTIF(1<10??2??3)??IF(56>100??'true'??'false');
????IF()??????????????????????????á????????????????У??????????????????????л??????????????????????￡?MySQL????CASE??????????PHP??Perl?????switch-case?????????????
????CASE??????????Щ?????????????????
????????????:
????CASE [expression to be evaluated]
????WHEN [val 1] THEN [result 1]
????WHEN [val 2] THEN [result 2]
????WHEN [val 3] THEN [result 3]
????......
????WHEN [val n] THEN [result n]
????ELSE [default result]
????END