垦荒学园

简体中文

English

简体中文

English

切换主题

PostgreSQL SELECT ​

概述 ​

SELECT语句用于从一个或多个表中查询数据。它是最常用的SQL语句，是PostgreSQL中数据检索的基础。

基本SELECT语法 ​

sql
SELECT column1, column2, ...
FROM table_name;

选择所有列 ​

sql
-- 选择所有列
SELECT * FROM users;

-- 从特定表选择所有列
SELECT * FROM products;

选择特定列 ​

sql
-- 选择特定列
SELECT name, email FROM users;

-- 选择多个列
SELECT id, name, email, created_at FROM users;

列别名 ​

sql
-- 使用AS关键字
SELECT name AS customer_name, email AS contact_email FROM users;

-- 不使用AS关键字
SELECT name customer_name, email contact_email FROM users;

-- 带空格的别名（使用引号）
SELECT name AS "客户名称", email AS "电子邮件地址" FROM users;

SELECT中的表达式 ​

sql
-- 算术运算
SELECT product_name, price, price * 1.1 AS price_with_tax FROM products;

-- 字符串连接
SELECT first_name || ' ' || last_name AS full_name FROM users;

-- 使用函数
SELECT UPPER(name) AS name_upper, LENGTH(email) AS email_length FROM users;

DISTINCT - 去除重复 ​

什么是 DISTINCT？ ​

DISTINCT 关键字用于从查询结果中删除重复行，只返回指定列的唯一值。

基本语法 ​

sql
SELECT DISTINCT column1, column2, ...
FROM table_name;

单列 DISTINCT ​

sql
-- 去除重复
SELECT DISTINCT country FROM users;

-- 获取唯一的产品类别
SELECT DISTINCT category FROM products ORDER BY category;

-- 统计唯一值数量
SELECT COUNT(DISTINCT customer_id) AS unique_customers FROM orders;
SELECT COUNT(DISTINCT product_id) AS unique_products FROM order_items;

多列 DISTINCT ​

当对多列使用 DISTINCT 时，PostgreSQL 会根据所有指定列的组合来判断唯一性。

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

-- 获取城市和州的唯一组合
SELECT DISTINCT city, state FROM customers ORDER BY state, city;

-- 获取产品名称和类别的唯一组合
SELECT DISTINCT product_name, category FROM products;

DISTINCT 与聚合函数 ​

sql
-- 统计唯一客户数
SELECT COUNT(DISTINCT customer_id) AS unique_customers FROM orders;

-- 统计每个类别中的唯一产品数
SELECT 
    category,
    COUNT(DISTINCT product_id) AS unique_products
FROM products
GROUP BY category;

-- 多个不同计数
SELECT 
    COUNT(DISTINCT customer_id) AS unique_customers,
    COUNT(DISTINCT product_id) AS unique_products,
    COUNT(*) AS total_orders
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id;

DISTINCT ON（PostgreSQL 特有） ​

DISTINCT ON 为指定列的每个唯一值返回第一行。

sql
-- 获取每个部门的第一个员工
SELECT DISTINCT ON (department) 
    department, 
    employee_name, 
    salary
FROM employees
ORDER BY department, salary DESC;

-- 获取每个客户的最新订单
SELECT DISTINCT ON (customer_id)
    customer_id,
    order_id,
    order_date,
    total_amount
FROM orders
ORDER BY customer_id, order_date DESC;

-- 获取每个类别中价格最高的产品
SELECT DISTINCT ON (category)
    category,
    product_name,
    price
FROM products
ORDER BY category, price DESC;

DISTINCT 性能优化 ​

sql
-- 1. 在常用列上创建索引
CREATE INDEX idx_customers_city ON customers(city);
SELECT DISTINCT city FROM customers;

-- 2. DISTINCT vs GROUP BY
-- 使用 DISTINCT
SELECT DISTINCT category FROM products;

-- 使用 GROUP BY（有索引时可能更快）
SELECT category FROM products GROUP BY category;

-- 3. 避免在大结果集上使用 DISTINCT
-- 低效
SELECT DISTINCT * FROM large_table;

-- 更好：明确指定列
SELECT DISTINCT column1, column2 FROM large_table;

DISTINCT 与 JOIN ​

sql
-- 获取购买了特定产品的唯一客户
SELECT DISTINCT c.customer_id, c.customer_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE oi.product_id IN (1, 2, 3);

-- 获取上个月售出的唯一产品类别
SELECT DISTINCT p.category
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '1 month';

NULL 值处理 ​

DISTINCT 将 NULL 值视为相等：

sql
-- 如果多行在列中有 NULL，只返回一个 NULL
SELECT DISTINCT phone_number FROM customers;

-- 排除 NULL
SELECT DISTINCT phone_number 
FROM customers 
WHERE phone_number IS NOT NULL;

常见错误 ​

sql
-- 错误1：不必要地使用 DISTINCT
SELECT DISTINCT id FROM users;  -- 主键已经唯一

-- 错误2：DISTINCT 与 ORDER BY
-- 错误：ORDER BY 列必须在 SELECT 中
SELECT DISTINCT city FROM customers ORDER BY state;

-- 正确：包含所有 ORDER BY 列
SELECT DISTINCT city, state FROM customers ORDER BY state, city;

计算字段 ​

sql
-- 数学计算
SELECT 
    product_name,
    price,
    quantity,
    price * quantity AS total_value
FROM inventory;

-- 条件表达式
SELECT 
    name,
    age,
    CASE 
        WHEN age < 18 THEN '未成年'
        WHEN age >= 18 AND age < 65 THEN '成年'
        ELSE '老年'
    END AS age_group
FROM users;

从多个表选择 ​

sql
-- SELECT中的简单连接
SELECT users.name, orders.order_number
FROM users, orders
WHERE users.id = orders.user_id;

-- 更好的方式：使用显式JOIN
SELECT u.name, o.order_number
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

SELECT中的子查询 ​

sql
-- 标量子查询
SELECT 
    name,
    (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count
FROM users;

-- 带聚合的子查询
SELECT 
    product_name,
    price,
    (SELECT AVG(price) FROM products) AS avg_price
FROM products;

SELECT与函数 ​

sql
-- 聚合函数
SELECT COUNT(*) FROM users;
SELECT AVG(price) FROM products;
SELECT SUM(total) FROM orders;
SELECT MIN(price), MAX(price) FROM products;

-- 字符串函数
SELECT UPPER(name), LOWER(email) FROM users;
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

-- 日期函数
SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP;
SELECT name, AGE(birth_date) AS age FROM users;

SELECT与LIMIT ​

sql
-- 限制行数
SELECT * FROM users LIMIT 10;

-- 带偏移量的限制（分页）
SELECT * FROM users LIMIT 10 OFFSET 20;

-- 替代语法
SELECT * FROM users OFFSET 20 LIMIT 10;

SELECT与ORDER BY ​

sql
-- 按单列排序
SELECT * FROM users ORDER BY name;

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

-- 按多列排序
SELECT * FROM users ORDER BY country, name;

-- 按表达式排序
SELECT * FROM products ORDER BY price * quantity DESC;

SELECT与WHERE ​

sql
-- 基本过滤
SELECT * FROM users WHERE status = 'active';

-- 多个条件
SELECT * FROM products WHERE price > 50 AND category = 'Electronics';

-- 模式匹配
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- 范围
SELECT * FROM products WHERE price BETWEEN 10 AND 100;

-- 列表
SELECT * FROM users WHERE country IN ('USA', 'UK', 'Canada');

SELECT与GROUP BY ​

sql
-- 分组和计数
SELECT country, COUNT(*) AS user_count
FROM users
GROUP BY country;

-- 带多个聚合的分组
SELECT 
    category,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price,
    MIN(price) AS min_price,
    MAX(price) AS max_price
FROM products
GROUP BY category;

SELECT与HAVING ​

sql
-- 过滤分组
SELECT country, COUNT(*) AS user_count
FROM users
GROUP BY country
HAVING COUNT(*) > 100;

-- 带多个条件的HAVING
SELECT 
    category,
    AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 50 AND COUNT(*) > 10;

完整SELECT示例 ​

sql
-- 组合多个子句的复杂查询
SELECT 
    u.name,
    u.email,
    COUNT(o.id) AS order_count,
    SUM(o.total) AS total_spent,
    AVG(o.total) AS avg_order_value
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
    AND u.created_at >= '2024-01-01'
GROUP BY u.id, u.name, u.email
HAVING COUNT(o.id) > 0
ORDER BY total_spent DESC
LIMIT 100;

最佳实践 ​

sql
-- 1. 指定列而不是SELECT *
SELECT id, name, email FROM users;  -- 好
SELECT * FROM users;                 -- 生产环境中避免

-- 2. 使用表别名提高清晰度
SELECT u.name, o.order_number
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 3. 使用有意义的列别名
SELECT 
    COUNT(*) AS total_users,
    AVG(age) AS average_age
FROM users;

-- 4. 格式化以提高可读性
SELECT 
    u.id,
    u.name,
    u.email,
    COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.email;

常见模式 ​

分页 ​

sql
-- 第1页（第1-10行）
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 0;

-- 第2页（第11-20行）
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 10;

-- 第3页（第21-30行）
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20;

前N条记录 ​

sql
-- 最贵的10个产品
SELECT * FROM products ORDER BY price DESC LIMIT 10;

-- 消费最多的5个客户
SELECT 
    user_id,
    SUM(total) AS total_spent
FROM orders
GROUP BY user_id
ORDER BY total_spent DESC
LIMIT 5;

条件选择 ​

sql
-- 根据条件选择不同的列
SELECT 
    name,
    CASE 
        WHEN status = 'active' THEN email
        ELSE 'N/A'
    END AS contact
FROM users;

小结 ​

SELECT语句是PostgreSQL的基础：

  • 基本：SELECT columns FROM table
  • 过滤：使用WHERE子句
  • 排序：使用ORDER BY子句
  • 分组：使用GROUP BY与聚合
  • 限制：使用LIMIT和OFFSET
  • 别名：提高可读性
  • 函数：转换和聚合数据