-- 查找重復(fù)記錄（以 user_id 和 email 為例）
WITH Duplicates AS (
  SELECT 
    user_id, 
    email, 
    ROW_NUMBER() OVER (PARTITION BY email ORDER BY user_id) AS rn
  FROM users
)
SELECT * FROM Duplicates WHERE rn > 1;
-- 刪除重復(fù)記錄（保留最新的一條）
DELETE FROM users
WHERE user_id NOT IN (
  SELECT MAX(user_id) 
  FROM users 
  GROUP BY email
);

-- 填充缺失值（將 NULL 替換為默認(rèn)值）
UPDATE products
SET price = COALESCE(price, 0),  -- 價格為 NULL 時填充 0
    category = IFNULL(category, '未知')  -- 分類為 NULL 時填充 '未知'
WHERE price IS NULL OR category IS NULL;
-- 刪除包含缺失值的記錄
DELETE FROM orders
WHERE customer_id IS NULL;

-- 統(tǒng)一郵箱為小寫
UPDATE users
SET email = LOWER(TRIM(email));
-- 標(biāo)準(zhǔn)化日期格式（將 '2023-12-31' 轉(zhuǎn)為 '31-12-2023'）
UPDATE orders
SET order_date = DATE_FORMAT(STR_TO_DATE(order_date, '%Y-%m-%d'), '%d-%m-%Y');

-- 刪除年齡小于 0 或大于 120 的記錄
DELETE FROM users
WHERE age < 0 OR age > 120;
-- 查找不符合郵箱格式的記錄
SELECT * FROM users
WHERE email NOT REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';

-- 將字符串類型的價格轉(zhuǎn)為數(shù)值類型
ALTER TABLE products
MODIFY price DECIMAL(10, 2) AFTER CAST(price AS DECIMAL(10, 2));

-- 合并姓名（first_name 和 last_name）
UPDATE users
SET full_name = CONCAT(first_name, ' ', last_name);
-- 拆分地址（以逗號分隔）
ALTER TABLE customers
ADD street VARCHAR(100),
ADD city VARCHAR(50);
UPDATE customers
SET 
  street = SUBSTRING_INDEX(address, ',', 1),
  city = SUBSTRING_INDEX(address, ',', -1);

-- 計算平均價格和標(biāo)準(zhǔn)差
WITH Stats AS (
  SELECT 
    AVG(price) AS avg_price,
    STDDEV(price) AS std_price
  FROM products
)
-- 刪除價格超過 3 個標(biāo)準(zhǔn)差的異常值
DELETE FROM products
WHERE ABS(price - (SELECT avg_price FROM Stats)) > 3 * (SELECT std_price FROM Stats);

-- 示例：分批刪除重復(fù)記錄
DELETE FROM users
WHERE user_id IN (
  SELECT user_id FROM (
    SELECT user_id, 
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY user_id) AS rn
    FROM users
  ) t
  WHERE rn > 1
)
LIMIT 1000;  -- 每次處理 1000 條