MySQL筆記之運算子使用詳解

標籤：

運算子包括四類，分別是：算數運算子、比較子、邏輯運算子和位元運算符 

Mysql可以通過運算子來對錶中資料進行運算，比如通過出生日期求年齡等

運算子包括四類，分別是：算數運算子、比較子、邏輯運算子和位元運算符

算數運算子
加、減、乘運算

代碼如下:mysql> select a,a+5,a*2 from t1; +------+------+------+ | a    | a+5  | a*2  | +------+------+------+ |   24 |   29 |   48 | +------+------+------+  row in set (0.00 sec)    這裡的原值為24,後面也可以使用混合運算，只需要注意優先順序即可 

除法和模數運算

代碼如下:mysql> select a,a/3,a div 3,a%5,mod(a,5) from t1; +------+--------+---------+------+----------+ | a    | a/3    | a div 3 | a%5  | mod(a,5) | +------+--------+---------+------+----------+ |   24 | 8.0000 |       8 |    4 |        4 | +------+--------+---------+------+----------+  row in set (0.00 sec)
此處 / 和 div 代表整除，% 和 mod 代表模數

要注意的是，如果被除數為0,那麼計算結果是NULL

 

比較子 數值比較

代碼如下:mysql> select a,a=24,a<12,a>40,a>=24,a<=24,a!=24,a<>24,a<=>24 from t1; +------+------+------+------+-------+-------+-------+-------+--------+ | a    | a=24 | a<12 | a>40 | a>=24 | a<=24 | a!=24 | a<>24 | a<=>24 | +------+------+------+------+-------+-------+-------+-------+--------+ |   24 |    1 |    0 |    0 |     1 |     1 |     0 |     0 |      1 | +------+------+------+------+-------+-------+-------+-------+--------+  row in set (0.00 sec)

這裡的1代表真，0代表假，需要說明的是<>和<=>

<>代表不等於，等同於!=

<=>代表等於，等同於=

此外，等於和不等於不僅可以比較數值，還能比較字串

 

字串比較

代碼如下:mysql> select a,a=‘24‘,‘ha‘<>‘ha‘,‘xa‘=‘xa‘,‘b‘!=‘b‘ from t1; +------+--------+------------+-----------+----------+ | a    | a=‘24‘ | ‘ha‘<>‘ha‘ | ‘xa‘=‘xa‘ | ‘b‘!=‘b‘ | +------+--------+------------+-----------+----------+ |   24 |      1 |          0 |         1 |        0 | +------+--------+------------+-----------+----------+  row in set (0.00 sec) 

is null 和is not null

代碼如下:mysql> select a,a is null, a is not null from t1; +------+-----------+---------------+ | a    | a is null | a is not null | +------+-----------+---------------+ |   24 |         0 |             1 | +------+-----------+---------------+  row in set (0.00 sec)
這裡可以判斷是否為空白，NULL也可以跟NULL比較 

between and和not between and

代碼如下:mysql> select a,a between 15 and 30,a not between 15 and 30 from t1; +------+---------------------+-------------------------+ | a    | a between 15 and 30 | a not between 15 and 30 | +------+---------------------+-------------------------+ |   24 |                   1 |                       0 | +------+---------------------+-------------------------+  row in set (0.00 sec)
between and 和not between and可以判斷數值是否在某一區間內  in
mysql> select a,a in(1,2,23),a in(24,12,22) from t1;
+------+--------------+----------------+
| a    | a in(1,2,23) | a in(24,12,22) |
+------+--------------+----------------+
|   24 |            0 |              1 |
+------+--------------+----------------+
 row in set (0.00 sec)
判斷運算元是否在某一集合內 

like

代碼如下:mysql> select s,s like ‘beijing‘,s like ‘b%g‘,s like ‘bei____‘,s like ‘%jing‘ from t2; +---------+------------------+--------------+------------------+----------------+ | s       | s like ‘beijing‘ | s like ‘b%g‘ | s like ‘bei____‘ | s like ‘%jing‘ | +---------+------------------+--------------+------------------+----------------+ | beijing |                1 |            1 |                1 |              1 | +---------+------------------+--------------+------------------+----------------+  row in set (0.00 sec)

ike可以用來匹配字串，_代表單個字元，%代表多個字元

 

邏輯運算子 與運算

代碼如下:mysql> select 2&&2,2&&null,2 and 3,2 and 2; +------+---------+---------+---------+ | 2&&2 | 2&&null | 2 and 3 | 2 and 2 | +------+---------+---------+---------+ |    1 |    NULL |       1 |       1 | +------+---------+---------+---------+  row in set (0.00 sec)
這裡&&和and意思一樣 

或運算

代碼如下:

mysql> select 2||2,2||null,2 or 3,2 or 0; +------+---------+--------+--------+ | 2||2 | 2||null | 2 or 3 | 2 or 0 | +------+---------+--------+--------+ |    1 |       1 |      1 |      1 | +------+---------+--------+--------+  row in set (0.00 sec)
這裡||和or的意思一樣 

非運算

代碼如下:mysql> select !1,!2,!null; +----+----+-------+ | !1 | !2 | !null | +----+----+-------+ |  0 |  0 |  NULL | +----+----+-------+  row in set (0.00 sec)
此外還有位元運算，目前還沒用到，等用到的時候再補上

MySQL筆記之運算子使用詳解