MySQL delete multiple table data and delete associated data

1. Delete from T1 where condition

2, delete t1 from T1 where condition

3, delete t1 from T1,t2 where condition

4, delete t1,t2 from t1,t2 where condition

The first 3 are feasible, and the 4th is not.

That is, simply use the DELETE statement to remove data from multiple tables, but you can create cascading deletes and cascade between two tables

Delete a relationship, you can delete data from one table while deleting the related data from another table.

1, from the data table t1 those ID values in the datasheet t2 have matching records are deleted

The code is as follows	Copy Code
Delete T1 from t1,t2 where t1.id=t2.id or delete from T1 USING t1,t2 where t1.id=t2.id

2, from the datasheet T1 in the data table T2 no matching records to find out and delete

The code is as follows	Copy Code
DELETE T1 from T1 left JOIN T2 on t1.id=t2.id WHERE t2.id is NULL or DELETE from t1,using T1 left JOIN T2 in T1.id=t2.id WHERE t2.id is NULL

3, from the two tables to find the same record of data and two of the data in the table deleted

The code is as follows	Copy Code
DELETE t1,t2 from T1 left JOIN T2 on T1.id=t2.id WHERE t1.id=25

Note that the t1,t2 in the delete t1,t2 from here cannot be an alias

Such as:

The code is as follows	Copy Code
Delete T1,T2 from table_name as T1 left join Table2_name as T2 on T1.id=t2.id where Table_name.id=25

Executing in the data is wrong (MYSQL version is not less than 5.0 in 5.0 is OK)

The above statement is rewritten as

The code is as follows	Copy Code
Delete Table_name,table2_name from table_name as T1 left join Table2_name as T2 on T1.id=t2.id where table_name.id=25

Executing in the data is wrong (MYSQL version is less than 5.0 in 5.0 is OK)

）

Delete How to delete associated data and implement cascading deletions

Create TABLE IF not EXISTS ' dcsmember ' (
' id ' int (3) auto_increment NOT NULL primary key,
' name ' varchar (a) not NULL,
' password ' varchar NOT NULL,
' PhoneNumber ' char (+),
' time ' DATETIME not null,
' jifen ' int (8) & nbsp NOT NULL default ' varchar ',
' email ' is not null,
' power ' int (2) is not null default ' 1 '
) CHARACTER SET gb2312 ";
Create table  IF not EXISTS ' SP ' (
"spid" int (5) auto_increment NOT null primary key,
' Spuserid ' int (3) n OT null,
' spname ' varchar () not NULL,
' Spmoney ' float (6) is not NULL,
' spopt ' char is not NULL,
' Spsay ' VA Rchar,
' sptime ' DATE DEFAULT ' 2008-10-01 ',
' Spendor ' TINYINT (1) DEFAULT ' 1 ',
INDEX (' Spuserid ')
) CH Aracter SET gb2312 ";
 

The code is as follows	Copy Code
$sqldel = "DELETE from Dcsmember WHERE email= ' $value '";

Now I'm going to delete the members ' information from the membership list.
, you also need to delete the member's information from another table, Dcsmember. ID is outer code, corresponding to Sp.spuserid,?
How to Write

code is as follows	copy code
Delete D, s From Dcsmember D INNER join SP s on d.id = S.spuserid where d.email= "xxxxxx"