第4章 数据预处理

4.1 数据表匹配合并

#INNER JOIN匹配查询
SELECT record_date,city,age,category,price,gender,pay,mp
FROM data1
INNER JOIN data2
ON data1.id=data2.id;

#LEFT JOIN匹配查询
SELECT record_date,city,age,category,price,gender,pay,mp
FROM data1
LEFT JOIN data2
ON data1.id=data2.id;

#RIGHT JOIN匹配查询
SELECT record_date,city,age,category,price,gender,pay,mp
FROM data1
RIGHT JOIN data2
ON data1.id=data2.id;

4.2 数据排序

#数据升序排序
SELECT *
FROM data1
ORDER BY age;

#数据降序排序
SELECT *
FROM data1
ORDER BY age DESC;

#对多列数据进行排序
SELECT *
FROM data1
ORDER BY age,price DESC;

4.3 数据分组

#age字段分组
SELECT age,
CASE
	WHEN age<30 THEN 'A'
	WHEN age>=30 AND age<50 THEN 'B'
	WHEN age>=50 THEN 'C'
	ELSE 'D' 
END
AS age_type
FROM data1;

#直接分组查询并汇总
SELECT COUNT(id) AS id_count,SUM(price) AS total_price,
CASE
	WHEN age<30 THEN 'A'
	WHEN age>=30 AND age<50 THEN 'B'
	WHEN age>=50 THEN 'C'
	ELSE 'D'
END
AS age_type
FROM data1
GROUP BY age_type
ORDER BY id_count;

4.4 数据分列

#数据分列
SELECT id,Record_date,city,age,category,price,
SUBSTRING_INDEX(category,'-',1)AS size,
SUBSTRING_INDEX(category,'-',-1)AS colour
FROM data1;

#按分列后的结果进行单列数据汇总
SELECT SUBSTRING_INDEX(category,'-',1) AS size,COUNT(id)
FROM data1
GROUP BY size;

#按分列后的结果进行多列数据汇总
SELECT SUBSTRING_INDEX(category,'-',1) AS size,COUNT(id) AS id_count,
ROUND(SUM(price),2) AS total_price
FROM data1
GROUP BY size;

#数据分列（改表） 
#添加两个空字段 
ALTER TABLE data1
ADD (size VARCHAR(255),colour VARCHAR(255)); 

#更新分列后的字段内容
UPDATE data1
SET size = SUBSTRING_INDEX(category,'-',1),
colour =  SUBSTRING_INDEX(category,'-',-1); 

SELECT *
FROM data1;