MySQL 架构、存储引擎、库表操作一站式掌握
目录
- 什么是数据库？
- 认识MySQL
- MySQL架构
- SQL分类
- 存储引擎
- 核心考点补充（期末考试必记）
- 库操作
- 数据库的创建
- 字符集与校验规则
- 三、数据库的修改
- 数据库的删除
- 数据库的备份与恢复
- 查看数据库连接情况
- 表操作
- 表的创建
- 表结构的查看
- 表的修改
- 表的删除
什么是数据库？
文件也可以存储数据，为什么还要有数据库？
- 虽然一般的文件也提供了数据存储的功能，但是没有提供非常好的数据管理能力（在用户角度）。
文件保存数据有以下几个缺点：
- 安全性问题；
- 不利于数据查询和管理；
- 不利于存储海量数据；
- 文件在程序中控制不方便；
数据库的存储介质有磁盘和内存，它能更高效的管理数据。
数据库一般指的是，在磁盘或者内存中存储的特定结构组织的数据（在磁盘上存储的一套数据库方案）。
数据库本质：对数据内容存储的一套解决方案，根据字段或要求，得到想要的结果。
建立数据库，本质就是在Linux下建立一个目录。
在数据库内建表，本质就是在Linux下创建对应的文件。
数据库本质也是文件，只不过这些文件不由我们直接操作，而是由数据库服务帮我们操作。
认识MySQL
MySQL 是一种开源的关系型数据库管理系统，数据以表（Table） 的形式存储，表由 行（记录） 和 列（字段） 组成，通过主键、外键建立表间关联（如一对一、一对多关系），保证数据的一致性和完整性。
- mysql 是数据库服务的客户端；
- mysqld 是数据库服务的服务器端。
MySQL本质是一种基于C（mysql）S（mysqld）模式的网络服务。
登录MySQL：
- 初始基本都是免密码登录，后面必须设置密码；
- 输入密码时不会回显。
MySQL架构
SQL分类
- DDL（data definition language）：数据定义语言，用来维护存储数据的结构。
create、drop、alter等； - DML（data manipulation language）：数据操作语言，用来对数据进行操作。
insert、delete、update等；DML中又单独分了一个DQL，数据查询语言，例如select； - DCL（Data Control Language）：数据控制语言，主要负责权限管理和事务。
grant、revoke、commit等。
存储引擎
- 数据库管理系统如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法，就是存储引擎。
- MySQL的核心就是插件式存储引擎，支持多种存储引擎。
InnoDB vs MyISAM 核心对比：
| 对比维度 | InnoDB | MyISAM |
|---|---|---|
| 事务支持 | 支持 ACID 事务、事务回滚 / 提交 | 不支持事务 |
| 锁机制 | 行级锁（粒度细，高并发友好）（注：查询时也会加意向锁） | 表级锁（粒度粗，写操作阻塞全表） |
| 外键约束 | 支持外键 | 不支持外键 |
| 崩溃恢复 | 支持崩溃恢复（redo/undo 日志） | 不支持，崩溃后易数据损坏 |
| 索引类型 | 聚簇索引（主键索引存储数据，二级索引指向主键） | 非聚簇索引（索引和数据分开存储） |
| 全文索引 | 5.6 版本后支持 | 原生支持全文索引 |
| 存储结构 | 数据 + 索引存储在.ibd 文件 | 数据 (.MYD)、索引 (.MYI)、表结构 (.frm) 分开 |
| 计数效率 | count(*)需全表扫描（无专门计数存储） | count(*)极快（单独存储表行数） |
| 适用场景 | 高并发、需事务 / 外键（如电商订单、金融系统） | 读多写少、无需事务（如日志、报表查询） |
| 数据安全 | 高（事务 + 崩溃恢复） | 低（无事务，崩溃易丢数据） |
核心考点补充（期末考试必记）
库操作
数据库的创建
CREATEDATABASE[IFNOTEXISTS]库名[CHARACTERSET字符集][COLLATE校验规则];IF NOT EXISTS：避免库已存在时报错；CHARACTER SET：指定字符集（如utf8支持中文）；COLLATE：指定字符集的校验规则（如utf8_general_ci不区分大小写）。简单创建：
create database db1;（默认字符集utf8、校验规则utf8_general_ci）；指定字符集：
create database db2 charset=utf8;；指定字符集+校验规则：
create database db3 charset=utf8 collate utf8_general_ci;。
字符集与校验规则
查看系统默认字符集/校验规则：
showvariableslike'character_set_database';showvariableslike'collation_database';查看数据库支持的字符集/校验规则：
showcharset;showcollation;例如
utf8_general_ci（不区分大小写）：创建库时指定该规则，后续表中字符串比较会忽略大小写。
三、数据库的修改
ALTERDATABASE库名[CHARACTERSET新字符集][COLLATE新校验规则];- 仅支持修改字符集和校验规则。
alterdatabasemytestcharset=gbk;-- 将mytest的字符集改为gbk数据库的删除
DROPDATABASE[IFEXISTS]库名;- 说明：删除后库文件及内部表会被级联删除，需谨慎操作。
数据库的备份与恢复
- 语法：
mysqldump -P端口 -u用户 -p密码-B库名>备份文件路径; - 案例：
mysqldump-P3306-uroot-p123456-Bmytest>D:/mytest.sql; - 扩展：
- 备份单表：
mysqldump -u root -p 库名 表名 > 路径;； - 备份多库：
mysqldump -u root -p -B 库1 库2 > 路径;。
- 备份单表：
source 备份文件路径;-- 如：source D:/mytest.sql;- 注意：若备份时未加
-B，需先创建空库，切换到该库后再执行source。
查看数据库连接情况
- 语法：
showprocesslist; - 作用：查看当前连接MySQL的用户、状态，可用于排查异常连接/入侵。
表操作
表的创建
CREATETABLE表名(列名1数据类型,列名2数据类型[COMMENT'注释'])[characterset字符集][collate校验规则][engine存储引擎];- 字符集/校验规则默认继承数据库的配置；
COMMENT可给列加说明。
createtableusers(idint,namevarchar(20)comment'用户名',passwordchar(32)comment'密码是32位的md5值',birthdaydatecomment'生日')charactersetutf8engineMYISAM;表结构的查看
- 语法：
desc 表名; - 示例：
desc users;（展示列名、类型、是否允许空等信息）
表的修改
支持对列、表名的修改，常用操作：
- 新增列：
altertableusersaddassetsvarchar(100);-- 新增assets列 - 修改列类型/长度：
altertableusersmodifynamevarchar(60);-- 将name长度改为60 - 删除列：
altertableusersdroppassword;-- 删除password列（数据会被删除） - 修改表名：
altertableusersrenametoemployee;-- 表名改为employee - 修改列名：
altertableemployee change name xingmingvarchar(60);-- name改为xingming（需指定类型）
表的删除
- 语法：
DROP TABLE [IF EXISTS] 表名; - 示例：
drop table t1; - 说明：删除表会同时删除表结构、数据和索引，需谨慎操作。
本篇文章的分享就到这里了，如果您觉得在本文有所收获，还请留下您的三连支持哦~