Insert is used to insert new rows into an existing table. INSERT ... The values statement inserts a row based on an explicitly specified value. Let's take a look at the definition of the INSERT statement standard, which can be omitted in []:
Grammar
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 way to insert
The code is as follows |
Copy Code |
Mysql>insert into worker values (' Tom ', ' tom@yahoo.com '), (' Paul ', ' paul@yahoo.com '); Or Insert into links values (' Jerichen ', ' Gdsz '); |
Bulk save Data
What if we want to insert more than one data into the database at once? Are you sure you want to write more than one statement? Certainly not, because the MySQL design is still very human. It provides a non-standard format for INSERT statements, that is, values (field value 1, field value 2, field value 3), (the value of another field 1, the value of another field 2, the value of another field 3);
# Insert two data at the same time, look at the syntax description, that into was omitted by me
The code is as follows |
Copy Code |
Insert links (Name,url) VALUES (' Jerichen ', ' Gdsz '), (' Alone ', ' gdgz '); |
Use Insert ... SELECT statement inserts rows selected from other tables
When we learned to create a table in the previous section, we knew that we could use Select to create tables directly from other tables, and even to copy data records at the same time. If you already have a table, you can also benefit from the combination of the SELECT statement.
Input data from other tables, such as:
The code is as follows |
Copy Code |
Mysql>insert into Tbl_name1 (col1,col2) select Col3,col4 from Tbl_name2; |
You can also omit the list of columns in the destination table if you have data entry for each column.
The code is as follows |
Copy Code |
Mysql>insert into tbl_name1 select Col3,col4 from Tbl_name2; |
INSERT into ... The SELECT statement satisfies the following criteria:
A query cannot contain an ORDER BY clause.
For A few notes on INSERT statements:
In fact, is not what the description, are the examples above, but lazy knock, and feel that there is no great significance, are needed to understand. Give no example is the same.
1. Because before I introduced the creation of table structure in the log, the field can have default value, in MySQL4.0.3 and later versions all support a default keyword, when we use INSERT statement, we can make the value of the field equal to the default keyword, To make it equal to the default value when the database was created.
2. AutoIncrement field, this we do not have to give a value, because the system will automatically for the field to increase, but if you want to, but also can pass the value, see your mood.
3. Unique This we also said that is the only meaning of the field, such as the user ID set unique, there is already a user ID 1 data, if you want to insert a user ID 1 data is not successful, the system will be wrong.
4. If the database field allows null values, we can also set the field value to NULL in the INSERT INSERT statement.