MySQL查询语句

第4章：MySQL查询语句

4.1 SELECT查询基础

SELECT语句是SQL中最常用的命令，用于从数据库中检索数据。本节将详细介绍SELECT语句的基本语法、各种子句以及如何使用它们来构建复杂的查询。

4.1.1 基本SELECT语法

最基本的SELECT语句由SELECT和FROM子句组成：

SELECT column1, column2, ...
FROM table_name;

参数说明：

• column1, column2, ...：要检索的列名
• table_name：要从中检索数据的表名

选择所有列

使用星号（*）可以选择表中的所有列：

SELECT * FROM employees;

虽然这种方式方便，但在生产环境中应谨慎使用，因为：

• 它会返回不必要的数据，增加网络传输负担
• 如果表结构变化（如添加新列），查询结果也会变化
• 它可能导致索引无法有效使用

选择特定列

通常更好的做法是明确指定需要的列：

SELECT employee_id, first_name, last_name, hire_date
FROM employees;

这样可以：

• 减少数据传输量
• 使查询意图更明确
• 可能提高查询性能

列别名

可以使用AS关键字为列指定别名，使结果更易读：

SELECT 
    employee_id AS id,
    first_name AS name,
    hire_date AS "Date Hired"
FROM employees;

AS关键字是可选的，可以省略：

SELECT 
    employee_id id,
    first_name name,
    hire_date "Date Hired"
FROM employees;

如果别名包含空格或特殊字符，需要用引号括起来。

表别名

同样，可以为表指定别名，这在处理多表查询时特别有用：

SELECT e.employee_id, e.first_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

去除重复行

使用DISTINCT关键字可以去除结果中的重复行：

SELECT DISTINCT department_id
FROM employees;

DISTINCT适用于整个SELECT列表，而不仅仅是它后面的列：

SELECT DISTINCT department_id, job_id
FROM employees;

上面的查询会返回唯一的department_id和job_id组合。

4.1.2 WHERE子句

WHERE子句用于过滤结果，只返回满足指定条件的行：

SELECT column1, column2, ...
FROM table_name
WHERE condition;

比较运算符

WHERE子句中可以使用以下比较运算符：

示例：

-- 查找工资大于10000的员工
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 10000;

-- 查找特定部门的员工
SELECT employee_id, first_name, last_name
FROM employees
WHERE department_id = 50;

逻辑运算符

可以使用逻辑运算符组合多个条件：

示例：

-- 查找工资在10000到15000之间的员工
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary >= 10000 AND salary <= 15000;

-- 查找销售部或IT部的员工
SELECT employee_id, first_name, last_name, department_id
FROM employees
WHERE department_id = 80 OR department_id = 60;

-- 查找非销售部的员工
SELECT employee_id, first_name, last_name, department_id
FROM employees
WHERE NOT department_id = 80;

BETWEEN运算符

BETWEEN运算符用于测试值是否在指定范围内（包括边界值）：

SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary BETWEEN 10000 AND 15000;

这等同于：

SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary >= 10000 AND salary <= 15000;

也可以使用NOT BETWEEN排除某个范围：

SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary NOT BETWEEN 10000 AND 15000;

IN运算符

IN运算符用于测试值是否在指定的值列表中：

SELECT employee_id, first_name, last_name, department_id
FROM employees
WHERE department_id IN (60, 80, 100);

这等同于：

SELECT employee_id, first_name, last_name, department_id
FROM employees
WHERE department_id = 60 OR department_id = 80 OR department_id = 100;

也可以使用NOT IN排除某些值：

SELECT employee_id, first_name, last_name, department_id
FROM employees
WHERE department_id NOT IN (60, 80, 100);

IN运算符的优势：

• 代码更简洁易读
• 执行顺序更明确
• 通常性能更好
• 可以包含子查询

LIKE运算符

LIKE运算符用于模式匹配，通常与通配符一起使用：

示例：

-- 查找名字以'J'开头的员工
SELECT employee_id, first_name, last_name
FROM employees
WHERE first_name LIKE 'J%';

-- 查找名字中包含'an'的员工
SELECT employee_id, first_name, last_name
FROM employees
WHERE first_name LIKE '%an%';

-- 查找名字正好是5个字符的员工
SELECT employee_id, first_name, last_name
FROM employees
WHERE first_name LIKE '_____';

-- 查找名字第二个字符是'a'的员工
SELECT employee_id, first_name, last_name
FROM employees
WHERE first_name LIKE '_a%';

也可以使用NOT LIKE排除匹配的模式：

SELECT employee_id, first_name, last_name
FROM employees
WHERE first_name NOT LIKE 'J%';

注意：LIKE操作符对大小写的敏感性取决于数据库的配置和列的排序规则。

IS NULL运算符

IS NULL运算符用于测试值是否为NULL（空值）：

-- 查找没有分配部门的员工
SELECT employee_id, first_name, last_name
FROM employees
WHERE department_id IS NULL;

同样，IS NOT NULL用于查找非空值：

-- 查找有分配部门的员工
SELECT employee_id, first_name, last_name
FROM employees
WHERE department_id IS NOT NULL;

重要：在SQL中，NULL表示未知或缺失的值，不等于零或空字符串。NULL值不能使用等号（=）或不等号（<>）进行比较，必须使用IS NULL或IS NOT NULL。

4.1.3 ORDER BY子句

ORDER BY子句用于对结果集进行排序：

SELECT column1, column2, ...
FROM table_name
[WHERE condition]
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

参数说明：

• column1, column2, ...：用于排序的列
• ASC：升序排列（默认）
• DESC：降序排列

示例：

-- 按工资升序排列员工
SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY salary;

-- 按工资降序排列员工
SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY salary DESC;

-- 多列排序：先按部门ID升序，再按工资降序
SELECT employee_id, first_name, last_name, department_id, salary
FROM employees
ORDER BY department_id ASC, salary DESC;

ORDER BY子句也可以使用列的位置编号，但这不是推荐的做法，因为它使查询不易理解：

SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY 4 DESC;  -- 按第4列（salary）降序排列

可以使用表达式或函数结果排序：

-- 按姓名长度排序
SELECT employee_id, first_name, last_name
FROM employees
ORDER BY LENGTH(first_name);

-- 按年薪（计算值）排序
SELECT employee_id, first_name, last_name, salary, salary*12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC;

4.1.4 LIMIT子句

LIMIT子句用于限制结果集中返回的行数：

SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, ...]
LIMIT [offset,] row_count;

参数说明：

• offset：起始行的偏移量，从0开始计数（可选，默认为0）
• row_count：要返回的最大行数

示例：

-- 返回前10名员工
SELECT employee_id, first_name, last_name
FROM employees
LIMIT 10;

-- 返回第11到第20名员工
SELECT employee_id, first_name, last_name
FROM employees
LIMIT 10, 10;
-- 或使用更明确的OFFSET语法
SELECT employee_id, first_name, last_name
FROM employees
LIMIT 10 OFFSET 10;

LIMIT通常与ORDER BY一起使用，以获取"前N名"或"后N名"记录：

-- 获取工资最高的5名员工
SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

注意：LIMIT是MySQL特有的语法。在其他数据库系统中，可能使用不同的语法，如Oracle的ROWNUM或SQL Server的TOP。

4.1.5 GROUP BY子句

GROUP BY子句用于将结果集按一个或多个列分组，通常与聚合函数一起使用：

SELECT column1, column2, ..., aggregate_function(column)
FROM table_name
[WHERE condition]
GROUP BY column1, column2, ...
[ORDER BY column1, column2, ...];

常用的聚合函数包括：

• COUNT()：计算行数
• SUM()：计算总和
• AVG()：计算平均值
• MAX()：找出最大值
• MIN()：找出最小值

示例：

-- 计算每个部门的员工数
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

-- 计算每个部门的平均工资
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

-- 找出每个部门的最高和最低工资
SELECT department_id, MAX(salary) AS max_salary, MIN(salary) AS min_salary
FROM employees
GROUP BY department_id;

-- 多列分组：按部门和职位分组
SELECT department_id, job_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id, job_id;

GROUP BY的一些重要规则：

1. SELECT列表中的每个非聚合列都必须出现在GROUP BY子句中
2. GROUP BY子句中的列不必出现在SELECT列表中
3. GROUP BY子句不能使用列别名
4. 默认情况下，GROUP BY的结果是按照分组列升序排序的，但这种行为不应被依赖

4.1.6 HAVING子句

HAVING子句用于过滤分组后的结果，类似于WHERE子句过滤行：

SELECT column1, column2, ..., aggregate_function(column)
FROM table_name
[WHERE condition]
GROUP BY column1, column2, ...
HAVING condition
[ORDER BY column1, column2, ...];

示例：

-- 查找员工数超过10人的部门
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 10;

-- 查找平均工资超过10000的部门
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 10000;

-- 结合WHERE和HAVING：查找2005年后入职的员工中，平均工资超过10000的部门
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date >= '2005-01-01'
GROUP BY department_id
HAVING AVG(salary) > 10000;

WHERE与HAVING的区别：

• WHERE在分组前过滤行，HAVING在分组后过滤组
• WHERE不能包含聚合函数，HAVING可以
• WHERE作用于表中的列，HAVING作用于SELECT列表中的列或表达式

4.1.7 子查询

子查询是嵌套在另一个查询中的SELECT语句，可以用在SELECT、FROM、WHERE和HAVING子句中。

WHERE子句中的子查询

-- 查找工资高于平均工资的员工
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- 查找与Steven King同部门的员工
SELECT employee_id, first_name, last_name
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM employees
    WHERE first_name = 'Steven' AND last_name = 'King'
);

FROM子句中的子查询（派生表）

-- 查找每个部门的平均工资，并与公司总平均工资比较
SELECT d.department_id, d.avg_salary,
       (SELECT AVG(salary) FROM employees) AS company_avg,
       d.avg_salary - (SELECT AVG(salary) FROM employees) AS difference
FROM (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) d;

SELECT子句中的子查询（标量子查询）

-- 为每个员工显示其部门名称
SELECT e.employee_id, e.first_name, e.last_name,
       (SELECT department_name FROM departments d WHERE d.department_id = e.department_id) AS department_name
FROM employees e;

多行子查询

返回多行的子查询需要使用特殊的运算符：IN、ANY、ALL等。

-- 查找管理者是部门经理的员工
SELECT employee_id, first_name, last_name
FROM employees
WHERE manager_id IN (
    SELECT employee_id
    FROM employees
    WHERE job_id = 'MANAGER'
);

-- 查找工资高于任何IT程序员的员工
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > ANY (
    SELECT salary
    FROM employees
    WHERE job_id = 'IT_PROG'
);

-- 查找工资高于所有IT程序员的员工
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > ALL (
    SELECT salary
    FROM employees
    WHERE job_id = 'IT_PROG'
);

EXISTS子查询

EXISTS运算符用于测试子查询是否返回任何行：

-- 查找有下属的员工
SELECT employee_id, first_name, last_name
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM employees
    WHERE manager_id = e.employee_id
);

4.1.8 联合查询

UNION、UNION ALL、INTERSECT和EXCEPT运算符用于组合多个SELECT语句的结果。

UNION和UNION ALL

UNION将两个或多个查询的结果合并，并删除重复行；UNION ALL也合并结果，但保留重复行：

-- 合并当前员工和前员工的列表，删除重复项
SELECT employee_id, first_name, last_name, 'Current' AS status
FROM current_employees
UNION
SELECT employee_id, first_name, last_name, 'Former' AS status
FROM former_employees;

-- 合并当前员工和前员工的列表，保留重复项
SELECT employee_id, first_name, last_name, 'Current' AS status
FROM current_employees
UNION ALL
SELECT employee_id, first_name, last_name, 'Former' AS status
FROM former_employees;

使用UNION的规则：

1. 每个SELECT语句必须有相同数量的列
2. 对应列的数据类型必须兼容
3. 结果集的列名取自第一个SELECT语句

INTERSECT

INTERSECT返回两个查询结果的交集（同时存在于两个结果集中的行）：

-- 查找同时是员工和客户的人
SELECT first_name, last_name, email
FROM employees
INTERSECT
SELECT first_name, last_name, email
FROM customers;

注意：MySQL 8.0之前的版本不支持INTERSECT运算符，需要使用JOIN或子查询实现类似功能。

EXCEPT (MINUS)

EXCEPT返回第一个查询结果中存在但第二个查询结果中不存在的行：

-- 查找是员工但不是客户的人
SELECT first_name, last_name, email
FROM employees
EXCEPT
SELECT first_name, last_name, email
FROM customers;

注意：MySQL 8.0之前的版本不支持EXCEPT运算符，需要使用LEFT JOIN或子查询实现类似功能。在某些数据库系统（如Oracle）中，此运算符称为MINUS。

4.1.9 常用函数

MySQL提供了许多内置函数，可以在SELECT语句中使用。

字符串函数

示例：

-- 连接姓名
SELECT employee_id, CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

-- 提取邮箱用户名部分
SELECT email, SUBSTRING(email, 1, LOCATE('@', email) - 1) AS username
FROM employees;

-- 转换为大写
SELECT department_id, UPPER(department_name) AS department_name
FROM departments;

数值函数

示例：

-- 四舍五入工资到整数
SELECT employee_id, salary, ROUND(salary) AS rounded_salary
FROM employees;

-- 计算佣金金额
SELECT employee_id, salary, commission_pct,
       ROUND(salary * commission_pct, 2) AS commission_amount
FROM employees
WHERE commission_pct IS NOT NULL;

日期和时间函数

示例：

-- 计算员工工作年限
SELECT employee_id, first_name, last_name, hire_date,
       FLOOR(DATEDIFF(CURDATE(), hire_date) / 365) AS years_of_service
FROM employees;

-- 查找最近30天内入职的员工
SELECT employee_id, first_name, last_name, hire_date
FROM employees
WHERE hire_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

-- 按入职年份分组统计员工数
SELECT YEAR(hire_date) AS hire_year, COUNT(*) AS employee_count
FROM employees
GROUP BY YEAR(hire_date)
ORDER BY hire_year;

条件函数

CASE表达式有两种形式：

-- 简单CASE表达式
SELECT employee_id, first_name, last_name, salary,
       CASE department_id
           WHEN 10 THEN 'Administration'
           WHEN 20 THEN 'Marketing'
           WHEN 30 THEN 'Purchasing'
           WHEN 40 THEN 'Human Resources'
           ELSE 'Other'
       END AS department
FROM employees;

-- 搜索CASE表达式
SELECT employee_id, first_name, last_name, salary,
       CASE
           WHEN salary < 5000 THEN 'Low'
           WHEN salary BETWEEN 5000 AND 10000 THEN 'Medium'
           WHEN salary > 10000 THEN 'High'
           ELSE 'Unknown'
       END AS salary_grade
FROM employees;

聚合函数

示例：

-- 基本聚合
SELECT 
    COUNT(*) AS total_employees,
    SUM(salary) AS total_salary,
    AVG(salary) AS average_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary
FROM employees;

-- 分组聚合
SELECT department_id,
    COUNT(*) AS employee_count,
    SUM(salary) AS total_salary,
    AVG(salary) AS average_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary
FROM employees
GROUP BY department_id;

-- 使用GROUP_CONCAT
SELECT department_id,
    GROUP_CONCAT(first_name ORDER BY first_name SEPARATOR ', ') AS employees
FROM employees
GROUP BY department_id;

4.1.10 WITH子句（公用表表达式）

WITH子句（也称为公用表表达式或CTE）允许定义一个临时结果集，可以在后续的SELECT语句中引用。MySQL 8.0及以上版本支持此功能。

WITH cte_name AS (
    SELECT ...
)
SELECT ... FROM cte_name ...;

示例：

-- 使用CTE计算每个部门的平均工资
WITH dept_avg_salary AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.employee_id, e.first_name, e.last_name, e.salary,
       d.avg_salary,
       e.salary - d.avg_salary AS salary_diff
FROM employees e
JOIN dept_avg_salary d ON e.department_id = d.department_id;

-- 多个CTE
WITH dept_avg_salary AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
),
high_salary_depts AS (
    SELECT department_id
    FROM dept_avg_salary
    WHERE avg_salary > 10000
)
SELECT d.department_id, d.department_name
FROM departments d
JOIN high_salary_depts h ON d.department_id = h.department_id;

CTE的优势：

• 提高查询的可读性和维护性
• 允许引用同一子查询多次
• 可以递归引用自身（递归CTE）

递归CTE

递归CTE可以引用自身，用于处理层次结构数据：

WITH RECURSIVE cte_name AS (
    -- 基础查询（非递归部分）
    SELECT ...
    UNION ALL
    -- 递归查询（引用CTE自身）
    SELECT ... FROM table_name JOIN cte_name ON ...
)
SELECT ... FROM cte_name;

示例：

-- 查找员工的所有上级管理者
WITH RECURSIVE emp_hierarchy AS (
    -- 基础查询：选择一个起始员工
    SELECT employee_id, first_name, last_name, manager_id, 1 AS level
    FROM employees
    WHERE employee_id = 105
    
    UNION ALL
    
    -- 递归查询：查找上级管理者
    SELECT e.employee_id, e.first_name, e.last_name, e.manager_id, h.level + 1
    FROM employees e
    JOIN emp_hierarchy h ON e.employee_id = h.manager_id
)
SELECT * FROM emp_hierarchy;

4.1.11 SELECT语句的执行顺序

虽然我们按照以下顺序编写SELECT语句：

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ...

但实际的逻辑执行顺序是：

1. FROM：确定数据来源
2. WHERE：过滤行
3. GROUP BY：分组
4. HAVING：过滤组
5. SELECT：选择列和表达式
6. ORDER BY：排序
7. LIMIT：限制结果集大小

了解这个顺序有助于理解为什么：

• WHERE不能引用SELECT中定义的别名
• HAVING可以引用SELECT中的别名（在某些数据库中）
• ORDER BY可以引用SELECT中的别名

4.1.12 查询优化技巧

以下是一些优化SELECT查询的技巧：

1. 只选择需要的列：避免使用SELECT *，特别是对于大表。

2. 使用适当的索引：确保WHERE、JOIN和ORDER BY子句中使用的列有适当的索引。

3. 限制结果集大小：使用LIMIT子句，特别是对于大结果集。

4. 避免在WHERE子句中使用函数：在列上应用函数可能会阻止使用索引。

   -- 不好的做法（不能使用索引）
   SELECT * FROM employees WHERE YEAR(hire_date) = 2005;
   
   -- 更好的做法（可以使用索引）
   SELECT * FROM employees WHERE hire_date BETWEEN '2005-01-01' AND '2005-12-31';
   

5. 使用EXPLAIN分析查询：使用EXPLAIN命令查看查询的执行计划，找出潜在的性能问题。

   EXPLAIN SELECT * FROM employees WHERE department_id = 50;
   

6. 优化JOIN操作：

   • 确保JOIN条件有索引
   • 将小表放在JOIN的左侧
   • 考虑使用子查询替代某些JOIN

7. 使用适当的JOIN类型：根据需要选择INNER JOIN、LEFT JOIN等。

8. 避免相关子查询：相关子查询（引用外部查询的列的子查询）通常性能较差。

9. 使用EXISTS而不是IN：对于大型子查询，EXISTS通常比IN更高效。

10. 使用UNION ALL而不是UNION：如果不需要去重，UNION ALL比UNION更高效。

通过掌握SELECT查询的基础知识和高级技巧，您可以有效地从MySQL数据库中检索和分析数据。SELECT查询是SQL中最常用的命令，熟练掌握它对于数据库开发和管理至关重要。