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`)
