MySQL 数据库基础：概念、架构与核心使用指南

MySQL 数据库基础：概念、架构与核心使用指南

1. 数据库概念

存储数据用文件就可以了，为什么还要弄个数据库？

文件保存数据存在以下局限性：

• 安全性问题：文件权限控制较弱。
• 查询与管理困难：海量数据检索效率低。
• 程序控制不便：在应用层直接操作文件系统耦合度高。

数据库的存储介质主要包括磁盘和内存。为了解决上述问题，数据库管理系统（DBMS）应运而生，它能更高效地管理数据。数据库的水平是衡量一个程序员水平的重要指标。

2. 当前主流的数据库

数据库名称	描述
SQL Server	微软产品，.Net 程序员的最爱，适用于中大型项目。
Oracle	甲骨文产品，适合大型项目，复杂的业务逻辑，并发性能通常不如 MySQL。
MySQL	世界上最受欢迎的数据库，属于甲骨文旗下，并发性好，不适合做复杂的业务。主要用在电商、SNS、论坛。对简单的 SQL 处理效果好。
PostgreSQL	加州大学伯克利分校计算机系开发的关系型数据库，支持私用、商用及学术研究，可免费使用、修改和分发。
SQLite	轻型的嵌入式数据库，遵守 ACID 原则，占用资源极低，适合嵌入式设备。
H2	Java 开发的嵌入式数据库，作为类库可直接嵌入应用项目。

3. MySQL 的基本使用

3.1 MySQL 的安装

安装方式因操作系统而异，常见的有源码编译、包管理器安装或图形化安装工具。建议根据具体环境选择合适的安装文档进行配置。

3.2 连接服务器

命令示例：

mysql -h127.0.0.1 -P3306 -u root -p

输出示例：

Enter password: ****
Welcome to the MySQL monitor...
Your MySQL connection id is 2
Server version: 5.7.21-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help.
mysql>

注意：

• 如果不写 -h 127.0.0.1，默认连接本地主机。
• 如果不写 -P 3306，默认连接 3306 端口号。

3.3 服务器管理

在 Windows 系统中，可通过 Win + R 输入 services.msc 打开服务管理器，通过左侧按钮进行服务的停止、暂停或重启操作。

3.4 服务器、数据库、表关系

• 数据库服务器：安装了数据库管理系统程序的机器，该程序可管理多个数据库。
• 数据库：开发人员通常针对每一个应用创建一个独立的数据库。
• 表：为保存应用中实体的数据，在数据库中创建多个表。

三者层级关系为：服务器 > 数据库 > 表。

3.5 使用案例

以下是创建数据库、表及插入数据的完整流程：

1. 创建数据库

   CREATE DATABASE helloworld;
   

2. 使用数据库

   USE helloworld;
   

3. 创建数据库表

   CREATE TABLE student (
       id INT,
       name VARCHAR(32),
       gender VARCHAR(2)
   );
   

4. 表中插入数据

   INSERT INTO student (id, name, gender) VALUES (1, '张三', '男');
   INSERT INTO student (id, name, gender) VALUES (2, '李四', '女');
   INSERT INTO student (id, name, gender) VALUES (3, '王五', '男');
   

5. 查询表中的数据

   SELECT * FROM student;
   

3.6 数据逻辑存储

数据在物理上存储在磁盘文件中，但在逻辑上通过页、区等结构进行管理，以优化读写性能。

4. MySQL 架构

MySQL 是一个可移植的数据库，几乎能在所有主流操作系统上运行（Unix/Linux、Windows、Mac、Solaris）。尽管底层实现各异，但 MySQL 保证了各平台物理体系结构的一致性。

其架构主要分为三层：

• 连接层：负责处理客户端连接、认证及安全。
• 服务层：包含缓存、SQL 接口、解析器、优化器等核心功能。
• 引擎层：负责数据的存储和提取，支持插件式存储引擎。

5. SQL 的分类

5.1 SQL 分类：三大类

• DDL (Data Definition Language)：数据定义语言，用于维护存储数据的结构。代表指令：CREATE, DROP, ALTER。
• DML (Data Manipulation Language)：数据操纵语言，用于对数据进行操作。代表指令：INSERT, DELETE, UPDATE。其中 SELECT 常被单独划分为 DQL。
• DCL (Data Control Language)：数据控制语言，主要负责权限管理和事务。代表指令：GRANT, REVOKE, COMMIT。

5.2 MySQL SQL 语句分类详解表

分类	核心指令	核心功能	权威定义与理论依据	在 MySQL 中的关键特性
DDL (Data Definition Language)	CREATE, ALTER, DROP, TRUNCATE, RENAME	用于定义和管理数据库对象（如数据库、表、索引、视图等）的结构。	对应数据库三级模式结构的定义，描述数据库'骨架'。不操作数据本身，只操作容器。	隐式提交：执行前自动提交当前事务，不可回滚。
DML (Data Manipulation Language)	INSERT, UPDATE, DELETE	用于对数据库表中的数据进行增、删、改的操作。	用户与数据交互的接口，实现了对存储数据的操作。	需要提交：仅修改内存数据，需 COMMIT 确认或 ROLLBACK 撤销。
DQL (Data Query Language)	SELECT	专指用于查询数据的语言。	虽广义属 DML，但因无副作用被单独划分，便于理解和优化。	读操作，不触发隐式提交。
DCL (Data Control Language)	GRANT, REVOKE	用于控制数据库的访问权限和安全级别。	安全模型的重要组成部分，定义用户对对象的访问许可。	同样会触发隐式提交，立即生效。

5.3 补充说明：关于事务控制

在更精确的 SQL 标准划分中，用于管理事务的语句（如 COMMIT, ROLLBACK, SAVEPOINT）被单独划分为 TCL (Transaction Control Language)。

• TCL 的作用：确保被 DML 语句影响的表的数据一致性和完整性，将多个操作绑定为原子性单元。
• 与 DCL 的区别：DCL 管理'谁'有权限'做什么'，而 TCL 管理'做的结果'如何'永久保存或撤销'。

6. 存储引擎

6.1 存储引擎

存储引擎定义了数据库如何存储数据、建立索引以及更新和查询数据的技术方法。

MySQL 的核心就是插件式存储引擎，支持多种存储引擎。

6.2 查看存储引擎

SHOW ENGINES;

6.3 存储引擎对比

不同的存储引擎在事务支持、锁机制、性能等方面各有优劣，选择时需根据业务场景权衡。

7. MySQL 完整使用流程：创建学生表

7.1 使用场景

在实际开发中，我们需要从创建数据库开始，逐步构建数据结构。

终端操作演示：

mysql> CREATE DATABASE helloworld;
Query OK, 1 row affected (0.01 sec)

mysql> USE helloworld;
Database changed

mysql> CREATE TABLE student (
    -> id INT,
    -> name VARCHAR(32),
    -> gender VARCHAR(2)
    ->);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO student (id, name, gender) VALUES (1, '张三', '男');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM student;
+------+--------+--------+
| id   | name   | gender |
+------+--------+--------+
| 1    | 张三   | 男     |
+------+--------+--------+

7.2 MySQL 中的关键字大小写规范

MySQL 中的关键字（如 SELECT, DROP, CREATE 等）不区分大小写。

这意味着以下写法完全等价：

• DROP TABLE IF EXISTS student;
• drop table if exists student;
• Drop Table If Exists student;

7.2.1 为什么很多人喜欢用大写？

• 提高可读性：关键字大写，标识符小写，一眼区分 SQL 结构和自定义内容。
• 团队规范：保持代码风格一致。
• 习惯传承：早期教材和工具的惯例。

7.2.2 需要注意的大小写问题（表名、列名）

虽然关键字不敏感，但数据库、表、字段等标识符的大小写敏感性取决于：

• 操作系统：Linux 下区分大小写（文件系统），Windows/macOS 通常不区分。
• MySQL 配置：参数 lower_case_table_names 会影响处理逻辑。

建议：

• 统一使用小写命名表名和字段名（如 student, user_name）。
• 若混用大小写，建议使用反引号包裹，例如 `Student`。

7.2.3 结论

• SQL 关键字不必大写，但推荐大写以保持清晰。
• 表名、列名等建议统一小写，遵循团队规范。

8. 总结

掌握数据库基础是后端开发的核心能力之一。理解数据库概念、熟悉 SQL 分类、掌握存储引擎特性，并结合实际场景进行练习，能够帮助你构建稳固的数据处理能力。