文章目录一、库的操作1.1、创建数据库1.1.1、字符集与校验规则1.2、删除数据库1.3、查看数据库1.4、修改数据库二、表的操作2.1、创建表2.2、查看表2.3、修改表2.4、删除表三、备份与恢复3.1、备份3.2、恢复3.3、注意事项一、库的操作1.1、创建数据库语法CREATEDATABASE[IFNOTEXISTS]db_name[create_specification[,create_specification]...]create_specification:[DEFAULT]CHARACTERSETcharset_name[DEFAULT]COLLATEcollation_name说明大写的表示关键字。[]是可选项。CHARACTER SET指定数据库采用的字符集。COLLATE指定数据库字符集的校验规则。我们执行命令create database test1; show databases;创建一个test1数据库结果如下成功在我们的主机上创建了test1数据库。此时如果我们再次执行上述指令则会引发报错因为已经存在test1数据库了无法存在同名数据库。从文件操作的角度来看就是/var/lib/mysql路径下无法存在同名目录。但是如果我们添加[if not exists]选项的话即使指定的库名db_name已存在也会忽略错误并发出警告SQL 语句依然执行成功。1.1.1、字符集与校验规则创建数据库都会存在两个编码集数据库编码集字符集数据库未来存储数据所采用的编码集。数据库校验集校验集支持数据库进行字段比较使用的编码集。本质也是一种读取数据库中数据所采用的编码格式。结论数据库无论对数据做任何操作都必须保证字符集与校验集保持编码一致。例如我们存储数据的时候选择采用utf8字符集我们未来对数据进行各种操作的时候同样就必须采用对应的utf8校验集如果采用gdk等其他校验集我们就难以对数据进行各种操作。我们可以使用以下两条指令来查看系统默认的字符集和校验集。showvariableslikecharacter_set_database;-- 查看字符集showvariableslikecollation_database;-- 查看校验集以下两条指令可以用来查看系统支持的字符集和校验集。showcharset;-- 查看支持的字符集showcollation;-- 查看支持的校验集综上我们也可以指定字符集与校验集来创建数据库。当我们创建数据库没有指定字符集和校验规则时系统使用默认字符集utf8校验规则是utf8_ general_ ci。例子1️⃣创建一个使用utf8字符集的test2数据库。createdatabasetest2charsetutf8;-- 写法1createdatabasetest2charactersetutf8;-- 写法2例子2️⃣创建一个使用utf8字符集并带校对规则的test3数据库。createdatabasetest2charsetutf8collateutf8_general_ci;讲了这么多其实我们还是不太明白为什么需要存在这么多的字符集和校验集。接下来我将展示一个简单的实验来带大家感受不同编码集的区别。首先我们创建一个使用utf8字符集并带utf8_general_ci的校验集的db1数据库。utf8_general_ci校验集是不区分大小写的。其次我们再改数据库中创建一张test表并向其中插入数据。我们再创建一个db2数据库重复以上操作仅仅将校验集修改为utf8_bin该校验集严格区分大小写。然后对两个数据库的两张表执行同一个命令select*fromtestorderbyword;-- 排序得到的结果如下很明显在不同的校验集下直线相同的操作所得到的结果会有所差异。因此在创建数据库的时候也要根据项目需求来选择对应的字符集和校验集。此外提供多种字符集和校验集也是为了我们的数据库适配更多的设备场景。1.2、删除数据库语法DROPDATABASE[IFEXISTS]db_ name;说明执行删除之后的结果:数据库内部看不到对应的数据库。对应的数据库文件夹被删除级联删除里面的数据表全部被删。注意我们在未来的项目中不要随意删除数据库删除数据库对应的文件操作为rm因此一旦删除后就无法找回。建议先备份数据再删除。1.3、查看数据库语法SHOWDATABASES;说明查看数据库列表。语法USEdb_name;说明进入db_name数据库中可以对该数据库执行对应的操作。语法SELECTDATABASE();说明查看当前所处的数据库。补充语法SHOWCREATEDATABASEdb_name;说明查看数据库创建语句。注意MySQL建议我们关键字使用大写但这不是必须的。数据库名字的反引号,是为了防止使用的数据库名刚好是关键字。/*!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存储引擎];说明field表示列名。datatype表示列的类型。character set字符集如果没有指定字符集则以所在数据库的字符集为准。collate校验规则如果没有指定校验规则则以所在数据库的校验规则为准。示例在test1数据库中创建一个student表。createtablestudent(namevarchar(20),ageint,idint);使用命令desc [表名]可以查看对应表的字段信息。进入该数据库对应的目录可以看到student.ibd文件这个就是我们刚刚创建的表student。接下来我们再指定一个存储引擎来创建一个表teacher。createtableteacher(namevarchar(20),ageint,idint)engineMyIsam;按照预期该数据库路径下的确产生了对应的文件只不过当我们使用MyIsam存储引擎的时候生成的文件居然更多了。直接输出结论不同的存储引擎创建表的文件不一样。teacher表的存储引擎是MyISAM在数据目中有三个不同的文件分别是teacher_414.sdi表结构以紧凑的JSON格式保存了表的所有结构信息。teacher.MYD表数据。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_CLIENTCHARACTER_SET_CLIENT */;/*!40101 SET OLD_CHARACTER_SET_RESULTSCHARACTER_SET_RESULTS */;/*!40101 SET OLD_COLLATION_CONNECTIONCOLLATION_CONNECTION */;/*!50503 SET NAMES utf8mb4 */;/*!40103 SET OLD_TIME_ZONETIME_ZONE */;/*!40103 SET TIME_ZONE00:00 */;/*!40014 SET OLD_UNIQUE_CHECKSUNIQUE_CHECKS, UNIQUE_CHECKS0 */;/*!40014 SET OLD_FOREIGN_KEY_CHECKSFOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS0 */;/*!40101 SET OLD_SQL_MODESQL_MODE, SQL_MODENO_AUTO_VALUE_ON_ZERO */;/*!40111 SET OLD_SQL_NOTESSQL_NOTES, SQL_NOTES0 */;---- Current Database: test1--CREATEDATABASE/*!32312 IF NOT EXISTS*/test1/*!40100 DEFAULT CHARACTER SET utf8mb3 *//*!80016 DEFAULT ENCRYPTIONN */;USEtest1;---- Table structure for table teacher--DROPTABLEIFEXISTSteacher;/*!40101 SET saved_cs_client character_set_client */;/*!50503 SET character_set_client utf8mb4 */;CREATETABLEteacher(namevarchar(20)DEFAULTNULL,ageintDEFAULTNULL,idintDEFAULTNULL)ENGINEMyISAMDEFAULTCHARSETutf8mb3;/*!40101 SET character_set_client saved_cs_client */;---- Dumping data for table teacher--LOCKTABLESteacherWRITE;/*!40000 ALTER TABLE teacher DISABLE KEYS */;/*!40000 ALTER TABLE teacher ENABLE KEYS */;UNLOCKTABLES;---- Table structure for table user1--DROPTABLEIFEXISTSuser1;/*!40101 SET saved_cs_client character_set_client */;/*!50503 SET character_set_client utf8mb4 */;CREATETABLEuser1(namevarchar(60)DEFAULTNULLCOMMENT用户姓名,ageintDEFAULTNULL,image_pathvarchar(20)DEFAULTNULLCOMMENT用户头像)ENGINEInnoDBDEFAULTCHARSETutf8mb3;/*!40101 SET character_set_client saved_cs_client */;---- Dumping data for table user1--LOCKTABLESuser1WRITE;/*!40000 ALTER TABLE user1 DISABLE KEYS */;INSERTINTOuser1VALUES(张三,19,NULL),(李四,18,NULL),(王五,20,NULL);/*!40000 ALTER TABLE user1 ENABLE KEYS */;UNLOCKTABLES;/*!40103 SET TIME_ZONEOLD_TIME_ZONE */;/*!40101 SET SQL_MODEOLD_SQL_MODE */;/*!40014 SET FOREIGN_KEY_CHECKSOLD_FOREIGN_KEY_CHECKS */;/*!40014 SET UNIQUE_CHECKSOLD_UNIQUE_CHECKS */;/*!40101 SET CHARACTER_SET_CLIENTOLD_CHARACTER_SET_CLIENT */;/*!40101 SET CHARACTER_SET_RESULTSOLD_CHARACTER_SET_RESULTS */;/*!40101 SET COLLATION_CONNECTIONOLD_COLLATION_CONNECTION */;/*!40111 SET SQL_NOTESOLD_SQL_NOTES */;-- Dump completed on 2026-08-14 20:38:103.2、恢复语法source 数据库备份存储的文件路径;本质上就是再次执行该文件内的SQL语句。3.3、注意事项如果我们备份的不是整个数据库而是几张表呢应该如何做语法mysqldump-uroot-p数据库名 表名1 表名2数据库备份存储的文件路径同时备份多个数据库语法mysqldump-uroot-p-B数据库名1 数据库名2...数据库备份存储的文件路径如果备份一个数据库时没有带上-B参数在恢复数据库时需要先创建空数据库然后使用数据库再使用source来还原。补充语法showprocesslist;说明查看当前连接到该数据库的用户。完
【MySQL】库的操作与表的操作
文章目录一、库的操作1.1、创建数据库1.1.1、字符集与校验规则1.2、删除数据库1.3、查看数据库1.4、修改数据库二、表的操作2.1、创建表2.2、查看表2.3、修改表2.4、删除表三、备份与恢复3.1、备份3.2、恢复3.3、注意事项一、库的操作1.1、创建数据库语法CREATEDATABASE[IFNOTEXISTS]db_name[create_specification[,create_specification]...]create_specification:[DEFAULT]CHARACTERSETcharset_name[DEFAULT]COLLATEcollation_name说明大写的表示关键字。[]是可选项。CHARACTER SET指定数据库采用的字符集。COLLATE指定数据库字符集的校验规则。我们执行命令create database test1; show databases;创建一个test1数据库结果如下成功在我们的主机上创建了test1数据库。此时如果我们再次执行上述指令则会引发报错因为已经存在test1数据库了无法存在同名数据库。从文件操作的角度来看就是/var/lib/mysql路径下无法存在同名目录。但是如果我们添加[if not exists]选项的话即使指定的库名db_name已存在也会忽略错误并发出警告SQL 语句依然执行成功。1.1.1、字符集与校验规则创建数据库都会存在两个编码集数据库编码集字符集数据库未来存储数据所采用的编码集。数据库校验集校验集支持数据库进行字段比较使用的编码集。本质也是一种读取数据库中数据所采用的编码格式。结论数据库无论对数据做任何操作都必须保证字符集与校验集保持编码一致。例如我们存储数据的时候选择采用utf8字符集我们未来对数据进行各种操作的时候同样就必须采用对应的utf8校验集如果采用gdk等其他校验集我们就难以对数据进行各种操作。我们可以使用以下两条指令来查看系统默认的字符集和校验集。showvariableslikecharacter_set_database;-- 查看字符集showvariableslikecollation_database;-- 查看校验集以下两条指令可以用来查看系统支持的字符集和校验集。showcharset;-- 查看支持的字符集showcollation;-- 查看支持的校验集综上我们也可以指定字符集与校验集来创建数据库。当我们创建数据库没有指定字符集和校验规则时系统使用默认字符集utf8校验规则是utf8_ general_ ci。例子1️⃣创建一个使用utf8字符集的test2数据库。createdatabasetest2charsetutf8;-- 写法1createdatabasetest2charactersetutf8;-- 写法2例子2️⃣创建一个使用utf8字符集并带校对规则的test3数据库。createdatabasetest2charsetutf8collateutf8_general_ci;讲了这么多其实我们还是不太明白为什么需要存在这么多的字符集和校验集。接下来我将展示一个简单的实验来带大家感受不同编码集的区别。首先我们创建一个使用utf8字符集并带utf8_general_ci的校验集的db1数据库。utf8_general_ci校验集是不区分大小写的。其次我们再改数据库中创建一张test表并向其中插入数据。我们再创建一个db2数据库重复以上操作仅仅将校验集修改为utf8_bin该校验集严格区分大小写。然后对两个数据库的两张表执行同一个命令select*fromtestorderbyword;-- 排序得到的结果如下很明显在不同的校验集下直线相同的操作所得到的结果会有所差异。因此在创建数据库的时候也要根据项目需求来选择对应的字符集和校验集。此外提供多种字符集和校验集也是为了我们的数据库适配更多的设备场景。1.2、删除数据库语法DROPDATABASE[IFEXISTS]db_ name;说明执行删除之后的结果:数据库内部看不到对应的数据库。对应的数据库文件夹被删除级联删除里面的数据表全部被删。注意我们在未来的项目中不要随意删除数据库删除数据库对应的文件操作为rm因此一旦删除后就无法找回。建议先备份数据再删除。1.3、查看数据库语法SHOWDATABASES;说明查看数据库列表。语法USEdb_name;说明进入db_name数据库中可以对该数据库执行对应的操作。语法SELECTDATABASE();说明查看当前所处的数据库。补充语法SHOWCREATEDATABASEdb_name;说明查看数据库创建语句。注意MySQL建议我们关键字使用大写但这不是必须的。数据库名字的反引号,是为了防止使用的数据库名刚好是关键字。/*!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存储引擎];说明field表示列名。datatype表示列的类型。character set字符集如果没有指定字符集则以所在数据库的字符集为准。collate校验规则如果没有指定校验规则则以所在数据库的校验规则为准。示例在test1数据库中创建一个student表。createtablestudent(namevarchar(20),ageint,idint);使用命令desc [表名]可以查看对应表的字段信息。进入该数据库对应的目录可以看到student.ibd文件这个就是我们刚刚创建的表student。接下来我们再指定一个存储引擎来创建一个表teacher。createtableteacher(namevarchar(20),ageint,idint)engineMyIsam;按照预期该数据库路径下的确产生了对应的文件只不过当我们使用MyIsam存储引擎的时候生成的文件居然更多了。直接输出结论不同的存储引擎创建表的文件不一样。teacher表的存储引擎是MyISAM在数据目中有三个不同的文件分别是teacher_414.sdi表结构以紧凑的JSON格式保存了表的所有结构信息。teacher.MYD表数据。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_CLIENTCHARACTER_SET_CLIENT */;/*!40101 SET OLD_CHARACTER_SET_RESULTSCHARACTER_SET_RESULTS */;/*!40101 SET OLD_COLLATION_CONNECTIONCOLLATION_CONNECTION */;/*!50503 SET NAMES utf8mb4 */;/*!40103 SET OLD_TIME_ZONETIME_ZONE */;/*!40103 SET TIME_ZONE00:00 */;/*!40014 SET OLD_UNIQUE_CHECKSUNIQUE_CHECKS, UNIQUE_CHECKS0 */;/*!40014 SET OLD_FOREIGN_KEY_CHECKSFOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS0 */;/*!40101 SET OLD_SQL_MODESQL_MODE, SQL_MODENO_AUTO_VALUE_ON_ZERO */;/*!40111 SET OLD_SQL_NOTESSQL_NOTES, SQL_NOTES0 */;---- Current Database: test1--CREATEDATABASE/*!32312 IF NOT EXISTS*/test1/*!40100 DEFAULT CHARACTER SET utf8mb3 *//*!80016 DEFAULT ENCRYPTIONN */;USEtest1;---- Table structure for table teacher--DROPTABLEIFEXISTSteacher;/*!40101 SET saved_cs_client character_set_client */;/*!50503 SET character_set_client utf8mb4 */;CREATETABLEteacher(namevarchar(20)DEFAULTNULL,ageintDEFAULTNULL,idintDEFAULTNULL)ENGINEMyISAMDEFAULTCHARSETutf8mb3;/*!40101 SET character_set_client saved_cs_client */;---- Dumping data for table teacher--LOCKTABLESteacherWRITE;/*!40000 ALTER TABLE teacher DISABLE KEYS */;/*!40000 ALTER TABLE teacher ENABLE KEYS */;UNLOCKTABLES;---- Table structure for table user1--DROPTABLEIFEXISTSuser1;/*!40101 SET saved_cs_client character_set_client */;/*!50503 SET character_set_client utf8mb4 */;CREATETABLEuser1(namevarchar(60)DEFAULTNULLCOMMENT用户姓名,ageintDEFAULTNULL,image_pathvarchar(20)DEFAULTNULLCOMMENT用户头像)ENGINEInnoDBDEFAULTCHARSETutf8mb3;/*!40101 SET character_set_client saved_cs_client */;---- Dumping data for table user1--LOCKTABLESuser1WRITE;/*!40000 ALTER TABLE user1 DISABLE KEYS */;INSERTINTOuser1VALUES(张三,19,NULL),(李四,18,NULL),(王五,20,NULL);/*!40000 ALTER TABLE user1 ENABLE KEYS */;UNLOCKTABLES;/*!40103 SET TIME_ZONEOLD_TIME_ZONE */;/*!40101 SET SQL_MODEOLD_SQL_MODE */;/*!40014 SET FOREIGN_KEY_CHECKSOLD_FOREIGN_KEY_CHECKS */;/*!40014 SET UNIQUE_CHECKSOLD_UNIQUE_CHECKS */;/*!40101 SET CHARACTER_SET_CLIENTOLD_CHARACTER_SET_CLIENT */;/*!40101 SET CHARACTER_SET_RESULTSOLD_CHARACTER_SET_RESULTS */;/*!40101 SET COLLATION_CONNECTIONOLD_COLLATION_CONNECTION */;/*!40111 SET SQL_NOTESOLD_SQL_NOTES */;-- Dump completed on 2026-08-14 20:38:103.2、恢复语法source 数据库备份存储的文件路径;本质上就是再次执行该文件内的SQL语句。3.3、注意事项如果我们备份的不是整个数据库而是几张表呢应该如何做语法mysqldump-uroot-p数据库名 表名1 表名2数据库备份存储的文件路径同时备份多个数据库语法mysqldump-uroot-p-B数据库名1 数据库名2...数据库备份存储的文件路径如果备份一个数据库时没有带上-B参数在恢复数据库时需要先创建空数据库然后使用数据库再使用source来还原。补充语法showprocesslist;说明查看当前连接到该数据库的用户。完