MySQL 运算符
本章节我们主要介绍 MySQL 的运算符及运算符的优先级。
MySQL 主要有以下几种运算符：算术运算符
比较运算符
逻辑运算符
位运算符
算术运算符
MySQL 支持的算术运算符包括:运算符作用+加法
-减法
*乘法
/ 或 DIV除法
% 或 MOD取余
在除法运算和模运算中，如果除数为0，将是非法除数，返回结果为NULL。
1、加mysql>?select?1+2;
+-----+
|?1+2?|
+-----+
|???3?|
+-----+
2、减mysql>?select?1-2;
+-----+
|?1-2?|
+-----+
|??-1?|
+-----+
3、乘mysql>?select?2*3;
+-----+
|?2*3?|
+-----+
|???6?|
+-----+
4、除mysql>?select?2/3;
+--------+
|?2/3????|
+--------+
|?0.6667?|
+--------+
5、商mysql>?select?10?DIV?4;
+----------+
|?10?DIV?4?|
+----------+
|????????2?|
+----------+
6、取余mysql>?select?10?MOD?4;
+----------+
|?10?MOD?4?|
+----------+
|????????2?|
+----------+
比较运算符
SELECT 语句中的条件语句经常要使用比较运算符。通过这些比较运算符，可以判断表中的哪些记录是符合条件的。比较结果为真，则返回 1，为假则返回 0，比较结果不确定则返回 NULL。符号描述备注=等于
<>, !=不等于
>大于
<=小于等于
>=大于等于
BETWEEN在两值之间>=min&&<=max
NOT BETWEEN不在两值之间
IN在集合中
NOT IN不在集合中
<=>严格比较两个NULL值是否相等两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0
LIKE模糊匹配
REGEXP 或 RLIKE正则式匹配
IS NULL为空
IS NOT NULL不为空
1、等于mysql>?select?2=3;
+-----+
|?2=3?|
+-----+
|???0?|
+-----+
mysql>?select?NULL?=?NULL;
+-------------+
|?NULL?=?NULL?|
+-------------+
|????????NULL?|
+-------------+
2、不等于mysql>?select?2<>3;
+------+
|?2<>3?|
+------+
|????1?|
+------+
3、安全等于
与 = 的区别在于当两个操作码均为 NULL 时，其所得值为 1 而不为 NULL，而当一个操作码为 NULL 时，其所得值为 0而不为 NULL。mysql>?select?2<=>3;
+-------+
|?2<=>3?|
+-------+
|?????0?|
+-------+
mysql>?select?null=null;
+-----------+
|?null=null?|
+-----------+
|??????NULL?|
+-----------+
mysql>?select?null<=>null;
+-------------+
|?null<=>null?|
+-------------+
|???????????1?|
+-------------+
4、小于mysql>?select?2<3;
+-----+
|?2<3?|
+-----+
|???1?|
+-----+
5、小于等于mysql>?select?2<=3;
+------+
|?2<=3?|
+------+
|????1?|
+------+
6、大于mysql>?select?2>3;
+-----+
|?2>3?|
+-----+
|???0?|
+-----+
7、大于等于mysql>?select?2>=3;
+------+
|?2>=3?|
+------+
|????0?|
+------+
8、BETWEENmysql>?select?5?between?1?and?10;
+--------------------+
|?5?between?1?and?10?|
+--------------------+
|??????????????????1?|
+--------------------+
9、INmysql>?select?5?in?(1,2,3,4,5);
+------------------+
|?5?in?(1,2,3,4,5)?|
+------------------+
|????????????????1?|
+------------------+
10、NOT INmysql>?select?5?not?in?(1,2,3,4,5);
+----------------------+
|?5?not?in?(1,2,3,4,5)?|
+----------------------+
|????????????????????0?|
+----------------------+
11、IS NULLmysql>?select?null?is?NULL;
+--------------+
|?null?is?NULL?|
+--------------+
|????????????1?|
+--------------+
mysql>?select?'a'?is?NULL;
+-------------+
|?'a'?is?NULL?|
+-------------+
|???????????0?|
+-------------+
12、IS NOT NULLmysql>?select?null?IS?NOT?NULL;
+------------------+
|?null?IS?NOT?NULL?|
+------------------+
|????????????????0?|
+------------------+
mysql>?select?'a'?IS?NOT?NULL;
+-----------------+
|?'a'?IS?NOT?NULL?|
+-----------------+
|???????????????1?|
+-----------------+
13、LIKEmysql>?select?'12345'?like?'12%';
+--------------------+
|?'12345'?like?'12%'?|
+--------------------+
|??????????????????1?|
+--------------------+
mysql>?select?'12345'?like?'12_';
+--------------------+
|?'12345'?like?'12_'?|
+--------------------+
|??????????????????0?|
+--------------------+
14、REGEXPmysql>?select?'beijing'?REGEXP?'jing';
+-------------------------+
|?'beijing'?REGEXP?'jing'?|
+-------------------------+
|???????????????????????1?|
+-------------------------+
mysql>?select?'beijing'?REGEXP?'xi';
+-----------------------+
|?'beijing'?REGEXP?'xi'?|
+-----------------------+
|?????????????????????0?|
+-----------------------+
逻辑运算符
逻辑运算符用来判断表达式的真假。如果表达式是真，结果返回 1。如果表达式是假，结果返回 0。运算符号作用NOT 或 !逻辑非
AND逻辑与
OR逻辑或
XOR逻辑异或
1、与mysql>?select?2?and?0;
+---------+
|?2?and?0?|
+---------+
|???????0?|
+---------+
mysql>?select?2?and?1;
+---------+
|?2?and?1?|
+---------+
|???????1?|
+---------+
2、或mysql>?select?2?or?0;
+--------+
|?2?or?0?|
+--------+
|??????1?|
+--------+
mysql>?select?2?or?1;
+--------+
|?2?or?1?|
+--------+
|??????1?|
+--------+
mysql>?select?0?or?0;
+--------+
|?0?or?0?|
+--------+
|??????0?|
+--------+
mysql>?select?1?||?0;
+--------+
|?1?||?0?|
+--------+
|??????1?|
+--------+
3、非mysql>?select?not?1;
+-------+
|?not?1?|
+-------+
|?????0?|
+-------+
mysql>?select?!0;
+----+
|?!0?|
+----+
|??1?|
+----+
4、异或mysql>?select?1?xor?1;
+---------+
|?1?xor?1?|
+---------+
|???????0?|
+---------+
mysql>?select?0?xor?0;
+---------+
|?0?xor?0?|
+---------+
|???????0?|
+---------+
mysql>?select?1?xor?0;
+---------+
|?1?xor?0?|
+---------+
|???????1?|
+---------+
mysql>?select?null?or?1;
+-----------+
|?null?or?1?|
+-----------+
|?????????1?|
+-----------+
mysql>?select?1?^?0;
+-------+
|?1?^?0?|
+-------+
|?????1?|
+-------+
位运算符
位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数，进行位运算。然后再将计算结果从二进制数变回十进制数。运算符号作用&按位与
|按位或
^按位异或
!取反
<
>>右移
1、按位与mysql>?select?3&5;
+-----+
|?3&5?|
+-----+
|???1?|
+-----+
2、按位或mysql>?select?3|5;
+-----+
|?3|5?|
+-----+
|???7?|
+-----+
3、按位异或mysql>?select?3^5;
+-----+
|?3^5?|
+-----+
|???6?|
+-----+
4、按位取反mysql>?select?~18446744073709551612;
+-----------------------+
|?~18446744073709551612?|
+-----------------------+
|?????????????????????3?|
+-----------------------+
5、按位右移mysql>?select?3>>1;
+------+
|?3>>1?|
+------+
|????1?|
+------+
6、按位左移mysql>?select?3<<1;
+------+
|?3<<1?|
+------+
|????6?|
+------+
运算符优先级
最低优先级为： :=。
最高优先级为： !、BINARY、 COLLATE。