Sql 简明教程

SQL - INSERT Query

The SQL INSERT INTO Statement

SQL INSERT INTO Statement 用于向数据库中的表中添加新数据行。几乎所有 RDBMS 都提供此 SQL 查询，用于向数据库表中添加记录。

The SQL INSERT INTO Statement is used to add new rows of data into a table in the database. Almost all the RDBMS provide this SQL query to add the records in database tables.

我们在表中使用此语句插入每条记录的值都应该与相应列属于相同的数据类型，并满足该列的约束（如果有）。使用插入语句传递的值应该匹配表中的列数，或者匹配当前查询中提及的列数。如果任何这些条件不满足，此语句会生成错误。

Each value in the records we are inserting in a table using this statement should be of the same datatype as the respective column and satisfy the constraints of the column (if any). The values passed using an insert statement should match the number of columns in the table or, the number of columns mentioned in the current query. If any of these conditions are not satisfied, this statement generates an error.

Syntax

SQL INSERT INTO 语句有两个基本语法，如下所示−

There are two basic syntaxes of the SQL INSERT INTO statement which are shown below −

INSERT INTO TABLE_NAME (column1, column2...columnN)
VALUES (value1, value2...valueN);

此处，column1、column2、column3、…​columnN 是要向其插入数据的表中的列名。

Here, column1, column2, column3,…​columnN are the names of the columns in the table into which you want to insert the data.

还有另一个 INSERT INTO 语句的语法，它只能指定列值，而不指定列名。但是，确保这些值按表中的列顺序排列。

There is another syntax of INSERT INTO statement where you can specify only column values without column names. But, make sure the order of the values is in the same order as the columns in the table.

以下是 SQL INSERT 查询的第二个语法 −

Following is second syntax of the SQL INSERT Query −

INSERT INTO TABLE_NAME
VALUES (value1,value2...valueN);

Example

让我们创建一个名为 CUSTOMERS 的表，在 MySQL 数据库中使用 CREATE TABLE 语句，如下所示 −

To see an example, let us create a table with name CUSTOMERS in the MySQL database using the CREATE TABLE statement as shown below −

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)
);

以下 SQL INSERT INTO 语句将在空 CUSTOMERS 表中创建三条记录。

The following SQL INSERT INTO statements will create three records in the empty CUSTOMERS table.

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Kaushik', 23, 'Kota', 2000.00 );

我们还可以使用以下查询插入 multiple rows at once ，如下所示 −

We can also insert multiple rows at once using the following query as shown below −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 );

以下查询使用第二个语法在 CUSTOMERS 表中添加另一条记录，如下所示 −

Following query adds another record in the CUSTOMERS table using the second syntax as shown below −

INSERT INTO CUSTOMERS
VALUES (7, 'Muffy', 24, 'Indore', 10000.00 );

Verification

若要检查记录是否已插入 CUSTOMERS 表，请使用 SELECT 查询 −

To check if the records are inserted into the CUSTOMERS table, use the SELECT query −

SELECT * FROM CUSTOMERS;

将显示包含在该表中的所有记录的表。

The table will be displayed with all the records included in it.

ID

NAME

AGE

ADDRESS

SALARY

1

Ramesh

32

Ahmedabad

2000.00

2

Khilan

25

Delhi

1500.00

3

Kaushik

23

Kota

2000.00

4

Chaitali

25

Mumbai

6500.00

5

Hardik

27

Bhopal

8500.00

6

Komal

22

Hyderabad

4500.00

7

Muffy

24

Indore

10000.00

Inserting Data into a Table Using Another

有时，您只需将数据从现有表复制到同一数据库中的另一张表中。SQL 提供了便捷的方法来执行此操作 −

Sometimes, you just need to copy the data from an existing table to another table in the same database. SQL provides convenient ways to do so −

  1. Using INSERT…​ SELECT

  2. Using INSERT…​ TABLE

The INSERT…​ SELECT Statement

您可以通过使用现有另一张表的 select 语句将数据填充到一张表中；前提是另一张表具有一组字段，为填充第一张表所必需。

You can populate the data into a table through the select statement using an already existing another table; provided the other table has a set of fields, which are required to populate the first table.

以下是语法 −

Here is the syntax −

INSERT INTO first_table_name [(column_name(s))]
SELECT column1, column2, ...columnN
FROM second_table_name
[WHERE condition];

Example

以下语句将创建另一张表，其名称为 BUYERS ，与 CUSTOMERS 表的结构相同 −

The following statement would create another table named BUYERS with the same structure as CUSTOMERS table −

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

现在使用 INSERT…​ INTO 语句，让我们将 CUSTOMERS 表中的所有记录插入到 BUYERS 表中。

Now using the INSERT…​ INTO statement, let us insert all the records from the CUSTOMERS table into the BUYERS table.

INSERT INTO BUYERS (ID, NAME, AGE, ADDRESS, SALARY)
SELECT * FROM CUSTOMERS;

Output

输出将显示为：

The output will be displayed as −

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

Verification

若要验证记录是否正确插入，请使用以下 SELECT 查询 −

To verify if the records are inserted properly or not, use the following SELECT query −

SELECT * FROM BUYERS;

该表将显示包含与 CUSTOMERS 相同记录的内容 −

The table will be displayed containing the same records as CUSTOMERS −

ID

NAME

AGE

ADDRESS

SALARY

1

Ramesh

32

Ahmedabad

2000.00

2

Khilan

25

Delhi

1500.00

3

Kaushik

23

Kota

2000.00

4

Chaitali

25

Mumbai

6500.00

5

Hardik

27

Bhopal

8500.00

6

Komal

22

Hyderabad

4500.00

7

Muffy

24

Indore

10000.00

The INSERT…​ TABLE Statement

如果您有两张表结构完全相同，那么您可以使用 INSERT…​TABLE 语句将一张表的内容插入到另一张表中，而无需选择特定列。

If you have two tables structure exactly same, then instead of selecting specific columns you can insert the contents of one table into another using the INSERT…​TABLE statement.

以下是执行此操作的语法 −

Following is the syntax to do so −

INSERT INTO first_table_name TABLE second_table_name;

Example

在此示例中，让我们使用我们在前一个示例中创建的相同 CUSTOMERS 表，并将其内容复制到另一张名为 SHOPPERS 的表中。为此，让我们创建表 SHOPPERS，其结构与 CUSTOMERS 表相同 −

In this example, let us use the same CUSTOMERS table we have created in the previous example and copy its contents into another table named SHOPPERS. For that, let’s create the table SHOPPERS with the same structure as CUSTOMERS table −

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

现在使用以下语句将 CUSTOMERS 表中的所有记录插入到 SHOPPERS 表中 −

Now use the following statement to insert all the records from the CUSTOMERS table into SHOPPERS table −

INSERT INTO SHOPPERS TABLE CUSTOMERS;

Output

此查询将生成以下输出 −

This query will generate the following output −

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

Verification

如果您使用下面显示的 SELECT 语句验证 SHOPPERS 表的内容 −

If you verify the contents of the SHOPPERS table using the SELECT statement shown below −

SELECT * FROM SHOPPERS;

该表将显示新插入的值，如下 −

The table will be displayed with the newly inserted values as −

ID

NAME

AGE

ADDRESS

SALARY

1

Ramesh

32

Ahmedabad

2000.00

2

Khilan

25

Delhi

1500.00

3

Kaushik

23

Kota

2000.00

4

Chaitali

25

Mumbai

6500.00

5

Hardik

27

Bhopal

8500.00

6

Komal

22

Hyderabad

4500.00

7

Muffy

24

Indore

10000.00

Insert Data Only in Specified Columns

您可以仅从一张表中选择特定列来插入到另一张表中。以下 SQL 语句将从 CUSTOMERS 表中插入一条包含 "ID"、"NAME" 和 "AGE" 的新记录到 BUYERS 表中。

You can select only particular columns from a table to insert into another table. The following SQL statement will insert a new record into BUYERS table with "ID", "NAME" and "AGE" from CUSTOMERS table.

在继续之前，让我们先按照以下方法清除 BUYERS 表中的所有记录：

Before we proceed further let’s clean all the records from BUYERS table as follows:

DELETE FROM BUYERS;

现在我们有了一个空的 BUYERS 表，让我们使用以下 SQL 语句：

Now we have empty BUYERS table, let’s use the following SQL statement:

INSERT INTO BUYERS (ID, NAME, AGE)
SELECT ID, NAME, AGE FROM CUSTOMERS;

Output

此查询将生成以下输出 −

This query will generate the following output −

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

Verification

如果您使用下面显示的 SELECT 语句验证 BUYERS 表的内容 −

If you verify the contents of the BUYERS table using the SELECT statement shown below −

SELECT * FROM BUYERS;

该表将显示新插入的值，如下 −

The table will be displayed with the newly inserted values as −

ID

NAME

AGE

ADDRESS

SALARY

1

Ramesh

32

2

Khilan

25

3

Kaushik

23

4

Chaitali

25

5

Hardik

27

6

Komal

22

7

Muffy

24