MySQL 8 教程

7.1 基本查询语句

基本查询语句是使用SELECT语句从数据库表中检索数据。SELECT语句是SQL中最常用的语句之一，它的基本语法如下：

SELECT 字段列表
FROM 表名
[WHERE 条件]
[GROUP BY 分组字段]
[HAVING 分组条件]
[ORDER BY 排序字段 [ASC|DESC]]
[LIMIT [偏移量,] 行数];

其中：

• 字段列表：要查询的字段名称，可以使用*表示查询所有字段
• 表名：要查询数据的表名称
• WHERE：可选，指定查询条件，只有满足条件的记录才会被查询出来
• GROUP BY：可选，指定分组字段，将结果按照分组字段的值进行分组
• HAVING：可选，指定分组条件，只有满足条件的分组才会被查询出来
• ORDER BY：可选，指定排序字段和排序方式（升序ASC或降序DESC）
• LIMIT：可选，限制查询结果的数量，可以指定偏移量和行数

-- 假设有一个employees表，包含id, name, department, position, salary, hire_date等字段

-- 查询所有字段
SELECT * FROM employees;

-- 查询指定字段
SELECT id, name, department, salary FROM employees;

-- 使用别名
SELECT id AS 员工ID, name AS 姓名, department AS 部门, salary AS 工资 FROM employees;

-- 过滤数据（只查询技术部的员工）
SELECT * FROM employees WHERE department = '技术部';

-- 排序（按工资降序排序）
SELECT * FROM employees ORDER BY salary DESC;

-- 分页查询（查询第11-20条记录）
SELECT * FROM employees LIMIT 10, 10;

-- 使用表达式（计算年薪）
SELECT id, name, salary, salary * 12 AS annual_salary FROM employees;

-- 使用DISTINCT去重（查询所有部门）
SELECT DISTINCT department FROM employees;

-- 使用聚合函数（计算平均工资）
SELECT AVG(salary) AS avg_salary FROM employees;

-- 组合使用多个子句
SELECT department, COUNT(*) AS employee_count, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date > '2020-01-01'
GROUP BY department
HAVING employee_count > 5
ORDER BY avg_salary DESC
LIMIT 10;

7.2 单表查询

单表查询是指只涉及一个表的查询操作。MySQL提供了丰富的查询条件和表达式，可以帮助我们精确地检索所需的数据。

7.2.1 查询所有字段

使用星号（*）可以查询表中的所有字段，但如前所述，这通常不是最佳实践。

SELECT * FROM 表名;

-- 查询employees表中的所有字段
SELECT * FROM employees;

7.2.2 查询指定字段

指定要查询的字段名称，可以只查询实际需要的数据。

SELECT 字段1, 字段2, ..., 字段n FROM 表名;

-- 查询employees表中的id, name, department, salary字段
SELECT id, name, department, salary FROM employees;

7.2.3 查询指定记录

使用WHERE子句可以过滤出满足特定条件的记录。

SELECT 字段列表 FROM 表名 WHERE 条件;

-- 查询工资大于8000的员工
SELECT * FROM employees WHERE salary > 8000;

-- 查询技术部且工资大于8000的员工
SELECT * FROM employees WHERE department = '技术部' AND salary > 8000;

-- 查询技术部或工资大于10000的员工
SELECT * FROM employees WHERE department = '技术部' OR salary > 10000;

-- 查询工资在6000到10000之间的员工
SELECT * FROM employees WHERE salary BETWEEN 6000 AND 10000;

-- 查询工资为6000、8000或10000的员工
SELECT * FROM employees WHERE salary IN (6000, 8000, 10000);

-- 查询名字以'张'开头的员工
SELECT * FROM employees WHERE name LIKE '张%';

-- 查询名字中包含'小'的员工
SELECT * FROM employees WHERE name LIKE '%小%';

-- 查询名字长度为2的员工
SELECT * FROM employees WHERE name LIKE '__';

-- 查询入职日期为空的员工
SELECT * FROM employees WHERE hire_date IS NULL;

-- 查询入职日期不为空的员工
SELECT * FROM employees WHERE hire_date IS NOT NULL;

-- 使用NOT运算符取反
SELECT * FROM employees WHERE NOT department = '行政部';

-- 使用括号改变运算符优先级
SELECT * FROM employees WHERE (department = '技术部' AND salary > 8000) OR (department = '市场部' AND salary > 10000);

7.2.4 带IN关键字的查询

IN关键字用于查询字段值在指定集合中的记录。

SELECT 字段列表 FROM 表名 WHERE 字段 IN (值1, 值2, ..., 值n);

-- 查询部门为技术部、市场部或财务部的员工
SELECT * FROM employees WHERE department IN ('技术部', '市场部', '财务部');

-- 使用NOT IN排除特定部门的员工
SELECT * FROM employees WHERE department NOT IN ('行政部', '人力资源部');

-- IN后面可以跟子查询
-- 查询与张三同部门的员工
SELECT * FROM employees WHERE department IN (
    SELECT department FROM employees WHERE name = '张三'
);

7.2.5 带BETWEEN AND的范围查询

BETWEEN AND关键字用于查询字段值在指定范围内的记录。

SELECT 字段列表 FROM 表名 WHERE 字段 BETWEEN 值1 AND 值2;

-- 查询工资在6000到10000之间的员工
SELECT * FROM employees WHERE salary BETWEEN 6000 AND 10000;

-- 查询2022年入职的员工
SELECT * FROM employees WHERE hire_date BETWEEN '2022-01-01' AND '2022-12-31';

-- 使用NOT BETWEEN排除特定范围
SELECT * FROM employees WHERE salary NOT BETWEEN 5000 AND 8000;

7.2.6 带LIKE的字符匹配查询

LIKE关键字用于进行模糊查询，可以配合通配符使用。

SELECT 字段列表 FROM 表名 WHERE 字段 LIKE 模式;

常用的通配符包括：

• %：表示任意多个字符（包括0个）
• _：表示任意一个字符

-- 查询名字以'张'开头的员工
SELECT * FROM employees WHERE name LIKE '张%';

-- 查询名字以'华'结尾的员工
SELECT * FROM employees WHERE name LIKE '%华';

-- 查询名字中包含'小'的员工
SELECT * FROM employees WHERE name LIKE '%小%';

-- 查询名字为两个字符的员工
SELECT * FROM employees WHERE name LIKE '__';

-- 查询名字第一个字符是'张'，第三个字符是'华'的员工
SELECT * FROM employees WHERE name LIKE '张_华';

-- 使用ESCAPE关键字定义转义字符
-- 假设有一个字段包含百分号，需要查询包含'50%'的记录
SELECT * FROM products WHERE description LIKE '%50!%%' ESCAPE '!';

7.2.7 查询空值

使用IS NULL关键字可以查询字段值为空的记录，使用IS NOT NULL关键字可以查询字段值不为空的记录。

SELECT 字段列表 FROM 表名 WHERE 字段 IS NULL;
SELECT 字段列表 FROM 表名 WHERE 字段 IS NOT NULL;

-- 查询没有填写邮箱的用户
SELECT * FROM users WHERE email IS NULL;

-- 查询已填写电话号码的用户
SELECT * FROM users WHERE phone IS NOT NULL;

7.2.8 带AND的多条件查询

使用AND运算符可以组合多个条件，只有同时满足所有条件的记录才会被查询出来。

SELECT 字段列表 FROM 表名 WHERE 条件1 AND 条件2 [AND 条件3 ...];

-- 查询技术部且工资大于8000的员工
SELECT * FROM employees WHERE department = '技术部' AND salary > 8000;

-- 查询2022年入职且工资在6000到10000之间的员工
SELECT * FROM employees WHERE hire_date BETWEEN '2022-01-01' AND '2022-12-31' AND salary BETWEEN 6000 AND 10000;

7.2.9 带OR的多条件查询

使用OR运算符可以组合多个条件，只要满足任一条件的记录就会被查询出来。

SELECT 字段列表 FROM 表名 WHERE 条件1 OR 条件2 [OR 条件3 ...];

-- 查询技术部或市场部的员工
SELECT * FROM employees WHERE department = '技术部' OR department = '市场部';

-- 查询工资大于10000或2023年入职的员工
SELECT * FROM employees WHERE salary > 10000 OR hire_date >= '2023-01-01';

7.2.10 查询结果不重复

使用DISTINCT关键字可以去除查询结果中的重复记录。

SELECT DISTINCT 字段列表 FROM 表名;

-- 查询所有不重复的部门
SELECT DISTINCT department FROM employees;

-- 查询所有不重复的部门和职位组合
SELECT DISTINCT department, position FROM employees;

7.2.11 对查询结果排序

使用ORDER BY子句可以对查询结果进行排序，默认是升序（ASC），可以指定为降序（DESC）。

SELECT 字段列表 FROM 表名 ORDER BY 字段1 [ASC|DESC], 字段2 [ASC|DESC], ...;

-- 按工资降序排序
SELECT * FROM employees ORDER BY salary DESC;

-- 先按部门升序排序，再按工资降序排序
SELECT * FROM employees ORDER BY department ASC, salary DESC;

-- 按入职日期降序排序（最新入职的在前）
SELECT * FROM employees ORDER BY hire_date DESC;

-- 使用表达式排序（按名字长度排序）
SELECT * FROM employees ORDER BY LENGTH(name);

7.2.12 分组查询

使用GROUP BY子句可以将查询结果按照指定字段进行分组，通常与聚合函数一起使用。

SELECT 分组字段, 聚合函数(字段) FROM 表名 GROUP BY 分组字段;

-- 按部门分组，计算每个部门的员工数量
SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department;

-- 按部门分组，计算每个部门的平均工资、最高工资和最低工资
SELECT department, AVG(salary) AS avg_salary, MAX(salary) AS max_salary, MIN(salary) AS min_salary FROM employees GROUP BY department;

-- 按部门和职位分组，计算每组的员工数量
SELECT department, position, COUNT(*) AS employee_count FROM employees GROUP BY department, position;

-- 带HAVING子句的分组查询（只显示员工数量大于5的部门）
SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department HAVING employee_count > 5;

-- 结合WHERE和HAVING（先过滤，再分组，最后对分组结果过滤）
SELECT department, AVG(salary) AS avg_salary FROM employees WHERE hire_date > '2020-01-01' GROUP BY department HAVING avg_salary > 8000;

7.2.13 使用LIMIT限制查询结果的数量

使用LIMIT子句可以限制查询结果的数量，常用于分页查询。

SELECT 字段列表 FROM 表名 LIMIT [偏移量,] 行数;

-- 查询前10条记录
SELECT * FROM employees LIMIT 10;

-- 查询第11-20条记录（偏移量为10，查询10条记录）
SELECT * FROM employees LIMIT 10, 10;

-- 查询工资最高的5名员工
SELECT * FROM employees ORDER BY salary DESC LIMIT 5;

-- 查询技术部工资最高的3名员工
SELECT * FROM employees WHERE department = '技术部' ORDER BY salary DESC LIMIT 3;

-- 分页查询（第page页，每页pageSize条记录）
-- 第1页：LIMIT 0, pageSize
-- 第2页：LIMIT pageSize, pageSize
-- 第page页：LIMIT (page-1)*pageSize, pageSize
SELECT * FROM employees LIMIT (page-1)*pageSize, pageSize;  -- 在MySQL 5.5+中支持

7.3 使用集合函数查询

集合函数（也称为聚合函数）用于对一组值进行计算并返回单个结果。MySQL支持多种集合函数，包括COUNT()、SUM()、AVG()、MAX()和MIN()等。

7.3.1 COUNT()函数

COUNT()函数用于计算表中的行数或非NULL值的数量。

-- 计算表中的总行数
SELECT COUNT(*) FROM 表名;

-- 计算指定字段非NULL值的数量
SELECT COUNT(字段名) FROM 表名;

-- 计算不重复值的数量
SELECT COUNT(DISTINCT 字段名) FROM 表名;

-- 计算员工总数
SELECT COUNT(*) AS total_employees FROM employees;

-- 计算有邮箱的员工数量
SELECT COUNT(email) AS email_count FROM employees;

-- 计算不同部门的数量
SELECT COUNT(DISTINCT department) AS department_count FROM employees;

-- 按部门分组，计算每个部门的员工数量
SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department;

7.3.2 SUM()函数

SUM()函数用于计算数值列的总和。

SELECT SUM(字段名) FROM 表名;

-- 计算所有员工的工资总和
SELECT SUM(salary) AS total_salary FROM employees;

-- 按部门分组，计算每个部门的工资总和
SELECT department, SUM(salary) AS department_total_salary FROM employees GROUP BY department;

-- 计算满足条件的记录的字段总和
SELECT SUM(salary) AS tech_department_total_salary FROM employees WHERE department = '技术部';

7.3.3 AVG()函数

AVG()函数用于计算数值列的平均值。

SELECT AVG(字段名) FROM 表名;

-- 计算所有员工的平均工资
SELECT AVG(salary) AS avg_salary FROM employees;

-- 按部门分组，计算每个部门的平均工资
SELECT department, AVG(salary) AS department_avg_salary FROM employees GROUP BY department;

-- 计算满足条件的记录的字段平均值
SELECT AVG(salary) AS tech_department_avg_salary FROM employees WHERE department = '技术部';

7.3.4 MAX()函数

MAX()函数用于计算列的最大值。

SELECT MAX(字段名) FROM 表名;

-- 找出最高工资
SELECT MAX(salary) AS max_salary FROM employees;

-- 按部门分组，找出每个部门的最高工资
SELECT department, MAX(salary) AS department_max_salary FROM employees GROUP BY department;

-- 找出最新的入职日期
SELECT MAX(hire_date) AS latest_hire_date FROM employees;

-- 找出最长的名字
SELECT name, LENGTH(name) AS name_length FROM employees ORDER BY name_length DESC LIMIT 1;
-- 或者
SELECT name, LENGTH(name) AS name_length FROM employees WHERE LENGTH(name) = (SELECT MAX(LENGTH(name)) FROM employees);

7.3.5 MIN()函数

MIN()函数用于计算列的最小值。

SELECT MIN(字段名) FROM 表名;

-- 找出最低工资
SELECT MIN(salary) AS min_salary FROM employees;

-- 按部门分组，找出每个部门的最低工资
SELECT department, MIN(salary) AS department_min_salary FROM employees GROUP BY department;

-- 找出最早的入职日期
SELECT MIN(hire_date) AS earliest_hire_date FROM employees;

-- 找出最短的名字
SELECT name, LENGTH(name) AS name_length FROM employees ORDER BY name_length ASC LIMIT 1;
-- 或者
SELECT name, LENGTH(name) AS name_length FROM employees WHERE LENGTH(name) = (SELECT MIN(LENGTH(name)) FROM employees);

7.4 连接查询

连接查询是指基于两个或多个表之间的关系查询数据。连接查询是关系型数据库中最强大的功能之一，它允许我们从多个表中检索相关的数据。MySQL支持多种类型的连接查询，包括内连接、外连接（左外连接、右外连接、全外连接）和交叉连接等。

7.4.1 内连接查询

内连接（INNER JOIN）是最常见的连接类型，它只返回两个表中匹配的行。

SELECT 字段列表
FROM 表1
INNER JOIN 表2 ON 表1.字段 = 表2.字段;

-- 假设有两个表：employees和departments
-- employees表包含id, name, department_id等字段
-- departments表包含id, department_name等字段

-- 使用INNER JOIN连接两个表
SELECT e.id, e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

-- 可以省略INNER关键字
SELECT e.id, e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;

-- 使用WHERE子句进行过滤
SELECT e.id, e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.department_name = '技术部';

-- 连接多个表
-- 假设有三个表：orders, customers和products
-- orders表包含id, customer_id, product_id, quantity等字段
-- customers表包含id, name等字段
-- products表包含id, product_name, price等字段

SELECT o.id, c.name AS customer_name, p.product_name, o.quantity
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN products p ON o.product_id = p.id;

-- 使用USING子句简化连接条件（当两个表的连接字段名相同时）
SELECT e.id, e.name, d.department_name
FROM employees e
JOIN departments d USING (id);  -- 假设两个表的连接字段都叫id

-- 自连接（连接同一个表）
-- 假设employees表包含id, name, manager_id等字段
-- manager_id是另一个员工的id

SELECT e.id, e.name, m.name AS manager_name
FROM employees e
JOIN employees m ON e.manager_id = m.id;

7.4.2 外连接查询

外连接（OUTER JOIN）不仅返回两个表中匹配的行，还返回左表、右表或两个表中不匹配的行。外连接分为左外连接、右外连接和全外连接三种类型。

7.4.2.1 左外连接

左外连接（LEFT OUTER JOIN）返回左表中的所有行，以及右表中匹配的行。如果右表中没有匹配的行，则返回NULL值。

SELECT 字段列表
FROM 左表
LEFT [OUTER] JOIN 右表 ON 左表.字段 = 右表.字段;

-- 返回所有员工及其部门信息，如果员工没有部门，则部门信息为NULL
SELECT e.id, e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

-- 使用WHERE子句过滤出没有部门的员工
SELECT e.id, e.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.id IS NULL;

-- 结合GROUP BY使用
SELECT d.department_name, COUNT(e.id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.department_name;

7.4.2.2 右外连接

右外连接（RIGHT OUTER JOIN）返回右表中的所有行，以及左表中匹配的行。如果左表中没有匹配的行，则返回NULL值。

SELECT 字段列表
FROM 左表
RIGHT [OUTER] JOIN 右表 ON 左表.字段 = 右表.字段;

-- 返回所有部门及其员工信息，如果部门没有员工，则员工信息为NULL
SELECT d.id, d.department_name, e.id AS employee_id, e.name AS employee_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

-- 使用WHERE子句过滤出没有员工的部门
SELECT d.id, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id
WHERE e.id IS NULL;

7.4.2.3 全外连接

全外连接（FULL OUTER JOIN）返回左表和右表中的所有行。如果某一侧没有匹配的行，则返回NULL值。需要注意的是，MySQL目前不直接支持FULL OUTER JOIN，但可以通过UNION ALL来模拟。

-- MySQL不支持以下语法
-- SELECT 字段列表 FROM 表1 FULL OUTER JOIN 表2 ON 表1.字段 = 表2.字段;

-- 可以使用UNION ALL来模拟全外连接
SELECT 字段列表 FROM 表1 LEFT JOIN 表2 ON 表1.字段 = 表2.字段
UNION ALL
SELECT 字段列表 FROM 表1 RIGHT JOIN 表2 ON 表1.字段 = 表2.字段 WHERE 表1.字段 IS NULL;

-- 返回所有员工和所有部门，匹配的显示匹配信息，不匹配的显示NULL
SELECT e.id AS employee_id, e.name AS employee_name, d.id AS department_id, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
UNION ALL
SELECT e.id AS employee_id, e.name AS employee_name, d.id AS department_id, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id
WHERE e.id IS NULL;

7.4.3 复合条件连接查询

复合条件连接查询是指连接条件包含多个表达式的连接查询。

SELECT 字段列表
FROM 表1
JOIN 表2 ON 表1.字段1 = 表2.字段1 AND 表1.字段2 = 表2.字段2 [AND ...];

-- 或者
SELECT 字段列表
FROM 表1
JOIN 表2 ON 表1.字段1 = 表2.字段1
WHERE 表1.字段2 = 表2.字段2 [AND ...];

-- 假设有两个表：orders和order_details
-- orders表包含id, order_date, customer_id等字段
-- order_details表包含id, order_id, product_id, quantity, price等字段

-- 使用复合条件连接两个表
SELECT o.id, o.order_date, od.product_id, od.quantity, od.price
FROM orders o
JOIN order_details od ON o.id = od.order_id AND o.order_date > '2023-01-01';

-- 或者使用WHERE子句
SELECT o.id, o.order_date, od.product_id, od.quantity, od.price
FROM orders o
JOIN order_details od ON o.id = od.order_id
WHERE o.order_date > '2023-01-01';

-- 多表复合条件连接
SELECT o.id, c.name, p.product_name, od.quantity
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_details od ON o.id = od.order_id
JOIN products p ON od.product_id = p.id
WHERE o.order_date BETWEEN '2023-01-01' AND '2023-03-31'
AND p.price > 100;

-- 使用OR连接多个条件
SELECT o.id, o.order_date, od.product_id
FROM orders o
JOIN order_details od ON o.id = od.order_id
WHERE o.customer_id = 1001 OR od.product_id IN (5001, 5002, 5003);

7.5 子查询

子查询是指嵌套在另一个SQL语句中的SELECT语句。子查询可以用于SELECT、INSERT、UPDATE和DELETE语句中，通常用于复杂的查询场景。

7.5.1 带ANY、SOME关键字的子查询

ANY和SOME关键字用于比较操作符之后，表示与子查询结果中的任意一个值进行比较。ANY和SOME是同义词，用法相同。

SELECT 字段列表 FROM 表名 WHERE 字段 操作符 ANY (子查询);

-- 假设有两个表：employees和departments
-- 查询工资高于任何一个技术部员工的员工
SELECT * FROM employees WHERE salary > ANY (SELECT salary FROM employees WHERE department = '技术部');

-- 使用SOME关键字（与ANY等价）
SELECT * FROM employees WHERE salary > SOME (SELECT salary FROM employees WHERE department = '技术部');

-- 查询工资低于任何一个管理层员工的普通员工
SELECT * FROM employees WHERE position = '普通员工' AND salary < ANY (SELECT salary FROM employees WHERE position LIKE '%经理');

-- 使用IN关键字代替= ANY
SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments WHERE department_name LIKE '%部');
-- 等价于
SELECT * FROM employees WHERE department_id = ANY (SELECT id FROM departments WHERE department_name LIKE '%部');

7.5.2 带ALL关键字的子查询

ALL关键字用于比较操作符之后，表示与子查询结果中的所有值进行比较。

SELECT 字段列表 FROM 表名 WHERE 字段 操作符 ALL (子查询);

-- 查询工资高于所有行政部员工的员工
SELECT * FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE department = '行政部');

-- 查询工资低于所有技术部员工的员工
SELECT * FROM employees WHERE salary < ALL (SELECT salary FROM employees WHERE department = '技术部');

-- 查询没有订单的客户
-- 假设有customers和orders表
SELECT * FROM customers WHERE id <> ALL (SELECT DISTINCT customer_id FROM orders);

-- 使用NOT IN代替<> ALL
SELECT * FROM customers WHERE id NOT IN (SELECT DISTINCT customer_id FROM orders);

7.5.3 带EXISTS关键字的子查询

EXISTS关键字用于检查子查询是否返回任何行。如果子查询返回至少一行，则EXISTS条件为真；否则为假。

SELECT 字段列表 FROM 表名 WHERE EXISTS (子查询);

-- 查询有订单的客户
SELECT * FROM customers c WHERE EXISTS (SELECT * FROM orders o WHERE o.customer_id = c.id);

-- 查询没有订单的客户
SELECT * FROM customers c WHERE NOT EXISTS (SELECT * FROM orders o WHERE o.customer_id = c.id);

-- 查询有工资高于10000的员工的部门
SELECT * FROM departments d WHERE EXISTS (SELECT * FROM employees e WHERE e.department_id = d.id AND e.salary > 10000);

-- 结合其他条件
SELECT * FROM employees e WHERE department = '技术部' AND EXISTS (SELECT * FROM projects p WHERE p.manager_id = e.id);

7.5.4 带IN关键字的子查询

IN关键字用于检查字段值是否在子查询结果集中。

SELECT 字段列表 FROM 表名 WHERE 字段 IN (子查询);

-- 查询属于技术部、市场部或财务部的员工
SELECT * FROM employees WHERE department IN ('技术部', '市场部', '财务部');

-- 使用子查询
-- 查询与张三同部门的员工
SELECT * FROM employees WHERE department IN (
    SELECT department FROM employees WHERE name = '张三'
);

-- 查询有订单的客户
SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders);

-- 查询没有订单的客户
SELECT * FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);

-- 嵌套子查询
-- 查询工资高于本部门平均工资的员工
SELECT * FROM employees e1 WHERE salary > (
    SELECT AVG(salary) FROM employees e2 WHERE e2.department = e1.department
);

7.5.5 带比较运算符的子查询

比较运算符（如=、>、<、>=、<=、<>等）可以与子查询一起使用，用于比较字段值与子查询结果。

SELECT 字段列表 FROM 表名 WHERE 字段 比较运算符 (子查询);

-- 查询工资高于平均工资的员工
SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

-- 查询工资等于最高工资的员工
SELECT * FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);

-- 查询工资低于最低管理层工资的普通员工
SELECT * FROM employees WHERE position = '普通员工' AND salary < (SELECT MIN(salary) FROM employees WHERE position LIKE '%经理');

-- 使用<>运算符
SELECT * FROM employees WHERE department <> (SELECT department FROM employees WHERE name = '张三');

-- 带比较运算符和ANY/SOME的子查询
SELECT * FROM employees WHERE salary > ANY (SELECT salary FROM employees WHERE department = '技术部');

-- 带比较运算符和ALL的子查询
SELECT * FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE department = '行政部');

7.6 合并查询结果

合并查询结果是指将两个或多个SELECT语句的结果合并成一个结果集。MySQL提供了UNION和UNION ALL运算符来实现这一功能。

7.6.1 UNION运算符

UNION运算符用于合并两个或多个SELECT语句的结果集，并去除重复的行。

SELECT 字段列表 FROM 表1
UNION
SELECT 字段列表 FROM 表2;

-- 查询工资高于10000的员工和技术部的员工（去除重复）
SELECT * FROM employees WHERE salary > 10000
UNION
SELECT * FROM employees WHERE department = '技术部';

-- 合并不同表的查询结果
-- 假设有两个表：employees和contractors
-- 这两个表有相同的结构
SELECT id, name, department FROM employees
UNION
SELECT id, name, department FROM contractors;

-- 合并查询结果并排序
(SELECT id, name, salary FROM employees WHERE department = '技术部')
UNION
(SELECT id, name, salary FROM employees WHERE salary > 10000)
ORDER BY salary DESC;

7.6.2 UNION ALL运算符

UNION ALL运算符用于合并两个或多个SELECT语句的结果集，但不去除重复的行。与UNION相比，UNION ALL的执行效率更高，因为它不需要额外的去重操作。

SELECT 字段列表 FROM 表1
UNION ALL
SELECT 字段列表 FROM 表2;

-- 查询工资高于10000的员工和技术部的员工（不去除重复）
SELECT * FROM employees WHERE salary > 10000
UNION ALL
SELECT * FROM employees WHERE department = '技术部';

-- 合并不同表的查询结果
SELECT id, name, department FROM employees
UNION ALL
SELECT id, name, department FROM contractors;

-- 合并查询结果并排序
(SELECT id, name, salary FROM employees WHERE department = '技术部')
UNION ALL
(SELECT id, name, salary FROM employees WHERE salary > 10000)
ORDER BY salary DESC;

7.7 为表和字段取别名

在SQL查询中，可以为表和字段取别名（Alias），这可以使查询语句更简洁、更易读，特别是在连接查询和复杂查询中。

7.7.1 为表取别名

为表取别名可以简化表名的引用，特别是在连接查询中引用多个表时。

SELECT 字段列表
FROM 表名 [AS] 表别名;

-- 为表取别名
SELECT e.id, e.name, e.salary
FROM employees AS e;

-- 省略AS关键字
SELECT e.id, e.name, e.salary
FROM employees e;

-- 在连接查询中使用表别名
SELECT e.id, e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;

-- 自连接中使用表别名
SELECT e1.id, e1.name, e2.name AS manager_name
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.id;

-- 在子查询中使用表别名
SELECT e.id, e.name, e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(s.salary)
    FROM employees s
    WHERE s.department = e.department
);

7.7.2 为字段取别名

为字段取别名可以使查询结果的列名更有意义，或者解决列名冲突的问题。

SELECT 字段名 [AS] 字段别名 FROM 表名;

-- 为字段取别名
SELECT id AS 员工ID, name AS 姓名, salary AS 工资
FROM employees;

-- 省略AS关键字
SELECT id 员工ID, name 姓名, salary 工资
FROM employees;

-- 为计算字段取别名
SELECT name, salary, salary * 12 AS 年薪
FROM employees;

-- 在聚合函数中使用字段别名
SELECT department, COUNT(*) AS 员工数量, AVG(salary) AS 平均工资
FROM employees
GROUP BY department;

-- 在连接查询中解决列名冲突
-- 假设有两个表都有name字段
SELECT e.name AS 员工姓名, d.name AS 部门名称
FROM employees e
JOIN departments d ON e.department_id = d.id;

-- 在ORDER BY子句中使用字段别名
SELECT name, salary * 12 AS 年薪
FROM employees
ORDER BY 年薪 DESC;

7.8 使用正则表达式查询

MySQL支持使用正则表达式进行复杂的字符串匹配查询。正则表达式提供了比LIKE更强大和灵活的匹配能力。在MySQL中，可以使用REGEXP或RLIKE运算符（它们是同义词）来进行正则表达式匹配。

7.8.1 基本语法

SELECT 字段列表 FROM 表名 WHERE 字段 REGEXP '正则表达式';

7.8.2 常用的正则表达式模式

• ^：匹配字符串的开始
• $：匹配字符串的结束
• .：匹配任意单个字符
• *：匹配前面的字符零次或多次
• +：匹配前面的字符一次或多次
• ?：匹配前面的字符零次或一次
• [字符集]：匹配字符集中的任意一个字符
• [^字符集]：匹配不在字符集中的任意一个字符
• |：匹配两个或多个模式中的一个
• (模式)：分组，将多个字符作为一个整体
• {n}：匹配前面的字符恰好n次
• {n,}：匹配前面的字符至少n次
• {n,m}：匹配前面的字符至少n次，最多m次

-- 查询以特定字符或字符串开头的记录
-- 查询名字以'张'开头的员工
SELECT * FROM employees WHERE name REGEXP '^张';

-- 查询以特定字符或字符串结尾的记录
-- 查询名字以'华'结尾的员工
SELECT * FROM employees WHERE name REGEXP '华$';

-- 用符号'.'来替代字符串中的任意一个字符
-- 查询名字为两个字，且第一个字是'张'的员工
SELECT * FROM employees WHERE name REGEXP '^张.$';

-- 使用'*'和'+'匹配多个字符
-- 查询名字中包含'小'后面跟着任意字符的员工
SELECT * FROM employees WHERE name REGEXP '小.*';

-- 查询名字中包含'小'后面至少跟着一个字符的员工
SELECT * FROM employees WHERE name REGEXP '小.+';

-- 匹配指定字符串
-- 查询名字中包含'小明'或'小红'的员工
SELECT * FROM employees WHERE name REGEXP '小明|小红';

-- 匹配指定字符中的任意一个
-- 查询名字中包含'张'、'李'或'王'的员工
SELECT * FROM employees WHERE name REGEXP '[张李王]';

-- 查询名字中第一个字符是'张'、'李'或'王'的员工
SELECT * FROM employees WHERE name REGEXP '^[张李王]';

-- 匹配指定字符以外的字符
-- 查询名字中不包含'张'、'李'或'王'的员工
SELECT * FROM employees WHERE name REGEXP '^[^张李王]';

-- 使用{n,}或者{n,m}指定字符串连续出现的次数
-- 查询名字由3个字符组成的员工
SELECT * FROM employees WHERE name REGEXP '^.{3}$';

-- 查询电话号码是11位数字的员工
SELECT * FROM employees WHERE phone REGEXP '^[0-9]{11}$';

-- 查询邮箱以.com或.cn结尾的员工
SELECT * FROM employees WHERE email REGEXP '\.(com|cn)$';

-- 使用括号进行分组
-- 查询名字中包含'小小'后面跟着'明'或'红'的员工
SELECT * FROM employees WHERE name REGEXP '小小(明|红)';

7.9 MySQL 8.0的新特性1——GROUP BY不再隐式排序

在MySQL 5.7及之前的版本中，使用GROUP BY子句时，结果集会隐式地按照分组字段进行排序。这可能会导致不必要的性能开销，特别是当不需要排序时。MySQL 8.0移除了这一默认行为，GROUP BY子句不再隐式排序结果集。

7.9.1 行为变化

在MySQL 5.7及之前的版本中：

• 执行SELECT department, COUNT(*) FROM employees GROUP BY department;时，结果集会自动按照department字段升序排序
• 如果不需要排序，可以通过设置sql_mode参数移除ONLY_FULL_GROUP_BY模式，或者使用ORDER BY NULL来禁用排序

在MySQL 8.0中：

• 执行SELECT department, COUNT(*) FROM employees GROUP BY department;时，结果集不会自动排序
• 如果需要排序，必须显式地使用ORDER BY子句

-- MySQL 5.7及之前的版本，结果集会自动按department升序排序
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

-- MySQL 8.0，结果集不会自动排序
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

-- 在MySQL 8.0中，如果需要排序，必须显式使用ORDER BY
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
ORDER BY department ASC;  -- 升序排序

-- 或者降序排序
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
ORDER BY department DESC;  -- 降序排序

-- 按照聚合函数的结果排序
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
ORDER BY employee_count DESC;  -- 按员工数量降序排序

7.10 MySQL 8.0的新特性2——通用表表达式

MySQL 8.0引入了通用表表达式（Common Table Expressions，简称CTE），这是一种临时结果集，可以在SELECT、INSERT、UPDATE或DELETE语句中引用。CTE可以使复杂的查询更加清晰和易于维护。

7.10.1 基本语法

CTE的基本语法如下：

WITH cte_name [(column1, column2, ...)] AS (
    SELECT 语句
)
SELECT 语句 FROM cte_name;

其中：

• cte_name：CTE的名称
• (column1, column2, ...)：可选，指定CTE中字段的名称
• SELECT 语句：定义CTE的查询语句

7.10.2 非递归CTE

非递归CTE是最基本的CTE类型，它不引用自身。

-- 基本的非递归CTE
WITH high_salary_employees AS (
    SELECT id, name, department, salary
    FROM employees
    WHERE salary > 10000
)
SELECT * FROM high_salary_employees
ORDER BY salary DESC;

-- 为CTE指定字段名
WITH department_stats (department_name, employee_count, avg_salary) AS (
    SELECT department, COUNT(*), AVG(salary)
    FROM employees
    GROUP BY department
)
SELECT * FROM department_stats
WHERE employee_count > 5
ORDER BY avg_salary DESC;

-- 使用多个CTE
WITH 
    tech_employees AS (
        SELECT id, name, salary
        FROM employees
        WHERE department = '技术部'
    ),
    avg_salary AS (
        SELECT AVG(salary) AS avg_tech_salary
        FROM tech_employees
    )
SELECT te.id, te.name, te.salary, as.avg_tech_salary
FROM tech_employees te
CROSS JOIN avg_salary as
WHERE te.salary > as.avg_tech_salary;

-- 在连接查询中使用CTE
WITH recent_orders AS (
    SELECT id, customer_id, order_date, total_amount
    FROM orders
    WHERE order_date > '2023-01-01'
)
SELECT c.name, ro.order_date, ro.total_amount
FROM customers c
JOIN recent_orders ro ON c.id = ro.customer_id
ORDER BY ro.order_date DESC;

7.10.3 递归CTE

递归CTE是一种特殊的CTE，它引用自身，用于处理层次结构或递归关系的数据，如组织结构、文件系统等。

WITH RECURSIVE cte_name [(column1, column2, ...)] AS (
    -- 初始查询（锚点成员）
    SELECT 语句
    UNION [ALL]
    -- 递归查询（递归成员），引用CTE自身
    SELECT 语句 FROM cte_name WHERE 条件
)
SELECT 语句 FROM cte_name;

-- 生成数字序列
WITH RECURSIVE numbers AS (
    SELECT 1 AS n  -- 初始查询
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 10  -- 递归查询
)
SELECT * FROM numbers;

-- 输出：1, 2, 3, 4, 5, 6, 7, 8, 9, 10

-- 查询组织结构（员工和他们的经理）
-- 假设employees表包含id, name, manager_id字段
-- manager_id是另一个员工的id，表示该员工的经理
WITH RECURSIVE org_structure AS (
    -- 锚点成员：查询没有经理的员工（即最高级别的员工）
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    -- 递归成员：查询有经理的员工，并连接到他们的经理
    SELECT e.id, e.name, e.manager_id, os.level + 1
    FROM employees e
    JOIN org_structure os ON e.manager_id = os.id
)
SELECT * FROM org_structure
ORDER BY level, id;

-- 计算累积总和
-- 假设有一个sales表包含month和amount字段
WITH RECURSIVE cumulative_sales AS (
    -- 锚点成员：查询第一个月的销售额
    SELECT month, amount, amount AS cumulative_amount
    FROM sales
    WHERE month = '2023-01'
    UNION ALL
    -- 递归成员：计算后续月份的累积销售额
    SELECT s.month, s.amount, cs.cumulative_amount + s.amount
    FROM sales s
    JOIN cumulative_sales cs ON s.month = DATE_ADD(cs.month, INTERVAL 1 MONTH)
)
SELECT * FROM cumulative_sales
ORDER BY month;

-- 遍历文件系统结构
-- 假设有一个files表包含id, name, parent_id字段
-- parent_id是另一个文件或目录的id，表示该文件或目录的父目录
WITH RECURSIVE file_structure AS (
    -- 锚点成员：查询根目录
    SELECT id, name, parent_id, name AS path
    FROM files
    WHERE parent_id IS NULL
    UNION ALL
    -- 递归成员：查询子目录和文件
    SELECT f.id, f.name, f.parent_id, CONCAT(fs.path, '/', f.name)
    FROM files f
    JOIN file_structure fs ON f.parent_id = fs.id
)
SELECT * FROM file_structure
ORDER BY path;