MySQL筆記之運算符使用詳解_MySQL教程
推薦：MySQL筆記之修改數據的解決方法本篇文章介紹了，在mysql中修改數據的解決方法。需要的朋友參考下
Mysql可以通過運算符來對表中數據進行運算，比如通過出生日期求年齡等
運算符包括四類，分別是：算數運算符、比較運算符、邏輯運算符和位運算符
算數運算符
加、減、乘運算
復制代碼 代碼如下:m.ghpqjb.com
mysql> select a,a+5,a*2 from t1;
+------+------+------+
| a | a+5 | a*2 |
+------+------+------+
| 24 | 29 | 48 |
+------+------+------+
row in set (0.00 sec)
這里的原值為24,后面也可以使用混合運算，只需要注意優(yōu)先級即可
除法和取模運算
復制代碼 代碼如下:m.ghpqjb.com
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
比較運算符
數值比較
復制代碼 代碼如下:m.ghpqjb.com
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代表假，需要說明的是<>和<=>
<>代表不等于，等同于!=
<=>代表等于，等同于=
此外，等于和不等于不僅可以比較數值，還能比較字符串
字符串比較
復制代碼 代碼如下:m.ghpqjb.com
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
復制代碼 代碼如下:m.ghpqjb.com
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
復制代碼 代碼如下:m.ghpqjb.com
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可以判斷數值是否在某一區(qū)間內
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
復制代碼 代碼如下:m.ghpqjb.com
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可以用來匹配字符串，_代表單個字符，%代表多個字符
邏輯運算符
與運算
復制代碼 代碼如下:m.ghpqjb.com
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意思一樣
或運算
復制代碼 代碼如下:m.ghpqjb.com
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的意思一樣
非運算
復制代碼 代碼如下:m.ghpqjb.com
mysql> select !1,!2,!null;
+----+----+-------+
| !1 | !2 | !null |
+----+----+-------+
| 0 | 0 | NULL |
+----+----+-------+
row in set (0.00 sec)
此外還有位運算，目前還沒用到，等用到的時候再補上
分享：MySQL筆記之連接查詢詳解連接查詢是將兩個或兩個以上的表按某個條件連接起來，從中選取需要的數據
相關MySQL教程：
- MSSQL清空日志刪除日志文件
- 關于數據庫中保留小數位的問題
- 解析mysql與Oracle update的區(qū)別
- mysql 導入導出數據庫以及函數、存儲過程的介紹
- MySQL——修改root密碼的4種方法(以windows為例)
- 解決MYSQL出現(xiàn)Can''t create/write to file ''#sql_5c0_0.MYD''的問題
- 深入理解SQL的四種連接-左外連接、右外連接、內連接、全連接
- 解析:內聯(lián),左外聯(lián),右外聯(lián),全連接,交叉連接的區(qū)別
- mysql出現(xiàn)“Incorrect key file for table”處理方法
- mysql重裝后出現(xiàn)亂碼設置為utf8可解決
- 淺析一個MYSQL語法(在查詢中使用count)的兼容性問題
- 解析MySQL中INSERT INTO SELECT的使用
- 相關鏈接：
- 教程說明：
MySQL教程-MySQL筆記之運算符使用詳解
。