MySQL 操作符
MySQL 操作符是 MySQL 数据库操作中不可或缺的一部分,它们用于执行各种数据运算、比较、逻辑判断等。
-
MySQL 中有多种操作符可用于数据查询和筛选
-
MySQL 所提供的运算符可以直接对表中数据或字段进行运算
-
MySQL 支持 4 种运算符,分别是:算术运算符、比较预算符、逻辑运算符、位运算符
算术运算符
执行算术运算,它们是最常用、最简单的一类运算符。
运算符 | 作用 | 使用方法 |
---|---|---|
+ | 加法运算 | 用于获得一个或多个值的和 |
- | 减法运算 | 用于从一个值中减去另一个值 |
* | 乘法运算 | 使数字相乘,得到两个或多个值的乘积 |
/ | 除法运算,返回商 | 用一个值除以另一个值得到商 |
%, MOD(a,b)=>a%b | 求余运算,返回余数 | 用一个值除以另一个值得到余数 |
-
行除法运算时,由于 64 无法被 3 整除,因此 MySQL 对 64/3 求商的结果保存到了小数点后面四位,结果为 21.3333
-
除数为 0 的除法是没有意义的。所以在除法运算和取余运算中,如果除数为 0,返回结果为 NULL
比较运算符
主要用于数值的比较、字符串的匹配(字符串比较不区分大小写)、表达式的值等。
当使用 SELECT
语句进行查询时,MySQL 允许用户对表达式的左边操作数和右边操作数进行比较,比较结果为真,则返回 1,为假则返回 0,比较结果不确定则返回 NULL。
运算符 | 作用 |
---|---|
= | 等于 |
<=> | 安全的等于 |
<> 或者 != | 不等于 |
<= | 小于等于 |
>= | 大于等于 |
> | 大于 |
IS NULL 或者 ISNULL | 判断一个值是否为空 |
IS NOT NULL | 判断一个值是否不为空 |
BETWEEN AND | 判断一个值是否落在两个值之间 |
1)等于运算(=)
- 若有一个或两个操作数为 NULL,则比较运算的结果为 NULL。
- 若两个操作数都是字符串,则按照字符串进行比较。
- 若两个操作数均为整数,则按照整数进行比较。
- 若一个操作数为字符串,另一个操作数为数字,则 MySQL 可以自动将字符串转换为数字。
- NULL不能被用于比较运算。
2)安全等于运算符 (<=>)
<=>
操作符和 =
操作符类似,但 <=>
可以用来判断 NULL 值:
- 当两个操作数均为 NULL 时,其返回值为 1 而不为 NULL;
- 当一个操作数为 NULL 时,其返回值为 0 而不为 NULL。
3)不等于运算符(< > 或 !=)
<>
和 !=
用于判断数字、字符串、表达式是否不相等。
- 对于
<>
和!=
,如果两侧操作数不相等,返回值为 1,否则返回值为 0; - 如果两侧操作数有一个是 NULL,那么返回值也是 NULL。
4) 比大小运算符( >、<、>=、<= )
- 条件成立返回值为1,否则返回值为0。
- 如果两侧操作数有一个是 NULL,那么返回值也是 NULL。
5)BETWEEN AND 运算符
BETWEEN AND
运算符用来判断表达式的值是否位于两个数之间,或者说是否位于某个范围内,语法格式如下:
expr BETWEEN low AND high
#等价于
expr >= low AND value <= high
expr
表示要判断的表达式,low 表示最小值,high 表示最大值。如果
expr
大于等于 low 并且小于等于 high,则返回值为 1,否则返回值为 0。
- 对于字符串类型的比较,按字母表中字母顺序进行比较。
- 若expr、low、high中任一一个值为NULL,则返回值为NULL
NOT BETWEEN AND
expr NOT BETWEEN low AND high
#等价于
expr < low OR expr > high
expr
小于 low 并且大于 high,则返回值为 1,否则返回值为 0。
逻辑(布尔)运算符
包括与、或、非和异或、等逻辑运算符。其返回值为布尔型,真值(1 或 true)和假值(0 或 false)。
运算符 | 作用 |
---|---|
NOT 或者 ! | 逻辑非 |
AND 或者 && | 逻辑与 |
OR 和 | | 逻辑或 |
XOR | 逻辑异或 |
1) 逻辑非运算(NOT 或者 !)
NOT
和 !
都是逻辑非运算符,返回和操作数相反的结果:
-
当操作数为 0(假)时,返回值为 1;
-
当操作数为非零值时,返回值为 0;
-
当操作数为 NULL 时,返回值为 NULL
-
需要注意的是,
NOT
的优先级要低于+
,而!
的优先级别要高于+
在使用运算符运算时,一定要注意运算符的优先级,如果不能确定计算顺序,最好使用括号,以保证运算结果的正确。
2)逻辑与运算符(AND 或者 &&)
- 当所有操作数都为非零值并且不为 NULL 时,返回值为 1;
- 当一个或多个操作数为 0 时,返回值为 0;
- 操作数中有任何一个为 NULL 时,返回值为 NULL。
- 0 AND NULL == 0
AND
运算符可以有多个操作数,要注意多个操作数运算时,AND 两边要使用空格隔开,否则影响结果的正确性。
TRUE | FALSE | NULL | |
---|---|---|---|
TRUE | TRUE | FALSE | NULL |
FALSE | FALSE | FALSE | FALSE |
NULL | NULL | FALSE | NULL |
3)逻辑或运算符(OR 或者 ||)
- 当两个操作数都为非 NULL 值时,如果有任意一个操作数为非零值,则返回值为 1,否则结果为 0;
- 当有一个操作数为 NULL 时,如果另一个操作数为非零值,则返回值为 1,否则结果为NULL;
- 假如两个操作数均为 NULL 时,则返回值为 NULL。
TRUE | FALSE | NULL | |
TRUE | TRUE | TRUE | TRUE |
FALSE | TRUE | FALSE | NULL |
NULL | TRUE | NULL | NULL |
4)异或运算(XOR 运算符)
XOR
表示逻辑异或:
-
当任意一个操作数为 NULL 时,返回值为 NULL;
-
对于非 NULL 的操作数,如果两个操作数都是非 0 值或者都是 0 值,则返回值为 0;
-
如果一个为0值,另一个为非 0 值,返回值为 1。
-
a XOR b 的计算等同于 (a AND (NOT b))或者 ((NOT a) AND b)。
位运算符
包括按位与、按位或、按位取反、按位异或、按位左移和按位右移等位运算符。
位运算必须先将数据转换为补码,然后在根据数据的补码进行操作。运算完成后,将得到的值转换为原来的类型(十进制数),返回给用户。
位运算一般用于操作整数,对整数进行位运算才有实际的意义。整数在内存中是以补码形式存储的,正数的补码形式和原码形式相同,
而负数的补码形式和它的原码形式是不一样的,对负数进行位运算时,操作的是它的补码,而不是它的原码。
运算符 | 说明 | 使用形式 | 举例 |
---|---|---|---|
| | 位或 | a | b | 5 |8 |
& | 位与 | a & b | 5 & 8 |
^ | 位异或 | a ^ b | 5 ^ 8 |
~ | 位取反 | ~a | ~5 |
<< | 位左移 | a << b | 5 << 2,表示整数 5 按位左移 2 位 |
>> | 位右移 | a >> b | 5 >> 2,表示整数 5 按位右移 2 位 |
运算符优先级
优先级由低到高排列 | 运算符 |
---|---|
1 | =(赋值运算)、:= |
2 | II、OR |
3 | XOR |
4 | &&、AND |
5 | NOT |
6 | BETWEEN、CASE、WHEN、THEN、ELSE |
7 | =(比较运算)、<=>、>=、>、<=、<、<>、!=、 IS、LIKE、REGEXP、IN |
8 | | |
9 | & |
10 | <<、>> |
11 | -(减号)、+ |
12 | *、/、% |
13 | ^ |
14 | -(负号)、〜(位反转) |
15 | ! |
一般情况下,级别高的运算符优先进行计算,如果级别相同,MySQL 按表达式的顺序从左到右依次计算。
在无法确定优先级的情况下,可以使用圆括号“()”来改变优先级,并且这样会使计算过程更加清晰。
集合运算符
运算符 | 含义 | 英文 |
---|---|---|
∪ ∪ ∪ | 并 | Union |
− − − | 差 | Difference |
∩ ∩ ∩ | 交 | Intersection |
× × × | 笛卡尔积 | Cartesian Product |
专门的关系运算符
运算符 | 含义 | 英文 |
---|---|---|
σ σ σ | 选择 | Selection |
π π π | 投影 | Projection |
⋈ ⋈ ⋈ | 链接 | Join |
÷ ÷ ÷ | 除 | Division |
IN 、NOT 和 NOT IN
NOT
NOT
是一个逻辑操作符,用于取反给定的条件或表达式的结果。它可以用于多个地方,例如 WHERE
子句、JOIN
条件和其他逻辑表达式。
语法结构:
NOT condition
SELECT NOT true; -- 返回 false
SELECT NOT false; -- 返回 true
SELECT NOT NULL; -- 返回 NULL
在三值逻辑中,NULL
既不等于 true
,也不等于 false
,而是表示未知或缺失的值。因此,在使用 NOT
操作符时,如果条件或表达式的结果为 NULL
,NOT
将返回 NULL
。
IN 和 NOT IN
IN 运算符用来判断表达式的值是否位于给出的列表中;如果是,返回值为 1,否则返回值为 0。
NOT IN 用来判断表达式的值是否不存在于给出的列表中;如果不是,返回值为 1,否则返回值为 0。
IN 和 NOT IN 的语法结构:
SELECT column1, column2, ...
FROM table_name
WHERE column_name IN / NOT IN (value1, value2, ...);
column_name
是要匹配的列名,(value1, value2, ...)
是要匹配列中的值的列表。查询将返回在列中匹配任一值的行。
-
这些操作符对于在
WHERE
子句中过滤数据非常有用,以便针对特定的值或值列表进行查询。 -
值列表可以是一个具体的值,也可以是一个子查询的结果。
'''
名为 `employees` 的表,其中包含员工的信息,
查询部门编号为 1、3 和 5 的员工
'''
SELECT *
FROM employees
WHERE department_id IN (1, 3, 5);
对空值NULL的处理
当 IN 运算符的两侧有一个为空值 NULL 时,如果找不到匹配项,则返回值为 NULL;如果找到了匹配项,则返回值为 1。
当 NOT IN 运算符的两侧有一个为空值 NULL 时,如果找不到匹配项,则返回值为 NULL;如果找到了匹配项,则返回值为 0
- NULL != NULL
LIKE
LIKE
是一个用于模式匹配的操作符。它通常与 [[Filtering data in Tables#WHERE query by condition|WHERE]] 子句一起使用,用于在查询中选择满足指定模式条件的数据。
语法格式:
column_name LIKE pattern [ESCAPE escape_character]
LIKE
运算符使用通配符来匹配字符串。常用的通配符有两个:
%
:代表零个或多个字符。_
:代表一个字符。
-- 匹配以 "A" 开头的字符串
SELECT column_name
FROM table_name
WHERE column_name LIKE 'A%';
-- 匹配以 "ab" 结尾的字符串
SELECT column_name
FROM table_name
WHERE column_name LIKE '%ab';
-- 匹配包含 "abc" 的字符串
SELECT column_name
FROM table_name
WHERE column_name LIKE '%abc%';
-- 匹配以 "a" 开头,以 "b" 结尾,且中间有一个字符的字符串
SELECT column_name
FROM table_name
WHERE column_name LIKE 'a_b';
ESCAPE
是用于指定转义字符的关键字,用于指定在进行模式匹配时要使用的自定义转义字符。
有时需要在模式中匹配这些占位符本身,而不是作为通配符。此时需要使用 反斜杠符号 ‘\’ 或 ESCAPE
关键字来指定一个转义字符来转义这些占位符。
'''查询包含 '_20' 的数据'''
# 直接使用反斜杠
SELECT * FROM product WHERE serial LIKE '%/_20%';
# 使用ESCAPE关键字
SELECT * FROM product WHERE serial LIKE '%$_20%' ESCAPE '$';
SELECT * FROM product WHERE serial LIKE '%_20%' ESCAPE '\' ;