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

位元運算
&
用法：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 的函數很相像。正因如此，常有初學者會將兩者混淆，分不清何時該用哪一種。我們來看看以下這個例子：

$SQL1 = "SELECT realname FROM friend WHERE SUBSTRING(realname, 1, 2) = '陳信' ";
$SQL2 = "SELECT realname FROM friend WHERE realname = '" . SUBSTR('陳信宏', 0, 4) . "' ";
?>

　這兩個 SQL 敘述乍看之下似乎頗為相似，它們都使用了「取部份字串」的功能，但是 ......

1. 前者的 SUBSTRING( ) 是 MySQL 函數，而後者的 SUBSTR( ) 則是 PHP 的函數。
2. 前者將找出 realname 前 2 字為「陳信」的結果，而後者會找到 realname 恰好是「陳信」的結果來。
3. 對字串起始位置的編號，在 MySQL 裡是從 1 開始，在 PHP 當中則是 0。
4. 對中文字串的長度計算，前者是 1 個中文字算 1 個單位長度，後者則是當 2 個單位長度來計算。

　在 PHP 與 MySQL 之間，像這種「相似，但用法與結果卻不同」的函數不少，日後請小心使用

出處