之前的一篇文章讲述了用Excel进行数据分析的过程,如今我们对比一下在Excel和MySQL中相同过程有着什么样的变化。
原始数据就是猴子老师第三关的数据 根据猴子老师第三关的步骤制作 初次尝试有很多不足 欢迎讨论指正。
1.首先将招聘网站的原始数据导入MySQL。因为中文的列名可能会对后面的数据有影响,所以我们将列名改为英文。
因为表格当中没有唯一识别的主键,所以我们单独加上了一列做主键。
也可以在MySQL中新建表格将相应的列名对应。
导入表格。
Excel选择csv格式。
导入数据,选择编码,这样表格中中文不会出现乱码。
此时一定要将原来的Excel表格关闭。然后不用改其他的东西,一直选择下一步。
确认主键
点击下一步,然后点击开始。
成功之后关闭,即可看见表格已经生成。
双击表格可以看见数据都传进MySQL中
在查询中编辑sql语句即可。
按照Excel的步骤进行
1.提出问题:
1)在哪些城市找到数据分师工作的机会比较大?
2)数据分师的薪水如何?
3)根据工作经验的不同,薪酬是怎样变化的?
2.清洗数据
第一步选择子集
使用ALTER TABLE <表名> DROP COLUMN <列名>将不用的子集删除。
选中红框圈住的语句&#xff0c;点击运行下的运行已选择
注意&#xff0c;并列情况下逗号&#xff0c;不可少。
再次运行上面的语句&#xff0c;可以发现下面的列已经删除了其他的部分。
第二步重新命名列名已经不用了&#xff0c;直接进行第三步删除重复值。
我们看一下我们导入的数据的数量
使用count函数&#xff0c;计算主键的个数&#xff0c;为6874个
而真正去掉重复项之后&#xff0c;应该是5031个
但是DISTINCT只能在SELECT中将重复值去掉&#xff0c;不能彻底去掉重复值&#xff0c;所以要使用
DELETE FROM <表名>
WHERE<条件>&#xff1b;
来删除行。或者以后在用到计算数据时在列名的前面加DISTINCT 用到就加
DELETE FROM &#96;招聘网站数据-原始数据 1&#96;
WHERE
job_ID in
(SELECT job_ID FROM (SELECT job_ID
FROM &#96;招聘网站数据-原始数据 1&#96;
GROUP BY job_ID
HAVING COUNT(job_ID)>1)a)
AND zhujian NOT in
(SELECT zhujian FROM(SELECT MIN(zhujian) AS zhujian
FROM &#96;招聘网站数据-原始数据 1&#96;
GROUP BY job_ID
HAVING COUNT(job_ID)>1)b);
删除重复值保留一个值的方法是删除job_ID为重复ID&#xff0c;但是主键不是最小值的行。
注意&#xff1a;DELETE FROM &#96;招聘网站数据-原始数据 1&#96;
WHERE
job_ID in
(SELECT job_ID
FROM &#96;招聘网站数据-原始数据 1&#96;
GROUP BY job_ID
HAVING COUNT(job_ID)>1)
AND zhujian NOT in
(SELECT MIN(zhujian) AS zhujian
FROM &#96;招聘网站数据-原始数据 1&#96;
GROUP BY job_ID
HAVING COUNT(job_ID)>1);
会报错 [Err] 1093 - You can&#39;t specify target table 表格名称 for update in FROM clause
原因是&#xff1a;更新数据时使用了查询&#xff0c;而查询的数据又做了更新的条件&#xff0c;mysql不支持这种方式。oracel和msserver都支持这种方式。 怎么规避这个问题&#xff1f; 再加一层封装。
且注意注意select min(zhujian) 后面要有as zhujian
第四步&#xff0c;缺失值处理
可知city列有缺失值
可知3,5行有缺失值
使用update<表名>
set<列名>&#61;<表达式>
where<条件>;
第五步&#xff0c;一致化处理
将薪水变成最高工资&#xff0c;最低工资和平均工资
利用locate函数&#xff1a;
LOCATE(substr,str)
返回子串 substr 在字符串 str 中第一次出现的位置。如果子串 substr 在 str 中不存在&#xff0c;返回值为 0&#xff1a;
此函数相当于Excel中的find函数
substring函数
字符串截取&#xff1a;substring(str from 起始位置 for 终止位置 )
LENGTH函数 是计算字符串的长度&#xff0c;等同于Excel中的LEN函数
但是 得到的是select的值 无法长久的保存 想要长久的保存要更新列&#xff0c;然后update数据
更新列采用 alter table<表名> add column<列的定义>
update 来更新数据
update<表名>
set<列名>&#61;<表达式>&#xff1b;
平均薪水
已上这个的平均薪水还是15 可能是将后面的当成字符串了
但是在update时会报错
再次更新列 然后update数据
数据透视表&#xff1a;根据自己的问题绘制相应的要展示的结果
但是不知道为什么不限 和应届毕业生不正确 等以后明白了再来更新
发现之前新建的值都是字符型 这里改成integer型
使用 use information_schema;
select * from columns where column_name&#61;&#39;字段名&#39; ;
来查看列的定义&#xff08;注意是两个&#xff1b;&#xff09;
vlookup
至此 基本的Excel功能在MySQL中体现了