Mysql 简明教程

MySQL - Create Tables

在 MySQL 关系数据库系统中，SQL 用于以结构化表格的形式存储数据。这些表格由字段和记录组成。一个字段表示一个定义了要存储在表格中的数据类型的列，记录是一行，它包含实际数据。MySQL 提供了各种查询来与数据交互，允许你创建表格、更新表格、删除表格等。

In the MySQL relational database system, SQL is used to store data in the form of structured tables. These tables consist of fields and records. A field represents a column that defines the type of data to be stored in a table, and a record is a row containing the actual data. MySQL provides various queries to interact with the data, allowing you to create tables, update them, delete them, etc.

MySQL Create Table Statement

要在提示符中创建 MySQL RDBMS 表，请使用 CREATE TABLE 语句。

To create a table in MySQL RDBMS in prompt, CREATE TABLE statement is used.

MySQL 创建表格的查询必须定义表格的结构。结构由表格的名称和表格中的列名称加上各列的数据类型组成。请注意，每个表格在数据库中都必须有唯一名称。

A MySQL 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.

首先，表格创建命令需要以下详细信息 -

To begin with, the table creation command requires the following details −

Name of the table.
Name of the columns.
Definitions for each column.

Syntax

以下是创建 MySQL 表的基本 SQL 语法 -

Following is the basic SQL syntax to create a MySQL table −

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

Example

在以下查询中，我们使用 CREATE TABLE 语句创建一个名为 CUSTOMERS 的表 -

In the following query, we are creating a table named CUSTOMERS using the CREATE TABLE Statement −

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

此处，需要对几个项目进行说明 -

Here, a few items need explanation −

Field Attribute AUTO_INCREMENT in MySQL automatically increments the value in the ID column by one for each new record you add. It starts from the next available number.
Field Attribute NOT NULL is being used because we do not want this field to be NULL. So, if a user tries to create a record with a NULL value in that field, then MySQL will raise an error.
Keyword PRIMARY KEY is used to define a column as a primary key. It ensures that every record in that column is unique. You can also use it for multiple columns by separating them with commas.

Output

当我们执行上述查询时，输出将获得如下：

When we execute the above query, the output is obtained as follows −

Query OK, 0 rows affected (0.03 sec)

Verification

完成创建表后，我们可以使用以下查询检查是否成功创建 -

Once we have finished creating the table, we can check whether it has been created successfully or not using the following query −

DESC CUSTOMERS;

上述查询显示 CUSTOMERS 表的结构：列名称、数据类型等。

The above query displays the structure of the CUSTOMERS table: column names, their datatypes, etc.

Creating Tables from Command Prompt

我们可以通过定义结构和列从命令提示符创建 MySQL 表。

We can create a MySQL table from the command prompt by defining its structure and columns.

以下是在命令提示符下创建 MySQL 表的操作步骤：

Following are the steps to perform to create a MySQL table from Command Prompt:

Firstly, open the command prompt and enter the following command: mysql -u root -p to access the MySQL database management system.
After entering the command, enter the password to log into the MySQL server.
Then, we can start creating a table using the respected SQL CREATE TABLE query.

Example

在以下示例中，我们从命令提示符创建一个名为 CUSTOMERS 的 MySQL 表。

In the following example, we are creating a MySQL table named CUSTOMERS from command prompt.

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

NOTE - MySQL 在 SQL 命令末尾输入分号 (;) 之前不会终止命令。

NOTE − MySQL does not terminate a command until you give a semicolon (;) at the end of SQL command.

Output

当我们执行上述查询时，输出将获得如下：

When we execute the above query, the output is obtained as follows −

Query OK, 0 rows affected (0.03 sec)

Verification

我们可以使用以下查询验证是否成功创建表 -

We can verify if the table has been created successfully or not using the following query −

mysql> DESC CUSTOMERS;

上述查询将显示 CUSTOMERS 表的结构和说明 -

The above query will show the structure and description of the CUSTOMERS table −

Creating a Table from an Existing Table

我们可以通过使用 SQL CREATE TABLE 和 SELECT 语句创建现有表的副本表，包括其结构和数据。副本表具有相同的列及其定义，并且还会填充与原始表相同的数据。

We can create a duplicate table of an existing table including its structure and data, by using the SQL CREATE TABLE and SELECT statements. The duplicate table has the same columns and their definitions, and it also gets filled with the same data as the original table.

Note - 因为它是一个全新的表，所以对它所做的任何更改都不会反映在源表中。

Note − As it is a completely new table, any changes made in it would not be reflected in the original table.

Syntax

以下是通过其他表创建表的语法 -

Following is the syntax for creating a table from another table −

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

此处，column1、column2…是现有表的字段，可以用来创建新表的字段。并且 WHERE 子句可以随时使用。

Here, column1, column2… are the fields of the existing table and the same would be used to create fields of the new table. And the WHERE clause is optional to use.

Example

我们来考虑 TUTORIALS 数据库中现有的表 CUSTOMERS −

Let us consider an existing table CUSTOMERS in the TUTORIALS database −

mysql> USE TUTORIALS;
Database changed
mysql> SELECT * FROM CUSTOMERS;

以下为 CUSTOMERS 表 −

Following is the CUSTOMERS table −

现在，使用以下查询，我们正创建一个名为 SAMPLE 的新表，其结构和记录与 CUSTOMERS 相同。

Now, Using the following query, we are creating a new table named SAMPLE with the same structure and records as CUSTOMERS.

CREATE TABLE SAMPLE AS
SELECT * FROM CUSTOMERS;

Output

正如我们在输出中看到的那样，表 SAMPLE 已成功创建。

As we can see in the output, the table SAMPLE is created successfully.

Query OK, 7 rows affected (0.03 sec)
Records: 7  Duplicates: 0  Warnings: 0

Verification

使用以下 SELECT 语句，让我们验证新表 SAMPLE 是否包含记录。

Using the following SELECT statement, let us verify if the new table SAMPLE contains the records or not.

SELECT * FROM SAMPLE;

就像我们在下面的输出中看到的，SAMPLE 表已创建，并且包含 CUSTOMER 表中的所有记录。 −

As we can in the output below, the SAMPLE table has been created with all the records from the CUSTOMERS table. −

The IF NOT EXISTS clause

如果您尝试使用现有名称创建一个表，将生成一个错误。 −

If you try to create a table with an existing name an error will be generated −

CREATE TABLE Employee(Name VARCHAR(255));
ERROR 1050 (42S01): Table 'employee' already exists

如果您按如下所示使用 IF NOT EXISTS 子句以及 CREATE 语句，将创建一个新表，并且如果存在具有给定名称的表，则将忽略该查询。

If you use the IF NOT EXISTS clause along with the CREATE statement as shown below a new table will be created and if a table with the given name, already exists the query will be ignored.

CREATE TABLE Test(Name VARCHAR(255));
Query OK, 0 rows affected (0.69 sec)

Create table into MySQL Database Using a Client Program

除了使用 MySQL 查询将表创建到 MySQL 数据库中，我们还可以使用客户端程序执行 CREATE TABLE 操作。

In addition to Create a table into MySQL Database using the MySQL query, we can also perform the CREATE TABLE operation using a client program.

Syntax

以下是在各种编程语言中创建表的语法 −

Following are the syntaxes to Create a table in various programming languages −

Example

以下是这些程序 −

Following are the programs −