Shulou(Shulou.com)05/31 Report--

This article mainly explains "the update statement of MySQL database". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn the update statement of MySQL database.

1. INSERT and REPLACE

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

1. General usage of INSERT

The INSERT statement in MySQL is quite different from the standard INSERT. In the 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 in 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 use in pairs, such as the following statement will produce a medium effect.

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

If the SET method is used, at least one column must be assigned a value. If a field uses default values (such as default or self-increment), both methods can omit those fields. For example, if self-increment is used in the id field, 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 has also made some changes on VALUES. If nothing is written in the VALUES, MySQL will insert the new record using the default values for each column in the table.

INSERT INTO users () VALUES ()

If nothing is written after the table name, it means that values are assigned to all fields in the table. In this way, not only are the values in VALUES consistent with the number of columns, but the order cannot be reversed. INSERT INTO users VALUES (123, Yao Ming, 25)

MySQL will report an error if you write the INSERT statement in the following form.

INSERT INTO users VALUES (Yao Ming, 25)

two。 Insert multiple records using INSERT

When you see this title, you may ask, what is there to say? if you call the INSERT statement multiple times, you can insert multiple records. However, using this method will increase the load on the server, because every time the SQL server is executed, the SQL will be analyzed, optimized, 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 Ming, 25), Bill. Gates, 50), (Martians, 600)

The above INSERT statement inserts three records in succession into the users table. It is worth noting that the VALUES in the above INSERT statement must be followed by a pair of (...) values for each record. In the middle, use the "," division. Suppose there is a table table1

CREATE TABLE table1 (n INT)

If you want to insert five records into table1, the following is incorrect:

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

And the correct way to write it should be like this:

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

Of course, column names can also be omitted in this way, so that the number of values in each pair of parentheses must be the same, and the 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 often encounter this situation when using a database. If a table has a unique index on a field, when we insert a record into the table with an existing key value, an error of primary key conflict will be thrown. Of course, we may want to overwrite the original record value with the new record value. If you use traditional practice, you must first delete the previous record using the delete statement, and then insert the new record using INSERT. In MySQL, it provides us with a new solution, which is the REPLACE statement. When using REPLACE to insert a record, if it is not repeated, REPLACE has the same function as INSERT. If there is a duplicate record, REPLACE replaces the original record value with the value of the new record.

The biggest advantage of using REPLACE is that you can combine DELETE and INSERT into one to form an atomic operation. This eliminates the need to add complex operations such as transactions when using DELETE and INSERT at the same time.

When using REPLACE, there must be a unique index in the table, and the field in which the index is located cannot allow null values, otherwise REPLACE will be exactly the same as INSERT.

After executing REPLACE, the system returns the number of rows affected. If 1 is returned, there is no duplicate record in the table. If 2 is returned, there is a duplicate record. The system automatically calls DELETE to delete the record, and then the record is inserted with INSERT. If the returned value is greater than 2, there are multiple unique indexes and multiple records are deleted and inserted.

The syntax of REPLACE is very similar to INSERT, such as the following REPLACE statement inserts or updates a record.

REPLACE INTO users (id,name,age) VALUES 123Zhao 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

It was mentioned above that REPLACE can affect more than three records because there is more than one unique index in the table. In this case, REPLACE will consider each unique index, delete the duplicate record corresponding to each index, and then insert the new record. Suppose you have a table1 table with three fields a, b, and c. They all have a unique index.

CREATE TABLE table1 (an INT NOT NULL UNIQUE,b INT NOT NULL UNIQUE,c INT NOT NULL UNIQUE)

Suppose there are already three records in the table1

A b c

1 1 1

2 2 2

3 3 3

Let's use the replace statement to insert a record into the table1.

REPLACE INTO table1 (a, b, c) VALUES

The result returned is as follows

Query OK, 4 rows affected (0.00 sec)

The record in table1 is as follows

A b c

1 2 3

We can see that REPLACE deletes all three records and then inserts (1, 2, 3).

At this point, I believe you have a deeper understanding of the "update statement of MySQL database". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.