INSERT (1)

INSERT new data using the INSERT statement

Syntax: INSERT [INTO] tbl_name [(col_name,...)] VALUES (pression ,...),...
INSERT [INTO] tbl_name SET col_name = expression ,...

Let's start to use the INSERT statement to add records. This is an SQL statement. You need to specify the table for which you want to INSERT data rows or the table that puts values by row. The INSERT statement has several forms:

You can specify the values of all columns:

For example:

shell> mysql –u root –pmysql> use mytest;mysql> insert into worker values(“tom”,”tom@yahoo.com”);

The word "INTO" is optional since MySQL 3.22.5. This is also true for other forms of INSERT statements .) The VALUES table must contain the VALUES of each column in the table and are given in the order of column storage in the table. Generally, this is the column definition order during table creation. If not, use DESCRIBE tbl_name to view the order .)

Multiple value tables can provide multiple rows of data at a time.

Mysql> insert into worker values ('Tom ', 'Tom @ yahoo.com'), ('Paul ', 'Paul @ yahoo.com ');

The INSERT... VALUES format with multiple value tables is supported in MySQL 3.22.5 or later versions.

You can give the column to be assigned a value and then list the values. This is useful for creating records that require initial settings for only a few columns.

For example:

Mysql> insert into worker (name) values ('Tom ');

Since MySQL 3.22.5, this form of INSERT also allows multiple value tables:

Mysql> insert into worker (name) values ('Tom '), ('Paul ');

All columns without names in the column list will be given the default value.

Since MySQL 3.22. 10, columns and values can be given in the form of col_name = value.

For example:

Mysql> insert into worker set name = 'Tom ';

A default value is assigned to all rows not named in the SET clause.

INSERT statements in this form cannot INSERT multiple rows.

An expression can reference any column previously set in a value table. For example, you can:

Mysql> insert into tbl_name (col1, col2) VALUES (15, col1 * 2 );

But it cannot be like this:

Mysql> insert into tbl_name (col1, col2) VALUES (col2 * 2, 15 );

Use INSERT... SELECT statement insert selected rows from other tables

When we learn how to create a table in the previous section, we know that you can use select to directly create a table from other tables, or even copy data records at the same time. If you already have a table, you can also benefit from the cooperation of select statements.

Input data from other tables, for example:

Mysql> insert into tbl_name1 (col1, col2) select col3, col4 from tbl_name2;

You can also skip the column list of the target table if each column has data input.

Mysql> insert into tbl_name1 select col3, col4 from tbl_name2;

The insert into... SELECT statement meets the following conditions:

A query cannot contain an order by clause.

The target table of the INSERT statement cannot appear in the FROM clause in the SELECT query section, because this is forbidden in ansi SQL to allow the SELECT statement FROM the table you are inserting. The problem is that SELECT may find records previously inserted during the same running period. When a sub-selection clause is used, the situation can be easily confused)

Use replace, replace... Insert select statement

The REPLACE function is exactly the same as the INSERT function. If an old record in the table has the same value for a new record with a unique index, the old record is deleted before the new record is inserted. In this case, the insert statement produces an error.

The REPLACE statement can also be used with the brown SELECT statement. Therefore, the content in the last two sections is suitable for REPALCE ..

Note that the REPLACE statement may change the original record, so be careful when using it.