《Mysql學習MySQL筆記之運算符使用詳解》要點：
本文介紹了Mysql學習MySQL筆記之運算符使用詳解，希望對您有用。如果有疑問，可以聯系我們。
Mysql可以通過運算符來對表中數據進行運算,比如通過出生日期求年齡等MYSQL實例
運算符包括四類,分別是：算數運算符、比較運算符、邏輯運算符和位運算符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實例
代碼如下:
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,那么計算結果是NULLMYSQL實例
比較運算符
數值比較
MYSQL實例
代碼如下:
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實例
<=>代表等于,等同于=MYSQL實例
此外,等于和不等于不僅可以比較數值,還能比較字符串MYSQL實例
字符串比較
MYSQL實例
代碼如下:
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實例
代碼如下:
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實例
代碼如下:
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)
判斷操作數是否在某一集合內MYSQL實例
like
MYSQL實例
代碼如下:
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實例
邏輯運算符
與運算
MYSQL實例
代碼如下:
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實例
代碼如下:
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實例
代碼如下:
mysql> select !1,!2,!null;
+----+----+-------+
| !1 | !2 | !null |
+----+----+-------+
|? 0 |? 0 |? NULL |
+----+----+-------+
?row in set (0.00 sec)
此外還有位運算,目前還沒用到,等用到的時候再補上
轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/1446.html
