数据库之五 筛选条件

【零】数据准备

【1】创建表

drop table if exists employee;	--防止存在
create table employee(
id int not null unique auto_increment,	--编号
name varchar(15) not null,	--姓名
sex enum('male', 'female') not null default 'male',	--性别枚举
age int(3) unsigned not null,	--年龄
hire_date  varchar(50),	--入职日期
post varchar(50),	--职业部门
salary double(15,2),	--工资
office int,	--办公室
depart_id int);	--办公室id

【2】插入数据

insert into employee (name, sex, age, hire_date, post, salary, office, depart_id) values
('John', 'male', 30, '20210201', 'teacher', 2500.00, 501, 1),
('Alice', 'female', 35, '20210315', 'sale', 2800.00, 502, 2),
('Michael', 'male', 28, '20220118', 'operation', 2700.00, 503, 3),
('Emily', 'female', 32, '20231203', 'teacher', 2600.00, 501, 4),
('David', 'male', 29, '20200829', 'sale', 2900.00, 502, 2),
('Sophia', 'female', 31, '20230912', 'operation', 2800.00, 503, 3),
('Daniel', 'male', 27, '20230724', 'teacher', 2700.00, 501, 1),
('Olivia', 'female', 34, '20210907', 'sale', 3000.00, 502, 2),
('Matthew', 'male', 30, '20221231', 'operation', 2900.00, 503, 3),
('Emma', 'female', 33, '20230305', 'teacher', 2800.00, 501, 1),
('Christopher', 'male', 28, '20210214', 'sale', 3100.00, 502, 2),
('Ava', 'female', 35, '20231228', 'operation', 3000.00, 503, 3),
('Andrew', 'male', 29, '20200802', 'teacher', 2900.00, 501, 1),
('Isabella', 'female', 31, '20231015', 'sale', 3200.00, 502, 2),
('Joshua', 'male', 27, '20230827', 'operation', 3100.00, 503, 3),
('Mia', 'female', 34, '20210810', 'teacher', 3000.00, 501, 1),
('William', 'male', 30, '20221224', 'sale', 3300.00, 502, 2),
('Samantha', 'female', 33, '20230207', 'operation', 3200.00, 503, 3),
('Joseph', 'male', 28, '20210118', 'teacher', 3100.00, 501, 1),
('Charlotte', 'female', 35, '20231102', 'sale', 3400.00, 502, 2),
('Robert', 'male', 32, '20220615', 'operation', 5000.00, 503, 3);

【一】筛选条件之 where

• WHERE：用于筛选行的条件。

【1】条件为整数或浮点数

（1）查询年龄在30到31的员工信息

• and

select * from employee
where age >=30 and age <=31; 

• between

select *from employee 
where age between 30 and 31;

• in

select * 
from employee
where age in (30, 31);

+----+----------+--------+-----+-----------+-----------+---------+--------+-----------+
| id | name     | sex    | age | hire_date | post      | salary  | office | depart_id |
+----+----------+--------+-----+-----------+-----------+---------+--------+-----------+
|  1 | John     | male   |  30 | 20210201  | teacher   | 2500.00 |    501 |         1 |
|  6 | Sophia   | female |  31 | 20230912  | operation | 2800.00 |    503 |         3 |
|  9 | Matthew  | male   |  30 | 20221231  | operation | 2900.00 |    503 |         3 |
| 14 | Isabella | female |  31 | 20231015  | sale      | 3200.00 |    502 |         2 |
| 17 | William  | male   |  30 | 20221224  | sale      | 3300.00 |    502 |         2 |
+----+----------+--------+-----+-----------+-----------+---------+--------+-----------+

（2）查询薪资不在2600到3500之间的员工信息

• not between

select * from employee 
where salary not between 2600 and 3500;

• or

select * from employee
where salary < 2600 or salary >3500;

+----+--------+------+-----+-----------+-----------+---------+--------+-----------+
| id | name   | sex  | age | hire_date | post      | salary  | office | depart_id |
+----+--------+------+-----+-----------+-----------+---------+--------+-----------+
|  1 | John   | male |  30 | 20210201  | teacher   | 2500.00 |    501 |         1 |
| 21 | Robert | male |  32 | 20220615  | operation | 5000.00 |    503 |         3 |
+----+--------+------+-----+-----------+-----------+---------+--------+-----------+

【2】查询信息是字符串（模糊匹配）

（1）模糊匹配

• 在 SQL 中，LIKE 操作符用于模糊匹配字符串。

• 它通常与通配符一起使用，其中 % 表示匹配任意多个字符，而 _ 表示匹配单个字符。

• 通配符% 用于匹配任意多个字符，包括零个字符。

  • LIKE '%mode'：匹配以 “mode” 结尾的任意字符串。

  • LIKE 'mode%'：匹配以 “mode” 开头的任意字符串。

  • LIKE '%mode%'：匹配包含 “mode” 的任意位置的字符串。

• 通配符_ 用于匹配单个字符。

  • LIKE '_mode'：匹配以一个任意字符开头，然后是 “mode”。
  • LIKE 'mode_'：匹配以 “mode” 开头，然后是一个任意字符。
  • LIKE '_mode_'：匹配以一个任意字符开头和一个任意字符结尾，中间有 “mode”。

（2）查找姓名中含有’th‘的员工信息

• 通配符%

select * from employee
where name like "%th%";

+----+----------+--------+-----+-----------+-----------+---------+--------+-----------+
| id | name     | sex    | age | hire_date | post      | salary  | office | depart_id |
+----+----------+--------+-----+-----------+-----------+---------+--------+-----------+
|  9 | Matthew  | male   |  30 | 20221231  | operation | 2900.00 |    503 |         3 |
| 18 | Samantha | female |  33 | 20230207  | operation | 3200.00 |    503 |         3 |
+----+----------+--------+-----+-----------+-----------+---------+--------+-----------+

（3）查看姓名是四个字符的员工信息

• 通配符_

select * from employee
where name like '____';

• char_length()

select * from employee
where char_length(name) = 4;

+----+------+--------+-----+-----------+---------+---------+--------+-----------+
| id | name | sex    | age | hire_date | post    | salary  | office | depart_id |
+----+------+--------+-----+-----------+---------+---------+--------+-----------+
|  1 | John | male   |  30 | 20210201  | teacher | 2500.00 |    501 |         1 |
| 10 | Emma | female |  33 | 20230305  | teacher | 2800.00 |    501 |         1 |
+----+------+--------+-----+-----------+---------+---------+--------+-----------+

【3】查看信息是空NULL

• 判断空需要用IS，不能用=

# 修改10号员工的办公室为空
update employee
set office=null
where id = 10;

# 查看办公室信息为空的员工信息
select * from employee
where office is null;

+----+------+--------+-----+-----------+---------+---------+--------+-----------+
| id | name | sex    | age | hire_date | post    | salary  | office | depart_id |
+----+------+--------+-----+-----------+---------+---------+--------+-----------+
| 10 | Emma | female |  33 | 20230305  | teacher | 2800.00 |   NULL |         1 |
+----+------+--------+-----+-----------+---------+---------+--------+-----------+

• 使用=号判断是字符，这里查询结果将为空

mysql> # 查看办公室信息为空的员工信息
mysql> select * from employee
    -> where office = null;
Empty set (0.00 sec)

【4】exists

• 语法

select 字段名 from 表名 where exists (执行语句);

（1）说明

• 执行语句只返回布尔值
• 返回true时，外层查询语句执行
• 返回false时，外层查询语句不执行

（2）示例

• 部门有21人以上才进行员工信息查询

select * 
from employee
where exists(
select * from employee
where id >21 );

Empty set (0.00 sec)

【二】筛选条件之 group by

• GROUP BY：用于对结果集进行分组。

【1】注意：严格模式

（1）讲解

• 在严格模式only_full_group_by下，对于一个 SELECT 查询中包含了 GROUP BY 子句，除了 GROUP BY 中的列外，SELECT 列表中的每一列都必须是在聚合函数中，或者在 GROUP BY 子句中的列。

（2）查看

• 直接查看，会报错，由于严格模式only_full_group_by

mysql> select * from employee group by post;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db2.employee.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

• 那我们临时删除一下这个严格模式

# 查看原来的样子
select @@sql_mode;

set @new_mode = replace(@@session.sql_mode, 'ONLY_FULL_GROUP_BY,', '');
select @new_mode;
set session sql_mode = @new_mode;

# 查看修改成功没
select @@sql_mode;

• 再次查看

select * from employee
group by post;

+----+---------+--------+-----+-----------+-----------+---------+--------+-----------+
| id | name    | sex    | age | hire_date | post      | salary  | office | depart_id |
+----+---------+--------+-----+-----------+-----------+---------+--------+-----------+
|  3 | Michael | male   |  28 | 20220118  | operation | 2700.00 |    503 |         3 |
|  2 | Alice   | female |  35 | 20210315  | sale      | 2800.00 |    502 |         2 |
|  1 | John    | male   |  30 | 20210201  | teacher   | 2500.00 |    501 |         1 |
+----+---------+--------+-----+-----------+-----------+---------+--------+-----------+

（3）分析

• 首先需要知道分组得到的是什么？

  • 分组并不是按照组进行排序后输出所有内容
  • 分组的结果是每个组的一个聚合值，如平均值、总和、最大值等。这个聚合值代表了该组的汇总信息。

• 没有严格模式only_full_group_by，查看到的是什么信息？

  • 经过和数据准备的插入数据对比
  • 可以知道返回的是每一个组的第一个成员信息
  • 信息存在不确定性

• 为什么要有严格模式only_full_group_by？

  • 就拿薪资来说，你想通过分组得到的是这个组的平均薪资还是最高薪资还是最低薪资
  • 如果不告诉数据库，数据库也不知道给你提供什么信息好
  • 为了避免在非确定性的情况下返回数据。启用该模式可以更好地保证 SQL 查询的语义准确性。

• 上述查看操作是临时修改，重启后会自动恢复，所以不必额外操作

【2】常用的聚合函数和起别名

（1）查看每个部门的最高薪资

• max最大值

select post, max(salary) from employee
group by post;

+-----------+-------------+
| post      | max(salary) |
+-----------+-------------+
| operation |     5000.00 |
| sale      |     3400.00 |
| teacher   |     3100.00 |
+-----------+-------------+

• as起别名，可以不写，但是不推荐

select post as '部门', max(salary) as '最高薪资' from employee
group by post;

+-----------+--------------+
| 部门      | 最高薪资     |
+-----------+--------------+
| operation |      5000.00 |
| sale      |      3400.00 |
| teacher   |      3100.00 |
+-----------+--------------+

（2）查看每个部门的最低薪资

• min最小值

select post, min(salary) from employee
group by post;

+-----------+-------------+
| post      | min(salary) |
+-----------+-------------+
| operation |     2700.00 |
| sale      |     2800.00 |
| teacher   |     2500.00 |
+-----------+-------------+

（3）查看每个部门的平均薪资

• avg平均值

select post, avg(salary) from employee
group by post;

+-----------+-------------+
| post      | avg(salary) |
+-----------+-------------+
| operation | 3242.857143 |
| sale      | 3100.000000 |
| teacher   | 2800.000000 |
+-----------+-------------+

（4）查看每个部门的薪资总和

• sum求和

select post, sum(salary) from employee
group by post;

+-----------+-------------+
| post      | sum(salary) |
+-----------+-------------+
| operation |    22700.00 |
| sale      |    21700.00 |
| teacher   |    19600.00 |
+-----------+-------------+

（5）查看每个部门的人数

• count计数求和
  • count括号里面的具体内容不重要
  • 就算是填0也可以，仅是计数
  • 但是这个函数无法对null计数，他会跳过这个值
• 又但是count(*)可以统计空行
• 所以推荐使用count(*)

select post, count(0) from employee
group by post;
+-----------+----------+
| post      | count(0) |
+-----------+----------+
| operation |        7 |
| sale      |        7 |
| teacher   |        7 |
+-----------+----------+

select post, count(office) from employee
group by post;

+-----------+---------------+
| post      | count(office) |
+-----------+---------------+
| operation |             7 |
| sale      |             7 |
| teacher   |             6 |	--前面将10号的office改为了null
+-----------+---------------+

select post, count(*)
from employee
group by post;

+-----------+----------+
| post      | count(*) |
+-----------+----------+
| operation |        7 |
| sale      |        7 |
| teacher   |        7 |
+-----------+----------+

CREATE TABLE test (
  Department VARCHAR(50),
  Salary INT
);

INSERT INTO test (Department, Salary) VALUES
  ('Sales', 0),
  ('Sales', 50000),
  ('HR', 0),
  ('HR', 55000),
  ('IT', 0),
  ('IT', 60000);

（6）查询每个部门下的所有员工姓名

• group_concat拼接

• 简单查询所有员工名字

select post, group_concat(name)
from employee
group by post;

+-----------+-----------------------------------------------------------+
| post      | group_concat(name)                                        |
+-----------+-----------------------------------------------------------+
| operation | Michael,Sophia,Matthew,Ava,Joshua,Samantha,Robert         |
| sale      | Alice,David,Olivia,Christopher,Isabella,William,Charlotte |
| teacher   | John,Emily,Daniel,Emma,Andrew,Mia,Joseph                  |
+-----------+-----------------------------------------------------------+

• 还可以在每个员工名字上添加字符串

select post, group_concat(name, "_copy")
from employee
group by post;

+-----------+----------------------------------------------------------------------------------------------+
| post      | group_concat(name, "_copy")                                                                  |
+-----------+----------------------------------------------------------------------------------------------+
| operation | Michael_copy,Sophia_copy,Matthew_copy,Ava_copy,Joshua_copy,Samantha_copy,Robert_copy         |
| sale      | Alice_copy,David_copy,Olivia_copy,Christopher_copy,Isabella_copy,William_copy,Charlotte_copy |
| teacher   | John_copy,Emily_copy,Daniel_copy,Emma_copy,Andrew_copy,Mia_copy,Joseph_copy                  |
+-----------+----------------------------------------------------------------------------------------------+

• 还可以添加其他信息在这里
  • 同时查询员工的年龄

select post, group_concat(name, ':', age)
from employee
group by post;

+-----------+--------------------------------------------------------------------------------+
| post      | group_concat(name, ':', age)                                                   |
+-----------+--------------------------------------------------------------------------------+
| operation | Michael:28,Sophia:31,Matthew:30,Ava:35,Joshua:27,Samantha:33,Robert:32         |
| sale      | Alice:35,David:29,Olivia:34,Christopher:28,Isabella:31,William:30,Charlotte:35 |
| teacher   | John:30,Emily:32,Daniel:27,Emma:33,Andrew:29,Mia:34,Joseph:28                  |
+-----------+--------------------------------------------------------------------------------+

（7）补充：

• concat，不分组之前使用，先别管limit

select concat("name:",name), concat('age:',age)
from employee
limit 2;
+----------------------+--------------------+
| concat("name:",name) | concat('age:',age) |
+----------------------+--------------------+
| name:John            | age:30             |
| name:Alice           | age:35             |
+----------------------+--------------------+

select concat("name:",name, "-age:",age)
from employee
limit 2;
+-----------------------------------+
| concat("name:",name, "-age:",age) |
+-----------------------------------+
| name:John-age:30                  |
| name:Alice-age:35                 |
+-----------------------------------+

• as还可以给表起别名

select emp.name, emp.salary
from employee as emp
limit 2;
+-------+---------+
| name  | salary  |
+-------+---------+
| John  | 2500.00 |
| Alice | 2800.00 |
+-------+---------+

• 可以直接对列进行算数运算*

select name, salary*12 as 'annual_salary'
from employee
limit 2;
+-------+---------------+
| name  | annual_salary |
+-------+---------------+
| John  |      30000.00 |
| Alice |      33600.00 |
+-------+---------------+

【3】group by 和 where

（1）这两者的先后顺序

• 只要有where那么它一定在group by的前面
  • where 先对整体进行过滤
  • group by 再对数据记录进行分组

（2）where 不能使用聚合函数

• 聚合函数是对组进行操作
• 没有分组那么就不能使用聚合函数

【三】筛选条件之 having

• 对分组后的结果进行条件过滤。

【1】说明

• HAVING 子句的语法和 WHERE 子句基本一致，不同之处在于它用于对分组后的结果进行条件筛选，而 WHERE 则用于对原始数据行进行筛选。

• HAVING 子句和 WHERE 子句都可以包含条件表达式，比较运算符，逻辑运算符等。但是，HAVING 子句通常会涉及到聚合函数，因为它是在分组后的数据上进行筛选的。在 HAVING 子句中，你可以直接使用聚合函数来过滤分组后的结果，而在 WHERE 子句中是不允许直接使用聚合函数的。

【2】练习

• 查询各部门30岁以上的员工平均工资，并且只保留部门平均薪资大于3000的部门

select post, avg(salary)
from employee
where age >30
group by post
having avg(salary) > 3000;

+-----------+-------------+
| post      | avg(salary) |
+-----------+-------------+
| operation | 3500.000000 |
| sale      | 3100.000000 |
+-----------+-------------+

• 查询各办公室中男性的平均工资，并只保留排名前两名的部门

select office, avg(salary)
from employee
where sex = 'male'
group by office
having avg(salary) > 3000;

+--------+-------------+
| office | avg(salary) |
+--------+-------------+
|    502 | 3100.000000 |
|    503 | 3425.000000 |
+--------+-------------+

【四】筛选条件之 distinct

• DISTINCT：用于返回唯一的行。

【1】说明

（1）引入

• DISTINCT 是 SQL 查询中用于去重的关键字，它确保结果集中的行是唯一的。
• 使用 DISTINCT 时，查询将只返回具有唯一性的行，即每行的值都是唯一的。

（2）去重条件

• 数据完全一样才能去重，这意味着所有选定的列的值必须完全相同。
• 在使用 DISTINCT 进行去重时，系统会比较所选择的列中的所有值，只有在所有列的值都相同时，才会认为两行是相同的，其中一行将被去重。

【2】练习

• 查看所有员工的年龄有哪些

select distinct age 
from employee; 

+-----+
| age |
+-----+
|  30 |
|  35 |
|  28 |
|  32 |
|  29 |
|  31 |
|  27 |
|  34 |
|  33 |
+-----+

• 查看所有员工的年龄和工资的对应关系

select distinct age, salary
from employee;

+-----+---------+
| age | salary  |
+-----+---------+
|  30 | 2500.00 |
|  35 | 2800.00 |
|  28 | 2700.00 |
|  32 | 2600.00 |
|  29 | 2900.00 |
|  31 | 2800.00 |
|  27 | 2700.00 |
|  34 | 3000.00 |
|  30 | 2900.00 |
|  33 | 2800.00 |
|  28 | 3100.00 |
|  35 | 3000.00 |
|  31 | 3200.00 |
|  27 | 3100.00 |
|  30 | 3300.00 |
|  33 | 3200.00 |
|  35 | 3400.00 |
|  32 | 5000.00 |
+-----+---------+

【五】筛选条件之 order by

• ORDER BY：用于对结果进行排序。

【1】说明

（1）引入

• ORDER BY 用于对查询结果进行排序。
• 默认情况下，ORDER BY 是升序排列的，如果想要降序排列，需要使用 DESC。

（2）ASC和DESC

• ASC 是升序（默认），DESC 是降序。
• 你可以使用 ASC 来明确指定升序，尽管通常可以省略，但为了清晰起见，有时可以明确写出来

【2】练习

• 按照降序查询所有员工的姓名和工资

select name, salary
from employee
order by salary desc;

+-------------+---------+
| name        | salary  |
+-------------+---------+
| Robert      | 5000.00 |
| Charlotte   | 3400.00 |
| William     | 3300.00 |
| Isabella    | 3200.00 |
| Samantha    | 3200.00 |
| Christopher | 3100.00 |
| Joshua      | 3100.00 |
| Joseph      | 3100.00 |
| Olivia      | 3000.00 |
| Ava         | 3000.00 |
| Mia         | 3000.00 |
| David       | 2900.00 |
| Matthew     | 2900.00 |
| Andrew      | 2900.00 |
| Alice       | 2800.00 |
| Sophia      | 2800.00 |
| Emma        | 2800.00 |
| Michael     | 2700.00 |
| Daniel      | 2700.00 |
| Emily       | 2600.00 |
| John        | 2500.00 |
+-------------+---------+

• 按照薪资降序，年龄升序查看员工信息
  • 只用薪资相同的情况下
  • 才会按照年龄的要求排序

select name, age, salary
from employee
order by salary desc, age asc;

+-------------+-----+---------+
| name        | age | salary  |
+-------------+-----+---------+
| Robert      |  32 | 5000.00 |
| Charlotte   |  35 | 3400.00 |
| William     |  30 | 3300.00 |
| Isabella    |  31 | 3200.00 |
| Samantha    |  33 | 3200.00 |
| Joshua      |  27 | 3100.00 |
| Christopher |  28 | 3100.00 |
| Joseph      |  28 | 3100.00 |
| Olivia      |  34 | 3000.00 |
| Mia         |  34 | 3000.00 |
| Ava         |  35 | 3000.00 |
| David       |  29 | 2900.00 |
| Andrew      |  29 | 2900.00 |
| Matthew     |  30 | 2900.00 |
| Sophia      |  31 | 2800.00 |
| Emma        |  33 | 2800.00 |
| Alice       |  35 | 2800.00 |
| Daniel      |  27 | 2700.00 |
| Michael     |  28 | 2700.00 |
| Emily       |  32 | 2600.00 |
| John        |  30 | 2500.00 |
+-------------+-----+---------+

【六】筛选条件之 limit

• LIMIT：用于限制结果集的行数。

【1】说明

（1）引入

• LIMIT 关键字用于限制查询结果返回的行数。
• 它通常与 ORDER BY 一起使用，以确保在应用限制之前对结果进行排序。

（2）参数

• 如果只提供一个参数，LIMIT n 将返回前 n 行，这默认为从第一行开始。

• 可以通过提供两个参数来指定从哪一行开始返回，例如 LIMIT 3, 5 表示从第三行开始返回五行。

【2】练习

• 查询薪资最高的三个人信息

select name, salary
from employee
order by salary desc
limit 3;

+-----------+---------+
| name      | salary  |
+-----------+---------+
| Robert    | 5000.00 |
| Charlotte | 3400.00 |
| William   | 3300.00 |

• 查看年龄按照低到高的第五位到第十位的薪资情况

select name, salary
from employee
order by age asc, salary desc
limit 5,5;

+---------+---------+
| name    | salary  |
+---------+---------+
| Andrew  | 2900.00 |
| David   | 2900.00 |
| William | 3300.00 |
| Matthew | 2900.00 |
| John    | 2500.00 |
+---------+---------+

【七】筛选条件之 regexp

• regexp：正则表达式匹配

【1】说明

（1）引入

• REGEXP 关键字用于执行正则表达式匹配。
• 它通常与 WHERE 子句一起使用，以筛选满足正则表达式条件的行。

（2）常用匹配方式

选项	说明	例子	匹配值示例
^	匹配文本的开始字符	‘^b’ 匹配以字母 b 开头的字符串	book、big、banana、bike
$	匹配文本的结束字符	‘st$’ 匹配以 st 结尾的字符串	test、resist、persist
.	匹配任何单个字符	‘b.t’ 匹配任何 b 和 t 之间有一个字符	bit、bat、but、bite
*	匹配前面的字符 0 次或多次	‘f*n’ 匹配字符 n 前面有任意个字符 f	fn、fan、faan、abcn
+	匹配前面的字符 1 次或多次	‘ba+’ 匹配以 b 开头，后面至少紧跟一个 a	ba、bay、bare、battle
?	匹配前面的字符 0 次或1次	‘sa?’ 匹配0个或1个a字符	sa、s
字符串	匹配包含指定字符的文本	‘fa’ 匹配包含‘fa’的文本	fan、afa、faad
[字符集合]	匹配字符集合中的任何一个字符	‘[xz]’ 匹配 x 或者 z	dizzy、zebra、x-ray、extra
[^]	匹配不在括号中的任何字符	‘[^abc]’ 匹配任何不包含 a、b 或 c 的字符串	desk、fox、f8ke
字符串{n,}	匹配前面的字符串至少 n 次	‘b{2}’ 匹配 2 个或更多的 b	bbb、bbbb、bbbbbbb
字符串{n,m}	匹配前面的字符串至少 n 次， 至多 m 次	‘b{2,4}’ 匹配最少 2 个，最多 4 个 b	bbb、bbbb

【2】练习

• 查询2021年入职的员工信息

select name, hire_date
from employee
where hire_date regexp '^2021';

+-------------+-----------+
| name        | hire_date |
+-------------+-----------+
| John        | 20210201  |
| Alice       | 20210315  |
| Olivia      | 20210907  |
| Christopher | 20210214  |
| Mia         | 20210810  |
| Joseph      | 20210118  |
+-------------+-----------+

• 查询15号入职的员工信息

select name, hire_date
from employee
where hire_date regexp '15$';

+----------+-----------+
| name     | hire_date |
+----------+-----------+
| Alice    | 20210315  |
| Isabella | 20231015  |
| Robert   | 20220615  |
+----------+-----------+

• 查询九月份入职的员工信息

select name, hire_date
from employee
where hire_date regexp '....09..';

+--------+-----------+
| name   | hire_date |
+--------+-----------+
| Sophia | 20230912  |
| Olivia | 20210907  |
+--------+-----------+

• 查询名字中含有tt或者t的员工信息

select name, age
from employee
where name regexp 'tt*';

select name, age
from employee
where name regexp 'tt?';

+-------------+-----+
| name        | age |
+-------------+-----+
| Matthew     |  30 |
| Christopher |  28 |
| Samantha    |  33 |
| Charlotte   |  35 |
| Robert      |  32 |
+-------------+-----+

• 查询名字中有tt的员工信息

select name, age
from employee
where name regexp 'tt+';

+-----------+-----+
| name      | age |
+-----------+-----+
| Matthew   |  30 |
| Charlotte |  35 |
+-----------+-----+

• 查询名字中含有tt的员工信息

select name, age
from employee
where name regexp 'tt';

+-----------+-----+
| name      | age |
+-----------+-----+
| Matthew   |  30 |
| Charlotte |  35 |

• 查询名字含有tt或者mm的员工信息

select name, age
from employee
where name regexp 'tt|mm';

+-----------+-----+
| name      | age |
+-----------+-----+
| Matthew   |  30 |
| Emma      |  33 |
| Charlotte |  35 |
+-----------+-----+

• 查询名字中含有x或y或z的员工信息

select name, age
from employee
where name regexp '[xyz]';

+-------+-----+
| name  | age |
+-------+-----+
| Emily |  32 |
+-------+-----+

• 查询名字为Alice以外的员工信息（存在一定问题，建议少用）
  • 不区分大小写
  • 若要区分大小写，需要加上binary

select name, age
from employee
where name regexp '[^alice]';

select name, age	--将排除不了
from employee
where name regexp binary '[^alice]';

+-------------+-----+
| name        | age |
+-------------+-----+
| John        |  30 |
| Michael     |  28 |
| Emily       |  32 |
| David       |  29 |
| Sophia      |  31 |
| Daniel      |  27 |
| Olivia      |  34 |
| Matthew     |  30 |
| Emma        |  33 |
| Christopher |  28 |
| Ava         |  35 |
| Andrew      |  29 |
| Isabella    |  31 |
| Joshua      |  27 |
| Mia         |  34 |
| William     |  30 |
| Samantha    |  33 |
| Joseph      |  28 |
| Charlotte   |  35 |
| Robert      |  32 |
+-------------+-----+

• 查询名字中不含有一个以上的t的员工信息

select name, age
from employee
where name regexp 't{2,}';

+-----------+-----+
| name      | age |
+-----------+-----+
| Matthew   |  30 |
| Charlotte |  35 |

• 查询名字中含有一个或者两个m的员工信息

select name, age
from employee
where name regexp 'm{1,2}';

+----------+-----+
| name     | age |
+----------+-----+
| Michael  |  28 |
| Emily    |  32 |
| Matthew  |  30 |
| Emma     |  33 |
| Mia      |  34 |
| William  |  30 |
| Samantha |  33 |
+----------+-----+