Insert record

Insert records

There are two basic syntaxes for inserting records

Insert basic syntax one

Insert Basic Grammar 2

Explanation

Basic Grammar 1 and Basic Grammar The difference between 2 is:

1. In the insert statement of basic syntax 1, as many values ​​as there are fields in the table must be inserted. No one can be more, and no one can be less. If there is a default value and you don’t want to pass it, you can write null.

2. In Basic Syntax 2, unless there are required fields, values ​​must be written. If you don't want to write a default value, you can ignore it. mysql will automatically complete the default value.

3. In basic syntax 2, the order of user(id,username,sex) fields is the order of values.

Assume that there is a table called the user table. We describe the fields, field descriptions, types, and field optional and required states. The table structure is as follows:

Write the insert statement in the above table according to the basic syntax:

insert into user values(null,'王开 ','php@163.com',null ,1);

Note

1. It is not necessary to specify the field name, but the order after values ​​should be consistent with the sorting of the table fields.

2. Fields with default values ​​do not need to be written, then they will be the default values.

3. If there is a default value or a nullable field and you do not want to pass in a specific value, you can write null.

4. The data format must be consistent with the data format specified in the table.

Write the insert statement in the above table according to basic syntax 2:

insert into user(username,sex) values('王开',1);

Note

1. Fields with IDs that are auto-incrementing do not need to pass in values. Each time this is inserted, The value of the field will automatically increase by 1.

2. Fields with default values ​​and nullable values ​​do not need to be passed

3. The insertion order of table user(username,sex) shall prevail

4. Basic syntax The second is the more common usage

Basic syntax variation: insert multiple records at one time

insert into user(username,password,sex)
 values('黄晓明', 'abcdef', 1),
 ( 'angelababy', 'bcdeef', 0),
 ( '陈赫', '123456', 1),
 ('王宝强', '987654', 1);

After inserting the record, return the record ID

Mysql提供了一个LAST_INSERT_ID（）的函数。
mysql> SELECT LAST_INSERT_ID();  -> 195

Simply speaking, this function will Returns the value of the auto-incremented field in the table of the inserted record. Generally, we name the auto-incremented field ID. This will return the ID value of the record just inserted.

mysql implementation method of inserting a record when it does not exist and then updating it if it exists

mysql> truncate `200702`; 
 Query OK, 0 rows affected (0.01 sec) 
 mysql> select * from `200702`; 
 Empty set (0.01 sec) 
 mysql> insert into `200702` (`domain`, `2nd_domain`, `tld`, `query_ns1`, `query_ns2`, `report_date`) values ('dnspod.com', 'dnspod', 'com', 1000, 2000, '2007-02-04') ON DUPLICATE KEY UPDATE `query_ns1` = `query_ns1` + 1000, `query_ns2` = `query_ns2` + 2000; 
 Query OK, 1 row affected (0.00 sec)

Of course, when creating a table, don’t forget to make a unique for the domain

UNIQUE KEY `domain` (`domain`,`report_date`)