5 detailed MySQL Database UPDATE statement

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, in a standard SQL statement, there is only one form of INSERT statement that inserts one record at a time.

INSERT into tablename (column name ...) Values (column value);

There is another form of MySQL.

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

The first method separates the column name from the column value, and when used, the column name 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 Ming, 25);

The second method allows column names and column values to appear and be used in pairs, such as the following statement, which produces the effect of a middle sample.

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 users (name, age) VALUES (Yao, 25);

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 you do not write anything after the table name, you are 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 you write the INSERT statement as follows, MySQL will get an error.

INSERT into Users VALUES (Yao Ming, 25);

2. Insert multiple records with insert

See this title maybe everyone will ask, this is what 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 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 doesnt 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).

Transfer from http://www.2cto.com/database/201007/54178.html

5 detailed MySQL Database UPDATE statement