PostgreSQL教程FG034-PG查询过滤：高级条件（IN/EXISTS/BETWEEN）与优化

目录大纲

Part01-基础概念与理论知识
- 1.1 高级过滤条件的概念与作用
- 1.2 常见高级过滤条件
Part02-生产环境规划与建议
- 2.1 过滤条件的选择原则
- 2.2 过滤条件的性能考虑
Part03-生产环境项目实施方案
Part04-生产案例与实战讲解
- 4.1 高级过滤条件实战案例
- 4.2 过滤条件优化实战
Part05-风哥经验总结与分享
- 5.1 过滤条件的性能优化
- 5.2 常见问题与解决方案

Part01-基础概念与理论知识

1.1 高级过滤条件的概念与作用

高级过滤条件是指在SQL查询中用于筛选数据的复杂条件，它们可以帮助我们更精确地获取所需的数据。与基本的等于、大于、小于等条件相比，高级过滤条件提供了更灵活、更强大的筛选能力。

高级过滤条件的主要作用：

精确筛选：根据复杂条件筛选数据
提高查询效率：减少不必要的数据返回
简化查询语句：用简洁的语法表达复杂的筛选逻辑
增强可读性：使查询语句更易于理解

学习交流加群风哥微信: itpux-com

1.2 常见高级过滤条件

PostgreSQL支持以下常见的高级过滤条件：

IN：判断值是否在指定的集合中
EXISTS：判断子查询是否返回结果
BETWEEN：判断值是否在指定的范围内
LIKE：模糊匹配
ILIKE：不区分大小写的模糊匹配
NOT：否定条件
AND/OR：组合多个条件

更多视频教程www.fgedu.net.cn

Part02-生产环境规划与建议

2.1 过滤条件的选择原则

选择合适的过滤条件需要考虑以下因素：

数据分布：了解数据的分布情况，选择合适的过滤条件
查询需求：根据实际查询需求选择合适的过滤条件
性能考虑：不同过滤条件的性能可能不同
可读性：选择易于理解的过滤条件

过滤条件选择建议：

对于少量固定值的筛选，使用IN条件
对于存在性检查，使用EXISTS条件
对于范围查询，使用BETWEEN条件
对于模糊匹配，使用LIKE或ILIKE条件
对于复杂条件，组合使用AND/OR和括号

风哥提示：在生产环境中，应根据实际数据量和查询需求选择合适的过滤条件，以提高查询性能。

2.2 过滤条件的性能考虑

过滤条件的性能影响因素：

索引使用：过滤条件是否能够有效使用索引
数据量：筛选后的数据量大小
条件复杂度：条件的复杂程度
子查询性能：如果使用EXISTS，子查询的性能

性能优化建议：

为过滤条件的列创建索引
使用EXISTS替代IN，特别是当子查询结果集较大时
使用BETWEEN替代多个AND条件
避免在过滤条件中使用函数，这会导致索引失效
合理使用NOT条件，注意其对索引的影响

更多学习教程公众号风哥教程itpux_com

Part03-生产环境项目实施方案

3.1 IN条件的使用

3.1.1 基本IN条件

— 创建测试表
CREATE TABLE fgedu_employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
department VARCHAR(100),
position VARCHAR(100),
salary DECIMAL(10, 2),
hire_date DATE
);

— 插入测试数据
INSERT INTO fgedu_employees (name, department, position, salary, hire_date) VALUES
(‘张三’, ‘技术部’, ‘开发工程师’, 10000.00, ‘2024-01-01’),
(‘李四’, ‘技术部’, ‘测试工程师’, 8000.00, ‘2024-02-01’),
(‘王五’, ‘市场部’, ‘销售经理’, 15000.00, ‘2024-03-01’),
(‘赵六’, ‘市场部’, ‘销售人员’, 9000.00, ‘2024-04-01’),
(‘钱七’, ‘财务部’, ‘会计’, 9500.00, ‘2024-05-01’),
(‘孙八’, ‘财务部’, ‘出纳’, 8000.00, ‘2024-06-01’),
(‘周九’, ‘技术部’, ‘架构师’, 20000.00, ‘2024-07-01’),
(‘吴十’, ‘市场部’, ‘市场总监’, 18000.00, ‘2024-08-01’);

— 使用IN条件筛选部门
SELECT * FROM fgedu_employees
WHERE department IN (‘技术部’, ‘市场部’);

— 使用IN条件筛选职位
SELECT * FROM fgedu_employees
WHERE position IN (‘开发工程师’, ‘销售经理’, ‘架构师’);

— 使用NOT IN条件
SELECT * FROM fgedu_employees
WHERE department NOT IN (‘财务部’);

3.1.2 IN条件与子查询

— 创建部门表
CREATE TABLE fgedu_departments (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
manager VARCHAR(100)
);

— 插入测试数据
INSERT INTO fgedu_departments (name, manager) VALUES
(‘技术部’, ‘周九’),
(‘市场部’, ‘吴十’),
(‘财务部’, ‘钱七’);

— 使用子查询的IN条件
SELECT * FROM fgedu_employees
WHERE department IN (
SELECT name FROM fgedu_departments
WHERE manager LIKE ‘%九%’
);

— 使用子查询的NOT IN条件
SELECT * FROM fgedu_employees
WHERE department NOT IN (
SELECT name FROM fgedu_departments
WHERE manager LIKE ‘%七%’
);

from PostgreSQL视频:www.itpux.com

3.2 EXISTS条件的使用

3.2.1 基本EXISTS条件

— 创建项目表
CREATE TABLE fgedu_projects (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
department VARCHAR(100),
start_date DATE,
end_date DATE
);

— 插入测试数据
INSERT INTO fgedu_projects (name, department, start_date, end_date) VALUES
(‘项目A’, ‘技术部’, ‘2026-01-01’, ‘2026-06-30’),
(‘项目B’, ‘技术部’, ‘2026-02-01’, ‘2026-07-31’),
(‘项目C’, ‘市场部’, ‘2026-03-01’, ‘2026-08-31’);

— 使用EXISTS条件
SELECT * FROM fgedu_employees e
WHERE EXISTS (
SELECT 1 FROM fgedu_projects p
WHERE p.department = e.department
);

— 使用NOT EXISTS条件
SELECT * FROM fgedu_employees e
WHERE NOT EXISTS (
SELECT 1 FROM fgedu_projects p
WHERE p.department = e.department
);

3.2.2 EXISTS条件与相关子查询

— EXISTS条件与相关子查询
SELECT * FROM fgedu_employees e
WHERE EXISTS (
SELECT 1 FROM fgedu_projects p
WHERE p.department = e.department
AND p.start_date >= ‘2026-02-01’
);

— 比较EXISTS和IN的性能
— 使用EXISTS
SELECT * FROM fgedu_employees e
WHERE EXISTS (
SELECT 1 FROM fgedu_projects p
WHERE p.department = e.department
);

— 使用IN
SELECT * FROM fgedu_employees e
WHERE e.department IN (
SELECT DISTINCT department FROM fgedu_projects
);

学习交流加群风哥QQ113257174

3.3 BETWEEN条件的使用

3.3.1 基本BETWEEN条件

— 使用BETWEEN条件筛选薪资范围
SELECT * FROM fgedu_employees
WHERE salary BETWEEN 9000 AND 15000;

— 使用BETWEEN条件筛选日期范围
SELECT * FROM fgedu_employees
WHERE hire_date BETWEEN ‘2024-03-01’ AND ‘2024-06-30’;

— 使用NOT BETWEEN条件
SELECT * FROM fgedu_employees
WHERE salary NOT BETWEEN 8000 AND 10000;

3.3.2 BETWEEN条件的注意事项

— BETWEEN是包含边界值的
SELECT * FROM fgedu_employees
WHERE salary BETWEEN 8000 AND 8000;

— 对于日期时间类型，BETWEEN的边界处理
SELECT * FROM fgedu_employees
WHERE hire_date BETWEEN ‘2024-01-01’ AND ‘2024-01-01’;

— 等同于
SELECT * FROM fgedu_employees
WHERE hire_date >= ‘2024-01-01’ AND hire_date <= '2024-01-01';

Part04-生产案例与实战讲解

4.1 高级过滤条件实战案例

4.1.1 员工数据筛选

— 筛选技术部或市场部且薪资在10000以上的员工
SELECT * FROM fgedu_employees
WHERE department IN (‘技术部’, ‘市场部’)
AND salary > 10000
ORDER BY salary DESC;

— 筛选2024年第二季度入职的员工
SELECT * FROM fgedu_employees
WHERE hire_date BETWEEN ‘2024-04-01’ AND ‘2024-06-30’
ORDER BY hire_date;

— 筛选有项目的部门的员工
SELECT * FROM fgedu_employees e
WHERE EXISTS (
SELECT 1 FROM fgedu_projects p
WHERE p.department = e.department
)
ORDER BY department, name;

4.1.2 销售数据筛选

— 创建销售表
CREATE TABLE fgedu_sales (
id SERIAL PRIMARY KEY,
product_id INTEGER,
product_name VARCHAR(100),
category VARCHAR(50),
sale_date DATE,
quantity INTEGER,
unit_price DECIMAL(10, 2),
total_amount DECIMAL(10, 2)
);

— 插入测试数据
INSERT INTO fgedu_sales (product_id, product_name, category, sale_date, quantity, unit_price, total_amount) VALUES
(1, ‘手机’, ‘电子产品’, ‘2026-04-01’, 10, 5999.00, 59990.00),
(2, ‘电脑’, ‘电子产品’, ‘2026-04-02’, 5, 9999.00, 49995.00),
(3, ‘平板’, ‘电子产品’, ‘2026-04-03’, 8, 3999.00, 31992.00),
(4, ‘耳机’, ‘电子产品’, ‘2026-04-04’, 15, 999.00, 14985.00),
(5, ‘键盘’, ‘电脑配件’, ‘2026-04-05’, 12, 1999.00, 23988.00),
(6, ‘鼠标’, ‘电脑配件’, ‘2026-04-06’, 20, 599.00, 11980.00),
(7, ‘手机’, ‘电子产品’, ‘2026-04-07’, 12, 5999.00, 71988.00),
(8, ‘电脑’, ‘电子产品’, ‘2026-04-08’, 6, 9999.00, 59994.00);

— 筛选特定类别的产品销售
SELECT * FROM fgedu_sales
WHERE category IN (‘电子产品’)
AND sale_date BETWEEN ‘2026-04-01’ AND ‘2026-04-05’
ORDER BY sale_date, total_amount DESC;

— 筛选销售额在20000到50000之间的销售记录
SELECT * FROM fgedu_sales
WHERE total_amount BETWEEN 20000 AND 50000
ORDER BY total_amount;

— 筛选特定产品的销售记录
SELECT * FROM fgedu_sales
WHERE product_name IN (‘手机’, ‘电脑’)
AND quantity > 5
ORDER BY product_name, sale_date;

风哥提示：在使用高级过滤条件时，应注意条件的组合方式，确保查询结果符合预期。

4.2 过滤条件优化实战

4.2.1 索引优化

— 为过滤条件的列创建索引
CREATE INDEX idx_employees_department ON fgedu_employees(department);
CREATE INDEX idx_employees_salary ON fgedu_employees(salary);
CREATE INDEX idx_employees_hire_date ON fgedu_employees(hire_date);
CREATE INDEX idx_sales_category ON fgedu_sales(category);
CREATE INDEX idx_sales_sale_date ON fgedu_sales(sale_date);
CREATE INDEX idx_sales_total_amount ON fgedu_sales(total_amount);

— 分析表以更新统计信息
ANALYZE fgedu_employees;
ANALYZE fgedu_sales;

— 查看查询执行计划
EXPLAIN ANALYZE
SELECT * FROM fgedu_employees
WHERE department IN (‘技术部’, ‘市场部’)
AND salary BETWEEN 9000 AND 15000;

EXPLAIN ANALYZE
SELECT * FROM fgedu_sales
WHERE category IN (‘电子产品’)
AND sale_date BETWEEN ‘2026-04-01’ AND ‘2026-04-05’;

4.2.2 过滤条件优化

— 使用EXISTS替代IN
— 优化前：使用IN
EXPLAIN ANALYZE
SELECT * FROM fgedu_employees e
WHERE e.department IN (
SELECT DISTINCT department FROM fgedu_projects
);

— 优化后：使用EXISTS
EXPLAIN ANALYZE
SELECT * FROM fgedu_employees e
WHERE EXISTS (
SELECT 1 FROM fgedu_projects p
WHERE p.department = e.department
);

— 使用BETWEEN替代多个AND条件
— 优化前：使用多个AND条件
EXPLAIN ANALYZE
SELECT * FROM fgedu_employees
WHERE salary >= 9000 AND salary <= 15000; -- 优化后：使用BETWEEN EXPLAIN ANALYZE SELECT * FROM fgedu_employees WHERE salary BETWEEN 9000 AND 15000; -- 避免在过滤条件中使用函数 -- 优化前：在过滤条件中使用函数 EXPLAIN ANALYZE SELECT * FROM fgedu_employees WHERE EXTRACT(YEAR FROM hire_date) = 2024; -- 优化后：避免使用函数 EXPLAIN ANALYZE SELECT * FROM fgedu_employees WHERE hire_date BETWEEN '2024-01-01' AND '2024-12-31';