SQL数据处理流程

一、数据处理

1、数据清洗

• 对空值处理：删除/填充为0

-- 用 0 填充 NULL
SELECT COALESCE(sales, 0) AS sales FROM orders;-- 删除含 NULL 的记录
DELETE FROM users WHERE email IS NULL;

COALESCE(bonus, 0) 相当于IF(bonus IS NULL, 0, bonus)，当数据为null值，就返回0，否则返回本身

• 去重复值：删除（查找重复记录，删除重复值）

-- 查找重复记录
SELECT name, COUNT(*) FROM products GROUP BY name HAVING COUNT(*) > 1;-- 删除重复记录（保留最小 ID）
DELETE FROM products WHERE id NOT IN (SELECT MIN(id) FROM products GROUP BY name);

• 去字符串空格

-- 去除字符串空格
SELECT TRIM(name) FROM customers;

trim（name）去除字符串name首尾空格

• 处理异常值：筛选掉/替换异常值

-- 过滤年龄小于 0 或大于 120 的记录
SELECT * FROM users WHERE age BETWEEN 0 AND 120;-- 过滤金额为负数的记录
SELECT * FROM orders WHERE amount >= 0;

-- 将异常年龄替换为 NULL
UPDATE users 
SET age = NULL 
WHERE age < 0 OR age > 120;-- 使用 CASE 表达式替换异常值
SELECT name,CASE WHEN age < 0 THEN NULLWHEN age > 120 THEN 120ELSE ageEND AS valid_age
FROM users;

2、数据转换

（1）数据格式转换

• 字符串转换为数值

-- 字符串转数值
SELECT CAST(revenue AS DECIMAL(10,2)) FROM sales;

cast：CAST(expression AS target_type [length])

• expression：要转换的值（如列名、常量或表达式）
• target_type：目标数据类型（如 INTEGER、VARCHAR、DATE 等）
• length：可选参数，用于指定长度（如 VARCHAR(50)）
• 用于不同格式之间的转换

• 数值与字符串相互转换：

-- 字符串转整数
SELECT CAST('123' AS INTEGER);  -- 结果：123-- 整数转字符串
SELECT CAST(123 AS VARCHAR(10));  -- 结果：'123'-- 浮点数（保留两位小数）
SELECT CAST(3.14159 AS DECIMAL(10,2));  -- 结果：3.14-- 浮点数转字符串
SELECT CAST(CAST(3.14159 AS DECIMAL(10,2)) AS VARCHAR);  -- 结果："3.14"

CAST(3.14159 AS DECIMAL(10,2)) ，在计算机中浮点数是用二进制储存的，所以要先用DECIMAL(10,2)转换为十进制表示的数据，2代表保留两位小数

• 日期和字符串相互转换：

-- 字符串转日期（格式需匹配）
SELECT CAST('2023-01-01' AS DATE);  -- 结果：2023-01-01-- 日期转字符串（按指定格式）
SELECT CAST(CURRENT_DATE AS VARCHAR(10));  -- 结果：'2023-01-01'

（2）统一日期格式 

-- 日期格式化
SELECT DATE_FORMAT(order_date, '%Y-%m') AS month FROM orders;

（3）字符串拼接

-- 字符串拼接
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

 二、数据集成

1、表连接：内连接、左连接、右连接

2、子查询

3、联合查询 union

三、高级数据转换

1、聚合与分组、窗口函数

2、数据透视 （case when新增列）

四、性能优化

（1）减少子查询，多使用表连接

（2）避免SELECT *，表明要查询的列

（3）大数据量时使用LIMIT offset进行分页

（4）创建索引，避免全表查询（索引就相当于目录）