25.2 MySQL 运算符

1. DUAL伪表

在MySQL中, DUAL是一个虚拟的表, 实际上并不存在于数据库中, 是为了方便查询或其他目的而创建的临时表.
它通常用于执行一些不依赖于实际表的查询操作, 例如获取系统日期或时间.

由于DUAL表并不实际存储数据, 它只是一个虚拟的表结构, 用于执行查询操作.
以下是一个使用DUAL伪表获取当前日期和时间的示例: SELECT NOW() FROM DUAL;

请注意, 在使用DUAL伪表时, 你实际上并不需要从表中检索任何数据, 因为DUAL只是一个虚拟表.
因此, 你可以将查询中的FROM子句替换为DUAL, 以执行不依赖于实际表的查询操作.

使用DUAL的原因主要有以下几点:
* 1. 简化计算: 通过在SELECT语句中使用DUAL, 可以方便地计算表达式或执行函数, 而无需创建临时表或执行子查询.
* 2. 统一语法: 在某些情况下, 使用DUAL可以使SQL语句保持统一的语法结构, 简化代码逻辑.
总之, 使用FROM DUAL子句是为了满足语法完整性的要求, 并确保查询的准确性和可读性.
1
2
3
4
5
6
7
8
9
10
11
12
13

-- 获取当前时间:
mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2024-02-19 15:25:38 |
+---------------------+
1 row in set (0.00 sec)

-- 使用DUAL伪表统一语法:
mysql> SELECT NOW() FROM DUAL;
+---------------------+
| NOW()               |
+---------------------+
| 2024-02-19 15:26:19 |
+---------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

2. 算术运算

算术运算符主要用于数学运算, 可以连接运算符前后的两个数值或表达式, 对数值或表达式进行运算.
1

注意事项:
* 1. 当在SQL中进行算术运算时, 操作数必须是数字类型, 对于非整型的表达式会进行隐式转换, 尝试将其转换为数值类型.
     如果转换成功, 则使用转换后的数值进行计算; 如果转换失败, MySQL通常会将该表达式按0计算.
     这种行为是为了确保查询的执行不会因为非数值类型的表达式而中断.

     隐式转换可能会导致一些意想不到的结果, 尤其是当转换失败时.
     因此, 在编写MySQL查询时, 最好确保使用的数据类型是匹配的, 以避免潜在的问题.
 
* 2. 以下类型的字符串可以被转为数值:
     整数字符串: 由数字字符(0-9)组成的字符串, 可以表示一个整数. 例如, "123"可以被转为数值123.
     浮点数字符串: 由数字字符和小数点组成的字符串, 可以表示一个浮点数(小数). 例如, "3.14"可以被转为数值3.14.
     科学计数法字符串: 使用科学计数法表示的数值字符串, 例如, "1.23E4"可以被转为数值12300.0.
     
     字符串中的空格和其他非数字字符可能会导致转换失败.
     此外, 某些特殊情况下, 如超出数值类型范围或格式不符合要求, 转换也可能失败.

* 3. 在数学运算中, 0不能用作除数; 在MySQL中, 一个数除以0为NULL.
* 4. 与浮点数进行计算得到的结果是浮点数, 除法运算的结果是浮点数(不管是否能除尽, 结果都为一个浮点数).
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

2.1 加减法运算

-- 加法运算:
mysql> SELECT 1 + 2;
+-------+
| 1 + 2 |
+-------+
|     3 |
+-------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8

-- 使用伪表满足语法完整性:
mysql> SELECT 1 + 2 FROM DUAL;
+-------+
| 1 + 2 |
+-------+
|     3 |
+-------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8

-- 数字类型字符串可转换为数字:
mysql> SELECT 1 + '1' FROM DUAL;
+---------+
| 1 + '1' |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8

-- 非数字类型的字符会转换成0参与计算:
mysql> SELECT 1 + 'A' FROM DUAL;
+---------+-----------+
| 1 + 'A' | 'A' + 'A' |
+---------+-----------+
|       1 |         0 |
+---------+-----------+
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7
8

-- 减法运算:
mysql> SELECT 1 - 1 FROM DUAL;
+-------+
| 1 - 1 |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

-- 数字类型字符串可转换为数字:
mysql> SELECT 1 - '1' FROM DUAL;
+---------+
| 1 - '1' |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

2.2 乘除法运算

mysql> SELECT 100 * 1, 100 * 1.0, 100 / 1.0, 100 / 2, 100 + 2 * 5 / 2, 100 / 3, 100 / 0 FROM DUAL;
+---------+-----------+-----------+---------+-----------------+---------+---------+
| 100 * 1 | 100 * 1.0 | 100 / 1.0 | 100 / 2 | 100 + 2 * 5 / 2 | 100 / 3 | 100 / 0 |
+---------+-----------+-----------+---------+-----------------+---------+---------+
|     100 |     100.0 |  100.0000 | 50.0000 |        105.0000 | 33.3333 |    NULL |
+---------+-----------+-----------+---------+-----------------+---------+---------+
1 row in set, 1 warning (0.01 sec)
1
2
3
4
5
6
7

2.3 求模运算

mysql> SELECT 10 % 2, 10 % 3 FROM DUAL;
+--------+--------+
| 10 % 2 | 10 % 3 |
+--------+--------+
|     0  |      1 |
+--------+--------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

-- 筛选出员工表中, employee_id是偶数的员工信息(名字, 员工id):
mysql> SELECT first_name, employee_id  FROM employees WHERE employee_id % 2 = 0;
+-------------+-------------+
| first_name  | employee_id |
+-------------+-------------+
| Steven      |         100 |
| Lex         |         102 |
| Bruce       |         104 |
| Valli       |         106 |
| Nancy       |         108 |
| ...         |         ... | -- 省略
| Donald      |         198 |
| Jennifer    |         200 |
| Pat         |         202 |
| Hermann     |         204 |
| William     |         206 |
+-------------+-------------+
54 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

3. 比较运算

3.1 比较运算符

比较运算符: 用来对表达式左边的操作数和右边的操作数进行比较, 
比较的结果为真则返回1, 比较的结果为假则返回0, 其他情况则返回NULL.
比较运算符经常被用来作为SELECT查询语句的条件来使用, 用于返回符合条件的记录.
1
2
3

注意事项: 
* 在SQL中, 比较运算时返回的结果是0和1, 分别表示假和真.
* SQL中赋值符号使用(:=).
* 整型与字符比较时, 会将字符串进行隐式转换成数字, 转换失败则按0计算.
* 字符串之间按ASCII编码表对应的数字进行比较.
1
2
3
4
5

3.1.1 等号运算

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

-- 查询员工表中, 工资为10000的员工信息(名字, 薪资):
mysql>  SELECT first_name, salary FROM employees WHERE salary = 10000;
+------------+----------+
| first_name | salary   |
+------------+----------+
| Peter      | 10000.00 |
| Janette    | 10000.00 |
| Harrison   | 10000.00 |
| Hermann    | 10000.00 |
+------------+----------+
4 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11

3.1.2 安全等于运算

安全等于运算符(<=>)可以用来对NULL进行判断.
其中一个操作数为NULL时, 其返回值为0, 而不为NULL.
在两个操作数均为NULL时, 其返回值为1, 而不为NULL.
1
2
3

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

-- 查询员工表中, 佣金百分比为NULL的员工名字和佣金百分比:
mysql> SELECT first_name, commission_pct FROM employees WHERE commission_pct <=> NULL;
+-------------+----------------+
| first_name  | commission_pct |
+-------------+----------------+
| Steven      |           NULL |
| Neena       |           NULL |
| ...         |           ...  |  -- 省略
| Hermann     |           NULL |
| Shelley     |           NULL |
| William     |           NULL |
+-------------+----------------+
72 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

3.1.3 不等于运算

不等于运算符用于判断两边的数字, 字符串或者表达式的值是否不相等, 如果不相等则返回1, 相等则返回0.
1

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

3.1.4 大于小于运算

-- 查询员工表中, 工资在6000到8000之间的员工信息(名字, 薪资), 不包括6000和8000.
mysql> SELECT first_name, salary FROM employees WHERE salary > 6000 AND salary < 8000;
+-------------+---------+
| first_name  | salary  |
+-------------+---------+
| Ismael      | 7700.00 |
| Jose Manuel | 7800.00 |
| ...         | ...     |  -- 省略
| Kimberely   | 7000.00 |
| Charles     | 6200.00 |
| Susan       | 6500.00 |
+-------------+---------+
19 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

3.2 比较运算关键字

3.2.1 为空运算符

IS NULL或者ISNULL()判断一个值是否为NULL, 如果为NULL则返回1, 否则返回0.
1

-- 查询员工表中, 佣金比例(commission_pct)的值为NULL的员工信息(名字, 佣金比例):
mysql> SELECT first_name, commission_pct FROM employees WHERE commission_pct IS NULL;
+-------------+----------------+
| first_name  | commission_pct |
+-------------+----------------+
| Steven      |           NULL |
| Neena       |           NULL |
| ...         |           ...  |  -- 省略
| Hermann     |           NULL |
| Shelley     |           NULL |
| William     |           NULL |
+-------------+----------------+
72 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

-- 或者:
mysql> SELECT first_name FROM employees WHERE ISNULL(commission_pct);
+-------------+----------------+
| first_name  | commission_pct |
+-------------+----------------+
| Steven      |           NULL |
| Neena       |           NULL |
| ...         |           ...  |  -- 省略
| Hermann     |           NULL |
| Shelley     |           NULL |
| William     |           NULL |
+-------------+----------------+
72 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

3.2.2 不为空运算

IS NOT NULL判断一个值是否为NULL, 如果不为NULL则返回1, 否则返回0.
1

-- 查询员工表中, commission_pct不为NULL的员工信息(名字, 佣金比例):
mysql>  SELECT first_name, commission_pct FROM employees WHERE commission_pct IS NOT NULL;
+-------------+----------------+
| first_name  | commission_pct |
+-------------+----------------+
| John        |           0.40 |
| Karen       |           0.30 |
| Alberto     |           0.30 |
| Gerald      |           0.30 |
| Eleni       |           0.20 |
| ...         |           ...  | -- 省略
| Jack        |           0.20 |
| Kimberely   |           0.15 |
| Charles     |           0.10 |
+-------------+----------------+
35 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

-- 或(先判断是否为NULL, 然后取反, 这个示例是提供一种思路):
mysql> SELECT first_name, commission_pct FROM employees WHERE NOT commission_pct <=> NULL;
+-------------+----------------+
| first_name  | commission_pct |
+-------------+----------------+
| John        |           0.40 |
| Karen       |           0.30 |
| Alberto     |           0.30 |
| Gerald      |           0.30 |
| Eleni       |           0.20 |
| ...         |           ...  | -- 省略
| Jack        |           0.20 |
| Kimberely   |           0.15 |
| Charles     |           0.10 |
+-------------+----------------+
35 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

3.2.3 最小/大运算

-- 比较序列中最大的值(a-z对应97-122):
mysql> SELECT LEAST('g','b','t','m'), GREATEST('g','b','t','m') FROM DUAL;
+------------------------+---------------------------+
| LEAST('g','b','t','m') | GREATEST('g','b','t','m') |
+------------------------+---------------------------+
| b                      | t                         |
+------------------------+---------------------------+
1 row in set (0.01 sec)
1
2
3
4
5
6
7
8

3.2.4 区间取值

使用SQL查询语句中的BETWEEN关键字来筛选一个字段在某个区间内的值时, 这个区间是包含边界值的.
也就是说, 如果字段的值等于边界下限或边界上限, 那么这些值也会被包含在查询结果中.

BETWEEN 的语法如下:
SELECT column_name(s)  
FROM table_name  
WHERE column_name BETWEEN value1 AND value2;
这里, column_name(s)是你想要检索的列名, table_name是表名, value1是范围的开始值(包含), value2是范围的结束值(包含).
1
2
3
4
5
6
7
8

-- 查询员工表中, 工资在6000到8000的员工信息(名字, 薪资):
mysql> SELECT first_name, salary FROM employees WHERE salary BETWEEN 6000 AND 8000;
+-------------+---------+
| first_name  | salary  |
+-------------+---------+
| Bruce       | 6000.00 |
| Ismael      | 7700.00 |
| Jose Manuel | 7800.00 |
| Luis        | 6900.00 |
| Matthew     | 8000.00 |
| ...         | ...     | -- 省略
| Sundita     | 6100.00 |
| Kimberely   | 7000.00 |
| Charles     | 6200.00 |
| Pat         | 6000.00 |
| Susan       | 6500.00 |
+-------------+---------+
24 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

3.2.5 属于/不属于

-- 查询员工表中, 部门编号为10, 20, 30的员工信息(姓名, 部门id):
mysql> SELECT first_name, department_id FROM employees WHERE department_id IN (10, 20, 30);
+------------+---------------+
| first_name | department_id |
+------------+---------------+
| Jennifer   |            10 |
| Michael    |            20 |
| Pat        |            20 |
| Den        |            30 |
| Alexander  |            30 |
| Shelli     |            30 |
| Sigal      |            30 |
| Guy        |            30 |
| Karen      |            30 |
+------------+---------------+
9 rows in set (0.00 sec)

-- 或:
mysql> SELECT first_name, department_id FROM employees WHERE 
department_id = 10 OR department_id = 20 OR department_id = 30;
+------------+---------------+
| first_name | department_id |
+------------+---------------+
| Jennifer   |            10 |
| Michael    |            20 |
| Pat        |            20 |
| Den        |            30 |
| Alexander  |            30 |
| Shelli     |            30 |
| Sigal      |            30 |
| Guy        |            30 |
| Karen      |            30 |
+------------+---------------+
9 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

-- 查询员工表中部门编号不是10, 20, 30的员工信息(姓名, 部门id):
mysql>  SELECT first_name, department_id FROM employees WHERE department_id NOT IN (10, 20, 30);
+-------------+---------------+
| first_name  | department_id |
+-------------+---------------+
| Steven      |            90 |
| Neena       |            90 |
| ...         |            .. |  -- 省略
| Susan       |            40 |
| Hermann     |            70 |
| Shelley     |           110 |
| William     |           110 |
+-------------+---------------+
97 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

3.2.6 模糊查询

在模糊查询中, 有两个特殊符号:
* 1. 百分号(%): 代表多个不确定个数的字符(0个, 1个或多个).
* 2. 下划线(_): 代表一个不确定的字符.
转义说明:
* 1. 可以在特殊符号前面添加\取消转义.
* 2. 可以使用关键字ESCAPE指定取消转义的字符.
1
2
3
4
5
6

-- 查询员工表中,  名字包含字符'a'的员工名字:
mysql> SELECT first_name FROM employees WHERE first_name LIKE '%a%';
+-------------+
| first_name  |
+-------------+
| Neena       |
| Alexander   |
| David       |
| Valli       |
| ...         |  -- 省略
| Hermann     |
| William     |
+-------------+
70 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- 查询员工表中, 名字以'a'开头的员工名字:
mysql> SELECT first_name FROM employees WHERE first_name LIKE 'a%';
+------------+
| first_name |
+------------+
| Alexander  |
| Alexander  |
| Adam       |
| Alberto    |
| Allan      |
| Amit       |
| Alyssa     |
| Alexis     |
| Anthony    |
| Alana      |
+------------+
10 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

-- 查询员工表中, 名字含有字符'a'与字符'e'的员工名字:
-- 两种情况: 1. a在e前面; 2. e在a前面.
mysql> SELECT first_name FROM employees WHERE first_name LIKE '%a%e%' OR first_name LIKE '%e%a%';
+-------------+
| first_name  |
+-------------+
| Neena       |
| Alexander   |
| Daniel      |
| ...         |  -- 省略
| Michael     |
| Hermann     |
+-------------+
29 rows in set (0.00 sec)

-- 或(使用AND让匹配的数据同时满足两个条件):
mysql> SELECT first_name FROM employees WHERE first_name LIKE '%a%' AND first_name LIKE '%e%';
+-------------+
| first_name  |
+-------------+
| Neena       |
| Alexander   |
| Daniel      |
| ...         |  -- 省略
| Michael     |
| Hermann     |
+-------------+
29 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

-- 查询员工表中, 名字的第3个字符是'a'的员工名字:
mysql> SELECT first_name FROM employees WHERE first_name LIKE '__a%';
+------------+
| first_name |
+------------+
| Diana      |
| Adam       |
| Shanta     |
| Clara      |
| Charles    |
| Jean       |
| Alana      |
+------------+
7 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- 查询职位历史表中. (job_history)职位id的第3个字符是_且第4个字符是'a'的职位id:
mysql> SELECT job_id FROM job_history WHERE job_id LIKE '__\_a%';
+------------+
| job_id     |
+------------+
| AC_ACCOUNT |
| AC_ACCOUNT |
| AD_ASST    |
+------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

-- 使用关键字ESCAPE自定义取消转义符号:
mysql>  SELECT job_id FROM job_history WHERE job_id LIKE '__$_a%' ESCAPE '$';
+------------+
| job_id     |
+------------+
| AC_ACCOUNT |
| AC_ACCOUNT |
| AD_ASST    |
+------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

3.2.7 REGEXP正则匹配

正则表达式通常被用来检索或替换那些符合某个模式的文本内容, 根据指定的匹配模式匹配文本中符合要求的特殊字符串.
例如: 从一个文本文件中提取电话号码, 查找一篇文章中重复的单词或者替换用户输入的某些敏感词语等,
这些地方都可以使用正则表达式; 正则表达式强大而且灵活，可以应用于非常复杂的查询.
MySQL中使用REGEXP关键字指定正则表达式的字符匹配模式(RLIKE关键字的效果与其一样).

下表列出了REGEXP操作符中常用字符匹配列表:
1
2
3
4
5
6

* 1. 查询以特定字符或字符串开头的记录信息; '^'匹配以特定字符或者字符串开头的文本.
     例: 查询员工表中, 名字以字母'b'开头的员工名字, SQL语句如下:
1
2

mysql> SELECT first_name FROM employees WHERE first_name REGEXP '^b';
+------------+
| first_name |
+------------+
| Bruce      |
| Britney    |
+------------+
2 rows in set (0.02 sec)

-- 或:
mysql> SELECT first_name FROM employees WHERE first_name RLIKE '^b';
+------------+
| first_name |
+------------+
| Bruce      |
| Britney    |
+------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

* 2. 查询以特定字符或字符串结尾的记录信息; '$'匹配以特定字符或者字符串结尾的文本.
     查询员工表中, 名字以字母't'结尾的员工名字, SQL语句如下:
1
2

mysql> SELECT first_name FROM employees WHERE first_name REGEXP 't$';
+------------+
| first_name |
+------------+
| Amit       |
| Pat        |
+------------+
2 rows in set (0.00 sec)

-- 或:
mysql> SELECT first_name FROM employees WHERE first_name RLIKE 't$';
+------------+
| first_name |
+------------+
| Amit       |
| Pat        |
+------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

* 3. 用符号"."来替代字符串中的任意一个字符.
     查询员工表中, 名字中有字母'a'与'c', 且两个字母之间还有一个字母的员工名字, SQL语句如下:
1
2

mysql> SELECT first_name FROM employees WHERE first_name REGEXP 'a.c';
+------------+
| first_name |
+------------+
| Nancy      |
| Vance      |
+------------+
2 rows in set (0.00 sec)

-- 或:
mysql> SELECT first_name FROM employees WHERE first_name RLIKE 'a.c';
+------------+
| first_name |
+------------+
| Nancy      |
| Vance      |
+------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

* 4. 使用"*"和"+"来匹配多个字符; 星号'*'匹配的字符包括0次. 加号'+'匹配的字符至少一次.
     查询员工表中, 名字以字母'b'开头且后面会出现字母'e'的员工名字, SQL语句如下:
1
2

mysql> SELECT first_name FROM employees WHERE first_name RLIKE '^b.*e+.*';
+------------+
| first_name |
+------------+
| Bruce      |
| Britney    |
+------------+
2 rows in set (0.00 sec)
-- 或:
mysql> SELECT first_name FROM employees WHERE first_name RLIKE '^b.*e.*';
+------------+
| first_name |
+------------+
| Bruce      |
| Britney    |
+------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

'^b.*e+.*'和'^b.*e.*'这两个表达式, 它们都达到了相同的目的, 即匹配以b开头并且包含至少一个e的字符串.
如果您想要更明确地表示e至少出现一次, 可以使用+, 这样其他阅读代码的人可以更清楚地理解您的意图.

然而, 如果认为"+"在这种情况下是多余的, 或者为了简化表达式和提高可读性, 可以选择不使用它.
在正则表达式中, ".*" 已经允许了任意数量的任意字符, 包括零个或多个e, 所以即使省略了"+", 表达式仍然有效.

综上所述, 两种方式都可以使用, 选择哪种方式取决于个人偏好和对代码可读性的考虑.
在实际应用中, 如果团队成员都理解并同意您的选择, 那么任何一种方式都是可行的.
1
2
3
4
5
6
7
8

* 5. 匹配指定字符串; 在查询文本中匹配字符串.
     在正则表达式中, 如果想要匹配指定的字符串, 并且这些字符串之间是'或'的关系(即匹配任意一个), 
     可以使用竖线'|'作为分隔符来列出多个可能的匹配项.
1
2
3

-- 查询员工表中, 名字中有字符'it'的员工名字:
mysql> SELECT first_name FROM employees WHERE first_name REGEXP 'it';
+------------+
| first_name |
+------------+
| Amit       |
| Sundita    |
| Nandita    |
| Britney    |
+------------+
4 rows in set (0.00 sec)

-- 查询员工表中, 名字中有字符'it'或'ii'的员工名字:
mysql> SELECT first_name FROM employees WHERE first_name REGEXP 'it|ic';
+------------+
| first_name |
+------------+
| Michael    |
| Patrick    |
| Amit       |
| Sundita    |
| Nandita    |
| Britney    |
| Michael    |
+------------+
7 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

* 6. 匹配指定字符中的任意一个, 使用方括号'[]'指定一个字符集合, 匹配其中任何一个字符.
     查询员工表中, 名字中有字符'a'或'i'的员工名字,  SQL语句如下:
1
2

mysql> SELECT first_name FROM employees WHERE first_name REGEXP '[ai]';
+-------------+
| first_name  |
+-------------+
| Neena       |
| Alexander   |
| David       |
| ...         |  -- 省略
| William     |
+-------------+
88 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12

* 7. 匹配指定字符以外的字符, "[^字符集合]"匹配不在指定集合中的任何字符.
     查询员工表中, 名字中不包含字符'a-e'的员工名字, SQL语句如下:
1
2

-- 错误示例(很容易犯错):
-- '[^a-e]': 匹配除a-e以外的字符.
mysql> SELECT first_name FROM employees WHERE first_name REGEXP '[^a-e]';
+-------------+
| first_name  |
+-------------+
| Steven      |
| Neena       |
| Tayler      |
| ...         | -- 省略
| William     |
+-------------+
107 rows in set (0.00 sec)

-- 在正则表达式中, '[^a-e]'表示一个字符类, 这个字符类中的^符号用作否定前缀, 意味着它会匹配任何不在a-e范围内的字符.
-- [^a-e]: 会匹配文本中的每个字符, 字符串"Steven"中('S', 't', 'e', 'v', 'e', 'n')都不是在 'a' 到 'e' 的范围内.
-- 所以, 正则表达式 '[^a-e]' 会匹配到 "Steven".

-- 正确示例:
-- 对于严格排除包含指定字符范围的名字, 可以使用其他方法, 例如使用正则表达式的边界匹配(如^和$):
mysql> SELECT first_name FROM employees WHERE first_name REGEXP '^[^a-e]+$';
+------------+
| first_name |
+------------+
| John       |
| Luis       |
| Guy        |
| TJ         |
| Ki         |
| John       |
| John       |
| Winston    |
| Timothy    |
+------------+
9 rows in set (0.00 sec)

-- ^: 这个符号表示匹配字符串的开始位置.
-- [^a-e]: 这是一个字符类(character class), 其中的^用作否定前缀, 意味着匹配任何不是在a-e范围内的字符.
-- +: 这个符号表示匹配前面的模式(在这里是 [^a-e])一次或多次.
-- 所以, 这个正则表达式会匹配一个或多个连续的, 不是 'a' 到 'e' 之间的字符.
-- $: 这个符号表示匹配字符串的结束位置.

-- 或:
mysql> SELECT first_name FROM employees WHERE first_name NOT RLIKE '[A-E]';
+------------+
| first_name |
+------------+
| John       |
| Luis       |
| Guy        |
| TJ         |
| Ki         |
| John       |
| John       |
| Winston    |
| Timothy    |
+------------+
9 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

* 8. 使用{n,}或者{n,m}来指定字符串连续出现的次数.
     "字符串{n,}"表示至少匹配n次前面的字符; "字符串{n,m}"表示匹配前面的字符串不少于n次, 不多于m次.
     例如, a{2,}表示字母a连续出现至少2次, 也可以大于2次; a{2,4}表示字母a连续出现最少2次, 最多不能超过4次.
     查询员工表中, 名字中字符'e'出现2-3次的员工名字;
1
2
3
4

-- 注意: 不要在逗号和数字之间加空格.
mysql> SELECT first_name FROM employees WHERE first_name REGEXP 'e{2, 3}';
ERROR 3692 (HY000): Incorrect description of a {min,max} interval.

mysql> SELECT first_name FROM employees WHERE first_name REGEXP 'e{2,3}';
+------------+
| first_name |
+------------+
| Neena      |
+------------+
1 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

在没有使用特殊符号的情况下, LIKE和REGEXP的表现会有所不同.
LIKE操作符: 如果使用LIKE进行匹配, 并且目标字符串出现在文本的中间, 它通常不会被找到并无法返回相应的行.
因为LIKE使用的是简单的通配符规则, 它主要关注字符串的开头和结尾是否匹配给定的模式.
例如, 如果我们要查找以"apple"开头的文本, 可以使用LIKE操作符进行匹配.
但是, 如果文本是"bananaappleorange", 虽然"apple"出现在中间, 但LIKE操作符不会返回这一行.

REGEXP操作符: 如果使用REGEXP进行匹配, 并且目标字符串出现在文本的中间, 它可以被找到并返回相应的行.
因为REGEXP基于正则表达式, 它可以识别并匹配各种复杂的模式, 包括字符串的中间部分.
例如, 如果我们要查找包含"apple"的文本, 可以使用REGEXP操作符进行匹配.
在文本"bananaappleorange"中, "apple"出现在中间, 但REGEXP仍然会返回这一行, 因为它能够识别并匹配字符串中的任意模式.
总结来说, LIKE更适合用于简单的模式匹配, 而REGEXP则更适合用于复杂的模式匹配和文本搜索.
在处理包含目标字符串位于中间的情况时, REGEXP能够提供更灵活和全面的匹配结果.
1
2
3
4
5
6
7
8
9
10
11
12

4. 逻辑运算

逻辑运算:
* 1. 逻辑与 AND (&&): 当两个布尔值同时为true时, 返回true; 否则, 返回false.
* 2. 逻辑或 OR  (||): 当两个布尔值中至少一个为true时, 返回true; 否则, 返回false.
* 3. 逻辑非 NOT (!) : 对单个布尔值进行取反操作, 即将true变为false, 将false变为true.
* 4. 逻辑异或 XOR   : 当两个布尔值一个为true, 一个为false时, 返回true.
     
以下是AND, OR, NOT和XOR四种逻辑运算的真值表格:
1
2
3
4
5
6
7

| A | B | AND (A && B) | OR (A || B) | NOT (!A) | XOR (A ^ B) |
| ------- | ------- | -------------- | ------------- | ---------- | ------------- |
| true | true | true | true | false | false |
| true | false | false | true | false | true |
| false | true | false | true | true | true |
| false | false | false | false | true | false |

-- 查询员工表中, 部门id为50, 并且工资高于6000的员工信息(名字, 工资, 部门id):
mysql> SELECT first_name, salary, department_id FROM employees WHERE department_id = 50 AND salary > 6000;
+------------+---------+---------------+
| first_name | salary  | department_id |
+------------+---------+---------------+
| Matthew    | 8000.00 |            50 |
| Adam       | 8200.00 |            50 |
| Payam      | 7900.00 |            50 |
| Shanta     | 6500.00 |            50 |
+------------+---------+---------------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

-- 查询员工表中, 部门id为10或20的员工信息(名字, 部门id):
mysql> SELECT first_name, department_id FROM employees WHERE department_id = 10 OR department_id = 20;
+------------+---------------+
| first_name | department_id |
+------------+---------------+
| Jennifer   |            10 |
| Michael    |            20 |
| Pat        |            20 |
+------------+---------------+
3 rows in set (0.03 sec)
1
2
3
4
5
6
7
8
9
10

-- 查询员工表中, 工资不在6000 - 8000 之间的员工信息(名字, 工资):
mysql> SELECT first_name, salary FROM employees WHERE salary NOT BETWEEN 6000 AND 8000;
+------------+----------+
| first_name | salary   |
+------------+----------+
| Steven     | 24000.00 |
| Neena      | 17000.00 |
| Lex        | 17000.00 |
| ...        |      ... |  -- 省略
| Jennifer   |  4400.00 |
| Michael    | 13000.00 |
| Hermann    | 10000.00 |
| Shelley    | 12000.00 |
| William    |  8300.00 |
+------------+----------+
83 rows in set (0.00 sec)

-- 查询员工表中, 佣金提成不为NULL的员工信息(名字, 佣金提成):
-- SELECT first_name, commission_pct FROM employees WHERE commission_pct IS NOT NULL;
mysql> SELECT first_name, commission_pct FROM employees WHERE NOT commission_pct <=> NULL;
+-------------+----------------+
| first_name  | commission_pct |
+-------------+----------------+
| John        |           0.40 |
| Karen       |           0.30 |
| Alberto     |           0.30 |
| Gerald      |           0.30 |
| ...         |            ... | -- 省略
| Alyssa      |           0.25 |
| Jonathon    |           0.20 |
| Jack        |           0.20 |
| Kimberely   |           0.15 |
| Charles     |           0.10 |
+-------------+----------------+
35 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

-- 异或两种情况:
-- * 1. 部门id为50,   则工资小于6000.
-- * 2. 部门id不为50, 则工资大于6000.
mysql> SELECT first_name, department_id, salary FROM employees WHERE department_id = 50 XOR salary > 6000;
+-------------+---------------+----------+
| first_name  | department_id | salary   |
+-------------+---------------+----------+
| Steven      |            90 | 24000.00 |
| ...         |            .. |      ... | -- 省略
| Alana       |            50 |  3100.00 |
| Kevin       |            50 |  3000.00 |
| Donald      |            50 |  2600.00 |
| Douglas     |            50 |  2600.00 |
| Michael     |            20 | 13000.00 |
| Susan       |            40 |  6500.00 |
| Hermann     |            70 | 10000.00 |
| Shelley     |           110 | 12000.00 |
| William     |           110 |  8300.00 |
+-------------+---------------+----------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

5. 位运算

mysql> SELECT 12 & 5, 12 | 5, 12 ^ 5 FROM DUAL;
+--------+--------+--------+
| 12 & 5 | 12 | 5 | 12 ^ 5 |
+--------+--------+--------+
|      4 |     13 |      9 |
+--------+--------+--------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

十进制: 12 --> 二进制: 1 1 0 0   十进制: 12 --> 二进制: 1 1 0 0   十进制: 12 --> 二进制: 1 1 0 0  
十进制:  5 --> 二进制: 0 1 0 1   十进制:  5 --> 二进制: 0 1 0 1   十进制:  5 --> 二进制: 0 1 0 1
与计算: --一一得一-------------  或计算: --有一得一-------------   异或计算:--一零得一-----------
十进制:  4 <-- 二进制: 0 1 0 0   十进制: 13 <-- 二进制: 1 1 0 1    十进制: 9 <-- 十进制: 1 0 0 1
1
2
3
4

mysql> SELECT  ~1 FROM DUAL;
+----------------------+
| ~1                   |
+----------------------+
| 18446744073709551614 |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT 10 & ~1 FROM DUAL;
+---------+
| 10 & ~1 |
+---------+
|      10 |
+---------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

数字1:  0000000000000000000000000000000000000000000000000000000000000001,
1取反:  1111111111111111111111111111111111111111111111111111111111111110. (18446744073709551614)
数字10: 0000000000000000000000000000000000000000000000000000000000001010,
与运算: ----------------------------------------------------------------
数字10: 0000000000000000000000000000000000000000000000000000000000001010.
1
2
3
4
5

-- 在一定范围内满足: 每向左移动1位, 相当于乘以2; 每向右移动一位, 相当于除以2.
mysql> SELECT 4 << 1 , 8 >> 1 FROM DUAL;
+--------+--------+
| 4 << 1 | 8 >> 1 |
+--------+--------+
|      8 |      4 |
+--------+--------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8

6. 运算符优先级

请注意, 表格中较高的优先级的运算符会优先进行计算, 而较低优先级的运算符则会在较高优先级运算符计算完毕后进行.
括号可以用于改变运算符的优先级.
1
2

7. 练习

-- 1. 匹配员工表中, 名字以a开头的员工名字:
mysql> SELECT first_name FROM employees WHERE first_name REGEXP '^a';
+------------+
| first_name |
+------------+
| Alexander  |
| Alexander  |
| ...        |  -- 省略
| Alexis     |
| Anthony    |
| Alana      |
+------------+
10 rows in set (0.02 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

-- 2. 匹配员工表中, 名字以r结尾的员工名字:
mysql> SELECT first_name FROM employees WHERE first_name REGEXP 'r$';
+-------------+
| first_name  |
+-------------+
| Alexander   |
| Alexander   |
| ...         |  -- 省略
| Tayler      |
| Jennifer    |
| Jennifer    |
+-------------+
11 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

-- 3. 匹配员工表中, 名字以a或b或c开头的员工名字:
mysql> SELECT first_name FROM employees WHERE first_name REGEXP '^[a-c]';
+-------------+
| first_name  |
+-------------+
| Alexander   |
| Bruce       |
| Alexander   |
| Adam        |
| ...         |  -- 省略
| Alexis      |
| Anthony     |
| Britney     |
| Alana       |
+-------------+
16 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16