bitsCN.com
mysql可以通过运算符来对表中数据进行运算，比如通过出生日期求年龄等
运算符包括四类，分别是：算数运算符、比较运算符、逻辑运算符和位运算符
win可用的mysql指令skill
下载
安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录，或需要将该能力封装成可公开复用的 Codex skill。
算数运算符
加、减、乘运算
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,后面也可以使用混合运算，只需要注意优先级即可
除法和取模运算
mysql> select a,a/3,a p 3,a%5,mod(a,5) from t1;+------+--------+---------+------+----------+| a | a/3 | a p 3 | a%5 | mod(a,5) |+------+--------+---------+------+----------+| 24 | 8.0000 | 8 | 4 | 4 |+------+--------+---------+------+----------+1 row in set (0.00 sec)
此处 / 和 p 代表整除，% 和 mod 代表取模
要注意的是，如果被除数为0,那么计算结果是NULL
比较运算符
数值比较
mysql> select a,a=24,a40,a>=24,a24,a24 from t1;+------+------+------+------+-------+-------+-------+-------+--------+| a | a=24 | a40 | a>=24 | a24 | a24 |+------+------+------+------+-------+-------+-------+-------+--------+| 24 | 1 | 0 | 0 | 1 | 1 | 0 | 0 | 1 |+------+------+------+------+-------+-------+-------+-------+--------+1 row in set (0.00 sec)
这里的1代表真，0代表假，需要说明的是和
代表不等于，等同于!=
代表等于，等同于=
此外，等于和不等于不仅可以比较数值，还能比较字符串
字符串比较
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可以判断数值是否在某一区间内
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)
like可以用来匹配字符串，_代表单个字符，%代表多个字符
逻辑运算符
与运算
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意思一样
或运算
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的意思一样
非运算
mysql> select !1,!2,!null;+----+----+-------+| !1 | !2 | !null |+----+----+-------+| 0 | 0 | NULL |+----+----+-------+1 row in set (0.00 sec)
此外还有位运算，目前还没用到，等用到的时候再补上
bitsCN.com
