Home > Database > Mysql Tutorial > Detailed explanation of sample code for mysql delete multi-table connection deletion function

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

黄舟
Release: 2017-03-15 17:16:08
Original
1499 people have browsed it

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;
Copy after login

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

TRUNCATE customer;
Copy after login

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";
Copy after login

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 = &#39;BV&#39;;
Copy after login

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";
Copy after login

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;
Copy after login

Can only be used for tables of type MyISAM

Clean MyISAM tables

OPTIMIZE TABLE customer;
Copy after login

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!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template