如何删除除MySQL中的某些行以外的所有行？
您可以对不想删除的行使用NOTIN运算符。以下是语法-
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)