MySQL 第三讲---基础篇 库与表操作（上）

前言：

各位读者朋友，欢迎来到《MySQL 第三讲——基础篇：库与表的操作》！在上一讲中，我们学习了MySQL的基础概念、以及简单的连接与退出操作。从本讲开始，我们将正式进入MySQL的核心实践环节——数据库与数据表的操作。

如果说数据库是数据的“仓库”，那么库（Database）和表（Table）就是这座仓库的骨架与容器。无论是存储用户信息、记录交易数据，还是管理复杂业务逻辑，库与表的设计与操作都是数据管理的基石。掌握它们的创建、修改、删除方法，以及字段类型的选择与约束的设定，是每一位开发者的必备技能。 

无论你是零基础的初学者，还是希望巩固基础的开发者，本讲内容都将帮助你迈出MySQL实战的关键一步。建议在学习过程中，结合本讲提供的代码示例，在自己的MySQL环境中动手实践，逐步构建起对数据库操作的直观理解。

让我们开始吧！下一站，你将成为自己数据世界的“架构师”！

---

一、库操作

1.1 创建数据库

CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [,
create_specification] ...]
create_specification:
[DEFAULT] CHARACTER SET charset_name
[DEFAULT] COLLATE collation_name

• create database 表示为 固定用法
• db_name 表示为数据库名称
• [ IF NOT EXISTS ] 表示可选项
• CHARACTER SET : 指定数据库采用的字符集
• COLLATE : 指定数据库字符集的校验规则

第三个可选项的意思是，如果不存在，就创建.
注意： 

如果创建数据库时未指明数据库的编码格式或校验规则，则默认使用MySQL配置文件中对应的编码格式或校验规则。

 如果没有对MySQL的配置文件进行过修改，则默认的编码格式是utf8，默认的校验规则是utf8_general_ci。

示例：

 

 并且由于我们添加了 【if not exists 】,如果存在同名的数据库，则会报警，并且不会创建一个同名的数据库。

指定utf8编码格式和utf8_general_ci校验规则创建数据库 

注意： 

SQL中的charset=utf8，也可以写成character set=utf8或character set utf8。 

SQL中的collate=utf8_general_ci，也可以写成collate utf8_general_ci。 

---

1.2 数据库的删除 

如果我们想要删除一个数据库，直接输入 drop database 数据库名，当我们删除一个数据库后，该数据库对应的目录也会被删除，若该目录下有表存在，则也会被直接删除。

因此不建议随意删除数据库。

我们一直说Linux下一切皆文件，所以存储数据的数据库本质上不也是一种文件吗？

创建数据库的本质就是在 /var/lib/mysql 路径下创建一个目录，删除数据库的本质就是在 /var/lib/mysql 下删除一个目录。

---

1.3 字符集和校验规则

创建数据库时，有两个编码集：数据库的字符集 和 数据库的校验集，这两个字符集决定了数据库的存储与读取的判断逻辑。

• 数据库的字符集 —— 数据库存数据时，采用什么编码
• 数据库的校验集 —— 支持数据库，进行字段比较使用的编码，本质是读取数据库中数据的采用的编码格式 ( 查找或匹配数据库，就需要进行比较字段，想要被比较，两者就需要先被读出来，此时两者的校验规则应该相同)

        1.31 查看系统默认字符集以及校验规则 

系统默认支持的字符集

   通过查看MySQL系统变量variables中的character_set_database，可以得知系统默认的字符集。

show variables like 'character_set_database';

演示：

注意： 如果是在指定数据库下使用该SQL，则查看的是该数据库对应的字符集。 

系统默认支持的字符集校验规则

1 show variables like 'collation_database';

演示： 

1.32 查看数据库支持的字符集

查看数据库支持的字符集

1 show charset;

演示：

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

1.33 查看数据库支持的字符集校验规则

1 show collation;

演示：图片太长了，只截取一部分 

1.34 校验规则对数据库的影响

 1.字符集编码格式和字符集校验规则的区别

字符集编码格式指的是在存储数据时各个字符的底层编码，用于指定数据的存储格式。
字符集校验规则是在字符集内用于比较字符的一套规则，用于对数据进行对比。
例如我们在存储数据时是按照utf8的格式进行存储的，那么将来在对数据进行做对比时也必须按照utf8的格式进行对比，因为 “存数据” 和 “取数据” 的方式必须保持一致。

2.校验规则对数据库的影响

字符集编码格式和校验规则必须是对应的，但实际上同一种字符集编码格式一般会有多种可选择的校验规则，比如 utf8编码格式的校验规则有 utf8_general_ci、utf8_bin、utf8_unicode_ci等校验规则。使用不同的校验规则操作数据库中的数据可能会得到不同的结果。

例如：utf8_general_ci校验规则在进行比对数据时是不区分大小写的，而utf8_bin校验规则在对比数据时是区分大小写的。

 一个演示示例：

创建数据库时指定数据库的校验规则为utf8_bin,数据库的编码格式默认为utf8。如下：

在该数据库中创建一个简单的person表，由于创建未指定表的编码格式和校验规则，因此person表将继承当前数据库的编码格式和校验规则。

如下：关于表的操作与数据类型，我们下面都会讲解。

这时向表中插入一些数据。如下：

通过select语句可以查看插入表中的数据，这时指定查看表中name='alice'的记录时只会将alice筛选出来，根本原因就是utf8_bin校验规则在进行数据比对时是不区分大小写的。

如下： 

但是我们要是在utf_ general_ci的校验规则下呢？

创建数据库时指定数据库的校验规则为utf8_general_ci，数据库的编码格式默认为utf8。如下：

在该数据库中同样创建和刚才一样的person表，该person表会将继承当前数据库的编码格式和校验规则。如下：

这时向表中插入刚才相同的数据。如下： 

这时指定查看表中name='alice'的记录时会将alice与Alice筛选出来，根本原因就是utf8_general_ci校验规则在进行数据比对时是不区分大小写的。如下：

---

1.4 操纵数据库

        1.41 查看数据库

               使用show databases SQL可以查看系统中所有的数据库。

1 show databases;

 

        1.42 显示创建语句

显示创建语句

使用show create database 数据库名SQL可以查看对应数据库的创建语句。

如下：

说明一下：

• MySQL建议SQL中的关键字使用大写，我们写小写也是可以的，MySQL会在程序内部进行大小写转化。
• 数据库的名字加上反引号，是为了防止使用的数据库名与关键字冲突。
• /*!40100 DEFAULT CHARACTER SET utf8 */不是注释，它表示当前MySQL版本如果大于4.10，则执行后面的SQL语句。（就是类似于条件编译）

1.43 修改数据库

修改数据库

ALTER DATABASE db_name [[DEFAULT] CHARSET=character_name] [[DEFAULT] COLLATE=collation_name];

注意：这里的修改数据库指的是修改数据库的字符集或校验规则。

1.44 删除数据库

删除数据库的SQL如下：

DROP DATABASE [IF EXISTS] db_name;

删除数据库后该数据库对应的文件夹就被删除了。比如：

并且删除数据库后，该数据库下的所有表也都会被级联删除，意味着该数据库下面的所有的数据和表结构都会删除，因此不要随意删除数据库。

1.5 数据库的备份和恢复

数据库备份

为了演示数据库备份，下面我们创建一个数据库，并在该数据库中创建两个表。

如下

同时，还向people表当中插入两个数据。 

 对指定数据库进行备份的命令：（在bash下执行）

mysqldump -P 端口号 -u 用户名 -p 密码 -B 数据库名1 数据库名2 ...  > 数据库备份存储的文件路径

 

我们查看一下，backup.up的备份内容，我们可以发现文件中的内容就是我们在该数据库中执行的各种SQL命令，包括创建数据库、创建表、插入数据等SQL语句。

-- MySQL dump 10.13  Distrib 8.0.42, for Linux (x86_64)
--
-- Host: localhost    Database: backup
-- ------------------------------------------------------
-- Server version	8.0.42-0ubuntu0.22.04.1

/*!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: `backup`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `backup` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `backup`;

--
-- Table structure for table `human`
--

DROP TABLE IF EXISTS `human`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `human` (
  `id` int DEFAULT NULL,
  `name` varchar(5) DEFAULT NULL,
  `num` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `human`
--

LOCK TABLES `human` WRITE;
/*!40000 ALTER TABLE `human` DISABLE KEYS */;
/*!40000 ALTER TABLE `human` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `people`
--

DROP TABLE IF EXISTS `people`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `people` (
  `id` int DEFAULT NULL,
  `name` varchar(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `people`
--

LOCK TABLES `people` WRITE;
/*!40000 ALTER TABLE `people` DISABLE KEYS */;
INSERT INTO `people` VALUES (2,'张三'),(3,'李四');
/*!40000 ALTER TABLE `people` ENABLE KEYS */;
UNLOCK TABLES;
/*!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 2025-05-07 18:41:53

 现在我们删除一下数据库。

数据库恢复

使用如下命令即可对指定数据库进行恢复：

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

这里我们可以看到数据库已经被恢复出来了。 

表的备份和恢复

表备份

使用如下命令即可对指定表进行备份：

mysqldump -P 端口号 -u 用户名 -p 密码 数据库名 表名1 表名2 ... > 表备份存储的文件路径

接下来，我们对上面的数据库内部的表进行备份和恢复

表恢复

表恢复之前需要先选中一个数据库，表明需要将表恢复到哪一个数据库中，为了防止恢复出来的表与该数据库中已有的表的表名重复，一般在恢复表时会选择创建一个空的数据库，然后在该数据库中进行表的恢复。

在数据库中使用如下命令即可对指定表进行恢复：

source 表备份存储的文件路径

为了演示表恢复，我们先将刚才的数据库删除。如下：

这里我们看到表已经被完全恢复出来了。 

1.5 查看连接情况

使用 show processlist SQL来查看当前连接MySQL的用户。

1. Id列：一个标识，可以在MySQL中通过kill id杀死指定id的线程。
2. User列：显示当前用户，如果不是root，这个命令就只显示你权限范围内的SQL语句。
3. Host列：显示这个语句是从哪个IP的哪个端口上发出的，可用来追踪出问题语句的用户。
4. db列：当前执行的命令是在哪一个数据库上，如果没有指定数据库，则该值为NULL。
5. Command列：显示当前连接执行的命令，一般就是休眠（Sleep）、查询（Query）和连接（Connect）。
6. Time列：表示该线程处于当前状态的时间，单位是秒。\nState列：显示使用当前连接的SQL语句的状态。
7. Info列：一般记录的是线程执行的语句，默认只显示前100个字符，如果要看全部信息，需要使用show full processlist。

show processlist可以告诉我们当前有哪些用户连接到我们的MySQL，如果查出某个用户不是你正常登录的，那么很有可能你的数据库被人入侵了，以后如果发现自己的数据库比较慢时，可以用这个SQL来查看数据库连接情况

---

总结

至此，我们已经完成了《MySQL 第三讲——基础篇：库与表操作（上）》的学习！通过本讲内容，相信你已经掌握了如何通过SQL命令创建、管理数据库。

但是我们还没对我们数据库中，添加关键要素---表，而关于它的讲解我们要放在下一节讲解了。

在下一讲《MySQL 第三讲——基础篇：库与表操作（下）》中，我们将深入探讨关于表的结构操作。

我们下期见。