Mysql 简明教程

MySQL - Insert Query

在使用 CREATE TABLE 语句在 MySQL 数据库中创建表后，我们只会得到一个仅定义了其结构的空表。要向其中填充数据，我们需要使用单独的查询手动添加记录。

After creating a table in a MySQL database with the CREATE TABLE statement, we will only have an empty table that only has its structure defined. To populate it with data, we need to add records manually using separate queries.

The MySQL INSERT Statement

要将数据插入 MySQL 表中，我们需要使用 MySQL INSERT 语句。我们可以使用“mysql>”提示符或使用任何客户端程序（如 PHP、Java 等）将数据插入 MySQL 表中。

To insert data into a MySQL table, we would need to use the MySQL INSERT statement. We can insert data into the MySQL table by using the 'mysql>' prompt or by using any client program such as PHP, Java etc.

由于表的结构已经定义，MySQL INSERT 语句只接受符合表结构的数据。插入表中的数据必须具有相同的数据类型，满足约束（如果有），等等。如果插入的数据不满足这些条件，INSERT INTO 语句将显示错误。

Since the structure of a table is already defined, the MySQL INSERT statement will only accept the data which is according to the structure of the table. Data inserted into a table must have same data types, satisfy the constraints (if any), etc. If the inserted data does not satisfy these conditions, the INSERT INTO statement displays an error.

Syntax

以下是 MySQL INSERT 语句的语法 −

Following is the syntax of the MySQL INSERT statement −

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

要插入字符串值，需要将所有值放入双引号或单引号中。例如 "value" 。

To insert string values, it is required to keep all the values into double or single quotes. For example "value".

Inserting Data from the Command Prompt

要从命令提示符插入数据，我们将使用 SQL INSERT INTO 语句将数据插入 MySQL 表中。

To insert data from the command prompt, we will use SQL INSERT INTO statement to insert data into an MySQL table.

Example

首先，让我们使用以下查询创建一个名为 CUSTOMERS 的表：

First of all, let us create a table named CUSTOMERS using the following query −

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

现在，我们将向上述所创建的表中插入一条记录−

Now, we will insert a single record into the above created table −

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

我们还可以使用以下查询同时插入多条记录−

We can also insert multiple records simultaneously using the following query −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 );

即使不指定列名，也可以将记录插入到数据库中，前提是查询中的逗号分隔值与对应列的属性匹配，如下所示−

Inserting records into a database is also possible even if you do not specify the column name if the comma separated values in the query match the attributes of corresponding columns as shown below −

INSERT INTO CUSTOMERS VALUES
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

Verification

我们可以使用如下所示的语句验证是否插入了数据−

We can verify whether the the data is inserted using this statement as shown below −

SELECT * FROM CUSTOMERS;

所生成的 CUSTOMERS 表如下所示−

The CUSTOMERS table produced is as shown below −

Inserting Data Into a Table Using Another Table

有时，我们只需要将数据从数据库中的一个现有表复制到同一数据库中的另一个表中。有各种方法可以做到这一点−

Sometimes, we just need to copy the data from one existing table in a database to another table in the same database. And there are various ways to do so −

  1. Using INSERT…​ SELECT

  2. Using INSERT…​ TABLE

INSERT…​ SELECT Statement

INSERT…​ SELECT Statement

我们可以通过对另一个表的 select 语句将数据填充到一个表中；前提是另一个表具有一组字段，这些字段是填充第一个表所必需的。

We can populate the data into a table through the select statement over 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 table_name1 [(column1, column2, ... columnN)]
SELECT column1, column2, ...columnN
FROM table_name2
[WHERE condition];

Example

在以下查询中，我们正在使用与 CUSTOMERS 表相同的结构创建一个另一个表 CUSTOMERS_Copy −

In the following query, we are creating another table CUSTOMERS_Copy with the same structure as CUSTOMERS table −

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

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

Now, let us use the INSERT…​INTO statement to insert the records into the CUSTOMERS_Copy table from CUSTOMERS table.

INSERT INTO CUSTOMERS_Copy SELECT * from CUSTOMERS;

Output

这会产生以下输出 −

This will generate the following output −

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

Verification

执行以下查询以验证是否已从 CUSTOMERS 表中插入记录 −

Execute the following query to verify whether the the records are inserted from CUSTOMERS table or not −

SELECT * FROM CUSTOMERS_Copy;

所获得的 CUSTOMERS_Copy 表如下所示−

The CUSTOMERS_Copy table obtained is as shown below −

INSERT…​TABLE Statement

INSERT…​TABLE Statement

另一方面，我们不用选择特定的列，而是可以使用 INSERT…​TABLE 语句将一个表的内容插入到另一个表中。

On the other hand, instead of selecting specific columns, we can insert the contents of one table into another using the INSERT…​TABLE statement.

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

Following is the syntax to do so −

INSERT INTO table1 TABLE table2;

Example

在本例中，让我们使用我们在前面的示例中创建的同一个 CUSTOMERS 表，并将其内容复制到另一个表 CUSTOMERS_dummy 中。

In this example, let us use the same CUSTOMERS table we have created in the previous example and copy its contents to another table CUSTOMERS_dummy.

为此，我们首先将使用与 CUSTOMERS 表相同的结构创建 CUSTOMERS_dummy 表 −

For that, first of all, we will create the table CUSTOMERS_dummy with the same structure as CUSTOMERS table −

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

使用 CUSTOMERS 表，我们将将其所有值插入到 CUSTOMERS_dummy 表中 −

Using the CUSTOMERS table, we will insert all its values into CUSTOMERS_dummy table −

INSERT INTO CUSTOMERS_dummy TABLE CUSTOMERS;

Output

此查询将生成以下输出 −

This query will generate the following output −

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

Verification

我们可以使用 SELECT 语句检索一个表的内容。您可以使用如下所示的语句验证是否插入了数据−

We can retrieve the contents of a table using the SELECT statement. You can verify whether the the data is inserted using this statement as shown below −

SELECT * FROM CUSTOMERS_dummy;

所获得的 CUSTOMERS_dummy 表如下所示−

The CUSTOMERS_dummy table obtained is as shown below −

INSERT …​ SET

使用 INSERT…​SET 语句，可以通过向选定列设置值来插入记录。以下是此语句的语法：

You can insert a record by setting values to selected columns using the INSERT…​SET statement. Following is the syntax of this statement −

INSERT INTO table_name SET column_name1 = value1, column_name2=value2,......;

其中，table_name 是需要向其中插入记录的表的名称，并且 column_name1 = value1、column_name2 = value2 […​] 是选定的列名称和各自的值。

Where, table_name is the name of the table into which you need to insert the record and column_name1 = value1, column_name2 = value2 …​…​ are the selected column names and the respective values.

Example

以下查询使用 INSERT…​SET 语句将记录插入 CUSTOMERS 表中。在此，我们仅向 ID、NAME 和 AGE 列传递值（其余值将为 NULL）：

Following query inserts a record into the CUSTOMERS table using the INSERT…​SET statement. Here, we are passing values only to the ID, NAME and, AGE columns (remaining values will be NULL) −

INSERT INTO CUSTOMERS
SET ID = 8, NAME = 'Sarmista', AGE = 35;

Verification

如果您使用 SELECT 语句检索 CUSTOMERS 表的内容，则可以观察到插入的行，如下所示：

If you retrieve the contents of the CUSTOMERS table using the SELECT statement you can observe the inserted row as shown below

SELECT * FROM CUSTOMERS WHERE ID=8;

Output

以下是以上程序的输出 −

Following is the output of the above program −

Inserting Data Using a Client Program

除了使用 MySQL 查询将数据插入 MySQL 数据库的表中之外，还可以使用客户端程序来执行 INSERT 操作。

Besides inserting data into a table in a MySQL database with a MySQL query, we can also use a client program to perform the INSERT operation.

Syntax

以下是此操作在各种编程语言中的语法 −

Following are the syntaxes of this operation in various programming languages −

Example

以下是这些程序 −

Following are the programs −