数据库基础概念与设计

-- 用户表
+----+-----------+--------+--------+
| id | username  | name   | gender |
+----+-----------+--------+--------+
| 1  | zhangsan  | 张三   | 1      |
| 2  | lisi      | 李四   | 2      |
| 3  | wangwu    | 王五   | 1      |
+----+-----------+--------+--------+

-- 性别表（外键关联）
+----+--------+
| id | name   |
+----+--------+
| 1  | 男     |
| 2  | 女     |
+----+--------+

// 用户文档 1
{
  "_id": ObjectId("507f1f77bcf86cd799439011"),
  "username": "zhangsan",
  "name": "张三",
  "gender": "男",
  "age": 25
}

// 用户文档 2（结构可以不同）
{
  "_id": ObjectId("507f1f77bcf86cd799439012"),
  "username": "lisi",
  "name": "李四",
  "gender": "女",
  "email": "lisi@example.com",
  "address": {
    "city": "北京",
    "street": "朝阳区"
  }
}

关系型数据库：
┌─────────────────┐
│ 用户表          │
├─────┬─────┬─────┤
│ id  │ name│ age │  ← 固定结构
├─────┼─────┼─────┤
│  1  │张三 │ 25  │
└─────┴─────┴─────┘

非关系型数据库：
{
  "_id": 1,
  "name": "张三",
  "age": 25,
  "tags": ["前端", "Vue"]  ← 结构灵活
}

-- 用户表
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50),
  name VARCHAR(50),
  class_id INT UNIQUE,  -- 唯一外键
  FOREIGN KEY (class_id) REFERENCES classes(id)
);

-- 班级表
CREATE TABLE classes (
  id INT PRIMARY KEY AUTO_INCREMENT,
  class_name VARCHAR(50)
);

┌─────────┐         ┌─────────┐
│  用户   │ 1 ──── 1 │  班级   │
└─────────┘         └─────────┘

-- 用户表
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50),
  name VARCHAR(50)
);

-- 订单表
CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  order_no VARCHAR(50),
  user_id INT,  -- 外键
  FOREIGN KEY (user_id) REFERENCES users(id)
);

┌─────────┐         ┌─────────┐
│  用户   │ 1 ──── N │  订单   │
└─────────┘         └─────────┘

// user.entity.ts
import { Entity, PrimaryGeneratedColumn, Column, OneToMany } from 'typeorm';
import { Order } from './order.entity';

@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  username: string;

  @OneToMany(() => Order, order => order.user)
  orders: Order[];
}

// order.entity.ts
import { Entity, PrimaryGeneratedColumn, Column, ManyToOne } from 'typeorm';
import { User } from './user.entity';

@Entity()
export class Order {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  orderNo: string;

  @ManyToOne(() => User, user => user.orders)
  user: User;
}

-- 学生表
CREATE TABLE students (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50)
);

-- 课程表
CREATE TABLE courses (
  id INT PRIMARY KEY AUTO_INCREMENT,
  course_name VARCHAR(50)
);

-- 中间表（关联表）
CREATE TABLE student_courses (
  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)
);

┌─────────┐         ┌─────────┐         ┌─────────┐
│  学生   │ N ──── M │ 中间表  │ M ──── N │  课程   │
└─────────┘         └─────────┘         └─────────┘

// student.entity.ts
import { Entity, PrimaryGeneratedColumn, Column, ManyToMany, JoinTable } from 'typeorm';
import { Course } from './course.entity';

@Entity()
export class Student {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @ManyToMany(() => Course, course => course.students)
  @JoinTable()  // 自动创建中间表
  courses: Course[];
}

// course.entity.ts
import { Entity, PrimaryGeneratedColumn, Column, ManyToMany } from 'typeorm';
import { Student } from './student.entity';

@Entity()
export class Course {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  courseName: string;

  @ManyToMany(() => Student, student => student.courses)
  students: Student[];
}

数据库关联类型总结：
│
├── 1:1 一对一
│   ├── 示例：用户 ↔ 身份证
│   ├── 特点：外键 UNIQUE
│   └── 实现：一个表的外键关联另一个表的主键
│
├── 1:N 一对多
│   ├── 示例：用户 ↔ 订单
│   ├── 特点：外键在"多"的一方
│   └── 实现：子表的外键关联父表的主键
│
└── M:N 多对多
    ├── 示例：学生 ↔ 课程
    ├── 特点：需要中间表
    └── 实现：两个外键组成联合主键

┌─────────────────┐         ┌─────────────────┐         ┌─────────────────┐
│     作者        │         │      书籍       │         │    出版商       │
├─────────────────┤         ├─────────────────┤         ├─────────────────┤
│ id: INT (PK)    │         │ id: INT (PK)    │         │ id: INT (PK)    │
│ name: VARCHAR   │◄───────►│ title: VARCHAR  │◄───────►│ name: VARCHAR   │
│ email: VARCHAR  │  1   N  │ isbn: VARCHAR   │  N   1  │ address: VARCHAR│
│ bio: TEXT       │         │ price: DECIMAL  │         │ phone: VARCHAR  │
└─────────────────┘         │ author_id: FK   │         └─────────────────┘
                            │ publisher_id: FK│
                            └─────────────────┘
                                    │
                                    │ 1
                                    │
                                    ▼ N
                            ┌─────────────────┐
                            │    库存记录     │
                            ├─────────────────┤
                            │ id: INT (PK)    │
                            │ book_id: FK     │
                            │ quantity: INT   │
                            │ location: VARCHAR│
                            └─────────────────┘

图例说明：
───► 一对多关系（1 ── N）
◄───► 多对一关系（N ── 1）

                    ┌──────────┐
                    │   用户   │
                    └────┬─────┘
                         │ 1
                         │
                         ▼ N
                    ┌──────────┐
                    │   订单   │
                    └────┬─────┘
                         │ N
                         │
          ┌──────────────┼──────────────┐
          │              │              │
          ▼ M            ▼ 1            ▼ N
    ┌──────────┐   ┌──────────┐   ┌──────────┐
    │ 订单商品 │   │  支付    │   │   收货   │
    └────┬─────┘   └──────────┘   └──────────┘
         │ N
         │
         ▼ 1
    ┌──────────┐
    │   商品   │
    └────┬─────┘
         │ 1
         │
         ▼ N
    ┌──────────┐
    │   库存   │
    └──────────┘

关系说明：
• 用户 1:N 订单（一个用户多个订单）
• 订单 1:N 订单商品（一个订单多个商品）
• 订单商品 M:1 商品（多个订单商品对应一个商品）
• 商品 1:N 库存（一个商品多个库存记录）

常见字段类型选择：
│
├── 整数类型
│   ├── TINYINT：0-255（状态、标志位）
│   ├── INT：主键、数量
│   └── BIGINT：大数值、时间戳
│
├── 字符串类型
│   ├── CHAR：固定长度（手机号、身份证）
│   ├── VARCHAR：可变长度（用户名、标题）
│   └── TEXT：长文本（文章、描述）
│
├── 小数类型
│   ├── DECIMAL：金额、精确计算
│   └── FLOAT：评分、百分比
│
├── 日期类型
│   ├── DATE：日期（生日）
│   ├── DATETIME：日期时间（创建时间）
│   └── TIMESTAMP：时间戳
│
└── 特殊类型
    ├── JSON：JSON 数据
    ├── ENUM：枚举（状态）
    └── BOOLEAN：布尔值

-- 用户表
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
  username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
  email VARCHAR(100) NOT NULL UNIQUE COMMENT '邮箱',
  password VARCHAR(255) NOT NULL COMMENT '密码（加密）',
  phone CHAR(11) COMMENT '手机号',
  gender TINYINT DEFAULT 0 COMMENT '性别：0-未知，1-男，2-女',
  age TINYINT UNSIGNED COMMENT '年龄',
  avatar VARCHAR(255) COMMENT '头像URL',
  status TINYINT DEFAULT 1 COMMENT '状态：0-禁用，1-启用',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  INDEX idx_username (username),
  INDEX idx_email (email),
  INDEX idx_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- 文章表
CREATE TABLE articles (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '文章ID',
  title VARCHAR(200) NOT NULL COMMENT '标题',
  content TEXT COMMENT '内容',
  summary VARCHAR(500) COMMENT '摘要',
  author_id INT NOT NULL COMMENT '作者ID',
  category_id INT COMMENT '分类ID',
  view_count INT DEFAULT 0 COMMENT '浏览次数',
  like_count INT DEFAULT 0 COMMENT '点赞数',
  is_published BOOLEAN DEFAULT FALSE COMMENT '是否发布',
  published_at DATETIME COMMENT '发布时间',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  FOREIGN KEY (author_id) REFERENCES users(id),
  INDEX idx_author (author_id),
  INDEX idx_category (category_id),
  INDEX idx_published (is_published, published_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章表';

数据库流行度排名：
│
├── 1. Oracle           ━━━━━━━━━━━━━━━━━━━━ 关系型
├── 2. MySQL            ━━━━━━━━━━━━━━━━━   关系型
├── 3. Microsoft SQL Server ━━━━━━━━━━━━━━ 关系型
├── 4. PostgreSQL       ━━━━━━━━━━━━━      关系型
├── 5. MongoDB          ━━━━━━━━━━━        非关系型
├── 6. Redis            ━━━━━━━━━          非关系型
├── 7. Elasticsearch    ━━━━━━━━━          搜索引擎
├── 8. IBM Db2          ━━━━━━━            关系型
├── 9. SQLite           ━━━━━━             关系型
└── 10. Cassandra       ━━━━━              非关系型

数据库发展趋势：
│
├── MySQL 
│   ├── 免费开源
│   ├── 性能优秀
│   └── 大多数业务系统首选
│
├── PostgreSQL 
│   ├── 开源免费
│   ├── 特性介于关系型和非关系型之间
│   └── 程序员流行度高
│
├── MongoDB 
│   ├── 灵活的数据结构
│   ├── 对开发者友好
│   └── 适合敏捷开发
│
└── Oracle 
    ├── 价格昂贵
    ├── 国产化替代
    └── 政企采购减少

-- 多表关联查询
SELECT 
  u.username,
  u.name,
  COUNT(o.id) as order_count,
  SUM(o.total_amount) as total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2024-01-01'
GROUP BY u.id
HAVING total_amount > 1000
ORDER BY total_amount DESC
LIMIT 10;

-- 子查询
SELECT * FROM products
WHERE category_id IN (
  SELECT id FROM categories 
  WHERE parent_id = (
    SELECT id FROM categories WHERE name = '电子产品'
  )
);

-- 添加字段（大数据表可能很慢）
ALTER TABLE users ADD COLUMN nickname VARCHAR(50);

-- 修改字段类型（可能锁表）
ALTER TABLE users MODIFY COLUMN age INT;

-- 添加索引（大数据表可能很慢）
CREATE INDEX idx_email ON users(email);

// 简单查询
db.users.find({ age: { $gt: 18 } })

// 复杂查询
db.orders.aggregate([
  { $match: { status: "completed" } },
  { $group: { 
    _id: "$userId", 
    totalAmount: { $sum: "$amount" } 
  }},
  { $sort: { totalAmount: -1 } },
  { $limit: 10 }
])

// 文本搜索
db.articles.find({ $text: { $search: "database" } })

// 地理位置查询
db.stores.find({
  location: {
    $near: {
      $geometry: {
        type: "Point",
        coordinates: [116.397128, 39.916527]
      },
      $maxDistance: 1000
    }
  }
})

数据库选型建议：
│
├── 小型项目
│   └── MySQL / PostgreSQL
│
├── 中型项目
│   ├── MySQL + Redis（缓存）
│   └── PostgreSQL + Redis
│
├── 大型项目
│   ├── MySQL + Redis + MongoDB
│   └── PostgreSQL + Redis + Elasticsearch
│
├── 金融系统
│   └── Oracle / MySQL（强事务）
│
├── 内容管理
│   └── MongoDB + Redis
│
├── 实时系统
│   └── Redis + Kafka
│
└── 大数据分析
    └── Hadoop + Cassandra + Elasticsearch

数据库范式：
│
├── 第一范式（1NF）
│   └── 字段不可再分
│
├── 第二范式（2NF）
│   └── 非主键字段完全依赖主键
│
├── 第三范式（3NF）
│   └── 非主键字段不传递依赖主键
│
└── 反范式化
    └── 为了性能，适当冗余数据

-- 符合范式（需要 JOIN）
SELECT u.name, d.department_name
FROM users u
JOIN departments d ON u.dept_id = d.id;

-- 反范式化（冗余字段，查询更快）
ALTER TABLE users ADD COLUMN department_name VARCHAR(50);
-- 每次更新时同步更新冗余字段

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

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

-- 普通索引
CREATE INDEX idx_username ON users(username);

-- 组合索引
CREATE INDEX idx_status_created ON users(status, created_at);

-- 全文索引
CREATE FULLTEXT INDEX idx_content ON articles(content);

--  避免 SELECT *
SELECT * FROM users;

--  指定需要的字段
SELECT id, username, email FROM users;

--  避免 WHERE 子句中使用函数
SELECT * FROM users WHERE YEAR(created_at) = 2024;

--  使用范围查询
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

--  使用 LIMIT 分页
SELECT * FROM users LIMIT 0, 10;

--  使用 EXISTS 替代 IN
SELECT * FROM users WHERE EXISTS (
  SELECT 1 FROM orders WHERE orders.user_id = users.id
);

--  选择合适的字段类型
-- 手机号：CHAR(11) 而不是 VARCHAR(50)
-- 状态：TINYINT 而不是 INT
-- 金额：DECIMAL 而不是 FLOAT

--  设置默认值
ALTER TABLE users MODIFY COLUMN status TINYINT DEFAULT 1;

--  使用 NOT NULL
ALTER TABLE users MODIFY COLUMN username VARCHAR(50) NOT NULL;

--  适当冗余
-- 频繁查询的字段可以冗余，减少 JOIN

分库分表策略：
│
├── 垂直分库
│   └── 按业务模块拆分（用户库、订单库）
│
├── 垂直分表
│   └── 按字段拆分（用户基本信息、用户扩展信息）
│
├── 水平分库
│   └── 按数据量拆分（用户库1、用户库2）
│
└── 水平分表
    └── 按数据量拆分（订单表1、订单表2）

分表键选择：
├── 用户 ID
├── 订单 ID
└── 时间（按月/年分表）

记忆技巧：
│
├──  关系型数据库特点
│   └── 表格 + SQL + 事务 + 强一致性
│
├──  非关系型数据库特点
│   └── 灵活 + 快速 + 易扩展 + 最终一致性
│
├──  三种关联类型
│   └── 1:1 唯一、1:N 外键、M:N 中间表
│
└──  选型口诀
    └── 金融业务关系型，海量数据非关系

学习路径规划：
│
├── 第一阶段：理解概念（1-2 天）
│   ├── 理解关系型和非关系型数据库
│   ├── 理解三种关联类型
│   └── 学会看 ER 图
│
├── 第二阶段：实践练习（1 周）
│   ├── 创建数据库和表
│   ├── 实现 CRUD 操作
│   └── 练习关联查询
│
└── 第三阶段：深入应用（持续）
    ├── 数据库设计优化
    ├── 性能调优
    └── 分库分表实践

数据库基础知识图谱：
│
├── 数据库类型
│   ├── 关系型数据库
│   │   ├── MySQL
│   │   ├── Oracle
│   │   ├── PostgreSQL
│   │   └── SQL Server
│   └── 非关系型数据库
│       ├── MongoDB
│       ├── Redis
│       └── Memcached
│
├── 关联类型
│   ├── 一对一（1:1）
│   ├── 一对多（1:N）
│   └── 多对多（M:N）
│
├── ER 图
│   ├── 实体（Entity）
│   ├── 属性（Attribute）
│   └── 关系（Relationship）
│
├── 设计工具
│   ├── Navicat
│   ├── dbdiagram.io
│   └── MySQL Workbench
│
└── 最佳实践
    ├── 范式设计
    ├── 索引优化
    ├── 查询优化
    └── 分库分表