数据库学习笔记

1. 数据库基础

1.1 数据库概念

数据库是按照一定的数据结构组织、存储和管理数据的仓库。它可以被看作是一个电子化的文件柜，用户可以对文件中的数据进行增加、删除、修改、查询等操作。

1.2 数据库管理系统 (DBMS)

数据库管理系统是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库。常见的DBMS包括：

• 关系型数据库：MySQL、PostgreSQL、Oracle、SQL Server
• NoSQL数据库：MongoDB、Redis、Cassandra、Elasticsearch
• 列式数据库：HBase、BigTable

1.3 数据库系统的特点

• 数据结构化：数据按照一定的数据模型组织
• 数据共享：多个用户可以同时访问数据
• 数据独立性：物理独立性和逻辑独立性
• 数据一致性：保证数据的准确性和完整性
• 数据安全性：防止未授权访问和数据泄露

2. 关系型数据库

2.1 关系模型

关系模型是基于数学集合论的数据库模型，它将数据组织成二维表格的形式，每个表格称为一个关系。

• 表（Table）：数据的集合，由行和列组成
• 行（Row）：表中的一条记录
• 列（Column）：表中的一个字段
• 主键（Primary Key）：唯一标识一条记录的字段
• 外键（Foreign Key）：关联两个表的字段

2.2 SQL基础

SQL（Structured Query Language）是用于管理关系型数据库的标准语言。

2.2.1 DDL（数据定义语言）

-- 创建数据库
CREATE DATABASE mydatabase;

-- 使用数据库
USE mydatabase;

-- 创建表
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT,
    major VARCHAR(100)
);

-- 修改表
ALTER TABLE students ADD COLUMN email VARCHAR(100);

-- 删除表
DROP TABLE students;

2.2.2 DML（数据操作语言）

-- 插入数据
INSERT INTO students (name, age, major) VALUES ('张三', 20, '计算机科学');

-- 更新数据
UPDATE students SET age = 21 WHERE id = 1;

-- 删除数据
DELETE FROM students WHERE id = 1;

-- 查询数据
SELECT * FROM students;
SELECT name, age FROM students WHERE major = '计算机科学';

2.2.3 DQL（数据查询语言）

-- 基本查询
SELECT * FROM students;

-- 条件查询
SELECT * FROM students WHERE age > 18 AND major = '计算机科学';

-- 排序
SELECT * FROM students ORDER BY age DESC;

-- 分组
SELECT major, COUNT(*) as count FROM students GROUP BY major;

-- 连接查询
SELECT students.name, courses.course_name 
FROM students 
JOIN student_courses ON students.id = student_courses.student_id
JOIN courses ON student_courses.course_id = courses.id;

-- 子查询
SELECT * FROM students WHERE age > (SELECT AVG(age) FROM students);

2.2.4 DCL（数据控制语言）

-- 创建用户
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password';

-- 授权
GRANT SELECT, INSERT ON mydatabase.students TO 'user1'@'localhost';

-- 撤销权限
REVOKE INSERT ON mydatabase.students FROM 'user1'@'localhost';

-- 删除用户
DROP USER 'user1'@'localhost';

3. NoSQL数据库

3.1 NoSQL概念

NoSQL（Not Only SQL）是一类非关系型数据库，它不使用传统的关系模型，而是采用不同的数据模型来存储数据。

3.2 NoSQL数据库类型

3.2.1 文档型数据库

以文档形式存储数据，如JSON、BSON等。

• MongoDB：最流行的文档型数据库
• CouchDB：支持多版本并发控制

// MongoDB示例
{
    "_id": ObjectId("5f8d0d55b6b9a72b8c4d5e6f"),
    "name": "张三",
    "age": 20,
    "major": "计算机科学",
    "courses": ["数据结构", "算法分析", "数据库系统"]
}

3.2.2 键值型数据库

以键值对形式存储数据，适合缓存、会话管理等场景。

• Redis：支持多种数据结构的内存数据库
• Memcached：简单高效的内存缓存系统

3.2.3 列族数据库

按列族存储数据，适合大规模数据存储和分析。

• HBase：基于Hadoop的分布式列族数据库
• Cassandra：高可扩展性的分布式数据库

3.2.4 图数据库

以图结构存储数据，适合社交网络、推荐系统等场景。

• Neo4j：最流行的图数据库
• OrientDB：支持多模型的数据库

4. 数据库设计

4.1 数据库设计流程

1. 需求分析：了解业务需求，确定数据范围和使用方式
2. 概念设计：创建ER图，确定实体、属性和关系
3. 逻辑设计：将ER图转换为关系模式，进行范式化
4. 物理设计：选择存储结构，创建索引，优化性能
5. 实施与维护：创建数据库，导入数据，进行维护和优化

4.2 数据库范式

数据库范式是设计关系型数据库时应遵循的规则，目的是减少数据冗余，提高数据一致性。

4.2.1 第一范式（1NF）

确保每列的原子性，即每列的值都不可再分。

4.2.2 第二范式（2NF）

在1NF的基础上，确保非主键列完全依赖于主键，而不是部分依赖。

4.2.3 第三范式（3NF）

在2NF的基础上，确保非主键列不传递依赖于主键。

4.2.4 巴斯-科德范式（BCNF）

在3NF的基础上，确保所有决定因素都是候选键。

5. 数据库优化

5.1 索引优化

• 为经常查询的列创建索引
• 避免在索引列上使用函数或表达式
• 考虑复合索引的顺序
• 定期维护索引，避免索引碎片化

5.2 查询优化

• 只查询需要的列，避免SELECT *
• 使用WHERE子句限制结果集
• 合理使用JOIN，避免过多表连接
• 使用EXPLAIN分析查询执行计划
• 考虑使用视图或存储过程

5.3 存储优化

• 选择合适的数据类型
• 分区表，提高查询性能
• 使用压缩技术减少存储空间
• 合理设置表空间和数据文件

5.4 并发控制

• 了解事务隔离级别
• 合理使用锁机制
• 避免长事务
• 使用乐观锁减少锁竞争

6. 数据库实践

6.1 数据库连接池

数据库连接池是管理数据库连接的技术，它可以提高应用程序的性能和可靠性。

// Java中使用HikariCP连接池
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/mydatabase");
config.setUsername("root");
config.setPassword("password");
config.setMaximumPoolSize(10);

HikariDataSource dataSource = new HikariDataSource(config);

// 获取连接
Connection connection = dataSource.getConnection();

// 使用连接
PreparedStatement statement = connection.prepareStatement("SELECT * FROM students");
ResultSet resultSet = statement.executeQuery();

// 关闭连接（返回连接池）
connection.close();

6.2 事务处理

事务是一组原子性的操作，要么全部成功，要么全部失败。

// Java中的事务处理
Connection connection = dataSource.getConnection();
try {
    // 开始事务
    connection.setAutoCommit(false);
    
    // 执行操作
    PreparedStatement statement1 = connection.prepareStatement(
        "UPDATE accounts SET balance = balance - 100 WHERE id = 1"
    );
    statement1.executeUpdate();
    
    PreparedStatement statement2 = connection.prepareStatement(
        "UPDATE accounts SET balance = balance + 100 WHERE id = 2"
    );
    statement2.executeUpdate();
    
    // 提交事务
    connection.commit();
} catch (SQLException e) {
    // 回滚事务
    connection.rollback();
    e.printStackTrace();
} finally {
    connection.setAutoCommit(true);
    connection.close();
}

6.3 数据备份与恢复

• 定期进行数据备份
• 使用多种备份策略：完全备份、增量备份、差异备份
• 测试备份的可恢复性
• 制定灾难恢复计划

-- MySQL备份示例
mysqldump -u root -p mydatabase > mydatabase_backup.sql

-- MySQL恢复示例
mysql -u root -p mydatabase < mydatabase_backup.sql