运算符与 SQL 的正则表达式

本文笔记根据【b站 尚硅谷-宋红康 MySQL 课程】整理。笔记和代码存储在我的 GitHub 库中 github.com/isKage/sql-notes。

1 算术运算符

包括：+, -, *, /, % 加、减、乘、除、取模运算

1
2
3
4
5
SELECT a + b;
SELECT a - b;
SELECT a * b;
SELECT a / b; -- or SELECT a DIV b;
SELECT a % b; -- or SELECT a MOD b;

1.1 加减运算 + -

  • int + int = int float + float = float 但 int + float = float

  • 只针对数值型计算，若为字符串，则先转化，若转换失败，则按照 0 计算

1
2
3
4
5
6
7
mysql> SELECT 100 + 1.2, '3' + 4, '3' + '4', 'a' + 3;
+-----------+---------+-----------+---------+
| 100 + 1.2 | '3' + 4 | '3' + '4' | 'a' + 3 | -- 'a' 转换失败，按照 0 计算
+-----------+---------+-----------+---------+
|     101.2 |       7 |         7 |       3 |
+-----------+---------+-----------+---------+
1 row in set, 1 warning (0.00 sec)

1.2 乘除运算 * /

  • int * int = int int * float = float 而 / 除法最后一定是 float 类型结果
  • number / 0 = NULL 除以 0 则为 NULL
1
2
3
4
5
6
7
mysql> SELECT 100 * 1, 100 * 1.0, 100 / 1, 100 DIV 0;
+---------+-----------+----------+-----------+
| 100 * 1 | 100 * 1.0 | 100 / 1  | 100 DIV 0 |
+---------+-----------+----------+-----------+
|     100 |     100.0 | 100.0000 |      NULL |
+---------+-----------+----------+-----------+
1 row in set, 1 warning (0.00 sec)

1.3 取模运算 %

  • A % B 表示 A 除以 B 的余数
1
2
3
4
5
6
7
mysql> SELECT 100 % 20, 100 % 99;
+----------+----------+
| 100 % 20 | 100 % 99 |
+----------+----------+
|        0 |        1 |
+----------+----------+
1 row in set (0.00 sec)

2 比较运算符

比较结果为真返回1

比较结果为假返回0

其他情况返回 NULL

包括：=, <=>, <>(!=), <, <=, >, >= 等号、安全等号、不等号、小于、小于等于、大于、大于等于

2.1 等号比较 =

判断等号两边的值、字符串或表达式是否相等，如果相等则返回 1，不相等则返回 0

  • 字符串与字符串比较，值与值比较
  • 若为字符串与值比较，则将字符串转换为值 int = str -> int = int(str) 注意：转换失败则为 0
  • 有一个为 NULL 则返回 NULL
1
2
3
4
5
6
7
mysql> SELECT 2 = 2, 2 = 3, 2 = '2', 'bc' = 'bc', 0 = 'a';
+-------+-------+---------+-------------+---------+
| 2 = 2 | 2 = 3 | 2 = '2' | 'bc' = 'bc' | 0 = 'a' | -- 'a' 转换失败则为 0
+-------+-------+---------+-------------+---------+
|     1 |     0 |       1 |           1 |       1 |
+-------+-------+---------+-------------+---------+
1 row in set, 1 warning (0.02 sec)

2.2 安全等号 <=>

<=> 会把 NULL 当作一个特殊的元素，参与比较，不再返回 NULL 。其他返回结果与等于运算符相同。

  • 安全等号 <=> 两边的操作数的值都为NULL 时，返回的结果为 1 。
  • 安全等号 <=> 一边为 NULL ，一边不为 NULL 时，返回 0 。
1
2
3
4
5
6
7
mysql> SELECT 1 = NULL, '' = NULL, NULL = NULL, 1 <=> NULL, NULL <=> NULL;
+----------+-----------+-------------+------------+---------------+
| 1 = NULL | '' = NULL | NULL = NULL | 1 <=> NULL | NULL <=> NULL |
+----------+-----------+-------------+------------+---------------+
|     NULL |      NULL |        NULL |          0 |             1 |
+----------+-----------+-------------+------------+---------------+
1 row in set (0.00 sec)

2.3 不等于比较 != or <>

判断等号两边的值、字符串或表达式是否不相等，如果不相等则返回 1，相等则返回 0

  • 与等号比较类似，面对 NULL 无论比较结果如何，最后均返回 NULL
1
2
3
4
5
6
7
mysql> SELECT 2 != 2, 2 != 3, 2 != '2', 'bc' != 'bc', 0 !='a', '' != NULL;
+--------+--------+----------+--------------+---------+------------+
| 2 != 2 | 2 != 3 | 2 != '2' | 'bc' != 'bc' | 0 !='a' | '' != NULL |
+--------+--------+----------+--------------+---------+------------+
|      0 |      1 |        0 |            0 |       0 |       NULL |
+--------+--------+----------+--------------+---------+------------+
1 row in set, 1 warning (0.01 sec)

2.4 大于小于 >, <, >=, <=

性质和等于 =，不等于 != or <> 相同

  • 比较结果为真返回1 比较结果为假返回0 其他情况返回 NULL
  • 面对 NULL 无论比较结果如何，最后均返回 NULL

3 其他类型运算符

3.1 空运算符 ISNULL, IS NULL

是否为 NULL ，如果为 NULL 则返回 1 ，否则返回 0

1
2
3
4
5
6
7
mysql> SELECT NULL IS NULL, ISNULL(NULL), ISNULL('a'), 1 IS NULL;
+--------------+--------------+-------------+-----------+
| NULL IS NULL | ISNULL(NULL) | ISNULL('a') | 1 IS NULL |
+--------------+--------------+-------------+-----------+
|            1 |            1 |           0 |         0 |
+--------------+--------------+-------------+-----------+
1 row in set (0.00 sec)

结合之前的安全等号，从表中寻找 NULL 有如下方法 （注：等号，不等号，大小于符号无法处理空值问题）

1
2
3
4
SELECT email, commission_pct FROM employees WHERE commission_pct IS NULL;
SELECT email, commission_pct FROM employees WHERE commission_pct <=> NULL;
SELECT email, commission_pct FROM employees WHERE ISNULL(commission_pct);
SELECT email, commission_pct FROM employees WHERE commission_pct = NULL; -- 查询失败

3.2 非空运算符 IS NOT NULL

是否为 NULL ，如果为 NULL 则返回 0 ，否则返回 1

1
2
3
4
5
6
7
mysql> SELECT NULL IS NOT NULL, 'a' IS NOT NULL, 1 IS NOT NULL;
+------------------+-----------------+---------------+
| NULL IS NOT NULL | 'a' IS NOT NULL | 1 IS NOT NULL |
+------------------+-----------------+---------------+
|                0 |               1 |             1 |
+------------------+-----------------+---------------+
1 row in set (0.00 sec)

3.3 最小值运算符 LEAST()

返回 LEAST(a1, a2, ..., ai, ..., an) 中的最小值

  • 当 ai 是 int 或者 float 时，LEAST 将返回其中最小的值
  • 当 ai 为 str 时，返回字母表中顺序最靠前的字符
  • 当列表中有 NULL 时，返回值为 NULL
1
2
3
4
5
6
7
mysql> SELECT LEAST(1, 2), LEAST(1.2, 3), LEAST('b', 'ac'), LEAST(1, NULL);
+-------------+---------------+------------------+----------------+
| LEAST(1, 2) | LEAST(1.2, 3) | LEAST('b', 'ac') | LEAST(1, NULL) |
+-------------+---------------+------------------+----------------+
|           1 |           1.2 | ac               |           NULL |
+-------------+---------------+------------------+----------------+
1 row in set (0.00 sec)

3.4 最大值运算符 GREATEST()

返回 GREATEST(a1, a2, ..., ai, ..., an) 中的最大值

  • 当 ai 是 int 或者 float 时，GREATEST 将返回其中最大的值
  • 当 ai 为 str 时，返回字母表中顺序最靠后的字符
  • 当列表中有 NULL 时，返回值为 NULL
1
2
3
4
5
6
7
mysql> SELECT GREATEST(1, 2), GREATEST(1.2, 3), GREATEST('b', 'ac'), GREATEST(1, NULL);
+----------------+------------------+---------------------+-------------------+
| GREATEST(1, 2) | GREATEST(1.2, 3) | GREATEST('b', 'ac') | GREATEST(1, NULL) |
+----------------+------------------+---------------------+-------------------+
|              2 |              3.0 | b                   |              NULL |
+----------------+------------------+---------------------+-------------------+
1 row in set (0.01 sec)

3.5 判断是否区间中的值 BETWEEN ... AND ...

寻找位于 [a, b] 中与否，即大于等于 a 小于等于 b

1
2
3
SELECT 列名1, 列名2
FROM 表名
WHERE 列名 BETWEEN a AND b;

例如，是则为 1 否则返回 0

1
2
3
4
5
6
7
mysql> SELECT 5 BETWEEN 1 AND 10, 0.4 BETWEEN 3 AND 6, 'x' BETWEEN 'a' AND 'z';
+--------------------+---------------------+-------------------------+
| 5 BETWEEN 1 AND 10 | 0.4 BETWEEN 3 AND 6 | 'x' BETWEEN 'a' AND 'z' |
+--------------------+---------------------+-------------------------+
|                  1 |                   0 |                       1 |
+--------------------+---------------------+-------------------------+
1 row in set (0.00 sec)

完整的查表运用

1
2
3
SELECT last_name, salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500;

3.6 属于运算符 IN

判断元素是否属于列表中，类似的，只要存在 NULL 则返回值为 NULL

1
2
3
4
5
6
7
mysql> SELECT 1 IN (2, 3), 'a' IN ('a', 'b'), NULL IN ('', 2), 'a' IN ('a', NULL);
+-------------+-------------------+-----------------+--------------------+
| 1 IN (2, 3) | 'a' IN ('a', 'b') | NULL IN ('', 2) | 'a' IN ('a', NULL) |
+-------------+-------------------+-----------------+--------------------+
|           0 |                 1 |            NULL |                  1 |
+-------------+-------------------+-----------------+--------------------+
1 row in set (0.00 sec)

3.7 不属于运算符 NOT IN

判断元素是否不属于列表，类似的，只要存在 NULL 则返回值为 NULL

1
2
3
4
5
6
7
mysql> SELECT 1 NOT IN (2, 3), NULL NOT IN (1, 2), 'a' NOT IN (1, NULL);
+-----------------+--------------------+----------------------+
| 1 NOT IN (2, 3) | NULL NOT IN (1, 2) | 'a' NOT IN (1, NULL) |
+-----------------+--------------------+----------------------+
|               1 |               NULL |                 NULL |
+-----------------+--------------------+----------------------+
1 row in set, 1 warning (0.01 sec)

3.8 模糊匹配 LIKE

模糊匹配字符串，如果满足条件则返回 1 ，否则返回 0 。同样，遇 NULL 则 NULL

  • 通配符：
1
2
'%' -- 匹配 0 个或多个字符。
'_' -- 只能匹配一个字符。
1
2
3
SELECT first_name
FROM employees
WHERE first_name LIKE 'S%';  -- 匹配 first_name 字段以 S 开头的字符串

1
2
3
SELECT last_name
FROM employees
WHERE last_name LIKE '_o%';  -- 匹配 last_name 字段形如 '一个字母 + o' 开头的字符串

3.9 利用 ESCAPE 处理特殊字符

当需要查询的元素中包含特殊字符，如 %, _, $ 等。可以使用 \ 或其他字符（除 \ 其他字符需要 ESCAPE 指明为转义字符）处理。

  • 例如：我们需要查询形如 增长50%左右 ，即匹配以任意字符开头，接着是 50%，最后是任意字符的字符串。所以可以采取 '%50\%%' 或 '%50$%%' ESCAPE '$'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT job_id
FROM jobs
WHERE job_id LIKE 'IT$_%' ESCAPE '$';  -- 指定 '$' 为转义字符，'$' 后的 '_' 为普通字符

SELECT job_id
FROM jobs
WHERE job_id LIKE 'IT@_%' ESCAPE '@';  -- 指定 '@' 为转义字符，'@' 后的 '_' 为普通字符

+---------+
| job_id  |
+---------+
| IT_PROG |
+---------+
1 row in set (0.00 sec)

4 逻辑运算符

逻辑运算符的返回结果为 1, 0, NULL

逻辑运算符大多符合直觉，并不复杂

4.1 非 运算符 NOT 或 !

  • !0 -> 1 值为0时返回1
  • !非0 -> 0 值为非0值时返回0
  • !NULL -> NULL 值为NULL时，返回NULL
1
2
3
4
5
6
7
mysql> SELECT NOT 1, NOT 0, NOT(1+1), NOT !1, NOT NULL;
+-------+-------+----------+--------+----------+
| NOT 1 | NOT 0 | NOT(1+1) | NOT !1 | NOT NULL |
+-------+-------+----------+--------+----------+
|     0 |     1 |        0 |      1 |     NULL |
+-------+-------+----------+--------+----------+
1 row in set, 1 warning (0.02 sec)

4.2 与 运算符 AND 或 &&

  • 非0非NULL && 非0非NULL -> 1 所有值均为非0值，并且都不为NULL时，返回1

  • 0 && ... -> 0一个值或者多个值为0时则返回0

  • NULL && ... -> NULL否则返回NULL

1
2
3
4
5
6
7
mysql> SELECT 2 AND 3, 0 AND 1, 0 AND NULL, 1 AND NULL;
+---------+---------+------------+------------+
| 2 AND 3 | 0 AND 1 | 0 AND NULL | 1 AND NULL |
+---------+---------+------------+------------+
|       1 |       0 |          0 |       NULL |
+---------+---------+------------+------------+
1 row in set (0.00 sec)

例如：查表

1
2
3
4
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >=10000
  AND job_id LIKE '%MAN%';

4.3 或 运算符 OR 或 ||

  • 非0非NULL || 非NULL -> 1 值都不为NULL，并且任何一个值为非0值时，则返回1
  • 0 || 0 -> 0 否则返回0；
  • 非0非NULL || NULL -> 1 当一个值为NULL，并且另一个值为非0值时，返回1，否则为NULL
  • NULL || NULL 当两个值都为NULL时，返回NULL
1
2
3
4
5
6
7
mysql> SELECT 2 OR 3, 2 OR 0, 3 OR NULL, 0 || NULL, NULL || NULL;
+--------+--------+-----------+-----------+--------------+
| 2 OR 3 | 2 OR 0 | 3 OR NULL | 0 || NULL | NULL || NULL |
+--------+--------+-----------+-----------+--------------+
|      1 |      1 |         1 |      NULL |         NULL |
+--------+--------+-----------+-----------+--------------+
1 row in set, 2 warnings (0.01 sec)

注：AND 的优先级高于 OR ，先处理与逻辑，再考虑或逻辑

4.4 异或 运算符 XOR

  • 存在 NULL时，则返回 NULL
  • 都非0非NULL，则返回 0
  • 0 XOR 0 -> 0 都 0 则返回 0
  • 一个为 0，另一个 非0非NULL ，则返回 1
1
2
3
4
5
6
7
mysql> SELECT NULL XOR 1, 2 XOR 3, 0 XOR 0, 0 XOR 4;
+------------+---------+---------+---------+
| NULL XOR 1 | 2 XOR 3 | 0 XOR 0 | 0 XOR 4 |
+------------+---------+---------+---------+
|       NULL |       0 |       0 |       1 |
+------------+---------+---------+---------+
1 row in set (0.00 sec)

5 位运算符

位运算符会先将操作数变成二进制数，然后进行位运算，最后将计算结果从二进制变回十进制数

位运算在所有语言中均类似，不过多介绍。

1
2
3
4
5
6
SELECT a & b;   -- 按位与
SELECT a | b;   -- 按位或
SELECT a ^ b;   -- 按位异或
SELECT ~ a;     -- 按位取反
SELECT a >> 2;  -- 按位右移
SELECT a << 2;  -- 按位左移

6 优先级

优先级越高，越先运算

优先级由高到低 1->8

建议使用多个计算式，均使用 ()

7 正则表达式

正则表达式在各种语言中均类似，下面介绍 SQL 语言中 REGEXP 的使用

7.1 REGEXP 的使用

1
2
3
SELECT 列名1, 列名2, 列名3, ...
FROM 表名
WHERE 列名 REGEXP 匹配条件(正则表达式);

7.2 常见正则表达式

7.2.1 匹配起始字符 ^

  • 表达式 ^K 以 K 起始的字符
1
SELECT * FROM employees WHERE last_name REGEXP '^K';

7.2.2 匹配结尾字符 $

  • 表达式 t$ 以 t 结尾的字符
1
SELECT * FROM employees WHERE first_name REGEXP 't$';

7.2.3 匹配单个字符 .

  • 表达式 a.b 包含字母 a 和 b 且 a 和 b 之间只有一个字符
1
SELECT * FROM employees WHERE last_name REGEXP 'a.b';

7.2.4 匹配多个字符 + *

  • 表达式 ba+ 包含字符串 ...ba... 其中 + 要求多个 a

+ 要求 1次或多次

1
SELECT * FROM employees WHERE last_name REGEXP '^ba+'; -- 包含 ba... ^b 要求必须以 b 开头

  • 表达式 b*an 包含 ...b...an 其中 * 要求多个 b

* 要求 0次或多次

7.2.5 匹配特定字符串 ''

  • 表达式 'un' 包含 un 即可
1
SELECT * FROM employees WHERE last_name REGEXP 'un';

注：可以使用逻辑运算符

1
SELECT * FROM employees WHERE last_name REGEXP 'un|am';  -- 包含 un 或 am

7.2.6 匹配集合中任一元素 []

  • 表达式 [orz] 包含o r z 任一一个即可
1
SELECT * FROM employees WHERE last_name REGEXP '[orz]';

7.2.7 匹配完全由集合外的元素构成 [^]

  • 表达式 [^A-Y] 表示只有当元素是有A-Y以外的元素构成时才被查询到。注意：大小写敏感。
1
SELECT * FROM employees WHERE email REGEXP '[^A-Y]'; -- 查询存在 小写字母 或 Z

7.2.8 使用 {n,} 或者 {n,m} 来指定字符串连续出现的次数

  • 表达式 a{2,} 表示字母 a 连续出现至少2次
  • 表达式 a{2,4} 表示字母 a 连续出现最少2次，最多不能超过4次
1
SELECT salary FROM employees WHERE salary REGEXP '0{2,3}'; -- 连续 2-3 个 0