Sql 简明教程

SQL - CREATE Table

本教程将教您如何使用 SQL 在 RDBMS 中创建表。我们使用 CREATE TABLE command 在数据库中创建表。

This tutorial will teach you how to use SQL to create tables in RDBMS. We use CREATE TABLE command to create a Table in a Database.

在 RDBMS 中，数据库表用于以某种结构（字段和记录）的形式存储数据。在 RDBMS 中， field 是一个列，它定义了表中要存储的数据类型， record 是包含实际数据的一行。简单来说，我们可以说表是行和列的组合。

In RDBMS, Database tables are used to store the data in the form of some structures (fields and records). Here, a field is a column defining the type of data to be stored in a table and record is a row containing actual data. In simple words, we can say a Table is a combination of rows and columns.

SQL 提供了多种查询，可以方便地与数据进行交互。我们可以使用 SQL 语句在这些表中创建和删除表，插入、更新和删除数据。

SQL provides various queries to interact with the data in a convenient way. We can use SQL statements to create and delete tables, inserting, updating and deleting data in these tables.

有关 RDBMS 中不同概念的更多详细信息，请查看 RDBMS Concepts 教程。

For a more detail on different concepts related to RDBMS please check RDBMS Concepts tutorial.

The SQL CREATE TABLE Statement

SQL 提供了 CREATE TABLE 语句，用于在给定数据库中创建新表。用于创建表的 SQL 查询必须定义表的结构。结构由表的名称和表中列的名称及其每个列的数据类型组成。请注意，每个表在数据库中必须有唯一的名称。

SQL provides the CREATE TABLE statement to create a new table in a given database. An SQL query to create a table must define the structure of a table. The structure consists of the name of a table and names of columns in the table with each column’s data type. Note that each table must be uniquely named in a database.

Syntax

CREATE TABLE 语句用于在数据库中创建新表。 -

CREATE TABLE statement is used to create a new table in a database. −

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

以下是要点 -

Here are the key points-

CREATE TABLE is the keyword telling the database system what you want to do. In this case, you want to create a new table. The unique name or identifier for the table follows the CREATE TABLE statement.
The column parameters (e.g. column1, column2, column3, etc.) specify the names of the columns of the table.
The datatype parameter specifies the type of data the column can hold (e.g. integer, varchar, string, etc.).
PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values.

Example: Creating Table in SQL

CREATE TABLE CUSTOMERS(
   ID          INT NOT NULL,
   NAME        VARCHAR (20) NOT NULL,
   AGE         INT NOT NULL,
   ADDRESS     CHAR (25),
   SALARY      DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

以下是要点 -

Here are the key points-

The following code block is an example, which creates a CUSTOMERS table with column name ID, NAME, AGE, ADDRESS and, SALARY and ID as a primary key.
NOT NULL are the constraints showing that these fields cannot be NULL while creating records in this table.

Verification

创建表后，可以检查它是否已成功创建。可以使用 SQL DESC table_name 命令按以下方式列出表的说明：

Once your table is created, you can check if it has been created successfully or not. You can use SQL DESC table_name command to list down the description of the table as follows:

DESC CUSTOMERS;

这将显示创建的表的结构：列名称、它们各自的数据类型、约束（如果有）等等。

This will display the structure of the table created: column names, their respective data types, constraints (if any) etc.

Field

Type

Null

Key

Default

Extra

int(11)

PRI

NULL

NAME

varchar(20)

NULL

AGE

int(11)

NULL

ADDRESS

char(25)

YES

NULL

SALARY

decimal(18,2)

YES

NULL

现在，你的数据库中已经有了 CUSTOMERS 表，可以使用此表来存储与客户相关的信息。

Now, you have CUSTOMERS table available in your database which you can use to store the required information related to customers.

SQL CREATE TABLE IF NOT EXISTS

考虑一种情况，即你尝试创建一个已经存在表，在这种情况下 MySQL 将抛出以下错误。

Consider a situation where you will try to create a table which already exists, in such situation MySQL will throw the following error.

ERROR 1050 (42S01): Table 'CUSTOMERS' already exists

因此，为了避免此错误，可以使用 SQL 命令 CREATE TABLE IF NOT EXISTS 来创建表。

So to avoid such error we can use SQL command CREATE TABLE IF NOT EXISTS to create a table.

Syntax

以下是 CREATE TABLE IF NOT EXISTS 语句的基本语法 −

Following is the basic syntax of a CREATE TABLE IF NOT EXISTS statement −

CREATE TABLE IF NOT EXISTS table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

Example: Creating Table if Not Exists

只有当不存在具有相同名称的表时，以下 SQL 命令才会创建 CUSTOMERS 表，否则它将退出并且不出现任何错误。

The following SQL command will create the CUSTOMERS table only when there is no table exists with the same name otherwise it will exit without any error.

CREATE TABLE IF NOT EXISTS CUSTOMERS(
   ID          INT NOT NULL,
   NAME        VARCHAR (20) NOT NULL,
   AGE         INT NOT NULL,
   ADDRESS     CHAR (25),
   SALARY      DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Creating a Table from an Existing Table

除了每次创建一个新表之外，还可以复制一个现有表及其内容（包括其结构）到一个新表中。可以使用 CREATE TABLE 语句和 SELECT 语句的组合来完成此操作。由于复制了其结构，因此新表将具有与原始表相同的列定义。此外，将使用旧表中的现有值填充新表。

Instead of creating a new table every time, one can also copy an existing table and its contents including its structure, into a new table. This can be done using a combination of the CREATE TABLE statement and the SELECT statement. Since its structure is copied, the new table will have the same column definitions as the original table. Furthermore, the new table would be populated using the existing values from the old table.

Syntax

从另一个表创建表的的基本语法如下 −

The basic syntax for creating a table from another table is as follows −

CREATE TABLE NEW_TABLE_NAME AS
SELECT [column1, column2...columnN]
FROM EXISTING_TABLE_NAME
WHERE Condition;

此处，column1、column2 等是现有表中的字段，并且将被用于创建新表的字段。

Here, column1, column2… are the fields of the existing table and the same would be used to create fields of the new table.

Example: Creating Table from an Existing Table

以下是一个示例，它将使用 CUSTOMERS 表以及包含客户 ID 和客户 SALARY 的字段来创建 SALARY 表 −

Following is an example, which would create a table SALARY using the CUSTOMERS table and having the fields customer ID and customer SALARY −

CREATE TABLE SALARY AS
SELECT ID, SALARY
FROM CUSTOMERS;

这将创建一个新的 SALARY 表，它将具有以下结构 −

This will create a new table SALARY which will have the following structure −

Field

Type

Null

Key

Default

Extra

int(11)

PRI

NULL

SALARY

decimal(18,2)

YES

NULL