View MySQL database update statement _ MySQL

There are two types of SQL statements used to operate databases: Query statements, SELECT statements, and update statements, which are also called data operation statements. The implication is to modify the data. There are three statements in the standard SQL statements: INSERT, UPDATE, and DE MySQL statements.

There are two types of SQL statements used to operate databases: Query statements, SELECT statements, and update statements, which are also called data operation statements. The implication is to modify the data. There are three statements in standard SQL: INSERT, UPDATE, and DELETE. There is another REPLACE statement in MySQL. Therefore, this article takes MySQL as the background to discuss how to make the update statement in SQL.

　　I. INSERT and REPLACE

Both INSERT and REPLACE statements INSERT new data into the table. The syntax of these two statements is similar. The main difference between them is how to process repeated data.

　　1. general INSERT usage

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

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. during use, the column name must be consistent with 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 the column names and column values to appear and use in pairs. the following statement will produce the same effect.

Insert into users SET id = 123, name = 'Yao Ming ', age = 25;

If the SET method is used, a value must be assigned to at least one column. If a field uses a missing value (such as default value or auto-increment value), you can omit these fields in either of the two methods. If auto-increment is used in the id field, the preceding 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 in VALUES. If nothing is written in VALUES, MySQL inserts a new record using the default value of each column in the table.

Insert into users () VALUES ();

If nothing is written after the table name, it indicates that all fields in the table are assigned a value. In this way, not only must the value in VALUES be consistent with the number of columns, but the order cannot 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 );

　　2. INSERT multiple records

If you see this title, you may ask, is there anything you can say about it? won't you be able to INSERT multiple records after multiple INSERT statements are called! However, using this method increases the load on the server, because every SQL Server executes the same SQL analysis and optimization operations. Fortunately, MySQL provides another solution, that is, using 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), ('marker', 600 );

The preceding INSERT statement inserts three records into the users table consecutively. It is worth noting that the VALUES of each record must be placed in a pair (…) after VALUES in the preceding INSERT statement (...) "," Is used in the middle. Suppose there is a table table1

Create table table1 (n INT );

If you want to insert five records into table 1, the following statement is incorrect:

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

The correct statement is as follows:

Insert into t able1 (I) VALUES (1), (2), (3), (4), (5 );

Of course, this statement can also omit the column name, so that the number of values in each pair of brackets must be consistent, and this number must be consistent with the number of columns. For example:

Insert into t able1 VALUES (1), (2), (3), (4), (5 );