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

355

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()等實現去重，同時注意索引優化、避免函數和類型轉換以提升性能。

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

直接說吧，WHERE子句在SQL里就是個過濾器，你想篩掉啥，就用它。

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

根據標題詳細展開說明解決該問題

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

WHERE 后面跟的是條件表達式，只有滿足條件的行才會被選中。排除數據，本質上就是構造一個“不滿足”的條件。

  1. 基礎排除：NOT 操作符

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

    最直接的方式就是使用 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 只能使用分組列或者聚合函數作為條件。

? 版權聲明
THE END
數據庫
# 數據庫# ai# 對象# 性能優化# sql# select# 數據類型# 區別# NULL# count# 類型轉換# date# 聚合函數# 隱式類型轉換
喜歡就支持一下吧
點贊5 分享