运算符概念
算术运算符
加减乘除求余
比较运算符
大于> 小于< 等于= 大于等于>= 小于等于<= 不等于!=
IN、 BETWEEN AND、 IS NULL、 GREATEST、 LEAST、 LIKE、 REGEXP
逻辑运算符
TRUE(1) FALSE(0) NOT(!) AND(&&) OR(||) XOR
位操作运算符
操作数按二进制位进行运算，包括与(&)位、或(|)位、非(~)位、异或(^)、左移(<<)、右移(>>)
算术运算符
mysql> create table tmpl4(num int);
Query OK, 0 rows affected (0.16 sec)
mysql> insert into tmpl4 values(64);
Query OK, 1 row affected (0.01 sec)
1.加减运算
mysql> select num,num+10,num-3+5,num+5-3,num+36.5 from tmpl4;
+------+--------+---------+---------+----------+
| num | num+10 | num-3+5 | num+5-3 | num+36.5 |
+------+--------+---------+---------+----------+
| 64 | 74 | 66 | 66 | 100.5 |
+------+--------+---------+---------+----------+
1 row in set (0.00 sec)
2.乘法、除法运算、取余
mysql> select num,num*2,num/2,num/3,num%3 from tmpl4;
+------+-------+---------+---------+-------+
| num | num*2 | num/2 | num/3 | num%3 |
+------+-------+---------+---------+-------+
| 64 | 128 | 32.0000 | 21.3333 | 1 |
+------+-------+---------+---------+-------+
1 row in set (0.00 sec)
3.用0除num
mysql> select num,num/0,num%0 from tmpl4;
+------+-------+-------+
| num | num/0 | num%0 |
+------+-------+-------+
| 64 | NULL | NULL |
+------+-------+-------+
1 row in set (0.00 sec)
比较运算符
1.等于运算符
mysql> select 1=0,'2'=2,2=2,'0.02'=0,'b'='b',(1+3)=(2+2),null=null from tmpl4;
+-----+-------+-----+----------+---------+-------------+-----------+
| 1=0 | '2'=2 | 2=2 | '0.02'=0 | 'b'='b' | (1+3)=(2+2) | null=null |
+-----+-------+-----+----------+---------+-------------+-----------+
| 0 | 1 | 1 | 0 | 1 | 1 | NULL |
+-----+-------+-----+----------+---------+-------------+-----------+
1 row in set (0.00 sec)
如下规则：
若两个参数为NULL，则结果为NULL
若两个参数为字符串，则按照字符串比较
若两个参数为整数，则按照整数比较
若两个参数一整数，一字符串，则会自动将字符串转为数字再进行比较
2.安全等于运算符（<=>）:
与=号用法一至，唯一区别是可以判断NULL，两者都为NULL时，返回值为1。
mysql> select 1<=>0,'2'<=>2,null<=>null from tmpl4;
+-------+---------+-------------+
| 1<=>0 | '2'<=>2 | null<=>null |
+-------+---------+-------------+
| 0 | 1 | 1 |
+-------+---------+-------------+
1 row in set (0.00 sec)
3.不等于运算符(<>或者!=)
mysql> select 'good'<>'god',1<>2,4!=4;
+---------------+------+------+
| 'good'<>'god' | 1<>2 | 4!=4 |
+---------------+------+------+
| 1 | 1 | 0 |
+---------------+------+------+
1 row in set (0.00 sec)
4.小于或等于运算符(<=)
mysql> select 'good'<='god',1<=2,null<=null;
+---------------+------+------------+
| 'good'<='god' | 1<=2 | null<=null |
+---------------+------+------------+
| 0 | 1 | NULL |
+---------------+------+------------+
1 row in set (0.00 sec)
5.is null(isnull)、is not null运算符
mysql> select null is null,isnull(null),isnull(10),10 is not null;
+--------------+--------------+------------+----------------+
| null is null | isnull(null) | isnull(10) | 10 is not null |
+--------------+--------------+------------+----------------+
| 1 | 1 | 0 | 1 |
+--------------+--------------+------------+----------------+
1 row in set (0.00 sec)
6.BETWEEN AND运算符
mysql> select 4 BETWEEN 2 AND 5,4 BETWEEN 4 AND 6,12 BETWEEN 9 AND 10;
+-------------------+-------------------+---------------------+
| 4 BETWEEN 2 AND 5 | 4 BETWEEN 4 AND 6 | 12 BETWEEN 9 AND 10 |
+-------------------+-------------------+---------------------+
| 1 | 1 | 0 |
+-------------------+-------------------+---------------------+
1 row in set (0.00 sec)
7.LEAST运算符
mysql> select least(2,0),least(20,3,100),least('a','b','c'),least(10,null);
+------------+-----------------+--------------------+----------------+
| least(2,0) | least(20,3,100) | least('a','b','c') | least(10,null) |
+------------+-----------------+--------------------+----------------+
| 0 | 3 | a | NULL |
+------------+-----------------+--------------------+----------------+
1 row in set (0.00 sec)
返回最小值，如果有一个自变量为NULL，无法判断大小则返回NULL。
8.GRETEST运算符(最大值同最小值)
mysql> select greatest(2,0),greatest(20,3,100.5),greatest('a','b','c'),greatest(10,null);
+---------------+----------------------+-----------------------+-------------------+
| greatest(2,0) | greatest(20,3,100.5) | greatest('a','b','c') | greatest(10,null) |
+---------------+----------------------+-----------------------+-------------------+
| 2 | 100.5 | c | NULL |
+---------------+----------------------+-----------------------+-------------------+
1 row in set (0.00 sec)
9.IN、NOT IN 运算符
mysql> select 2 in (1,3,5,'thks'),'thks' not in ('thsk',2,3),'5' in (5,62);
+---------------------+----------------------------+---------------+
| 2 in (1,3,5,'thks') | 'thks' not in ('thsk',2,3) | '5' in (5,62) |
+---------------------+----------------------------+---------------+
| 0 | 1 | 1 |
+---------------------+----------------------------+---------------+
1 row in set, 2 warnings (0.00 sec)
存在NULL时的IN查询,左侧为NULL，表中找不到匹配项并且表达式中有一个为NULL
mysql> select null in (1,20,'dasd'),10 in ('dasdm',null,56);
+-----------------------+-------------------------+
| null in (1,20,'dasd') | 10 in ('dasdm',null,56) |
+-----------------------+-------------------------+
| NULL | NULL |
+-----------------------+-------------------------+
1 row in set, 1 warning (0.00 sec)
10.LIKE
匹配对象 like 要匹配的格式，满足返回1，否则0
- '%',匹配任何数目的字符
- '_',只能匹配一个字符
mysql> select 'stud' like 'stud','stud' like 'stu_','stud' like '%d','stud' like 's__';
+--------------------+--------------------+------------------+-------------------+
| 'stud' like 'stud' | 'stud' like 'stu_' | 'stud' like '%d' | 'stud' like 's__' |
+--------------------+--------------------+------------------+-------------------+
| 1 | 1 | 1 | 0 |
+--------------------+--------------------+------------------+-------------------+
1 row in set (0.00 sec)
REGEXP正则表达式
匹配对象 REGEXP 指定匹配的字符串
- '^'匹配以该字符串后面的字符开头
- '$'匹配以该字符串前的字符结尾
- '.'匹配任何一个单字符
- '[....]'匹配在方括号内的任何字符，为了命名字符的范围，使用了一个'-','[a-z]'匹配任何字符。'[0-9]'匹配任何数字。
- ' * '匹配零个或多个在他前面的字符；'x'匹配任何数量的'x'字符，'[0-9]'匹配任何数量的数字
mysql> select 'ssky' REGEXP '^s','ssky' REGEXP 'y$','ssky' REGEXP '.sky','ssky' REGEXP '[ab]';
+--------------------+--------------------+----------------------+----------------------+
| 'ssky' REGEXP '^s' | 'ssky' REGEXP 'y$' | 'ssky' REGEXP '.sky' | 'ssky' REGEXP '[ab]' |
+--------------------+--------------------+----------------------+----------------------+
| 1 | 1 | 1 | 0 |
+--------------------+--------------------+----------------------+----------------------+
1 row in set (0.04 sec)
逻辑运算符
1.NOT 或者 ！
逻辑运算当为0时，所得值为1，当操作数为非零时，所得值为0，当操作数为NULL时，返回值为NULL。
mysql> select not 10,not (1-1), not -5, not null, not 1+1;
+--------+-----------+--------+----------+---------+
| not 10 | not (1-1) | not -5 | not null | not 1+1 |
+--------+-----------+--------+----------+---------+
| 0 | 1 | 0 | NULL | 0 |
+--------+-----------+--------+----------+---------+
1 row in set (0.00 sec)
mysql> select !10,!-5,!(1-1),!(1+1);
+-----+-----+--------+--------+
| !10 | !-5 | !(1-1) | !(1+1) |
+-----+-----+--------+--------+
| 0 | 0 | 1 | 0 |
+-----+-----+--------+--------+
1 row in set (0.00 sec)
2.AND 或者 &&
逻辑运算为非0并且不为NULL，返回1，有一个为0则返回0,其余情况返回NULL
mysql> select 1 AND -1,1 AND 0,0 AND NULL,1 AND NULL;
+----------+---------+------------+------------+
| 1 AND -1 | 1 AND 0 | 0 AND NULL | 1 AND NULL |
+----------+---------+------------+------------+
| 1 | 0 | 0 | NULL |
+----------+---------+------------+------------+
1 row in set (0.00 sec)
mysql> select 1 && -1,1 && 0,0 && NULL,1 && NULL;
+---------+--------+-----------+-----------+
| 1 && -1 | 1 && 0 | 0 && NULL | 1 && NULL |
+---------+--------+-----------+-----------+
| 1 | 0 | 0 | NULL |
+---------+--------+-----------+-----------+
1 row in set (0.00 sec)
3.OR 或者 ||
两值均为非NULL，只要一个不是0，则为1；有一个为NULL，只要有一个不是0，则是1，否则则为NULL
mysql> select 0 or -1, 0 or NULL, 1 or NULL, 0 or 0;
+---------+-----------+-----------+--------+
| 0 or -1 | 0 or NULL | 1 or NULL | 0 or 0 |
+---------+-----------+-----------+--------+
| 1 | NULL | 1 | 0 |
+---------+-----------+-----------+--------+
1 row in set (0.00 sec)
mysql> select 0 || -1,0 || NULL, 50 || NULL, 0 || 0;
+---------+-----------+------------+--------+
| 0 || -1 | 0 || NULL | 50 || NULL | 0 || 0 |
+---------+-----------+------------+--------+
| 1 | NULL | 1 | 0 |
+---------+-----------+------------+--------+
1 row in set (0.00 sec)
4.XOR
逻辑异或运算，当任意一个为NULL，返回NULL，两个都非NULL,一个为0，一个为非0，返回1，否则返回0。
mysql> select 0 xor 0, 1 xor 1, 1 xor NULL, 25 xor 0, 0 xor NULL;
+---------+---------+------------+----------+------------+
| 0 xor 0 | 1 xor 1 | 1 xor NULL | 25 xor 0 | 0 xor NULL |
+---------+---------+------------+----------+------------+
| 0 | 0 | NULL | 1 | NULL |
+---------+---------+------------+----------+------------+
位运算符
1.位或运算符(|)
位或运算的实质是将参与运算的几个数据，按对应的二进制数逐位进行逻辑或运算。对应的二进制位有一个或两个为1，则该位的运算结果为1，否则为0。
mysql> select 10 | 15, 9 | 4 | 2;
+---------+-----------+
| 10 | 15 | 9 | 4 | 2 |
+---------+-----------+
| 15 | 15 |
+---------+-----------+
1 row in set (0.00 sec)
2.位与运算符(&)
位与运算的实质是将参与运算的几个操作数，按对应的二进制数逐位进行逻辑与运算，对应的二进制都为1，则该位的运算结果为1，否则为0
mysql> select 10 & 15, 9 &4 &2;
+---------+---------+
| 10 & 15 | 9 &4 &2 |
+---------+---------+
| 10 | 0 |
+---------+---------+
1 row in set (0.00 sec)
3.位异或运算符（^）
位与运算的实质是将参与运算的几个操作数，按对应的二进制数逐位进行逻辑与运算，对应的二进制不一样时，则该位的运算结果为1，否则为0
mysql> select 10 ^ 15,1^0,1^1;
+---------+-----+-----+
| 10 ^ 15 | 1^0 | 1^1 |
+---------+-----+-----+
| 5 | 1 | 0 |
+---------+-----+-----+
1 row in set (0.00 sec)
4.位左移运算符（<<）
使指定的二进制所有位都左移指定的位数，左移后左边高位的数值将丢弃，右边低位的位置用0补齐。
mysql> select 1<<2,2<<4;
+------+------+
| 1<<2 | 2<<4 |
+------+------+
| 4 | 32 |
+------+------+
1 row in set (0.00 sec)
1的二进制位0000 0001，左移两位之后变为0000 0100，即十进制整数4，再左移两位变为16。
5.位取反运算符(~)
位取反运算实质是将参与运算的数据，按对应的二进制数逐位反转，即1取反后变为0，0取反后变为1。
mysql> select 5 & ~1;
+--------+
| 5 & ~1 |
+--------+
| 4 |
+--------+
1 row in set (0.00 sec)
~优先级高，1本来为0000 0001，取反后为1111 1110，5的二进制为0000 0101，与运算后为0000 0100，则为4
mysql经过位运算后的数值是一个64位的无符号整数，可以使用BIN()函数产看1取反之后的结果
+------------------------------------------------------------------+
| bin(~1) |
+------------------------------------------------------------------+
| 1111111111111111111111111111111111111111111111111111111111111110 |
+------------------------------------------------------------------+
1 row in set (0.00 sec)