0x00 基础数据

表名: users
id username password
1 test-user-01 123456
2 test-user-02 234567

0x01 运算符概述

运算符是告诉MySQL执行特殊算术或逻辑操作的符号。MySQL的内部运算符很丰富

主要有四大类：算术运算符、比较运算符、逻辑运算符、位操作运算符。

0x02 算术运算符

运算符 作用 测试SQL(返回True) 测试SQL(返回False)
+ 加法运算 select * from users where id=1 and 2=(1+1) select * from users where id=1 and 1=(1+1)
- 减法运算 select * from users where id=1 and 1=(2-1) select * from users where id=1 and 1=(3-1)
* 乘法运算 select from users where id=1 and 1=(11) select from users where id=1 and 1=(12)
/ 除法运算 select * from users where id=1 and 1=(1/1) select * from users where id=1 and 1=(1/2)
% 求余运算,返回余数 select * from users where id=1 and 1=(3%2) select * from users where id=1 and 1=(3%3)

0x03 比较运算符

一个比较运算符的结果总是1、0或者是NULL，比较运算符经常在SELECT的查询条件字句中使用，用来查询满足指定条件的记录

运算符 作用 测试SQL(返回True) 测试SQL(返回False)
= 等于 select * from users where id=1 and 1=1 select * from users where id=1 and 1=2
<> , != 不等于 select from users where id=1 and 1<>2

select from users where id=1 and 1!=2		 select from users where id=1 and 1<>1

select from users where id=1 and 1!=1
<= 小于等于 select * from users where id=1 and 1<=1 select * from users where id=1 and 2<=1
>= 大于等于 select * from users where id=1 and 1>=1 select * from users where id=1 and 1>=2
> 大于 select * from users where id=1 and 2>1 select * from users where id=1 and 1>2
< 小于 select * from users where id=1 and 1<2 select * from users where id=1 and 2<1
!< 不小于 select * from users where id=1 and 1!<1 select * from users where id=1 and 1!<2
!> 不大于 select * from users where id=1 and 2!>3 select * from users where id=1 and 4!>2

0x04 逻辑运算符

逻辑运算符的求值所得结果均为1(TRUE)、0(FALSE)

运算符 作用 测试SQL(返回True) 测试SQL(返回False)
NOT或者! 逻辑非(配合其他使用)
AND 逻辑与 select * from users where id=1 and 1=1 select * from users where id=1 and 1=2
OR 逻辑或 select * from users where id=9999 or 1=1 select * from users where id=9999 or 1=2
IS NULL 判断一个值是否为NULL select from users where id=1 and (select id from users where id=9999) IS NULL

select from users where id=1 and (NULL) IS NULL		 select from users where id=1 and (select id from users where id=1) IS NULL

select from users where id=1 and (1) IS NULL
IS NOT NULL 判断一个值是否不为NULL select from users where id=1 and (select id from users where id=1) IS NOT NULL

select from users where id=1 and (1) IS NOT NULL		 select from users where id=1 and (select id from users where id=999) IS NOT NULL

select from users where id=1 and (null) IS NOT NULL
LIKE 通配符匹配 select * from users where id=1 and ‘aa’ LIKE ‘%a%’ select * from users where id=1 and ‘aa’ LIKE ‘%b%’
NOT LIKE 就是 LIKE 取反 select * from users where id=1 and ‘aa’ NOT LIKE ‘%a%’ select * from users where id=1 and ‘aa’ NOT LIKE ‘%b%’
BETWEEN AND 判断一个值是否落在两个值之间 select * from users where id=1 and 111 between 50 and 200 select * from users where id=1 and 50 between 100 and 200
IN 判断一个值是否落在两个值之间 select * from users where id=1 and 1 in (1,2,3,4) select * from users where id=1 and 1 in (2,3,4)
NOT IN 判断一个值不是IN列表中的任意一个值 select * from users where id=1 and 1 not in (2,3,4) select * from users where id=1 and 1 not in (1,2,3,4)
EXISTS 如果子查询返回了数据, 那么结果为True,否则为False select * from users where id=1 and EXISTS(select id from users where id=1) select * from users where id=1 and EXISTS(select id from users where id=999)
ALL 如果一组的比较都为True, 则比较结果为True select from users where id=1 and 1 <= ALL(select id from users)

select from users where id=1 and 2 >= ALL(select 1)		 select from users where id=1 and 333 <= ALL(select id from users)

select from users where id=1 and 2 >= ALL(select 333)
ANY 如果一组的比较中任何一个为True, 则比较结果为True select from users where id=1 and 1 >= ANY(select id from users where id=1)

select from users where id=1 and 1 >= ANY(select 1)		 select from users where id=1 and 1 >= ANY(select id from users where id=333)

select from users where id=1 and 1 >= ANY(select 333)
SOME 如果一组的比较中, 有些比较为True, 则比较结果为True select from users where id=1 and 2 >= SOME(select id from users where id=2)

select from users where id=1 and 2 >= SOME(select 2)		 select * from users where id=1 and 2 >= SOME(select id from users where id=3)

select * from users where id=1 and 2 >= SOME(select 3) |

0x05 位操作运算符

参与运算符的操作数，按二进制位进行运算

运算符 作用 测试SQL(返回True) 测试SQL(返回False)
& 位与 select * from users where id=1 and 1=(1&1) select * from users where id=1 and 1=(1&2)
^ 位异或 select * from users where id=1 and 1=(1^0) select * from users where id=1 and 1=(1^1)
| 位或 select * from users where id=1 and 1=(1|1) select * from users where id=1 and 1=(1|2)