Mysql update Statement details

This article details the update data of the update statement in mysql. If you need it, please refer to this article.

This article details the update data of the update statement in mysql. If you need it, please refer to this article.

MySQL UPDATE statement for a single table:

　

The Code is as follows:
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1 = expr1 [, col_name2 = expr2...] [WHERE where_definition] [Order by...] [LIMIT row_count] UPDATE statements for multiple tables: UPDATE [LOW_PRIORITY] [IGNORE] table_references SET col_name1 = expr1 [, col_name2 = expr2...] [WHERE where_definition]

The UPDATE syntax uses the new value to UPDATE columns in the original table rows. The SET clause indicates the columns to be modified and the values to be given. The WHERE clause specifies the rows to be updated. If there is no WHERE clause, all rows are updated. If the order by clause is specified, the row is updated in the specified ORDER. The LIMIT clause is used to specify a LIMIT to LIMIT the number of rows that can be updated.

The MySQL UPDATE Statement supports the following modifiers:

If you use the LOW_PRIORITY keyword, the UPDATE execution is delayed until no other clients read from the table.

If you use the IGNORE keyword, the update statement will not be interrupted even if an error occurs during the update process. If duplicate keyword conflicts occur, these rows are not updated. If the column is updated, the new value will cause data conversion errors, and these rows will be updated to the closest legal value.

If you access a column through tbl_name in an expression, UPDATE uses the current value in the column. For example, the following statement sets the age column to be greater than the current value:

　

The Code is as follows:
MySQL> UPDATE persondata SET ageage = age + 1; MySQL UPDATE values are evaluated from left to right. For example, the following statement doubles the age column and then adds it: MySQL> UPDATE persondata SET ageage = age * 2, ageage = age + 1;

If you set a column to its current value, MySQL will notice this, but will not update it.

If you update a column that has been defined as not null to NULL, the column is set to the default value corresponding to the column type, and the number of warnings is accumulated. For numeric type, the default value is 0; for string type, the default value is null string (''); For type, the default value is" zero.

UPDATE returns the number of actually changed rows. The MySQL_info () c api function returns the number of matched and updated rows and the number of warnings generated during the UPDATE process.

You can use LIMIT row_count to LIMIT the UPDATE range. The LIMIT clause is a LIMIT for matching rows. As long as the row_count rows that can satisfy the WHERE clause are found, the statement is aborted, regardless of whether these rows are changed.

If an UPDATE statement contains an order by clause, the row is updated in the ORDER specified BY the clause.

You can also perform UPDATE operations on multiple tables. The table_references clause lists the tables contained in the Union. The following is an example:

　

The Code is as follows:
SQL> UPDATE items, month SET items. price = month. price WHERE items. id = month. id;

The preceding example shows the internal Union using the comma operator, but the multiple-table UPDATE statement can use any types of Union allowed in the SELECT statement, such as left join.

Note: you cannot use order by, LIMIT, or multiple-table UPDATE simultaneously.

In a modified multiple-table UPDATE, some columns are referenced. You only need the MySQL UPDATE permission for these columns. Some columns are read but not modified. You only need the SELECT permission for these columns.

If your multiple-table UPDATE statement contains an InnoDB table with foreign key restrictions, the order in which the MySQL optimizer processes the table may be different from that in the upper-lower-level relationship. In this case, the statement is invalid and rolled back. At the same time, UPDATE a single table and rely ON the on update function. This function is provided by InnoDB to modify other tables.

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.

The Code is as follows:
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:

The Code is as follows:
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.

The Code is as follows:
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:

The Code is 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.

The Code is as follows:
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.

The Code is as follows:
Insert into users VALUES (123, 'Yao ming', 25 );

If the INSERT statement is written as follows, MySQL will report an error.

The Code is as follows:
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.

The Code is as follows:
Insert into users (name, age) VALUES ('Yao ming', 25), ('bill. Gates', 50), ('marker', 600 );