A2-02-24.DML- Inserting Data into A Table Using MySQL INSERT Statement

转载自:http://www.mysqltutorial.org/mysql-insert-statement.aspx

Inserting Data into A Table Using MySQL INSERT Statement

 

Summary: in this tutorial, you will learn how to use MySQL INSERT statement to insert data into the database tables.

Simple MySQL INSERT statement

The MySQL INSERT statement allows you to insert one or more rows into a table. The following illustrates the syntax of the INSERT statement:

First, you specify the table name and a list of comma-separated columns inside parentheses after the INSERT INTO clause.

Then, you put a comma-separated values of the corresponding columns inside the parentheses followed the VALUES keyword.

You need to have an INSERT privilege to use the INSERT statement.

Let’s create a new table named tasks for practicing the INSERT statement.

For example, if you want to insert a new task into the tasks table, you use the INSERT  statement as follows:

After executing the statement, MySQL returns a message to inform the number of rows affected. In this case, one row were affected.

MySQL Insert Statement Example

MySQL INSERT – insert multiple rows

In order to insert multiple rows into a table, you use the INSERT statement with the following syntax:

In this form, the value list of each row is separated by a comma. For example, to insert multiple rows into the tasks table, you use the following statement:

3 rows affected. Great!

MySQL Insert multiple rows

If you specify the value of the corresponding column for all columns in the table, you can ignore the column list in the INSERT statement as follows:

and

Notice that you don’t have to specify the value for auto-increment column e.g., taskid column because MySQL generates value for the auto-increment column automatically.

MySQL INSERT with SELECT clause

In MySQL, you can specify the values for the INSERT statement from a SELECT statement. This feature is very handy because you can copy a table fully or partially using the INSERT and SELECT clauses as follows:

Let’s copy the tasks table to the task_1 table.

First, create a new table named tasks_1 by copying the structure of the tasks table as follows:

Second, insert data from the tasks table into the tasks_1  table using the following INSERT  statement:

Third, check the tasks_1 table to see if we actually copy it from the tasks table.

MySQL Insert - Copy a Table example

MySQL INSERT with ON DUPLICATE KEY UPDATE

If the new row violates the PRIMARY KEY or UNIQUE constraint, MySQL issues an error. For example, if you execute the following statement:

MySQL issues an error message:

Because the row with the primary key task_id 4 already exists in the tasks table, the statement violates the PRIMARY KEY constraint.

However, if you specify the ON DUPLICATE KEY UPDATE option in the INSERT statement, MySQL will insert a new row or update the existing row with the new values.

For example, the following statement updates the existing row whose task_id is 4 with the new task_id and subject.

MySQL issues a message saying that  2 rows affected .

Let’s check the tasks table:

MySQL Insert ON DUPLICATE KEY UPDATE

The new row was not inserted. But the existing row with the task_id 4 was updated.

The INSERT ON DUPLICATE KEY UPDATE statement above is equivalent to the following UPDATE statement:

For more information on the INSERT ON DUPLICATE KEY UPDATE statement, please check it out the MySQL insert or update tutorial.

In this tutorial, we have shown you how to use various forms of the MySQL INSERT statement to insert data into a table.

posted @ 2018-08-22 17:23  zhuntidaoren  阅读(194)  评论(0编辑  收藏  举报