How to delete MySQL database data

DELETE statement

DELETE statement is used to delete records, the syntax is as follows: (similar to "UPDATE" syntax)

DELETE [IGNORE] FROM 表名
WHERE 条件1, 条件2, .....
ORDER BY ......
LIMIT ......;

-- 使用 IGNORE 关键字时，当存在外键约束组织我们删除记录，那么则会忽略删除该条数据
-- 使用 WHERE 子句删除条件范围内的记录；如果不使用 WHERE 子句，则是删除全表范围
-- 使用 ORDER BY 关键字，将被删除的记录进行排序以后，产出符合条件的一些数据
-- 使用 LIMIT 关键字依然是分页的意思，

" DELETE" statement execution order: FROM ---> WHERE ---> ORDER BY ---> LIMIT ---> DELETE

DELETE statement practice ①

Delete records of employees with more than 20 years of service in 10 departments (this is relatively simple, only involves one table)

DELETE FROM  t_emp 	
WHERE deptno = 10 
AND DATEDIFF(NOW(), hiredate)/365 >= 20;

DELETE statement practice ②

Delete the employee records with the highest salary in the 20 departments (in addition to using the WHERE clause to filter out the employees in the 20 departments, you also need to sort them in descending order according to salary and delete the highest records)

DELETE FROM  t_emp 	
WHERE deptno = 20 
ORDER BY sal+IFNULL(comm,0) DESC
LIMIT 1;

The syntax of the "DELETE" statement is still very simple. After having the foundation of the "UPDATE" statement, it will be easier to understand when looking at the "DELETE" statement, because these clauses are not very different. many.

Table join (inner join) of DELETE statement

Because the efficiency of correlated subqueries is very low, we can use table joins to improve the deletion efficiency of DELETE statements.

The inner join syntax of "DELETE" SQL statement is as follows:

DELETE 表1, ...... FROM 表1 JOIN 表2 ON 条件
WHERE 条件1, 条件2, ......
ORDER BY ......
LIMIT ......

-- 需要注意的是，这里的 "WHERE"、"ORDER BY"、"LIMIT" 子句都是可选条件

Table connection exercise of DELETE statement ①

Delete the SALES department and all employee records in this department

Analysis: Delete records from two tables

Analysis: Use a DELETE statement to delete records from two tables. You can use table connection (refer to the syntax above)

Analysis: Belong to The employee records of the SALES department are unknown. You need to first use the conditional query to filter out the employee records of the SALES department

DELETE e, d
FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno
WHERE d.dname="SALES";

##Table connection exercise for DELETE statement ②

Delete every employee record that is lower than the average minimum salary of the department

Analysis: Still use table connection, because first you need to query the department for grouping, query Department number and average base salary.

Analysis: Use the queried result set as a table to connect with the "employee table". The connection condition is the employee's "department number". Another condition is that the employee's monthly salary must be lower than the average of the department. Basic salary

DELETE e
FROM t_emp e JOIN (SELECT deptno, AVG(sal) AS avg FROM t_emp GROUP BY deptno) t
ON e.deptno=t.deptno 
AND e.sal < t.avg;

Table connection exercise of DELETE statement ③

Delete the employee records of employee "KING" and his subordinates, use table connection to achieve

Analysis: Use the "WHERE" clause to find employee records whose "ename" is "KING"

Analysis: Combine the found employee records of "KING" with the "mgr" field of the employee table as The "employee number" of "KING" is connected

DELETE e
FROM t_emp e JOIN (SELECT empno FROM t_emp WHERE ename="KING") t
ON e.mgr=t.empno OR e.empno=t.empno;

The table connection of the DELETE statement (outer connection)

The table connection of the "DELETE" statement can be It is an inner connection or an outer connection.

The outer join syntax of the "DELETE" SQL statement is as follows:

DELETE 表1, ...... FROM 表1 [LEFT | RIGHT] JOIN 表2 ON 条件
WHERE 条件1, 条件2, ......
ORDER BY ......
LIMIT ......

-- 需要注意的是，这里的 "WHERE"、"ORDER BY"、"LIMIT" 子句都是可选条件
-- 除了 在连接表 的时候选择 "LEFT JOIN" 与 "RIGHT JOIN" 的区别之外，其他的与内连接的语法一样

Outer join practice of the DELETE statement

Delete employees in the "SALES" department, as well as employees without departments.

Analysis: Use "left outer join" to connect with the department table, because "Zhang San" in the "employee table" does not have a department; so it should be retained and connected with the department table.

DELETE e
FROM t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno
WHERE d.dname="SALES" OR e.deptno IS NULL

Quickly delete all data in the table

The "DELETE" statement deletes records under the transaction mechanism ( What is the "transaction mechanism" will be introduced in detail in a subsequent article). Before deleting records, you must first save the deleted records to the log file, and then delete the records. When "DELETE" is used to delete large amounts of data, the transaction mechanism causes the deletion to slow down.

The "TRUNCATE" statement deletes records outside the "transaction mechanism", and its execution speed is much faster than the "DELETE" statement.

The syntax of "TRUNCATE" is as follows:

TRUNCATE TABLE 表名;

-- 需要注意的是，"TRUNCATE"  语句一次只能清空一张数据表，不能够一次性的清空多张数据表。

rrree

MySQL Delete statement section

##Statement Functiondrop statement Delete database and tabledelete statementDelete records in the tableupdate set statementModify and update statements, the original value will also be overwritten (delete) when updating alert statementDelete field: alter table table name drop field name;

	drop database name; drop table name;
	delete from weibo_user where username="xiaomu"; Note: If the where condition is not added to the delete statement, all records in the table will be deleted
	Update set is used with where to change certain records Note: If there is no where clause in the update set statement to specify the change Condition, all values ​​of this field in the database will be updated
	Delete Primary key: alter table table name drop primary key; Update table name: alter table table name rename to new table name;

The above is the detailed content of How to delete MySQL database data. For more information, please follow other related articles on the PHP Chinese website!