mysql 删除指定日期以前得数据 mysql删除历史数据方法

如果记录不再需要，可以用delete 命令进行删除，语法如下：

DELETE FROM tablename [WHERE CONDITION]

例如，在emp 中将ename 为‘dony’的记录全部删除，命令如下：

mysql> delete from emp where ename='dony';
Query OK, 1 row affected (0.00 sec)

在MySQL 中可以一次删除多个表的数据，语法如下：

DELETE t1,t2…tn FROM t1,t2…tn [WHERE CONDITION]

如果from 后面的表名用别名，则delete 后面的也要用相应的别名，否则会提示语法错误。
在下例中，将表emp 和dept 中deptno 为3 的记录同时都删除：

mysql> select * from emp;
+--------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+--------+------------+---------+--------+
| zzx | 2000-01-01 | 100.00 | 1 |
| lisa | 2003-02-01 | 200.00 | 2 |
| bjguan | 2004-04-02 | 100.00 | 1 |
| bzshen | 2005-04-01 | 300.00 | 3 |
| dony | 2005-02-05 | 2000.00 | 4 |
+--------+------------+---------+--------+
5 rows in set (0.00 sec)
mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
| 1 | tech |
| 2 | sale |
| 3 | hr |
| 5 | fin |
+--------+----------+
4 rows in set (0.00 sec)
mysql> delete a,b from emp a,dept b where a.deptno=b.deptno and a.deptno=3;
Query OK, 2 rows affected (0.04 sec)
mysql>
mysql>
mysql> select * from emp;
+--------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+--------+------------+---------+--------+
| zzx | 2000-01-01 | 100.00 | 1 |
| lisa | 2003-02-01 | 200.00 | 2 |
| bjguan | 2004-04-02 | 100.00 | 1 |
| dony | 2005-02-05 | 2000.00 | 4 |
+--------+------------+---------+--------+
4 rows in set (0.00 sec)
mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
| 1 | tech |
| 2 | sale |
| 5 | fin |
+--------+----------+
3 rows in set (0.00 sec)

 

如果觉得对您有帮助，麻烦您点一下推荐，谢谢！



好记忆不如烂笔头