Detailed usage of the MySQL database insert, UPDATE, delete, and replace statements

This article is a MySQL database insert, UPDATE, delete and replace statements in the use of detailed analysis of the introduction, the need for friends to refer to the

MySQL database insert and UPDATE statements
Introduction: The SQL used to manipulate the database is generally divided into two kinds, one is the query statement, that is, we call the SELECT statement, the other is to update the statement, also known as data manipulation statements. 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 types, one is the query statement, which 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. There is another replace statement in MySQL, so this article takes MySQL as the background to discuss how to make an UPDATE statement in SQL.

First, insert and replace
the INSERT and replace statements have the ability to insert new data into a table. The syntax for these two statements is similar. The main difference between them is how to handle duplicate data.

1. Insert General usage
INSERT statements in MySQL are not the same as standard inserts, and in standard SQL statements there is only one form of INSERT statement that inserts one record at a time.
INSERT into tablename (column name ...) Values (column value);
and there is another form in MySQL.
INSERT into tablename SET column_name1 = value1, column_name2 = value2, ...; The first method of
separates the column names from the column values and, when used, the column names must be the same as the number of column values. The the following statement inserts a record into the users table:
INSERT into the users (ID, name, age) VALUES (123, ' Yao Ming ',);
The second method allows column names and column values to appear and be used in pairs, such as the following statement, which produces a medium-like effect.
INSERT into the users SET ID = 123, name = ' Yao Ming ', age = 25;
If you use the Set method, you must assign at least one column. If a field uses a saved value (such as Default or self-increment), these fields can be omitted from either of these methods. The above two statements can be written in the form of
INSERT into user (name, age) VALUES (' Yao Ming ',), if the self-increment is used on the ID field;
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 the new record using the default values for each column in the table.
INSERT into Users () VALUES ();
If you write nothing after the table name, you assign a value to all the fields in the table. In this way, not only values are consistent with the number of columns, but the order cannot be reversed. INSERT into Users VALUES (123, ' Yao ', 25);
If you write the INSERT statement as follows, MySQL will make an error.
INSERT into Users VALUES (' Yao Ming ',);

2. Insert multiple records using Insert
See this title maybe people will ask, what is there to say, call multiple INSERT statements can not insert more than one record? However, this method is used to increase the load on the server, because every time the SQL Server executes, the SQL is analyzed, optimized, and so on. Fortunately, MySQL offers another solution, which is to insert multiple records using an INSERT statement. This is not a standard SQL syntax, so it can only be used in MySQL.
INSERT into the users (name, age)
VALUES (' Yao Ming ', 25), (' Bill Gates ', 50), (' Martians ',);
The INSERT statement above inserts 3 consecutive records into the users table. It is worth noting that the values in the INSERT statement above must have the value of each record put on a pair of (...) In, the middle uses "," split. Suppose you have a table table1
CREATE table table1 (n INT);
If you want to insert 5 records into Table1, the following is wrong:
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
and the correct writing should be:
INSERT into T ABL E1 (i) VALUES (1), (2), (3), (4), (5);
Of course, this type of writing can also omit the column name so that the number of values in each pair of parentheses must be the same, and that number must be consistent with 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 a database. If a table establishes a unique index on a field, when we insert a record into the table using the existing key value, it throws 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 a 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 for us to provide a new solution, this is the replace statement. When you insert a record with replace, if you do not repeat, replace is the same as the Insert feature, and if there are duplicate records, replace replaces the original record value with the value of the new record. The biggest advantage of

using replace is that delete and insert can be combined to form an atomic operation. This eliminates the need to consider complex operations such as adding transactions while using both delete and insert.
When you use Replace, the table must have a unique index, and the field in which the index is located cannot allow null values, otherwise replace will be exactly the same as the insert.

After the replace is executed, the system returns the number of rows affected, and if you return 1, there is no duplicate record in the table, and if you return 2, there is a duplicate record, and the system automatically calls delete to delete the record, and then records the insertion with insert. If the returned value is greater than 2, then there are multiple unique indexes, and 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);
inserts multiple records:
REPLACE into the users (ID, name, age)
VALUES (123, ' Zhao Benshan ', M), (134, ' Mary ',);
Replace can also use the SET statement
Replace into the users set id = 123, name = ' Zhao Benshan ', age = n
mentioned that replace may affect more than 3 records because there are more than one An index. In this case, replace will consider each unique cable, and the duplicate records corresponding to each index are deleted, and the new record is inserted. Suppose you have 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 isn't null UNIQUE,C int not null UNIQUE);
Suppose there are 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 (1,2,3);
The results returned are as follows
Query OK, 4 rows Affected (0.00 sec)
Records in Table1 are as follows
a b C
1 2 3
We can see that replace deleted the original 3 records. , and then insert (1, 2, 3).

The function of the

Second, update
update is to update the data in the table. This syntax is similar to the second use of insert. You must provide a table name and a set expression, where you can add a where to limit the updated record range.
UPDATE table_anem SET column_name1 = value1, column_name2 = value2, ...
WHERE ...;
Change the age of the record with ID equal to 123 in the Users table to
Update users SET age = the WHERE id = 123
Similarly, you can use update to update the values of multiple fields updates users SET age =, name = ' Mike ' WHERE id = 123;
The UPDATE statement above specifies a condition through the where, otherwise, update updates the values of all records in the table.
when updating records by using update, MySQL converts this value to a value of the corresponding type if the type of the field being updated does not match the assigned value. If the field is a numeric type and the assignment exceeds the maximum range of the data type, MySQL converts the value to the maximum or minimum value for that range. If the string is too long, MySQL truncates the extra string. If you set a non-empty field to null, set the field to their default value, the default value for the number is 0, and the string's default value is an empty string (not NULL, is "").
There are two situations in which update does not affect the data in the table.
1. When a condition in a 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 '. The update also returns the number of records that are updated, as with insert, replace,
. However, the number of records does not include records that satisfy the where condition but have not been updated. The UPDATE statement, such as the same, does not update any records.
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 the field in other fields is moreUpdate automatically when new.
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 this with some APIs. The C API provided by MySQL provides an option to get the number of records you want. The default number of records that the MySQL JDBC driver gets is also the number of matching records.
Update and replace are basically similar, but there are two differences between them.
1. Update does nothing while there is no matching record, and replace is updated when there are duplicate records, inserted when there is no duplicate record.
2. Update optionally updates part of a record's field. Replace deletes the record completely when it finds a duplicate record, and then inserts a new record. In other words, all the fields are updated.

Three, 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. The DELETE statement allows you to select the record you want to delete through the where. Using TRUNCATE TABLE deletes all records in the table. Therefore, the DELETE statement is more flexible.
If you want to empty all 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 add a WHERE clause, it is the same as the TRUNCATE table, but they are a bit different, that is, delete can return the number of records deleted, and TRUNCATE TABLE returns 0.

If you have a self added field in a table, and you delete all records by using the TRUNCATE TABLE and delete without the WHERE clause, This self-added field restores the starting value to 1. If you do not want to do this, you can add a forever where in the DELETE statement, such as where 1 or where true.
DELETE from table1 WHERE 1; The statement above

scans each record as it executes. But it does not compare, because this where condition is always true. While this can keep the maximum value of the increment, it is much more expensive to perform than delete without the WHERE clause because it scans all records. The biggest difference between the
Delete and TRUNCATE TABLE is that delete can select the record to delete through the where statement. But the speed of execution is not fast. You can also return the number of records that were deleted. The TRUNCATE TABLE cannot delete the specified record, and the deleted record cannot be returned. But it executes very fast. Unlike the standard SQL statements, the delete supports the order BY and limit clauses, which allow us to better control the records to be deleted by using these two clauses. If we only want to remove part of the record that the WHERE clause filtered out, you can use Limib, and if you want to delete the following records, you canThe order by and limit are used in conjunction. Let's say we want to delete the first 6 records in the users table that name equals "Mike." You can use the following DELETE statement:
Delete from users where name = ' Mike ' LIMIT 6;
General MySQL is not sure which 6 of these 6 records to delete, in order to more insurance, We can sort records by using order by.
DELETE from users WHERE name = ' Mike ' ORDER by ID DESC LIMIT 6