我要评论一、数据库的创建
1. 语法格式
create database [if not exists] db_name [create_specification [, create_specification] ...] create_specification: [default] character set charset_name [default] collate collation_name
2. 参数说明
- 大写单词为 sql 关键字，实际编写不强制大小写，但规范建议大写
[]表示可选项，编写时可省略if not exists：仅当数据库不存在时执行创建，若已存在则不执行、不报错character set / charset：指定数据库的字符集，决定数据的存储编码格式collate：指定字符集的校验规则，决定数据比较、排序、查询时的编码比对规则
3. 创建案例
创建名为 db1 的数据库，使用默认字符集和校验规则：
create database db1;
执行结果：
query ok, 1 row affected (0.01 sec)
未指定时，mysql 默认字符集为 utf8，默认校验规则为 utf8_general_ci。
创建使用 utf8 字符集的 db2 数据库：
create database db2 charset=utf8;
执行结果：
query ok, 1 row affected (0.00 sec)
创建使用 utf8 字符集、utf8_general_ci 校验规则的 db3 数据库：
create database db3 charset=utf8 collate utf8_general_ci;
执行结果
query ok, 1 row affected (0.01 sec)
创建完数据库后，可以通过 show databases; 查看当前已创建的所有数据库：
show databases;
执行结果：
+--------------------+ | database | +--------------------+ | information_schema | | db1 | | db2 | | db3 | | mysql | | performance_schema | | sys | +--------------------+ 7 rows in set (0.00 sec)
创建好的数据库会在 mysql 配置文件指定的数据目录下新建同名目录文件，一个数据库对应 linux 下的一个目录文件，默认路径为 /var/lib/mysql。删除数据库本质上就是删除该目录，因此尽量不使用 linux 的 mkdir 和 rmdir 指令来操作数据库目录，应始终通过 sql 语句完成。
4. 底层存储原理
在 linux 系统中，mysql 数据库本质对应文件系统中的一个目录，默认存储路径为 /var/lib/mysql。创建数据库等价于在该路径下新建同名目录，目录内会生成一个 .opt 文件，用于记录该数据库的字符集和校验规则配置。不建议直接通过 linux 命令创建 / 删除数据库目录，应通过 sql 语句操作。
二、字符集与校验规则
1. 核心概念
创建数据库的时候，有两套配套的编码规则，必须一一对应：
- 数据库字符集：定义数据存储时的编码格式，决定支持哪些语言文字
- 数据库校验集：定义数据查询、比较、排序时的读取和比对规则
所有数据库操作都必须保证编码格式一致，否则会出现乱码或比对异常。数据库内的数据表会默认继承数据库的字符集和校验规则，无需重复设置。
2. 常用查看命令
查看当前数据库的默认字符集：
show variables like 'character_set_database';
查看当前数据库的默认校验规则：
show variables like 'collation_database';
查看 mysql 支持的所有字符集：
show charset;
查看 mysql 支持的所有字符集校验规则：
show collation;
3. 校验规则的实际影响
以 utf8 字符集的两种常见校验规则为例：
utf8_general_ci：不区分大小写（ci = case insensitive）utf8_bin：区分大小写（bin = binary，按二进制值精确比对）
案例对比查询结果差异：
- 不区分大小写（utf8_general_ci）：查询
name='a'会同时返回 a 和 a - 区分大小写（utf8_bin）：查询
name='a'仅返回 a
排序结果差异：
- 不区分大小写排序：a, a, b, b（同字母大小写排在一起）
- 区分大小写排序：a, b, a, b（按 ascii 码值排序，大写字母码值更小）
三、数据库的管理操作
1. 查看所有数据库
show databases;
执行结果：
+--------------------+ | database | +--------------------+ | information_schema | | db1 | | db2 | | db3 | | mysql | | performance_schema | | sys | +--------------------+ 7 rows in set (0.00 sec)
2. 查看数据库的创建语句
注意：不是创建数据库，而是展示创建数据库时候的命令。
show create database 数据库名;
示例：
show create database db1;
执行结果：
+----------+------------------------------------------------------------------+ | database | create database | +----------+------------------------------------------------------------------+ | db1 | create database `db1` /*!40100 default character set utf8 */ | +----------+------------------------------------------------------------------+ 1 row in set (0.00 sec)
补充：查看当前所在的数据库
select database();
执行结果：
+------------+ | database() | +------------+ | db1 | +------------+ 1 row in set (0.00 sec)
结果说明：
- 数据库名两侧的反引号
`：用于规避数据库名与 sql 关键字冲突 /*!40100 ... */：不是普通注释，是 mysql 版本条件语法 —— 当 mysql 版本高于 4.01 时，才执行内部的语句，类似编程语言的条件编译。
3. 修改数据库
语法格式：
alter database db_name [alter_specification [, alter_specification] ...] alter_specification: [default] character set charset_name [default] collate collation_name
修改操作仅支持修改数据库的字符集和校验规则，无法修改数据库名。
示例：将 mytest 数据库字符集改为 gbk
alter database mytest charset=gbk;
执行结果：
query ok, 1 row affected (0.00 sec)
验证修改结果：
show create database mytest;
执行结果：
+----------+------------------------------------------------------------------+ | database | create database | +----------+------------------------------------------------------------------+ | mytest | create database `mytest` /*!40100 default character set gbk */ | +----------+------------------------------------------------------------------+ 1 row in set (0.00 sec)
4. 删除数据库
语法格式：
drop database [if exists] db_name;
示例：
drop database db3;
执行结果：
query ok, 0 rows affected (0.01 sec)
验证删除结果：
show databases;
执行结果：
+--------------------+ | database | +--------------------+ | information_schema | | db1 | | db2 | | mysql | | performance_schema | | sys | +--------------------+ 6 rows in set (0.00 sec)
if exists：仅当数据库存在时执行删除，不存在则不报错- 删除效果：数据库从 mysql 中消失，对应磁盘目录被级联删除，库内所有表和数据一并删除，操作不可逆，需谨慎使用
四、数据库备份与恢复
1. 逻辑备份（mysqldump 工具）
mysqldump 是 mysql 自带的客户端工具，必须在操作系统终端（shell/cmd）执行，不能在 mysql 命令行内运行。这个命令在我们下载 mysql 的时候就安装好了的。备份本质是导出所有建库、建表、插入数据的 sql 语句，保存为 .sql 文件。注意：这个备份不止备份了数据库里面的数据，还备份了我们以往对该数据库的有效操作，一起备份形成了一个 .sql 文件，在进行恢复的时候就把这个 .sql 文件里面记录的操作再执行一遍。
常用备份场景：
备份单个完整数据库（带 -b 参数）：
mysqldump -p3306 -u root -p密码 -b 数据库名 > 备份文件路径.sql
示例：
mysqldump -p3306 -u root -p123456 -b mytest > d:/mytest.sql
执行结果（终端无额外输出，备份文件已生成）：
# 查看备份文件内容（部分） d:\> type d:/mytest.sql | head -20 -- mysql dump 10.13 distrib 8.0.33, for win64 (x86_64) -- -- host: localhost database: mytest -- ------------------------------------------------------ -- server version 8.0.33 /*!40101 set @old_character_set_client=@@character_set_client /; /!40101 set @old_character_set_results=@@character_set_results /; /!40101 set @old_collation_connection=@@collation_connection /; /!50503 set names utf8mb4 /; /!40103 set @old_time_zone=@@time_zone /; /!40103 set time_zone='+00:00' /; /!40014 set @old_unique_checks=@@unique_checks, unique_checks=0 /; /!40014 set @old_foreign_key_checks=@@foreign_key_checks, foreign_key_checks=0 /; /!40101 set @old_sql_mode=@@sql_mode, sql_mode='no_auto_value_on_zero' /; /!40111 set @old_sql_notes=@@sql_notes, sql_notes=0 */;
备份数据库中的指定表：
mysqldump -u root -p密码 数据库名 表名1 表名2 > 备份文件路径.sql
同时备份多个数据库：
mysqldump -u root -p密码 -b 数据库名1 数据库名2 > 备份文件路径.sql
-b 参数的区别：
- 带 -b：备份文件中包含
create database语句，恢复时无需提前建库 - 不带 -b：备份文件不含建库语句，恢复前必须先手动创建空数据库并切换到该库
2. 数据恢复
在 mysql 命令行中执行：
source 备份文件的绝对路径;
示例：
source d:/mytest.sql;
执行结果：
query ok, 0 rows affected (0.00 sec) query ok, 0 rows affected (0.00 sec) ... query ok, 1 row affected (0.01 sec) query ok, 1 row affected (0.00 sec) ... 恢复完成后，查看数据库确认 mysql> show databases; +--------------------+ | database | +--------------------+ | information_schema | | mytest | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec)
恢复本质是依次执行备份文件中的所有 sql 语句，重建库、表和数据。
3. 物理备份方式
由于数据库本质是磁盘目录，也可以直接拷贝数据库目录到目标路径完成备份 / 恢复，属于物理层面的备份。生产环境更推荐使用 mysqldump 等逻辑备份方式，兼容性和可控性更强。
五、查看数据库连接状态
show processlist;
执行结果：
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+ | id | user | host | db | command | time | state | info | +----+-----------------+-----------+------+---------+-------+------------------------+------------------+ | 5 | root | localhost | db1 | query | 0 | init | show processlist | | 6 | event_scheduler | localhost | null | daemon | 12345 | waiting on empty queue | null | +----+-----------------+-----------+------+---------+-------+------------------------+------------------+ 2 rows in set (0.00 sec)
该命令可以查看当前所有连接到 mysql 的客户端信息，包括连接 id、用户名、客户端地址、正在执行的命令、运行时长等。可用于：
- 排查异常连接，判断数据库是否被非法访问
- 数据库性能变慢时，可通过该命令查看是否有长耗时的异常连接占用资源
到此这篇关于mysql 数据库基础操作知识点整理(最新推荐)的文章就介绍到这了,更多相关mysql数据库基础操作内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论