Excel是数据分析中最常用的工具,本篇文章通过mysql与excel的功能对比介绍如何使用mysql完成excel中的数据生成,数据清洗,预处理,以及最常见的数据分类,数据筛选,分类汇总,以及数据透视等操作。本篇文章我们介绍第5,6,7部分内容,数据提取,数据筛选以及数据汇总及透视。
5,数据提取
第五部分是数据提取,也是数据分析中最常见的一个工作。下面介绍每一种函数的使用方法。
按列提取数据
#按列提取 SELECT city FROM data1;
![](/d/20211018/2f7475e76ac227f8dd47f033687fbf80.gif)
按行提取数据
#按行提取
SELECT * FROM data1 WHERE city='beijing';
![](/d/20211018/0d6c6ce224847894d9543619beba5df9.gif)
按位置提取数据
#按位置提取
SELECT * FROM data1 LIMIT 2,5;
![](/d/20211018/c0b2b2636a93519a878de10d27a53ef9.gif)
按条件提取数据
#按条件提取并计算
SELECT AVG(price) FROM data1 WHERE city='beijing' AND age25;
![](/d/20211018/40f009fe5a291d7cedfb379cada2497d.gif)
6,数据筛选
第六部分为数据筛选,使用与,或,非三个条件配合大于,小于和等于对数据进行筛选,并进行计数和求和。与excel中的筛选功能和countifs和sumifs功能相似。
按条件筛选(与,或,非)
Excel数据目录下提供了“筛选”功能,用于对数据表按不同的条件进行筛选。mysql中使用WHERE完成筛选操作,配合sum和count函数还能实现excel中sumif和countif函数的功能。
![](/d/20211018/d192ca0a504c5f21a28b18a98e5130d0.gif)
#数据筛选AND
SELECT * FROM data1 WHERE city='shanghai' AND age>30;
![](/d/20211018/dbb9d74a627908454146bc9276c9185e.gif)
#数据筛选IN
SELECT * FROM data1 WHERE city IN ('shanghai','beijing');
![](/d/20211018/a5181f4b976acedb293849ce995d5186.gif)
#数据筛选OR
SELECT * FROM data1 WHERE city='shanghai' OR age>30;
![](/d/20211018/7b0be67d4dd0a3f0d301d498b7e41db4.gif)
#数据筛选(不等于)
SELECT * FROM data1 WHERE city !='beijing';
![](/d/20211018/f4cce7e5e8434692de5b192c7f498f45.gif)
#数据筛选like(模糊筛选)
SELECT * FROM data1 WHERE city LIKE 'bei%';
![](/d/20211018/d4f3cc8741775f8848a74f0131e3bfd8.gif)
#筛选后计数 countif
SELECT COUNT(id) AS id_count FROM data1 WHERE city='shanghai'AND age>30;
![](/d/20211018/38502f14a4f0c5e683b661f1a5a88032.gif)
#筛选后求和 sumtif
SELECT SUM(price) AS price FROM data1 WHERE city='beijing' AND age30;
![](/d/20211018/5511330a3fb6306624d5e3feb1141577.gif)
#筛选后求均值 averageif
SELECT AVG(price) AS avg_price FROM data1 WHERE city !='beijing';
![](/d/20211018/7fe30e2b1f0ae72b237bbde7a1319812.gif)
7,数据分类汇总及透视
第七部分是对数据进行分类汇总,Excel中使用分类汇总和数据透视可以按特定维度对数据进行汇总,mysql中使用的主要函数是GROUP BY和CASE WHEN。下面分别介绍这两个函数的使用方法。
分类汇总
Excel的数据目录下提供了“分类汇总”功能,可以按指定的字段和汇总方式对数据表进行汇总。mysql中通过GROUP BY完成相应的操作,并可以支持多级分类汇总。
![](/d/20211018/c76af5775baf2692d966a13321a62a2c.gif)
GROUP BY是进行分类汇总的函数,使用方法很简单,制定要分组的列名称就可以,也可以同时制定多个列名称,GROUP BY按列名称出现的顺序进行分组。同时要制定分组后的汇总方式,常见的是计数和求和两种。
#单列分类汇总
SELECT city,COUNT(id) AS id_count FROM data1 GROUP BY city ORDER BY id_count;
![](/d/20211018/34f606b6ca22424f0b4d4f108536927a.gif)
#多列分类汇总
SELECT city,colour,ROUND(SUM(price),2) AS id_count FROM data1 GROUP BY city,colour;
![](/d/20211018/2d7f3c1d88d76b598b75c10c53173390.gif)
数据透视
Excel中的插入目录下提供“数据透视表”功能对数据表按特定维度进行汇总。mysql中没有直接提供数据透视表功能。但通过CASE WHEN函数实现同样的效果。
![](/d/20211018/d0ecbfc213ecd6c8b0bc14773b6d273b.gif)
数据透视表也是常用的一种数据分类汇总方式,并且功能上比GROUP BY要强大一些。下面的代码中设定city为行字段,colour为列字段,price为值字段,计算price金额。
#查看原始数据表
SELECT * FROM data1;
![](/d/20211018/1abdc0738bc1e30d62d0b95a16ce380e.gif)
#使用CASE WHEN进行数据透视
CREATE VIEW data_Items AS (
SELECT
data1.city,
CASE WHEN colour = "A" THEN price END AS A,
CASE WHEN colour = "B" THEN price END AS B,
CASE WHEN colour = "C" THEN price END AS C,
CASE WHEN colour = "F" THEN price END AS F
FROM data1
);
#查看结果
SELECT * FROM data_Items;
![](/d/20211018/8a001e0c34d45fcdca48a9c305c3152e.gif)
#对字段进行求和汇总
CREATE VIEW data1_Extended_Pivot AS (
SELECT
city,
SUM(A) AS A,
SUM(B) AS B,
SUM(C) AS C,
SUM(F) AS F
FROM data_Items
GROUP BY city
);
#查看结果
SELECT * FROM data1_Extended_Pivot;
![](/d/20211018/75954b5b3eb24c90626dc57afbfd00ad.gif)
#对空值进行处理
CREATE VIEW data1_Extended_Pivot_Pretty AS (
SELECT
city,
COALESCE(A, 0) AS A,
COALESCE(B, 0) AS B,
COALESCE(C, 0) AS C,
COALESCE(F, 0) AS F
FROM data1_Extended_Pivot
);
#查看数据透视结果
SELECT * FROM data1_Extended_Pivot_Pretty;
![](/d/20211018/b3eee8d490a5f930e7f12d0538735590.gif)
您可能感兴趣的文章:- PHP导入Excel到MySQL的方法
- Excel数据导入Mysql数据库的实现代码
- 使用phpexcel类实现excel导入mysql数据库功能(实例代码)
- PHP导出MySQL数据到Excel文件(fputcsv)
- php 将excel导入mysql
- Mysql中文乱码以及导出为sql语句和Excel问题解决方法[图文]