【软件工程】SQL数据库指南

核心概念

基本组成

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

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

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

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

示例：

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

主键 (Primary Key)

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

唯一性：不能重复
非空性：不能为空
稳定性：不要轻易改变

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

外键 (Foreign Key)

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

重点：

外键字段名可以随意命名（常用 表名_id，但非强制）
真正定义关系的是 FOREIGN KEY 约束，不是字段名

-- 用户表
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)  -- 这里定义关系
);

可视化：

users                    orders
+----+------+           +----+-------+-------+
| id | name |           | id | buyer | total |
+----+------+           +----+-------+-------+
| 1  | 张三 | <-------- | 1  | 1     | 100   |
| 2  | 李四 |           | 2  | 1     | 200   |
+----+------+           +----+-------+-------+

添加外键约束的方式：

-- 创建表时
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 约束

多对多

多个学生 ↔ 多门课程
实现：需要中间表，包含两个外键

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)

-- 查询用户及其订单
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`	二进制数据

索引与优化

索引作用

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

-- 创建索引
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类型），原因是索引会非常大，且全文搜索应该用专门的全文索引
经常更新的字段：原因是每次更新都要更新索引，影响性能

优化技巧

-- ❌ 避免
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（持久性）：提交后永久保存

使用方法

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：

/var/lib/mysql/mydb/
  ├── users.ibd
  ├── posts.ibd
  └── orders.ibd

PostgreSQL：

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

SQLite：

1	mydata.db（单文件）

备份方法

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

# 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

方法二：物理备份

# 停止服务
sudo systemctl stop mysql
sudo cp -r /var/lib/mysql /backup/
sudo systemctl start mysql

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

方法三：远程备份

# 从远程导出
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