| ｜初探｜欄位型態｜存取 MySQL｜PHP 的 MySQL Functions｜進階｜ | |
|---|---|
| MySQL 簡介 |
|
|
運算元與函數 | |
| MySQL 允許我們在 SQL 敘述裡頭插入運算元或是函數，這使得查詢所得不僅僅是原始資料，更可以是資料間彼此交互作用的結果。以下針對 MySQL 中較為常用的運算元與函數做簡單的介紹，其餘未被列出的部份，請自行參閱線上文件。 | |
算術運算 | |
| + | |
| 用法：SELECT 2 + 3 說明：加法運算 | |
| - | |
| 用法：SELECT 5 - 4 說明：減法運算 | |
| * | |
| 用法：SELECT 4 * 6 說明：乘法運算 | |
| / | |
| 用法：SELECT 12 / 6 說明：除法運算 | |
| % 或 MOD(N,M) | |
| 用法：SELECT 12 % 5 說明：模數運算（取餘數） | |
| ABS(X) | |
| 用法：SELECT ABS(-32) | |
| 說明：求 X 的絕對值 | |
| FLOOR(X) | |
| 用法：SELECT FLOOR(1.23) | |
| 說明：求不大於 X 的最大整數（有「無條件捨去」的效果） | |
| CEILING(X) | |
| 用法：SELECT CEILING(1.23) | |
| 說明：求不小於 X 的最小整數（有「無條件進入」的效果） | |
| ROUND(X) | |
| 用法：SELECT ROUND(1.58) | |
| 說明：求 X 四捨五入到個位的值 | |
|
比較運算 | |
| = | |
| 用法：SELECT 2 = 2 說明：等於 | |
| <> 或 != | |
| 用法：SELECT '.01' <> '0.01' 說明：不等於 | |
| <= | |
| 用法：SELECT 0.1 <= 2 說明：小於等於 | |
| < | |
| 用法：SELECT 2 < 2 說明：小於 | |
| >= | |
| 用法：SELECT 2 >= 2 說明：大於等於 | |
| > | |
| 用法：SELECT 2 > 2 說明：大於 | |
| <=> | |
| 用法：SELECT NULL <=> NULL | |
| 說明：NULL safe equal，功能與 = 相似，只要兩個值相等就傳回 1，即使是 NULL | |
| IS NULL | |
| 用法：SELECT 1 IS NULL 說明：判斷值是否為 NULL | |
| IS NOT NULL | |
| 用法：SELECT 1 IS NOT NULL 說明：判斷值是否不為 NULL | |
| expr BETWEEN min AND max | |
| 用法：SELECT 1 BETWEEN 2 AND 3 說明：判斷值是否介於 min 與 max 之間 | |
| expr NOT BETWEEN min AND max | |
| 用法：SELECT 1 NOT BETWEEN 2 AND 3 說明：判斷值是否不介於 min 與 max 之間 | |
| expr IN (value, ...) | |
| 用法：SELECT 2 IN (0, 3, 5, 'wefwf') 說明：判斷是否符合列舉項目的範圍 | |
| expr NOT IN (value, ...) | |
| 用法：SELECT 2 NOT IN (0, 3, 5, 'wefwf') 說明：判斷是否不在列舉項目的範圍 | |
| ISNULL(expr) | |
| 用法：SELECT ISNULL(1+1) 說明：判斷 expr 中是不是 NULL 值 | |
| COALESCE(list) | |
| 用法：SELECT COALESCE(NULL,1) 說明：在 list 的所有項目中，找出第一個不是 NULL 的資料 | |
| INTERVAL(N,N1,N2,N3,...) | |
| 用法：SELECT INTERVAL(23, 1, 15, 17, 30, 44) | |
| 說明：若 n<n1 就傳回 0，若 n<n2 就傳回 1，餘依此類推。當 n 為 NULL 時，傳回 -1。所用的值（n1, n2, n3, ...）必須嚴格遞增，意即 n1 < n2 < n3 < ...，否則在快速的 binary search 運作下，會傳回不可預期的錯誤結果。 | |
|
位元運算 | |
| & | |
| 用法：SELECT 29 & 15 說明：交集運算（AND） | |
| | | |
| 用法：SELECT 29 | 15 說明：聯集運算（OR） | |
| ^ | |
| 用法：SELECT 1 ^ 1 說明：互斥運算（XOR） | |
| ~ | |
| 用法：SELECT 5 & ~1 說明：Invert all bits | |
| >> | |
| 用法：SELECT 4 >> 2 說明：向右移位 | |
| << | |
| 用法：SELECT 1 << 2 說明：向左移位 | |
|
邏輯運算 | |
| NOT 或 ! | |
| 用法：SELECT NOT 1 說明：否 | |
| OR 或 || | |
| 用法：SELECT 1 || 0 說明：或 | |
| AND 或 && | |
| 用法：SELECT 1 && 1 說明：且 | |
| XOR | |
| 用法：SELECT 1 XOR 1 說明：互斥，「a XOR b」等於「a AND (NOT b)」或是「(NOT a) AND b」 | |
|
字串運算 | |
| LIKE 與萬用字元（ % 、 _ ） | |
| 「%」是個萬用字元，它可以用來代表零或多個字元；「_」也是個萬用字元，但它只能用來代表一個字元。將它們與「LIKE」搭配使用，可以用來對字串內容進行模糊比對，這與「=」的精確比對是不同的。 | |
| 用法：SELECT realname LIKE '陳%' FROM student | |
| 說明：找出姓「陳」的所有學生 | |
| 用法：SELECT num LIKE '_568' FROM product | |
| 說明：找出編號（共 4 碼）末 3 碼為「568」的所有產品 | |
| ASCII(str) | |
| 用法：SELECT ASCII('dx') | |
| 說明：傳回 str 字串中最左邊字元的 ASCII 碼 | |
| CONCAT(s1, s2, .....) | |
| 用法：SELECT CONCAT('My', 'S', 'QL') | |
| 說明：將字串 s1, s2, ... 全部連接成一個字串 | |
|
LENGTH(str)
OCTET_LENGTH(str) CHAR_LENGTH(str) CHARACTER_LENGTH(str) | |
|
用法：SELECT LENGTH('陳信宏123') SELECT OCTET_LENGTH('陳信宏123') SELECT CHAR_LENGTH('陳信宏123') SELECT CHARACTER_LENGTH('陳信宏123') | |
| 說明：傳回 str 字串的長度。前兩者傳回 9，後兩者傳回 6（因為每個中文字均算 1） | |
| LOCATE(s1,s2) INSTR(s2, s1) | |
| 用法：SELECT INSTR('foobarbar', 'bar') | |
| 說明：傳回 s1 字串在 s2 字串第一次出現的位置是第幾個字 | |
| LEFT(str, len) | |
| 用法：SELECT LEFT('foobarbar', 5) | |
| 說明：從字串 str 的左邊取 len 長度的字串傳回 | |
| RIGHT(str, len) | |
| 用法：SELECT RIGHT('foobarbar', 5) | |
| 說明：從字串 str 的右邊取 len 長度的字串傳回 | |
| SUBSTRING(str, pos, len) MID(str, pos, len) | |
| 用法：SELECT SUBSTRING('Quadratically', 5, 6) | |
| 說明：從字串 str 的第 pos 位置開始，取出 len 長度的字串傳回 | |
| LTRIM(str) | |
| 用法：SELECT LTRIM(' barbar') | |
| 說明：移除字串 str 開頭的空白 | |
| RTRIM(str) | |
| 用法：SELECT RTRIM('barbar ') | |
| 說明：移除字串 str 結尾的空白 | |
| TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str) | |
|
用法：SELECT TRIM(' bar ') SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx') SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx') SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz') | |
| 說明：移除 str 字串中與 remstr 相同的字串，BOTH 是開頭與結尾都移除，LEADING 是移除開頭，TRAILING 是移除結尾，預設是 BOTH。 | |
| REPLACE(str, from_str, to_str) | |
| 用法：SELECT REPLACE('www.mysql.com', 'w', 'Ww') | |
| 說明：將 str 字串中，所有的 from_str 都替換成 to_str | |
| LCASE(str) LOWER(str) | |
| 用法：SELECT LCASE('QUADRATICALLY') | |
| 說明：將 str 中的字串轉成小寫 | |
| UCASE(str) UPPER(str) | |
| 用法：SELECT UCASE('Hej') | |
| 說明：將 str 中的字串轉成大寫 | |
|
日期與時間運算 | |
| DAYOFWEEK(date) | |
| 用法：SELECT DAYOFWEEK('1998-02-03') | |
| 說明：傳回日期 date 的星期索引（1：週日，2：週一，... 7：週六） | |
| NOW( ) | |
| 用法：SELECT NOW() | |
| 說明：以 YYYY-MM-DD HH:MM:SS 或 YYYYMMDDHHMMSS 的格式傳回目前的時間 | |
| UNIX_TIMESTAMP( ) 或 UNIX_TIMESTAMP(date) | |
| 用法： | |
| 說明：若指定 date，則傳回當時的 Unix timestamp；否則，傳回目前的 Unix timestamp。 | |
| 補充：Unix timestamp 是一個正整數，代表從「1970-01-01 00:00:00 GMT」起的秒數 | |
|
與 GROUP BY 搭配的函數 | |
| COUNT(expr) | |
|
用法：SELECT city, COUNT(*) FROM friend GROUP BY city SELECT COUNT(*) FROM student | |
| 說明：用於計算資料列筆數，也可以不必搭配 GROUP BY 使用 | |
| AVG(expr) | |
| 用法：SELECT city, AVG(age) FROM friend GROUP BY city | |
| 說明：用於計算群組的平均值 | |
| MIN(expr) 與 MAX(expr) | |
| 用法：SELECT city, MIN(age), MAX(age) FROM friend GROUP BY city | |
| 說明：用於計算群組中的最小值與最大值 | |
| SUM(expr) | |
| 用法：SELECT city, SUM(member) FROM friend GROUP BY city | |
| 說明：用於計算群組的總和 | |
 經驗談
以上所列的均是 MySQL 的函數，有些看來與 PHP 的函數很相像。正因如此，常有初學者會將兩者混淆，分不清何時該用哪一種。我們來看看以下這個例子：
<? 這兩個 SQL 敘述乍看之下似乎頗為相似，它們都使用了「取部份字串」的功能，但是 ......
在 PHP 與 MySQL 之間，像這種「相似，但用法與結果卻不同」的函數不少，日後請小心使用。 | |
|
| |
| ｜初探｜欄位型態｜存取 MySQL｜PHP 的 MySQL Functions｜進階｜ | |