MYSQL 常用数据清洗代码
时间: 2023-11-12 15:25:34 浏览: 36
1. 删除重复行
DELETE FROM table_name WHERE column_name1 = value AND column_name2 = value;
2. 去除空格
UPDATE table_name SET column_name = TRIM(column_name);
3. 删除空值
DELETE FROM table_name WHERE column_name IS NULL;
4. 去除特殊字符
UPDATE table_name SET column_name = REPLACE(column_name, '特殊字符', '');
5. 转换数据类型
ALTER TABLE table_name MODIFY column_name INT;
6. 分割字符串
SELECT SUBSTR(column_name, 1, POSITION(',' IN column_name)-1) AS column1, SUBSTR(column_name, POSITION(',' IN column_name)+1) AS column2 FROM table_name;
7. 合并列
UPDATE table_name SET column_name = CONCAT(column_name1, ' ', column_name2);
8. 转换日期格式
UPDATE table_name SET column_name = DATE_FORMAT(column_name, '%Y-%m-%d');
9. 转换大小写
UPDATE table_name SET column_name = UPPER(column_name);
10. 替换空值
UPDATE table_name SET column_name = IFNULL(column_name, '替换值');