如何在SQL中创建和管理表

介绍

表是 SQL 数据库中的主要组织结构。它们由许多列组成，这些列反映了表中每一行或记录的各个属性。作为数据组织的一个基本方面，任何使用关系数据库的人都必须了解如何根据需要创建、更改和删除表。

在本指南中，我们将介绍如何在 SQL 中创建表，以及如何修改和删除现有表。

先决条件

为了遵循本指南，您需要一台运行某种类型的使用 SQL 的关系数据库管理系统 (RDBMS) 的计算机。本指南中的说明和示例已使用以下环境进行验证：

运行 Ubuntu 20.04 的服务器，具有管理权限的非 root 用户和配置了 UFW 的防火墙，如我们的 Ubuntu 20.04 初始服务器设置指南中所述。
如如何在 Ubuntu 20.04 上安装 MySQL 中所述，在服务器上安装并保护了 MySQL。本指南已通过非 root MySQL 用户验证，使用 Step 3 中描述的过程创建。

注意：请注意，许多 RDBMS 使用自己独特的 SQL 实现。尽管本教程中概述的命令适用于大多数 RDBMS，但如果您在 MySQL 以外的系统上测试它们，确切的语法或输出可能会有所不同。

您还需要一个加载了一些示例数据的数据库和表，您可以使用这些数据练习使用通配符。如果您没有这些，您可以阅读以下 连接到 MySQL 并设置示例数据库 部分，了解有关如何创建本指南将在整个示例中使用的数据库和表的详细信息。

您还可以使用嵌入在此页面上的交互式终端来试验本教程中的示例查询。单击下面的Launch an Interactive Terminal!按钮开始使用。

启动交互式终端！

连接到 MySQL 并设置示例数据库

如果您的 SQL 数据库系统在远程服务器上运行，请从本地计算机通过 SSH 连接到您的服务器：

ssh sammy@your_server_ip

然后打开 MySQL 服务器提示符，将 sammy 替换为您的 MySQL 用户帐户的名称。如果您在此页面上使用嵌入式交互式终端，请注意提示时使用的密码是单词 secret：

mysql -u sammy -p

创建一个名为 tablesDB 的数据库：

CREATE DATABASE tablesDB;

如果数据库创建成功，您将收到如下输出：

OutputQuery OK, 1 row affected (0.01 sec)

要选择 tablesDB 数据库，请运行以下 USE 语句：

USE tablesDB;

OutputDatabase changed

有了它，您就可以按照本指南的其余部分开始学习如何在 SQL 中创建和管理表了。

创建表

要在 SQL 中创建表，请使用 CREATE TABLE 命令，后跟所需的表名称：

CREATE TABLE table_name;

请注意，与每个 SQL 语句一样，CREATE TABLE 语句必须以分号 (;) 结尾。

此示例语法将创建一个没有任何列的空表。要创建包含列的表，请在表名后面加上列名列表及其对应的数据类型和约束，用括号括起来并用逗号分隔：

CREATE TABLE table_name (
column1_name column1_data_type,
column2_name column2_data_type,
. . .
columnN_name columnN_data_type
);

例如，假设您想创建一个表格来记录有关您在纽约市最喜欢的公园的一些信息。在决定了要记录每个公园的哪些属性之后，您将决定每个属性的列名以及每个属性的适当数据类型：

parkName：每个公园的名称。公园名称的长度存在很大差异，因此最大长度为 30 字符的 varchar 数据类型是合适的。
yearBuilt：公园建成年份。虽然 MySQL 具有 year 数据类型，但这仅允许从 1901 到 2155 的值。纽约市有几个公园建于 1901 年之前，因此您可以改用 int 数据类型。
firstVisit：您第一次访问每个公园的日期。 MySQL 具有 date 数据类型，您可以将其用于此列。它以 YYYY-MM-DD 的格式存储数据。
lastVisit：您最近访问每个公园的日期。同样，您可以为此使用 date 类型。

要使用具有这些名称和数据类型的列创建名为 faveParks 的表，您将运行以下命令：

CREATE TABLE faveParks (
parkName varchar(30),
yearBuilt int,
firstVisit date,
lastVisit date
);

OutputQuery OK, 0 rows affected (0.01 sec)

请记住，这只会创建表的结构，因为您没有向表中添加任何数据。

您还可以使用 CREATE TABLE AS 语法从现有表中创建新表：

CREATE TABLE new_table_name AS (
SELECT column1, column2, . . . columnN
FROM old_table_name
);

不是在新表的名称后面加上列及其数据类型的列表，而是在它后面加上 AS，然后在括号中加上一个 SELECT 语句，该语句返回原始列和数据您想复制到新表的表。

请注意，如果原始表的列包含任何数据，则所有这些数据也将被复制到新表中。此外，为清楚起见，此示例语法包括一个 SELECT 查询，该查询仅具有必需的 FROM 子句。但是，任何有效的 SELECT 语句都可以在这个地方工作。

为了说明，以下命令从先前创建的 faveParks 表中的两列创建一个名为 parkInfo 的表：

CREATE TABLE parkInfo AS (
SELECT parkName, yearBuilt
FROM faveParks
);

OutputQuery OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

如果 faveParks 表包含任何数据，则其 parkName 和 yearBuilt 列中的数据也将被复制到 parkInfo 表中，但在在这种情况下，两个表都是空的。

如果您尝试使用现有表的名称创建表，则会导致错误：

CREATE TABLE parkInfo (
name varchar(30),
squareFootage int,
designer varchar(30)
);

OutputERROR 1050 (42S01): Table 'parkInfo' already exists

为避免此错误，您可以在 CREATE TABLE 命令中包含 IF NOT EXISTS 选项。这将告诉数据库检查具有指定名称的数据库是否已经存在，如果存在，则发出警告而不是错误：

CREATE TABLE IF NOT EXISTS parkInfo (
name varchar(30),
squareFootage int,
designer varchar(30)
);

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

该命令仍然无法创建新表，因为名为 parkInfo 的表仍然存在。但请注意，此输出表明 CREATE TABLE 语句导致了警告。要查看警告消息，请运行诊断 SHOW WARNINGS 语句：

SHOW WARNINGS;

Output| Level | Code | Message                         |
+-------+------+---------------------------------+
| Note  | 1050 | Table 'parkInfo' already exists |
+-------+------+---------------------------------+
1 row in set (0.00 sec)

正如此输出所示，您之前收到的相同错误已注册为警告，因为您包含了 IF NOT EXISTS 选项。这在某些情况下很有用，比如在运行事务时；错误将导致整个事务失败，而警告将意味着只有导致它失败的语句。

更改表

有时您可能需要更改表的定义。这与更新表内的数据不同；相反，它涉及更改表本身的结构。为此，您将使用 ALTER TABLE 语法：

ALTER TABLE table_name ALTER_OPTION sub_options . . . ;

在开始 ALTER TABLE 语句后，指定要更改的表的名称。然后，您通过 RDBMS 中可用的任何选项来执行您想要的更改。

例如，您可能想要重命名表、添加新列、删除旧列或更改列的定义。您可以继续阅读以在先前在创建表部分中创建的 faveParks 表上练习这些示例。

要更改 faveParks 表的名称，您可以使用 RENAME TO 语法。此示例将 faveParks 表的名称更改为 faveNYCParks：

Warning：重命名表时要小心。如果应用程序使用该表或数据库中的其他表引用它，这样做可能会导致问题。

ALTER TABLE faveParks RENAME TO faveNYCParks;

OutputQuery OK, 0 rows affected (0.01 sec)

要添加新列，您需要传递 ADD COLUMN 选项。以下示例将名为 borough 的列添加到 faveNYCParks 表中，该列包含 varchar 类型的数据，但最大长度为 20 个字符：

ALTER TABLE faveNYCParks ADD COLUMN borough varchar(20);

OutputQuery OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

要从表中删除列及其包含的任何数据，您可以使用 DROP TABLE 语法。此示例命令删除 borough 列：

ALTER TABLE faveNYCParks DROP COLUMN borough;

许多 SQL 实现允许您使用 ALTER TABLE 更改列的定义。以下示例使用 MySQL 的 MODIFY COLUMN 子句，将 yearBuilt 列更改为使用 smallint 数据类型，而不是原来的 int 类型：

ALTER TABLE faveNYCParks MODIFY COLUMN yearBuilt smallint;

请注意，对于您可以使用 ALTER TABLE 语句更改的内容，每个 RDBMS 都有不同的选项。要了解使用 ALTER TABLE 可以做什么的全部范围，您应该查阅 RDBMS 的官方文档以了解可用的 ALTER TABLE 选项。

以下是一些流行的开源数据库的官方文档：

删除表

要删除表及其所有数据，请使用 DROP TABLE 语法：

Warning：运行 DROP TABLE 命令时要小心，因为它会永久删除您的表及其所有数据。

DROP TABLE table_name;

您可以使用单个 DROP 语句删除多个表，方法是用逗号和空格分隔它们的名称，如下所示：

DROP TABLE table1, table2, table3;

为了说明，以下命令将删除本指南前面创建的 faveNYCParks 和 parkInfo 表：

DROP TABLE IF EXISTS faveNYCParks, parkInfo;

请注意，此示例包括 IF EXISTS 选项。这与可用于 CREATE TABLE 的 IF NOT EXISTS 选项的功能相反。在这种情况下，如果指定的表之一不存在，IF EXISTS 将导致 DROP TABLE 语句返回警告而不是错误消息。

结论

通过阅读本指南，您了解了如何在基于 SQL 的数据库中创建、更改和删除表。此处列出的命令应该适用于任何使用 SQL 的数据库管理系统。请记住，每个 SQL 数据库都使用自己独特的语言实现，因此您应该查阅 DBMS 的官方文档以获取对每个命令及其全套选项的更完整描述。

如果您想了解有关使用 SQL 的更多信息，我们鼓励您查看本系列中关于如何使用 SQL 的其他教程。