How to insert data into an Insertinto statement in mysql

Source: Internet
Author: User
One of the most common methods for saving data to the database in mysql is to use the Insertinto statement directly. next I will introduce the Insertinto statement usage in detail.

One of the most common methods for saving data to the database in mysql is to directly use the Insert into statement. next I will introduce the Insert into statement usage in detail.

INSERT is used to INSERT a new row into an existing table. INSERT... The VALUES statement inserts rows based on the specified VALUES. Let's take a look at the standard definition of the insert statement, which can be omitted in:

Syntax

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name [(col_name,...)]

VALUES ({expr | DEFAULT },...),(...),...

[On duplicate key update col_name = expr,...]

Instance

Create table links (name varchar (255) not null default '', address varchar (255) not null default '');

The simplest insert method

The code is as follows:

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

Or

Insert into links values ('jerichen', 'gdsz ');

Batch save data

What if we want to insert multiple data entries into the database at one time? Must I write multiple statements? Certainly not, because MySQL is designed very human. It provides a non-standard format of the insert statement, that is, values (field value 1, field value 2, field value 3), (value of another field 1, value of another field 2, value of another field 3 );

# Insert two pieces of data at the same time. check the Syntax Description. I have omitted the.

The code is as follows:
Insert links (name, url) values ('jerichen', 'gdsz'), ('alone', 'gdgz ');


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:

The code is as follows:

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.

The code is as follows:

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.


Description of the insert statement:

In fact, it is not an example in the book. it is just a lazy knock, and it doesn't make much sense. it all needs to be understood by everyone. The same is true if you do not give an example.

1. this is because the field can have a DEFAULT value when I create a table structure in the log. MySQL 4.0.3 and later support a DEFAULT keyword. when we use the insert statement, the field value can be equal to the DEFAULT keyword to make it equal to the default value when the database is created.

2. AUTOINCREMENT auto-increment field. We do not need to provide a value because the system will automatically auto-increment this field. However, if you want to, you can also pass the value, depending on your mood.

3. UNIQUE, which we also said, is the UNIQUE meaning of the field. for example, if the user id is set to UNIQUE, a data with the user id of 1 already exists, if you want to insert another data with a user ID of 1 at this time, the system will fail.

4. if the database field allows NULL values, we can also set the field value to NULL in the insert statement.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.