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) |