您可以对不想删除的行使用NOT IN运算符。以下是语法-delete from yourTableName where yourColumnName NOT
IN(‘yourValue1’,‘yourValue2’,‘yourValue3’,.........N);
让我们首先创建一个表-mysql> create table deleteAllRowsWithCondition
-> (
-> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> Name varchar(20)
-> );
以下是使用insert命令在表中插入一些记录的查询-mysql> insert into deleteAllRowsWithCondition(Name) values('Larry');
mysql> insert into deleteAllRowsWithCondition(Name) values('John');
mysql> insert into deleteAllRowsWithCondition(Name) values('Sam');
mysql> insert into deleteAllRowsWithCondition(Name) values('Mike');
mysql> insert into deleteAllRowsWithCondition(Name) values('Carol');
mysql> insert into deleteAllRowsWithCondition(Name) values('Bob');
mysql> insert into deleteAllRowsWithCondition(Name) values('David');
以下是使用select语句显示表中所有记录的查询-mysql> select * from deleteAllRowsWithCondition;
这将产生以下输出-+----+-------+
| Id | Name |
+----+-------+
| 1 | Larry |
| 2 | John |
| 3 | Sam |
| 4 | Mike |
| 5 | Carol |
| 6 | Bob |
| 7 | David |
+----+-------+
7 rows in set (0.00 sec)
这是删除具有某些条件的所有行的查询。我们不会在此处删除“ John”,“ Mike”和“ Carol”-mysql> delete from deleteAllRowsWithCondition where Name NOT IN('John','Mike','Carol');
让我们检查是否从表中删除了某些行。以下是查询-mysql> select * from deleteAllRowsWithCondition;
这将产生以下输出-+----+-------+
| Id | Name |
+----+-------+
| 2 | John |
| 4 | Mike |
| 5 | Carol |
+----+-------+
3 rows in set (0.00 sec)