The UPDATE statement of the MySQL database perspective

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. General usage of Inserts

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's another form in MySQL.

INSERT into tablename SET column_name1 = value1, column_name2 = value2, ...;

The first method separates the column names from the column values, and when used, the column names must be the same as the number of column values. As the following statement inserts a record into the users table:

INSERT into the users (ID, name, age) VALUES (123, ' Yao ', 25);

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 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 following form, such as using the self-increment on the ID field:

INSERT into the 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 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 in the following form, MySQL will make an error.

INSERT into Users VALUES (' Yao Ming ', 25);

2. Insert multiple records using insert

See this title maybe people will ask, what is there to say, invoke 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 ', 600);

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 there is a table table1

CREATE TABLE table1 (n INT);

If you want to insert 5 records into the 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

And the right way to do that is:

INSERT into T Able1 (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

This may be the case when we use the 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);

To insert more than one record:

REPLACE into the users (ID, name, age)

VALUES (123, ' Zhao Benshan ', M), (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 might affect more than 3 records because there are more than one unique index in the table. In this case, replace takes into account each unique index, deletes the duplicate records for each index, and inserts the new record. 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);

Let's say 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)

The records in the Table1 are as follows

A b C

1 2 3

As we can see, replace deletes the original 3 records and then inserts (1, 2, 3).

Second, UPDATE

The update feature is updating 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 ...;

The following statement changes the age of the record with ID equals 123 in the Users table to 24

UPDATE users SET age = the 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 the where, otherwise, update updates the values of all records in the table.

When you update a record by using update, MySQL converts the value to the corresponding type of value 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 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 also returns the number of records that were updated. 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, the field is automatically updated when the 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 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 without matching records, 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.

Third, 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 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 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 using the TRUNCATE table and delete without the WHERE clause, the field returns the starting value to 1. If you don't 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 above statement 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 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 standard SQL statements, delete supports the ORDER BY and limit clauses, which allow us to better control the records to be deleted. If we only want to remove part of the record that the WHERE clause filters out, you can use Limib, and if you want to delete the following records, you can use the order by and limit. 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;

In general, MySQL is not sure which 6 of these 6 records to delete, for more insurance, we can use the order by to sort records.

DELETE from the users WHERE name = ' Mike ' ORDER by ID DESC LIMIT 6;