一、MySQL概述

一、数据库知识总览

---

二、数据库基本概念

1、数据库（Database）

数据库是存储和管理数据的容器，由一组相关的表组成。一个 MySQL 实例可以管理多个数据库。

2、数据库管理系统（DBMS）

数据库管理系统是一种操纵和管理数据库的大型软件。它位于用户（或应用程序）与操作系统之间，提供了一套完整的工具来创建、使用和维护数据库。

---

三、核心组件与概念

1、表（Table）

表是数据的结构化存储形式，由行（记录）和列（字段）组成，类似于 Excel 表格。每个表具有唯一的名称，并定义列的数据类型和约束。

2、SQL（Structured Query Language）

SQL 是与 MySQL 交互的标准语言，用于查询、插入、更新和删除数据。主要分为：

• DDL（数据定义语言）：定义数据库结构，如 CREATE、ALTER、DROP。
• DML（数据操作语言）：操作数据，如 SELECT、INSERT、UPDATE、DELETE。
• DCL（数据控制语言）：控制访问权限，如 GRANT、REVOKE。

3、索引（Index）

索引是提高查询效率的数据结构。常见类型包括：

索引类型	特点	用途
主键索引	唯一标识每行记录，不允许 NULL 值	确保数据行的唯一性，是表的主键
唯一索引	确保列值唯一，允许 NULL 值	防止数据重复，如用户邮箱、身份证号
普通索引	加速查询，允许重复值和 NULL 值	提高非唯一列的查询速度

4、事务（Transaction）

事务是一组操作，遵循 ACID 原则：

• 原子性（Atomicity）：事务中的所有操作要么全部完成，要么全部回滚。
• 一致性（Consistency）：事务执行前后，数据库从一个一致状态转换到另一个一致状态。
• 隔离性（Isolation）：并发执行的事务互不干扰。
• 持久性（Durability）：事务提交后，对数据的修改是永久性的。

5、存储引擎（Storage Engine）

MySQL 支持多种存储引擎，负责数据的存储、处理和检索。常见的有：

存储引擎	特点	适用场景
InnoDB	支持事务、行级锁和外键，提供高可靠性	需要事务支持、数据一致性要求高的应用（如电商、金融）
MyISAM	不支持事务，但插入和查询速度快，表级锁	读密集型、对事务要求不高的场景（如日志分析、数据仓库）
MEMORY	数据存储在内存中，速度极快，重启后数据丢失	临时数据存储、高速缓存

6、视图（View）

视图是基于 SQL 查询的虚拟表，可用于简化复杂查询或隐藏敏感数据。

1. -- 示例：创建一个视图，隐藏员工的薪资信息
2. CREATE VIEW employee_public AS
3. SELECT id, name, department, hire_date
4. FROM employees;

复制代码

7、触发器（Trigger）

触发器是在特定事件（如 INSERT、UPDATE、DELETE）发生时自动执行的存储过程。

1. -- 示例：在插入新订单后，自动更新库存
2. CREATE TRIGGER update_inventory AFTER INSERT ON orders
3. FOR EACH ROW
4. BEGIN
5. UPDATE products
6. SET stock = stock - NEW.quantity
7. WHERE id = NEW.product_id;
8. END;

复制代码

8、存储过程（Stored Procedure）

存储过程是预编译的 SQL 语句集合，可重复调用，能够减少网络传输并提高应用程序的性能和可维护性。

1. -- 示例：一个简单的存储过程，根据用户ID获取订单总数
2. DELIMITER //
3. CREATE PROCEDURE GetOrderCount(IN userId INT, OUT orderCount INT)
4. BEGIN
5. SELECT COUNT(*) INTO orderCount FROM orders WHERE user_id = userId;
6. END //
7. DELIMITER ;

复制代码

9、外键（Foreign Key）

外键用于建立表与表之间的关联，确保数据的引用完整性。

1. -- 示例：在 orders 表中创建外键，关联 users 表
2. ALTER TABLE orders
3. ADD CONSTRAINT fk_user
4. FOREIGN KEY (user_id) REFERENCES users(id)
5. ON DELETE CASCADE;

复制代码

---

四、数据库类型

1、关系型数据库

基于关系模型，以二维表（行和列）的形式存储数据，支持标准的 SQL 语言，强调数据的一致性和完整性。

代表产品	主要特点
MySQL	开源、流行、性能优异，适用于 Web 应用
Oracle	功能强大、稳定性高，常用于大型企业级应用
PostgreSQL	开源、支持高级特性（如 JSON、GIS），扩展性强
SQL Server	微软出品，与 .NET 生态集成紧密

2、非关系型数据库

不依赖固定的表结构，采用灵活的数据模型，适用于处理大规模、高并发和半结构化/非结构化数据。它进一步细分为：

• 键值（Key-Value）数据库：以键值对形式存储，读写速度极快，常用于缓存。代表：Redis。
• 文档数据库：以文档（如 JSON 格式）存储，适合存储不规则数据。代表：MongoDB。
• 图数据库：以节点和边的形式存储，擅长处理复杂的关系网络。代表：Neo4j。
• 列族数据库：以列族方式组织数据，适合海量数据的分布式存储。代表：HBase、Cassandra。

五、MySQL数据类型

1、数值类型 (Numeric Types)

数值类型用于存储数字数据，主要分为整数、浮点数和定点数。

整数类型

用于存储没有小数部分的数字。随着存储空间的增加，可表示的数值范围也随之扩大。

类型	存储空间	有符号范围	无符号范围	描述
TINYINT	1 字节	-128 到 127	0 到 255	极小整数
SMALLINT	2 字节	-32,768 到 32,767	0 到 65,535	小整数
MEDIUMINT	3 字节	-8,388,608 到 8,388,607	0 到 16,777,215	中等大小整数
INT / INTEGER	4 字节	-2,147,483,648 到 2,147,483,647	0 到 4,294,967,295	标准整数（最常用）
BIGINT	8 字节	-9,223,372,036,854,775,808 到 9,223,372,036,854,775,807	0 到 18,446,744,073,709,551,615	大整数，用于存储极大的数值（如全局唯一ID）

浮点数类型

用于存储带有小数部分的近似数值，适合科学计算等对绝对精度要求不那么极端的场景。

类型	存储空间	描述
FLOAT	4 字节	单精度浮点数，精度约 7 位有效数字
DOUBLE	8 字节	双精度浮点数，精度约 15 位有效数字

定点数类型

用于存储精确的小数值，底层以字符串形式存储，不会出现精度丢失。

类型	语法	描述
DECIMAL	DECIMAL(M, D)	精确数值，M 是总位数（精度），D 是小数位数（标度）。常用于金融、货币等对精度要求极高的字段。

1. -- 示例：创建表时使用不同的数值类型
2. CREATE TABLE products (
3. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -- 标准整数，无符号自增主键
4. price DECIMAL(10, 2), -- 精确价格，总10位，小数2位
5. weight FLOAT, -- 近似重量
6. stock SMALLINT UNSIGNED -- 无符号小整数表示库存
7. );

复制代码

2、字符串类型 (String Types)

字符串类型用于存储文本数据，根据长度是否固定和存储方式的不同进行分类。

定长字符串

• CHAR(M)：固定长度字符串。无论实际内容多长，都会占用 M 个字符的空间。优点是检索速度快，适合存储长度固定的数据（如身份证号、MD5密码、性别代码）。

变长字符串

• VARCHAR(M)：可变长度字符串。只占用实际字符长度加 1 或 2 个字节的额外空间。节省存储空间，是日常开发中最常用的字符串类型（如用户名、邮箱、地址）。

大文本类型

• TEXT：用于存储长文本（最大 65,535 字节）。适合存储文章正文、长篇评论等。
• LONGTEXT：用于存储超大文本（最大 4GB）。

二进制字符串

• BLOB：用于存储二进制大对象，如图片、音频、视频文件等。

类型	最大长度	描述
CHAR(M)	M 字符，M 范围 0-255	定长字符串，不足部分用空格填充
VARCHAR(M)	M 字符，M 范围 0-65,535	变长字符串，按实际长度存储
TEXT	65,535 字节	长文本数据
LONGTEXT	4 GB	超大文本数据
BLOB	65,535 字节	二进制大对象

1. -- 示例：创建表时使用不同的字符串类型
2. CREATE TABLE users (
3. id INT PRIMARY KEY,
4. username VARCHAR(50) NOT NULL, -- 变长用户名
5. id_card CHAR(18), -- 定长身份证号
6. bio TEXT, -- 长文本个人简介
7. avatar BLOB -- 二进制存储头像
8. );

复制代码

3、日期和时间类型 (Date and Time Types)

用于记录时间相关的数据。

日期类型

• DATE：仅表示日期，格式为 YYYY-MM-DD。

时间类型

• TIME：仅表示时间，格式为 HH:MM:SS。

日期时间类型

• DATETIME：表示日期和时间，格式为 YYYY-MM-DD HH:MM:SS。占用 8 字节，与时区无关。
• TIMESTAMP：表示时间戳，格式同 DATETIME，但占用 4 字节。其值会根据时区进行转换，且默认值通常与系统当前时间绑定，适合记录数据的创建或更新时间。

年份类型

• YEAR：仅表示年份，格式为 YYYY。

类型	格式	范围	描述
DATE	YYYY-MM-DD	‘1000-01-01’ 到 ‘9999-12-31’	日期
TIME	HH:MM:SS	‘-838:59:59’ 到 ‘838:59:59’	时间
DATETIME	YYYY-MM-DD HH:MM:SS	‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’	日期和时间，与时区无关
TIMESTAMP	YYYY-MM-DD HH:MM:SS	‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC	时间戳，受时区影响
YEAR	YYYY	1901 到 2155	年份

1. -- 示例：创建表时使用日期时间类型
2. CREATE TABLE orders (
3. order_id INT PRIMARY KEY,
4. order_date DATE, -- 订单日期
5. delivery_time TIME, -- 预计送达时间
6. created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 创建时间（自动记录）
7. updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- 更新时间（自动更新）
8. warranty_year YEAR -- 保修年份
9. );

复制代码