Detailed explanation of sample code for mysql delete multi-table connection deletion function

This article mainly introduces mysql delete Related information about the multi-table connection deletion function. Friends in need can refer to

for a single table Delete:

DELETE FROM tableName WHERE columnName = value;
删除表内的所有行：
即：保留表的结构、属性、索引
DELETE FROM tablename;
DELETE * FROM tablename;

Delete all content in the same table (delete data, table structure)

TRUNCATE customer;

Cannot report how many rows were deleted, and can only be used for a single table

Multi-table connection deletion:

DELETE orders,itrms FROM orders,items 
  WHERE orders.userid = items.userid
  AND orders.orderid = items.orderid
  AND orders.date<"2000/03/01";

The name of the table to be deleted is listed after DELETE, and the table used in the connection condition is listed after FROM

Assumed deletion All wineries in the BV region, but do not delete the place name

DELETE winery FROM region,winery 
  WHERE winery.regionid = region.regionid
  AND region.regionname = 'BV';

The query only affects the winery table, but also uses winery and region to find the records that need to be deleted

Use advanced connection query

DELETE orders,items FROM orders
  INNER JOIN otems ON orders.orderid = items.orderid
  AND orders.userid = items.userid
  WHERE orders.date<"2000/03/01";

You can also use nested queries in the DELETE statement (internal queries cannot reference deleted data), GROUP BY, HAVING;

You can also use ORDER BY in a single table query, singular unless used in conjunction with LIMIT Delete some data rows, otherwise it doesn't make much sense.

Add quick modifier to quickly delete index entries and accelerate large or frequent deletion operations

DELETE QUICK FROM customer WHERE userid<10;

Can only be used for tables of type MyISAM

Clean MyISAM tables

OPTIMIZE TABLE customer;

The above is the detailed content of Detailed explanation of sample code for mysql delete multi-table connection deletion function. For more information, please follow other related articles on the PHP Chinese website!