收藏本站 收藏本站
积木网首页 - 软件测试 - 常用手册 - 站长工具 - 技术社区
首页 > 数据库 > MySQL > 正文

首页 - PHP - 数据库 - 操作系统 - 游戏开发 - JS - Android - MySql - Redis - MongoDB - Win8 - Shell编程 - DOS命令 - jQuery - CSS样式 - Python - Perl

Access - Oracle - DB2 - SQLServer - MsSql2008 - MsSql2005 - Sqlite - PostgreSQL - node.js - extjs - JavaScript vbs - Powershell - Ruby

如何使用mysql完成excel中的数据生成

Excel是数据分析中最常用的工具,本篇文章通过mysql与excel的功能对比介绍如何使用mysql完成excel中的数据生成,数据清洗,预处理,以及最常见的数据分类,数据筛选,分类汇总,以及数据透视等操作。本篇文章我们介绍第5,6,7部分内容,数据提取,数据筛选以及数据汇总及透视。

查看图片 5,数据提取

第五部分是数据提取,也是数据分析中最常见的一个工作。下面介绍每一种函数的使用方法。

按列提取数据

#按列提取 SELECT city FROM data1;

查看图片

按行提取数据

#按行提取
SELECT * FROM data1 WHERE city='beijing';

查看图片

按位置提取数据

#按位置提取
SELECT * FROM data1 LIMIT 2,5;

查看图片

按条件提取数据

#按条件提取并计算
SELECT AVG(price) FROM data1 WHERE city='beijing' AND age<25;

查看图片

6,数据筛选

第六部分为数据筛选,使用与,或,非三个条件配合大于,小于和等于对数据进行筛选,并进行计数和求和。与excel中的筛选功能和countifs和sumifs功能相似。

按条件筛选(与,或,非)

Excel数据目录下提供了“筛选”功能,用于对数据表按不同的条件进行筛选。mysql中使用WHERE完成筛选操作,配合sum和count函数还能实现excel中sumif和countif函数的功能。

查看图片

#数据筛选AND
SELECT * FROM data1 WHERE city='shanghai' AND age>30;

查看图片

#数据筛选IN
SELECT * FROM data1 WHERE city IN ('shanghai','beijing');

查看图片

#数据筛选OR
SELECT * FROM data1 WHERE city='shanghai' OR age>30;

查看图片

#数据筛选(不等于)
SELECT * FROM data1 WHERE city !='beijing';

查看图片

#数据筛选like(模糊筛选)
SELECT * FROM data1 WHERE city LIKE 'bei%';

查看图片

#筛选后计数 countif
SELECT COUNT(id) AS id_count FROM data1 WHERE city='shanghai'AND age>30;

查看图片

#筛选后求和 sumtif
SELECT SUM(price) AS price FROM data1 WHERE city='beijing' AND age<30;

查看图片

#筛选后求均值 averageif
SELECT AVG(price) AS avg_price FROM data1 WHERE city !='beijing';

查看图片

7,数据分类汇总及透视

第七部分是对数据进行分类汇总,Excel中使用分类汇总和数据透视可以按特定维度对数据进行汇总,mysql中使用的主要函数是GROUP BY和CASE WHEN。下面分别介绍这两个函数的使用方法。

分类汇总

Excel的数据目录下提供了“分类汇总”功能,可以按指定的字段和汇总方式对数据表进行汇总。mysql中通过GROUP BY完成相应的操作,并可以支持多级分类汇总。

查看图片

GROUP BY是进行分类汇总的函数,使用方法很简单,制定要分组的列名称就可以,也可以同时制定多个列名称,GROUP BY按列名称出现的顺序进行分组。同时要制定分组后的汇总方式,常见的是计数和求和两种。

#单列分类汇总
SELECT city,COUNT(id) AS id_count FROM data1 GROUP BY city ORDER BY id_count;

查看图片

#多列分类汇总
SELECT city,colour,ROUND(SUM(price),2) AS id_count FROM data1 GROUP BY city,colour;

查看图片

数据透视

Excel中的插入目录下提供“数据透视表”功能对数据表按特定维度进行汇总。mysql中没有直接提供数据透视表功能。但通过CASE WHEN函数实现同样的效果。

查看图片

数据透视表也是常用的一种数据分类汇总方式,并且功能上比GROUP BY要强大一些。下面的代码中设定city为行字段,colour为列字段,price为值字段,计算price金额。

#查看原始数据表
SELECT * FROM data1;

查看图片

#使用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;

查看图片

#对字段进行求和汇总
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;

查看图片

#对空值进行处理
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;

查看图片

MySQL中报错:Can’t find file: ‘./mysql/plugin.frm’的解决方法
发现问题最近在工作中发现了一个问题,这个问题就是MySQL的磁盘满了,将数据库目录data移动到/data3目录,/etc/my.cnf里面也修改了相应的datadir目录,权

Mac OS系统下mysql 5.7.20安装教程图文详解
MacOSX下TAR.GZ方式安装MySQL5.7与MySQL5.6相比,5.7版本在安装时有两处不同:1:初始化方式改变,从scripts/mysql_install_db--user=mysql初始化方式变成了bin/mysqld--initializ

Mysql 5.7.20压缩版下载和安装简易教程
一、下载地址:http://dev.mysql.com/downloads/mysql/http://www.gimoo.net/softs/451120.html1.进入官网下载,显示的应该是最新版本,选择第二个(mysql5.7.20-winx64.zip)2.下

本周排行

更新排行

强悍的草根IT技术社区,这里应该有您想要的!
Copyright © 2010 Gimoo.Net. All Rights Rreserved  京ICP备05050695号