在写这篇文章之前,xxx已经写过了几篇关于改运算判断主题的文章,想要了解的朋友可以去翻一下之前的文章
Mysql可以通过运算符来对表中数据行进运算,比如通过出生日期求龄年等
运算符包含四类,分别是:算数运算符、比拟运算符、逻辑运算符和位运算符
算数运算符
加、减、乘运算
mysql> select a,a+5,a*2 from t1; +------+------+------+ | a | a+5 | a*2 | +------+------+------+ | 24 | 29 | 48 | +------+------+------+ 1 row in set (0.00 sec)
除法和取模运算
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)
要注意的是,如果被除数为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 | +------+------+------+------+-------+-------+-------+-------+--------+ 1 row in set (0.00 sec)
<>代表不即是,等同于!=
<=>代表即是,等同于=
此外,即是和不即是不仅可以比拟值数,还能比拟字符串
字符串比拟
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)
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)
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 | +------+--------------+----------------+ 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 | +---------+------------------+--------------+------------------+----------------+ 1 row in set (0.00 sec)
逻辑运算符
与运算
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)
或运算
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)
非运算
mysql> select !1,!2,!null; +----+----+-------+ | !1 | !2 | !null | +----+----+-------+ | 0 | 0 | NULL | +----+----+-------+ 1 row in set (0.00 sec)
文章结束给大家分享下程序员的一些笑话语录: Bphone之你们聊,我先走了!移动说:我在phone前加o,我叫o缝;苹果说:我在phone前i,我是i缝;微软说:我在phone前加w,我叫w缝;三星说:你们聊,我先走了!
将来王建宙写回忆录的时候,一定要有一句“常小兵为中国移动的发展做出了不可磨灭的贡献”。