【MySQL】库的操作与表的操作

一、库的操作

1.1、创建数据库

🐬语法：

CREATEDATABASE[IFNOTEXISTS]db_name[create_specification[,create_specification]...]create_specification:[DEFAULT]CHARACTERSETcharset_name[DEFAULT]COLLATEcollation_name

📚说明：

1. 大写的表示关键字。
2. []是可选项。
3. CHARACTER SET：指定数据库采用的字符集。
4. COLLATE：指定数据库字符集的校验规则。

我们执行命令：create database test1; show databases;
创建一个test1数据库，结果如下：

成功在我们的主机上创建了test1数据库。
此时，如果我们再次执行上述指令，则会引发报错，因为已经存在test1数据库了，无法存在同名数据库。从文件操作的角度来看，就是/var/lib/mysql路径下，无法存在同名目录。

但是如果我们添加[if not exists]选项的话，即使指定的库名db_name已存在，也会忽略错误并发出警告，SQL 语句依然执行成功。

1.1.1、字符集与校验规则

创建数据库，都会存在两个编码集：

1. 数据库编码集（字符集）：数据库未来存储数据所采用的编码集。
2. 数据库校验集（校验集）：支持数据库进行字段比较使用的编码集。本质也是一种读取数据库中数据所采用的编码格式。

结论：数据库无论对数据做任何操作，都必须保证字符集与校验集保持编码一致。
例如，我们存储数据的时候，选择采用utf8字符集，我们未来对数据进行各种操作的时候，同样就必须采用对应的utf8校验集，如果采用gdk等其他校验集，我们就难以对数据进行各种操作。

我们可以使用以下两条指令，来查看系统默认的字符集和校验集。

showvariableslike'character_set_database';-- 查看字符集showvariableslike'collation_database';-- 查看校验集

以下两条指令可以用来查看系统支持的字符集和校验集。

showcharset;-- 查看支持的字符集showcollation;-- 查看支持的校验集

综上，我们也可以指定字符集与校验集来创建数据库。
当我们创建数据库没有指定字符集和校验规则时，系统使用默认字符集：utf8，校验规则是：utf8_ general_ ci。

例子1️⃣：创建一个使用utf8字符集的test2数据库。

createdatabasetest2charset=utf8;-- 写法1createdatabasetest2charactersetutf8;-- 写法2

例子2️⃣：创建一个使用utf8字符集，并带校对规则的test3数据库。

createdatabasetest2charset=utf8collateutf8_general_ci;

讲了这么多，其实我们还是不太明白为什么需要存在这么多的字符集和校验集。接下来我将展示一个简单的实验，来带大家感受不同编码集的区别。
首先，我们创建一个使用utf8字符集，并带utf8_general_ci的校验集的db1数据库。utf8_general_ci校验集是不区分大小写的。其次，我们再改数据库中创建一张test表，并向其中插入数据。

我们再创建一个db2数据库，重复以上操作，仅仅将校验集修改为utf8_bin，该校验集严格区分大小写。
然后对两个数据库的两张表执行同一个命令：

select*fromtestorderbyword;-- 排序

得到的结果如下：

很明显，在不同的校验集下直线相同的操作，所得到的结果会有所差异。因此，在创建数据库的时候，也要根据项目需求来选择对应的字符集和校验集。此外，提供多种字符集和校验集，也是为了我们的数据库适配更多的设备场景。

1.2、删除数据库

🐬语法：

DROPDATABASE[IFEXISTS]db_ name;

📚说明：
执行删除之后的结果:

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

注意：我们在未来的项目中不要随意删除数据库，删除数据库对应的文件操作为rm，因此一旦删除后就无法找回。建议先备份数据再删除。

1.3、查看数据库

🐬语法：

SHOWDATABASES;

📚说明：

• 查看数据库列表。

🐬语法：

USEdb_name;

📚说明：

• 进入db_name数据库中，可以对该数据库执行对应的操作。

🐬语法：

SELECTDATABASE();

📚说明：

• 查看当前所处的数据库。

补充：
🐬语法：

SHOWCREATEDATABASEdb_name;

📚说明：

• 查看数据库创建语句。

💡注意：

1. MySQL建议我们关键字使用大写，但这不是必须的。
2. 数据库名字的反引号``,是为了防止使用的数据库名刚好是关键字。
3. /*!40100 default.... */这个不是注释，表示当前mysql版本大于4.01版本，就执行这句话。可以理解为一个判断语句。

1.4、修改数据库

🐬语法：

ALTERDATABASEdb_name[alter_spacification[,alter_spacification]...]alter_spacification:[DEFAULT]CHARACTERSETcharset_name[DEFAULT]COLLATEcollation_name

📚说明：

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

示例：将test1数据库的字符集修改为gbk。

二、表的操作

2.1、创建表

🐬语法：

CREATETABLEtable_name(field1 datatype,field2 datatype,field3 datatype)[characterset字符集][collate校验规则][engine存储引擎];

📚说明：

1. field表示列名。
2. datatype表示列的类型。
3. character set字符集，如果没有指定字符集，则以所在数据库的字符集为准。
4. collate校验规则，如果没有指定校验规则，则以所在数据库的校验规则为准。

示例：在test1数据库中创建一个student表。

createtablestudent(namevarchar(20),ageint,idint);

使用命令desc [表名]，可以查看对应表的字段信息。

进入该数据库对应的目录，可以看到student.ibd文件，这个就是我们刚刚创建的表student。

接下来，我们再指定一个存储引擎，来创建一个表teacher。

createtableteacher(namevarchar(20),ageint,idint)engineMyIsam;

按照预期，该数据库路径下的确产生了对应的文件，只不过，当我们使用MyIsam存储引擎的时候，生成的文件居然更多了。

直接输出结论：不同的存储引擎，创建表的文件不一样。
teacher表的存储引擎是MyISAM，在数据目中有三个不同的文件，分别是：

1. teacher_414.sdi：表结构，以紧凑的JSON格式保存了表的所有结构信息。
2. teacher.MYD：表数据。
3. teacher.MYI：表索引。

在MySQL 8.0之前，表结构是记录在.frm文件中的。从8.0开始，MySQL采用了一个统一的数据字典来管理元数据，同时为了兼容性和数据安全，为MyISAM等非InnoDB引擎的表额外生成了.sdi文件作为冗余备份。
对于InnoDB引擎的表，这些信息是直接内嵌在表空间文件.ibd里的，不会生成单独的.sdi文件。

2.2、查看表

🐬语法：

showtables;

📚说明：

• 查看数据库的表结构。
  🐬语法：

showcreatetable[name]\G

📚说明：

• 查看创建表的详细信息。\G选项：格式化，去掉不必要的符号

可以看到，这些语句与我们先前写的存在差异，这是由于服务器mysqld会对我们写的sql语句进行词法语法分析和优化。

2.3、修改表

在项目实际开发中，经常修改某个表的结构，比如字段名字、字段大小、字段类型、表的字符集类型以及表的存储引擎等等。我们还有需求，添加字段，删除字段等等。这时我们就需要修改表。
🐬语法：

ALTERTABLEtablenameADD(columndatatype[DEFAULTexpr][,columndatatype]...);ALTERTABLEtablenameMODIfy(columndatatype[DEFAULTexpr][,columndatatype]...);ALTERTABLEtablenameDROP(column);

示例1️⃣：修改表名

altertabletable_namerename[to]new_table_name;

命令中的to可以删去。

示例2️⃣：添加字段

altertableuser1addimage_pathvarchar(20)comment'用户头像'afterid;

示例3️⃣：修改字段

altertableuser1modifynamevarchar(60)comment'用户姓名';

示例4️⃣：删除字段

altertableuser1dropid;

注意：删除字段时一定要谨慎，删除字段后，其对应的列数据都会被删除。

2.4、删除表

🐬语法：

DROP[TEMPORARY]TABLE[IFEXISTS]tbl_name[,tbl_name]...

三、备份与恢复

3.1、备份

在做数据备份的时候，我们需要用到mysqldump，这是我们安装mysql时，顺带着一起安装在我们的主机中。
🐬语法：

mysqldump-P3306-uroot-p-B数据库名>数据库备份存储的文件路径

📚说明：

• 该指令是在命令行中操作，不是在mysql客户端操作的！

通过vim打开文件test1.sql，可以查看该文件对应的内容。通过观察，可以发现，文件内容就是我们所写的SQL语句，因此，得出结论，备份操作备份的是构建对应表的SQL语句，而不是表里的数据。

-- MySQL dump 10.13 Distrib 8.0.46, for Linux (x86_64)---- Host: localhost Database: test1-- -------------------------------------------------------- Server version 8.0.46/*!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 */;---- Current Database: `test1`--CREATEDATABASE/*!32312 IF NOT EXISTS*/`test1`/*!40100 DEFAULT CHARACTER SET utf8mb3 *//*!80016 DEFAULT ENCRYPTION='N' */;USE`test1`;---- Table structure for table `teacher`--DROPTABLEIFEXISTS`teacher`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!50503 SET character_set_client = utf8mb4 */;CREATETABLE`teacher`(`name`varchar(20)DEFAULTNULL,`age`intDEFAULTNULL,`id`intDEFAULTNULL)ENGINE=MyISAMDEFAULTCHARSET=utf8mb3;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `teacher`--LOCKTABLES`teacher`WRITE;/*!40000 ALTER TABLE `teacher` DISABLE KEYS */;/*!40000 ALTER TABLE `teacher` ENABLE KEYS */;UNLOCKTABLES;---- Table structure for table `user1`--DROPTABLEIFEXISTS`user1`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!50503 SET character_set_client = utf8mb4 */;CREATETABLE`user1`(`name`varchar(60)DEFAULTNULLCOMMENT'用户姓名',`age`intDEFAULTNULL,`image_path`varchar(20)DEFAULTNULLCOMMENT'用户头像')ENGINE=InnoDBDEFAULTCHARSET=utf8mb3;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `user1`--LOCKTABLES`user1`WRITE;/*!40000 ALTER TABLE `user1` DISABLE KEYS */;INSERTINTO`user1`VALUES('张三',19,NULL),('李四',18,NULL),('王五',20,NULL);/*!40000 ALTER TABLE `user1` ENABLE KEYS */;UNLOCKTABLES;/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;-- Dump completed on 2026-08-14 20:38:10

3.2、恢复

🐬语法：

source 数据库备份存储的文件路径;

本质上就是再次执行该文件内的SQL语句。

3.3、注意事项

如果我们备份的不是整个数据库，而是几张表呢？？应该如何做？
🐬语法：

mysqldump-uroot-p数据库名 表名1 表名2>数据库备份存储的文件路径

同时备份多个数据库：
🐬语法：

mysqldump-uroot-p-B数据库名1 数据库名2...>数据库备份存储的文件路径

如果备份一个数据库时，没有带上-B参数，在恢复数据库时，需要先创建空数据库，然后使用数据库，再使用source来还原。

补充：

🐬语法：

showprocesslist;

📚说明：

• 查看当前连接到该数据库的用户。

完🐳🐳🐳