pgsql-06 基础查询语句

2026-03-16 约 2854 字预计阅读 6 分钟

06 - 基础查询语句

1. 📖 概述

本章详细介绍PostgreSQL的基础查询语句,包括SELECT、WHERE、ORDER BY等,并与MySQL进行对比。

2. 🔍 SELECT基础查询

2.1 查询所有列

-- PostgreSQL & MySQL
SELECT * FROM users;

-- 推荐:明确指定列名
SELECT id, username, email, created_at FROM users;

2.2 查询指定列

-- PostgreSQL
SELECT
    username,
    email,
    created_at
FROM users;

-- 列别名
SELECT
    username AS user_name,
    email AS user_email,
    created_at AS registration_date
FROM users;

-- 不使用AS关键字
SELECT
    username "User Name",
    email "Email Address"
FROM users;

2.3 DISTINCT去重

-- PostgreSQL
SELECT DISTINCT city FROM users;

-- 多列去重
SELECT DISTINCT city, country FROM users;

-- DISTINCT ON (PostgreSQL独有)
SELECT DISTINCT ON (city) city, username, email
FROM users
ORDER BY city, created_at DESC;
-- 每个城市返回一条最新注册的用户

-- MySQL只有普通DISTINCT
-- SELECT DISTINCT city FROM users;

3. 🎯 WHERE条件查询

3.1 基本比较运算符

-- PostgreSQL & MySQL
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE price >= 100;
SELECT * FROM products WHERE price < 50;
SELECT * FROM products WHERE price <= 50;
SELECT * FROM products WHERE price = 99.99;
SELECT * FROM products WHERE price != 99.99;  -- 或 <>

3.2 逻辑运算符

-- AND
SELECT * FROM products
WHERE price > 50 AND stock > 0;

-- OR
SELECT * FROM products
WHERE category = 'Electronics' OR category = 'Books';

-- NOT
SELECT * FROM users
WHERE NOT is_active;

-- 组合使用
SELECT * FROM products
WHERE (price > 100 OR category = 'Premium')
  AND stock > 0;

3.3 BETWEEN范围查询

-- PostgreSQL & MySQL
SELECT * FROM products
WHERE price BETWEEN 50 AND 100;

-- 等价于
SELECT * FROM products
WHERE price >= 50 AND price <= 100;

-- 日期范围
SELECT * FROM orders
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

3.4 IN和NOT IN

-- IN
SELECT * FROM products
WHERE category IN ('Electronics', 'Books', 'Toys');

-- NOT IN
SELECT * FROM products
WHERE category NOT IN ('Clearance', 'Discontinued');

-- 子查询
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE is_premium);

3.5 LIKE模糊查询

-- PostgreSQL & MySQL
-- %: 任意字符(0个或多个)
-- _: 单个字符

SELECT * FROM users WHERE username LIKE 'john%';      -- john开头
SELECT * FROM users WHERE username LIKE '%smith';     -- smith结尾
SELECT * FROM users WHERE username LIKE '%admin%';    -- 包含admin
SELECT * FROM users WHERE username LIKE 'use_';       -- 4个字符,use开头

-- NOT LIKE
SELECT * FROM users WHERE email NOT LIKE '%@gmail.com';

-- PostgreSQL: ILIKE (不区分大小写)
SELECT * FROM users WHERE username ILIKE 'JOHN%';

-- MySQL: 使用LOWER/UPPER
-- SELECT * FROM users WHERE LOWER(username) LIKE 'john%';

3.6 正则表达式(PostgreSQL)

-- PostgreSQL: ~ 运算符
SELECT * FROM users WHERE email ~ '^[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,}$';

-- 不区分大小写
SELECT * FROM users WHERE username ~* '^admin';

-- 不匹配
SELECT * FROM users WHERE email !~ '@test\.com$';

-- MySQL: REGEXP 或 RLIKE
-- SELECT * FROM users WHERE email REGEXP '^[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,}$';

3.7 NULL值处理

-- IS NULL
SELECT * FROM users WHERE phone IS NULL;

-- IS NOT NULL
SELECT * FROM users WHERE email IS NOT NULL;

-- 注意:不能使用 = NULL 或 != NULL
-- 错误:
-- SELECT * FROM users WHERE phone = NULL;  ❌

-- COALESCE:返回第一个非NULL值
SELECT
    username,
    COALESCE(phone, 'No phone') AS contact
FROM users;

-- NULLIF:如果相等返回NULL
SELECT NULLIF(stock, 0) FROM products;

4. 📊 排序 ORDER BY

4.1 基本排序

-- 升序(默认)
SELECT * FROM products ORDER BY price;
SELECT * FROM products ORDER BY price ASC;

-- 降序
SELECT * FROM products ORDER BY price DESC;

-- 多列排序
SELECT * FROM products
ORDER BY category ASC, price DESC;

4.2 NULL值排序

-- PostgreSQL: NULLS FIRST / NULLS LAST
SELECT * FROM users
ORDER BY phone NULLS FIRST;   -- NULL值排在前面

SELECT * FROM users
ORDER BY phone DESC NULLS LAST;  -- NULL值排在后面

-- MySQL默认NULL排在前面(ASC)或后面(DESC)

4.3 按表达式排序

-- 按计算结果排序
SELECT
    username,
    LENGTH(username) AS name_length
FROM users
ORDER BY LENGTH(username) DESC;

-- 按CASE表达式排序
SELECT * FROM orders
ORDER BY
    CASE status
        WHEN 'urgent' THEN 1
        WHEN 'pending' THEN 2
        WHEN 'processing' THEN 3
        ELSE 4
    END;

5. 📈 限制结果集

5.1 LIMIT和OFFSET

-- PostgreSQL & MySQL 5.7+
-- 前10条记录
SELECT * FROM products LIMIT 10;

-- 跳过前20条,取10条(分页)
SELECT * FROM products LIMIT 10 OFFSET 20;

-- PostgreSQL简写
SELECT * FROM products OFFSET 20 LIMIT 10;

-- 分页示例(第3页,每页10条)
SELECT * FROM products
ORDER BY id
LIMIT 10 OFFSET 20;  -- (page - 1) * page_size

-- MySQL老语法
-- SELECT * FROM products LIMIT 20, 10;  -- LIMIT offset, count

5.2 FETCH(SQL标准)

-- PostgreSQL: FETCH (更标准的写法)
SELECT * FROM products
ORDER BY id
OFFSET 20 ROWS
FETCH FIRST 10 ROWS ONLY;

-- MySQL 8.0+ 也支持FETCH

6. 🔢 聚合查询

6.1 基本聚合函数

-- COUNT: 计数
SELECT COUNT(*) FROM users;                    -- 所有行
SELECT COUNT(phone) FROM users;                 -- 非NULL值
SELECT COUNT(DISTINCT city) FROM users;         -- 去重计数

-- SUM: 求和
SELECT SUM(amount) FROM orders;

-- AVG: 平均值
SELECT AVG(price) FROM products;

-- MAX/MIN: 最大/最小值
SELECT MAX(price), MIN(price) FROM products;

-- 组合使用
SELECT
    COUNT(*) AS total_products,
    SUM(stock) AS total_stock,
    AVG(price) AS avg_price,
    MAX(price) AS max_price,
    MIN(price) AS min_price
FROM products;

6.2 GROUP BY分组

-- 按单列分组
SELECT
    category,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price
FROM products
GROUP BY category;

-- 按多列分组
SELECT
    category,
    brand,
    COUNT(*) AS count,
    AVG(price) AS avg_price
FROM products
GROUP BY category, brand
ORDER BY category, brand;

6.3 HAVING过滤分组

-- HAVING: 对分组后的结果进行过滤
SELECT
    category,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING COUNT(*) > 10 AND AVG(price) > 50;

-- WHERE vs HAVING
-- WHERE: 分组前过滤
-- HAVING: 分组后过滤

SELECT
    category,
    AVG(price) AS avg_price
FROM products
WHERE stock > 0              -- 先过滤库存>0的产品
GROUP BY category
HAVING AVG(price) > 100;     -- 再过滤平均价格>100的分类

7. 🔗 子查询

7.1 WHERE子查询

-- 标量子查询
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- IN子查询
SELECT * FROM orders
WHERE user_id IN (
    SELECT id FROM users WHERE is_premium = TRUE
);

-- EXISTS子查询
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- NOT EXISTS
SELECT * FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

7.2 FROM子查询

-- 子查询作为表
SELECT
    category,
    avg_price
FROM (
    SELECT
        category,
        AVG(price) AS avg_price,
        COUNT(*) AS count
    FROM products
    GROUP BY category
) AS category_stats
WHERE avg_price > 100;

7.3 SELECT子查询

-- 列子查询
SELECT
    username,
    email,
    (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count
FROM users;

8. 🎨 CASE表达式

8.1 简单CASE

-- PostgreSQL & MySQL
SELECT
    product_name,
    price,
    CASE category
        WHEN 'Electronics' THEN 'Tech'
        WHEN 'Books' THEN 'Media'
        WHEN 'Clothing' THEN 'Fashion'
        ELSE 'Other'
    END AS category_type
FROM products;

8.2 搜索CASE

-- 更灵活的CASE表达式
SELECT
    product_name,
    price,
    CASE
        WHEN price < 20 THEN 'Cheap'
        WHEN price < 50 THEN 'Moderate'
        WHEN price < 100 THEN 'Expensive'
        ELSE 'Premium'
    END AS price_range,
    CASE
        WHEN stock = 0 THEN 'Out of Stock'
        WHEN stock < 10 THEN 'Low Stock'
        ELSE 'In Stock'
    END AS stock_status
FROM products;

9. 🔧 实用查询技巧

9.1 字符串操作

-- PostgreSQL
SELECT
    username,
    CONCAT(first_name, ' ', last_name) AS full_name,
    first_name || ' ' || last_name AS full_name_concat,  -- 推荐
    UPPER(username) AS upper_name,
    LOWER(email) AS lower_email,
    LENGTH(username) AS name_length,
    SUBSTRING(email FROM 1 FOR 10) AS email_prefix,
    POSITION('@' IN email) AS at_position,
    SPLIT_PART(email, '@', 1) AS email_user,
    SPLIT_PART(email, '@', 2) AS email_domain
FROM users;

-- MySQL类似
-- SELECT
--     CONCAT(first_name, ' ', last_name) AS full_name,
--     UPPER(username),
--     SUBSTRING(email, 1, 10),
--     SUBSTRING_INDEX(email, '@', 1) AS email_user
-- FROM users;

9.2 日期操作

-- PostgreSQL
SELECT
    created_at,
    created_at::DATE AS date_only,
    EXTRACT(YEAR FROM created_at) AS year,
    EXTRACT(MONTH FROM created_at) AS month,
    EXTRACT(DAY FROM created_at) AS day,
    DATE_TRUNC('month', created_at) AS month_start,
    created_at + INTERVAL '1 day' AS tomorrow,
    AGE(NOW(), created_at) AS account_age,
    TO_CHAR(created_at, 'YYYY-MM-DD HH24:MI:SS') AS formatted
FROM users;

-- 查询今天的数据
SELECT * FROM orders WHERE created_at::DATE = CURRENT_DATE;

-- 查询最近7天
SELECT * FROM orders WHERE created_at >= NOW() - INTERVAL '7 days';

-- 查询本月
SELECT * FROM orders
WHERE DATE_TRUNC('month', created_at) = DATE_TRUNC('month', NOW());

9.3 数值操作

-- PostgreSQL
SELECT
    price,
    ROUND(price, 2) AS rounded,
    CEIL(price) AS ceiling,
    FLOOR(price) AS floor,
    ABS(profit) AS absolute,
    price * 0.9 AS discounted,
    MOD(id, 2) AS is_even
FROM products;

10. 📋 完整查询示例

10.1 电商查询示例

-- 查询最近30天的销售情况
SELECT
    DATE(created_at) AS sale_date,
    COUNT(*) AS order_count,
    SUM(total_amount) AS daily_revenue,
    AVG(total_amount) AS avg_order_value,
    COUNT(DISTINCT user_id) AS unique_customers
FROM orders
WHERE created_at >= NOW() - INTERVAL '30 days'
  AND status IN ('completed', 'shipped')
GROUP BY DATE(created_at)
ORDER BY sale_date DESC;

-- 产品库存预警
SELECT
    p.id,
    p.name,
    p.stock,
    p.price,
    c.name AS category,
    CASE
        WHEN p.stock = 0 THEN 'OUT_OF_STOCK'
        WHEN p.stock < 10 THEN 'LOW_STOCK'
        WHEN p.stock < 50 THEN 'MEDIUM_STOCK'
        ELSE 'SUFFICIENT'
    END AS stock_status
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
WHERE p.stock < 50
ORDER BY p.stock ASC, p.price DESC;

-- 用户活跃度分析
SELECT
    u.id,
    u.username,
    u.email,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.total_amount), 0) AS lifetime_value,
    MAX(o.created_at) AS last_order_date,
    AGE(NOW(), MAX(o.created_at)) AS days_since_last_order
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username, u.email
HAVING COUNT(o.id) > 0
ORDER BY lifetime_value DESC
LIMIT 100;

11. 🆚 PostgreSQL vs MySQL 差异

功能	PostgreSQL	MySQL
DISTINCT ON	支持	不支持
ILIKE	支持(不区分大小写)	不支持
正则表达式	`~`, `~*`, `!~`	`REGEXP`, `RLIKE`
NULLS FIRST/LAST	支持	不支持
BOOLEAN类型	真布尔类型	TINYINT(1)
INTERVAL	支持	不支持
字符串连接	`\|\|` 或 `CONCAT`	`CONCAT`
LIMIT语法	`LIMIT n OFFSET m`	`LIMIT m, n`
FETCH	支持	8.0+支持

12. 🎯 最佳实践

12.1 1. 明确列名

-- 好的做法
SELECT id, username, email FROM users;

-- 避免
SELECT * FROM users;  -- 生产环境避免使用

12.2 2. 使用别名提高可读性

SELECT
    u.username,
    COUNT(o.id) AS total_orders,
    SUM(o.total_amount) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

12.3 3. 合理使用索引

-- 确保WHERE、ORDER BY、JOIN的列有索引
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at);

12.4 4. 避免N+1查询

-- 不好:N+1查询问题
SELECT * FROM users;  -- 然后循环查询每个用户的订单

-- 好:使用JOIN一次查询
SELECT
    u.*,
    COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

12.5 5. 使用EXPLAIN分析查询

-- PostgreSQL
EXPLAIN ANALYZE
SELECT * FROM products WHERE price > 100;

-- MySQL
-- EXPLAIN SELECT * FROM products WHERE price > 100;

13. 📝 练习题

查询价格在50-100之间的所有产品,按价格降序排列
统计每个分类的产品数量和平均价格
查询最近30天内注册的用户数量
找出购买次数超过5次的用户
查询库存为0的产品,显示产品名称和分类

13.1 参考答案

-- 1
SELECT * FROM products
WHERE price BETWEEN 50 AND 100
ORDER BY price DESC;

-- 2
SELECT
    category,
    COUNT(*) AS product_count,
    ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category
ORDER BY product_count DESC;

-- 3
SELECT COUNT(*) AS new_users
FROM users
WHERE created_at >= NOW() - INTERVAL '30 days';

-- 4
SELECT
    u.id,
    u.username,
    COUNT(o.id) AS purchase_count
FROM users u
INNER JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username
HAVING COUNT(o.id) > 5
ORDER BY purchase_count DESC;

-- 5
SELECT
    p.name AS product_name,
    c.name AS category_name
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
WHERE p.stock = 0;

14. 📚 下一步

学习高级查询技巧
了解多表连接查询

关键要点:

PostgreSQL的DISTINCT ON非常强大
ILIKE提供不区分大小写的匹配
NULLS FIRST/LAST控制NULL值排序
使用EXPLAIN分析查询性能
避免SELECT *,明确指定列名

目录

pgsql-06 基础查询语句

06 - 基础查询语句

1. 📖 概述

2. 🔍 SELECT基础查询

2.1 查询所有列

2.2 查询指定列

2.3 DISTINCT去重

3. 🎯 WHERE条件查询

3.1 基本比较运算符

3.2 逻辑运算符

3.3 BETWEEN范围查询

3.4 IN和NOT IN

3.5 LIKE模糊查询

3.6 正则表达式(PostgreSQL)

3.7 NULL值处理

4. 📊 排序 ORDER BY

4.1 基本排序

4.2 NULL值排序

4.3 按表达式排序

5. 📈 限制结果集

5.1 LIMIT和OFFSET

5.2 FETCH(SQL标准)

6. 🔢 聚合查询

6.1 基本聚合函数

6.2 GROUP BY分组

6.3 HAVING过滤分组

7. 🔗 子查询

7.1 WHERE子查询

7.2 FROM子查询

7.3 SELECT子查询

8. 🎨 CASE表达式

8.1 简单CASE

8.2 搜索CASE

9. 🔧 实用查询技巧

9.1 字符串操作

9.2 日期操作

9.3 数值操作

10. 📋 完整查询示例

10.1 电商查询示例

11. 🆚 PostgreSQL vs MySQL 差异

12. 🎯 最佳实践

12.1 1. 明确列名

12.2 2. 使用别名提高可读性

12.3 3. 合理使用索引

12.4 4. 避免N+1查询

12.5 5. 使用EXPLAIN分析查询

13. 📝 练习题

13.1 参考答案

14. 📚 下一步