查询进阶

本章将介绍MySQL查询的高级用法，包括条件查询、排序、分组、聚合函数等内容。

基本查询

SELECT基础

-- 创建示例表
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    position VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE,
    status ENUM('active', 'inactive') DEFAULT 'active'
);

-- 插入测试数据
INSERT INTO employees (name, department, position, salary, hire_date, status) VALUES
('张三', '技术部', '工程师', 15000, '2020-01-15', 'active'),
('李四', '技术部', '高级工程师', 25000, '2018-06-01', 'active'),
('王五', '销售部', '销售经理', 20000, '2019-03-20', 'active'),
('赵六', '销售部', '销售员', 12000, '2021-07-10', 'active'),
('钱七', '人事部', 'HR', 10000, '2022-01-05', 'active'),
('孙八', '技术部', '工程师', 16000, '2020-08-15', 'inactive'),
('周九', '财务部', '会计', 14000, '2019-11-01', 'active'),
('吴十', '技术部', '架构师', 35000, '2017-01-01', 'active');

-- 查询所有列
SELECT * FROM employees;

-- 查询指定列
SELECT name, department, salary FROM employees;

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

-- 表别名
SELECT e.name, e.department, e.salary
FROM employees AS e;

DISTINCT去重

-- 查询所有部门
SELECT department FROM employees;

-- 去重查询
SELECT DISTINCT department FROM employees;

-- 多列去重
SELECT DISTINCT department, position FROM employees;

-- 统计去重数量
SELECT COUNT(DISTINCT department) AS 部门数量
FROM employees;

条件查询

WHERE子句

-- 基本条件查询
SELECT * FROM employees WHERE department = '技术部';

-- 比较运算符
SELECT * FROM employees WHERE salary > 20000;
SELECT * FROM employees WHERE salary >= 20000;
SELECT * FROM employees WHERE salary < 15000;
SELECT * FROM employees WHERE salary <= 15000;
SELECT * FROM employees WHERE salary != 15000;
SELECT * FROM employees WHERE salary <> 15000;  -- 不等于

-- 日期条件
SELECT * FROM employees WHERE hire_date > '2020-01-01';
SELECT * FROM employees WHERE hire_date BETWEEN '2020-01-01' AND '2021-12-31';

-- NULL值判断
SELECT * FROM employees WHERE department IS NULL;
SELECT * FROM employees WHERE department IS NOT NULL;

逻辑运算符

-- AND：同时满足多个条件
SELECT * FROM employees 
WHERE department = '技术部' AND salary > 20000;

-- OR：满足任一条件
SELECT * FROM employees 
WHERE department = '技术部' OR department = '销售部';

-- NOT：取反
SELECT * FROM employees 
WHERE NOT department = '技术部';

-- 组合使用（注意优先级）
SELECT * FROM employees 
WHERE (department = '技术部' OR department = '销售部')
AND salary > 15000;

IN和BETWEEN

-- IN：匹配列表中的值
SELECT * FROM employees 
WHERE department IN ('技术部', '销售部');

-- NOT IN：不在列表中
SELECT * FROM employees 
WHERE department NOT IN ('技术部', '销售部');

-- BETWEEN：范围查询（包含边界）
SELECT * FROM employees 
WHERE salary BETWEEN 10000 AND 20000;

-- NOT BETWEEN
SELECT * FROM employees 
WHERE salary NOT BETWEEN 10000 AND 20000;

-- 日期范围
SELECT * FROM employees 
WHERE hire_date BETWEEN '2020-01-01' AND '2020-12-31';

LIKE模糊查询

/*
 * LIKE通配符：
 * % - 匹配任意多个字符
 * _ - 匹配单个字符
 */

-- 以"张"开头
SELECT * FROM employees WHERE name LIKE '张%';

-- 以"三"结尾
SELECT * FROM employees WHERE name LIKE '%三';

-- 包含"工程"
SELECT * FROM employees WHERE position LIKE '%工程%';

-- 匹配单个字符
SELECT * FROM employees WHERE name LIKE '张_';

-- 组合使用
SELECT * FROM employees WHERE position LIKE '%工程%师';

-- 转义特殊字符
SELECT * FROM employees WHERE name LIKE '%\%%';  -- 查找包含%的名称

-- 使用ESCAPE指定转义字符
SELECT * FROM employees WHERE name LIKE '%@%%' ESCAPE '@';

排序

ORDER BY

-- 升序排序（默认）
SELECT * FROM employees ORDER BY salary;

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

-- 多列排序
SELECT * FROM employees 
ORDER BY department ASC, salary DESC;

-- 按列位置排序
SELECT name, salary FROM employees 
ORDER BY 2 DESC;  -- 按第2列（salary）排序

-- 按表达式排序
SELECT name, salary, salary * 12 AS annual_salary
FROM employees 
ORDER BY annual_salary DESC;

-- NULL值排序（MySQL默认NULL最小）
SELECT * FROM employees ORDER BY salary IS NULL, salary DESC;

自定义排序

-- 使用FIELD函数自定义排序
SELECT * FROM employees 
ORDER BY FIELD(department, '技术部', '销售部', '人事部', '财务部');

-- 使用CASE自定义排序
SELECT * FROM employees 
ORDER BY 
    CASE department
        WHEN '技术部' THEN 1
        WHEN '销售部' THEN 2
        WHEN '人事部' THEN 3
        ELSE 4
    END;

分页查询

/*
 * LIMIT语法：
 * LIMIT offset, count
 * 或 LIMIT count OFFSET offset
 */

-- 限制返回数量
SELECT * FROM employees LIMIT 5;

-- 分页查询（每页5条，第1页）
SELECT * FROM employees LIMIT 0, 5;

-- 第2页
SELECT * FROM employees LIMIT 5, 5;

-- 第3页
SELECT * FROM employees LIMIT 10, 5;

-- 使用OFFSET关键字
SELECT * FROM employees LIMIT 5 OFFSET 0;

-- 分页公式
-- LIMIT (page - 1) * pageSize, pageSize
-- 例如：第3页，每页10条
-- LIMIT (3-1)*10, 10 = LIMIT 20, 10

-- 分页排序
SELECT * FROM employees 
ORDER BY salary DESC 
LIMIT 0, 5;  -- 工资最高的5人

聚合函数

基本聚合函数

-- COUNT：计数
SELECT COUNT(*) AS 总人数 FROM employees;
SELECT COUNT(department) AS 有部门的人数 FROM employees;
SELECT COUNT(DISTINCT department) AS 部门数量 FROM employees;

-- SUM：求和
SELECT SUM(salary) AS 工资总和 FROM employees;

-- AVG：平均值
SELECT AVG(salary) AS 平均工资 FROM employees;

-- MAX：最大值
SELECT MAX(salary) AS 最高工资 FROM employees;

-- MIN：最小值
SELECT MIN(salary) AS 最低工资 FROM employees;

-- 组合使用
SELECT 
    COUNT(*) AS 总人数,
    SUM(salary) AS 工资总和,
    AVG(salary) AS 平均工资,
    MAX(salary) AS 最高工资,
    MIN(salary) AS 最低工资
FROM employees;

GROUP BY分组

-- 按部门分组统计
SELECT 
    department,
    COUNT(*) AS 人数,
    AVG(salary) AS 平均工资,
    SUM(salary) AS 工资总和
FROM employees
GROUP BY department;

-- 多列分组
SELECT 
    department,
    position,
    COUNT(*) AS 人数,
    AVG(salary) AS 平均工资
FROM employees
GROUP BY department, position;

-- GROUP BY与ORDER BY结合
SELECT 
    department,
    COUNT(*) AS 人数
FROM employees
GROUP BY department
ORDER BY 人数 DESC;

-- GROUP BY与LIMIT结合
SELECT 
    department,
    AVG(salary) AS 平均工资
FROM employees
GROUP BY department
ORDER BY 平均工资 DESC
LIMIT 3;

HAVING子句

/*
 * WHERE vs HAVING：
 * WHERE：过滤行，在分组前执行
 * HAVING：过滤组，在分组后执行
 */

-- 查询平均工资大于15000的部门
SELECT 
    department,
    AVG(salary) AS 平均工资
FROM employees
GROUP BY department
HAVING AVG(salary) > 15000;

-- 查询人数大于2的部门
SELECT 
    department,
    COUNT(*) AS 人数
FROM employees
GROUP BY department
HAVING COUNT(*) > 2;

-- WHERE和HAVING结合
SELECT 
    department,
    COUNT(*) AS 人数,
    AVG(salary) AS 平均工资
FROM employees
WHERE status = 'active'  -- 先过滤状态
GROUP BY department
HAVING AVG(salary) > 15000;  -- 再过滤分组

-- HAVING中使用别名
SELECT 
    department,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING avg_salary > 15000;

高级查询技巧

CASE表达式

-- 简单CASE
SELECT 
    name,
    salary,
    CASE 
        WHEN salary >= 30000 THEN '高薪'
        WHEN salary >= 20000 THEN '中薪'
        WHEN salary >= 10000 THEN '普通'
        ELSE '低薪'
    END AS 薪资等级
FROM employees;

-- 统计各薪资等级人数
SELECT 
    CASE 
        WHEN salary >= 30000 THEN '高薪'
        WHEN salary >= 20000 THEN '中薪'
        ELSE '普通'
    END AS 薪资等级,
    COUNT(*) AS 人数
FROM employees
GROUP BY 
    CASE 
        WHEN salary >= 30000 THEN '高薪'
        WHEN salary >= 20000 THEN '中薪'
        ELSE '普通'
    END;

-- 在ORDER BY中使用CASE
SELECT * FROM employees
ORDER BY 
    CASE department
        WHEN '技术部' THEN 1
        WHEN '销售部' THEN 2
        ELSE 3
    END,
    salary DESC;

子查询

-- 标量子查询（返回单个值）
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- 列子查询（返回一列）
SELECT * FROM employees
WHERE department IN (
    SELECT DISTINCT department FROM employees WHERE salary > 20000
);

-- 行子查询（返回一行）
SELECT * FROM employees
WHERE (department, salary) = (
    SELECT department, MAX(salary) 
    FROM employees 
    GROUP BY department 
    LIMIT 1
);

-- EXISTS子查询
SELECT * FROM employees e1
WHERE EXISTS (
    SELECT 1 FROM employees e2
    WHERE e2.department = e1.department
    AND e2.salary > e1.salary
);

-- FROM子句中的子查询
SELECT 
    department,
    avg_salary
FROM (
    SELECT 
        department,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS dept_avg
WHERE avg_salary > 15000;

UNION操作

-- UNION：合并结果集，去重
SELECT name, department FROM employees WHERE department = '技术部'
UNION
SELECT name, department FROM employees WHERE salary > 20000;

-- UNION ALL：合并结果集，不去重
SELECT name, department FROM employees WHERE department = '技术部'
UNION ALL
SELECT name, department FROM employees WHERE salary > 20000;

-- INTERSECT（MySQL 8.0+）：交集
SELECT name FROM employees WHERE department = '技术部'
INTERSECT
SELECT name FROM employees WHERE salary > 20000;

-- EXCEPT（MySQL 8.0+）：差集
SELECT name FROM employees WHERE department = '技术部'
EXCEPT
SELECT name FROM employees WHERE salary < 20000;

本章小结

本章学习了：

• 基本查询：SELECT、DISTINCT、别名
• 条件查询：WHERE、比较运算符、逻辑运算符
• 模糊查询：LIKE通配符
• 排序：ORDER BY、自定义排序
• 分页：LIMIT、OFFSET
• 聚合函数：COUNT、SUM、AVG、MAX、MIN
• 分组：GROUP BY、HAVING
• 高级技巧：CASE、子查询、UNION

下一章，我们将学习多表查询，了解如何进行表连接操作。