第三章：SQL 基础入门

3.1 SQL 概述

SQL（Structured Query Language）是用于管理和操作关系型数据库的标准语言。PostgreSQL 完全遵循 SQL 标准，并提供丰富的扩展功能。

┌──────────────────────────────────────────────────────┐
│                    SQL 语句分类                        │
├──────────────────────────────────────────────────────┤
│  DDL (数据定义语言)  │  CREATE, ALTER, DROP          │
│  DML (数据操作语言)  │  SELECT, INSERT, UPDATE, DELETE│
│  DCL (数据控制语言)  │  GRANT, REVOKE                  │
│  TCL (事务控制语言)  │  COMMIT, ROLLBACK, SAVEPOINT    │
└──────────────────────────────────────────────────────┘

3.2 数据定义语言 (DDL)

3.2.1 创建表

-- 基本创建表语法
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    age INTEGER DEFAULT 18,
    status BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

3.2.2 查看表结构

-- 查看表结构
\d users

-- 或
DESCRIBE users;

-- 查看所有表
\dt

-- 查看表及其索引
\d+ users

3.2.3 修改表结构

-- 添加列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users ADD COLUMN last_login TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

-- 修改列
ALTER TABLE users ALTER COLUMN age TYPE SMALLINT;
ALTER TABLE users ALTER COLUMN username SET NOT NULL;

-- 重命名列
ALTER TABLE users RENAME COLUMN phone TO mobile;

-- 删除列
ALTER TABLE users DROP COLUMN mobile;

-- 重命名表
ALTER TABLE users RENAME TO app_users;

3.2.4 删除表

-- 删除表
DROP TABLE users;

-- 删除表（如果存在）
DROP TABLE IF EXISTS users;

-- 级联删除（删除表及所有依赖对象）
DROP TABLE users CASCADE;

-- 删除多个表
DROP TABLE users, orders, products;

3.2.5 临时表

-- 创建临时表
CREATE TEMPORARY TABLE temp_data (
    id INTEGER,
    value TEXT
);

-- 会话级临时表（提交事务后保留）
CREATE TEMP TABLE session_data (
    id SERIAL PRIMARY KEY,
    data TEXT
);

-- 事务级临时表
CREATE TEMP TABLE temp_data (
    id SERIAL PRIMARY KEY,
    data TEXT
) ON COMMIT DELETE ROWS;

3.3 数据操作语言 (DML)

3.3.1 插入数据

-- 插入单行（指定列）
INSERT INTO users (username, email, age) 
VALUES ('张三', 'zhangsan@example.com', 25);

-- 插入单行（所有列）
INSERT INTO users 
VALUES (1, '李四', 'lisi@example.com', 30, true, CURRENT_TIMESTAMP);

-- 插入多行
INSERT INTO users (username, email, age) VALUES
    ('王五', 'wangwu@example.com', 28),
    ('赵六', 'zhaoliu@example.com', 35),
    ('孙七', 'sunqi@example.com', 22);

-- 从其他表插入
INSERT INTO backup_users SELECT * FROM users WHERE created_at > '2024-01-01';

-- 使用 DEFAULT 值
INSERT INTO users (username, email) VALUES ('test', 'test@example.com');

3.3.2 更新数据

-- 更新单列
UPDATE users SET age = 26 WHERE username = '张三';

-- 更新多列
UPDATE users 
SET age = 27, 
    status = false,
    last_login = CURRENT_TIMESTAMP
WHERE username = '李四';

-- 使用表达式更新
UPDATE users 
SET age = age + 1
WHERE age < 30;

-- 使用子查询更新
UPDATE users 
SET email = (
    SELECT new_email 
    FROM email_updates 
    WHERE email_updates.old_email = users.email
)
WHERE EXISTS (
    SELECT 1 FROM email_updates WHERE old_email = users.email
);

3.3.3 删除数据

-- 删除满足条件的行
DELETE FROM users WHERE username = '张三';

-- 删除所有行（快速，但不会重置序列）
DELETE FROM users;

-- 使用 LIMIT 删除
DELETE FROM users 
WHERE id IN (
    SELECT id FROM users ORDER BY created_at LIMIT 100
);

-- 使用 RETURNING 返回删除的行
DELETE FROM users 
WHERE age > 60
RETURNING *;

3.4 数据查询语言 (DQL)

3.4.1 基本 SELECT

-- 查询所有列
SELECT * FROM users;

-- 查询指定列
SELECT username, email FROM users;

-- 带别名
SELECT 
    username AS "用户名",
    email AS "邮箱",
    age AS "年龄"
FROM users;

-- 使用表达式
SELECT 
    username,
    age,
    age + 5 AS "五年后年龄"
FROM users;

3.4.2 过滤与条件

-- WHERE 子句
SELECT * FROM users WHERE age >= 18;

-- 多条件 AND
SELECT * FROM users 
WHERE age >= 18 AND status = true;

-- 多条件 OR
SELECT * FROM users 
WHERE age < 20 OR age > 60;

-- 组合条件
SELECT * FROM users 
WHERE (age >= 18 AND age <= 30) OR status = true;

-- NOT 条件
SELECT * FROM users WHERE NOT status;

-- IN 运算符
SELECT * FROM users 
WHERE username IN ('张三', '李四', '王五');

-- BETWEEN 运算符
SELECT * FROM users 
WHERE age BETWEEN 18 AND 30;

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

-- ILIKE 不区分大小写匹配
SELECT * FROM users 
WHERE username ILIKE '%张%';

3.4.3 常用运算符

-- 比较运算符
=   -- 等于
<>  -- 不等于
!=  -- 不等于
<   -- 小于
>   -- 大于
<=  -- 小于等于
>=  -- 大于等于

-- 逻辑运算符
AND -- 逻辑与
OR  -- 逻辑或
NOT -- 逻辑非

-- 模式匹配
LIKE    -- 模糊匹配
ILIKE   -- 不区分大小写的模糊匹配
~~      -- LIKE 的等价写法
~~*     -- ILIKE 的等价写法
!~~     -- NOT LIKE 的等价写法

3.4.4 排序与限制

-- 单列排序（升序）
SELECT * FROM users ORDER BY age ASC;

-- 单列排序（降序）
SELECT * FROM users ORDER BY age DESC;

-- 多列排序
SELECT * FROM users 
ORDER BY age DESC, created_at ASC;

-- NULL 值排序（NULL 值排在最前）
SELECT * FROM users ORDER BY age NULLS FIRST;

-- NULL 值排序（NULL 值排在最后）
SELECT * FROM users ORDER BY age DESC NULLS LAST;

-- 限制返回数量
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

-- 分页查询
SELECT * FROM users 
ORDER BY id 
LIMIT 10 OFFSET 20;

-- 跳过前 20 条，取 10 条
SELECT * FROM users 
LIMIT 10 
OFFSET ((20 / 10 - 1) * 10);

3.4.5 聚合函数

-- COUNT 计数
SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM users WHERE status = true;
SELECT COUNT(DISTINCT username) FROM users;

-- SUM 求和
SELECT SUM(age) FROM users;
SELECT SUM(age) FROM users WHERE status = true;

-- AVG 平均值
SELECT AVG(age) FROM users;

-- MAX 最大值
SELECT MAX(age) FROM users;

-- MIN 最小值
SELECT MIN(age) FROM users;

-- 组合使用
SELECT 
    COUNT(*) AS total_users,
    COUNT(*) FILTER (WHERE status = true) AS active_users,
    AVG(age) AS avg_age,
    MAX(age) AS max_age,
    MIN(age) AS min_age
FROM users;

3.4.6 GROUP BY 分组

-- 基本分组
SELECT status, COUNT(*) FROM users GROUP BY status;

-- 多列分组
SELECT status, age_group, COUNT(*)
FROM (
    SELECT 
        status,
        CASE 
            WHEN age < 20 THEN '青少年'
            WHEN age < 30 THEN '青年'
            WHEN age < 50 THEN '中年'
            ELSE '老年'
        END AS age_group
    FROM users
) t
GROUP BY status, age_group;

-- HAVING 过滤分组
SELECT 
    status, 
    COUNT(*) AS user_count,
    AVG(age) AS avg_age
FROM users 
GROUP BY status
HAVING COUNT(*) > 10;

-- 带有聚合的完整示例
SELECT 
    date_trunc('month', created_at) AS month,
    COUNT(*) AS new_users,
    COUNT(*) FILTER (WHERE status = true) AS active_users,
    AVG(age)::NUMERIC(10,2) AS avg_age
FROM users
WHERE created_at >= '2024-01-01'
GROUP BY date_trunc('month', created_at)
ORDER BY month DESC;

3.5 表连接 (JOIN)

3.5.1 内连接 (INNER JOIN)

-- 基本内连接
SELECT 
    u.username,
    o.order_id,
    o.total_amount,
    o.created_at
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 多个表连接
SELECT 
    u.username,
    o.order_id,
    p.product_name,
    oi.quantity,
    oi.price
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;

3.5.2 左连接 (LEFT JOIN)

-- 左连接：包含左表所有记录
SELECT 
    u.username,
    o.order_id,
    o.total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- 找出没有订单的用户
SELECT u.username
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.order_id IS NULL;

3.5.3 右连接 (RIGHT JOIN)

-- 右连接：包含右表所有记录
SELECT 
    u.username,
    o.order_id,
    o.total_amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

3.5.4 全连接 (FULL OUTER JOIN)

-- 全连接：包含两个表的所有记录
SELECT 
    u.username,
    o.order_id
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

3.5.5 连接类型图示

INNER JOIN          LEFT JOIN           RIGHT JOIN          FULL JOIN
┌───┬───┐          ┌───┬───┐           ┌───┬───┐           ┌───┬───┐
│ A │ B │           │ A │ B │           │ A │ B │           │ A │ B │
├───┼───┤           ├───┼───┤           ├───┼───┤           ├───┼───┤
│ 1 │ X │           │ 1 │ X │           │ 1 │ X │           │ 1 │ X │
│ 2 │ Y │           │ 2 │ Y │           │   │ Z │           │ 2 │ Y │
│ 3 │   │           │ 3 │   │ ← NULL     │   │   │           │   │ Z │
└───┴───┘           └───┴───┘           └───┴───┘           └───┴───┘

3.6 子查询

3.6.1 标量子查询

-- 返回单个值
SELECT 
    username,
    age,
    (SELECT AVG(age) FROM users) AS avg_age,
    age - (SELECT AVG(age) FROM users) AS diff_from_avg
FROM users;

3.6.2 表子查询

-- 在 FROM 中使用子查询
SELECT 
    status,
    COUNT(*) AS user_count
FROM (
    SELECT 
        *,
        CASE 
            WHEN age < 30 THEN 'young'
            ELSE 'senior'
        END AS age_category
    FROM users
) t
GROUP BY status, age_category;

3.6.3 IN/NOT IN 子查询

-- IN 子查询
SELECT * FROM products
WHERE category_id IN (
    SELECT id FROM categories WHERE name LIKE '%电子%'
);

-- NOT IN 子查询（注意 NULL 问题）
SELECT * FROM users
WHERE id NOT IN (
    SELECT user_id FROM orders WHERE status = 'completed'
);

3.6.4 EXISTS/NOT EXISTS 子查询

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

-- NOT EXISTS 子查询
SELECT * FROM categories c
WHERE NOT EXISTS (
    SELECT 1 FROM products p 
    WHERE p.category_id = c.id
);

3.7 联合查询 (UNION)

-- UNION：合并并去重
SELECT username, email FROM users
UNION
SELECT admin_name, admin_email FROM admins;

-- UNION ALL：合并保留重复
SELECT username FROM users
UNION ALL
SELECT admin_name FROM admins;

-- 带有 ORDER BY
(SELECT username, email, 'user' AS source FROM users)
UNION
(SELECT admin_name, admin_email, 'admin' AS source FROM admins)
ORDER BY username;

3.8 条件表达式

3.8.1 CASE 表达式

-- 简单 CASE
SELECT 
    username,
    age,
    CASE age
        WHEN 1 THEN '婴儿'
        WHEN 2 THEN '幼儿'
        WHEN 3 THEN '儿童'
        ELSE '其他'
    END AS age_description
FROM users;

-- 搜索 CASE
SELECT 
    username,
    salary,
    CASE 
        WHEN salary < 3000 THEN '低薪'
        WHEN salary >= 3000 AND salary < 8000 THEN '中等'
        WHEN salary >= 8000 THEN '高薪'
        ELSE '未知'
    END AS salary_level
FROM employees;

3.8.2 COALESCE 函数

-- 返回第一个非 NULL 值
SELECT 
    username,
    COALESCE(phone, mobile, email, '无联系方式') AS contact
FROM users;

3.8.3 NULLIF 函数

-- 如果两个值相等返回 NULL，否则返回第一个值
SELECT 
    product_name,
    price,
    NULLIF(price, 0) AS non_zero_price  -- 如果 price 是 0 则返回 NULL
FROM products;

3.8.4 GREATEST 和 LEAST

-- 返回最大值
SELECT GREATEST(10, 20, 5, 30);  -- 结果: 30

-- 返回最小值
SELECT LEAST(10, 20, 5, 30);     -- 结果: 5

-- 在查询中使用
SELECT 
    username,
    math_score,
    english_score,
    GREATEST(math_score, english_score) AS best_score
FROM students;

3.9 本章小结

本章介绍了 PostgreSQL 的 SQL 基础操作：

分类	核心语句
DDL	CREATE TABLE, ALTER TABLE, DROP TABLE
DML	INSERT, UPDATE, DELETE
DQL	SELECT, WHERE, ORDER BY, GROUP BY
连接	INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN
高级	子查询, UNION, CASE 表达式

📌 下一章预告

下一章将深入介绍 PostgreSQL 的数据类型，包括内置类型和高级类型如 JSON、数组等。