跳到主要内容

PostgreSQL 基础语法

本章将介绍 PostgreSQL 的基础语法，包括数据库操作、表创建、数据类型和基本查询。

第一个 PostgreSQL 查询​

连接 PostgreSQL 后，让我们从最简单的查询开始：

-- 查看 PostgreSQL 版本
SELECT version();

-- 查看当前数据库
SELECT current_database();

-- 查看当前用户
SELECT current_user;

-- 简单的计算
SELECT 1 + 1 AS result;

代码解释​

  • SELECT：用于从数据库查询数据
  • version()：PostgreSQL 内置函数，返回数据库版本
  • current_database()：返回当前连接的数据库名
  • current_user：返回当前登录的用户名
  • AS：用于给列或表达式起别名

数据库操作​

创建数据库​

-- 创建数据库（基本语法）
CREATE DATABASE myapp;

-- 创建数据库并指定编码
CREATE DATABASE myapp 
    WITH 
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'zh_CN.UTF-8'
    LC_CTYPE = 'zh_CN.UTF-8'
    TEMPLATE = template0;

参数说明：

  • OWNER：指定数据库所有者
  • ENCODING：指定字符编码，推荐使用 UTF8
  • LC_COLLATE：指定排序规则
  • LC_CTYPE：指定字符分类
  • TEMPLATE：指定模板数据库

查看数据库​

-- 列出所有数据库
\l
-- 或者
SELECT datname FROM pg_database;

-- 查看数据库详情
\l+ myapp
-- 或者
SELECT * FROM pg_database WHERE datname = 'myapp';

连接数据库​

-- 切换数据库（在 psql 中）
\c myapp

-- 或者使用 \connect
\connect myapp

删除数据库​

-- 删除数据库（必须先断开连接）
DROP DATABASE IF EXISTS myapp;
谨慎操作

删除数据库是危险操作，会永久删除所有数据，请在执行前确保数据已备份。

数据类型​

PostgreSQL 支持丰富的数据类型，下面介绍最常用的类型。

数值类型​

类型大小范围说明
SMALLINT2 字节-32768 ~ 3276716 位整数
INTEGER4 字节-2147483648 ~ 214748364732 位整数
BIGINT8 字节-9223372036854775808 ~ 922337203685477580764 位整数
REAL4 字节6 位精度单精度浮点数
DOUBLE PRECISION8 字节15 位精度双精度浮点数
NUMERIC(p, s)可变用户定义精确数值
SERIAL4 字节1 ~ 2147483647自增整数
-- 整数
CREATE TABLE numbers (
    small_num SMALLINT,
    normal_num INTEGER,
    big_num BIGINT
);

-- 浮点数
CREATE TABLE measurements (
    temperature REAL,           -- 单精度
    price DOUBLE PRECISION,    -- 双精度
    amount NUMERIC(10, 2)      -- 精确数值：总共10位，小数2位
);

-- 自增序列
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50)
);

字符串类型​

类型最大长度说明
CHAR(n)n 个字符固定长度，不足部分用空格填充
VARCHAR(n)n 个字符可变长度，有最大长度限制
TEXT无限制可变长度，无最大限制
-- 字符串示例
CREATE TABLE users (
    username CHAR(20),     -- 固定 20 字符
    bio VARCHAR(500),      -- 最多 500 字符
    content TEXT           -- 无限制长度
);

-- 插入字符串数据
INSERT INTO users (username, bio, content) 
VALUES ('john_doe', 'Software Engineer', 'This is a long story about...');

日期和时间类型​

类型大小范围说明
DATE4 字节4713 BC ~ 5874897 AD仅日期
TIME8 字节00:00:00 ~ 24:00:00仅时间
TIMESTAMP8 字节4713 BC ~ 294276 AD日期和时间
TIMESTAMPTZ8 字节带时区日期时间+时区
INTERVAL16 字节时间间隔时间段
-- 日期时间示例
CREATE TABLE events (
    event_date DATE,           -- 日期：2024-01-15
    event_time TIME,           -- 时间：14:30:00
    created_at TIMESTAMP,      -- 时间戳：2024-01-15 14:30:00
    updated_at TIMESTAMPTZ     -- 带时区：2024-01-15 14:30:00+08
);

-- 插入日期时间
INSERT INTO events (event_date, event_time, created_at, updated_at)
VALUES 
    ('2024-01-15', '14:30:00', '2024-01-15 14:30:00', '2024-01-15 14:30:00+08');

布尔类型​

-- 布尔类型
CREATE TABLE settings (
    is_active BOOLEAN,
    is_verified BOOLEAN DEFAULT FALSE
);

-- 插入布尔值（多种方式）
INSERT INTO settings (is_active, is_verified) 
VALUES 
    (TRUE, FALSE),
    ('yes', 'no'),
    (1, 0);

JSON 类型​

PostgreSQL 支持两种 JSON 类型：JSON 和 JSONB。

-- JSON 类型示例
CREATE TABLE api_logs (
    id SERIAL PRIMARY KEY,
    request_data JSON,      -- 存储原始 JSON
    response_data JSONB     -- 存储优化后的 JSON
);

-- 插入 JSON 数据
INSERT INTO api_logs (request_data, response_data)
VALUES 
    ('{"method": "GET", "path": "/users"}', '{"status": 200, "data": []}');

数组类型​

PostgreSQL 原生支持数组类型：

-- 数组类型示例
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    scores INTEGER[],        -- 整数数组
    tags TEXT[]              -- 文本数组
);

-- 插入数组数据
INSERT INTO students (name, scores, tags)
VALUES 
    ('张三', ARRAY[90, 85, 92], ARRAY['good', 'math']),
    ('李四', '{95, 88, 91}', '{excellent, science}');

UUID 类型​

-- UUID 类型
CREATE TABLE sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id INTEGER,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 插入数据
INSERT INTO sessions (user_id) VALUES (1);

表操作​

创建表​

-- 简单表创建
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    password VARCHAR(255) NOT NULL,
    age INTEGER,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

约束说明：

  • PRIMARY KEY：主键，唯一标识
  • NOT NULL：非空约束
  • UNIQUE：唯一约束
  • DEFAULT：默认值
  • CHECK：自定义约束

创建表（带更多约束）​

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price NUMERIC(10, 2) NOT NULL CHECK (price > 0),
    stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
    category_id INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 外键约束
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- 创建类别表（用于外键示例）
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE
);

查看表结构​

-- 查看表结构
\d users
-- 或者
\d+ users

-- 使用 SQL 查询
SELECT 
    column_name, 
    data_type, 
    is_nullable, 
    column_default
FROM information_schema.columns 
WHERE table_name = 'users'
ORDER BY ordinal_position;

修改表结构​

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

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

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

-- 设置默认值
ALTER TABLE users ALTER COLUMN is_active SET DEFAULT TRUE;

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

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

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

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

-- 重命名列
ALTER TABLE app_users RENAME COLUMN username TO user_name;

删除表​

-- 删除表
DROP TABLE IF EXISTS products;

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

数据操作​

插入数据​

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

-- 插入多行
INSERT INTO users (username, email, password, age)
VALUES 
    ('alice', '[email protected]', 'hash1', 30),
    ('bob', '[email protected]', 'hash2', 28),
    ('carol', '[email protected]', 'hash3', 35);

-- 插入默认值
INSERT INTO users (username, email, password)
VALUES ('dave', '[email protected]', 'hash4');

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

查询数据​

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

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

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

-- 条件查询
SELECT * FROM users WHERE age >= 25;

-- 多个条件
SELECT * FROM users 
WHERE age >= 25 AND is_active = TRUE;

-- 排序
SELECT * FROM users ORDER BY age DESC;

-- 限制结果
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

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

更新数据​

-- 更新单行
UPDATE users 
SET age = 26, updated_at = CURRENT_TIMESTAMP
WHERE username = 'john';

-- 更新多行
UPDATE users 
SET is_active = FALSE
WHERE created_at < '2023-01-01';

-- 使用表达式更新
UPDATE products 
SET price = price * 0.9
WHERE category_id = 1;

-- 更新并返回
UPDATE users 
SET age = 30 
WHERE username = 'alice'
RETURNING id, username, age;

删除数据​

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

-- 删除所有数据（保留表结构）
DELETE FROM users;

-- 使用 TRUNCATE（更快）
TRUNCATE TABLE users;

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

常用运算符​

比较运算符​

-- 等于、不等于
SELECT * FROM users WHERE age = 25;
SELECT * FROM users WHERE age != 25;

-- 大于、小于
SELECT * FROM users WHERE age > 25;
SELECT * FROM users WHERE age < 25;

-- 范围
SELECT * FROM users WHERE age BETWEEN 20 AND 30;

-- IN 查询
SELECT * FROM users WHERE username IN ('john', 'alice', 'bob');

逻辑运算符​

-- AND
SELECT * FROM users WHERE age > 20 AND is_active = TRUE;

-- OR
SELECT * FROM users WHERE username = 'john' OR username = 'alice';

-- NOT
SELECT * FROM users WHERE NOT is_active;

模式匹配​

-- LIKE（区分大小写）
SELECT * FROM users WHERE username LIKE 'j%';      -- 以 j 开头
SELECT * FROM users WHERE username LIKE '%oh%';   -- 包含 oh

-- ILIKE（不区分大小写）
SELECT * FROM users WHERE username ILIKE 'JOHN';

-- 正则表达式
SELECT * FROM users WHERE username ~ '^[a-z]+$';
SELECT * FROM users WHERE username ~* '^[A-Z]+$';  -- 不区分大小写

注释​

-- 单行注释

/*
 * 多行注释
 * 可以跨越多行
 */

-- 为表添加注释
COMMENT ON TABLE users IS '用户信息表';

-- 为列添加注释
COMMENT ON COLUMN users.username IS '用户名';

小结​

本章我们学习了：

  1. 基本的 SQL 查询：SELECT、查看数据库信息
  2. 数据库操作：创建、查看、删除数据库
  3. 数据类型：数值、字符串、日期时间、布尔、JSON、数组、UUID
  4. 表操作：创建表、修改表结构、删除表
  5. 数据操作：INSERT、SELECT、UPDATE、DELETE
  6. 运算符和条件：比较运算符、逻辑运算符、模式匹配
  7. 注释：单行和多行注释

练习​

  1. 创建一个电子商务数据库
  2. 创建商品表（products），包含名称、价格、库存、分类
  3. 插入至少 5 条商品数据
  4. 查询所有商品，按价格排序
  5. 更新某个商品的价格
  6. 删除某个商品