数据库：MySQL梳理

  一、数据库简介

1. 什么是数据库？

数据库（Database，简称 DB）是按照数据结构来组织、存储和管理数据的仓库。它可以将零散的数据结构化，方便用户进行各种操作，比如查询、修改、删除等。

2. 数据库的作用

• 数据存储：将海量数据有序存储，避免数据混乱丢失。
• 数据管理：对数据进行增、删、改、查（CRUD），并维护数据的一致性、完整性。
• 数据共享：支持多用户、多应用同时访问和使用数据。
• 数据安全：通过权限控制、备份等机制保障数据不被非法访问或损坏。

3. 关系型数据库

关系型数据库基于 ** 关系模型（二维表结构）** 组织数据，表与表之间通过关联（如外键）建立联系。它的特点是数据结构化强、支持事务（保证数据操作的原子性、一致性等）。

4. 常见的数据库

• MySQL：开源免费，性能优异，是 Web 开发领域的 “宠儿”（如电商、社交平台常用）。
• Oracle：功能强大、安全性高，常用于大型企业级应用（如银行核心系统）。
• SQL Server：微软出品，与.NET 生态整合度高，多用于 Windows 环境下的企业应用。
• PostgreSQL：开源且功能丰富，支持复杂数据类型和高级 SQL 特性，适合科研、地理信息等场景。

二、SQL 数据库语言

SQL（Structured Query Language，结构化查询语言）是操作关系型数据库的 **“通用语言”**，它可以让我们和数据库 “对话”，实现数据的各种操作。

1. SQL 语言介绍

SQL 语法简洁且功能强大，分为四类核心操作语言：

分类	英文全称	作用	示例（MySQL）
DDL	数据定义语言（Data Definition Language）	定义、修改数据库对象（库、表、结构等）	CREATE DATABASE test_db;（创建数据库）
DML	数据操作语言（Data Manipulation Language）	操作表中数据（增、删、改）	INSERT INTO user(name) VALUES('张三');（插入数据）
DQL	数据查询语言（Data Query Language）	查询表中数据	SELECT * FROM user;（查询 user 表所有数据）
DCL	数据控制语言（Data Control Language）	管理权限、事务等	GRANT SELECT ON user TO 'test_user';（授予查询 user 表的权限）

三、MySQL 数据库实战

MySQL 是关系型数据库中的 “明星选手”，我们以它为载体，深入学习数据库的实操技能。

1. MySQL 数据库概念

MySQL 是一款开源的关系型数据库管理系统，它体积小、速度快、成本低，被广泛应用于 Web 开发场景（如电商平台、博客系统、社交 APP 的后端数据存储）。

2. MySQL 数据库的安装卸载

• MySQL 安装：可从官网下载安装包，按向导步骤选择 “典型安装” 或 “自定义安装”，设置 root 用户密码（数据库的超级管理员密码，务必牢记）。
• MySQL 卸载：先通过 “控制面板 - 程序卸载” 移除安装程序，再删除残留的配置文件和数据文件（如C:\ProgramData\MySQL目录）。
• 进行测试：安装完成后，打开 “命令提示符”，输入mysql -u root -p，回车后输入 root 密码，若能成功进入 MySQL 命令行（显示mysql>提示符），则安装成功。

3. 数据库的操作（CRUD）

对数据库本身的 “增删改查”：

• 创建数据库：CREATE DATABASE shop_db CHARACTER SET utf8mb4;（创建名为shop_db的数据库，字符集设为 utf8mb4，支持 emoji 等特殊字符）。
• 查看数据库：SHOW DATABASES;（列出所有数据库）；SHOW CREATE DATABASE shop_db;（查看shop_db的创建详情）。
• 修改数据库：ALTER DATABASE shop_db CHARACTER SET gbk;（修改shop_db的字符集为 gbk）。
• 删除数据库：DROP DATABASE shop_db;（删除shop_db数据库，操作不可逆，谨慎执行）。

4. 数据库的数据类型

MySQL 为不同类型的数据提供了专属 “容器”，常见类型如下：

• 字符串型：CHAR(10)（固定长度字符串，最多 10 个字符）、VARCHAR(20)（可变长度字符串，最多 20 个字符）、TEXT（大文本，如文章内容）。
• 数值型：INT（整数，如年龄）、DECIMAL(10,2)（小数，如价格，总长度 10，小数点后 2 位）。
• 逻辑型：BOOLEAN（布尔值，TRUE或FALSE，底层存储为TINYINT(1)）。
• 日期类型：DATE（日期，如2025-10-23）、DATETIME（日期时间，如2025-10-23 15:30:00）。

5. 表的约束（保证数据完整性）

约束是给表中字段 “加规则”，防止脏数据录入。

• 单表约束：
  • 主键约束：唯一标识一条记录，且值非空。示例：CREATE TABLE user (id INT PRIMARY KEY, name VARCHAR(20));（id是主键，每条用户记录的id唯一）。
  • 唯一约束：字段值唯一，可空。示例：CREATE TABLE user (id INT, phone VARCHAR(11) UNIQUE);（手机号phone不能重复）。
  • 非空约束：字段值必须填写。示例：CREATE TABLE user (id INT, name VARCHAR(20) NOT NULL);（name不能为空）。
• 多表外键约束：建立表与表之间的关联，保证数据的 “参照完整性”。示例：
  2. CREATE TABLE category (
  3. cid INT PRIMARY KEY,
  4. cname VARCHAR(50)
  5. );
  6. CREATE TABLE product (
  7. pid INT PRIMARY KEY,
  8. pname VARCHAR(50),
  9. cid INT,
  10. FOREIGN KEY (cid) REFERENCES category(cid)
  11. );

  复制代码
  这里product的cid是外键，关联category的cid，确保product的cid必须在category中存在。

6. 表结构操作（CRUD）

对表结构的 “增删改查”：

• 创建表：结合数据类型和约束，定义表结构。示例：
  2. CREATE TABLE user (
  3. id INT PRIMARY KEY AUTO_INCREMENT, -- 主键且自增
  4. name VARCHAR(20) NOT NULL,
  5. age INT,
  6. email VARCHAR(50) UNIQUE
  7. );

  复制代码
• 删除和查看表：
  • 删除表：DROP TABLE user;（谨慎执行）。
  • 查看表：SHOW TABLES;（列出数据库中所有表）；DESC user;（查看user表的结构）。
• 修改表：
  • 添加字段：ALTER TABLE user ADD COLUMN gender CHAR(1);（给user表加gender字段）。
  • 修改字段：ALTER TABLE user MODIFY COLUMN age TINYINT;（把age的类型从INT改成TINYINT）。
  • 删除字段：ALTER TABLE user DROP COLUMN email;（删除email字段）。

四、数据的操作（CRUD）

对表中数据的 “增删改查”，是业务开发中最频繁的操作。

1. 插入数据（INSERT）

向表中添加新记录。示例：

2. -- 方式1：指定字段插入
3. INSERT INTO user (name, age, gender) VALUES ('张三', 25, '男');
4. -- 方式2：不指定字段（需按表字段顺序）
5. INSERT INTO user VALUES (NULL, '李四', 30, '女'); -- id自增，所以传NULL

复制代码

2. 修改数据（UPDATE）

更新表中已有记录，必须加 WHERE 条件，否则会修改全表数据！示例：

2. UPDATE user SET age = 26 WHERE name = '张三'; -- 把张三的年龄改成26

复制代码

3. 删除数据（DELETE）

删除表中记录，必须加 WHERE 条件，否则会删除全表数据！示例：

2. DELETE FROM user WHERE id = 2; -- 删除id为2的记录

复制代码

4. 查询数据（SELECT）

从表中检索数据，是 SQL 中最灵活、功能最丰富的操作。

• 基本查询：SELECT name, age FROM user;（查询user表的name和age字段）。
• 查询语句中使用运算和别名：
  2. SELECT name, age + 1 AS next_year_age FROM user; -- 计算“明年年龄”并起别名

  复制代码
• 条件查询：用WHERE加条件（如=、>、LIKE、IN等）筛选数据。示例：
  2. SELECT * FROM user WHERE age > 25 AND gender = '男'; -- 年龄>25的男性用户
  3. SELECT * FROM user WHERE name LIKE '张%'; -- 名字以“张”开头的用户
  4. SELECT * FROM user WHERE age IN (25, 30, 35); -- 年龄是25、30、35的用户

  复制代码
• 排序：用ORDER BY对结果排序（ASC升序，DESC降序，默认升序）。示例：
  2. SELECT * FROM user ORDER BY age DESC, id ASC; -- 先按年龄降序，再按id升序

  复制代码
• 分组：用GROUP BY将数据分组，常结合聚合函数使用。示例：
  2. SELECT gender, COUNT(*) AS user_count FROM user GROUP BY gender; -- 按性别分组，统计每组人数

  复制代码

五、聚合函数

聚合函数是对一组数据进行 “汇总计算”，返回单个结果。

1. 聚合函数概念

它忽略空值（NULL），专注于对有效数据的统计、计算，常用于分组查询（GROUP BY）或整体数据统计。

2. 常见聚合函数

• COUNT()：统计记录数。示例：SELECT COUNT(*) FROM user;（统计 user 表总记录数）；SELECT COUNT(age) FROM user;（统计age字段非空的记录数）。
• SUM()：求和。示例：SELECT SUM(age) FROM user;（统计所有用户年龄的总和）。
• AVG()：求平均值。示例：SELECT AVG(age) FROM user;（统计用户年龄的平均值）。
• MAX()：求最大值。示例：SELECT MAX(age) FROM user;（查询用户的最大年龄）。
• MIN()：求最小值。示例：SELECT MIN(age) FROM user;（查询用户的最小年龄）。

3. 聚合函数的使用

结合分组查询，实现更灵活的统计。示例： 

2. SELECT gender, AVG(age) AS avg_age, MAX(age), MIN(age)
3. FROM user
4. GROUP BY gender;
5. -- 按性别分组，统计每组的平均年龄、最大年龄、最小年龄

复制代码

六、多表查询

当业务数据分散在多张表中时，就需要通过多表查询来整合信息。它主要分为三类：内连接、外连接和子查询。

1. 内连接（INNER JOIN）

内连接只返回两张表中匹配（满足连接条件）的记录，是最常用的多表查询方式。

语法：SELECT 字段 FROM 表1 INNER JOIN 表2 ON 表1.字段 = 表2.字段;

示例：现有user表（用户信息）和order表（订单信息），通过user_id关联，查询用户及其订单：

2. SELECT u.name, o.order_no, o.amount
3. FROM user u
4. INNER JOIN `order` o
5. ON u.user_id = o.user_id;

复制代码

说明：只有存在订单的用户才会被查询出来，无订单的用户不会出现在结果中。

2. 外连接

外连接会返回某张表的所有记录 + 另一张表匹配的记录，分为左外连接、右外连接和全外连接（MySQL 暂不支持全外连接，可通过UNION实现）。

• 左外连接（LEFT OUTER JOIN / LEFT JOIN）：返回左表所有记录，以及右表中匹配的记录；右表无匹配则显示NULL。

  示例：查询所有用户（包括无订单的用户）及其订单：

  2. SELECT u.name, o.order_no, o.amount
  3. FROM user u
  4. LEFT JOIN `order` o
  5. ON u.user_id = o.user_id;

  复制代码

• 右外连接（RIGHT OUTER JOIN / RIGHT JOIN）：返回右表所有记录，以及左表中匹配的记录；左表无匹配则显示NULL。

  示例：查询所有订单（包括无关联用户的异常订单）及用户信息：

  2. SELECT u.name, o.order_no, o.amount
  3. FROM user u
  4. RIGHT JOIN `order` o
  5. ON u.user_id = o.user_id;

  复制代码

3. 子查询

子查询是将一个查询的结果作为另一个查询的条件或数据源，可以嵌套在SELECT、WHERE、FROM等子句中。

• 作为WHERE条件：

  示例：查询 “订单金额大于平均金额” 的订单：

  2. SELECT order_no, amount
  3. FROM `order`
  4. WHERE amount > (SELECT AVG(amount) FROM `order`);

  复制代码

• 作为FROM数据源：

  示例：先统计每个用户的订单总金额，再查询总金额大于 1000 的用户：

  2. SELECT t.user_name, t.total_amount
  3. FROM (
  4. SELECT u.name AS user_name, SUM(o.amount) AS total_amount
  5. FROM user u
  6. INNER JOIN `order` o ON u.user_id = o.user_id
  7. GROUP BY u.user_id
  8. ) t
  9. WHERE t.total_amount > 1000;

  复制代码

七、表设计

表设计是数据库建模的核心，它通过定义表与表之间的关系，确保数据的一致性和可维护性。常见的表关系有四种：一对多、多对多、一对一、多对一（多对一可视为一对多的反向，核心逻辑一致）。

1. 一对多（One-to-Many）

关系说明：A 表中一条记录对应 B 表中多条记录；B 表中一条记录仅对应 A 表中一条记录。

实现方式：在 “多” 的一方的表中，添加 “一” 的一方的主键作为外键。

示例：category（商品分类，一）和product（商品，多）的关系：

• category表：cid（主键）、cname
• product表：pid（主键）、pname、cid（外键，关联category.cid）

这样，一个分类可以对应多个商品，而一个商品只能属于一个分类。

2. 多对多（Many-to-Many）

关系说明：A 表中一条记录对应 B 表中多条记录；B 表中一条记录也对应 A 表中多条记录。

实现方式：需创建一张中间表，存储两张表的主键作为联合外键。

示例：student（学生，多）和course（课程，多）的关系：

• student表：sid（主键）、sname
• course表：cid（主键）、cname
• 中间表student_course：sid（外键，关联student.sid）、cid（外键，关联course.cid）、score（成绩，可选字段）

这样，一个学生可以选多门课程，一门课程也可以被多个学生选择。

3. 一对一（One-to-One）

关系说明：A 表中一条记录仅对应 B 表中一条记录；B 表中一条记录也仅对应 A 表中一条记录。

实现方式：有两种常见方式：

• 方式 1：在其中一张表中添加另一张表的主键作为外键，且该外键设置为唯一。
• 方式 2：两张表的主键相互关联（即主键值完全一致）。

示例：user（用户基本信息，一）和user_detail（用户详细信息，一）的关系：

• 方式 1 实现：
  • user表：uid（主键）、username、password
  • user_detail表：id（主键）、uid（外键，唯一，关联user.uid）、phone、address
• 方式 2 实现：
  • user表：uid（主键）、username、password
  • user_detail表：uid（主键，关联user.uid）、phone、address

一对一关系常用于拆分大表（将不常访问的字段放到另一张表，提升查询性能）。

4. 多对一（Many-to-One）

关系说明：与 “一对多” 逻辑一致，只是视角不同。A 表中多条记录对应 B 表中一条记录，即 B 表（一）对 A 表（多）是 “一对多”，A 表（多）对 B 表（一）是 “多对一”。

实现方式：与 “一对多” 完全相同 —— 在 “多” 的一方添加 “一” 的一方的主键作为外键。

示例：employee（员工，多）和department（部门，一）的关系：

• department表：did（主键）、dname
• employee表：eid（主键）、ename、did（外键，关联department.did）

从员工视角看，多个员工属于一个部门（多对一）；从部门视角看，一个部门有多个员工（一对多）