跳到主要内容

PostgreSQL 基础语法

本章将介绍 PostgreSQL 的 SQL 语法基础，包括标识符规则、注释、数据定义和操作语句等核心内容。掌握这些基础知识是使用 PostgreSQL 的第一步。

SQL 语法结构​

语句结构​

SQL 语句由多个元素组成，基本结构如下：

-- SQL 语句的基本结构
SELECT column1, column2    -- 选择列
FROM table_name            -- 指定表
WHERE condition            -- 过滤条件
GROUP BY column            -- 分组
HAVING condition           -- 分组过滤
ORDER BY column            -- 排序
LIMIT count;               -- 限制行数

语句分隔符​

PostgreSQL 使用分号 ; 作为语句分隔符。在脚本文件中，每条语句应以分号结束：

-- 多条语句需要分号分隔
SELECT * FROM users;
SELECT * FROM orders;

-- 单条语句在交互式环境中可以省略分号
SELECT version()

大小写规则​

PostgreSQL 对不同元素的大小写处理方式不同：

元素大小写敏感性说明
关键字不敏感SELECT 等同于 select
标识符不敏感（默认）未加引号时转换为小写
字符串敏感'Hello' 不等于 'hello'
加引号标识符敏感"Users" 不等于 "users"
-- 关键字不区分大小写（推荐使用大写）
select * from users;      -- OK
SELECT * FROM users;      -- OK，推荐

-- 未加引号的标识符转换为小写
CREATE TABLE Users (id INT);   -- 创建表 "users"
SELECT * FROM Users;           -- 查询表 "users"
SELECT * FROM users;           -- 查询表 "users"

-- 加引号的标识符保持原样
CREATE TABLE "Users" (id INT); -- 创建表 "Users"
SELECT * FROM "Users";         -- 必须加引号
SELECT * FROM users;           -- 错误！找不到表 "users"
SELECT * FROM Users;           -- 错误！转换为 "users"

最佳实践：始终使用小写字母命名表和列，避免使用双引号，保持代码简洁一致。

标识符命名规则​

命名规范​

标识符（表名、列名、索引名等）需要遵循以下规则：

-- 合法的标识符
CREATE TABLE users (id INT);                    -- 字母开头
CREATE TABLE user_orders (id INT);              -- 字母和下划线
CREATE TABLE order_items_2024 (id INT);         -- 包含数字
CREATE TABLE _temp_data (id INT);               -- 下划线开头

-- 需要加引号的标识符（包含特殊字符或保留字）
CREATE TABLE "user" (id INT);                   -- 保留字
CREATE TABLE "order-items" (id INT);            -- 包含连字符
CREATE TABLE "2024_orders" (id INT);            -- 数字开头
CREATE TABLE "Order Table" (id INT);            -- 包含空格

-- 不推荐的命名方式
CREATE TABLE "MyTable" (id INT);                -- 大小写混合
CREATE TABLE "select" (id INT);                 -- 使用保留字

命名约定​

-- 推荐的命名约定

-- 表名：小写，使用下划线分隔，使用复数
CREATE TABLE users (...);
CREATE TABLE order_items (...);
CREATE TABLE product_categories (...);

-- 列名：小写，使用下划线分隔，使用单数
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email_address VARCHAR(100),
    created_at TIMESTAMP,
    is_active BOOLEAN
);

-- 主键命名：表名单数 + _id 或直接 id
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,          -- 推荐
    -- 或
    order_id SERIAL PRIMARY KEY
);

-- 外键命名：关联表名单数 + _id
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id),
    product_id INTEGER REFERENCES products(id)
);

-- 索引命名：idx_表名_列名
CREATE INDEX idx_users_email ON users(email_address);
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- 唯一约束命名：uq_表名_列名 或 uk_表名_列名
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email_address);

保留字​

PostgreSQL 有大量保留字，不能直接用作标识符：

-- 常见保留字（部分）
-- SELECT, FROM, WHERE, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER
-- TABLE, INDEX, VIEW, DATABASE, SCHEMA, USER, ROLE, GRANT, REVOKE
-- AND, OR, NOT, IN, LIKE, BETWEEN, IS, NULL, TRUE, FALSE
-- ORDER, GROUP, HAVING, LIMIT, OFFSET, JOIN, INNER, OUTER, LEFT, RIGHT

-- 如果必须使用保留字作为标识符，需要加双引号
CREATE TABLE "select" (id INT);      -- 不推荐
CREATE TABLE "user" (id INT);        -- 不推荐

-- 更好的做法：使用替代名称
CREATE TABLE selections (id INT);
CREATE TABLE app_users (id INT);

注释​

PostgreSQL 支持两种注释格式：

单行注释​

使用 -- 开始，到行末结束：

-- 这是单行注释
SELECT * FROM users;  -- 这也是单行注释

-- 注释可以独占一行
-- 用于解释复杂逻辑
SELECT 
    id,
    name,
    salary * 12 AS annual_salary  -- 计算年薪
FROM employees;

多行注释​

使用 /* */ 包裹，可以跨越多行：

/*
 * 这是多行注释
 * 可以跨越多行
 * 用于详细说明
 */
SELECT * FROM orders;

/*
 计算每个部门的统计信息：
 - 员工数量
 - 平均工资
 - 最高工资
 - 最低工资
*/
SELECT 
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary
FROM employees
GROUP BY department;

/* 注释可以嵌套在代码中 */
SELECT id, /* 这是一个内联注释 */ name FROM users;

注释最佳实践​

-- 好的注释：解释"为什么"，而不是"是什么"

-- 不好的注释（解释显而易见的内容）
SELECT * FROM users;  -- 查询所有用户

-- 好的注释（解释业务逻辑）
-- 只查询活跃用户，排除已注销和禁用的账户
-- 因为注销用户的数据已被清除，不应出现在报表中
SELECT * FROM users 
WHERE status = 'active'
  AND deleted_at IS NULL;

-- 使用注释标记待办事项
-- TODO: 添加缓存优化
-- FIXME: 处理时区问题
-- NOTE: 此查询在数据量大时可能较慢

-- 使用注释分隔代码块
-- ============================================
-- 用户统计查询
-- ============================================

SELECT 
    COUNT(*) AS total_users,
    COUNT(*) FILTER (WHERE status = 'active') AS active_users,
    COUNT(*) FILTER (WHERE status = 'inactive') AS inactive_users
FROM users;

数据定义语言 (DDL)​

数据定义语言用于定义和修改数据库结构。

创建表 (CREATE TABLE)​

-- 基本创建表语法
CREATE TABLE table_name (
    column1 data_type [constraints],
    column2 data_type [constraints],
    ...
);

-- 创建用户表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    birth_date DATE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建带外键的表
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    order_number VARCHAR(20) NOT NULL UNIQUE,
    total_amount NUMERIC(10, 2) NOT NULL DEFAULT 0,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 添加检查约束
    CONSTRAINT chk_amount_positive CHECK (total_amount >= 0),
    CONSTRAINT chk_status_valid CHECK (status IN ('pending', 'processing', 'completed', 'cancelled'))
);

-- 创建带注释的表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(10, 2) NOT NULL
);

COMMENT ON TABLE products IS '商品信息表';
COMMENT ON COLUMN products.id IS '商品唯一标识';
COMMENT ON COLUMN products.name IS '商品名称';
COMMENT ON COLUMN products.price IS '商品价格（单位：元）';

常用约束​

-- PRIMARY KEY：主键约束
CREATE TABLE example (
    id INTEGER PRIMARY KEY,              -- 单列主键
    -- 或
    -- PRIMARY KEY (id)
);

-- 复合主键
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    PRIMARY KEY (order_id, product_id)
);

-- FOREIGN KEY：外键约束
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),  -- 简写形式
    -- 或完整形式
    -- customer_id INTEGER,
    -- FOREIGN KEY (customer_id) REFERENCES customers(id)
    
    -- 带删除/更新行为的外键
    manager_id INTEGER REFERENCES users(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

-- UNIQUE：唯一约束
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE,         -- 列级约束
    email VARCHAR(100),
    
    CONSTRAINT uq_users_email UNIQUE (email)  -- 表级约束
);

-- NOT NULL：非空约束
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(10, 2) NOT NULL
);

-- CHECK：检查约束
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    salary NUMERIC(10, 2),
    age INTEGER,
    
    CONSTRAINT chk_salary_positive CHECK (salary > 0),
    CONSTRAINT chk_age_valid CHECK (age >= 18 AND age <= 65)
);

-- DEFAULT：默认值
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    role VARCHAR(20) DEFAULT 'user'
);

修改表 (ALTER TABLE)​

-- 添加列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users ADD COLUMN address TEXT;

-- 添加多个列
ALTER TABLE users 
    ADD COLUMN city VARCHAR(50),
    ADD COLUMN country VARCHAR(50);

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

-- 修改列类型
ALTER TABLE users ALTER COLUMN age TYPE BIGINT;

-- 修改列名
ALTER TABLE users RENAME COLUMN name TO username;

-- 添加约束
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);
ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age >= 0);

-- 删除约束
ALTER TABLE users DROP CONSTRAINT uq_users_email;

-- 设置默认值
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';

-- 删除默认值
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;

-- 设置非空约束
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- 删除非空约束
ALTER TABLE users ALTER COLUMN email DROP NOT NULL;

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

-- 添加外键
ALTER TABLE orders 
    ADD CONSTRAINT fk_orders_customer 
    FOREIGN KEY (customer_id) REFERENCES customers(id);

-- 重命名约束
ALTER TABLE users RENAME CONSTRAINT uq_users_email TO uq_users_email_address;

删除表 (DROP TABLE)​

-- 删除表
DROP TABLE users;

-- 如果存在则删除（推荐）
DROP TABLE IF EXISTS users;

-- 级联删除（同时删除依赖对象）
DROP TABLE users CASCADE;

-- 查看删除影响
DROP TABLE users CASCADE;
-- NOTICE: drop cascades to constraint fk_orders_user on table orders

清空表 (TRUNCATE)​

-- 清空表数据（比 DELETE 快）
TRUNCATE TABLE users;

-- 重置自增序列
TRUNCATE TABLE users RESTART IDENTITY;

-- 级联清空（有外键引用时）
TRUNCATE TABLE users CASCADE;

-- 清空多个表
TRUNCATE TABLE users, orders, products;

数据操作语言 (DML)​

数据操作语言用于操作表中的数据。

插入数据 (INSERT)​

-- 插入单行
INSERT INTO users (username, email) 
VALUES ('john', '[email protected]');

-- 插入单行（所有列）
INSERT INTO users 
VALUES (DEFAULT, 'jane', '[email protected]', 'hash123', 'Jane', 'Doe', NULL, TRUE, DEFAULT, DEFAULT);

-- 插入多行
INSERT INTO users (username, email, first_name) VALUES
    ('alice', '[email protected]', 'Alice'),
    ('bob', '[email protected]', 'Bob'),
    ('charlie', '[email protected]', 'Charlie');

-- 从查询结果插入
INSERT INTO users_archive (username, email, created_at)
SELECT username, email, created_at
FROM users
WHERE created_at < '2023-01-01';

-- 插入并返回
INSERT INTO users (username, email) 
VALUES ('david', '[email protected]')
RETURNING id, username, created_at;

-- 插入冲突处理 (UPSERT)
INSERT INTO users (username, email) 
VALUES ('john', '[email protected]')
ON CONFLICT (username) 
DO UPDATE SET email = EXCLUDED.email;

-- 插入冲突时忽略
INSERT INTO users (username, email) 
VALUES ('john', '[email protected]')
ON CONFLICT (username) DO NOTHING;

更新数据 (UPDATE)​

-- 更新单列
UPDATE users SET status = 'inactive' WHERE id = 1;

-- 更新多列
UPDATE users 
SET 
    first_name = 'John',
    last_name = 'Doe',
    updated_at = CURRENT_TIMESTAMP
WHERE id = 1;

-- 使用表达式更新
UPDATE products SET price = price * 1.1 WHERE category = 'electronics';

-- 使用 CASE 表达式
UPDATE products 
SET price = CASE 
    WHEN category = 'electronics' THEN price * 1.1
    WHEN category = 'clothing' THEN price * 1.05
    ELSE price * 1.02
END;

-- 使用子查询更新
UPDATE orders 
SET status = 'completed'
WHERE id IN (
    SELECT order_id 
    FROM order_items 
    GROUP BY order_id 
    HAVING SUM(quantity) > 10
);

-- 更新并返回
UPDATE users 
SET status = 'inactive' 
WHERE last_login < '2023-01-01'
RETURNING id, username, status;

-- PostgreSQL 18+ 支持返回新旧值
UPDATE products 
SET price = price * 1.1
WHERE category = 'electronics'
RETURNING 
    name,
    OLD.price AS old_price,
    NEW.price AS new_price;

删除数据 (DELETE)​

-- 删除指定行
DELETE FROM users WHERE id = 1;

-- 条件删除
DELETE FROM users WHERE status = 'inactive';

-- 使用子查询删除
DELETE FROM orders 
WHERE customer_id IN (
    SELECT id FROM customers WHERE status = 'deleted'
);

-- 删除并返回
DELETE FROM users 
WHERE status = 'inactive'
RETURNING id, username, deleted_at;

-- PostgreSQL 18+ 支持返回被删除的值
DELETE FROM products 
WHERE stock = 0
RETURNING 
    OLD.name AS deleted_product,
    OLD.price;

-- 删除所有数据（不推荐，使用 TRUNCATE 更快）
DELETE FROM users;

常用运算符​

比较运算符​

-- 基本比较
SELECT * FROM products WHERE price = 100;
SELECT * FROM products WHERE price != 100;
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE price >= 100;
SELECT * FROM products WHERE price < 100;
SELECT * FROM products WHERE price <= 100;

-- 范围比较
SELECT * FROM products WHERE price BETWEEN 50 AND 100;
SELECT * FROM products WHERE price NOT BETWEEN 50 AND 100;

-- 列表比较
SELECT * FROM products WHERE category IN ('electronics', 'clothing');
SELECT * FROM products WHERE category NOT IN ('electronics', 'clothing');

-- NULL 比较（重要：不能使用 = 或 !=）
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

-- 模式匹配
SELECT * FROM users WHERE name LIKE '张%';      -- 以"张"开头
SELECT * FROM users WHERE name LIKE '%三';      -- 以"三"结尾
SELECT * FROM users WHERE name LIKE '%小%';     -- 包含"小"
SELECT * FROM users WHERE name LIKE '_三';      -- 第二个字是"三"
SELECT * FROM users WHERE name ILIKE 'ZHANG%';  -- 不区分大小写

逻辑运算符​

-- AND：同时满足所有条件
SELECT * FROM products 
WHERE category = 'electronics' AND price < 1000;

-- OR：满足任一条件
SELECT * FROM products 
WHERE category = 'electronics' OR category = 'clothing';

-- NOT：取反
SELECT * FROM products 
WHERE NOT (category = 'electronics');

-- 组合使用（使用括号明确优先级）
SELECT * FROM products 
WHERE (category = 'electronics' OR category = 'clothing')
  AND price BETWEEN 100 AND 500;

算术运算符​

-- 基本运算
SELECT 10 + 5;    -- 加法：15
SELECT 10 - 5;    -- 减法：5
SELECT 10 * 5;    -- 乘法：50
SELECT 10 / 3;    -- 整数除法：3
SELECT 10.0 / 3;  -- 浮点除法：3.333...
SELECT 10 % 3;    -- 取余：1
SELECT 2 ^ 3;     -- 幂运算：8

-- 在查询中使用
SELECT 
    name,
    price,
    quantity,
    price * quantity AS total,
    ROUND(price * 1.1, 2) AS price_with_tax
FROM order_items;

字符串运算符​

-- 字符串连接
SELECT 'Hello' || ' ' || 'World';           -- 'Hello World'
SELECT CONCAT('Hello', ' ', 'World');       -- 'Hello World'
SELECT CONCAT_WS('-', 'a', 'b', 'c');       -- 'a-b-c'

-- 在查询中使用
SELECT 
    first_name || ' ' || last_name AS full_name,
    CONCAT(email, '@example.com') AS full_email
FROM users;

表达式和函数​

CASE 表达式​

-- 简单 CASE
SELECT 
    name,
    CASE category
        WHEN 'electronics' THEN '电子产品'
        WHEN 'clothing' THEN '服装'
        WHEN 'food' THEN '食品'
        ELSE '其他'
    END AS category_cn
FROM products;

-- 搜索 CASE
SELECT 
    name,
    price,
    CASE 
        WHEN price < 100 THEN '低价'
        WHEN price < 500 THEN '中价'
        WHEN price < 1000 THEN '高价'
        ELSE '奢侈品'
    END AS price_level
FROM products;

-- 在聚合中使用
SELECT 
    category,
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE price > 500) AS high_price_count,
    SUM(CASE WHEN price > 500 THEN price ELSE 0 END) AS high_price_total
FROM products
GROUP BY category;

NULL 处理函数​

-- COALESCE：返回第一个非 NULL 值
SELECT COALESCE(phone, email, 'N/A') AS contact FROM users;

-- NULLIF：如果两值相等返回 NULL
SELECT NULLIF(price, 0) FROM products;  -- 避免除零错误

-- 在计算中使用
SELECT 
    price,
    discount,
    price - COALESCE(discount, 0) AS final_price
FROM products;

-- 安全除法
SELECT price / NULLIF(quantity, 0) AS unit_price FROM orders;

小结​

本章我们学习了 PostgreSQL 的基础语法：

  1. SQL 语法结构：语句组成、分隔符、大小写规则
  2. 标识符命名：命名规则、保留字、命名约定
  3. 注释：单行注释、多行注释、最佳实践
  4. 数据定义语言：CREATE、ALTER、DROP、TRUNCATE
  5. 数据操作语言：INSERT、UPDATE、DELETE
  6. 运算符：比较、逻辑、算术、字符串运算符
  7. 表达式：CASE 表达式、NULL 处理函数

练习​

  1. 创建一个包含各种约束的用户表
  2. 编写 INSERT 语句插入测试数据
  3. 使用 UPDATE 语句批量更新数据
  4. 使用 CASE 表达式对数据进行分类

参考资源​