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

This article is to share with you the content of sample analysis of add, delete, change and query syntax in MySQL. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Ordinary single table update or delete sql we must be familiar with, but have you ever used linked table update or delete sql, this in the table association to do update and delete operation is very useful.

-

-

You can also perform UPDATE operations that include multiple tables. The table_ references clause lists the tables contained in the union. This syntax is explained in section 13.2.7.1, "JOIN Grammar". Here is an example:

UPDATE items,month SET items.price=month.price

WHERE items.id=month.id

The above example shows an internal union using a comma operator, but the multiple-table UPDATE statement can use any type of union allowed in the SELECT statement, such as LEFT JOIN.

Note: you cannot use ORDER BY or LIMIT with multiple-table UPDATE.

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

If you use a multiple-table UPDATE statement that contains InnoDB tables with foreign key restrictions, the order in which the MySQL optimizer processes the tables may be different from the order in which the upper and lower levels are related. 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 and is used to modify other tables accordingly. See Section 15.2.6.4, "FOREIGN KEY constraints".

Currently, you cannot update a table in a subquery and select from the same table.

-

Example: for example, I now have a main article table, and an article schedule, the main article table stores some articles of the title,category classification, add time, and update time and other basic information, attached to the article table put the article content.

I now want to update the content of the article with id 1 in the schedule. I can execute the following sql:

Update a left join b on a.id=b.a_id set a. Titleboys, girls, aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

Then look at the join table of delete to delete www~phperz~com.

Description in the mysql manual

You can specify multiple tables in a DELETE statement and delete rows from one or more tables based on specific conditions in multiple tables. However, you cannot use ORDER BY or LIMIT in a multi-table DELETE statement.

The table_references section lists the tables contained in the federation. This syntax is explained in section 13.2.7.1, "JOIN Grammar". Www~phperz~com

For the first syntax, only the corresponding rows in the table listed before the FROM clause are deleted. For the second syntax, only the corresponding rows in the table listed in the FROM clause (before the USING clause) are deleted. The effect is that you can delete rows from many tables at the same time and search with other tables:

DELETE t1, t2 FROM t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id

Or:

DELETE FROM t1, t2 USING t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id

When searching for rows to be deleted, these statements use all three tables, but only delete the corresponding rows from tables T1 and T2.

The above example shows an internal union using a comma operator, but a multi-table DELETE statement can use all the types of unions allowed in a select statement, such as LEFT JOIN.

This syntax allows you to add. * after the name to be compatible with Access.

If you use multi-table DELETE statements that include InnoDB tables, and those tables are restricted by foreign keys, the MySQL optimizer processes the tables, changing the original dependency. In this case, the statement has an error and returns to the previous step. To avoid this error, you should remove it from a single table and modify the other tables accordingly, relying on the ON DELETE functionality provided by InnoDB.

Note: when referencing a table name, you must use an alias (if given): php programmer station

DELETE t1 FROM test AS t1, test2 WHERE...

Cross-database deletions are supported for multiple table deletions, but in this case, you cannot use aliases when referencing tables. Example: phperz~com

DELETE test1.tmp1, test2.tmp2 FROM test1.tmp1, test2.tmp2 WHERE...

Currently, you cannot delete from a table while selecting from the same table in a subquery.

-

Now let's also take those two tables as examples and delete the contents of the article whose id is 1 in the main table. I can execute the following sql:

Delete a,b from a left join b on a.id=b.a_id where a.id=1

Notice where I add red. If you delete only the data in one table in multiple tables, then only the name of that table is followed by the delete. My example is to delete the data of aid=1 in both an and b, that is, to delete the data of both tables at the same time. Php programmer station

As shown in the figure:

Note: this article is explained by mysql, and others such as sql server and access,oracle also have their own different grammars. Please explore by yourself according to the grammar in the manual.

Thank you for reading! This is the end of this article on "sample Analysis of adding, deleting and changing Grammar in MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

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.