摘要:mySQL常用的語法
小弟最近接了一個專案，使用mySQL+VS2005 發現mySQL語法跟標準T-SQL寫法，有些許的不同，以下是我常用到的語法，
怕我下次要用會忘記，所以先記起來，年紀大記憶體只剩 1024 K
各位看倌，如果有更好的寫法，可以告訴我，我會一直更新下去，謝謝。
字串函式：字串相加
官方說明：CONCAT(str1,str2,...)
select concat('A','mySQL','bbb','B') addStr; --回傳 'AmySQLbbbB'
select concat('A','B','C','D','E','F','...','Z') addStr;-- 回傳'ABCDEF...Z'自動補不足長度的預設字(不知道該如何解釋，看下去就知道了)
官方說明：
補左邊：LPAD(str,len,padstr)
補右邊：RPAD(str,len,padstr)
select lpad('A',5,'?') autoLStr;--回傳????A
select Rpad('A',5,'?') autoRStr;--回傳A????最麻煩的NULL判斷，搭配字串相加的涵式，來看結果
select ifnull(null,'ABC') mNull;--回傳 'ABC'
select ifnull('ABC','') mNull;回傳 --'ABC'
--如果沒有判斷NULL字串相加的結果
select concat('ABC',null,'DEF','...','Z') mNull;--回傳NULL
select concat('ABC',ifnull(null,'myNull'),'DEF','...','Z') mNull;--回傳ABCCmyNullDEF...Z字串反轉：
其它的字串用法跟標準寫法差不多
譬如：SUBSTRING,LTRIM,…基本的字串語法
日期涵式：
因為小弟只有用格式化跟系統日期，其於的日期涵式都大同小異
官方日期格式化的說明：
| Specifier | Description |
%a |
Abbreviated weekday name (Sun..Sat) |
%b |
Abbreviated month name (Jan..Dec) |
%c |
Month, numeric (0..12) |
%D |
Day of the month with English suffix (0th, 1st, 2nd, 3rd, …) |
%d |
Day of the month, numeric (00..31) |
%e |
Day of the month, numeric (0..31) |
%f |
Microseconds (000000..999999) |
%H |
Hour (00..23) |
%h |
Hour (01..12) |
%I |
Hour (01..12) |
%i |
Minutes, numeric (00..59) |
%j |
Day of year (001..366) |
%k |
Hour (0..23) |
%l |
Hour (1..12) |
%M |
Month name (January..December) |
%m |
Month, numeric (00..12) |
%p |
AM or PM |
%r |
Time, 12-hour (hh:mm:ss followed by AM or PM) |
%S |
Seconds (00..59) |
%s |
Seconds (00..59) |
%T |
Time, 24-hour (hh:mm:ss) |
%U |
Week (00..53), where Sunday is the first day of the week |
%u |
Week (00..53), where Monday is the first day of the week |
%V |
Week (01..53), where Sunday is the first day of the week; used with %X |
%v |
Week (01..53), where Monday is the first day of the week; used with %x |
%W |
Weekday name (Sunday..Saturday) |
%w |
Day of the week (0=Sunday..6=Saturday) |
%X |
Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V |
%x |
Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v |
%Y |
Year, numeric, four digits |
%y |
Year, numeric (two digits) |
%% |
A literal ‘%’ character |
% |
x, for any ‘x’ not listed above |
小弟會用到範例：
SELECT current_date nowDate;--取得目前日期;
SELECT current_time nowTime;--取得目前時間;
SELECT DATE_FORMAT(sysdate(), '%Y/%m/%d');--'2008/09/16' 類似.net 寫法的Now.ToString("yyyy/MM/dd")
SELECT TIME_FORMAT(sysdate(), '%H:%i:%s');--回傳現在時分秒 類似 .net寫法的 Now.ToString("HH:mm:ss")累了，先寫到這邊，改天再寫其它的。