第2课: 数据库操作

数据库设计原理

什么是数据库？

数据库是有组织的数据集合，用于存储、管理和检索信息。在MySQL中，数据库是表的容器，每个数据库包含多个相关的表。

数据库的层次结构：

实例（Instance）： 运行的MySQL服务进程
数据库（Database）： 逻辑数据容器
表（Table）： 数据存储的基本单位
行（Row）： 表中的一条记录
列（Column）： 表中的字段

数据库设计原则

单一职责： 每个数据库应该有明确的业务边界，专注于特定的业务领域
数据独立性： 逻辑结构与物理存储分离，使应用程序不受存储结构变化的影响
可扩展性： 设计时考虑未来的扩展需求，包括数据量增长和业务逻辑变化
安全性： 合理的权限管理和数据保护，防止未授权访问和数据泄露
性能优化： 考虑数据访问模式，优化查询性能和存储效率
一致性： 确保数据的准确性和完整性，避免数据冗余和不一致

数据库设计范式

范式理论（Normalization）：

范式是数据库设计的规范，用于减少数据冗余和提高数据一致性。常见的范式包括：

第一范式（1NF）： 确保每列都是原子的，不可再分解
第二范式（2NF）： 在1NF基础上，确保非主键列完全依赖于主键
第三范式（3NF）： 在2NF基础上，确保非主键列不依赖于其他非主键列
BC范式（BCNF）： 在3NF基础上，确保所有决定因素都是候选键

注意： 虽然高范式可以减少数据冗余，但在实际应用中，为了性能考虑，有时会适当反范式化。

数据库范式示意图

创建数据库

基本语法

使用 CREATE DATABASE 语句创建新数据库：

-- 创建数据库
CREATE DATABASE mydb;

-- 创建数据库并指定字符集
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 如果不存在则创建
CREATE DATABASE IF NOT EXISTS mydb;

字符集和校对规则详解

字符集（Character Set）：

utf8mb4： 支持完整的Unicode字符（推荐）
utf8： 只支持基本多文种平面（BMP）字符
latin1： 西方语言字符集
gbk/gb2312： 中文字符集

校对规则（Collation）：

utf8mb4_unicode_ci： 基于Unicode标准，支持多语言
utf8mb4_general_ci： 性能更好，但排序准确性稍差
utf8mb4_bin： 二进制比较，区分大小写

高级创建选项

-- 创建数据库并指定存储引擎默认值
CREATE DATABASE mydb 
DEFAULT CHARACTER SET utf8mb4 
DEFAULT COLLATE utf8mb4_unicode_ci;

-- 创建数据库并设置注释
CREATE DATABASE mydb 
COMMENT '这是一个示例数据库';

-- 查看数据库的完整创建语句
SHOW CREATE DATABASE mydb;

查看数据库信息

系统数据库介绍

MySQL安装后会包含几个系统数据库：

系统数据库功能：

information_schema： 存储数据库元数据信息
mysql： 存储用户权限和系统变量
performance_schema： 性能监控和统计信息
sys： 性能监控的简化视图

查看数据库信息

-- 查看所有数据库（包括系统数据库）
SHOW DATABASES;

-- 查看数据库的详细信息
SELECT * FROM information_schema.SCHEMATA 
WHERE SCHEMA_NAME = 'mydb';

-- 查看数据库创建语句
SHOW CREATE DATABASE mydb;

-- 查看数据库大小和表数量
SELECT 
    table_schema AS '数据库',
    COUNT(*) AS '表数量',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS '大小(MB)'
FROM information_schema.tables 
GROUP BY table_schema;

选择和使用数据库

USE命令详解

在操作表之前，需要先选择要使用的数据库：

-- 选择数据库
USE mydb;

-- 查看当前使用的数据库
SELECT DATABASE();

-- 在连接时直接指定数据库
mysql -u root -p mydb

-- 在SQL语句中显式指定数据库
SELECT * FROM mydb.table_name;

数据库上下文管理

最佳实践：

在脚本开头使用USE database_name;明确指定数据库
避免在SQL语句中硬编码数据库名
使用连接池时，在连接字符串中指定数据库
定期检查当前数据库上下文，避免误操作

修改数据库属性

ALTER DATABASE命令

使用ALTER DATABASE可以修改数据库的属性：

-- 修改数据库字符集
ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 修改数据库注释
ALTER DATABASE mydb COMMENT '修改后的数据库描述';

-- 查看修改后的数据库信息
SHOW CREATE DATABASE mydb;

修改限制和注意事项

重要提醒：

修改字符集不会影响已有表，只会影响新创建的表
要修改现有表的字符集，需要单独对每个表执行ALTER TABLE
修改数据库属性需要相应的权限
在生产环境中修改数据库属性前务必进行测试

删除数据库

DROP DATABASE命令

警告： 删除数据库会永久删除其中的所有表和数据，操作前请务必备份！

-- 删除数据库
DROP DATABASE mydb;

-- 如果存在则删除
DROP DATABASE IF EXISTS mydb;

-- 删除前确认数据库内容
SHOW TABLES FROM mydb;
SELECT COUNT(*) FROM information_schema.tables 
WHERE table_schema = 'mydb';

删除操作的安全措施

安全删除流程：

备份重要数据
确认数据库名称和内容
停止所有对该数据库的访问
执行删除操作
验证删除结果

数据库备份和恢复

-- 备份数据库（命令行）
mysqldump -u root -p mydb > mydb_backup.sql

-- 恢复数据库
mysql -u root -p mydb < mydb_backup.sql

-- 备份所有数据库
mysqldump -u root -p --all-databases > all_databases_backup.sql

数据库命名规范和最佳实践

命名规则

字符限制： 只能包含字母、数字、下划线
开头字符： 不能以数字开头，建议使用字母开头
大小写： 建议使用小写字母，避免大小写混淆
长度限制： 数据库名最长64个字符
保留字： 避免使用MySQL保留关键字

命名最佳实践

推荐的命名模式：

业务导向： shop_db（电商）、blog_system（博客系统）
环境标识： shop_dev（开发）、shop_prod（生产）
版本管理： shop_v1、shop_v2（版本控制）
团队标识： team_a_shop、team_b_shop（多团队）

命名反模式

应该避免的命名方式：

使用无意义的名称：db1、test123
使用特殊字符：my-db、my.db
使用空格：my database
使用中文或特殊Unicode字符
使用过长的名称

数据库设计考虑因素

业务边界： 根据业务模块划分数据库
数据量预估： 预估数据增长规模
性能要求： 读写频率、并发量
安全需求： 数据敏感性、访问控制
备份策略： 备份频率、恢复时间目标

实践练习：数据库设计与操作

第一部分：基础操作

创建业务数据库： 创建一个名为 ecommerce 的数据库，使用utf8mb4字符集
验证创建： 使用 SHOW DATABASES 查看是否创建成功
选择数据库： 使用 USE ecommerce 选择该数据库
确认上下文： 使用 SELECT DATABASE() 确认当前数据库
查看详细信息： 使用 SHOW CREATE DATABASE ecommerce 查看数据库创建语句

第二部分：系统探索

探索系统数据库： 查看 information_schema 数据库中的表结构
数据库统计： 查询所有数据库的大小和表数量统计
字符集验证： 确认新创建数据库的字符集设置是否正确

第三部分：高级操作

创建测试数据库： 创建 test_backup 数据库用于备份测试
修改属性： 为测试数据库添加注释说明
备份操作： 学习使用 mysqldump 备份数据库
安全删除： 在确认备份后，安全删除测试数据库

第四部分：设计思考

命名规范： 为以下业务场景设计合适的数据库名称：
- 在线教育平台
- 医院管理系统
- 物流跟踪系统
架构设计： 思考如何为大型电商系统设计数据库架构

学习要点：

掌握数据库创建、选择、修改、删除的基本操作
理解字符集和校对规则的重要性
熟悉系统数据库的结构和功能
掌握数据库备份和恢复的基本方法
培养数据库设计的思维模式

常见问题与解决方案

问题1：数据库创建失败

可能原因： 权限不足、数据库名冲突、字符集不支持

解决方案： 检查用户权限、确认数据库名唯一性、验证字符集支持

问题2：字符集乱码

可能原因： 客户端字符集不匹配、数据库字符集设置错误

解决方案： 统一字符集设置、使用utf8mb4字符集、检查连接参数

问题3：数据库无法删除

可能原因： 权限不足、数据库正在被使用、存在锁定

解决方案： 检查权限、终止相关连接、重启MySQL服务

上一课下一课