MySQL UPDATE update statement refinement

1. INSERT and REPLACE
Both the INSERT and REPLACE statements insert new data into the table. The syntax of these two statements is similar. The main difference is how they handle duplicate data.

1. as INSERT
The INSERT statement in MySQL is not quite the same as the standard INSERT statement. In the standard SQL statement, the INSERT statement that inserts one record at a time has only one form.
INSERT INTO tablename(column name...) VALUES (column values);
There is another form in MySQL.
INSERT INTO tablename SET column_name1 = value1, column_name2 = value2,... ;
The first method separates the column name from the column value. When used, the column name must correspond to the number 1 of the column value. The following statement inserts a record into the users table:
INSERT INTO users (id, name, age) VALUES (123, 'yao Ming', 25);
The second method allows column names and column values to appear and be used in pairs, and the following statement produces a middle sample effect.
INSERT INTO users SET id = 123, name = 'yao Ming', age = 25;
If you use SET, you must assign at least 1 column. If a field USES a truncated value (such as default or self-increment), both methods omit the fields. If a self-increment is used on the id field, the above two statements can be written as follows:
INSERT INTO users (name, age) VALUES (' yao Ming ', 25);
INSERT INTO uses SET name = 'yao Ming', age = 25;
MySQL also made some changes on VALUES. If nothing is written in VALUES, MySQL inserts a new record using the default value for each column in the table.
INSERT INTO users () VALUES();
If nothing is written after the table name, it represents an assignment to all the fields in the table. In this way, not only do the values in VALUES correspond to column number 1, but the order cannot be reversed. INSERT INTO users VALUES (123, 'yao Ming', 25);
MySQL will report an error if the INSERT statement is written as follows.
INSERT INTO users VALUES (' yao Ming ', 25);

2. Insert multiple records using INSERT

If you look at this title, you might ask, what's the point? If you call the INSERT statement multiple times, you can insert multiple records. However, using this method increases the load on the server, because every time the SQL server performs the same analysis, optimization, and other operations on SQL. Fortunately, MySQL provides another solution, which is to insert multiple records using one INSERT statement. This is not the standard SQL syntax, so it can only be used in MySQL.
INSERT INTO users (name, age) VALUES (' yao Ming ', 25), (' Bill Gates', 50), (' martians', 600);

The INSERT statement above inserts three consecutive records into the users table. It is important to note that after VALUES in the INSERT statement above, the value of each record must be placed in 1 pair (...). In the middle, use "," partition. Let's say I have a table, table1
CREATE TABLE table1(n INT);
If you want to insert 5 records into table1, the following is not correct:
INSERT INTO table1 (i) VALUES(1,2,3,4,5);
MySQL will throw the following error
ERROR 1136: Column count doesn't match value count at row 1
The correct way to write it is:
INSERT INTO table1(i) VALUES(1),(2),(3),(4),(5);
Of course, you can also omit the column names in this way, so that the number of values in brackets has to be 1 to 1 for every 1 pair, and that number has to be 1 to the number of columns. Such as:
INSERT INTO table1 VALUES(1),(2),(3),(4),(5);

3. REPLACE statement

this is a situation we may encounter a lot when working with databases. If a table has a unique index on a field, then when we insert another record into the table with an existing key value, we will throw a primary key conflict error. Of course, we might want to override the old record value with the new record value. If you use the traditional approach, you must first delete the original record using the DELETE statement and then insert the new record using INSERT. MySQL provides us with a new solution, which is the REPLACE statement. When inserting a record with REPLACE, REPLACE performs the same function as INSERT if it is not repeated. If there is a duplicate record, REPLACE replaces the original record with the value of the new record.
The great advantage of using REPLACE is that you can combine DELETE and INSERT to form one atomic operation. This eliminates the need to consider complex operations such as adding transactions while using both DELETE and INSERT.
When REPLACE is used, the table must have a unique index, and the field in which the index is located must not allow null values, otherwise REPLACE is exactly like INSERT.
After executing REPLACE, the system returns the number of affected rows. If 1 is returned, there is no duplicate record in the table. If 2 is returned, there is one duplicate record. If the value returned is greater than 2, there are multiple 1-only indexes, and multiple records are deleted and inserted.
The syntax of REPLACE is very similar to INSERT. The following REPLACE statement inserts or updates a record.
REPLACE INTO users (id, name, age) VALUES (123, 'zhao benshan', 50);
Insert multiple records:
REPLACE INTO users (id, name, age) VALUES (123, 'zhao benshan', 50), (134, 'Mary', 15);
REPLACE can also use the SET statement
REPLACE INTO users SET id = 123, name = 'zhao benshan', age = 50;
It was mentioned above that REPLACE may affect more than three records because there are more than one unique index in the table. In this case, REPLACE will consider each unique index, delete the duplicate record corresponding to each index, and insert the new record. Suppose you have an table1 table with three fields a, b, c. They all have one unique index.

CREATE TABLE table1(a INT NOT NULL UNIQUE,b INT NOT NULL UNIQUE,c INT NOT NULL UNIQUE);
Suppose you already have three records in table1
a b c
1 1 1
2 2 2
3 3 3
Now we insert a record into table1 using the REPLACE statement.
REPLACE INTO table1(a, b, c) VALUES(1,2,3);
The results returned are as follows
Query OK, 4 rows affected (0.00 sec)
The records in table1 are as follows
a b c
1 2 3
We can see that REPLACE deleted all three of the original records and then inserted (1, 2, 3).

2. UPDATE

The function of UPDATE is to update the data in the table. The syntax is similar to the second use of INSERT. The table name must be provided along with the SET expression, which can be followed by WHERE to limit the updated record range.
UPDATE table_anem SET column_name1 = value1, column_name2 = value2, WHERE ;
The following statement changes the age of records in users table where id is equal to 123 to 24
UPDATE users SET age = 24 WHERE id = 123;
Also, you can update the values of multiple fields with UPDATE users SET age = 24, name = 'Mike' WHERE id = 123;
The UPDATE statement above specifies a condition through WHERE, otherwise UPDATE updates the values of all records in the table.
When updating a record with UPDATE, if the type of the field being updated does not match the value assigned, MySQL converts the value to the value of the corresponding type. If the field is of a numeric type and the value assigned exceeds the maximum range of the data type, MySQL converts the value to the maximum or minimum range. If the string is too long, MySQL truncates the extra string. If a non-empty field is set to be empty, then set the field to their default value, the default value for a number is 0, and the default value for a string is an empty string (not null, but "").
There are two situations where UPDATE does not affect the data in the table.
1. When the condition in WHERE has no record matching it in the table.
2. When we assign the same value to a field, such as field abc to '123', the original value of abc is '123'.
Like INSERT and REPLACE1, UPDATE also returns the number of records updated. However, these records do not include records that meet the WHERE condition but are not updated. The same UPDATE statement below does not update any records.
UPDATE users SET age = 30 WHERE id = 12;
Query OK, 0 rows affected (0.00 sec)
Note that if the type of a field is TIMESTAMP, this field is automatically updated when other fields are updated.
Sometimes we need to get the number of rows selected by UPDATE, not the number of rows updated. We can do this with some API. For example, C API provides an option to get the number of records you want. The default number of records obtained by the JDBC driver of MySQL is also the number of matched records.
UPDATE and REPLACE are basically similar, but there are two differences between them.
1. UPDATE does nothing when there is no matching record, while REPLACE updates when there is a duplicate record and inserts when there is no duplicate record.
2. UPDATE can optionally update a part 1 field of the record. When REPLACE finds a duplicate record, it deletes the record completely and inserts a new record. That is, all the fields are updated.

3. DELETE and TRUNCATE TABLE

There are two ways to delete data in MySQL, the DELETE statement and the TRUNCATE TABLE statement. The DELETE statement can select the record to be deleted by WHERE. Using TRUNCATE TABLE will delete all records in the table. As a result, the DELETE statement is more flexible.
If you want to empty all the records in the table, you can use the following two methods:
DELETE FROM table1
TRUNCATE TABLE table1
TABLE in the second record is optional.
If you want to delete partial records from a table, you can only use the DELETE statement.
DELETE FROM table1 WHERE ;
If DELETE does not add the WHERE clause, it is one like TRUNCATE TABLE, but they differ in that DELETE can return the number of records deleted, while TRUNCATE TABLE returns 0.
If a table has a self-increment field, after deleting all records using TRUNCATE TABLE and DELETE without the WHERE clause, this self-increment field returns the starting value to 1.
DELETE FROM table1 WHERE 1;
The statement above will scan every record as it executes. But it doesn't compare, because this WHERE condition is always true. This keeps the self-increasing maximum, but since it scans all the records, its execution cost is much higher than DELETE without the WHERE clause.
The biggest difference between DELETE and TRUNCATE TABLE is that DELETE can select the record to delete through the WHERE statement. But not very fast. You can also return the number of records that were deleted. TRUNCATE TABLE cannot delete the specified record and cannot return the deleted record. But it works really fast.
Unlike the standard SQL statement, DELETE supports ORDER BY and LIMIT clauses, which give us better control over the records to be deleted. For example, when we want to delete only one part of the records filtered out by the WHERE clause, we can use LIMIB. If we want to delete the later records, we can use ORDER BY and LIMIT together. Suppose we want to delete the first six records in the users table where name is equal to "Mike". You can use the following DELETE statement:
DELETE FROM users WHERE name = 'Mike' LIMIT 6;
1 general MySQL is not sure which 6 of the 6 records are deleted. To be safe, we can use ORDER BY to sort the records.
DELETE FROM users WHERE name = 'Mike' ORDER BY id DESC LIMIT 6;