文章目录
- 一、比较运算符
- (一)正则表达式
- (二)模糊匹配 LIKE
- (三)转义字符
- 二、单行比较运算符
- (一)安全等号 <=>
- 1. 和 =
- 2. 和 =
- (二)区间运算符 between...and...
- (三)为空 is null
- (四)不为空 is not null
- (五)空字符串
- (六)二进制数比较 binary
- 三、多行比较运算符
- (一)> ALL
- (二)> ANY
- (三)在集合中 IN
- (四)不在集合中 NOT IN
- 四、算术运算符
- (一)除 /
- (二)求商 DIV
- (三)幂运算 power
- (四)取模
- 五、逻辑运算符
- (一)逻辑与
- (二)逻辑或
- (三)非
- (四)异或
- 六、位运算符
- (一)按位右移
- (二)按位异或
- (三)按位取反
- 七、运算符优先级
一、比较运算符
SELECT 语句中的条件语句经常要使用比较运算符。通过这些比较运算符,可以判断表中的哪些记录是符合条件的。比较结果为真(即比较关系成立),则返回 1
,比较结果为假(即比较关系不成立)则返回 0
,比较结果不确定则返回 NULL
。
符号 | 描述 | 备注 |
| 等于 | |
| 二进制数比较 | 例如,使用 |
| 不等于 | 在 SQL 的一些版本中,该操作符可被写成 |
| 大于 | |
| 小于 | |
| 小于等于 | |
| 大于等于 | |
| 在两值之间 |
|
| 不在两值之间 | |
| 在集合中 | |
| 不在集合中 | |
| 安全等于 | 与 |
| 匹配某种模式,通常结合通配符使用。筛查出匹配某种模式的 | |
| 筛查出不匹配某种模式的,即过滤掉匹配某种模式的 | |
| 正则式匹配 | |
| 为空 | |
| 不为空 | |
| 最小的 | 当有两个或多个参数时,返回最小值 |
| 最大的 | 当有两个或多个参数时,返回最大值 |
(一)正则表达式
能够匹配到文本,则返回 1,如下所示:
mysql> select 'beijing' regexp 'jing';
+-------------------------+
| 'beijing' regexp 'jing' |
+-------------------------+
| 1 |
+-------------------------+
1 row in set (0.00 sec)
注:‘beijing’ 是被匹配的源字符串,‘jing’ 是正则表达式。
mysql> select 'beijing' regexp 'xi';
+-----------------------+
| 'beijing' regexp 'xi' |
+-----------------------+
| 0 |
+-----------------------+
1 row in set (0.00 sec)
(二)模糊匹配 LIKE
-
%
表示匹配任意数量(包含 0 个)的任意字符,跟 like 配合使用 -
_
下划线表示匹配任意 1 字符
select * from emp_xxx where lower(job) like '%sales%';
select * from emp_xxx where job like '_a%';
(三)转义字符
查询表名以 S_
开头的表的总数:
select count(*) from user_tables where table_name like 'S\_%' escape '\';
说明:_
是特殊符号,需要转义成普通字符。如果要查询的数据中有特殊字符(比如 _
或 %
),在做模糊查询时,需要加上反斜杠 \
符号表示转义,并且用 escape
短语指明转义符号 \
。
二、单行比较运算符
单行比较运算符:>
、<
、>=
、<=
、=
、<>
、<=>
,单行比较运算符只能和一个值比较,即后面只能跟着“一个值”。
注意:SQL 语句中的等号 =
不是赋值表达式,而是相等比较表达式(比较表达式/相等关系式/相等表达式/相等关系表达式)
(一)安全等号 <=>
1. 和 =
像常规等于号 =
一样,两个值进行比较,如果比较结果是真,即比较关系成立,两边的值相等,则返回结果值 1,比较结果是假,即比较关系不成立,两边的值不相等,则返回结果值 0。
'A' <=> 'B' 这个比较表达式,比较结果为假,返回 0
'a' <=> 'a' 这个比较表达式,比较结果为真,返回 1
2. 和 =
<=>
和 =
不同的是,空值 NULL 是没有任何意义的,所以等号 =
运算符不能把 NULL 作为有效的结果,任何数值与空值通过等号 =
比较的结果都是 NULL,但是很多时候我们并不希望得到这样的结果,所以可以使用安全等号 <=>
。
例如,'a' <=> NULL
得 0
;NULL<=> NULL
得出 1
。而等号 =
运算符规则是 'a' = NULL
结果是 NULL
;甚至 NULL = NULL
结果也是 NULL
。
除了 <=>
,还有两个其他的操作符用来处理某个值和 NULL
做比较,也就是 IS NULL
和 IS NOT NULL
。他们是 ANSI 标准中的一部分,因此也可以用在其他数据库中。而 <=>
只能在 Mysql
中使用。
你可以把 <=>
当作 Mysql
中的方言。
'a' IS NULL ==> 'a' <=> NULL
'a' IS NOT NULL ==> NOT('a' <=> NULL)
据此,你可以把下面的查询语句的条件改的更具移植性一点:
where t1.name is null
(二)区间运算符 between…and…
select * from emp_xxx where salary between 5000 and 10000;
select * from emp_xxx where salary not between 5000 and 8000;
(三)为空 is null
select * from emp_xxx where bonus is null;
(四)不为空 is not null
select * from emp_xxx where bonus is not null;
(五)空字符串
查询字段 bonus 为空串的所有记录信息:
select * from emp_xxx where bonus = '';
(六)二进制数比较 binary
mysql> select binary 'b'='B';
+----------------+
| binary 'b'='B' |
+----------------+
| 0 |
+----------------+
1 row in set (0.00 sec)
mysql> select 'b'='B';
+---------+
| 'b'='B' |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
说明:使用 =
比较字符时,默认不区分大小写,所以小写 b 和大写 B,相等比较结果为真,返回 1;如果前面加上 binary,则是比较两者的二进制数,肯定是不同的,所以比较结果为假,返回 0。
三、多行比较运算符
多行比较运算符:> ALL
、> ANY
、< ALL
、< ANY
、IN
,可以和多个值比较,后面可以跟着“多个值”
(一)> ALL
select ename from emp_xxx
where salary > ALL(500,1000,2500);
(二)> ANY
select ename from emp_xxx
where salary > ANY(500,1000,2500);
(三)在集合中 IN
select * from emp_xxx where job in ('Manager' , 'Analyst');
(四)不在集合中 NOT IN
select * from emp_xxx where depno not in (20 , 30);
注意:not in(列表)
的列表中有 null
值 , 将没有结果返回,所以列表中不能含有 null
,in(列表)
则没有关系。
四、算术运算符
运算符 | 作用 |
| 加法 |
| 减法 |
| 乘法 |
| 除法运算,商如果是小数,最多会保留小数点的后四位,且会四舍五入 |
| 除法运算,整除,商如果是小数,只保留整数部分,且不会四舍五入 |
| 求余 |
| 取模,余数和模数实际运算是有区别的,详见《求余和取模的计算公式》 |
power | 幂运算、乘方运算 |
注意:
- 在除法运算和模运算中,如果除数为0,将是非法除数,返回结果为NULL。
- 加法运算时,若含有字符串,而字符串以数字开头,转换成数字,如下:
mysql> select 1+'5a';
+--------+
| 1+'5a' |
+--------+
| 6 |
+--------+
1 row in set, 1 warning (0.00 sec)
- 加法运算时,若含有字符串,而字符串以字母开头的,转换成 0,如下所示:
mysql> select 1+'a5';
+--------+
| 1+'a5' |
+--------+
| 1 |
+--------+
1 row in set, 1 warning (0.00 sec)
(一)除 /
mysql> select 8/3;
+--------+
| 8/3 |
+--------+
| 2.6667 |
+--------+
(二)求商 DIV
mysql> select 8 div 3;
+---------+
| 8 div 3 |
+---------+
| 2 |
+---------+
1 row in set (0.00 sec)
(三)幂运算 power
mysql> select power(2,3);
+------------+
| power(2,3) |
+------------+
| 8 |
+------------+
1 row in set (0.00 sec)
(四)取模
mysql> select 10 MOD 4;
+----------+
| 10 MOD 4 |
+----------+
| 2 |
+----------+
五、逻辑运算符
逻辑运算符用来判断表达式的真假。如果表达式是真,结果返回 1。如果表达式是假,结果返回 0。
运算符号 | 作用 |
| 逻辑非 |
| 逻辑与 |
| 逻辑或 |
| 逻辑异或 |
(一)逻辑与
&&
或者 AND
是“与”运算的两种表达方式。如果所有数据不为0且不为空值(NULL),则结果返回1;如果存在任何一个数据为0,则结果返回0;如果存在一个数据为NULL且没有数据为0,则结果返回NULL。“与”运算符支持多个数据同时进行运算。
mysql> select 2 and 0;
+---------+
| 2 and 0 |
+---------+
| 0 |
+---------+
1 row in set (0.00 sec)
mysql> select 2 and 3;
+---------+
| 2 and 3 |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
(二)逻辑或
两者只要有一个数是大于 0 的,表达式就是真,返回 1。如下所示:
mysql> select 2 or 0;
+--------+
| 2 or 0 |
+--------+
| 1 |
+--------+
mysql> select 0 or 0;
+--------+
| 0 or 0 |
+--------+
| 0 |
+--------+
mysql> select 1 || 0;
+--------+
| 1 || 0 |
+--------+
| 1 |
+--------+
mysql> select null or 1;
+-----------+
| null or 1 |
+-----------+
| 1 |
+-----------+
(三)非
mysql> select not 1;
+-------+
| not 1 |
+-------+
| 0 |
+-------+
mysql> select !0;
+----+
| !0 |
+----+
| 1 |
+----+
(四)异或
两者的值相同,则表示假,返回 0;两者的值不同,则表示真,返回 1。这里的值是指 1 或者 0。
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 1 ^ 0;
+-------+
| 1 ^ 0 |
+-------+
| 1 |
+-------+
六、位运算符
位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数,进行位运算。然后再将计算结果从二进制数变回十进制数。
运算符号 | 作用 |
| 按位与 |
| 按位或 |
| 按位异或 |
| 按位取反 |
| 左移 |
| 右移 |
(一)按位右移
mysql> select 3>>1;
+------+
| 3>>1 |
+------+
| 1 |
+------+
(二)按位异或
mysql> select 3^5;
+-----+
| 3^5 |
+-----+
| 6 |
+-----+
(三)按位取反
mysql> select ~18446744073709551612;
+-----------------------+
| ~18446744073709551612 |
+-----------------------+
| 3 |
+-----------------------+
七、运算符优先级
优先级由低到高排列 | 运算符 |
1 |
|
2 |
|
3 |
|
4 |
|
5 |
|
6 |
|
7 |
|
8 |
|
9 |
|
10 |
|
11 |
|
12 |
|
13 |
|
14 |
|
可以看出,不同运算符的优先级是不同的。一般情况下,级别高的运算符优先进行计算,如果级别相同,MySQL 按表达式的顺序从左到右依次计算。
另外,在无法确定优先级的情况下,可以使用圆括号 ()
来改变优先级,并且这样会使计算过程更加清晰。