【MySQL】数据库的相关操作

MySQL数据库的操作

• MySQL数据库的操作
• github地址
• 0. 前言
• 1. 查看数据库
• 2. 创建和删除数据库
  • 创建
  • 删除
• 3. 数据库的编码
  • 相同编码的必要性
  • 字符集和校验规则
    • 查看系统默认字符集和校验规则
    • 查看数据库支持的所有字符集和字符集校验规则
  • 创建指定编码的数据库
  • 不同校验规则对数据库的影响
    • 不区分大小写
    • 区分大小写
    • 结果差异
• 4. 操纵数据库
  • 修改数据库
  • 查看创建数据库时使用的命令
• 5. 数据库的备份与恢复
  • 备份
  • 恢复
• 6. 查看数据库的连接情况
• 7. 结语

MySQL数据库的操作

github地址

有梦想的电信狗

0. 前言

在学习 MySQL 的过程中，很多初学者往往只停留在“能用”的层面，却忽略了数据库底层的一些行为特征。
例如：创建数据库时究竟发生了什么？删除数据库为什么如此危险？字符集与校验规则对结果有什么影响？

本篇文章将从最基础的数据库操作讲起，带你系统地理解 MySQL 中数据库的查看、创建、删除、修改、编码设置、备份与恢复等操作。
同时结合 Linux 实操演示，帮助你建立数据库操作与系统层面行为的映射关系，从“命令使用”上升到“原理理解”，打好数据库管理的根基。

如果你想在今后的开发中更加自如地管理数据库、避免误操作带来的严重后果，那么这篇文章将非常适合你。

小提示：

MySQL 建议我们关键字使用大写，但是不是必须的

1. 查看数据库

查看当前用户所能查看的数据库的列表：

• sql语法：

showdatabases;

查看当前正在使用的是哪个数据库：

sql语法：

use db_name;# 使用名为 db_name 的数据库 可以将其和 Linux 中的 cd 命令 做类比selectdatabase();# 查看当前正在使用的是哪个数据库 可以将其和 Linux 中的 pwd 命令 做类比

2. 创建和删除数据库

• 创建数据库：在Linux系统中，创建数据库本质是在数据库的存储目录下(当前ubuntu 22.04下为/var/lib/mysql) 创建一个目录
• 删除数据库：在Linux系统中，删除数据库本质是数据库的存储目录下(当前ubuntu 22.04下为/var/lib/mysql) 删除一个目录
  • 注意：不能通过手动在数据库的存储目录下(当前ubuntu 22.04下为/var/lib/mysql) 创建或删除目录的方式创建或删除数据库，应该通过sql命令创建和删除

创建

sql语法：

CREATEDATABASE[IFNOTEXISTS] db_name [create_specification [,create_specification]...] create_specification: [DEFAULT]CHARACTERSET charset_name [DEFAULT]COLLATE collation_name 

说明：

• 大写的内容表示关键字
• [] 中的内容是可选项
• CHARACTER SET：指定创建的数据库采用的字符编码集
• COLLATE：指定创建的数据库字符集的校验规则

创建数据库实操：

# 创建名为 d1 的数据库， 以下两种写法等同createdatabase d1;createdatabaseifnotexists d1;

删除

• sql语法：

DROPDATABASE[IFEXISTS] db_ name;

执行删除之后的结果：

• 数据库内部看不到对应的数据库
• 数据库对应的文件夹被删除。级联删除，里面的数据表也全部被删除

注意：不要随意删除数据库

删除数据库演示，实际开发项目时不要随意删除数据库

dropdatabase d1;

3. 数据库的编码

相同编码的必要性

数据一定有自己的编码格式，编码集必须是统一的。存取数据时，编码格式必须是相同的

创建数据库的时候，有两个编码集：

1. 数据库编码集：数据库存数据时采用的编码格式
2. 数据库校验集：支持数据库进行字段比较使用的编码，本质也是一种读取数据库中数据的采用的编码格式

数据库无论对数据做任何操作，都必须保证操作和编码必须是编码一致的

字符集和校验规则

查看系统默认字符集和校验规则

• sql语法：

show variables like'character_set_database';# 查看系统默认字符集show variables like'collation_database';# 查看系统默认校验规则

查看数据库多端的编码格式：

sql语法：

show variables like'collation_%';

• connection端：
• database端
• server端：

查看数据库支持的所有字符集和字符集校验规则

showcharset;# 查看数据库支持的字符集show collation;# # 查看数据库支持的字符集校验规则

• 字符集主要是控制用什么语言。比如utf8就可以使用中文

创建指定编码的数据库

• 创建名为 db1 的数据库，使用默认编码

createdatabase db1;

说明：当我们创建数据库没有指定字符集和校验规则时，系统使用默认字符集：utf8，默认校验规则是：utf8_general_ci

• 创建一个使用utf8字符集的 db2 数据库

# 以下两种写法等效createdatabase db2 charset=utf8;createdatabase db3 characterset utf8;

• 创建一个使用utf8编码集，并且校对规则为utf8_general_ci的数据库 db3

createdatabase db4 charset=utf8 collate utf8_general_ci;# 既指明编码集，又指明校验集

• 创建一个使用gbk编码集，并且校对规则为gbk_chinese_ci的数据库 d6

createdatabase d6 charset=gbk collate gbk_chinese_ci;

不同校验规则对数据库的影响

不区分大小写

创建不区分大小写的数据库 test1

createdatabase test1 collate utf8_general_ci;# test1 设置校验规则为 utf8_general_ci，结果 不区分大小写use test1;createtableifnotexists person(name varchar(20));showtables;desc person;insertinto person (name)values('a');insertinto person (name)values('b');insertinto person (name)values('A');insertinto person (name)values('B');insertinto person (name)values('c');insertinto person (name)values('D');select*from person;select*from person where name='a';# test1 设置校验规则为 utf8_general_ci，结果 不区分大小写

区分大小写

• 创建区分大小写的数据库test2

createdatabase test2 collate utf8_bin;# test2 设置校验规则为 utf8_bin，结果 区分大小写use test2;createtableifnotexists person(name varchar(20));showtables;desc person;insertinto person (name)values('a');insertinto person (name)values('b');insertinto person (name)values('A');insertinto person (name)values('B');insertinto person (name)values('c');insertinto person (name)values('D');select*from person;select*from person where name='a';# test2 设置校验规则为 utf8_bin，结果 区分大小写

结果差异

• 校验集是否区分大小写会影响我们查看到的结果和排序结果

4. 操纵数据库

修改数据库

sql语法：

ALTERDATABASE db_name [alter_spacification [,alter_spacification]...] alter_spacification: [DEFAULT]CHARACTERSET charset_name [DEFAULT]COLLATE collation_name 

说明：

• 对数据库的修改主要指的是修改数据库的字符集，校验规则

使用示例：

将数据库 test1 的字符集改成 gbk

alterdatabase test1 charset=gbk collate gbk_chinese_ci;

查看创建数据库时使用的命令

sql语法：

showcreatedatabase 数据库名;

使用示例：

showcreatedatabase test1;

说明：查看创建数据库时使用的命令，包含了命令中我们设置的字符集和校验规则

• MySQL 建议我们关键字使用大写，但是不是必须的。
• 数据库名字的反引号``，是为了防止使用的数据库名刚好是关键字
• /*!40100 default.... */这个不是注释。前面的 CREATE DATABASE d1所有版本的mysql都会执行，/*!40100 default.... */表示当前mysql版本大于4.01版本，就执行/* */中的sql语句

5. 数据库的备份与恢复

数据库本身是一个文件夹，我们可以像备份文件夹那样拷贝一份/var/lib/mysql/路径下的文件夹，但这么做会存在相当多的问题我们推荐使用MySQL本身提供的命令，对数据库进行备份

备份

• 对某个数据库进行备份，需要退出和该数据库的连接

语法：

mysqldump -P3306 -u root -p 密码 -B 数据库名 > 数据库备份存储的文件路径;

• 带了 -B 选项时，备份出的文件中会多了一个 create database 的命令
• 该备份数据库命令无需在mysql命令行中执行，可以在Linux中的任意路径执行

示例：

# 可以在普通用户的home目录中 新建的 MySql 路径下执行# 对名为 test1 的数据库进行备份 mysqldump -P3306 -u root -p -B test1 >/home/changan_memory/MySql_Back/mytest1.sql;

备份出的mytest.sql 文件里的内容，其实把我们整个创建数据库，建表，导入数据的执行的sql语句都装载这个文件中

如果备份的不是整个数据库，而是其中的一张表，怎么做？

mysqldump -u root -p 数据库名 表名1 表名2> D:/mytest.sql# D:/mytest.sql 为假设的数据库备份存放路径

同时备份多个数据库 ？

mysqldump -u root -p -B 数据库名1 数据库名2...> 数据库备份存放路径 

恢复

语法：

• 该命令需在mysql命令行中执行

source /home/changan_memory/MySql_Back/mytest1.sql;

如果想对数据库改名，可以在备份时不带 -B ，恢复数据库时，先创建数据库，新的数据库名就是要改的新名字，再直接source，即可重命名并恢复。

恢复数据库，其实就是将备份出的mytest1.sql文件中记录的sql语句再执行一遍

6. 查看数据库的连接情况

语法：

show processlist;

• 该命令可以告诉我们当前有哪些用户连接到我们的MySQL，如果查出某个用户不是我们自己正常登陆的，很有可能数据库被人入侵了。
• 以后大家发现自己数据库比较慢时，可以用这个指令来查看数据库连接情况

7. 结语

通过本文的学习，我们从 MySQL 数据库的基本操作到安全备份与恢复进行了系统梳理。
理解了数据库在系统中的物理表现形式、字符集对数据存储与比较的影响、以及删除表和数据库操作的潜在风险。

这些知识虽然看似基础，但却是每一位开发者必须掌握的底层技能。
只有真正理解数据库的行为，才能在复杂项目中写出更安全、更高效的代码。

🌱 技术的成长从理解细节开始，
⚙️ 稳扎稳打，才能让数据库为你所用。

以上就是本文的所有内容了，如果觉得文章对你有帮助，欢迎 点赞⭐收藏 支持！如有疑问或建议，请在评论区留言交流，我们一起进步

分享到此结束啦
一键三连，好运连连！你的每一次互动，都是对作者最大的鼓励！征程尚未结束，让我们在广阔的世界里继续前行！ 🚀