The usage of the INSERT, UPDATE, delete, and replace statements in MySQL database

This article is a detailed analysis of the use of the MySQL database insert, UPDATE, delete, and replace statements, the need for a friend reference

MySQL database insert and UPDATE statements
Citation: SQL used to manipulate the database is generally divided into two types, one is the query statement, which is what we call the SELECT statement, the other is the UPDATE statement, also known as the data manipulation statement. The implication is that the data is modified. There are 3 statements in standard SQL, which are insert, update, and delete.

The SQL used to manipulate the database is generally divided into two kinds, one is the query statement, which is what we call the SELECT statement, and the other is the UPDATE statement, also called the data operation statement. The implication is that the data is modified. There are 3 statements in standard SQL, which are insert, update, and delete. There is another replace statement in MySQL, so this article takes MySQL as a background to discuss how to make an UPDATE statement in SQL.

First, insert and replace
The functionality of the INSERT and Replace statements is to insert new data into the table. The syntax for these two statements is similar. The main difference is how to handle duplicate data.

1. General usage of Insert
the INSERT statement in MySQL is not the same as the standard insert, and in a standard SQL statement, the INSERT statement that inserts one record at a time has only one form.
INSERT into tablename (column name ...) Values (column value);
and there is another form of MySQL.
INSERT into tablename SET column_name1 = value1, column_name2 = value2, ...;
The first method separates column names from column values, and when used, the column names must match the number of column values. The following statement inserts a record into the users table:
INSERT into users (ID, name, age) VALUES (123, ' Yao ', ');
INSERT into users SET ID = 123, name = ' Yao Ming ', age = 25;
If set mode is used, at least one column must be assigned a value. If a field uses default values (such as defaults or self-increment), both methods can omit these fields. If self-increment is used on the ID field, the above two statements can be written as follows:
INSERT into User (name, age) VALUES (' Yao Ming ');
INSERT into uses SET name = ' Yao Ming ', age = 25;
MySQL has also made some changes in the values. If you don't write anything in values, MySQL will use the default values for each column in the table to insert a new record.
INSERT into Users () VALUES ();
If nothing is written after the table name, it means assigning values to all the fields in the table. In this way, not only is the value in values 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 error.
INSERT into Users VALUES (' Yao Ming ');

2. Insert multiple records with insert
See this title maybe everyone will ask, this has what to say, call multiple INSERT statements can not insert more than one record! But use this method to increase the load on the server, because every SQL Server executes the same SQL analysis, optimization, and so on. Fortunately, MySQL provides another solution, which is to use 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 ', 25), (' Bill Gates ', 50), (' Martians ', 600);
The INSERT statement above inserts 3 records into the users table consecutively. It is important to note that the values in the INSERT statement above must be placed in pairs (...) for each record. In the middle, use "," split. Suppose there is a table table1
CREATE TABLE table1 (n INT);
If you want to insert 5 records into Table1, the following is the wrong way to write:
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 wording should be:
INSERT into T Able1 (i) VALUES (1), (2), (3), (4), (5);
Of course, this notation can also omit column names, so that the number of values in each pair of parentheses must be the same, and that number must be the same as the number of columns. Such as:
INSERT into T able1 VALUES (1), (2), (3), (4), (5);

3. Replace statement
We may encounter this situation frequently when working with databases. If a table has a unique index on a field, when we insert a record into the table with a key value that already exists, it will throw a primary key conflict error. Of course, we might want to overwrite the original record value with the value of the new record. If you use the traditional approach, you must first delete the original record by using the DELETE statement, and then insert the new record using insert. And in MySQL we provide a new solution, which is the replace statement. When inserting a record with replace, replace is the same as the Insert function, and replace replaces the original record value with the value of the new record if it is duplicated.

The biggest benefit of using replace is that you can combine delete and insert to form an atomic operation. This eliminates the need to consider complex operations such as adding transactions while using delete and insert.
When using replace, the table must have a unique index, and the field in which the index resides cannot allow null values, otherwise the replace is exactly the same as the insert.

After you perform the replace, the system returns the number of rows affected, and if 1 is returned, indicating that there are no duplicate records in the table, and if 2 is returned, a duplicate record is called, and the system automatically calls the delete to delete the record, and then records inserts to insert the record. If the value returned is greater than 2, it indicates that there are multiple unique indexes, and that multiple records are deleted and inserted.

The syntax for replace is very similar to insert, as the following replace statement inserts or updates a record.
REPLACE into Users (id,name,age) VALUES (123, ' Zhao Benshan ', 50);
insert more than one record:
REPLACE into users (ID, name, age)
VALUES (123, ' Zhao Benshan ', [134], ' Mary ', 15);
Replace can also use the SET statement
REPLACE into users SET ID = 123, name = ' Zhao Benshan ', age = 50;
The above mentioned that replace may affect more than 3 records because there is more than one unique index in the table. In this case, replace takes each unique index into account and deletes the duplicate records for each index, and then inserts the new record. Suppose there is a Table1 table with 3 fields A, B, 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);
Let's say there's 3 records in Table1.
A b C
1 1 1
2 2 2
3 3 3
Below we use the Replace statement to insert a record into the Table1.
REPLACE into Table1 (A, B, c) VALUES (All-in-all);
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
As we can see, replace deletes the original 3 records and inserts (1, 2, 3).

Second, UPDATE
Update is a feature that updates the data in a table. This syntax is similar to the second usage of insert. You must provide the table name and the set expression, which you can then add where to limit the updated record range.
UPDATE table_anem SET column_name1 = value1, column_name2 = value2, ...
WHERE ...;
Use the following statement to change the age of the record with ID equal to 123 in the Users table to 24
UPDATE users SET age = WHERE id = 123;
Similarly, you can use update to update the values of multiple fields update users SET age = $, 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.
When updating records with update, if the type of the field being updated does not match the assigned value, MySQL converts the value to the value of the corresponding type. If the field is a numeric type and the assigned value exceeds the maximum range of the data type, then MySQL converts the value to the maximum or minimum value of the range. If the string is too long, MySQL truncates the extra string. If you set the non-null field to NULL, set this field to their default value, the default value for the number is 0, and the default value of the string is an empty string (not NULL, is "").
There are two scenarios in which update does not affect the data in the table.
1. When the condition in the where does not have a record in the table and it matches.
2. When we assign the same value to a field, such as assigning the field ABC to ' 123 ', the original value of ABC is ' 123 '.
As with insert, replace, UPDATE returns the number of records that were updated. However, the number of records does not include records that meet the Where condition but are not updated. Any records are not updated as in the UPDATE statement below.
UPDATE users SET age = WHERE id = 12;
Query OK, 0 rows Affected (0.00 sec)
Note that if the type of a field is timestamp, then this field is updated automatically when other fields are updated.
At some point we need to get the number of rows selected by update instead of the number of rows being updated. We can do that with some API. The C API provided by MySQL provides an option to get the number of records you want. The default number of records that MySQL's JDBC driver gets is also the number of matching records.
Update and replace are basically similar, but there are two different points between them.
1. Update does nothing when there are no matching records, and replace is updated when there are duplicate records and inserted when there are no duplicate records.
2. Update can selectively update a portion of a record's fields. Replace deletes the record completely when it finds duplicate records, and then inserts a new record. That is, all the fields have been updated.

Third, delete and truncate TABLE
There are two ways to delete data in MySQL, one is a DELETE statement and the other is a 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. Therefore, the DELETE statement is more flexible.
If you want to clear all the records in a table, you can use the following two methods:
DELETE from table1
TRUNCATE TABLE table1
The table in the second record is optional.
If you want to delete some of the records in a table, you can only use the DELETE statement.
DELETE from table1 WHERE ...;
If Delete does not have a WHERE clause, then it is the same as TRUNCATE TABLE, but they are a little different, that is, the delete can return the number of records deleted, and TRUNCATE TABLE returns 0.

If there is a self-increment field in a table, the self-increment field restores the starting value to 1 when all records are deleted using TRUNCATE TABLE and delete without a WHERE clause. If you do not want to do this, you can add a permanent where in the DELETE statement, such as where 1 or where true.
DELETE from table1 WHERE 1;

The above statement scans each record as it executes. But it is not compared, because this where condition is always true. This can maintain the maximum value of the increment, but because it scans all records, it is much more expensive to execute than delete without a WHERE clause.
The biggest difference between delete and TRUNCATE TABLE is that delete can select the record to delete through the where statement. But the speed of execution was unpleasant. You can also return the number of records that were deleted. Truncate TABLE cannot delete the specified record, and the deleted record cannot be returned. But it executes very fast.

Unlike standard SQL statements, delete supports both the order BY and the limit clauses, and with these two clauses, we have more control over the records to be deleted. If we want to delete only a portion of the records filtered by the WHERE clause, you can use Limib, which can be used with order by and limit if you want to delete the latter few records. Suppose we want to delete the first 6 records in the users table that have name equal to "Mike". You can use the followingDELETE statement:
DELETE from users WHERE name = ' Mike ' LIMIT 6;
In general, MySQL does not determine which 6 of the 6 records were deleted, and we can use order by to sort the records in order to be more insured.
DELETE from the users WHERE name = ' Mike ' ORDER by ID DESC LIMIT 6

The usage of the INSERT, UPDATE, delete, and replace statements in MySQL database