MySQL笔记之运算符使用详解_MySQL教程

编辑Tag赚U币

推荐:MySQL笔记之修改数据的解决方法
本篇文章介绍了,在mysql中修改数据的解决方法。需要的朋友参考下

Mysql可以通过运算符来对表中数据进行运算,比如通过出生日期求年龄等

运算符包括四类,分别是:算数运算符、比较运算符、逻辑运算符和位运算符


算数运算符
加、减、乘运算

复制代码 代码如下:www.mb5u.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,后面也可以使用混合运算,只需要注意优先级即可


除法和取模运算

复制代码 代码如下:www.mb5u.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


比较运算符
数值比较

复制代码 代码如下:www.mb5u.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代表假,需要说明的是<>和<=>

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

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

此外,等于和不等于不仅可以比较数值,还能比较字符串


字符串比较

复制代码 代码如下:www.mb5u.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

复制代码 代码如下:www.mb5u.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

复制代码 代码如下:www.mb5u.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可以判断数值是否在某一区间内


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

复制代码 代码如下:www.mb5u.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可以用来匹配字符串,_代表单个字符,%代表多个字符


逻辑运算符
与运算

复制代码 代码如下:www.mb5u.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意思一样


或运算

复制代码 代码如下:www.mb5u.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的意思一样


非运算

复制代码 代码如下:www.mb5u.com

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

此外还有位运算,目前还没用到,等用到的时候再补上

分享:MySQL笔记之连接查询详解
连接查询是将两个或两个以上的表按某个条件连接起来,从中选取需要的数据

来源:模板无忧//所属分类:MySQL教程/更新时间:2013-05-04
相关MySQL教程