Detailed MySQL database update statement

SQL used to manipulate the database is generally divided into two kinds, one is the query, which is what we call the SELECT statement, the other is to update the statement, also known as data manipulation statements. The implication is to modify the data. There are 3 statements in the standard SQL, which are INSERT, UPDATE, and DELETE. There is another REPLACE statement in MySQL, so this article uses MySQL as a background to discuss how to make updates in SQL.

First, INSERT and REPLACE

The function of INSERT and REPLACE statements are to insert new data into the table. The syntax of these two statements is similar. The main difference between them is how to deal with duplicate data.

1. The general usage of INSERT

The INSERT statement in MySQL is not the same as a standard INSERT. In a standard SQL statement, there is only one form of INSERT statement that inserts a record at a time.

INSERT INTO tablename (column name ...) VALUES (column value);

In MySQL there is another form.

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

The first method separates column names from column values. When used, column names must match the number of column values. As the following statement to the users table to insert a record:

INSERT INTO users (id, name, age) VALUES (123, 'Yao Ming', 25);

The second method allows column names and column values ​​appear in pairs and use, such as the following statement will produce a positive effect.

INSERT INTO users SET id = 123, name = 'Yao Ming', age = 25;

If you use the SET method, you must specify at least one column. If a field uses a default value, such as default or auto-increment, both of these methods can omit these fields. Such as the use of the id field value-added, 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 at VALUES. If nothing is written in VALUES, MySQL will insert the new record using the default for each column in the table.

INSERT INTO users () VALUES ();

If the table name does not write anything, it means that all the fields in the table assignment. In this way, not only the value in VALUES matches the number of columns, but the order can not 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);

Use INSERT insert multiple records

See this title maybe we will ask, what is good to say, call multiple INSERT statement can not insert multiple records it! However, the use of this method to increase the server load, because the implementation of each SQL server The same SQL analysis, optimization and other operations. Fortunately, MySQL provides another solution, is to use an INSERT statement to insert multiple records. This is not a standard SQL syntax and therefore can only be used in MySQL.

INSERT INTO users (name, age)

VALUES ('Yao Ming', 25), ('Bill Gates', 50), ('Martian', 600);

The INSERT statement above continuously inserts 3 records into the users table. It is noteworthy that, after VALUES in the above INSERT statement must each record value into a pair (...), the middle use "," segmentation. Suppose there is a table table1

CREATE TABLE table1 (n INT);

If you want to insert 5 records into table1, the following wording is wrong:

INSERT INTO table1 (i) VALUES (1,2,3,4,5);

MySQL will throw the following error

ERROR 1136: Column count does not match value count at row 1

The correct wording should be like this:

INSERT INTO t able1 (i) VALUES (1), (2), (3), (4), (5);

Of course, this wording can also omit the column name, so that the value of each pair of brackets must be the same number, and this number must be consistent with the number of columns. Such as:

INSERT INTO t able1 VALUES (1), (2), (3), (4), (5);

REPLACE statement

We often encounter this situation when using the database. If a table in a field to create a unique index, when we then use this table to insert an existing key value of a record, it will throw a primary key conflict error. Of course, we may want to overwrite the original record value with the value of the new record. If you use the traditional approach, you must first use the DELETE statement to delete the original record, and then use INSERT insert a new record. MySQL provides us with a new solution, which is the REPLACE statement. Using REPLACE to insert a record, if not repeated, REPLACE and INSERT function, if there are duplicate records, REPLACE the value of the new record to replace the original record value.

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 DELETE and INSERT together.

When using REPLACE, the table must have a unique index, and the index of the field where the value can not allow null, otherwise REPLACE and INSERT exactly the same.

After the implementation of REPLACE, the system returns the number of rows affected, if the return of 1, that there is no duplicate records in the table, return 2, indicating a duplicate record, the system automatically calls DELETE Delete this record, and then INSERT record to insert this record again. If the return value is greater than 2, it shows that there are multiple unique indexes, multiple records are deleted and inserted.

REPLACE syntax and INSERT are very similar, such as the following REPLACE statement is to insert or update 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, this is because there is more than one unique index in the table. In this case, REPLACE considers each unique index, removes duplicate records for each index, and inserts the new record. Suppose there is a table1 table, there are three 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);

Suppose table1 already has 3 records

abc

1 1 1

2 2 2

3 3 3

Below we use the REPLACE statement to insert a record into table1.