关系型数据库完全指南：MySQL、PostgreSQL、SQLite、Oracle

本文全面介绍四种主流关系型数据库：MySQL、PostgreSQL、SQLite、Oracle。包含背景介绍、核心特性、CRUD 语法对比、使用场景和选型建议，帮助你快速选择最适合的数据库方案。

一、数据库背景介绍

1.1 数据库发展简史

数据库的演进：

文件系统时代（1960年代前）
- 数据存储在文件中，程序直接操作文件
- 问题：数据冗余、不一致、难以共享
层次模型和网状模型（1960-1970年代）
- 层次数据库：IBM 的 IMS
- 网状数据库：CODASYL 标准
- 问题：结构复杂、难以维护
关系模型诞生（1970年）
- Edgar F. Codd 提出关系模型理论
- 奠定了现代数据库的基础
- 使用数学集合论处理数据
SQL 标准化（1980年代）
- IBM 开发 SQL（Structured Query Language）
- 1986年成为 ANSI 标准
- 统一的数据库查询语言
商业数据库崛起（1980-1990年代）
- Oracle、IBM DB2、Microsoft SQL Server
- 企业级应用广泛采用
开源数据库兴起（1990年代后）
- MySQL（1995）、PostgreSQL（1996）
- 降低数据库使用成本
- Web 应用快速发展

1.2 什么是关系型数据库

关系型数据库（RDBMS - Relational Database Management System） 是一种基于关系模型的数据库管理系统，使用表格（表）来存储和管理数据。

关系模型理论基础：

由 Edgar F. Codd 在 1970 年提出
基于数学集合论和谓词逻辑
数据以"关系"（表）的形式组织
通过关系代数进行数据操作

核心概念：

表（Table/Relation）：数据的二维结构，由行和列组成
- 每一行代表一条记录（元组）
- 每一列代表一个属性（字段）
行（Row/Record/Tuple）：表中的一条记录，表示一个实体
列（Column/Field/Attribute）：表中的字段，定义数据类型和约束
主键（Primary Key）：唯一标识一条记录的字段或字段组合
外键（Foreign Key）：关联其他表的字段，维护引用完整性
索引（Index）：提高查询速度的数据结构
约束（Constraint）：保证数据完整性的规则（NOT NULL、UNIQUE、CHECK 等）

关系模型特点：

✅ ACID 特性：
- 原子性（Atomicity）：事务要么全部成功，要么全部失败
- 一致性（Consistency）：事务前后数据保持一致
- 隔离性（Isolation）：并发事务互不干扰
- 持久性（Durability）：提交的数据永久保存
✅ 数据完整性：通过约束保证数据正确性
✅ 标准化查询：使用 SQL（Structured Query Language）统一查询
✅ 事务支持：保证数据操作的可靠性
✅ 范式化设计：减少数据冗余，提高数据一致性

1.3 为什么需要数据库

1. 数据持久化需求：

问题：程序运行时的数据存储在内存中，程序关闭后数据丢失
解决：数据库将数据持久化到磁盘，永久保存
优势：数据不因程序重启而丢失

2. 数据管理需求：

结构化存储：按表结构组织数据，便于管理和理解
高效查询：通过索引和查询优化器快速检索数据
数据关联：通过外键建立表间关系，维护数据一致性
数据约束：通过约束保证数据正确性（如邮箱格式、年龄范围）

3. 并发控制需求：

问题：多用户同时访问时可能出现数据冲突
解决：通过锁机制和事务隔离级别控制并发访问
优势：保证多用户环境下的数据一致性

4. 数据安全需求：

权限控制：不同用户有不同的数据访问权限
备份恢复：定期备份，故障时快速恢复
审计日志：记录数据操作历史，便于追踪

5. 性能优化需求：

索引优化：快速定位数据
查询优化器：自动选择最优执行计划
缓存机制：减少磁盘 I/O

1.4 数据库的应用场景

Web 应用：

用户信息管理（注册、登录、个人资料）
内容管理（文章、评论、标签）
电商系统（商品、订单、购物车）
社交网络（好友关系、动态、消息）

企业系统：

ERP（企业资源规划）：库存、采购、销售
CRM（客户关系管理）：客户信息、销售机会
财务系统：账目、报表、审计
OA（办公自动化）：流程、文档、审批

数据分析：

数据仓库：历史数据存储和分析
BI（商业智能）：报表生成、数据可视化
大数据分析：结合 Hadoop、Spark 等工具

移动应用：

本地数据缓存：离线数据存储
用户偏好设置：应用配置信息
消息队列：待同步数据

IoT 和嵌入式：

设备数据采集：传感器数据存储
边缘计算：本地数据处理
日志记录：设备运行日志

1.5 四种数据库的定位

数据库	定位	特点	典型用户
MySQL	通用 Web 数据库	普及度高、生态成熟、易上手、性能优秀	中小型 Web 应用、电商平台
PostgreSQL	企业级开源数据库	功能强大、标准 SQL、扩展性强、复杂查询优秀	企业级应用、数据分析系统
SQLite	嵌入式数据库	轻量级、零配置、单文件、跨平台	移动应用、桌面软件、IoT
Oracle	企业级商业数据库	功能全面、高可用、商业支持、性能卓越	大型企业、金融机构、政府

选择建议：

快速开发 Web 应用 → MySQL
复杂业务系统 → PostgreSQL
移动/桌面应用 → SQLite
大型企业核心系统 → Oracle

二、MySQL

2.1 背景介绍

历史与发展：

1995 年由 Michael Widenius 开发
2008 年被 Sun 收购，2010 年 Sun 被 Oracle 收购
目前由 Oracle 维护，同时有 MariaDB（MySQL 分支）作为开源替代

市场地位：

全球最流行的开源关系型数据库
在 Web 应用领域占据主导地位
LAMP/LNMP 技术栈的核心组件

核心特性：

✅ 开源免费（GPL 许可证）
✅ 性能优秀，读写速度快
✅ 生态成熟，工具和文档丰富
✅ 支持多种存储引擎（InnoDB、MyISAM 等）
✅ 主从复制、读写分离支持完善

2.2 作用和使用场景

主要作用：

Web 应用数据存储
- 用户管理：用户注册、登录、权限管理
- 内容管理：文章、评论、标签、分类
- 电商系统：商品、订单、购物车、支付记录
- 社交功能：好友关系、动态、消息
业务系统后端
- CRM 系统：客户信息、销售机会、跟进记录
- ERP 系统：库存、采购、销售、财务
- OA 系统：流程审批、文档管理、考勤
- 教育系统：课程、学生、成绩、选课
日志和监控数据
- 系统日志：操作日志、错误日志、访问日志
- 性能监控：服务器指标、应用性能数据
- 审计日志：数据变更记录、安全审计
缓存和会话存储
- 会话存储：用户会话数据（替代文件存储）
- 缓存备份：Redis 的持久化备份
- 临时数据：验证码、临时文件元数据

适用场景：

✅ 非常适合：

中小规模业务系统
- 日活用户 < 1000 万
- 数据量 < 100GB
- 并发连接 < 1000
- 案例：中小型电商平台、企业官网、博客系统
常规 Web 应用
- 博客系统：WordPress、Typecho
- 论坛系统：Discuz、phpBB
- CMS 系统：Drupal、Joomla
- Wiki 系统：MediaWiki
电商平台
- 商品管理：商品信息、库存、价格
- 订单系统：订单创建、支付、物流
- 用户系统：注册、登录、个人中心
- 案例：淘宝早期、京东部分业务
快速开发项目
- 需要快速落地
- 团队熟悉 MySQL
- 生态工具丰富（ORM、管理工具）
LAMP/LNMP 技术栈
- Linux + Apache/Nginx + MySQL + PHP/Python
- 经典 Web 开发组合

❌ 不太适合：

强分析型系统
- 复杂报表生成
- 数据仓库场景
- OLAP（联机分析处理）
- 替代方案：PostgreSQL、专用分析数据库
复杂数据类型需求
- 需要数组类型
- JSON 深度查询和索引
- 地理信息处理
- 替代方案：PostgreSQL（JSONB、PostGIS）
对 SQL 标准要求极高
- 需要严格遵循 SQL 标准
- 复杂窗口函数
- 替代方案：PostgreSQL
需要大量扩展功能
- 全文检索（虽然支持，但不如 PostgreSQL）
- 复杂数据类型
- 替代方案：PostgreSQL

实际应用案例：

Facebook：早期使用 MySQL（后迁移到自研系统）
Twitter：部分业务使用 MySQL
YouTube：早期使用 MySQL
Wikipedia：使用 MySQL
GitHub：使用 MySQL
阿里巴巴：大量使用 MySQL（淘宝、天猫）

2.3 项目案例：中小型电商平台

项目背景：某中小型电商平台，日活用户约 50 万，商品数量 10 万+，日均订单 5000+。需要支持用户注册登录、商品浏览、购物车、订单管理、支付等核心功能。

技术选型理由：

团队熟悉 MySQL，开发效率高
业务逻辑相对简单，主要是 CRUD 操作
需要快速上线，MySQL 生态成熟
中小规模数据量，MySQL 性能足够

数据库设计：

-- 用户表
CREATE TABLE users (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(64) NOT NULL UNIQUE COMMENT '用户名',
  email VARCHAR(128) NOT NULL UNIQUE COMMENT '邮箱',
  password_hash VARCHAR(255) NOT NULL COMMENT '密码哈希',
  phone VARCHAR(20) COMMENT '手机号',
  avatar_url VARCHAR(255) COMMENT '头像URL',
  status TINYINT DEFAULT 1 COMMENT '状态：1-正常，0-禁用',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_email (email),
  INDEX idx_username (username),
  INDEX idx_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- 商品表
CREATE TABLE products (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL COMMENT '商品名称',
  description TEXT COMMENT '商品描述',
  price DECIMAL(10, 2) NOT NULL COMMENT '价格',
  original_price DECIMAL(10, 2) COMMENT '原价',
  stock INT DEFAULT 0 COMMENT '库存',
  category_id INT NOT NULL COMMENT '分类ID',
  image_url VARCHAR(255) COMMENT '主图URL',
  status TINYINT DEFAULT 1 COMMENT '状态：1-上架，0-下架',
  sales_count INT DEFAULT 0 COMMENT '销量',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_category (category_id),
  INDEX idx_status (status),
  INDEX idx_price (price)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';

-- 订单表
CREATE TABLE orders (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_no VARCHAR(32) NOT NULL UNIQUE COMMENT '订单号',
  user_id BIGINT NOT NULL COMMENT '用户ID',
  total_amount DECIMAL(10, 2) NOT NULL COMMENT '订单总金额',
  status TINYINT DEFAULT 0 COMMENT '状态：0-待支付，1-已支付，2-已发货，3-已完成，4-已取消',
  payment_method VARCHAR(20) COMMENT '支付方式',
  shipping_address TEXT COMMENT '收货地址',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_user_id (user_id),
  INDEX idx_order_no (order_no),
  INDEX idx_status (status),
  INDEX idx_created_at (created_at),
  FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

-- 订单项表
CREATE TABLE order_items (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_id BIGINT NOT NULL COMMENT '订单ID',
  product_id BIGINT NOT NULL COMMENT '商品ID',
  product_name VARCHAR(255) NOT NULL COMMENT '商品名称（快照）',
  product_price DECIMAL(10, 2) NOT NULL COMMENT '商品价格（快照）',
  quantity INT NOT NULL COMMENT '数量',
  subtotal DECIMAL(10, 2) NOT NULL COMMENT '小计',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_order_id (order_id),
  INDEX idx_product_id (product_id),
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单项表';

-- 购物车表
CREATE TABLE cart_items (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL COMMENT '用户ID',
  product_id BIGINT NOT NULL COMMENT '商品ID',
  quantity INT NOT NULL DEFAULT 1 COMMENT '数量',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uk_user_product (user_id, product_id),
  INDEX idx_user_id (user_id),
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='购物车表';

关键实现：

-- 1. 用户注册（事务保证数据一致性）
START TRANSACTION;
INSERT INTO users (username, email, password_hash) 
VALUES ('alice', 'alice@example.com', '$2y$10$...');
SET @user_id = LAST_INSERT_ID();
-- 可以在这里添加其他初始化操作
COMMIT;

-- 2. 商品列表查询（分页、排序、筛选）
SELECT 
  p.id,
  p.name,
  p.price,
  p.original_price,
  p.image_url,
  p.sales_count,
  c.name as category_name
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
WHERE p.status = 1
  AND p.category_id = 1  -- 分类筛选
  AND p.price BETWEEN 100 AND 1000  -- 价格区间
ORDER BY p.sales_count DESC, p.created_at DESC
LIMIT 20 OFFSET 0;

-- 3. 创建订单（事务 + 库存检查）
START TRANSACTION;
-- 检查库存
SELECT stock FROM products WHERE id = 1 FOR UPDATE;
-- 创建订单
INSERT INTO orders (order_no, user_id, total_amount, status) 
VALUES ('ORD20240101001', 1, 999.99, 0);
SET @order_id = LAST_INSERT_ID();
-- 添加订单项
INSERT INTO order_items (order_id, product_id, product_name, product_price, quantity, subtotal)
VALUES (@order_id, 1, 'Laptop', 999.99, 1, 999.99);
-- 扣减库存
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;

-- 4. 用户订单统计（聚合查询）
SELECT 
  u.username,
  COUNT(o.id) as order_count,
  COALESCE(SUM(o.total_amount), 0) as total_spent,
  AVG(o.total_amount) as avg_order_amount,
  MAX(o.created_at) as last_order_time
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status IN (1, 2, 3)
WHERE u.id = 1
GROUP BY u.id, u.username;

-- 5. 热门商品查询（使用子查询）
SELECT 
  p.id,
  p.name,
  p.price,
  p.sales_count,
  COUNT(oi.id) as order_item_count
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
WHERE p.status = 1
GROUP BY p.id, p.name, p.price, p.sales_count
HAVING order_item_count > 10
ORDER BY p.sales_count DESC
LIMIT 10;

性能优化：

-- 1. 慢查询优化：添加复合索引
CREATE INDEX idx_products_category_status_price 
ON products(category_id, status, price);

-- 2. 订单查询优化：分区表（按月分区）
ALTER TABLE orders PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at)) (
  PARTITION p202401 VALUES LESS THAN (202402),
  PARTITION p202402 VALUES LESS THAN (202403),
  -- ... 更多分区
);

-- 3. 读写分离：主库写，从库读
-- 主库：写操作
INSERT INTO orders ...;
-- 从库：读操作
SELECT * FROM orders WHERE user_id = 1;

项目成果：

系统稳定运行，支持日均 5000+ 订单
查询响应时间 < 100ms（95% 分位）
数据库大小约 50GB，性能良好
使用主从复制实现高可用

2.4 快速上手

安装与连接：

# macOS 安装
brew install mysql
brew services start mysql

# Linux 安装
sudo apt-get install mysql-server  # Ubuntu/Debian
sudo yum install mysql-server      # CentOS/RHEL

# 连接数据库
mysql -u root -p
# 输入密码后进入 MySQL 命令行

创建数据库和表：

-- 创建数据库
CREATE DATABASE demo CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE demo;

-- 创建用户表
CREATE TABLE users (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(64) NOT NULL UNIQUE,
  email VARCHAR(128) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  status ENUM('active', 'inactive', 'banned') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_email (email),
  INDEX idx_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

基础 CRUD 操作：

-- 插入数据
INSERT INTO users (username, email, password_hash) 
VALUES ('alice', 'alice@example.com', 'hash123');

INSERT INTO users (username, email, password_hash) 
VALUES 
  ('bob', 'bob@example.com', 'hash456'),
  ('charlie', 'charlie@example.com', 'hash789');

-- 查询数据
SELECT * FROM users;
SELECT id, username, email FROM users WHERE status = 'active';
SELECT * FROM users WHERE email LIKE '%@example.com' ORDER BY created_at DESC LIMIT 10;

-- 更新数据
UPDATE users SET status = 'inactive' WHERE id = 1;
UPDATE users SET email = 'newemail@example.com', updated_at = NOW() WHERE username = 'alice';

-- 删除数据
DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE status = 'banned' AND created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);

事务操作：

-- 开启事务
START TRANSACTION;

-- 执行多个操作
INSERT INTO users (username, email, password_hash) VALUES ('user1', 'user1@example.com', 'hash1');
UPDATE users SET status = 'active' WHERE username = 'user1';

-- 提交事务
COMMIT;

-- 或回滚
ROLLBACK;

2.4 数据类型

数值类型：

TINYINT      -- 1字节，-128到127
SMALLINT     -- 2字节，-32768到32767
MEDIUMINT    -- 3字节
INT/INTEGER  -- 4字节，常用
BIGINT       -- 8字节，大整数

FLOAT        -- 单精度浮点
DOUBLE       -- 双精度浮点
DECIMAL(M,D) -- 精确小数，M总位数，D小数位

字符串类型：

CHAR(64)     -- 固定长度，适合短字符串
VARCHAR(255) -- 可变长度，常用
TEXT         -- 长文本，最大65535字符
MEDIUMTEXT   -- 中等文本，最大16MB
LONGTEXT     -- 超长文本，最大4GB

日期时间类型：

DATE         -- 日期：'2024-01-01'
TIME         -- 时间：'14:30:00'
DATETIME     -- 日期时间：'2024-01-01 14:30:00'
TIMESTAMP    -- 时间戳，自动更新
YEAR         -- 年份

2.5 性能优化

索引优化：

-- 主键索引（自动创建）
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(64)
);

-- 普通索引
CREATE INDEX idx_name ON users(name);

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

-- 复合索引（最左前缀原则）
CREATE INDEX idx_name_age ON users(name, age);

查询优化：

-- 使用 EXPLAIN 分析查询
EXPLAIN SELECT * FROM users WHERE name = 'Alice';

-- 避免 SELECT *
SELECT id, name, email FROM users;  -- 好
SELECT * FROM users;                -- 差

-- 使用 LIMIT
SELECT * FROM users LIMIT 10 OFFSET 20;

三、PostgreSQL

3.1 背景介绍

历史与发展：

1986 年由 Michael Stonebraker 在加州大学伯克利分校开发
1996 年开源，采用 PostgreSQL 许可（类似 BSD）
被称为"最先进的开源关系型数据库"

市场地位：

企业级应用的首选开源数据库
Odoo、GitLab 等知名项目的默认数据库
在复杂业务场景中逐渐超越 MySQL

核心特性：

✅ 严格遵循 SQL 标准
✅ 支持复杂数据类型（数组、JSONB、范围类型等）
✅ 强大的扩展能力（PostGIS、全文检索等）
✅ 优秀的查询优化器
✅ ACID 特性完整支持

3.2 作用和使用场景

主要作用：

企业级应用
- ERP 系统：复杂业务流程、多模块集成
- CRM 系统：客户关系管理、销售流程
- 财务系统：账务处理、报表生成
- HR 系统：员工管理、薪资计算
数据分析系统
- 数据仓库：历史数据存储和分析
- 报表系统：复杂报表生成
- BI 工具：商业智能分析
- OLAP 场景：联机分析处理
地理信息系统（GIS）
- 结合 PostGIS：地理数据存储和查询
- 地图应用：位置服务、路径规划
- 空间分析：地理围栏、距离计算
- 案例：OpenStreetMap、地理信息平台
内容管理系统
- JSONB 存储：半结构化数据（用户偏好、配置）
- 文档存储：替代部分 NoSQL 场景
- 元数据管理：灵活的元数据结构
科学计算和研究
- 数据研究：科研数据存储
- 统计分析：结合 R、Python 进行数据分析

适用场景：

✅ 特别适合：

企业级系统、复杂业务域
- 复杂的业务逻辑
- 多表关联查询
- 复杂的数据模型
- 案例：Odoo ERP、GitLab、Reddit
需要强一致性 + 复杂查询
- 金融系统：账户余额、交易记录
- 电商系统：库存管理、订单处理
- 优势：ACID 保证、复杂查询优化
结构化 + 半结构化混合存储
- 用户配置信息（JSONB）
- 产品属性（灵活结构）
- 优势：JSONB 性能优于 MySQL 的 JSON
报表/分析/复杂检索需求
- 复杂报表生成
- 数据分析查询
- 全文检索需求
- 案例：数据仓库、BI 系统
特定应用场景
- Odoo：官方推荐 PostgreSQL
- GitLab：使用 PostgreSQL
- Reddit：使用 PostgreSQL
- Instagram：部分业务使用 PostgreSQL
需要地理信息处理
- PostGIS 扩展：地理数据支持
- 地图应用、位置服务
- 空间数据查询和分析
需要扩展功能
- 全文检索：PostgreSQL 内置支持
- 数组类型：存储标签、分类
- 范围类型：时间范围、数值范围
- 自定义类型：创建领域特定类型

❌ 不太适合：

极简、零运维的小应用
- 简单的个人项目
- 原型验证
- 替代方案：SQLite
只需要简单 CRUD 的场景
- 简单的增删改查
- 不需要复杂查询
- 替代方案：MySQL（更简单）
团队不熟悉 PostgreSQL
- 团队只有 MySQL 经验
- 学习成本考虑
- 建议：评估学习成本 vs 功能需求

实际应用案例：

Odoo：开源 ERP 系统，官方推荐 PostgreSQL
GitLab：代码托管平台，使用 PostgreSQL
Reddit：社交新闻网站，使用 PostgreSQL
Instagram：部分业务使用 PostgreSQL
Apple：部分服务使用 PostgreSQL
Skype：使用 PostgreSQL
NASA：部分项目使用 PostgreSQL

3.3 项目案例：企业级内容管理系统（CMS）

项目背景：某大型企业需要构建一个内容管理系统，支持文章发布、用户管理、标签分类、全文检索、数据分析等功能。系统需要处理大量半结构化数据（文章元数据、用户偏好），并支持复杂的查询和报表生成。

技术选型理由：

需要存储和查询 JSON 格式的元数据
需要全文检索功能
需要复杂的报表查询
需要数组类型存储标签
团队有 PostgreSQL 经验

数据库设计：

-- 启用扩展
CREATE EXTENSION IF NOT EXISTS "pg_trgm";  -- 全文检索
CREATE EXTENSION IF NOT EXISTS "btree_gin";  -- GIN 索引优化

-- 用户表（使用 JSONB 存储用户偏好）
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  username VARCHAR(64) NOT NULL UNIQUE,
  email VARCHAR(128) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  profile JSONB DEFAULT '{}'::jsonb,  -- 用户资料（JSONB）
  preferences JSONB DEFAULT '{}'::jsonb,  -- 用户偏好设置
  tags TEXT[],  -- 用户标签数组
  status VARCHAR(20) DEFAULT 'active',
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- 创建 JSONB 和数组索引
CREATE INDEX idx_users_profile_gin ON users USING GIN(profile);
CREATE INDEX idx_users_preferences_gin ON users USING GIN(preferences);
CREATE INDEX idx_users_tags_gin ON users USING GIN(tags);

-- 文章表（支持全文检索）
CREATE TABLE articles (
  id BIGSERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL,
  excerpt TEXT,
  author_id BIGINT NOT NULL,
  category_id INT,
  tags TEXT[],  -- 文章标签数组
  metadata JSONB DEFAULT '{}'::jsonb,  -- 文章元数据（作者信息、来源等）
  status VARCHAR(20) DEFAULT 'draft',  -- draft, published, archived
  view_count INT DEFAULT 0,
  like_count INT DEFAULT 0,
  published_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),
  FOREIGN KEY (author_id) REFERENCES users(id)
);

-- 全文检索索引
CREATE INDEX idx_articles_title_trgm ON articles USING GIN(title gin_trgm_ops);
CREATE INDEX idx_articles_content_trgm ON articles USING GIN(content gin_trgm_ops);
CREATE INDEX idx_articles_tags_gin ON articles USING GIN(tags);
CREATE INDEX idx_articles_metadata_gin ON articles USING GIN(metadata);
CREATE INDEX idx_articles_status_published ON articles(status, published_at) 
WHERE status = 'published';

-- 评论表
CREATE TABLE comments (
  id BIGSERIAL PRIMARY KEY,
  article_id BIGINT NOT NULL,
  user_id BIGINT,
  parent_id BIGINT,  -- 父评论ID（支持嵌套评论）
  content TEXT NOT NULL,
  metadata JSONB DEFAULT '{}'::jsonb,  -- 评论元数据（IP、设备等）
  status VARCHAR(20) DEFAULT 'approved',
  created_at TIMESTAMPTZ DEFAULT now(),
  FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (parent_id) REFERENCES comments(id)
);

-- 创建评论树形结构索引
CREATE INDEX idx_comments_article_parent ON comments(article_id, parent_id);

关键实现：

-- 1. 插入文章（使用 JSONB 和数组）
INSERT INTO articles (title, content, author_id, tags, metadata) 
VALUES (
  'PostgreSQL 全文检索指南',
  'PostgreSQL 提供了强大的全文检索功能...',
  1,
  ARRAY['postgresql', 'database', 'tutorial'],
  '{"source": "blog", "read_time": 5, "difficulty": "intermediate"}'::jsonb
) RETURNING id;

-- 2. JSONB 查询：查找特定元数据的文章
SELECT id, title, metadata
FROM articles
WHERE metadata @> '{"difficulty": "intermediate"}'::jsonb
  AND metadata->>'read_time'::int < 10;

-- 3. 数组查询：查找包含特定标签的文章
SELECT id, title, tags
FROM articles
WHERE tags && ARRAY['postgresql', 'database'];  -- 包含任一标签
-- 或
WHERE tags @> ARRAY['postgresql'];  -- 包含所有指定标签

-- 4. 全文检索：搜索文章内容
SELECT 
  id,
  title,
  ts_rank(to_tsvector('english', content), query) as rank
FROM articles, to_tsquery('english', 'postgresql & database') query
WHERE to_tsvector('english', content) @@ query
ORDER BY rank DESC
LIMIT 10;

-- 5. 复杂报表：用户文章统计（使用窗口函数）
SELECT 
  u.username,
  COUNT(a.id) as article_count,
  SUM(a.view_count) as total_views,
  AVG(a.view_count) as avg_views,
  MAX(a.published_at) as last_published,
  ROW_NUMBER() OVER (ORDER BY COUNT(a.id) DESC) as author_rank
FROM users u
LEFT JOIN articles a ON u.id = a.author_id AND a.status = 'published'
GROUP BY u.id, u.username
ORDER BY article_count DESC;

-- 6. 嵌套评论查询（递归 CTE）
WITH RECURSIVE comment_tree AS (
  -- 根评论
  SELECT id, article_id, user_id, content, parent_id, created_at, 1 as level
  FROM comments
  WHERE article_id = 1 AND parent_id IS NULL

  UNION ALL

  -- 子评论
  SELECT c.id, c.article_id, c.user_id, c.content, c.parent_id, c.created_at, ct.level + 1
  FROM comments c
  INNER JOIN comment_tree ct ON c.parent_id = ct.id
)
SELECT * FROM comment_tree ORDER BY level, created_at;

-- 7. 用户偏好查询（JSONB 路径查询）
SELECT 
  username,
  preferences->'theme' as theme,
  preferences->'language' as language,
  preferences->'notifications'->>'email' as email_notifications
FROM users
WHERE preferences->>'theme' = 'dark';

-- 8. 时间范围查询：统计每日发布文章数
SELECT 
  DATE(published_at) as publish_date,
  COUNT(*) as article_count,
  SUM(view_count) as total_views
FROM articles
WHERE status = 'published'
  AND published_at >= now() - INTERVAL '30 days'
GROUP BY DATE(published_at)
ORDER BY publish_date DESC;

高级特性应用：

-- 1. 物化视图：预计算热门文章
CREATE MATERIALIZED VIEW popular_articles AS
SELECT 
  a.id,
  a.title,
  a.view_count,
  a.like_count,
  a.view_count + a.like_count * 10 as popularity_score,
  a.published_at
FROM articles a
WHERE a.status = 'published'
  AND a.published_at >= now() - INTERVAL '7 days'
ORDER BY popularity_score DESC
LIMIT 100;

-- 定期刷新物化视图
REFRESH MATERIALIZED VIEW CONCURRENTLY popular_articles;

-- 2. 部分索引：只索引已发布文章
CREATE INDEX idx_articles_published_title 
ON articles(title) 
WHERE status = 'published';

-- 3. 表达式索引：搜索用户名（不区分大小写）
CREATE INDEX idx_users_username_lower 
ON users(LOWER(username));

-- 4. 数组操作：添加标签
UPDATE articles 
SET tags = array_append(tags, 'new_tag')
WHERE id = 1;

-- 5. JSONB 更新：更新用户偏好
UPDATE users
SET preferences = preferences || '{"theme": "dark"}'::jsonb
WHERE id = 1;

性能优化：

-- 1. 查询计划分析
EXPLAIN ANALYZE
SELECT * FROM articles 
WHERE tags && ARRAY['postgresql'] 
  AND status = 'published'
ORDER BY published_at DESC
LIMIT 20;

-- 2. 统计信息更新
ANALYZE articles;

-- 3. 连接池配置（应用层）
-- 使用 PgBouncer 或应用连接池

项目成果：

支持 100 万+ 文章存储
全文检索响应时间 < 200ms
JSONB 查询性能优秀，替代了部分 NoSQL 需求
复杂报表查询性能良好
使用 JSONB 灵活存储元数据，无需频繁修改表结构

3.4 快速上手

安装与连接：

# macOS 安装
brew install postgresql@15
brew services start postgresql@15

# Linux 安装
sudo apt-get install postgresql postgresql-contrib  # Ubuntu/Debian
sudo yum install postgresql-server postgresql-contrib  # CentOS/RHEL

# 连接数据库
psql -U postgres
# 或指定数据库
psql -U postgres -d demo

创建数据库和表：

-- 创建数据库
CREATE DATABASE demo;
\c demo  -- 切换到 demo 数据库

-- 创建用户表（支持 JSONB）
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  username VARCHAR(64) NOT NULL UNIQUE,
  email VARCHAR(128) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  metadata JSONB DEFAULT '{}'::jsonb,  -- JSONB 类型
  tags TEXT[],  -- 数组类型
  status VARCHAR(20) DEFAULT 'active',
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- 创建索引
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_metadata_gin ON users USING GIN(metadata);  -- GIN 索引用于 JSONB
CREATE INDEX idx_users_tags ON users USING GIN(tags);  -- 数组索引

基础 CRUD 操作：

-- 插入数据
INSERT INTO users (username, email, password_hash, metadata, tags) 
VALUES (
  'alice', 
  'alice@example.com', 
  'hash123',
  '{"role": "admin", "age": 30}'::jsonb,
  ARRAY['developer', 'admin']
);

INSERT INTO users (username, email, password_hash) 
VALUES 
  ('bob', 'bob@example.com', 'hash456'),
  ('charlie', 'charlie@example.com', 'hash789');

-- 查询数据
SELECT * FROM users;
SELECT id, username, email FROM users WHERE status = 'active';
SELECT * FROM users WHERE email LIKE '%@example.com' ORDER BY created_at DESC LIMIT 10;

-- JSONB 查询
SELECT * FROM users WHERE metadata->>'role' = 'admin';
SELECT * FROM users WHERE metadata @> '{"role": "admin"}'::jsonb;
SELECT username, metadata->>'age' as age FROM users;

-- 数组查询
SELECT * FROM users WHERE 'developer' = ANY(tags);
SELECT * FROM users WHERE tags && ARRAY['admin', 'developer'];

-- 更新数据
UPDATE users SET status = 'inactive' WHERE id = 1;
UPDATE users 
SET 
  email = 'newemail@example.com',
  metadata = metadata || '{"updated": true}'::jsonb,
  updated_at = now()
WHERE username = 'alice';

-- 删除数据
DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE status = 'banned' AND created_at < now() - INTERVAL '1 year';

事务操作：

-- 开启事务
BEGIN;

-- 执行多个操作
INSERT INTO users (username, email, password_hash) VALUES ('user1', 'user1@example.com', 'hash1');
UPDATE users SET status = 'active' WHERE username = 'user1';

-- 提交事务
COMMIT;

-- 或回滚
ROLLBACK;

3.4 高级特性

JSONB 操作：

-- 创建包含 JSONB 的表
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  attributes JSONB,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- 插入 JSONB 数据
INSERT INTO products (name, attributes) 
VALUES (
  'Laptop',
  '{"brand": "Dell", "ram": "16GB", "storage": "512GB", "price": 999.99}'::jsonb
);

-- JSONB 查询
SELECT * FROM products WHERE attributes->>'brand' = 'Dell';
SELECT * FROM products WHERE attributes @> '{"ram": "16GB"}'::jsonb;
SELECT name, attributes->>'price' as price FROM products;

-- JSONB 更新
UPDATE products 
SET attributes = attributes || '{"discount": 0.1}'::jsonb
WHERE id = 1;

数组操作：

-- 创建包含数组的表
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255),
  tags TEXT[],
  created_at TIMESTAMPTZ DEFAULT now()
);

-- 插入数组数据
INSERT INTO posts (title, tags) 
VALUES ('Post 1', ARRAY['python', 'database', 'tutorial']);

-- 数组查询
SELECT * FROM posts WHERE 'python' = ANY(tags);
SELECT * FROM posts WHERE tags && ARRAY['python', 'javascript'];

四、SQLite

4.1 背景介绍

历史与发展：

2000 年由 D. Richard Hipp 开发
设计目标是"零配置"的嵌入式数据库
全球部署量最大的数据库引擎（在移动设备中）

核心特点：

✅ 单文件数据库：整个数据库就是一个文件
✅ 零配置：无需安装服务器，无需配置
✅ 轻量级：库文件只有几百 KB
✅ 跨平台：支持所有主流操作系统
✅ ACID 支持：完整的事务支持

限制：

❌ 并发写入性能有限
❌ 不支持网络访问（需要应用层实现）
❌ 数据类型相对简单

4.2 作用和使用场景

主要作用：

移动应用本地存储
- iOS 应用：Core Data 底层使用 SQLite
- Android 应用：Room 持久化库使用 SQLite
- 用户数据：用户设置、缓存数据、离线内容
- 案例：微信、支付宝（部分本地数据）
桌面软件数据存储
- 单机应用：不需要网络连接的桌面软件
- 配置存储：应用配置、用户偏好
- 数据缓存：临时数据、缓存文件
- 案例：Firefox（书签、历史）、Chrome（部分数据）
嵌入式系统和 IoT
- 边缘设备：传感器数据本地存储
- 资源受限设备：内存和存储有限
- 离线场景：网络不稳定时的数据存储
开发测试环境
- 快速原型：快速验证想法
- 单元测试：测试数据存储
- 开发环境：本地开发数据库
- CI/CD：自动化测试
数据分析和报表工具
- 数据导出：将数据导出为 SQLite 文件
- 数据分析：使用 SQL 分析数据
- 报表工具：本地报表生成
- 案例：一些数据分析工具使用 SQLite
浏览器和应用程序
- 浏览器：存储浏览历史、书签
- 邮件客户端：本地邮件存储
- 媒体播放器：播放列表、元数据

适用场景：

✅ 非常适合：

单机应用、桌面软件
- 不需要网络连接
- 单用户使用
- 案例：本地工具软件、桌面应用
移动端/小程序本地缓存
- 离线数据：网络断开时的数据访问
- 缓存数据：减少网络请求
- 用户设置：应用配置信息
- 案例：移动应用的本地数据库
IoT 边缘设备本地存储
- 资源受限：内存和存储有限
- 离线工作：网络不稳定
- 数据采集：传感器数据存储
- 案例：智能家居设备、工业传感器
测试环境、原型验证
- 快速搭建：无需安装数据库服务器
- 零配置：开箱即用
- 轻量级：不占用太多资源
- 案例：开发测试、概念验证
小型工具型应用
- 个人工具：个人使用的工具软件
- 小型项目：数据量小、用户少
- 案例：个人记账软件、本地笔记应用
数据分析和报表工具
- 数据导出格式：SQLite 作为数据交换格式
- 本地分析：使用 SQL 进行数据分析
- 案例：数据分析工具、报表生成器

❌ 不适合：

高并发写入场景
- 限制：SQLite 使用文件锁，并发写入性能差
- 建议：使用 MySQL/PostgreSQL
- 阈值：并发写入 > 10 个连接
多人/多服务频繁同时写同一库
- 问题：文件锁竞争
- 建议：使用服务器型数据库
- 案例：Web 应用后端、多用户系统
需要复杂数据类型和高级功能
- 限制：数据类型相对简单
- 缺少：复杂索引、窗口函数（3.25+ 才支持）
- 建议：使用 PostgreSQL
大型 Web 应用的后端数据库
- 限制：性能和并发能力
- 建议：使用 MySQL/PostgreSQL
- 例外：只读场景可以考虑
需要网络访问
- 限制：SQLite 是文件数据库，不支持网络协议
- 建议：需要应用层实现网络访问

实际应用案例：

iOS/Android 应用：大量移动应用使用 SQLite
Firefox/Chrome：浏览器使用 SQLite 存储数据
Skype：部分数据使用 SQLite
Adobe：部分产品使用 SQLite
Python：标准库包含 sqlite3 模块
PHP：内置 SQLite 支持

4.3 项目案例：移动端离线笔记应用

项目背景：开发一款移动端笔记应用，支持离线使用、数据同步、标签分类、全文搜索等功能。应用需要在网络断开时也能正常使用，数据存储在本地 SQLite 数据库中，网络恢复后自动同步到服务器。

技术选型理由：

移动应用本地存储需求
零配置，无需数据库服务器
轻量级，适合移动设备
支持事务，保证数据一致性
跨平台，iOS 和 Android 都支持

数据库设计：

-- 笔记表
CREATE TABLE notes (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  tags TEXT,  -- 逗号分隔的标签
  category_id INTEGER,
  is_pinned INTEGER DEFAULT 0,  -- 0-未置顶，1-置顶
  is_deleted INTEGER DEFAULT 0,  -- 0-未删除，1-已删除（软删除）
  sync_status TEXT DEFAULT 'pending',  -- pending, synced, conflict
  server_id INTEGER,  -- 服务器端ID（同步用）
  created_at TEXT DEFAULT (datetime('now')),
  updated_at TEXT DEFAULT (datetime('now')),
  synced_at TEXT  -- 最后同步时间
);

-- 创建索引
CREATE INDEX idx_notes_category ON notes(category_id);
CREATE INDEX idx_notes_created ON notes(created_at);
CREATE INDEX idx_notes_updated ON notes(updated_at);
CREATE INDEX idx_notes_sync ON notes(sync_status, server_id);
CREATE INDEX idx_notes_search ON notes(title, content);  -- 全文搜索索引

-- 分类表
CREATE TABLE categories (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL UNIQUE,
  color TEXT,  -- 分类颜色
  icon TEXT,  -- 分类图标
  sort_order INTEGER DEFAULT 0,
  created_at TEXT DEFAULT (datetime('now'))
);

-- 附件表（图片、文件等）
CREATE TABLE attachments (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  note_id INTEGER NOT NULL,
  file_path TEXT NOT NULL,  -- 本地文件路径
  file_name TEXT NOT NULL,
  file_size INTEGER,  -- 文件大小（字节）
  mime_type TEXT,  -- 文件类型
  sync_status TEXT DEFAULT 'pending',
  server_url TEXT,  -- 服务器端URL
  created_at TEXT DEFAULT (datetime('now')),
  FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE CASCADE
);

-- 同步日志表（记录同步历史）
CREATE TABLE sync_logs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  action TEXT NOT NULL,  -- create, update, delete
  table_name TEXT NOT NULL,  -- notes, attachments
  record_id INTEGER NOT NULL,
  sync_status TEXT,  -- success, failed
  error_message TEXT,
  created_at TEXT DEFAULT (datetime('now'))
);

关键实现：

-- 1. 创建笔记
INSERT INTO notes (title, content, tags, category_id) 
VALUES (
  'SQLite 使用指南',
  'SQLite 是一个轻量级的嵌入式数据库...',
  'database,sqlite,tutorial',
  1
);

-- 2. 查询笔记列表（支持分类、标签、搜索）
-- 按分类查询
SELECT id, title, updated_at, is_pinned
FROM notes
WHERE category_id = 1 
  AND is_deleted = 0
ORDER BY is_pinned DESC, updated_at DESC;

-- 按标签查询
SELECT id, title, tags, updated_at
FROM notes
WHERE tags LIKE '%database%'
  AND is_deleted = 0
ORDER BY updated_at DESC;

-- 全文搜索（使用 LIKE，SQLite 3.25+ 支持 FTS5）
SELECT id, title, content, 
  snippet(notes_fts, 2, '<b>', '</b>', '...', 32) as snippet
FROM notes
JOIN notes_fts ON notes.id = notes_fts.rowid
WHERE notes_fts MATCH 'sqlite database'
ORDER BY rank;

-- 3. 置顶笔记
UPDATE notes 
SET is_pinned = 1, updated_at = datetime('now')
WHERE id = 1;

-- 4. 软删除笔记
UPDATE notes 
SET is_deleted = 1, updated_at = datetime('now')
WHERE id = 1;

-- 5. 统计信息查询
SELECT 
  COUNT(*) as total_notes,
  COUNT(CASE WHEN is_pinned = 1 THEN 1 END) as pinned_notes,
  COUNT(CASE WHEN category_id IS NOT NULL THEN 1 END) as categorized_notes,
  COUNT(CASE WHEN sync_status = 'pending' THEN 1 END) as pending_sync
FROM notes
WHERE is_deleted = 0;

-- 6. 待同步数据查询（用于同步到服务器）
SELECT id, title, content, tags, category_id, server_id, sync_status
FROM notes
WHERE sync_status = 'pending'
ORDER BY updated_at ASC
LIMIT 50;

-- 7. 更新同步状态
UPDATE notes 
SET sync_status = 'synced', 
    server_id = 12345,
    synced_at = datetime('now')
WHERE id = 1;

-- 8. 批量操作（事务保证一致性）
BEGIN TRANSACTION;
-- 删除分类时，将笔记移到未分类
UPDATE notes SET category_id = NULL WHERE category_id = 1;
DELETE FROM categories WHERE id = 1;
COMMIT;

-- 9. 数据备份（导出为 SQL）
.output backup.sql
.dump
.output stdout

-- 10. 数据恢复（从 SQL 文件导入）
.read backup.sql

Python 集成示例：

import sqlite3
from datetime import datetime

class NoteApp:
    def __init__(self, db_path='notes.db'):
        self.conn = sqlite3.connect(db_path)
        self.conn.row_factory = sqlite3.Row
        self.init_db()

    def init_db(self):
        """初始化数据库"""
        cursor = self.conn.cursor()
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS notes (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                content TEXT NOT NULL,
                tags TEXT,
                created_at TEXT DEFAULT (datetime('now')),
                updated_at TEXT DEFAULT (datetime('now'))
            )
        ''')
        self.conn.commit()

    def create_note(self, title, content, tags=''):
        """创建笔记"""
        cursor = self.conn.cursor()
        cursor.execute('''
            INSERT INTO notes (title, content, tags)
            VALUES (?, ?, ?)
        ''', (title, content, tags))
        self.conn.commit()
        return cursor.lastrowid

    def search_notes(self, keyword):
        """搜索笔记"""
        cursor = self.conn.cursor()
        cursor.execute('''
            SELECT * FROM notes
            WHERE title LIKE ? OR content LIKE ?
            ORDER BY updated_at DESC
        ''', (f'%{keyword}%', f'%{keyword}%'))
        return cursor.fetchall()

    def get_pending_sync(self):
        """获取待同步的笔记"""
        cursor = self.conn.cursor()
        cursor.execute('''
            SELECT * FROM notes
            WHERE sync_status = 'pending'
            ORDER BY updated_at ASC
            LIMIT 50
        ''')
        return cursor.fetchall()

性能优化：

-- 1. 启用 WAL 模式（提高并发性能）
PRAGMA journal_mode = WAL;

-- 2. 设置同步模式（平衡性能和数据安全）
PRAGMA synchronous = NORMAL;  -- 或 FULL（更安全）

-- 3. 设置缓存大小（提高查询性能）
PRAGMA cache_size = -64000;  -- 64MB

-- 4. 定期清理和优化
-- 清理已删除的笔记（物理删除）
DELETE FROM notes WHERE is_deleted = 1 AND updated_at < datetime('now', '-30 days');

-- 优化数据库
VACUUM;

-- 5. 分析表统计信息
ANALYZE notes;

同步策略：

-- 1. 标记需要同步的记录
UPDATE notes 
SET sync_status = 'pending'
WHERE updated_at > COALESCE(synced_at, '1970-01-01');

-- 2. 处理冲突（服务器端数据更新）
-- 应用层逻辑：比较 updated_at 时间戳，保留最新的

-- 3. 批量同步
BEGIN TRANSACTION;
-- 同步逻辑...
COMMIT;

项目成果：

支持 10 万+ 笔记存储（单文件数据库）
查询响应时间 < 50ms
离线使用完全正常
数据同步成功率 > 99%
数据库文件大小 < 100MB（包含附件元数据）
零配置，用户无需任何设置

4.4 快速上手

安装与使用：

# macOS（通常已预装）
sqlite3 --version

# Linux 安装
sudo apt-get install sqlite3  # Ubuntu/Debian
sudo yum install sqlite       # CentOS/RHEL

# 创建/打开数据库
sqlite3 demo.db
# 进入 SQLite 命令行，输入 .help 查看帮助

创建数据库和表：

-- SQLite 中数据库就是文件，直接创建表即可
CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT NOT NULL UNIQUE,
  email TEXT NOT NULL,
  password_hash TEXT NOT NULL,
  status TEXT DEFAULT 'active',
  created_at TEXT DEFAULT (datetime('now')),
  updated_at TEXT DEFAULT (datetime('now'))
);

-- 创建索引
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_username ON users(username);

基础 CRUD 操作：

-- 插入数据
INSERT INTO users (username, email, password_hash) 
VALUES ('alice', 'alice@example.com', 'hash123');

INSERT INTO users (username, email, password_hash) 
VALUES 
  ('bob', 'bob@example.com', 'hash456'),
  ('charlie', 'charlie@example.com', 'hash789');

-- 查询数据
SELECT * FROM users;
SELECT id, username, email FROM users WHERE status = 'active';
SELECT * FROM users WHERE email LIKE '%@example.com' ORDER BY created_at DESC LIMIT 10;

-- 更新数据
UPDATE users SET status = 'inactive' WHERE id = 1;
UPDATE users 
SET email = 'newemail@example.com', updated_at = datetime('now') 
WHERE username = 'alice';

-- 删除数据
DELETE FROM users WHERE id = 1;
DELETE FROM users 
WHERE status = 'banned' AND datetime(created_at) < datetime('now', '-1 year');

事务操作：

-- 开启事务
BEGIN TRANSACTION;

-- 执行多个操作
INSERT INTO users (username, email, password_hash) VALUES ('user1', 'user1@example.com', 'hash1');
UPDATE users SET status = 'active' WHERE username = 'user1';

-- 提交事务
COMMIT;

-- 或回滚
ROLLBACK;

实用命令：

-- 查看表结构
.schema users

-- 查看所有表
.tables

-- 导出数据
.mode csv
.output users.csv
SELECT * FROM users;
.output stdout

-- 导入数据
.mode csv
.import users.csv users

-- 退出
.quit

4.4 数据类型

SQLite 使用动态类型系统，但建议使用以下类型：

INTEGER  -- 整数
REAL     -- 浮点数
TEXT     -- 文本字符串
BLOB     -- 二进制数据
NULL     -- NULL 值

注意：SQLite 的类型系统比较灵活，TEXT 可以存储任何文本，INTEGER 可以存储整数。

五、Oracle

5.1 背景介绍

历史与发展：

1977 年由 Larry Ellison 等人创立
第一个商业化的关系型数据库
在企业级市场占据主导地位

市场地位：

大型企业和政府机构的首选
金融、电信、制造业的核心系统
提供完整的商业支持和服务

核心特性：

✅ 强大的性能和可扩展性
✅ 完善的高可用和容灾方案
✅ 丰富的企业级功能
✅ 专业的商业支持
✅ 严格的 ACID 保证

成本考量：

商业许可证费用较高
需要专业的 DBA 团队
硬件和运维成本较高

5.2 作用和使用场景

主要作用：

核心业务系统
- 银行系统：核心银行系统、支付系统
- 保险系统：保单管理、理赔系统
- 证券交易：交易系统、清算系统
- 金融风控：风险控制、合规管理
大型 ERP 系统
- SAP：世界领先的 ERP 系统
- Oracle EBS：Oracle 自己的 ERP 系统
- PeopleSoft：人力资源管理系统
- Siebel：CRM 系统
数据仓库和大数据分析
- 数据仓库：企业级数据仓库
- OLAP：联机分析处理
- BI 系统：商业智能分析
- 大数据平台：结合 Hadoop、Spark
高可用系统
- RAC（Real Application Clusters）：集群高可用
- Data Guard：数据保护和灾难恢复
- GoldenGate：实时数据复制
- 99.99% 可用性：年停机时间 < 1 小时
政府和大型企业
- 政府系统：政务系统、公共服务
- 大型集团：跨国企业、集团公司
- 关键基础设施：电力、交通、通信

适用场景：

✅ 适合：

大型集团、金融、政企核心系统
- 金融行业：银行、保险、证券
- 政府机构：政务系统、公共服务
- 大型企业：跨国企业、集团公司
- 案例：工商银行、建设银行、中国移动
对商业支持、合规、成熟治理体系要求极高
- 合规要求：SOX、PCI-DSS 等合规标准
- 审计要求：完整的审计日志
- 商业支持：7x24 技术支持
- 案例：金融机构、上市公司
已经有 Oracle 生态资产/团队经验的组织
- 历史资产：已有 Oracle 系统
- 团队经验：DBA 团队熟悉 Oracle
- 培训投入：已有 Oracle 培训投入
- 迁移成本：迁移成本过高
需要极高性能和可扩展性的场景
- 高并发：百万级并发连接
- 大数据量：TB 到 PB 级数据
- 复杂查询：复杂业务逻辑
- 案例：大型电商、大型金融系统
对数据安全要求极高的行业
- 数据加密：透明数据加密（TDE）
- 访问控制：细粒度权限控制
- 审计功能：完整的审计追踪
- 案例：金融、医疗、政府
需要高级功能
- 分区表：大数据量分区管理
- 物化视图：预计算复杂查询
- 高级压缩：数据压缩节省空间
- 闪回技术：数据恢复功能

❌ 不适合：

中小型项目
- 成本过高：许可证费用昂贵
- 资源浪费：功能过于强大，用不上
- 建议：使用 MySQL/PostgreSQL
初创公司
- 预算有限：无法承担高昂费用
- 快速迭代：需要快速开发
- 建议：使用开源数据库
简单 Web 应用
- 过度设计：功能过于复杂
- 成本不匹配：成本与需求不匹配
- 建议：使用 MySQL/PostgreSQL
开源优先的项目
- 开源要求：必须使用开源技术
- 成本控制：严格控制成本
- 建议：使用 PostgreSQL
没有专业 DBA 团队
- 运维复杂：需要专业 DBA
- 学习曲线：学习成本高
- 建议：使用 MySQL/PostgreSQL

实际应用案例：

金融行业：工商银行、建设银行、中国银行
电信行业：中国移动、中国联通、中国电信
政府机构：各级政府、公共部门
大型企业：SAP、Oracle EBS 用户
电商平台：部分大型电商平台
制造业：大型制造企业 ERP 系统

成本考量：

许可证费用：按 CPU 核心数收费，价格昂贵
维护费用：年度维护费用（通常为许可证的 20-25%）
硬件成本：通常需要高性能服务器
人力成本：需要专业 DBA 团队
总拥有成本（TCO）：通常远高于开源数据库

5.3 项目案例：银行核心业务系统

项目背景：某大型银行的核心业务系统，需要处理日均千万级交易，支持账户管理、转账、支付、贷款、风控等核心业务。系统要求 99.99% 可用性，严格的数据一致性，完整的审计追踪，以及强大的容灾能力。

技术选型理由：

金融行业对数据一致性要求极高
需要 7x24 小时高可用支持
需要强大的容灾和备份能力
需要完整的审计和合规功能
需要处理海量数据和高并发
需要专业的商业支持

数据库设计：

-- 账户表（分区表，按账户类型分区）
CREATE TABLE accounts (
  account_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  account_no VARCHAR2(32) NOT NULL UNIQUE,
  customer_id NUMBER NOT NULL,
  account_type VARCHAR2(20) NOT NULL,  -- SAVINGS, CHECKING, LOAN
  balance NUMBER(18, 2) DEFAULT 0 NOT NULL,
  currency VARCHAR2(3) DEFAULT 'CNY',
  status VARCHAR2(20) DEFAULT 'ACTIVE',  -- ACTIVE, FROZEN, CLOSED
  open_date DATE DEFAULT SYSDATE,
  close_date DATE,
  created_at TIMESTAMP DEFAULT SYSTIMESTAMP,
  updated_at TIMESTAMP DEFAULT SYSTIMESTAMP
)
PARTITION BY LIST (account_type) (
  PARTITION p_savings VALUES ('SAVINGS'),
  PARTITION p_checking VALUES ('CHECKING'),
  PARTITION p_loan VALUES ('LOAN')
);

-- 创建索引
CREATE INDEX idx_accounts_customer ON accounts(customer_id);
CREATE INDEX idx_accounts_status ON accounts(status);
CREATE UNIQUE INDEX idx_accounts_no ON accounts(account_no);

-- 交易表（按时间范围分区，按月分区）
CREATE TABLE transactions (
  transaction_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  transaction_no VARCHAR2(32) NOT NULL UNIQUE,
  account_id NUMBER NOT NULL,
  transaction_type VARCHAR2(20) NOT NULL,  -- DEPOSIT, WITHDRAW, TRANSFER, PAYMENT
  amount NUMBER(18, 2) NOT NULL,
  balance_after NUMBER(18, 2) NOT NULL,  -- 交易后余额
  counterparty_account VARCHAR2(32),  -- 对方账户（转账用）
  description VARCHAR2(255),
  status VARCHAR2(20) DEFAULT 'PENDING',  -- PENDING, SUCCESS, FAILED, CANCELLED
  transaction_date TIMESTAMP DEFAULT SYSTIMESTAMP,
  created_at TIMESTAMP DEFAULT SYSTIMESTAMP,
  FOREIGN KEY (account_id) REFERENCES accounts(account_id)
)
PARTITION BY RANGE (transaction_date) (
  PARTITION p202401 VALUES LESS THAN (TO_DATE('2024-02-01', 'YYYY-MM-DD')),
  PARTITION p202402 VALUES LESS THAN (TO_DATE('2024-03-01', 'YYYY-MM-DD')),
  PARTITION p202403 VALUES LESS THAN (TO_DATE('2024-04-01', 'YYYY-MM-DD'))
  -- 按月自动添加分区
);

-- 创建索引
CREATE INDEX idx_transactions_account ON transactions(account_id, transaction_date);
CREATE INDEX idx_transactions_type ON transactions(transaction_type, transaction_date);
CREATE INDEX idx_transactions_status ON transactions(status);

-- 客户表
CREATE TABLE customers (
  customer_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  customer_no VARCHAR2(32) NOT NULL UNIQUE,
  name VARCHAR2(100) NOT NULL,
  id_type VARCHAR2(20),  -- ID_CARD, PASSPORT
  id_number VARCHAR2(50),
  phone VARCHAR2(20),
  email VARCHAR2(128),
  address VARCHAR2(255),
  status VARCHAR2(20) DEFAULT 'ACTIVE',
  risk_level VARCHAR2(20) DEFAULT 'LOW',  -- LOW, MEDIUM, HIGH
  created_at TIMESTAMP DEFAULT SYSTIMESTAMP,
  updated_at TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- 风控规则表
CREATE TABLE risk_rules (
  rule_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  rule_name VARCHAR2(100) NOT NULL,
  rule_type VARCHAR2(50),  -- AMOUNT_LIMIT, FREQUENCY_LIMIT, TIME_LIMIT
  rule_config CLOB,  -- JSON 格式的规则配置
  is_active NUMBER(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- 审计日志表（记录所有关键操作）
CREATE TABLE audit_logs (
  log_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  table_name VARCHAR2(100) NOT NULL,
  operation VARCHAR2(20) NOT NULL,  -- INSERT, UPDATE, DELETE
  record_id NUMBER,
  old_values CLOB,  -- 旧值（JSON 格式）
  new_values CLOB,  -- 新值（JSON 格式）
  user_id VARCHAR2(50),
  ip_address VARCHAR2(50),
  operation_time TIMESTAMP DEFAULT SYSTIMESTAMP
)
PARTITION BY RANGE (operation_time) (
  PARTITION p202401 VALUES LESS THAN (TO_DATE('2024-02-01', 'YYYY-MM-DD'))
  -- 按月分区
);

关键实现：

-- 1. 开户（事务保证一致性）
BEGIN
  -- 创建账户
  INSERT INTO accounts (account_no, customer_id, account_type, balance)
  VALUES ('ACC' || TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISSFF'), 1, 'SAVINGS', 0)
  RETURNING account_id INTO :new_account_id;

  -- 记录审计日志
  INSERT INTO audit_logs (table_name, operation, record_id, new_values, user_id)
  VALUES ('ACCOUNTS', 'INSERT', :new_account_id, 
    '{"account_no": "' || :account_no || '"}', 'SYSTEM');

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;
/

-- 2. 转账（关键业务逻辑，需要严格的事务控制）
CREATE OR REPLACE PROCEDURE transfer_money(
  p_from_account VARCHAR2,
  p_to_account VARCHAR2,
  p_amount NUMBER,
  p_result OUT NUMBER
) AS
  v_from_balance NUMBER;
  v_to_balance NUMBER;
BEGIN
  -- 锁定账户（防止并发问题）
  SELECT balance INTO v_from_balance
  FROM accounts
  WHERE account_no = p_from_account
  FOR UPDATE;

  -- 检查余额
  IF v_from_balance < p_amount THEN
    p_result := -1;  -- 余额不足
    RETURN;
  END IF;

  -- 扣减转出账户
  UPDATE accounts
  SET balance = balance - p_amount,
      updated_at = SYSTIMESTAMP
  WHERE account_no = p_from_account;

  -- 增加转入账户
  UPDATE accounts
  SET balance = balance + p_amount,
      updated_at = SYSTIMESTAMP
  WHERE account_no = p_to_account;

  -- 记录交易
  INSERT INTO transactions (transaction_no, account_id, transaction_type, amount, balance_after, counterparty_account, status)
  VALUES (
    'TXN' || TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISSFF'),
    (SELECT account_id FROM accounts WHERE account_no = p_from_account),
    'TRANSFER',
    -p_amount,
    v_from_balance - p_amount,
    p_to_account,
    'SUCCESS'
  );

  COMMIT;
  p_result := 0;  -- 成功

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    p_result := -2;  -- 系统错误
    RAISE;
END;
/

-- 3. 查询账户交易历史（使用窗口函数）
SELECT 
  transaction_no,
  transaction_type,
  amount,
  balance_after,
  transaction_date,
  ROW_NUMBER() OVER (ORDER BY transaction_date DESC) as rn,
  LAG(balance_after) OVER (ORDER BY transaction_date) as prev_balance
FROM transactions
WHERE account_id = 1
  AND transaction_date >= SYSTIMESTAMP - INTERVAL '30' DAY
ORDER BY transaction_date DESC
FETCH FIRST 50 ROWS ONLY;

-- 4. 风控检查（使用物化视图预计算）
CREATE MATERIALIZED VIEW daily_transaction_summary
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT 
  account_id,
  TRUNC(transaction_date) as trans_date,
  COUNT(*) as trans_count,
  SUM(ABS(amount)) as total_amount,
  MAX(ABS(amount)) as max_amount
FROM transactions
WHERE status = 'SUCCESS'
GROUP BY account_id, TRUNC(transaction_date);

-- 风控查询
SELECT a.account_no, dts.trans_count, dts.total_amount
FROM accounts a
JOIN daily_transaction_summary dts ON a.account_id = dts.account_id
WHERE dts.trans_date = TRUNC(SYSDATE)
  AND (dts.trans_count > 100 OR dts.total_amount > 100000);

-- 5. 数据闪回（恢复误操作）
-- 查看 1 小时前的数据
SELECT * FROM accounts
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR)
WHERE account_id = 1;

-- 恢复数据
UPDATE accounts
SET balance = (
  SELECT balance 
  FROM accounts
  AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR)
  WHERE account_id = 1
)
WHERE account_id = 1;

-- 6. 审计查询（合规要求）
SELECT 
  table_name,
  operation,
  user_id,
  operation_time,
  old_values,
  new_values
FROM audit_logs
WHERE table_name = 'ACCOUNTS'
  AND operation_time >= SYSTIMESTAMP - INTERVAL '7' DAY
ORDER BY operation_time DESC;

-- 7. 数据压缩（节省存储空间）
ALTER TABLE transactions COMPRESS FOR OLTP;

-- 8. 统计信息收集（优化查询性能）
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'BANK_SCHEMA',
    tabname => 'TRANSACTIONS',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
  );
END;
/

高可用配置：

-- 1. RAC（Real Application Clusters）配置
-- 多节点集群，实现高可用和负载均衡

-- 2. Data Guard 配置
-- 主备复制，实现容灾

-- 3. 闪回数据库
ALTER DATABASE FLASHBACK ON;

-- 4. 自动备份
-- 使用 RMAN（Recovery Manager）进行自动备份

性能优化：

-- 1. 分区表维护（自动添加新分区）
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'ADD_MONTHLY_PARTITION',
    job_type => 'PLSQL_BLOCK',
    job_action => 'BEGIN add_monthly_partition; END;',
    start_date => SYSTIMESTAMP,
    repeat_interval => 'FREQ=MONTHLY; BYMONTHDAY=1',
    enabled => TRUE
  );
END;
/

-- 2. 索引维护
-- 定期重建索引
ALTER INDEX idx_transactions_account REBUILD;

-- 3. 查询优化（使用提示）
SELECT /*+ INDEX(transactions, idx_transactions_account) */
  *
FROM transactions
WHERE account_id = 1;

项目成果：

支持日均 1000 万+ 交易处理
系统可用性 99.99%（年停机时间 < 1 小时）
交易响应时间 < 100ms（95% 分位）
数据量 PB 级，性能稳定
完整的审计追踪，满足合规要求
RAC + Data Guard 实现高可用和容灾
7x24 小时专业支持

5.4 快速上手

连接数据库：

# 使用 sqlplus 连接
sqlplus username/password@host:port/service_name

# 或使用 SQL Developer（图形化工具）
# 下载地址：https://www.oracle.com/database/sqldeveloper/

创建表：

-- 创建用户表
CREATE TABLE users (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  username VARCHAR2(64) NOT NULL UNIQUE,
  email VARCHAR2(128) NOT NULL,
  password_hash VARCHAR2(255) NOT NULL,
  status VARCHAR2(20) DEFAULT 'active',
  created_at TIMESTAMP DEFAULT SYSTIMESTAMP,
  updated_at TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- 创建索引
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_username ON users(username);

基础 CRUD 操作：

-- 插入数据
INSERT INTO users (username, email, password_hash) 
VALUES ('alice', 'alice@example.com', 'hash123');

INSERT ALL
  INTO users (username, email, password_hash) VALUES ('bob', 'bob@example.com', 'hash456')
  INTO users (username, email, password_hash) VALUES ('charlie', 'charlie@example.com', 'hash789')
SELECT * FROM dual;

-- 查询数据
SELECT * FROM users;
SELECT id, username, email FROM users WHERE status = 'active';
SELECT * FROM users
WHERE email LIKE '%@example.com' 
ORDER BY created_at DESC 
FETCH FIRST 10 ROWS ONLY;

-- 更新数据
UPDATE users SET status = 'inactive' WHERE id = 1;
UPDATE users 
SET email = 'newemail@example.com', updated_at = SYSTIMESTAMP 
WHERE username = 'alice';

-- 删除数据
DELETE FROM users WHERE id = 1;
DELETE FROM users 
WHERE status = 'banned' AND created_at < ADD_MONTHS(SYSTIMESTAMP, -12);

事务操作：

-- Oracle 默认自动提交关闭，需要显式提交
-- 开启事务（隐式）
INSERT INTO users (username, email, password_hash) VALUES ('user1', 'user1@example.com', 'hash1');
UPDATE users SET status = 'active' WHERE username = 'user1';

-- 提交事务
COMMIT;

-- 或回滚
ROLLBACK;

5.4 数据类型

数值类型：

NUMBER(p,s)  -- 精确数值，p 总位数，s 小数位
INTEGER      -- 整数
FLOAT        -- 浮点数

字符串类型：

VARCHAR2(n)  -- 可变长度字符串，最大 4000 字节
CHAR(n)      -- 固定长度字符串
CLOB         -- 大文本对象

日期时间类型：

DATE         -- 日期和时间
TIMESTAMP    -- 时间戳，精度更高
TIMESTAMP WITH TIME ZONE  -- 带时区的时间戳

六、CRUD 语法对比

6.1 创建表（CREATE TABLE）

操作	MySQL	PostgreSQL	SQLite	Oracle
自增主键	`AUTO_INCREMENT`	`SERIAL` 或 `BIGSERIAL`	`AUTOINCREMENT`	`GENERATED BY DEFAULT AS IDENTITY`
字符串类型	`VARCHAR(n)`	`VARCHAR(n)` 或 `TEXT`	`TEXT`	`VARCHAR2(n)`
时间戳	`TIMESTAMP`	`TIMESTAMPTZ`	`TEXT` 或 `DATETIME`	`TIMESTAMP`
JSON 支持	`JSON` (5.7+)	`JSONB`	无原生支持	`JSON` (12c+)

示例对比：

-- MySQL
CREATE TABLE users (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(64) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- PostgreSQL
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(64) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- SQLite
CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  created_at TEXT DEFAULT (datetime('now'))
);

-- Oracle
CREATE TABLE users (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR2(64) NOT NULL,
  created_at TIMESTAMP DEFAULT SYSTIMESTAMP
);

6.2 插入数据（INSERT）

特性	MySQL	PostgreSQL	SQLite	Oracle
单条插入	✅ 相同	✅ 相同	✅ 相同	✅ 相同
批量插入	`VALUES (...), (...)`	`VALUES (...), (...)`	`VALUES (...), (...)`	`INSERT ALL ... SELECT`
返回 ID	`LAST_INSERT_ID()`	`RETURNING id`	`last_insert_rowid()`	`RETURNING id INTO`

示例对比：

-- MySQL
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (name, email) VALUES 
  ('Bob', 'bob@example.com'),
  ('Charlie', 'charlie@example.com');

-- PostgreSQL
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (name, email) VALUES 
  ('Bob', 'bob@example.com'),
  ('Charlie', 'charlie@example.com');
-- 返回插入的 ID
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com') RETURNING id;

-- SQLite
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (name, email) VALUES 
  ('Bob', 'bob@example.com'),
  ('Charlie', 'charlie@example.com');

-- Oracle
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT ALL
  INTO users (name, email) VALUES ('Bob', 'bob@example.com')
  INTO users (name, email) VALUES ('Charlie', 'charlie@example.com')
SELECT * FROM dual;

6.3 查询数据（SELECT）

特性	MySQL	PostgreSQL	SQLite	Oracle
基本查询	✅ 相同	✅ 相同	✅ 相同	✅ 相同
分页	`LIMIT n OFFSET m`	`LIMIT n OFFSET m`	`LIMIT n OFFSET m`	`FETCH FIRST n ROWS ONLY OFFSET m`
字符串函数	`CONCAT()`	`\	\	`或`CONCAT()`	`\	\	`	`\	\	`或`CONCAT()`
日期函数	`NOW()`, `DATE()`	`now()`, `CURRENT_DATE`	`datetime('now')`	`SYSTIMESTAMP`, `SYSDATE`

示例对比：

-- MySQL
SELECT * FROM users WHERE name = 'Alice';
SELECT * FROM users ORDER BY created_at DESC LIMIT 10 OFFSET 20;
SELECT CONCAT(name, ' - ', email) as info FROM users;

-- PostgreSQL
SELECT * FROM users WHERE name = 'Alice';
SELECT * FROM users ORDER BY created_at DESC LIMIT 10 OFFSET 20;
SELECT name || ' - ' || email as info FROM users;

-- SQLite
SELECT * FROM users WHERE name = 'Alice';
SELECT * FROM users ORDER BY created_at DESC LIMIT 10 OFFSET 20;
SELECT name || ' - ' || email as info FROM users;

-- Oracle
SELECT * FROM users WHERE name = 'Alice';
SELECT * FROM users 
ORDER BY created_at DESC 
FETCH FIRST 10 ROWS ONLY OFFSET 20;
SELECT name || ' - ' || email as info FROM users;

6.4 更新数据（UPDATE）

特性	MySQL	PostgreSQL	SQLite	Oracle
基本更新	✅ 相同	✅ 相同	✅ 相同	✅ 相同
返回更新行	❌	`RETURNING *`	❌	`RETURNING *`
自动更新时间戳	`ON UPDATE CURRENT_TIMESTAMP`	需要触发器	需要触发器	需要触发器

示例对比：

-- MySQL
UPDATE users SET email = 'new@example.com' WHERE id = 1;
UPDATE users SET email = 'new@example.com', updated_at = NOW() WHERE id = 1;

-- PostgreSQL
UPDATE users SET email = 'new@example.com' WHERE id = 1;
UPDATE users SET email = 'new@example.com', updated_at = now() WHERE id = 1;
-- 返回更新的行
UPDATE users SET email = 'new@example.com' WHERE id = 1 RETURNING *;

-- SQLite
UPDATE users SET email = 'new@example.com' WHERE id = 1;
UPDATE users SET email = 'new@example.com', updated_at = datetime('now') WHERE id = 1;

-- Oracle
UPDATE users SET email = 'new@example.com' WHERE id = 1;
UPDATE users SET email = 'new@example.com', updated_at = SYSTIMESTAMP WHERE id = 1;
-- 返回更新的行
UPDATE users SET email = 'new@example.com' WHERE id = 1 RETURNING *;

6.5 删除数据（DELETE）

特性	MySQL	PostgreSQL	SQLite	Oracle
基本删除	✅ 相同	✅ 相同	✅ 相同	✅ 相同
返回删除行	❌	`RETURNING *`	❌	`RETURNING *`
级联删除	外键约束	外键约束	外键约束	外键约束

示例对比：

-- MySQL
DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE status = 'inactive' AND created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);

-- PostgreSQL
DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE status = 'inactive' AND created_at < now() - INTERVAL '1 year';
-- 返回删除的行
DELETE FROM users WHERE id = 1 RETURNING *;

-- SQLite
DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE status = 'inactive' AND datetime(created_at) < datetime('now', '-1 year');

-- Oracle
DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE status = 'inactive' AND created_at < ADD_MONTHS(SYSTIMESTAMP, -12);
-- 返回删除的行
DELETE FROM users WHERE id = 1 RETURNING *;

6.6 事务操作

特性	MySQL	PostgreSQL	SQLite	Oracle
开启事务	`START TRANSACTION` 或 `BEGIN`	`BEGIN`	`BEGIN TRANSACTION`	隐式（需要 `COMMIT`）
提交事务	`COMMIT`	`COMMIT`	`COMMIT`	`COMMIT`
回滚事务	`ROLLBACK`	`ROLLBACK`	`ROLLBACK`	`ROLLBACK`
保存点	`SAVEPOINT name`	`SAVEPOINT name`	`SAVEPOINT name`	`SAVEPOINT name`

示例对比：

-- MySQL
START TRANSACTION;
INSERT INTO users (name, email) VALUES ('user1', 'user1@example.com');
UPDATE users SET status = 'active' WHERE name = 'user1';
COMMIT;
-- 或 ROLLBACK;

-- PostgreSQL
BEGIN;
INSERT INTO users (name, email) VALUES ('user1', 'user1@example.com');
UPDATE users SET status = 'active' WHERE name = 'user1';
COMMIT;
-- 或 ROLLBACK;

-- SQLite
BEGIN TRANSACTION;
INSERT INTO users (name, email) VALUES ('user1', 'user1@example.com');
UPDATE users SET status = 'active' WHERE name = 'user1';
COMMIT;
-- 或 ROLLBACK;

-- Oracle
-- Oracle 默认不自动提交，需要显式提交
INSERT INTO users (name, email) VALUES ('user1', 'user1@example.com');
UPDATE users SET status = 'active' WHERE name = 'user1';
COMMIT;
-- 或 ROLLBACK;

6.7 JOIN 操作对比

场景：用户和订单关联查询

-- ========== MySQL ==========
-- 内连接
SELECT u.username, o.order_id, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 左连接
SELECT u.username, COALESCE(SUM(o.amount), 0) as total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

-- 右连接
SELECT u.username, o.order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- ========== PostgreSQL ==========
-- 内连接（语法相同）
SELECT u.username, o.order_id, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 左连接
SELECT u.username, COALESCE(SUM(o.amount), 0) as total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

-- 全外连接（PostgreSQL 支持）
SELECT u.username, o.order_id
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

-- ========== SQLite ==========
-- 内连接（语法相同）
SELECT u.username, o.order_id, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 左连接
SELECT u.username, COALESCE(SUM(o.amount), 0) as total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

-- 注意：SQLite 不支持 RIGHT JOIN 和 FULL OUTER JOIN

-- ========== Oracle ==========
-- 内连接（语法相同）
SELECT u.username, o.order_id, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 左连接
SELECT u.username, NVL(SUM(o.amount), 0) as total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

-- 全外连接（Oracle 支持）
SELECT u.username, o.order_id
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

6.8 聚合函数对比

函数	MySQL	PostgreSQL	SQLite	Oracle
计数	`COUNT(*)`	`COUNT(*)`	`COUNT(*)`	`COUNT(*)`
求和	`SUM(column)`	`SUM(column)`	`SUM(column)`	`SUM(column)`
平均值	`AVG(column)`	`AVG(column)`	`AVG(column)`	`AVG(column)`
最大值	`MAX(column)`	`MAX(column)`	`MAX(column)`	`MAX(column)`
最小值	`MIN(column)`	`MIN(column)`	`MIN(column)`	`MIN(column)`
字符串连接	`GROUP_CONCAT()`	`STRING_AGG()`	`GROUP_CONCAT()`	`LISTAGG()`

示例对比：

-- ========== MySQL ==========
SELECT 
  status,
  COUNT(*) as user_count,
  AVG(age) as avg_age,
  GROUP_CONCAT(username SEPARATOR ', ') as usernames
FROM users
GROUP BY status;

-- ========== PostgreSQL ==========
SELECT 
  status,
  COUNT(*) as user_count,
  AVG(age) as avg_age,
  STRING_AGG(username, ', ') as usernames
FROM users
GROUP BY status;

-- ========== SQLite ==========
SELECT 
  status,
  COUNT(*) as user_count,
  AVG(age) as avg_age,
  GROUP_CONCAT(username, ', ') as usernames
FROM users
GROUP BY status;

-- ========== Oracle ==========
SELECT 
  status,
  COUNT(*) as user_count,
  AVG(age) as avg_age,
  LISTAGG(username, ', ') WITHIN GROUP (ORDER BY username) as usernames
FROM users
GROUP BY status;

6.9 子查询对比

场景：查找没有订单的用户

-- ========== MySQL ==========
-- 使用 NOT EXISTS
SELECT * FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- 使用 NOT IN
SELECT * FROM users
WHERE id NOT IN (SELECT DISTINCT user_id FROM orders WHERE user_id IS NOT NULL);

-- ========== PostgreSQL ==========
-- 使用 NOT EXISTS（推荐）
SELECT * FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- 使用 NOT IN
SELECT * FROM users
WHERE id NOT IN (SELECT DISTINCT user_id FROM orders WHERE user_id IS NOT NULL);

-- 使用 EXCEPT（PostgreSQL 特有）
SELECT id FROM users
EXCEPT
SELECT DISTINCT user_id FROM orders WHERE user_id IS NOT NULL;

-- ========== SQLite ==========
-- 使用 NOT EXISTS
SELECT * FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- 使用 NOT IN
SELECT * FROM users
WHERE id NOT IN (SELECT DISTINCT user_id FROM orders WHERE user_id IS NOT NULL);

-- ========== Oracle ==========
-- 使用 NOT EXISTS
SELECT * FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- 使用 NOT IN
SELECT * FROM users
WHERE id NOT IN (SELECT DISTINCT user_id FROM orders WHERE user_id IS NOT NULL);

-- 使用 MINUS（Oracle 特有）
SELECT id FROM users
MINUS
SELECT DISTINCT user_id FROM orders WHERE user_id IS NOT NULL;

6.10 窗口函数对比

特性	MySQL	PostgreSQL	SQLite	Oracle
ROW_NUMBER()	8.0+	✅	3.25+	✅
RANK()	8.0+	✅	3.25+	✅
DENSE_RANK()	8.0+	✅	3.25+	✅
LAG()/LEAD()	8.0+	✅	3.25+	✅
SUM() OVER()	8.0+	✅	3.25+	✅

示例对比：

-- ========== MySQL (8.0+) ==========
SELECT 
  username,
  amount,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) as rank,
  SUM(amount) OVER (PARTITION BY user_id) as total_amount
FROM orders;

-- ========== PostgreSQL ==========
SELECT 
  username,
  amount,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) as rank,
  SUM(amount) OVER (PARTITION BY user_id) as total_amount,
  LAG(amount) OVER (ORDER BY created_at) as prev_amount
FROM orders;

-- ========== SQLite (3.25+) ==========
SELECT 
  username,
  amount,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) as rank,
  SUM(amount) OVER (PARTITION BY user_id) as total_amount
FROM orders;

-- ========== Oracle ==========
SELECT 
  username,
  amount,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) as rank,
  SUM(amount) OVER (PARTITION BY user_id) as total_amount,
  LAG(amount) OVER (ORDER BY created_at) as prev_amount
FROM orders;

6.11 完整业务场景示例对比

场景：电商系统 - 用户、商品、订单管理

-- ========== MySQL ==========
-- 1. 创建表结构
CREATE TABLE users (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(64) NOT NULL UNIQUE,
  email VARCHAR(128) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  stock INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  total_amount DECIMAL(10, 2) NOT NULL,
  status ENUM('pending', 'paid', 'shipped', 'completed') DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE order_items (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_id BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  quantity INT NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

-- 2. 插入数据
INSERT INTO users (username, email) VALUES 
  ('alice', 'alice@example.com'),
  ('bob', 'bob@example.com');

INSERT INTO products (name, price, stock) VALUES
  ('Laptop', 999.99, 10),
  ('Mouse', 29.99, 100);

-- 3. 创建订单（事务）
START TRANSACTION;
INSERT INTO orders (user_id, total_amount) VALUES (1, 1029.98);
SET @order_id = LAST_INSERT_ID();
INSERT INTO order_items (order_id, product_id, quantity, price) VALUES
  (@order_id, 1, 1, 999.99),
  (@order_id, 2, 1, 29.99);
COMMIT;

-- 4. 复杂查询：用户订单统计
SELECT 
  u.username,
  COUNT(o.id) as order_count,
  COALESCE(SUM(o.total_amount), 0) as total_spent,
  AVG(o.total_amount) as avg_order_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed' OR o.id IS NULL
GROUP BY u.id, u.username
ORDER BY total_spent DESC;

-- 5. 子查询：查找购买过特定商品的用户
SELECT DISTINCT u.username
FROM users u
WHERE u.id IN (
  SELECT o.user_id 
  FROM orders o
  INNER JOIN order_items oi ON o.id = oi.order_id
  WHERE oi.product_id = 1
);

-- ========== PostgreSQL ==========
-- 1. 创建表结构（基本相同，但使用不同的数据类型）
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  username VARCHAR(64) NOT NULL UNIQUE,
  email VARCHAR(128) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE products (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  price NUMERIC(10, 2) NOT NULL,
  stock INTEGER DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  total_amount NUMERIC(10, 2) NOT NULL,
  status VARCHAR(20) DEFAULT 'pending',
  created_at TIMESTAMPTZ DEFAULT now(),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE order_items (
  id BIGSERIAL PRIMARY KEY,
  order_id BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  quantity INTEGER NOT NULL,
  price NUMERIC(10, 2) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

-- 2. 插入数据（相同）
INSERT INTO users (username, email) VALUES 
  ('alice', 'alice@example.com'),
  ('bob', 'bob@example.com');

INSERT INTO products (name, price, stock) VALUES
  ('Laptop', 999.99, 10),
  ('Mouse', 29.99, 100);

-- 3. 创建订单（使用 RETURNING）
BEGIN;
INSERT INTO orders (user_id, total_amount) VALUES (1, 1029.98) RETURNING id INTO order_id_var;
INSERT INTO order_items (order_id, product_id, quantity, price) VALUES
  (order_id_var, 1, 1, 999.99),
  (order_id_var, 2, 1, 29.99);
COMMIT;

-- 4. 复杂查询（使用窗口函数）
SELECT 
  u.username,
  COUNT(o.id) as order_count,
  COALESCE(SUM(o.total_amount), 0) as total_spent,
  AVG(o.total_amount) as avg_order_amount,
  ROW_NUMBER() OVER (ORDER BY SUM(o.total_amount) DESC) as rank
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed' OR o.id IS NULL
GROUP BY u.id, u.username
ORDER BY total_spent DESC;

-- 5. 使用 EXISTS（性能更好）
SELECT DISTINCT u.username
FROM users u
WHERE EXISTS (
  SELECT 1 
  FROM orders o
  INNER JOIN order_items oi ON o.id = oi.order_id
  WHERE o.user_id = u.id AND oi.product_id = 1
);

-- ========== SQLite ==========
-- 1. 创建表结构
CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT NOT NULL UNIQUE,
  email TEXT NOT NULL,
  created_at TEXT DEFAULT (datetime('now'))
);

CREATE TABLE products (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  price REAL NOT NULL,
  stock INTEGER DEFAULT 0,
  created_at TEXT DEFAULT (datetime('now'))
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  total_amount REAL NOT NULL,
  status TEXT DEFAULT 'pending',
  created_at TEXT DEFAULT (datetime('now')),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE order_items (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  order_id INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  quantity INTEGER NOT NULL,
  price REAL NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

-- 2-5. 其他操作语法类似，但注意数据类型使用 TEXT 和 REAL

-- ========== Oracle ==========
-- 1. 创建表结构
CREATE TABLE users (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  username VARCHAR2(64) NOT NULL UNIQUE,
  email VARCHAR2(128) NOT NULL,
  created_at TIMESTAMP DEFAULT SYSTIMESTAMP
);

CREATE TABLE products (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR2(255) NOT NULL,
  price NUMBER(10, 2) NOT NULL,
  stock NUMBER DEFAULT 0,
  created_at TIMESTAMP DEFAULT SYSTIMESTAMP
);

CREATE TABLE orders (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  user_id NUMBER NOT NULL,
  total_amount NUMBER(10, 2) NOT NULL,
  status VARCHAR2(20) DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT SYSTIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE order_items (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  order_id NUMBER NOT NULL,
  product_id NUMBER NOT NULL,
  quantity NUMBER NOT NULL,
  price NUMBER(10, 2) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

-- 2-5. 其他操作语法类似，注意使用 NUMBER 类型和 FETCH FIRST 分页

七、选型指南

7.1 快速选型决策表

场景	推荐数据库	理由
Web 应用（中小型）	MySQL	生态成熟、团队熟悉、性能好
企业级系统	PostgreSQL	功能强大、标准 SQL、扩展性强
复杂业务/报表	PostgreSQL	查询优化器强、支持复杂类型
Odoo/GitLab	PostgreSQL	官方推荐
移动应用本地存储	SQLite	轻量级、零配置
桌面软件	SQLite	单文件、易部署
IoT 设备	SQLite	资源占用小
测试/原型	SQLite	快速搭建、无需配置
金融/政企核心	Oracle	商业支持、高可用
大型集团系统	Oracle	成熟方案、专业支持
预算有限	MySQL/PostgreSQL	开源免费
需要 JSON 支持	PostgreSQL	JSONB 性能优秀
需要地理信息	PostgreSQL + PostGIS	最佳选择

7.2 详细对比矩阵

特性	MySQL	PostgreSQL	SQLite	Oracle
许可证	GPL/商业	PostgreSQL License	Public Domain	商业
成本	免费	免费	免费	昂贵
性能	⭐⭐⭐⭐	⭐⭐⭐⭐⭐	⭐⭐⭐	⭐⭐⭐⭐⭐
易用性	⭐⭐⭐⭐⭐	⭐⭐⭐⭐	⭐⭐⭐⭐⭐	⭐⭐⭐
功能丰富度	⭐⭐⭐⭐	⭐⭐⭐⭐⭐	⭐⭐	⭐⭐⭐⭐⭐
扩展性	⭐⭐⭐⭐	⭐⭐⭐⭐⭐	⭐	⭐⭐⭐⭐⭐
并发性能	⭐⭐⭐⭐	⭐⭐⭐⭐⭐	⭐⭐	⭐⭐⭐⭐⭐
社区支持	⭐⭐⭐⭐⭐	⭐⭐⭐⭐	⭐⭐⭐⭐	⭐⭐⭐
商业支持	⭐⭐⭐⭐	⭐⭐⭐	⭐	⭐⭐⭐⭐⭐
学习曲线	平缓	中等	平缓	陡峭

7.3 使用建议

MySQL

适合场景：

✅ "快、稳、通用"的业务系统
✅ 中小规模 Web 应用
✅ 团队熟悉 MySQL

重点关注：

索引设计（避免全表扫描）
慢 SQL 优化（使用 EXPLAIN）
事务边界设计
主从复制配置（高可用）

PostgreSQL

适合场景：

✅ "复杂业务模型 + 强查询 + 可扩展能力"
✅ 企业级应用
✅ 需要 JSON/数组等复杂类型

重点关注：

合理使用 JSONB（替代 NoSQL 场景）
GIN/BTREE 索引组合
查询计划分析（EXPLAIN ANALYZE）
扩展插件（PostGIS、全文检索等）

SQLite

适合场景：

✅ "单机/轻量/边缘侧"
✅ 移动应用、桌面软件
✅ 测试和原型开发

重点关注：

并发写限制（不适合高并发写入）
WAL 模式优化（Write-Ahead Logging）
数据库文件备份
避免在 Web 应用后端使用（除非是只读场景）

Oracle

适合场景：

✅ "超大规模、强治理、商业支持要求高"
✅ 金融、政企核心系统
✅ 已有 Oracle 生态

重点关注：

成本控制（许可证费用）
版本升级策略
专业 DBA 团队
高可用架构（RAC、Data Guard）

7.4 迁移建议

从 MySQL 迁移到 PostgreSQL：

语法差异较小，主要是数据类型和函数
可以使用工具自动迁移（如 pgloader）
注意 JSON 类型的使用（MySQL 的 JSON vs PostgreSQL 的 JSONB）

从 SQLite 迁移到 MySQL/PostgreSQL：

表结构基本兼容
数据类型需要调整（TEXT → VARCHAR）
需要处理并发和性能问题

从 Oracle 迁移到 PostgreSQL：

PostgreSQL 被称为"开源的 Oracle"
语法相似度高
可以使用 ora2pg 工具辅助迁移

总结

选择合适的数据库需要综合考虑：

项目规模：小型用 SQLite，中型用 MySQL/PostgreSQL，大型考虑 Oracle
团队经验：选择团队熟悉的数据库
功能需求：复杂查询选 PostgreSQL，简单 CRUD 选 MySQL
成本预算：开源优先，商业场景考虑 Oracle
性能要求：高并发选 PostgreSQL/Oracle，轻量级选 SQLite

推荐组合：

Web 应用：MySQL + Redis（缓存）
企业系统：PostgreSQL + 扩展插件
移动应用：SQLite（本地）+ 后端 API（MySQL/PostgreSQL）
大型企业：Oracle（核心）+ MySQL/PostgreSQL（辅助系统）

希望这份指南能帮助你选择最适合的数据库方案！

关系型数据库完全指南：MySQL、PostgreSQL、SQLite、Oracle

目录

一、数据库背景介绍

1.1 数据库发展简史

1.2 什么是关系型数据库

1.3 为什么需要数据库

1.4 数据库的应用场景

1.5 四种数据库的定位

二、MySQL

2.1 背景介绍

2.2 作用和使用场景

2.3 项目案例：中小型电商平台

2.4 快速上手

2.4 数据类型

2.5 性能优化

三、PostgreSQL

3.1 背景介绍

3.2 作用和使用场景

3.3 项目案例：企业级内容管理系统（CMS）

3.4 快速上手

3.4 高级特性

四、SQLite

4.1 背景介绍

4.2 作用和使用场景

4.3 项目案例：移动端离线笔记应用

4.4 快速上手

4.4 数据类型

五、Oracle

5.1 背景介绍

5.2 作用和使用场景

5.3 项目案例：银行核心业务系统

5.4 快速上手

5.4 数据类型

六、CRUD 语法对比

6.1 创建表（CREATE TABLE）

6.2 插入数据（INSERT）

6.3 查询数据（SELECT）

6.4 更新数据（UPDATE）

6.5 删除数据（DELETE）

6.6 事务操作

6.7 JOIN 操作对比

6.8 聚合函数对比

6.9 子查询对比

6.10 窗口函数对比

6.11 完整业务场景示例对比

七、选型指南

7.1 快速选型决策表

7.2 详细对比矩阵

7.3 使用建议

MySQL

PostgreSQL

SQLite

Oracle

7.4 迁移建议

总结