Usage of INSERT, UPDATE, DELETE, and REPLACE statements in MySQL Databases

MySQL database insert and update statements
The SQL statements used to operate databases are generally divided into two types: Query statements, SELECT statements, and update statements, which are also called data operation statements. The implication is to modify the data. There are three statements in standard SQL: INSERT, UPDATE, and DELETE.

There are two types of SQL statements used to operate databases: Query statements, SELECT statements, and update statements, which are also called data operation statements. The implication is to modify the data. There are three statements in standard SQL: INSERT, UPDATE, and DELETE. There is another REPLACE statement in MySQL. As a result, this article takes MySQL as the background to discuss how to make the update statement in SQL.

I. INSERT and REPLACE
Both INSERT and REPLACE statements INSERT new data into the table. The syntax of these two statements is similar. The main difference between them is how to process repeated data.

1. General INSERT usage
The INSERT statement in MySQL is not the same as the standard INSERT statement. In the standard SQL statement, the INSERT statement that inserts a record at a time has only one form.
Insert into tablename (column name ...) VALUES (column value );
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. during use, the column name must be consistent with the number of column values. 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 the column names and column values to appear and use in pairs. The following statement will produce the same effect.
Insert into users SET id = 123, name = 'Yao Ming ', age = 25;
If the SET method is used, a value must be assigned to at least one column. If a field uses a missing value (such as default value or auto-increment value), you can omit these fields in either of the two methods. If auto-increment is used in the id field, the preceding 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 has also made some changes in VALUES. If nothing is written in VALUES, MySQL inserts a new record using the default value of each column in the table.
Insert into users () VALUES ();
If nothing is written after the table name, it indicates that all fields in the table are assigned a value. In this way, not only must the value in VALUES be consistent with the number of columns, but the order cannot be reversed. Insert into users VALUES (123, 'Yao ming', 25 );
If the INSERT statement is written as follows, MySQL will report an error.
Insert into users VALUES ('Yao Ming ', 25 );

2. INSERT multiple records
If you see this title, you may ask, is there anything you can say about it? Won't you be able to INSERT multiple records after multiple INSERT statements are called! However, using this method increases the load on the server, because every SQL Server executes the same SQL Analysis and Optimization operations. Fortunately, MySQL provides another solution, that is, using an INSERT statement to INSERT multiple records. This is not a standard SQL syntax, so it can only be used in MySQL.
Insert into users (name, age)
VALUES ('Yao Ming ', 25), ('bill Gates', 50), ('marker', 600 );
The preceding INSERT statement inserts three records into the users table consecutively. It is worth noting that the VALUES of each record must be placed in a pair (…) after VALUES in the preceding INSERT statement (...) "," Is used in the middle. Suppose there is a table table1
Create table table1 (n INT );
If you want to insert five records into Table 1, the following statement is incorrect:
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 statement is as follows:
Insert into t able1 (I) VALUES (1), (2), (3), (4), (5 );
Of course, this statement can also omit the column name, so that the number of values in each pair of brackets must be consistent, and this number must be consistent with the number of columns. For example:
Insert into t able1 VALUES (1), (2), (3), (4), (5 );

3. REPLACE statement
We may often encounter this situation when using databases. If a table creates a unique index on a field and inserts a record into the table using an existing key value, a primary key conflict error is thrown. Of course, we may want to overwrite the original record value with the new record value. If you use the traditional method, you must first use the DELETE statement to DELETE the original record, and then use INSERT to INSERT a new record. MySQL provides us with a new solution, which is the REPLACE statement. When you use REPLACE to INSERT a record, if it is not repeated, REPLACE is the same as the INSERT function. If there are repeated records, REPLACE replaces the original record value with the value of the new record.

The biggest advantage of using REPLACE is that you can combine DELETE and INSERT into one to form an atomic operation. In this way, you do not have to consider complicated operations such as adding transactions when using both DELETE and INSERT.
When using REPLACE, the table must have a unique index, and the field where the index is located cannot allow null values. Otherwise, REPLACE is exactly the same as INSERT.

After REPLACE is executed, the system returns the affected number of rows. If 1 is returned, no duplicate records exist in the table. If 2 is returned, a duplicate record exists, the system automatically calls DELETE to DELETE this record, and then inserts this record with INSERT. If the returned value is greater than 2, multiple unique indexes exist, and multiple records are deleted and inserted.

The REPLACE syntax 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;
As mentioned above, REPLACE may affect more than three records because there is more than one unique index in the table. In this case, REPLACE considers each unique index, deletes the duplicate record corresponding to each index, and inserts this new record. Assume that there is a table 1 with three fields a, B, and c. They all have a unique index.
Create table table1 (a int not null unique, B INT NOT NULL UNIQUE, c INT NOT NULL UNIQUE );
Assume that three records exist in table 1.
A B c
1 1 1
2 2 2
3 3 3
Next, we use the REPLACE statement to insert a record to table 1.
Replace into table1 (a, B, c) VALUES (1, 2, 3 );
The returned result is as follows:
Query OK, 4 rows affected (0.00 sec)
The record in table 1 is as follows:
A B c
1 2 3
As we can see, REPLACE deletes all the three original records and inserts (1, 2, 3.

Ii. UPDATE
UPDATE is used to UPDATE data in a table. This syntax is similar to the second INSERT syntax. The table name and SET expression must be provided. You can add the WHERE clause to limit the range of updated records.
UPDATE table_anem SET column_name1 = value1, column_name2 = value2 ,...
WHERE ...;
The following statement changes the age of the record whose id is 123 in the users table to 24.
UPDATE users' SET age = 24 WHERE id = 123;
Similarly, you can use UPDATE to UPDATE the values of multiple fields: UPDATE users SET age = 24, name = 'Mike 'WHERE id = 123;
The above UPDATE statement specifies a condition through WHERE. Otherwise, UPDATE updates the values of all records in the table.
If the type of the updated field does not match the assigned value when the UPDATE record is used, MySQL converts the value to a value of the corresponding type. If the field is of the numerical type and the value is assigned beyond the maximum range of the Data Type, MySQL converts the value to the maximum or minimum value of the range. If the string is too long, MySQL will cut off the extra strings. If you set a non-empty field to null, set this field to their default value. The default value of the number is 0, and the default value of the string is a null string (not null, it is "").
In either case, UPDATE does not affect the data in the table.
1. When the WHERE condition does not match the record in the table.
2. When we assign the same value to a field, for example, assign the field abc to '123', and the original value of abc is '123 '.
Like INSERT and REPLACE, UPDATE also returns the number of updated records. However, these records do not include records that meet the WHERE condition but are not updated. The following UPDATE statement 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.
In some cases, we need to get the number of rows selected for UPDATE, rather than the number of rows to be updated. We can achieve this through some APIs. For example, the c api provided by MySQL provides an option to obtain the number of records you want. The default number of records obtained by the JDBC driver of MySQL is also the number of matching records.
UPDATE and REPLACE are similar, but there are two differences between them.
1. UPDATE does not do anything when there is no matching record, while REPLACE is updated when there is a duplicate record and inserted when there is no duplicate record.
2. UPDATE can selectively UPDATE some fields of a record. REPLACE deletes this record and inserts a new record when it finds a duplicate record. That is, all fields are updated.

Iii. DELETE and TRUNCATE TABLE
There are two ways to DELETE data in MySQL: one is the DELETE statement and the other is the truncate table statement. You can use the WHERE clause to select the record to be deleted. However, using truncate table will delete all records in the TABLE. Therefore, the DELETE statement is more flexible.
To clear all records in the table, you can use the following two methods:
Delete from table1
Truncate table table1
The TABLE in the second record is optional.
To DELETE some records in a table, you can only use the DELETE statement.
Delete from table1 WHERE ...;
If DELETE does not add a WHERE clause, it is the same as the truncate table, but they are a little different. That is, DELETE can return the number of deleted records, while the truncate table returns 0.

If an auto-increment field exists in a TABLE, after deleting all records using truncate table and DELETE without WHERE clause, the auto-increment field restores the starting value to 1. if you do not want to do this, you can add the permanent WHERE statement in the DELETE statement, such as WHERE 1 or WHERE true.
Delete from table1 WHERE 1;

The preceding statement scans each record during execution. But it is not compared, because the WHERE condition is always true. In this way, although the maximum auto-increment value can be maintained, because it scans all records, the execution cost is much greater than the DELETE without the WHERE clause.
The biggest difference between DELETE and truncate table is that DELETE can use the WHERE statement to select the record to be deleted. However, the execution speed is not fast. You can also return the number of deleted records. The truncate table cannot delete the specified record, and the deleted record cannot be returned. But it runs very fast.

Unlike standard SQL statements, DELETE supports the ORDER BY and LIMIT clauses, which allow us to better control the records to be deleted. For example, if you only want to delete part of the records filtered out BY the WHERE clause, you can use LIMIB. If you want to delete the last few records, you can use order by and LIMIT together. Suppose we want to delete the first six records whose name is "Mike" in the users table. You can use the followingDELETE statement:
Delete from users WHERE name = 'Mike 'LIMIT 6;
Generally, MySQL does not determine which 6 records are deleted. To be more secure, we can use order by to sort the records.
Delete from users WHERE name = 'Mike 'order by id desc limit 6