MySQL培訓(xùn)教程：MySQL的運(yùn)算符計(jì)算

文章來(lái)源：更新時(shí)間:2016/7/12 13:35:55

在線老師點(diǎn)擊咨詢：

最新學(xué)訊：近期OCP認(rèn)證正在報(bào)名中，因考試人員較多請(qǐng)盡快報(bào)名獲取最近考試時(shí)間，報(bào)名費(fèi)用請(qǐng)聯(lián)系在線老師，甲骨文官方認(rèn)證，報(bào)名從速！

我要咨詢

MySQL可以通過(guò)運(yùn)算符來(lái)對(duì)表中數(shù)據(jù)進(jìn)行運(yùn)算，比如通過(guò)出生日期求年齡等，運(yùn)算符包括四類，分別是：算數(shù)運(yùn)算符、比較運(yùn)算符、邏輯運(yùn)算符和位運(yùn)算符

算數(shù)運(yùn)算符

加、減、乘運(yùn)算

mysql> select a,a+5,a*2 from t1;
+------+------+------+
| a    | a+5  | a*2  |
+------+------+------+
|   24 |   29 |   48 |
+------+------+------+
1 row in set (0.00 sec)

這里的原值為24,后面也可以使用混合運(yùn)算，只需要注意優(yōu)先級(jí)即可

除法和取模運(yùn)算

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 |
+------+--------+---------+------+----------+
1 row in set (0.00 sec)

此處 / 和 div 代表整除，% 和 mod 代表取模

要注意的是，如果被除數(shù)為0,那么計(jì)算結(jié)果是NULL

比較運(yùn)算符

數(shù)值比較

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 |
+------+------+------+------+-------+-------+-------+-------+--------+
1 row in set (0.00 sec)

這里的1代表真，0代表假，需要說(shuō)明的是<>和<=>

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

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

此外，等于和不等于不僅可以比較數(shù)值，還能比較字符串

字符串比較

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 |
+------+--------+------------+-----------+----------+
1 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 |
+------+-----------+---------------+
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 |
+------+---------------------+-------------------------+
1 row in set (0.00 sec)

between and 和not between and可以判斷數(shù)值是否在某一區(qū)間內(nèi)

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 |
+------+--------------+----------------+
1 row in set (0.00 sec)

判斷操作數(shù)是否在某一集合內(nèi)

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 |
+---------+------------------+--------------+------------------+----------------+
1 row in set (0.00 sec)

like可以用來(lái)匹配字符串，_代表單個(gè)字符，%代表多個(gè)字符

邏輯運(yùn)算符

與運(yùn)算

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 |
+------+---------+---------+---------+
1 row in set (0.00 sec)

這里&&和and意思一樣

或運(yùn)算

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 |
+------+---------+--------+--------+
1 row in set (0.00 sec)

這里||和or的意思一樣

非運(yùn)算

mysql> select !1,!2,!null;
+----+----+-------+
| !1 | !2 | !null |
+----+----+-------+
|  0 |  0 |  NULL |
+----+----+-------+
1 row in set (0.00 sec)

此外還有位運(yùn)算，目前還沒(méi)用到，等用到的時(shí)候再補(bǔ)上

顾美玲勾引管家|日韩黄色成人|国产精品theporn动漫|欧美日日日|国产精品探花在线

MySQL培訓(xùn)教程：MySQL的運(yùn)算符計(jì)算