MYSQL multiple table query, delete, update some SQL statements

Cases

The code is as follows	Copy Code
SELECT cat. ' Name ', class. ' Title ' From ' Cat ', ' class ' WHERE cat. ' id ' = class. ' Cat ' and cat. ' ID ' =2 LIMIT 0, 30 DELETE cat, class from Cat, Class WHERE cat. ' ID ' =class. ' Cat ' and Cat. ' ID ' =1

Multiple table Updates
in MySQL 3.23, you can use LIMIT # to ensure that only the given number of record rows is changed.

If an ORDER BY clause is used (supported from MySQL 4.0.0), the record row is updated in the specified sequence. This is actually only useful together with LIMIT.

Starting with the MySQL 4.0.4, you can also perform an UPDATE that contains multiple tables:

The code is as follows	Copy Code
UPDATE Items,month SET Items.price=month.price WHERE items.id=month.id;

Note: Multiple table UPDATE can not use order by or LIMIT.

Multiple table deletions
The first multiple table deletion format is supported from the MySQL 4.0.0. The second multiple table deletion format is supported from the MySQL 4.0.2.

The matching record row in the table listed only before the FROM or USING clause is deleted. The effect is that you want to delete the row of records from multiple tables, and you can also have other tables for retrieval.

After the table name, the. * is only for compatibility with Access:

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

In the above case, we simply delete the matching record rows from the T1 and T2 tables.

If an ORDER BY clause is used (supported from MySQL 4.0.0), the record row is deleted in the specified sequence. This is actually only useful together with LIMIT. Examples are as follows:

The code is as follows	Copy Code
DELETE from Somelog WHERE user = ' Jcole ' ORDER by timestamp LIMIT 1

This deletes the record row that matches the WHERE clause and was first inserted (determined by timestamp).

The Limit rows option for the DELETE statement is unique to MySQL, which tells the server the maximum number of record rows that can be deleted before control is returned to the client. This can be used to ensure that a specific DELETE command takes too long. You can simply repeat the DELETE command until the number of rows affected is less than the LIMIT value.

Starting with MySQL 4.0, you can specify multiple tables in a DELETE statement to remove rows of records that depend on special cases in multiple tables from one table. However, in a multiple-table deletion, you cannot use order by or LIMIT.