MySQL

如何在 MySQL 中创建和删除数据库和表

内容

介绍
先决条件
如何创建新数据库？
列出现有数据库
如何在 MySQL 中切换到不同的数据库
在数据库中创建表
如何在 MySQL 中查看表
如何使用 ALTER TABLE 命令更改表
删除表
删除数据库
结论
常见问题解答

分享到

介绍

MySQL 在表和数据库中维护它管理的数据。表在预定义的列和数据类型模式中存储数据。数据库是一种可以容纳多个表、索引、过程的结构，并有助于定义权限、自定义等方面的范围。

在本指南中，我们将向您展示如何在 MySQL 中创建表和数据库。我们将向您展示如何管理这两种结构的一些功能，然后我们将介绍如何删除它们以清理不再需要它们的结构。

先决条件

要按照本指南进行操作，您需要使用具有管理员权限的用户登录到 MySQL 服务器，使用 mysql 命令行客户端。

如果您不想使用 root MySQL 管理员帐户登录，则您的用户至少需要 CREATE 权限才能执行本指南中描述的操作。

您可以通过键入以下内容来检查用户的可用权限

SHOW GRANTS\G

如何创建新数据库？

我们将首先演示如何创建新的数据库。在 MySQL 中，数据库包含表、索引、过程等其他资源，这些资源通常与单个项目相关。每个资源都必须在数据库中创建，因此学习如何创建新数据库是第一步。

创建新数据库使用的基本语法如下所示

CREATE DATABASE <db_name>;

在执行命令之前，将 <db_name> 替换为您希望为数据库指定的名称。此命令将创建一个具有给定名称的数据库，并将当前用户设置为新数据库的所有者。

数据库将使用系统范围的默认值来设置字符集、排序规则和加密属性

字符集：设置新数据库的字符集。这是一个本地化选项，它会影响哪些字符被视为大写、小写和数字。
排序规则：设置数据库的排序规则或排序顺序。这是一个本地化选项，它决定项目在排序时如何组织。
加密：设置新数据库的加密。这是一个布尔选项，可以是 'Y' 表示是，或 'N' 表示否。

您可以通过键入以下内容来查找安装中可用的字符集

SHOW CHARACTER SET;

同样，您可以通过键入以下内容来查找可用的排序规则

SHOW COLLATION;

如果您想为这些参数中的一个指定非默认值，您可以像这样在主创建语句之后添加它们

CREATE DATABASE <db_name>
  CHARACTER SET = 'utf8mb4'
  COLLATE = 'utf8mb4_0900_ai_ci'
  ENCRYPTION = 'Y';

要按照本指南中的示例操作，请使用实例的默认区域设置和 UTF8 字符集创建一个名为 school 的数据库

CREATE DATABASE school CHARACTER SET = 'utf8mb4';

这将使用您提供的规范创建您的新数据库。

列出现有数据库

要列出服务器上可用的数据库，请键入

SHOW DATABASES;

这将列出当前环境中定义的每个数据库

+--------------------+
  Database           |
+--------------------+
  information_schema |
  mysql              |
  performance_schema |
  school             |
  sys                |
  testing            |
+--------------------+
6 rows in set (0.00 sec)

您可以使用 SHOW CREATE DATABASE 命令验证您提供的设置是否已应用于新的 school 数据库

SHOW CREATE DATABASE school;

输出将显示用于创建数据库的命令和选项，使用应用的任何默认值填充选项

+----------+----------------------------------------------------------------------------------------------------------------------------------+
  Database | Create Database                                                                                                                  |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
  school   | CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

如何在 MySQL 中切换到不同的数据库

创建数据库后，您可以通过几种不同的方式与它进行交互。

第一个选项是在您使用的每个命令中指定数据库。如果您要运行多个与数据库对象相关的命令，为了节省时间，您还可以“切换到”或“使用”数据库，以将新数据库设置为所有数据库相关命令的默认上下文。

要切换到其他数据库，请键入

USE <db_name>;

在我们的例子中，我们可以通过键入以下内容切换到我们创建的 school 数据库

USE school;

在数据库中创建表

创建数据库后，您可以在其中定义表和其他对象来存储和管理数据。在 MySQL 中，表由表名、列定义（这些定义反过来包括名称、数据类型和约束等）以及表选项组成。

MySQL 的 `CREATE TABLE` 命令的语法是什么？

使用 MySQL 的 CREATE TABLE 命令创建表的基本语法如下所示

CREATE TABLE <table_name> (
    <column_name> <data_type> [<column_constraint>],
    [<table_constraint>,]
);

我们可以将上面的命令模板分解成以下部分

CREATE TABLE <table_name>: 基本创建语句。 <table_name> 占位符应替换为要使用的表的名称。
<column_name> <data_type>: 在表中定义一个基本列。 <column_name> 占位符应替换为要用于列的名称。 <data_type> 指定列的 MySQL 数据类型。存储在表中的数据必须符合列结构和列数据类型才能被接受。
<column_constraint>: 列约束是可选的限制，为数据添加其他要求。例如，您可以要求条目不能为空、唯一或正整数。
<table_constraint>: 表约束类似于列约束，但涉及多个列的交互。例如，您可以有一个表约束，检查 DATE_OF_BIRTH 是否早于表中的 DATE_OF_DEATH。

如何仅在表不存在的情况下创建表？

MySQL 的默认行为是在尝试创建已经存在的表时引发错误。但是，可以在创建语句中添加可选的 IF NOT EXISTS 子句来覆盖此行为。

您可以在 CREATE TABLE 短语之后但在表名前插入该子句来使用 IF NOT EXISTS 子句

CREATE TABLE IF NOT EXISTS table_name (
    column_name TYPE [column_constraint],
    [table_constraint,]
);

这将修改命令的行为，如下所示：如果指定表名已经存在，MySQL 将发出警告而不是错误。命令的其余行为保持不变。

在 MySQL 数据库中创建表

上面给出的命令模板足以创建基本表。为了进一步说明我们的示例，我们将在我们的 school 数据库中创建两个表。一个表将被称为 supplies，另一个将被称为 teachers

在 supplies 表中，我们将包含以下字段

id: 每个类型学校用品的唯一 ID。
name: 特定学校项目的名称。
description: 项目的简短描述。
manufacturer: 项目制造商的名称。
color: 项目的颜色。
inventory: 我们拥有的某种类型学校用品的数量。这永远不应该小于 0。

我们可以使用以下 SQL 创建具有上述特性的 supplies 表

CREATE TABLE supplies (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  description VARCHAR(255),
  manufacturer VARCHAR(255),
  color VARCHAR(255),
  inventory INT CHECK (inventory >= 0)
);

这将在 school 数据库中创建 supplies 表。 PRIMARY KEY 列约束是一种特殊约束，用于指示可以唯一标识表中记录的列。因此，约束指定该列不能为 null 且必须唯一。MySQL 为主键列创建索引以提高查询速度。

通过键入以下内容来验证新表是否存在

SHOW TABLES;

+------------------+
  Tables_in_school |
+------------------+
  supplies         |
+------------------+
1 row in set (0.01 sec)

您可以通过键入以下内容来验证架构是否反映您的定义

SHOW CREATE TABLE supplies\G

*************************** 1. row ***************************
       Table: supplies
Create Table: CREATE TABLE `supplies` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `manufacturer` varchar(255) DEFAULT NULL,
  `color` varchar(255) DEFAULT NULL,
  `inventory` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `supplies_chk_1` CHECK ((`inventory` >= 0))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec

我们指定的列、数据类型和约束存在于输出中，尽管排序和显示可能不同。

接下来，创建一个 teachers 表。在这个表中，应该存在以下列

id: 唯一的员工识别号。
first_name: 教师的姓。
last_name: 教师的姓。
subject: 教师受雇教授的科目。
grade_level: 教师受雇教授的学生年级。

使用以下 SQL 创建具有上述架构的 teachers 表

CREATE TABLE teachers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(255),
  last_name VARCHAR(255),
  subject VARCHAR(255),
  grade_level INT
);

如何创建具有主键和外键的表

您可以在我们的一些其他 MySQL 指南中找到有关创建具有主键和外键的表的更多信息。主键和外键都是 MySQL 中数据库约束的类型。

一个主键是一个特殊列或列，保证在同一表中的行中是唯一的。所有主键都可以用来唯一标识特定行。主键不仅确保每行在主键列中都具有唯一值，而且还确保没有任何行在该列中包含 NULL 值。通常，MySQL 中的主键使用以下格式来指定自动分配的递增主键：id INT AUTO_INCREMENT PRIMARY KEY。

外键是一种确保一个表中的列或列与另一个表中包含的值匹配的方法。这有助于确保表之间的引用完整性。

如何在 MySQL 中查看表

在 MySQL 中，您可以通过几种不同的方法列出表，具体取决于您要查找的信息。

如果您想查看数据库中有哪些表，请输入

SHOW TABLES;

+------------------+
  Tables_in_school |
+------------------+
  supplies         |
  teachers         |
+------------------+
2 rows in set (0.00 sec)

您还可以检查表的架构是否与您的规格匹配

DESCRIBE teachers;

+--------------+--------------+------+-----+---------+----------------+
  Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
  id           | int          | NO   | PRI | NULL    | auto_increment |
  name         | varchar(255) | YES  |     | NULL    |                |
  description  | varchar(255) | YES  |     | NULL    |                |
  manufacturer | varchar(255) | YES  |     | NULL    |                |
  color        | varchar(255) | YES  |     | NULL    |                |
  inventory    | int          | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

teachers 表似乎与我们的定义相符。

如何使用 `ALTER TABLE` 命令更改表

如果您需要更改 MySQL 中现有表的架构，可以使用 ALTER TABLE 命令。 ALTER TABLE 命令与 CREATE TABLE 命令非常相似，但作用于现有表。

更改表语法

在 MySQL 中修改表的语法如下所示

ALTER TABLE <table_name> <change_command> <change_parameters>;

<change_command> 指示您要进行的更改的具体类型，无论它涉及设置表的不同选项、添加或删除列，还是更改类型或约束。命令的 <change_parameters> 部分包含 MySQL 完成更改所需的任何其他信息。

向表添加列

您可以使用 ADD 更改命令向 MySQL 表添加列。更改参数将包括列名、类型和选项，就像您在 CREATE TABLE 命令中指定它们一样。

例如，要向名为 some_table 的表添加一个名为 missing_column 的 text 类型列，您需要键入以下内容

ALTER TABLE some_table ADD missing_column text;

从表中删除列

如果您想删除现有列，可以使用 DROP COLUMN 命令。您需要指定要删除的列的名称作为更改参数

ALTER TABLE some_table DROP COLUMN useless_column;

更改列的数据类型

要更改列的数据类型，可以使用 ALTER COLUMN 更改命令以及 MODIFY COLUMN 列命令。此上下文中的参数包括列名及其新类型。

ALTER TABLE resident MODIFY COLUMN id INT;

其他表更改

可以使用 ALTER TABLE 命令实现许多其他类型的更改。有关可用选项的更多信息，请查看官方 MySQL 文档 ALTER TABLE。

删除表

如果您想删除表，可以使用 DROP TABLE SQL 语句。这将删除表以及其中存储的所有数据。

基本语法如下所示

DROP TABLE <table_name>;

如果表存在，这将删除该表，如果表名不存在，则抛出错误。

如果您希望在表存在时删除该表，并且如果该表不存在则不执行任何操作，则可以在语句中包含 IF EXISTS 限定符。

DROP TABLE IF EXISTS <table_name>;

默认情况下，在这些依赖项存在时，无法删除依赖于其他表或对象的表。

通过键入以下内容来删除我们之前创建的 supplies 表

DROP TABLE supplies;

我们将保留 teachers 数据库来演示删除数据库的语句也将删除所有子对象（如表）。

删除数据库

该 DROP DATABASE 语句告诉 MySQL 删除指定的数据库。基本语法如下所示

DROP DATABASE <database_name>;

用要删除的数据库的名称替换 <database_name> 占位符。如果找到数据库，这将删除数据库。如果找不到数据库，将发生错误。

DROP DATABASE some_database;

ERROR 1008 (HY000): Can't drop database 'some_database'; database doesn't exist

如果您希望在数据库存在时删除数据库，否则不执行任何操作，请包含可选的 IF EXISTS 选项。

DROP DATABASE IF EXISTS some_database;

Query OK, 0 rows affected, 1 warning (0.00 sec)

这将删除数据库，如果找不到数据库，则不执行任何操作。

要删除我们在本指南中使用的 school 数据库，请列出系统上的现有数据库

SHOW DATABASES;

+--------------------+
  Database           |
+--------------------+
  information_schema |
  mysql              |
  performance_schema |
  school             |
  sys                |
  testing            |
+--------------------+
6 rows in set (0.00 sec)

使用以下命令删除 school 数据库

DROP DATABASE school;

这将删除 school 数据库以及其中定义的 teachers 表。

结论

本文介绍了使用 MySQL 创建和删除数据库和表的知识。这些是设置数据库系统和定义数据结构所需的一些最基本命令。

如前所述，本 MySQL 教程中介绍的 SQL 语句，特别是 CREATE TABLE 语句，具有许多其他参数，可用于更改系统的行为。您可以通过查看官方 MySQL 文档来了解有关这些参数的更多信息。

常见问题

如何在 MySQL Workbench 中创建数据库？

在 MySQL Workbench 中，您首先需要创建一个新模型。

拥有模型后，您就可以添加一个新的模式，在 MySQL 中，这等同于一个新数据库。

如何在 MySQL 中更改表并在其中添加列？

在 MySQL 中更改表的语法是使用 ALTER TABLE，基本语法如下所示

ALTER TABLE <table_name> <change_command> <change_parameters>;

该 <change_command> 指示您想要进行的确切更改类型，无论它涉及设置表的不同选项、添加或删除列，还是更改类型或约束。

该 <change_parameters> 命令的一部分包含 MySQL 完成更改所需的任何其他信息。

要添加表，可以使用 ADD 更改命令。它看起来像

ALTER TABLE some_table ADD missing_column text;

如何在 MySQL 数据库中删除所有表？

在 MySQL 中，没有单个命令可以一次删除所有表。您需要一个脚本来遍历所有表。

删除表的语法使用 DROP TABLE SQL 语句，可以选择包含 IF EXISTS。

基本语法为

DROP TABLE IF EXISTS <table_name>;

如何在 MySQL 中删除索引？

要在 MySQL 中删除索引，可以使用 DROP INDEX 命令。基本语法包括 <index_name> 和 <table_name>。

它看起来像

DROP INDEX <index_name> ON <table_name>;

如何在 MySQL 中创建临时表？

您可以在 MySQL 中创建表时使用 TEMPORARY 关键字。这将创建一个临时表，该表仅在当前会话中可见，并在会话关闭时自动删除。

基本语法与创建表时相同，只是包含了 TEMPORARY。它看起来如下所示

CREATE TEMPORARY TABLE table_name(
   column_1_definition,
   column_2_definition,
   ...,
   table_constraints
);

关于作者

Justin Ellingwood

Justin 自 2013 年以来一直在撰写有关数据库、Linux、基础设施和开发人员工具的文章。他目前与妻子和两只兔子住在柏林。他通常不必以第三人称写作，这对所有相关方来说都是一种解脱。

上一个

连接到 MySQL 数据库

下一个

MySQL 数据类型的简介

在 GitHub 上编辑此页面