关系数据库基础

1.1 数据库是什么

在没有数据库之前，应用程序把数据直接存储为文本文件或 CSV 文件。这种方式在数据量小时尚可接受，但一旦数据量增长、多个程序同时访问、需要快速检索特定记录时，问题就会接踵而至。

DBMS — 数据库管理系统

DBMS（Database Management System，数据库管理系统） 是管理数据库的软件层。我们日常说的 MySQL、PostgreSQL、SQLite 本质上都是 DBMS。它负责：

• 解析并执行 SQL 语句
• 管理磁盘上的数据文件和内存缓存
• 事务处理与并发控制（锁、MVCC）
• 用户认证与权限管理
• 数据备份与恢复

1.2 关系模型的诞生

1970 年，IBM 研究员 E.F. Codd 发表论文《A Relational Model of Data for Large Shared Data Banks》，提出了关系模型。他用数学中的"集合论"和"一阶谓词逻辑"为数据库奠定了严格的理论基础。

关系模型的核心思想是：将数据组织成二维表格（关系），通过表之间的关联（外键）描述现实世界的联系，并用声明式的查询语言（SQL）操作数据，而不是命令式地描述"怎么找数据"。

1.3 核心术语

• 表 (Table / Relation) 存储同类数据的二维结构，由行和列组成。在关系代数中称为"关系（Relation）"。一个数据库通常包含多张表，例如 users 表、orders 表。
• 行 (Row / Tuple / Record) 表中的一条数据记录，也叫"元组（Tuple）"。每一行描述一个具体的实体实例，例如一个用户或一笔订单。
• 列 (Column / Attribute) 表中的一个字段，描述实体的某个属性，例如 name、email、created_at。每列有固定的数据类型。
• 主键 (Primary Key) 唯一标识一行数据的列（或列组合）。主键值不能重复、不能为 NULL。通常使用自增整数 id 或 UUID。
• 外键 (Foreign Key) 一张表中引用另一张表主键的列，用于建立表间关联。例如 orders 表的 user_id 列引用 users 表的 id，确保订单必须属于存在的用户。
• 索引 (Index) 数据库为加速查询而维护的额外数据结构（类似书的目录）。索引让 WHERE 条件查询从全表扫描变为快速定位，代价是占用额外存储空间并稍微降低写入速度。
• 事务 (Transaction) 一组作为整体执行的 SQL 操作，要么全部成功（COMMIT），要么全部回滚（ROLLBACK）。银行转账是经典示例：扣钱和加钱必须同时成功。
• Schema（模式） 数据库的结构定义，包括所有表的定义、列的类型、约束、索引等。可理解为数据库的"蓝图"。在 PostgreSQL 中 Schema 也是命名空间的概念（public schema）。

用 users 表直观理解术语

id (主键)	username	email	created_at
1	alice	alice@example.com	2024-01-15
2	bob	bob@example.com	2024-01-20
3	carol	carol@example.com	2024-02-01

1.4 SQL vs NoSQL 对比

关系型数据库并非万能，不同的数据模型适合不同的业务场景：

类型	代表产品	数据模型	优势	适用场景
关系型	MySQL, PostgreSQL, SQLite, Oracle	二维表格，严格 Schema	ACID 事务、强一致性、复杂 JOIN 查询、成熟生态	电商订单、金融系统、ERP、绝大多数 Web 后端
文档型	MongoDB, CouchDB	JSON/BSON 文档，灵活 Schema	灵活嵌套结构、Schema 可变、横向扩展容易	内容管理、用户画像、结构多变的数据
键值型	Redis, DynamoDB	Key → Value 哈希	极高读写性能（内存级）、简单水平扩展	缓存、Session 存储、计数器、排行榜
列式	Cassandra, HBase	列族（Column Family）	海量数据写入、按列聚合分析高效	时序数据、日志分析、物联网数据
图数据库	Neo4j, Amazon Neptune	节点 + 边（图结构）	关系遍历高效、多跳关系查询	社交关系、知识图谱、推荐系统

1.5 MySQL 安装与连接

Mac — Homebrew 安装

# 安装 Homebrew（如果尚未安装）
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

# 安装 MySQL 8
brew install mysql

# 启动 MySQL 服务
brew services start mysql

# 安全初始化（设置 root 密码、删除匿名用户）
mysql_secure_installation

# 连接到 MySQL
mysql -u root -p

Windows — 安装包方式

1. 访问 dev.mysql.com/downloads/installer/ 下载 MySQL Installer
2. 选择 "Developer Default" 安装类型，包含 MySQL Server、Workbench、Shell
3. 按向导设置 root 密码，选择 "Use Legacy Authentication Method"（兼容性好）
4. 安装完成后在 MySQL Shell 或命令提示符中连接：

-- Windows 命令提示符
"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe" -u root -p

MySQL 连接后的基本命令

-- 查看所有数据库
SHOW DATABASES;

-- 选择数据库
USE mydb;

-- 查看当前数据库中所有表
SHOW TABLES;

-- 查看表结构
DESCRIBE users;

-- 查看 MySQL 版本
SELECT VERSION();

1.6 PostgreSQL 安装与连接

Mac — Homebrew 安装

# 安装 PostgreSQL 16
brew install postgresql@16

# 启动服务
brew services start postgresql@16

# 将 psql 加入 PATH（按照 brew 输出的提示）
echo 'export PATH="/opt/homebrew/opt/postgresql@16/bin:$PATH"' >> ~/.zshrc
source ~/.zshrc

# 连接（默认用当前系统用户名登录，无需密码）
psql postgres

Windows — 安装包方式

1. 访问 postgresql.org/download/windows/ 下载 EnterpriseDB 安装包
2. 安装时设置 postgres 超级用户密码，默认端口 5432
3. 安装完成后使用 psql 命令行工具连接：

# 连接到默认数据库
psql -U postgres -h localhost

psql 常用元命令

\l          -- 列出所有数据库（等同于 MySQL 的 SHOW DATABASES）
\c mydb     -- 切换到 mydb 数据库
\dt         -- 列出当前数据库的所有表
\d users    -- 查看 users 表结构（列、类型、索引、约束）
\du         -- 列出所有用户/角色
\timing     -- 开关查询耗时显示
\q          -- 退出 psql

1.7 SQL 语句分类

SQL 语句按功能分为四大类，理解分类有助于系统学习：

分类	全称	主要语句	作用
DDL	Data Definition Language 数据定义语言	CREATE ALTER DROP TRUNCATE	定义和管理数据库结构（表、索引、视图等）
DML	Data Manipulation Language 数据操作语言	SELECT INSERT UPDATE DELETE	对表中数据进行增、删、改、查
DCL	Data Control Language 数据控制语言	GRANT REVOKE	管理用户权限（谁能读、写、执行什么）
TCL	Transaction Control Language 事务控制语言	BEGIN COMMIT ROLLBACK SAVEPOINT	控制事务的开始、提交、回滚

SQL-- DDL：定义结构
CREATE TABLE users (id INT PRIMARY KEY);
ALTER TABLE users ADD COLUMN email VARCHAR(255);
DROP TABLE users;

-- DML：操作数据
INSERT INTO users (id, email) VALUES (1, 'a@b.com');
SELECT * FROM users WHERE id = 1;
UPDATE users SET email = 'new@b.com' WHERE id = 1;
DELETE FROM users WHERE id = 1;

-- DCL：权限管理
GRANT SELECT, INSERT ON users TO 'readonly_user';
REVOKE INSERT ON users FROM 'readonly_user';

-- TCL：事务控制
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;