核心概念

基本组成

数据库 (Database) - 数据集合，如一个电商平台的所有数据

表 (Table) - 存储数据的基本单位，类似Excel表格

字段/列 (Column) - 表的属性

记录/行 (Row) - 一条具体数据

示例：

1
2
3
4
5
6
7
学生表 (students)
+----+--------+-----+--------+
| id | name   | age | gender |
+----+--------+-----+--------+
| 1  | 张三   | 20  | 男     |
| 2  | 李四   | 22  | 女     |
+----+--------+-----+--------+

主键 (Primary Key)

唯一标识每条记录的字段，特点：

  • 唯一性：不能重复
  • 非空性：不能为空
  • 稳定性：不要轻易改变
1
2
3
4
5
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

外键 (Foreign Key)

外键用于关联表与表的关系。

重点：

  • 外键字段名可以随意命名（常用 表名_id，但非强制）
  • 真正定义关系的是 FOREIGN KEY 约束，不是字段名
1
2
3
4
5
6
7
8
9
10
11
12
13
-- 用户表
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

-- 订单表
CREATE TABLE orders (
    id INT PRIMARY KEY,
    buyer INT,  -- 字段名自定义
    total DECIMAL(10, 2),
    FOREIGN KEY (buyer) REFERENCES users(id)  -- 这里定义关系
);

可视化：

1
2
3
4
5
6
7
users                    orders
+----+------+           +----+-------+-------+
| id | name |           | id | buyer | total |
+----+------+           +----+-------+-------+
| 1  | 张三 | <-------- | 1  | 1     | 100   |
| 2  | 李四 |           | 2  | 1     | 200   |
+----+------+           +----+-------+-------+

添加外键约束的方式：

1
2
3
4
5
6
7
-- 创建表时
FOREIGN KEY (buyer) REFERENCES users(id)

-- 创建后添加
ALTER TABLE orders 
ADD CONSTRAINT fk_user 
FOREIGN KEY (buyer) REFERENCES users(id);

外键作用：

  • 维护数据完整性
  • 防止插入不存在的关联数据
  • 可设置级联删除/更新
1
2
FOREIGN KEY (buyer) REFERENCES users(id) 
ON DELETE CASCADE;  -- 删除用户时自动删除其订单

表关系类型

一对多（最常见）

1
2
一个用户 → 多个订单
实现：在"多"的一方添加外键

一对一

1
2
一个用户 → 一份详细资料
实现：外键添加 UNIQUE 约束

多对多

1
2
3
4
5
6
7
8
9
10
多个学生 ↔ 多门课程
实现：需要中间表，包含两个外键

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

SQL基础操作

查询 (SELECT)

1
2
3
SELECT * FROM students;
SELECT name, age FROM students WHERE age > 20;
SELECT * FROM students ORDER BY age DESC LIMIT 10;

插入 (INSERT)

1
2
INSERT INTO students (name, age) VALUES ('王五', 21);
INSERT INTO students (name, age) VALUES ('赵六', 19), ('孙七', 22);

更新 (UPDATE)

1
2
UPDATE students SET age = 23 WHERE id = 1;
UPDATE students SET age = age + 1 WHERE gender = '男';

删除 (DELETE)

1
2
DELETE FROM students WHERE id = 1;
DELETE FROM students WHERE age < 18;

表连接 (JOIN)

1
2
3
4
5
6
7
8
9
10
-- 查询用户及其订单
SELECT users.name, orders.total
FROM users
INNER JOIN orders ON users.id = orders.user_id;

-- 查询用户订单统计（包括无订单用户）
SELECT users.name, COUNT(orders.id) as order_count
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id;

数据类型

数值类型

类型 说明 示例
int 整数 123
bigint 大整数 9999999999
float 单精度浮点 3.14
double 双精度浮点 3.14159265
decimal(m,n) 精确小数 decimal(10,2) → 12345.67

字符串类型

类型 说明 示例
varchar(n) 可变长字符串（最大n字符） varchar(50) → '张三'
char(n) 固定长字符串 char(10)
text 长文本（最大65KB） 主诉、诊断等

日期时间类型

类型 格式 示例
date YYYY-MM-DD 2024-05-15
time HH:MM:SS 14:37:21
datetime YYYY-MM-DD HH:MM:SS 2024-05-15 14:37:21
timestamp 同datetime，自动更新 记录创建/修改时间

其他

类型 说明
boolean 布尔值（0/1）
blob 二进制数据

索引与优化

索引作用

加速查询，类似书的目录。

1
2
3
4
5
6
-- 创建索引
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_author_status ON posts(author_id, status);

-- 唯一索引
CREATE UNIQUE INDEX idx_username ON users(username);

索引设计原则

应该建索引的字段 ✅

  • 主键字段（必须）：如 id
  • 经常用于WHERE条件的字段：如住院号、医疗卡号、卡号（用于查询患者）、门诊流水号（用于关联查询）
  • 经常用于排序的字段：如就诊时间、date（用于时间排序）
  • 经常用于JOIN的字段：如门诊流水号（用于表关联）
  • 高选择性字段（值的种类多）：如患者ID（每个患者不同）、门诊流水号（每次就诊不同）

不应该建索引的字段 ❌

  • 很少查询的字段：如备注、处理（很少用于WHERE条件）
  • 低选择性字段（值的种类少）：如性别（只有”男”、”女”两个值）、转归状态（只有几个固定值），原因是索引效果不明显，还占用空间
  • 大文本字段：如主诉、诊断（TEXT类型），原因是索引会非常大，且全文搜索应该用专门的全文索引
  • 经常更新的字段：原因是每次更新都要更新索引，影响性能

优化技巧

1
2
3
4
5
6
7
8
9
10
-- ❌ 避免
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- ✅ 推荐
SELECT name, email FROM users WHERE id = 1;
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- 分析查询
EXPLAIN SELECT * FROM posts WHERE author_id = 1;

事务 (Transaction)

  • 事务 (Transaction) 是指一组逻辑上不可分割的数据库操作序列，这些操作要么全部成功执行，要么全部不执行，以保证数据库从一个一致性状态转换到另一个一致性状态。
  • 若没有事务机制，数据库在并发、故障等情况下极易产生脏读、不可重复读、幻读等问题。
  • 简单说：事务就是“一荣俱荣，一损俱损”的操作打包机制，是保障数据库可靠性的基石。

ACID特性

  • Atomicity（原子性）：全部成功或全部失败
  • Consistency（一致性）：数据保持一致
  • Isolation（隔离性）：并发事务互不干扰
  • Durability（持久性）：提交后永久保存

使用方法

1
2
3
4
5
6
7
8
9
10
START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

-- 成功则提交
COMMIT;

-- 失败则回滚
ROLLBACK;

数据库文件与备份

文件位置

MySQL：

1
2
3
4
/var/lib/mysql/mydb/
  ├── users.ibd
  ├── posts.ibd
  └── orders.ibd

PostgreSQL：

1
2
/var/lib/postgresql/data/
（复杂结构，不建议直接操作）

SQLite：

1
mydata.db（单文件）

备份方法

方法一：逻辑备份（推荐）

1
2
3
4
5
6
7
8
9
10
11
# MySQL备份
mysqldump -u user -p database_name > backup.sql
mysqldump -u user -p --all-databases > all_backup.sql
mysqldump -u user -p db_name | gzip > backup.sql.gz

# 恢复
mysql -u user -p database_name < backup.sql

# PostgreSQL
pg_dump -U user db_name > backup.sql
psql -U user db_name < backup.sql

方法二：物理备份

1
2
3
4
5
6
7
# 停止服务
sudo systemctl stop mysql
sudo cp -r /var/lib/mysql /backup/
sudo systemctl start mysql

# 不停机备份（使用工具）
xtrabackup --backup --target-dir=/backup/

方法三：远程备份

1
2
3
4
5
# 从远程导出
mysqldump -h 172.21.8.164 -u user -p db_name > backup.sql

# 服务器间直接传输
mysqldump -h source_host -u user -p db | mysql -h dest_host -u user -p db

可视化工具

VSCode + SQLTools

安装：

  1. 安装”SQLTools”扩展
  2. 安装对应驱动（如”SQLTools MySQL/MariaDB”）

功能：

  • Tables：实际存储数据的表
  • Views：虚拟表，基于查询的动态结果
  • 右键表名 → “Show Table Records” 查看数据
  • 执行SQL查询
  • 导出结果（CSV/JSON）

其他工具

  • MySQL Workbench - MySQL官方
  • DBeaver - 支持多种数据库，免费
  • DataGrip - JetBrains出品，功能强大
  • Navicat - 商业软件，界面友好

总结

核心要点：

  1. 主键唯一标识记录，外键建立表关系
  2. 外键约束靠 FOREIGN KEY 定义，字段名可自定义
  3. 三种表关系：一对多、一对一、多对多
  4. 使用索引优化查询，但不要过度索引
  5. 事务保证数据完整性
  6. 定期备份数据，推荐用 mysqldump
  7. 使用可视化工具提高效率

学习路径：

  1. 掌握基本SQL操作（增删改查）
  2. 理解表关系和外键
  3. 学习索引和查询优化
  4. 了解事务和数据完整性
  5. 实践备份恢复
  6. 深入学习特定数据库系统

参考资源：