????????利用 MySQL 進行數據清洗是數據預處理的重要環節，以下是常見的數據清洗操作及對應 SQL 示例：

1. 去除重復數據

使用?ROW_NUMBER()?或?GROUP BY?識別并刪除重復記錄。

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

2. 處理缺失值

• 填充默認值：使用?COALESCE()?或?IFNULL()。
• 刪除缺失值：使用?WHERE?過濾。

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

3. 數據標準化（大小寫、格式統一）

• 轉換大小寫：使用?UPPER()?或?LOWER()。
• 去除空格：使用?TRIM()。
• 日期格式化：使用?STR_TO_DATE()?或?DATE_FORMAT()。

-- 統一郵箱為小寫
UPDATE users
SET email = LOWER(TRIM(email));-- 標準化日期格式（將 '2023-12-31' 轉為 '31-12-2023'）
UPDATE orders
SET order_date = DATE_FORMAT(STR_TO_DATE(order_date, '%Y-%m-%d'), '%d-%m-%Y');

4. 處理無效數據

• 范圍過濾：檢查數值是否在合理區間。
• 正則匹配：驗證格式（如郵箱、手機號）。

-- 刪除年齡小于 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,}$';

5. 數據類型轉換

使用?CAST()?或?CONVERT()?轉換字段類型。

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

6. 合并 / 拆分字段

• 合并字段：使用?CONCAT()。
• 拆分字段：使用?SUBSTRING()?或?SUBSTRING_INDEX()。

-- 合并姓名（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);

7. 異常值處理

通過統計方法（如 Z-score）識別并處理異常值。

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

執行建議

1. 備份數據：清洗前先備份，避免誤操作。
2. 測試邏輯：先用?SELECT?驗證清洗邏輯，再執行?UPDATE?或?DELETE。
3. 分批處理：大數據量時使用?LIMIT?分批更新，避免鎖表。

-- 示例：分批刪除重復記錄
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 rnFROM users) tWHERE rn > 1
)
LIMIT 1000;  -- 每次處理 1000 條

索引：

索引是數據庫中用于提高查詢效率的關鍵工具，它類似書籍的目錄，可以快速定位到數據的位置。

1. 索引的作用

• 加速查詢：通過索引，數據庫無需掃描全量數據，直接定位到符合條件的記錄。

• 優化排序：索引通常已排序，可避免額外的排序操作。

• 強制唯一性：唯一索引（如主鍵）可防止重復數據。

2. 索引的原理

• 數據結構：常見的索引使用 B-Tree（MySQL InnoDB）或哈希表（Memory 引擎）。

• 存儲方式：索引單獨存儲，包含鍵值和指向數據行的物理地址。