实战：基础查询综合

Q: LIMIT OFFSET 分页时，如果数据有新增或删除，会不会出现重复或遗漏？

会的。如果在翻页过程中有数据变化，用 OFFSET 分页可能出现重复或遗漏数据。更稳定的方案是用"游标分页"： WHERE id > 上次最后一条的 id LIMIT 10 ，但这需要每条数据有连续的排序依据。

Q: JOIN 查询时如果两个表有同名列怎么办？

用表别名或列别名区分。比如 SELECT e.name, d.name AS dept_name FROM employees e JOIN departments d ON e.department_id = d.id; 。建议养成给列起别名的习惯，让输出更清晰。

前 5 课我们学会了 SQL 的"听、说、读、写"——建表、查询、过滤、排序、增删改、数据类型。现在是时候把这些技能组合起来，解决真实的业务问题了。本课没有新语法，只有实战。

项目需求

你是一家电商公司的数据分析师，需要从公司的数据库中提取以下信息：

员工管理：查询特定部门的员工、按薪资排序、分页展示
商品筛选：按价格区间和库存筛选商品、模糊搜索商品名
订单分析：查询最近的订单、统计订单金额、更新订单状态
数据维护：批量更新价格、清理过期数据、插入新数据

数据库包含四张表：employees、departments、orders、products。

完整代码实现

第一步：创建数据库和表

SQL

-- 创建部门表
CREATE TABLE departments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    location TEXT
);

-- 创建员工表
CREATE TABLE employees (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    department_id INTEGER,
    salary DECIMAL(10,2),
    hire_date DATE,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- 创建商品表
CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    category TEXT,
    price DECIMAL(10,2),
    stock INTEGER DEFAULT 0
);

-- 创建订单表
CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_name TEXT NOT NULL,
    product_id INTEGER,
    quantity INTEGER,
    order_date DATE,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

第二步：插入测试数据

SQL

-- 插入部门数据
INSERT INTO departments (name, location) VALUES
    ('技术部', '北京'),
    ('市场部', '上海'),
    ('财务部', '北京'),
    ('人事部', '广州');

-- 插入员工数据
INSERT INTO employees (name, department_id, salary, hire_date) VALUES
    ('张三', 1, 15000.00, '2023-01-15'),
    ('李四', 1, 18000.00, '2022-06-01'),
    ('王五', 2, 12000.00, '2023-03-20'),
    ('赵六', 3, 13000.00, '2021-11-10'),
    ('钱七', 1, 20000.00, '2020-08-05'),
    ('孙八', 2, 11000.00, '2024-01-10'),
    ('周九', NULL, 9000.00, '2024-05-01'),
    ('吴十', 3, 14000.00, '2022-09-15');

-- 插入商品数据
INSERT INTO products (name, category, price, stock) VALUES
    ('iPhone 15', '手机', 5999.00, 100),
    ('MacBook Pro', '电脑', 12999.00, 50),
    ('AirPods Pro', '配件', 1899.00, 200),
    ('iPad Air', '平板', 4799.00, 80),
    ('Apple Watch', '手表', 2999.00, 150),
    ('Magic Keyboard', '配件', 999.00, 300),
    ('Mac Mini', '电脑', 4499.00, 0),
    ('iPhone 14', '手机', 4999.00, 20);

-- 插入订单数据
INSERT INTO orders (customer_name, product_id, quantity, order_date) VALUES
    ('小明', 1, 1, '2024-06-01'),
    ('小红', 3, 2, '2024-06-02'),
    ('小刚', 2, 1, '2024-06-03'),
    ('小明', 5, 1, '2024-06-05'),
    ('小李', 4, 3, '2024-06-10'),
    ('小红', 6, 5, '2024-06-15'),
    ('小王', 1, 2, '2024-06-20'),
    ('小刚', 8, 1, '2024-06-25');

第三步：员工管理查询

SQL

-- Q1：查询技术部所有员工，按薪资从高到低排列
SELECT name, salary, hire_date
FROM employees
WHERE department_id = 1
ORDER BY salary DESC;

输出：

TEXT

name    salary    hire_date
------  --------  ----------
钱七    20000.00  2020-08-05
李四    18000.00  2022-06-01
张三    15000.00  2023-01-15

SQL

-- Q2：查询薪资排名前 3 的员工（分页：第1页，每页3条）
SELECT name, salary, department_id
FROM employees
ORDER BY salary DESC
LIMIT 3 OFFSET 0;

输出：

TEXT

name    salary    department_id
------  --------  -------------
钱七    20000.00  1
李四    18000.00  1
张三    15000.00  1

SQL

-- Q3：查询第 2 页（每页 3 条）
SELECT name, salary, department_id
FROM employees
ORDER BY salary DESC
LIMIT 3 OFFSET 3;

输出：

TEXT

name    salary    department_id
------  --------  -------------
吴十    14000.00  3
赵六    13000.00  3
王五    12000.00  2

SQL

-- Q4：查询没有分配部门的员工
SELECT name, salary
FROM employees
WHERE department_id IS NULL;

输出：

TEXT

name    salary
------  --------
周九    9000.00

第四步：商品筛选查询

SQL

-- Q5：查询价格在 1000 到 5000 之间的商品，按价格升序
SELECT name, category, price, stock
FROM products
WHERE price BETWEEN 1000 AND 5000
ORDER BY price ASC;

输出：

TEXT

name            category  price    stock
--------------  --------  -------  -----
AirPods Pro     配件      1899.00  200
Apple Watch     手表      2999.00  150
iPad Air        平板      4799.00  80
Mac Mini        电脑      4499.00  0
Magic Keyboard  配件      999.00   300

SQL

-- Q6：模糊搜索商品名包含"iPhone"的商品
SELECT name, price, stock
FROM products
WHERE name LIKE '%iPhone%';

输出：

TEXT

name        price    stock
----------  -------  -----
iPhone 15   5999.00  100
iPhone 14   4999.00  20

SQL

-- Q7：查询有库存（stock > 0）且价格低于 3000 的商品
SELECT name, price, stock
FROM products
WHERE stock > 0 AND price < 3000
ORDER BY price DESC;

输出：

TEXT

name            price    stock
--------------  -------  -----
AirPods Pro     1899.00  200
Magic Keyboard  999.00   300

第五步：订单分析查询

SQL

-- Q8：查询最近 5 笔订单，显示商品名和客户名
SELECT o.customer_name, p.name AS product_name,
       o.quantity, o.order_date
FROM orders o
JOIN products p ON o.product_id = p.id
ORDER BY o.order_date DESC
LIMIT 5;

输出：

TEXT

customer_name  product_name   quantity  order_date
-------------  -------------  --------  ----------
小刚           iPhone 14      1         2024-06-25
小王           iPhone 15      2         2024-06-20
小红           Magic Keyboard 5         2024-06-15
小李           iPad Air       3         2024-06-10
小明           Apple Watch    1         2024-06-05

SQL

-- Q9：查询每笔订单的金额（单价 × 数量），按金额降序
SELECT o.customer_name, p.name AS product_name,
       p.price, o.quantity,
       (p.price * o.quantity) AS total_amount
FROM orders o
JOIN products p ON o.product_id = p.id
ORDER BY total_amount DESC;

输出：

TEXT

customer_name  product_name   price     quantity  total_amount
-------------  -------------  --------  --------  ------------
小刚           MacBook Pro    12999.00  1         12999.00
小王           iPhone 15      5999.00   2         11998.00
小李           iPad Air       4799.00   3         14397.00
小明           iPhone 15      5999.00   1         5999.00
小刚           iPhone 14      4999.00   1         4999.00
小明           Apple Watch    2999.00   1         2999.00
小红           AirPods Pro    1899.00   2         3798.00
小红           Magic Keyboard 999.00    5         4995.00

第六步：数据维护操作

SQL

-- Q10：将所有"配件"类商品价格上调 10%
-- 先确认影响范围
SELECT name, price FROM products WHERE category = '配件';

调整前：

TEXT

name            price
--------------  -------
AirPods Pro     1899.00
Magic Keyboard  999.00

SQL

-- 执行更新
UPDATE products
SET price = ROUND(price * 1.1, 2)
WHERE category = '配件';

-- 验证结果
SELECT name, price FROM products WHERE category = '配件';

调整后：

TEXT

name            price
--------------  -------
AirPods Pro     2088.90
Magic Keyboard  1098.90

SQL

-- Q11：将无库存商品（stock = 0）标记为下架状态
-- 这里我们删除无库存的商品作为示例
-- 先确认
SELECT id, name, stock FROM products WHERE stock = 0;

确认结果：

TEXT

id  name      stock
--  --------  -----
7   Mac Mini  0

SQL

-- 删除无库存商品
DELETE FROM products WHERE stock = 0;

-- Q12：插入新商品
INSERT INTO products (name, category, price, stock)
VALUES ('AirTag', '配件', 229.00, 500);

-- 最终查看所有商品
SELECT id, name, category, price, stock
FROM products
ORDER BY id;

最终商品列表：

TEXT

id  name            category  price     stock
--  --------------  --------  --------  -----
1   iPhone 15       手机      5999.00   100
2   MacBook Pro     电脑      12999.00  50
3   AirPods Pro     配件      2088.90   200
4   iPad Air        平板      4799.00   80
5   Apple Watch     手表      2999.00   150
6   Magic Keyboard  配件      1098.90   300
8   iPhone 14       手机      4999.00   20
9   AirTag          配件      229.00    500

代码走读

查询编号	用到的知识点	关键技巧
Q1-Q3	SELECT + WHERE + ORDER BY + LIMIT/OFFSET	分页查询用 `LIMIT n OFFSET (页码-1)*n`
Q4	WHERE + IS NULL	判断 NULL 必须用 IS NULL
Q5	BETWEEN + ORDER BY	BETWEEN 包含两端值
Q6	LIKE + 通配符 `%`	`%关键词%` 匹配任意位置
Q7	多条件 AND + 比较运算符	条件组合过滤
Q8	JOIN + ORDER BY + LIMIT	多表关联查询
Q9	JOIN + 表达式计算 + ORDER BY	在 SELECT 中做计算
Q10	UPDATE + WHERE + ROUND	更新前先 SELECT 确认
Q11	DELETE + WHERE	删除前先 SELECT 确认
Q12	INSERT + 最终验证	插入后查询验证

❓ 常见问题

Q LIMIT OFFSET 分页时，如果数据有新增或删除，会不会出现重复或遗漏？

A 会的。如果在翻页过程中有数据变化，用 OFFSET 分页可能出现重复或遗漏数据。更稳定的方案是用"游标分页"：WHERE id > 上次最后一条的 id LIMIT 10，但这需要每条数据有连续的排序依据。

Q LIKE '%关键词%' 会导致索引失效，数据量大时怎么办？

A LIKE 以通配符开头确实无法使用普通索引。数据量大时可以考虑：①使用全文索引（FTS5，SQLite 支持）；②在应用层用 Elasticsearch 等搜索引擎；③如果只需要前缀匹配，用 LIKE '关键词%' 可以利用索引。

Q JOIN 查询时如果两个表有同名列怎么办？

A 用表别名或列别名区分。比如 SELECT e.name, d.name AS dept_name FROM employees e JOIN departments d ON e.department_id = d.id;。建议养成给列起别名的习惯，让输出更清晰。

Q UPDATE 和 DELETE 操作可以撤销吗？

A 如果在事务中（BEGIN...COMMIT 之间），可以用 ROLLBACK 撤销。如果已经 COMMIT，在 SQLite 中基本无法恢复（除非有备份）。MySQL 可以通过 binlog 回放恢复。所以生产环境操作前一定要备份。

📖 小节

综合运用 SELECT + WHERE + ORDER BY + LIMIT 实现复杂查询和分页

用 IS NULL 处理空值，用 LIKE 做模糊匹配，用 BETWEEN 做范围查询

多表查询用 JOIN 关联，计算字段可在 SELECT 中用表达式

UPDATE/DELETE 前先 SELECT 确认，这是最重要的安全习惯

分页用 LIMIT + OFFSET，大数据量考虑游标分页

模糊搜索以 % 开头会失效索引，大数据量需考虑全文索引

📝 作业

练习 1（⭐）

编写查询完成以下任务：

查询 2023 年入职的所有员工，按入职日期升序排列

查询"手机"类别中价格最高的商品

查询客户"小明"的所有订单，显示商品名和数量

练习 2（⭐⭐）

编写查询完成以下任务：

查询每个部门的员工数量（提示：需要用 GROUP BY，可以先跳过后续课程再回来挑战）

查询所有订单的总金额

将"电脑"类商品价格下调 5%，然后查询验证

练习 3（⭐⭐⭐）

模拟一个"商品库存预警"场景：

查询所有库存低于 50 的商品，按库存升序排列

将这些商品信息插入到一个新建的 low_stock_alert 表中

给这些商品的库存各增加 100

验证更新结果

下一课

👉 07-join-intro - 连接查询入门

← Previous: 数据类型

Next: 连接查询入门 →

Web-Tutorial 技术团队

由多位开发者共同维护的编程教程平台。每篇教程由对应领域的开发者编写和审核，确保内容准确可靠。如发现任何问题，欢迎向我们反馈。

关于我们

404 Not Found

实战：基础查询综合

实战：基础查询综合

项目需求

完整代码实现

第一步：创建数据库和表

第二步：插入测试数据

第三步：员工管理查询

第四步：商品筛选查询

第五步：订单分析查询

第六步：数据维护操作

代码走读

❓ 常见问题

📖 小节

📝 作业

练习 1（⭐）

练习 2（⭐⭐）

练习 3（⭐⭐⭐）

下一课

🙏 帮我们做得更好