MySQL查询语句完全指南：从基础到高阶实战_select * from orders where id

本文链接：https://blog.youkuaiyun.com/qq_51508784/article/details/146050876

一、开篇：为什么选择MySQL查询作为切入点？

根据Stack Overflow 2023开发者调查，MySQL以51.1%的使用率蝉联最受欢迎数据库榜首。其查询语句作为数据操作的基石，支撑着全球数百万应用的日常运转。本指南将通过2000+字详解和30+个实战案例，带您系统掌握从基础检索到复杂分析的全套技能。

二、环境准备：示例数据表结构

2.1 员工信息表（employees）

列名	类型	约束
emp_id	INT	PRIMARY KEY
name	VARCHAR(50)	NOT NULL
department	VARCHAR(20)	DEFAULT '待分配'
salary	DECIMAL(10,2)	CHECK(salary>0)
hire_date	DATE

2.2 订单表（orders）

列名	类型	说明
order_id	INT	主键
emp_id	INT	外键关联员工
amount	DECIMAL(10,2)	订单金额
order_date	DATETIME	默认当前时间

三、基础查询全解析

3.1 SELECT基础语法

-- 基础检索
SELECT * FROM employees;  -- 全字段查询

-- 字段投影
SELECT name, salary, hire_date FROM employees; 

-- 算术运算
SELECT name, salary, salary*1.2 AS next_year_salary FROM employees;

-- 函数应用
SELECT UPPER(name), DATE_FORMAT(hire_date, '%Y/%m') FROM employees;

3.2 WHERE条件过滤

-- 比较运算符
SELECT * FROM employees WHERE salary >= 10000;

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

-- 逻辑运算符组合
SELECT * FROM employees
WHERE department = '技术部' 
  AND (salary > 15000 OR YEAR(hire_date) = 2023);

-- NULL值处理
SELECT * FROM employees WHERE department IS NULL;

3.3 结果排序与限制

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

-- 分页查询（每页10条）
SELECT * FROM orders
ORDER BY order_date DESC
LIMIT 20, 10;  -- 第三页数据：偏移量=(3-1)*10=20

四、聚合分析与分组查询

4.1 常用聚合函数

函数	说明	示例
COUNT()	统计行数	COUNT(DISTINCT dept)
SUM()	求和	SUM(salary)
AVG()	平均值	AVG(DATEDIFF(NOW(), hire_date)/365)
MAX()/MIN()	极值	MAX(order_date)

4.2 GROUP BY实战

-- 部门薪资统计
SELECT department, 
       COUNT(*) AS headcount,
       AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

-- 多列分组：按月统计订单
SELECT YEAR(order_date) AS year,
       MONTH(order_date) AS month,
       COUNT(*) AS order_count,
       SUM(amount) AS total_amount
FROM orders
GROUP BY year, month;

4.3 HAVING筛选分组

-- 筛选平均薪资过万的部门
SELECT department, AVG(salary) avg_salary
FROM employees
GROUP BY department
HAVING avg_salary > 10000;

-- 月订单超百万元的月份
SELECT YEAR(order_date) AS year,
       MONTH(order_date) AS month,
       SUM(amount) AS total
FROM orders
GROUP BY year, month
HAVING total >= 1000000;

五、高级查询技巧

5.1 多表连接查询

-- 内连接：员工订单数据
SELECT e.name, o.order_id, o.amount
FROM employees e
INNER JOIN orders o ON e.emp_id = o.emp_id;

-- 左连接：包含无订单员工
SELECT e.name, COUNT(o.order_id) AS order_count
FROM employees e
LEFT JOIN orders o ON e.emp_id = o.emp_id
GROUP BY e.emp_id;

5.2 子查询应用

-- 标量子查询：高于部门平均薪资的员工
SELECT name, salary, department
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department = e1.department
);

-- EXISTS子查询：有订单的技术部员工
SELECT name 
FROM employees e
WHERE department = '技术部'
  AND EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.emp_id = e.emp_id
);

5.3 联合查询（UNION）

-- 合并不同部门的员工名单
SELECT name FROM employees WHERE department = '销售部'
UNION ALL
SELECT name FROM contractors WHERE project = '销售支持';

-- 统计不同薪资区间人数
SELECT '高薪' AS level, COUNT(*) 
FROM employees WHERE salary >= 20000
UNION
SELECT '中薪', COUNT(*) 
FROM employees WHERE salary BETWEEN 10000 AND 19999
UNION
SELECT '基础', COUNT(*) 
FROM employees WHERE salary < 10000;

六、综合实战案例：电商数据分析

6.1 核心指标分析

-- 各品类销售额Top10
SELECT p.category,
       SUM(oi.quantity * oi.unit_price) AS sales,
       RANK() OVER(ORDER BY SUM(oi.quantity * oi.unit_price) DESC) AS rank
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
GROUP BY p.category
ORDER BY sales DESC
LIMIT 10;

-- 用户复购率计算
WITH purchase_stats AS (
    SELECT user_id,
           COUNT(DISTINCT DATE_FORMAT(order_date, '%Y-%m')) AS active_months
    FROM orders
    GROUP BY user_id
)
SELECT 
    CASE 
        WHEN active_months >= 3 THEN '高复购'
        WHEN active_months = 2 THEN '中复购'
        ELSE '低复购' 
    END AS segment,
    COUNT(*) AS user_count
FROM purchase_stats
GROUP BY segment;

6.2 异常数据检测

-- 检测异常大额订单
SELECT o.order_id, o.amount, e.name
FROM orders o
JOIN employees e ON o.emp_id = e.emp_id
WHERE o.amount > 3 * (
    SELECT AVG(amount) 
    FROM orders 
    WHERE emp_id = o.emp_id
);

-- 找出薪资倒挂的经理
SELECT m.name AS manager,
       e.name AS employee,
       e.salary AS emp_salary,
       m.salary AS mgr_salary
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id
WHERE e.salary > m.salary;

七、性能优化与避坑指南

7.1 索引黄金法则

-- 为高频查询字段创建索引
CREATE INDEX idx_department ON employees(department);
CREATE INDEX idx_order_date ON orders(order_date);

-- 复合索引最左前缀原则
CREATE INDEX idx_dept_salary ON employees(department, salary);

7.2 常见错误示例

-- 错误1：GROUP BY缺失非聚合列
SELECT name, AVG(salary)  -- name未在GROUP BY中
FROM employees
GROUP BY department;

-- 错误2：混淆WHERE和HAVING
SELECT department, AVG(salary)
FROM employees
WHERE AVG(salary) > 10000  -- WHERE不能使用聚合函数
GROUP BY department;

-- 错误3：错误的分页偏移量
SELECT * FROM products
LIMIT 5, 5;  -- 第2页数据：每页5条，偏移量=(2-1)*5=5

7.3 优化技巧

EXPLAIN分析执行计划

EXPLAIN SELECT * FROM employees WHERE department = '技术部';

避免全表扫描

-- 优化前
SELECT * FROM orders WHERE YEAR(order_date) = 2023;

-- 优化后（使用范围查询）
SELECT * FROM orders 
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

分页查询优化

-- 传统分页（数据量大时慢）
SELECT * FROM orders LIMIT 1000000, 20;

-- 优化方案：使用游标分页
SELECT * FROM orders 
WHERE order_id > 1000000
ORDER BY order_id
LIMIT 20;