【MySQL】2.MySQL实际操作
目录
一、数据分析基本流程
二、获取数据后的代码操作
(1)探索数据,查看定义
(2)筛选有用的字段
(3)建新表(查询+建表+插值 三合一)
注意:原始数据一定要保留,创建一个新的表
①从一个表中复制部分数据到一个新表进行操作
②从多个表中复制部分数据 汇总到一个新表进行操作
(4)排序(order by)
(5)聚合函数
(6)判断是否为空
三、字符串处理
(1)在SQL中,select = print
(2)数据脱敏
(3)模糊查找
(4)去除重复值
四、设置权限
五、其他操作
一、数据分析基本流程
1,明确目的;
2,获取数据;
-- python 爬取
3,数据预处理;3值1转换 (真正工作时有问题就是删)
- 缺失值
- 异常值
- 重复值
- 转换
4,数据分析
- 定维度(分析方向)
- 定指标(比、率)
5,可视化
6,撰写报告
注:Navicat快捷键
ctrl+/ 注释sql语句 #暂不适用
ctrl+shift +/ 解除注释
ctrl+r 运行查询窗口的sql语句
ctrl+shift+r 只运行选中的sql语句
二、获取数据后的代码操作
(1)探索数据,查看定义
DESC jobs;
SELECT * FROM jobs LIMIT 10;
(2)筛选有用的字段
--例如筛选出学历,工作经验,工资,城市,公司名称
SELECT positionId,positionName,companyFullName,companySize,industryField,financeStage,createTime,city,salary,workYear,jobNature,education
FROMjobs;
(3)建新表(查询+建表+插值 三合一)
注意:原始数据一定要保留,创建一个新的表
①从一个表中复制部分数据到一个新表进行操作
#从一个表中复制部分数据到一个新表进行操作
CREATE TABLE jobs1 AS
SELECTpositionId AS id,positionName AS `naem`, --也可以同时重命名列名companyFullName,companySize,industryField,financeStage,createTime,city,salary,workYear,jobNature,education
FROMjobs;
②从多个表中复制部分数据 汇总到一个新表进行操作
假设你有三个表 table1
、table2
和 table3
,每个表都有相同的结构(即相同的列)。你想将这些表中的某些列的数据汇总到一个新表 new_table
中。
第一步:创建新表
首先,创建一个新的表来存储汇总的数据:
CREATE TABLE new_table (id INT,name VARCHAR(255),value DECIMAL(10,2)-- 其他列根据需要添加
);
第二步:从多个表中选择数据并插入到新表中
使用 INSERT INTO ... SELECT ...
语句从每个表中选择你需要的列并插入到新表中。
INSERT INTO new_table (id, name, value)
SELECT id, name, value FROM table1
UNION ALL
SELECT id, name, value FROM table2
UNION ALL
SELECT id, name, value FROM table3;
(4)排序(order by)
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;
/*ASC增序;DESC降序;还可以按多个列排序*/SELECT name, height, (height * 2) AS double_height
FROM stu
ORDER BY double_height DESC;
/*还可以按表达式排序*/
(5)聚合函数
5个聚合函数
(1)count 数个数
(2)max 最大
(3)min 最小
(4)avg 平均值
(5)sum 求和-- 聚合函数的2个特点,竖着计算,自动跳空;
-- 判断是否为空的方法(1) 【用count函数将每一个字段都数一遍】
-- 数据库默认 NULL '' None 是空数据。
(6)判断是否为空
处理数据时,每个字段都要进行非空判断
另外,根据数据业务进行判断,例如positionId 只能是数字,positionId 不是数字的都是垃圾数据
-- 判断是否为空的方法(1)
SELECT COUNT(*),COUNT(positionid),COUNT(positionName),COUNT(companyFullName),COUNT(salary)
FROMjobs1;-- 判断是否为空的方法(2)
-- 数据库默认 NULL '' None 是这三种是空数据。
SELECTcount(*)
FROMjobs1
WHERE city IS NULL /*第1种IS NULL*/
-- 数据情况不是用肉眼来看的,是要通过程序来看的;SELECTcount(*)
FROMjobs1
WHERE city = 'None' /*第2种None*/SELECTcount(*)
FROMjobs1
WHERE city = '' /*第3种空串*/--总结起来就是:
SELECT count(*) FROM jobs1 WHERE city IS NULL OR city = 'None' OR city = '';
三、字符串处理
主要字符串操作:
字符串长度:select Length('abc');#存储长度; char_length()字符长度
左取:left
右取:right
截取子串:substr(str FROM pos FOR len)
SUBSTR('abcdefg' FROM 2 FOR 3) #从第2个截取3个
替换:replace
拼接:CONCAT(str1,str2,...)
按分隔符索引截取:substring_index(字符串,分隔符,count)
去除左右空格:Trim
大小写转换:Upper、lower
(1)在SQL中,select = print
SELECT 'Hello,world' AS HI;
SELECT 1+1 AS result;SELECT VERSION();
SELECT CURRENT_DATE;
(2)数据脱敏
例如:如果名字少于两个字,不用管;如果两个字,后一个字变*;如果三个字,中间一个字变*;如果超过字,除开头结尾,中间变*
SELECT`positionName` AS 脱敏前,CHAR_LENGTH(`positionName`) AS 长度,(CASEWHEN CHAR_LENGTH(`positionName`) = 2 THEN CONCAT(LEFT(`positionName`,1),'*')WHEN CHAR_LENGTH(`positionName`) = 3 THEN CONCAT(LEFT(`positionName`,1),'*',RIGHT(`positionName`,1))WHEN CHAR_LENGTH(`positionName`) > 3 THEN CONCAT(LEFT(`positionName`,1),REPEAT('*',CHAR_LENGTH(`positionName`)-2),RIGHT(`positionName`,1))ELSE`positionName`END
) AS 脱敏后
FROMjobs111
此处使用了case...when...结构
①简单搜索(基于字段值)
语法格式:
CASE column_name
WHEN value1 THEN result1
WHEN value2 THEN result2
...
[ELSE default_value]
END
②搜索函数(基于表达式)
语法格式:
SELECT id, name,CASE WHEN age > 18 THEN '成年人'WHEN age BETWEEN 6 AND 18 THEN '未成年人'ELSE '婴幼儿'END AS 'age_group'
FROM people;
(3)模糊查找
SELECT*
FROM
jobs111
WHERE positionName LIKE '%分析_';--通配符%匹配任意个
--通配符_匹配一个
(4)去除重复值
SELECTDISTINCT *
FROM
jobs111--只通过id判断并去除重复值
SELECT *
FROM
jobs1
WHEREpositionId IN (SELECTDISTINCT positionId
FROMjobs1);
四、设置权限
五、其他操作
字符串类——截取,大小写转换,拼接,替换
数字计算——加减乘除,ABS,...
日期时间——转化时间戳,获取当前时间,日期时间格式化,截取
逻辑判断——and not or
流程控制——if case when