数据定义

CREATE TABLE

基本建表

CREATE TABLE employees (
  id          INT           PRIMARY KEY AUTO_INCREMENT,
  name        VARCHAR(100)  NOT NULL,
  email       VARCHAR(255)  UNIQUE,
  department  VARCHAR(50)   DEFAULT 'General',
  salary      DECIMAL(10,2) CHECK (salary > 0),
  hire_date   DATE          NOT NULL,
  is_active   BOOLEAN       DEFAULT true,
  created_at  TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

从查询创建表

-- CTAS: Create Table As Select
CREATE TABLE employees_backup AS
SELECT * FROM employees WHERE hire_date >= '2024-01-01';

-- 只复制结构不复制数据
-- PostgreSQL
CREATE TABLE employees_empty (LIKE employees INCLUDING ALL);

-- MySQL
CREATE TABLE employees_empty LIKE employees;

-- SQL Server
SELECT * INTO employees_empty FROM employees WHERE 1 = 0;

临时表

-- 会话级临时表（连接断开后自动删除）
-- PostgreSQL
CREATE TEMPORARY TABLE temp_results AS
SELECT department, AVG(salary) AS avg_salary
FROM employees GROUP BY department;

-- MySQL
CREATE TEMPORARY TABLE temp_results AS
SELECT department, AVG(salary) AS avg_salary
FROM employees GROUP BY department;

-- SQL Server
CREATE TABLE #temp_results (
  department VARCHAR(50),
  avg_salary DECIMAL(10,2)
);

-- 全局临时表（SQL Server）
CREATE TABLE ##global_temp (
  id INT,
  value VARCHAR(100)
);

-- Oracle
CREATE GLOBAL TEMPORARY TABLE temp_results (
  id NUMBER,
  value VARCHAR2(100)
) ON COMMIT PRESERVE ROWS;  -- 或 ON COMMIT DELETE ROWS

数据类型

数值类型

-- DECIMAL 精度示例
-- DECIMAL(10,2): 最多 10 位数字，其中 2 位小数
-- 最大值: 99999999.99
CREATE TABLE products (
  price DECIMAL(10,2) NOT NULL,    -- 精确到分
  discount DECIMAL(5,4) NOT NULL   -- 如 0.1500 表示 15%
);

--  浮点数精度问题
SELECT 0.1 + 0.2 = 0.3;  -- 可能返回 false！
--  使用 DECIMAL
SELECT CAST(0.1 AS DECIMAL(10,2)) + CAST(0.2 AS DECIMAL(10,2)) = CAST(0.3 AS DECIMAL(10,2));

字符串类型

-- CHAR vs VARCHAR
-- CHAR(10): 'hello     ' (补空格到 10 位)
-- VARCHAR(10): 'hello' (实际长度 5)

-- PostgreSQL: VARCHAR 无长度限制时等同 TEXT
-- MySQL: VARCHAR 最大 65535 字节
-- SQL Server: VARCHAR(MAX) 可存储 2GB

-- PostgreSQL 特有类型
-- CHAR(n) / VARCHAR(n) / TEXT 都支持 Unicode
CREATE TABLE articles (
  title   VARCHAR(500) NOT NULL,
  content TEXT NOT NULL
);

日期时间类型

-- PostgreSQL: TIMESTAMPTZ（带时区）
CREATE TABLE events (
  event_time  TIMESTAMP NOT NULL,           -- 不带时区
  created_at  TIMESTAMPTZ DEFAULT NOW()     -- 带时区
);

-- 时间间隔运算
SELECT
  order_date,
  order_date + INTERVAL '7 days' AS expected_delivery,
  AGE(CURRENT_TIMESTAMP, order_date) AS time_elapsed  -- PostgreSQL
FROM orders;

-- MySQL 日期运算
SELECT
  order_date,
  DATE_ADD(order_date, INTERVAL 7 DAY) AS expected_delivery,
  DATEDIFF(CURRENT_DATE, order_date) AS days_elapsed
FROM orders;

特殊类型

-- PostgreSQL 丰富类型
CREATE TABLE pg_features (
  -- 布尔
  is_active   BOOLEAN DEFAULT true,
  -- 数组
  tags        TEXT[],
  scores      INT[],
  -- JSON
  metadata    JSONB,
  -- UUID
  id          UUID DEFAULT gen_random_uuid(),
  -- 网络
  ip_address  INET,
  mac_address MACADDR,
  -- 几何
  location    POINT,
  area        POLYGON,
  -- 货币
  price       MONEY,
  -- 位串
  flags       BIT(8),
  -- 范围
  age_range   INT4RANGE,
  -- 枚举
  status      status_enum
);

-- MySQL JSON
CREATE TABLE mysql_features (
  id       INT AUTO_INCREMENT PRIMARY KEY,
  metadata JSON,
  -- MySQL 8.0+ 支持 JSON 列的索引（通过生成列）
  metadata_name VARCHAR(100) GENERATED ALWAYS AS (JSON_UNQUOTE(metadata->'$.name')) STORED,
  INDEX idx_metadata_name (metadata_name)
);

约束

PRIMARY KEY 主键

-- 单列主键
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);

-- 自增主键
-- MySQL
id INT AUTO_INCREMENT PRIMARY KEY

-- PostgreSQL
id SERIAL PRIMARY KEY
-- 或
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

-- SQL Server
id INT IDENTITY(1,1) PRIMARY KEY

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

-- 命名约束（推荐）
CREATE TABLE users (
  id INT,
  CONSTRAINT pk_users_id PRIMARY KEY (id)
);

FOREIGN KEY 外键

-- 基本外键
CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- 外键动作
CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE CASCADE       -- 删除客户时级联删除订单
    ON UPDATE CASCADE       -- 更新客户 ID 时级联更新
);

-- 外键动作选项：
-- ON DELETE/UPDATE:
--   CASCADE:     级联操作
--   SET NULL:    设为 NULL（列必须允许 NULL）
--   SET DEFAULT: 设为默认值
--   RESTRICT:    拒绝操作（默认）
--   NO ACTION:   同 RESTRICT（标准 SQL）

-- 自引用外键
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  manager_id INT,
  FOREIGN KEY (manager_id) REFERENCES employees(id)
);

UNIQUE 唯一约束

-- 列级唯一约束
CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(255) UNIQUE
);

-- 命名唯一约束
CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(255),
  CONSTRAINT uk_users_email UNIQUE (email)
);

-- 复合唯一约束
CREATE TABLE user_roles (
  user_id INT,
  role_id INT,
  CONSTRAINT uk_user_role UNIQUE (user_id, role_id)
);

-- 唯一约束与 NULL
-- PostgreSQL: 允许多个 NULL（NULL ≠ NULL）
-- MySQL: 允许多个 NULL（InnoDB）
-- SQL Server: 允许一个 NULL（索引视图除外）

CHECK 检查约束

-- 列级 CHECK
CREATE TABLE products (
  id INT PRIMARY KEY,
  price DECIMAL(10,2) CHECK (price > 0),
  discount DECIMAL(5,4) CHECK (discount >= 0 AND discount <= 1),
  stock INT CHECK (stock >= 0)
);

-- 表级 CHECK（可引用多列）
CREATE TABLE orders (
  id INT PRIMARY KEY,
  start_date DATE,
  end_date DATE,
  CONSTRAINT chk_date_range CHECK (end_date >= start_date)
);

-- PostgreSQL: 支持更复杂的 CHECK
CREATE TABLE schedules (
  id INT PRIMARY KEY,
  day_of_week INT CHECK (day_of_week BETWEEN 1 AND 7),
  start_time TIME,
  end_time TIME,
  CONSTRAINT chk_time_range CHECK (end_time > start_time)
);

DEFAULT 默认值

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  status VARCHAR(20) DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  balance DECIMAL(10,2) DEFAULT 0.00
);

-- PostgreSQL: 动态默认值
CREATE TABLE users (
  id UUID DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- MySQL: 动态默认值（8.0+）
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

ALTER TABLE

-- 添加列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users ADD COLUMN age INT DEFAULT 0;

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

-- 修改列类型
-- PostgreSQL
ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(200);
-- MySQL
ALTER TABLE users MODIFY COLUMN name VARCHAR(200);
-- SQL Server
ALTER TABLE users ALTER COLUMN name VARCHAR(200);

-- 重命名列
-- PostgreSQL
ALTER TABLE users RENAME COLUMN name TO full_name;
-- MySQL
ALTER TABLE users CHANGE COLUMN name full_name VARCHAR(100);
-- SQL Server
EXEC sp_rename 'users.name', 'full_name', 'COLUMN';

-- 添加约束
ALTER TABLE users ADD CONSTRAINT uk_users_email UNIQUE (email);
ALTER TABLE orders ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id) REFERENCES customers(id);

-- 删除约束
ALTER TABLE users DROP CONSTRAINT uk_users_email;
-- MySQL
ALTER TABLE users DROP INDEX uk_users_email;

-- 重命名表
ALTER TABLE users RENAME TO app_users;
-- SQL Server
EXEC sp_rename 'users', 'app_users';

-- 添加主键
ALTER TABLE users ADD PRIMARY KEY (id);
-- 删除主键
ALTER TABLE users DROP PRIMARY KEY;  -- MySQL
ALTER TABLE users DROP CONSTRAINT users_pkey;  -- PostgreSQL

DROP

-- 删除表（表必须存在）
DROP TABLE users;

-- 如果存在则删除（避免报错）
DROP TABLE IF EXISTS users;

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

-- 删除数据库
DROP DATABASE mydb;
DROP DATABASE IF EXISTS mydb;

视图

视图是存储的查询定义，不存储实际数据（物化视图除外）。

-- 创建视图
CREATE VIEW active_users AS
SELECT id, name, email
FROM users
WHERE status = 'active' AND last_login > CURRENT_DATE - INTERVAL '30 days';

-- 使用视图
SELECT * FROM active_users WHERE name LIKE 'A%';

-- 可更新视图（满足条件时可直接 INSERT/UPDATE/DELETE）
-- 条件: 单表、无聚合、无 DISTINCT、无 GROUP BY 等
CREATE VIEW user_emails AS
SELECT id, name, email FROM users;

UPDATE user_emails SET email = 'new@example.com' WHERE id = 1;  --

-- WITH CHECK OPTION: 确保通过视图修改的数据仍满足视图条件
CREATE VIEW active_users AS
SELECT id, name, email, status FROM users WHERE status = 'active'
WITH CHECK OPTION;

-- 以下操作会被拒绝（因为修改后不再满足 status='active'）
UPDATE active_users SET status = 'inactive' WHERE id = 1;  --

-- 替换视图
CREATE OR REPLACE VIEW active_users AS
SELECT id, name, email, status FROM users WHERE status = 'active';

-- 删除视图
DROP VIEW IF EXISTS active_users;

索引

创建索引

-- 基本索引
CREATE INDEX idx_users_email ON users(email);

-- 唯一索引
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- 复合索引（注意列顺序）
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- 降序索引
CREATE INDEX idx_orders_date_desc ON orders(order_date DESC);

-- 部分索引（PostgreSQL）
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

-- 表达式索引（PostgreSQL）
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- 前缀索引（MySQL，用于长字符串）
CREATE INDEX idx_users_name_prefix ON users(name(20));

-- 函数索引（Oracle / PostgreSQL）
CREATE INDEX idx_orders_month ON orders(EXTRACT(MONTH FROM order_date));

索引类型

-- PostgreSQL: 指定索引类型
CREATE INDEX idx_users_email_hash ON users USING hash (email);
CREATE INDEX idx_articles_content ON articles USING gin (to_tsvector('english', content));
CREATE INDEX idx_logs_timestamp ON logs USING brin (created_at);

-- MySQL: 全文索引
CREATE FULLTEXT INDEX idx_articles_content ON articles(title, content);
SELECT * FROM articles WHERE MATCH(title, content) AGAINST('database optimization');

索引管理

-- 删除索引
DROP INDEX idx_users_email;
-- PostgreSQL 需要指定表名
DROP INDEX idx_users_email;  -- PostgreSQL
-- MySQL
ALTER TABLE users DROP INDEX idx_users_email;
-- SQL Server
DROP INDEX idx_users_email ON users;

-- 重建索引
-- PostgreSQL
REINDEX INDEX idx_users_email;
REINDEX TABLE users;

-- MySQL
ALTER TABLE users ENGINE=InnoDB;  -- 重建所有索引

-- SQL Server
ALTER INDEX idx_users_email ON users REBUILD;

-- 查看索引
-- PostgreSQL
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'users';

-- MySQL
SHOW INDEX FROM users;

-- 并发创建索引（PostgreSQL，不锁表）
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

序列

-- PostgreSQL: 创建序列
CREATE SEQUENCE order_seq
  START WITH 1000
  INCREMENT BY 1
  NO MAXVALUE
  NO CYCLE
  CACHE 20;

-- 使用序列
INSERT INTO orders (id, customer_id) VALUES (NEXTVAL('order_seq'), 1);

-- 查看当前值（不消耗）
SELECT CURRVAL('order_seq');

-- 设置序列值
SELECT SETVAL('order_seq', 5000);

-- 将序列关联到列
ALTER TABLE orders ALTER COLUMN id SET DEFAULT NEXTVAL('order_seq');

-- MySQL: 使用 AUTO_INCREMENT（无独立序列对象）
-- Oracle: 使用 SEQUENCE + TRIGGER 实现自增
CREATE SEQUENCE order_seq START WITH 1000 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER trg_orders_id
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  :NEW.id := order_seq.NEXTVAL;
END;

小结

• 选择合适的数据类型：金融用 DECIMAL，时间用 TIMESTAMPTZ，大文本用 TEXT
• 约束是数据完整性的保障：PRIMARY KEY 唯一标识、FOREIGN KEY 维护关联、CHECK 保证域完整性
• 视图简化查询但不存储数据，物化视图（见性能优化章节）缓存查询结果
• 索引是查询性能的关键：B-Tree 通用，GIN 适合 JSON/全文，BRIN 适合大表时序数据
• 序列用于生成唯一标识符，PostgreSQL 原生支持，MySQL 通过 AUTO_INCREMENT 实现