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