数据库核心概念详解

SQL 学习重点：
│
├── 数据查询（SELECT）
│   ├── 基础查询
│   ├── 条件查询（WHERE）
│   ├── 排序（ORDER BY）
│   ├── 分组（GROUP BY）
│   └── 关联查询（JOIN）
│
├── 数据操作（DML）
│   ├── 插入（INSERT）
│   ├── 更新（UPDATE）
│   └── 删除（DELETE）
│
└── 数据定义（DDL）
    ├── 创建表（CREATE TABLE）
    ├── 修改表（ALTER TABLE）
    └── 删除表（DROP TABLE）

┌────┬──────────┬────────┬────────┐
│ ID │ username │  name  │ gender │
├────┼──────────┼────────┼────────┤
│  1 │ zhangsan │  张三  │   1    │
│  2 │ lisi     │  李四  │   2    │
│  3 │ wangwu   │  王五  │   1    │
└────┴──────────┴────────┴────────┘

表头（列名）：ID、username、name、gender
记录（行）：3 条记录

表的组成要素：
│
├── 表名（Table Name）
│   └── 标识表的名称，如：users、orders
│
├── 列/字段（Column/Field）
│   └── 表头，定义数据的类型和含义
│
├── 行/记录（Row/Record）
│   └── 一条完整的数据
│
└── 单元格（Cell）
    └── 具体的数据值

-- 创建用户表
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,
  name VARCHAR(50),
  gender TINYINT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 查看表结构
DESC users;

-- 查看建表语句
SHOW CREATE TABLE users;

-- 删除表
DROP TABLE users;

-- username 列
数据类型：VARCHAR(50)
含义：用户名
约束：NOT NULL（不能为空）

-- gender 列
数据类型：TINYINT
含义：性别（1-男，2-女）
约束：DEFAULT 0（默认为 0）

CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '商品ID',
  name VARCHAR(100) NOT NULL COMMENT '商品名称',
  price DECIMAL(10, 2) NOT NULL COMMENT '价格',
  stock INT DEFAULT 0 COMMENT '库存',
  status TINYINT DEFAULT 1 COMMENT '状态：1-上架，0-下架',
  description TEXT COMMENT '商品描述',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';

CREATE TABLE users (
  -- PRIMARY KEY：主键约束
  id INT PRIMARY KEY AUTO_INCREMENT,
  
  -- NOT NULL：非空约束
  username VARCHAR(50) NOT NULL,
  
  -- UNIQUE：唯一约束
  email VARCHAR(100) UNIQUE,
  
  -- DEFAULT：默认值
  status TINYINT DEFAULT 1,
  
  -- CHECK：检查约束
  age INT CHECK (age >= 0 AND age <= 150),
  
  -- FOREIGN KEY：外键约束
  dept_id INT,
  FOREIGN KEY (dept_id) REFERENCES departments(id)
);

-- 一条记录
{
  id: 1,
  username: 'zhangsan',
  name: '张三',
  gender: 1,
  created_at: '2024-01-01 10:00:00'
}

-- 插入单条记录
INSERT INTO users (username, name, gender)
VALUES ('zhangsan', '张三', 1);

-- 插入多条记录
INSERT INTO users (username, name, gender) VALUES
  ('lisi', '李四', 2),
  ('wangwu', '王五', 1);

-- 查询所有记录
SELECT * FROM users;

-- 查询特定字段
SELECT id, username, name FROM users;

-- 条件查询
SELECT * FROM users WHERE gender = 1;

-- 排序
SELECT * FROM users ORDER BY created_at DESC;

-- 分页
SELECT * FROM users LIMIT 0, 10;  -- 第 1 页，每页 10 条
SELECT * FROM users LIMIT 10, 10;  -- 第 2 页，每页 10 条

-- 更新单个字段
UPDATE users SET name = '张三丰' WHERE id = 1;

-- 更新多个字段
UPDATE users 
SET name = '张三丰', gender = 1 
WHERE id = 1;

-- 删除特定记录
DELETE FROM users WHERE id = 1;

-- 条件删除
DELETE FROM users WHERE status = 0;

-- 单列主键
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50),
  name VARCHAR(50)
);

-- 或者在表定义后指定
CREATE TABLE users (
  id INT AUTO_INCREMENT,
  username VARCHAR(50),
  name VARCHAR(50),
  PRIMARY KEY (id)
);

-- 联合主键：多个字段组合作为主键
CREATE TABLE student_courses (
  student_id INT,
  course_id INT,
  score DECIMAL(5, 2),
  PRIMARY KEY (student_id, course_id)  -- 联合主键
);

主键的三大作用：
│
├── 1⃣ 唯一标识
│   └── 通过主键值唯一确定一条记录
│
├── 2⃣ 建立索引
│   └── 主键自动创建索引，提高查询速度
│
└── 3⃣ 关联关系
    └── 主键作为外键，建立表之间的关联

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键
  username VARCHAR(50) UNIQUE,         -- 唯一键
  email VARCHAR(100) UNIQUE,           -- 唯一键
  phone VARCHAR(20)                    -- 普通字段
);

主键设计原则：
│
├──  推荐做法
│   ├── 使用自增整数（AUTO_INCREMENT）
│   ├── 主键值简短、稳定
│   ├── 不要使用业务字段作为主键
│   └── 不要修改主键值
│
└──  不推荐做法
    ├── 使用 UUID（太长、索引效率低）
    ├── 使用业务字段（如手机号、邮箱）
    ├── 使用联合主键（除非必要）
    └── 频繁修改主键值

外键的作用：
│
├── 1⃣ 建立关联关系
│   └── 连接两个表的数据
│
├── 2⃣ 保证数据一致性
│   └── 不能插入不存在的外键值
│
└── 3⃣ 级联操作
    ├── 级联删除（ON DELETE CASCADE）
    └── 级联更新（ON UPDATE CASCADE）

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

-- 身份证信息表（一对一）
CREATE TABLE user_id_cards (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT UNIQUE,  -- UNIQUE 确保一对一
  id_card_no VARCHAR(18),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

┌─────────┐         ┌──────────────┐
│  用户   │ 1 ──── 1 │ 身份证信息   │
└─────────┘         └──────────────┘

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

-- 书本表（一对多）
CREATE TABLE books (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100),
  user_id INT,  -- 外键在"多"的一方
  FOREIGN KEY (user_id) REFERENCES users(id)
);

用户表（users）：
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | zhangsan | 123456   |
+----+----------+----------+

书本表（books）：
+----+----------+---------+
| id |   name   | user_id |
+----+----------+---------+
|  1 | 语文书   |    1    |
|  2 | 数学书   |    1    |
|  3 | 英语书   |    1    |
+----+----------+---------+

一个用户（张三）对应多本书（语文、数学、英语）

┌─────────┐         ┌─────────┐
│  用户   │ 1 ──── N │  书本   │
└─────────┘         └─────────┘

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

-- 角色表
CREATE TABLE roles (
  id INT PRIMARY KEY AUTO_INCREMENT,
  role_name VARCHAR(50)
);

-- 用户角色关联表（多对多）
CREATE TABLE user_roles (
  user_id INT,
  role_id INT,
  PRIMARY KEY (user_id, role_id),  -- 联合主键
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (role_id) REFERENCES roles(id)
);

用户表（users）：
+----+----------+
| id | username |
+----+----------+
|  1 | 张三     |
|  2 | 李四     |
+----+----------+

角色表（roles）：
+----+------------+
| id | role_name  |
+----+------------+
|  1 | 中队长     |
|  2 | 语文科代表 |
+----+------------+

用户角色关联表（user_roles）：
+---------+---------+
| user_id | role_id |
+---------+---------+
|    1    |    1    |  -- 张三 是 中队长
|    1    |    2    |  -- 张三 是 语文科代表
|    2    |    1    |  -- 李四 是 中队长
+---------+---------+

一个用户（张三）有多个角色（中队长、语文科代表）
一个角色（中队长）有多个用户（张三、李四）

┌─────────┐         ┌─────────────┐         ┌─────────┐
│  用户   │ N ──── M │ 用户角色表  │ M ──── N │  角色   │
└─────────┘         └─────────────┘         └─────────┘

三种关系速记：
│
├── 1:1 一对一
│   └── 外键 + UNIQUE
│
├── 1:N 一对多
│   └── 外键在"多"的一方
│
└── M:N 多对多
    └── 中间表 + 联合主键

--  不推荐：将书本作为数组存储
CREATE TABLE users_bad (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50),
  books TEXT  -- 存储 JSON 数组：["语文书","数学书","英语书"]
);

--  不推荐：大量冗余数据
CREATE TABLE users_bad (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50),
  book1 VARCHAR(50),  -- 书本1
  book2 VARCHAR(50),  -- 书本2
  book3 VARCHAR(50)   -- 书本3
);

-- 数据示例
+----+----------+--------+--------+--------+
| id | username | book1  | book2  | book3  |
+----+----------+--------+--------+--------+
|  1 | 张三     | 语文书 | 数学书 | 英语书 |
+----+----------+--------+--------+--------+

--  推荐：分表存储
-- 用户表
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50)
);

-- 书本表
CREATE TABLE books (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50),
  user_id INT,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

分表存储的优势：
│
├── 1⃣ 避免数据冗余
│   └── 数据只存储一次，减少存储空间
│
├── 2⃣ 提高查询效率
│   └── 可以建立索引，快速查询
│
├── 3⃣ 保证数据一致性
│   └── 修改一处，所有引用自动更新
│
├── 4⃣ 增强扩展性
│   └── 可以无限扩展关联数据
│
└── 5⃣ 易于维护
    └── 数据独立，修改互不影响

无索引查询：
遍历所有数据 → 逐条比较 → 时间复杂度 O(n)

有索引查询：
查询索引结构 → 快速定位 → 时间复杂度 O(log n)

-- 主键自动创建索引
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50)
);

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

-- 或在表定义时
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(100) UNIQUE
);

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

-- 或在表定义时
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50),
  INDEX idx_username (username)
);

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

-- 使用原则：最左前缀原则
--  使用索引：WHERE status = 1
--  使用索引：WHERE status = 1 AND created_at > '2024-01-01'
--  不使用索引：WHERE created_at > '2024-01-01'（缺少最左字段）

-- 全文索引（用于文本搜索）
CREATE FULLTEXT INDEX idx_content ON articles(content);

-- 使用全文搜索
SELECT * FROM articles WHERE MATCH(content) AGAINST('数据库');

索引使用原则：
│
├──  应该创建索引的字段
│   ├── 主键（自动创建）
│   ├── 外键（建议创建）
│   ├── 经常查询的字段
│   ├── 经常排序的字段
│   └── 经常作为 WHERE 条件的字段
│
└──  不应该创建索引的字段
    ├── 数据量小的表
    ├── 频繁更新的字段
    ├── 区分度低的字段（如性别、状态）
    └── 很少查询的字段

-- 查看表的索引
SHOW INDEX FROM users;

-- 删除索引
DROP INDEX idx_username ON users;

-- 或使用 ALTER TABLE
ALTER TABLE users DROP INDEX idx_username;

-- 场景：查询状态为启用且创建时间在 2024 年的用户

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

-- 2. 查询语句
SELECT * FROM users 
WHERE status = 1 
  AND created_at >= '2024-01-01' 
  AND created_at < '2025-01-01';

-- 3. 使用 EXPLAIN 分析查询
EXPLAIN SELECT * FROM users WHERE status = 1;

-- 查看是否使用了索引
-- key: idx_status_created（使用了索引）
-- rows: 预估扫描行数

-- 内连接：只返回两个表中都有匹配的记录
SELECT 
  users.username,
  books.name AS book_name
FROM users
INNER JOIN books ON users.id = books.user_id;

-- 结果：只显示有书本的用户

-- 左连接：返回左表所有记录，右表没有则为 NULL
SELECT 
  users.username,
  books.name AS book_name
FROM users
LEFT JOIN books ON users.id = books.user_id;

-- 结果：显示所有用户，没有书本的用户显示 NULL

-- 右连接：返回右表所有记录，左表没有则为 NULL
SELECT 
  users.username,
  books.name AS book_name
FROM users
RIGHT JOIN books ON users.id = books.user_id;

-- 结果：显示所有书本，没有用户的书本显示 NULL

-- 三表关联：用户 - 角色 - 权限

SELECT 
  u.username,
  r.role_name,
  p.permission_name
FROM users u
INNER JOIN user_roles ur ON u.id = ur.user_id
INNER JOIN roles r ON ur.role_id = r.id
INNER JOIN role_permissions rp ON r.id = rp.role_id
INNER JOIN permissions p ON rp.permission_id = p.id
WHERE u.id = 1;

-- 1. 使用索引：确保关联字段有索引
CREATE INDEX idx_user_id ON books(user_id);

-- 2. 只查询需要的字段
SELECT u.username, b.name
FROM users u
INNER JOIN books b ON u.id = b.user_id;

-- 3. 使用表别名
SELECT u.username, b.name
FROM users AS u
INNER JOIN books AS b ON u.id = b.user_id;

-- 4. 避免关联过多表（建议不超过 5 张）

表设计原则：
│
├── 1⃣ 范式设计
│   ├── 第一范式：字段不可再分
│   ├── 第二范式：非主键字段完全依赖主键
│   └── 第三范式：非主键字段不传递依赖主键
│
├── 2⃣ 命名规范
│   ├── 表名：小写 + 下划线 + 复数（users、orders）
│   ├── 字段名：小写 + 下划线（user_name、created_at）
│   └── 避免使用保留字
│
├── 3⃣ 字段设计
│   ├── 选择合适的数据类型
│   ├── 设置合理的默认值
│   ├── 添加 NOT NULL 约束
│   └── 添加 COMMENT 注释
│
└── 4⃣ 索引设计
    ├── 主键自动创建索引
    ├── 外键建议创建索引
    ├── 经常查询的字段创建索引
    └── 避免过多索引

-- 1. 使用 EXPLAIN 分析查询
EXPLAIN SELECT * FROM users WHERE status = 1;

-- 2. 避免 SELECT *
SELECT id, username, name FROM users;

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

-- 4. 使用索引覆盖
CREATE INDEX idx_covering ON users(status, created_at, username);

-- 5. 避免在 WHERE 子句中使用函数
--  不推荐
SELECT * FROM users WHERE YEAR(created_at) = 2024;
--  推荐
SELECT * FROM users 
WHERE created_at >= '2024-01-01' 
  AND created_at < '2025-01-01';

-- 用户表
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
  username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
  password VARCHAR(255) NOT NULL COMMENT '密码（加密）',
  email VARCHAR(100) UNIQUE COMMENT '邮箱',
  phone CHAR(11) COMMENT '手机号',
  status TINYINT DEFAULT 1 COMMENT '状态：1-启用，0-禁用',
  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_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- 订单表
CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '订单ID',
  order_no VARCHAR(50) NOT NULL UNIQUE COMMENT '订单号',
  user_id INT NOT NULL COMMENT '用户ID',
  total_amount DECIMAL(10, 2) NOT NULL COMMENT '订单总金额',
  status TINYINT DEFAULT 0 COMMENT '状态：0-待支付，1-已支付，2-已发货，3-已完成',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  
  FOREIGN KEY (user_id) REFERENCES users(id),
  INDEX idx_user_id (user_id),
  INDEX idx_order_no (order_no),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

记忆技巧：
│
├──  表 ≈ Excel 工作表
├──  列 ≈ 表头字段
├──  记录 ≈ 一行数据
├──  主键 ≈ 身份证号（唯一标识）
├──  外键 ≈ 关联桥梁
├──  索引 ≈ 图书馆目录
│
└── 三种关系
    ├── 1:1 → UNIQUE 外键
    ├── 1:N → 外键在"多"方
    └── M:N → 中间表

学习路径规划：
│
├── 第一阶段：理解概念（1-2 天）
│   ├── 理解表、列、记录
│   ├── 理解主键、外键
│   └── 理解三种关联关系
│
├── 第二阶段：实践练习（1 周）
│   ├── 创建数据库和表
│   ├── 实现 CRUD 操作
│   └── 练习关联查询
│
└── 第三阶段：深入应用（持续）
    ├── 索引优化
    ├── 查询优化
    └── 数据库设计

数据库核心概念知识图谱：
│
├── 表（Table）
│   ├── 表名
│   ├── 列/字段
│   └── 行/记录
│
├── 列（Column）
│   ├── 数据类型
│   ├── 约束条件
│   └── 默认值
│
├── 主键（Primary Key）
│   ├── 唯一标识
│   ├── 自带索引
│   ├── 单列主键
│   └── 联合主键
│
├── 外键（Foreign Key）
│   ├── 建立关联
│   ├── 保证一致性
│   └── 级联操作
│
├── 三种关联关系
│   ├── 一对一（1:1）
│   ├── 一对多（1:N）
│   └── 多对多（M:N）
│
├── 索引（Index）
│   ├── 主键索引
│   ├── 唯一索引
│   ├── 普通索引
│   └── 组合索引
│
└── 最佳实践
    ├── 表设计原则
    ├── 性能优化
    └── 查询优化