SQL中如何用WHERE排除某些数据 WHERE子句数据排除技巧大全

where子句在sql中用于过滤数据，通过条件表达式选择满足条件的行。1.基础排除使用not操作符，如not in排除指定值；2.多条件排除可用and或or组合，注意括号确保优先级；3.NULL值需用is not null排除；4.范围排除用not between；5.模糊排除用not like配合通配符；此外还可结合distinct、group by、row_number()等实现去重，同时注意索引优化、避免函数和类型转换以提升性能。

直接说吧，WHERE子句在SQL里就是个过滤器，你想筛掉啥，就用它。

根据标题详细展开说明解决该问题

WHERE 后面跟的是条件表达式，只有满足条件的行才会被选中。排除数据，本质上就是构造一个“不满足”的条件。

1. 基础排除：NOT 操作符

   

   最直接的方式就是使用 NOT 操作符。比如，你想排除 id 为 1, 2, 3 的数据：

   select * FROM your_table WHERE NOT id IN (1, 2, 3);

   这里，IN (1, 2, 3) 选择了 id 为 1, 2, 或者 3 的行，NOT IN 就反过来，选择了 id 不是 1, 2, 3 的行。

2. 多条件排除：AND 和 OR 的巧妙运用

   如果你的排除条件比较复杂，需要组合多个条件，AND 和 OR 就派上用场了。

   比如，你想排除 status 为 ‘pending’ 并且 create_time 在 ‘2023-01-01’ 之前的数据：

   SELECT * FROM your_table WHERE NOT (status = 'pending' AND create_time < '2023-01-01');

   注意这里的括号，它确保了 AND 操作的优先级高于 NOT。

   或者，你想排除 status 为 ‘pending’ 或者 status 为 ‘rejected’ 的数据：

   SELECT * FROM your_table WHERE status != 'pending' AND status != 'rejected';

   这里不能直接用NOT (status = ‘pending’ OR status = ‘rejected’)，因为可能存在status为NULL的情况，导致结果不符合预期。

3. NULL 值的排除

   NULL 值是个特殊的存在，不能直接用 = 或者 != 来判断。你需要使用 IS NULL 和 IS NOT NULL。

   比如，你想排除 email 为 NULL 的数据：

   SELECT * FROM your_table WHERE email IS NOT NULL;

4. 范围排除：BETWEEN 和 NOT BETWEEN

   如果你想排除某个范围的数据，可以使用 BETWEEN 和 NOT BETWEEN。

   比如，你想排除 price 在 10 到 100 之间的数据：

   SELECT * FROM your_table WHERE price NOT BETWEEN 10 AND 100;

5. 模糊排除：LIKE 和 NOT LIKE

   如果你想排除包含某个模式的数据，可以使用 LIKE 和 NOT LIKE。

   比如，你想排除 name 包含 ‘test’ 的数据：

   SELECT * FROM your_table WHERE name NOT LIKE '%test%';

   % 是通配符，表示任意字符。

SQL排除重复数据的几种方法？

1. DISTINCT 关键字

   最简单的方法就是使用 DISTINCT 关键字。它会返回指定列的唯一值。

   SELECT DISTINCT column1, column2 FROM your_table;

   但是，DISTINCT 只能作用于整个行，也就是说，只有当 column1 和 column2 的值都相同时，才会被认为是重复行。

2. GROUP BY 子句

   GROUP BY 子句可以将具有相同值的行分组在一起。然后，你可以使用聚合函数（比如 count、SUM、AVG 等）来处理这些分组。

   SELECT column1, column2, COUNT(*) FROM your_table GROUP BY column1, column2 HAVING COUNT(*) > 1;

   这个查询会返回 column1 和 column2 的值，以及它们的重复次数。HAVING COUNT(*) > 1 表示只返回重复的行。

3. ROW_NUMBER() 函数

   ROW_NUMBER() 函数可以为结果集中的每一行分配一个唯一的序号。你可以使用这个序号来删除重复的行。

   WITH RowNumCTE AS (     SELECT         *,         ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY (SELECT 0)) AS RowNum     FROM         your_table ) DELETE FROM RowNumCTE WHERE RowNum > 1;

   这个查询首先使用 ROW_NUMBER() 函数为每一行分配一个序号，然后删除序号大于 1 的行，也就是重复的行。PARTITION BY column1, column2 表示按照 column1 和 column2 进行分组，ORDER BY (SELECT 0) 只是为了保证语法正确，实际上并不影响结果。

4. 使用临时表

   你可以先将唯一的数据插入到临时表中，然后清空原表，再将临时表的数据插入到原表中。

   -- 创建临时表 CREATE TEMPORARY TABLE temp_table AS SELECT DISTINCT column1, column2 FROM your_table;  -- 清空原表 TRUNCATE TABLE your_table;  -- 将临时表的数据插入到原表 INSERT INTO your_table SELECT * FROM temp_table;  -- 删除临时表 DROP TEMPORARY TABLE temp_table;

   这种方法比较繁琐，但是可以处理一些特殊情况。

5. 利用唯一索引

   如果你的表中已经存在唯一索引，那么插入重复数据时会报错。你可以利用这个特性来删除重复数据。

   -- 创建唯一索引 CREATE UNIQUE INDEX unique_index ON your_table (column1, column2);  -- 忽略插入错误 INSERT IGNORE INTO your_table (column1, column2) SELECT column1, column2 FROM your_table;  -- 删除重复数据 DELETE FROM your_table WHERE id NOT IN (SELECT MIN(id) FROM your_table GROUP BY column1, column2);

   这种方法的前提是你的表中已经存在唯一索引，或者可以创建唯一索引。

SQL中WHERE子句的性能优化技巧有哪些？

1. 索引的使用

   这是最基本也是最重要的优化技巧。在 WHERE 子句中使用的列，如果经常被查询，那么应该为其创建索引。

   索引就像一本书的目录，可以帮助数据库快速找到需要的数据，而不需要扫描整个表。

   CREATE INDEX index_name ON your_table (column_name);

   但是，索引也不是越多越好。索引会占用额外的存储空间，并且在插入、更新、删除数据时，需要维护索引，会降低性能。所以，应该只为经常被查询的列创建索引。

2. 避免在 WHERE 子句中使用函数

   如果在 WHERE 子句中使用函数，会导致索引失效。因为数据库无法使用索引来查找函数的结果。

   比如，你想查询 create_time 在 ‘2023-01-01’ 之后的数据：

   -- 不好的写法 SELECT * FROM your_table WHERE date(create_time) > '2023-01-01';  -- 好的写法 SELECT * FROM your_table WHERE create_time > '2023-01-01 00:00:00';

   第一种写法使用了 DATE() 函数，会导致索引失效。第二种写法直接比较 create_time 的值，可以使用索引。

3. 避免使用 OR 操作符

   在某些情况下，使用 OR 操作符会导致索引失效。

   比如，你想查询 status 为 ‘pending’ 或者 status 为 ‘rejected’ 的数据：

   -- 不好的写法 SELECT * FROM your_table WHERE status = 'pending' OR status = 'rejected';  -- 好的写法 SELECT * FROM your_table WHERE status IN ('pending', 'rejected');

   第一种写法使用了 OR 操作符，可能会导致索引失效。第二种写法使用了 IN 操作符，可以使用索引。

   当然，这并不是绝对的。在某些情况下，使用 OR 操作符的性能可能更好。你需要根据实际情况进行测试。

4. 避免使用 != 或者 操作符

   在某些情况下，使用 != 或者 操作符会导致索引失效。

   比如，你想查询 status 不为 ‘pending’ 的数据：

   -- 不好的写法 SELECT * FROM your_table WHERE status != 'pending';  -- 好的写法 SELECT * FROM your_table WHERE status IS NULL OR status <> 'pending';

   第一种写法使用了 != 操作符，可能会导致索引失效。第二种写法使用了 IS NULL 和 操作符，可以使用索引。

   同样，这并不是绝对的。你需要根据实际情况进行测试。

5. 使用 EXISTS 代替 IN

   在某些情况下，使用 EXISTS 代替 IN 可以提高性能。

   比如，你想查询 your_table 中存在于 another_table 中的数据：

   -- 不好的写法 SELECT * FROM your_table WHERE id IN (SELECT id FROM another_table);  -- 好的写法 SELECT * FROM your_table WHERE EXISTS (SELECT 1 FROM another_table WHERE another_table.id = your_table.id);

   EXISTS 只会检查子查询是否返回任何行，而 IN 会将子查询的结果加载到内存中。所以，在子查询的结果集比较大的情况下，使用 EXISTS 的性能更好。

6. 优化子查询

   如果 WHERE 子句中包含子查询，那么应该尽量优化子查询。

   比如，你可以使用 JOIN 代替子查询。

   -- 不好的写法 SELECT * FROM your_table WHERE column1 IN (SELECT column1 FROM another_table WHERE column2 = 'value');  -- 好的写法 SELECT your_table.* FROM your_table JOIN another_table ON your_table.column1 = another_table.column1 WHERE another_table.column2 = 'value';

   JOIN 可以将两个表连接在一起，避免了多次查询数据库。

7. 使用 LIMIT 限制结果集

   如果只需要一部分数据，可以使用 LIMIT 限制结果集的大小。

   SELECT * FROM your_table WHERE column1 = 'value' LIMIT 10;

   这样可以减少数据库的负担，提高查询速度。

8. 避免在WHERE条件中使用类型转换

当WHERE条件涉及不同数据类型的比较时，数据库可能会尝试进行隐式类型转换，这通常会导致索引失效。确保比较的数据类型一致，或者显式地进行类型转换，但要小心，显式转换也可能导致索引失效，需要具体情况具体分析。

SQL中WHERE子句与HAVING子句的区别？

1. 作用对象不同

   WHERE 子句用于过滤行，它作用于表中的每一行，决定哪些行会被选中。

   HAVING 子句用于过滤分组，它作用于 GROUP BY 子句创建的每个分组，决定哪些分组会被选中。

2. 使用时机不同

   WHERE 子句在分组之前进行过滤，也就是说，它在 GROUP BY 子句之前执行。

   HAVING 子句在分组之后进行过滤，也就是说，它在 GROUP BY 子句之后执行。

3. 可以使用的条件不同

   WHERE 子句可以使用任何列作为条件，包括未分组的列。

   HAVING 子句只能使用分组列或者聚合函数作为条件。

4. 是否需要 GROUP BY 子句

   WHERE 子句不需要 GROUP BY 子句。

   HAVING 子句必须与 GROUP BY 子句一起使用。

举个例子，你想查询每个部门的平均工资，并且只返回平均工资大于 5000 的部门：

SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 5000;

在这个例子中，GROUP BY department 将员工按照部门进行分组，AVG(salary) 计算每个部门的平均工资，HAVING AVG(salary) > 5000 过滤掉平均工资小于等于 5000 的部门。

如果你想查询工资大于 3000 的员工，并且只返回这些员工所在的部门的平均工资大于 5000 的部门：

SELECT department, AVG(salary) FROM employees WHERE salary > 3000 GROUP BY department HAVING AVG(salary) > 5000;

在这个例子中，WHERE salary > 3000 过滤掉工资小于等于 3000 的员工，GROUP BY department 将剩余的员工按照部门进行分组，AVG(salary) 计算每个部门的平均工资，HAVING AVG(salary) > 5000 过滤掉平均工资小于等于 5000 的部门。

总结一下：WHERE 过滤行，HAVING 过滤分组。WHERE 在分组前执行，HAVING 在分组后执行。WHERE 可以使用任何列作为条件，HAVING 只能使用分组列或者聚合函数作为条件。