- select:查找
SELECT * FROM data.datanalyst
SELECT * FROM data.datanalyst
limit 10
SELECT city FROM data.datanalyst
SELECT city,positionId FROM data.datanalyst
SELECT city FROM data.datanalyst
order by companyId
SELECT city FROM data.datanalyst
order by companyId desc
- 按条件查找-单条件
- where companyId = 4154
- where companyId between 4154 and 10000
- where company <> "百度"
where company != "百度"
where city not in ("上海","长沙")
- where city in ("上海","长沙")
- where secondType like '后端%'
where companyId = 4154
and city in ("上海","长沙")
or city not in ("上海","长沙")
where companyId = 4154
and (city in ("上海","长沙")
or city not in ("上海","长沙"))
2. export:将结果输出
3. group by:数据透视表
SELECT city FROM data.datanalyst
group by city
SELECT city.count(positionId) FROM data.datanalyst
group by city
group by city,education
按照默认字段去计数
count(1)不包含空值
count(*)包含空值
SELECT city.count(positionId),count(1),count(*) FROM data.datanalyst
SELECT city.count(positionId),count(distinct companyId) FROM data.datanalyst
group by city
SELECT city.count(positionId) FROM data.datanalyst
(两者输出结果有细微区别,不影响使用)
- where industryfield like ’%电子商务%'
group by city
having count(positionId) >= 100
- having count(if(industryfield like ’%电子商务%',1,null)) >= 100
或直接不同城市包含职位数量、包含电子商务职位变化
SELECT
city,
count(1),
coun(if(industryfield like ’%电子商务%',industryfield,null))
FROM data.datanalyst
group by city
count(1) as total
注意total这个别名不能直接在下面使用
4. 拆分单元格
SELECT
left(salary,locate('k',salary)-1),
left(right(salary,length(salary)-locate('-',salary)),length(salary)-locate('-',salary)-1),
substr(salary,locate('-',salary)+1,length(salary)-locate('-',salary)-1),
salary FROM data.datanalyst
5. 子查询
select (bottom+top)/2,salary from(
SELECT
left(salary,locate('k',salary)-1),
left(right(salary,length(salary)-locate('-',salary)),length(salary)-locate('-',salary)-1),
substr(salary,locate('-',salary)+1,length(salary)-locate('-',salary)-1),
salary FROM data.datanalyst ) as t
select
case
when (bottom+top)/2 <= 10 then '0-10'
when (bottom+top)/2 <= 20 then '10-20'
when (bottom+top)/2 <= 30 then '20-30'
else '30+'
end,
,salary from(
SELECT
left(salary,locate('k',salary)-1),
left(right(salary,length(salary)-locate('-',salary)),length(salary)-locate('-',salary)-1),
substr(salary,locate('-',salary)+1,length(salary)-locate('-',salary)-1),
salary FROM data.datanalyst ) as t
select *
from data.datanalyst
where city in (
select city from data.datanalyst
group by city having count(positionId) >= 100
)
6. 跨表查询
SELECT * FROM data.datanalyst
join data.company as c on datanalyst.companyId = company.companyId
inner join = join
left join 类似于vlookup 不能匹配返回空值(可统计空值的数量)
其他的了解即可
7. 命令符加载大批量数据
文件位置可以直接拖拽进去,改一下斜杠即可 \改成/
load data local infile '文件位置' into table data.userinfo
fields terminated by ',';
8. 时间函数
函数名称与Excel相似
date(paidTime)
date_format(paidTime,'%Y-%m')
date_add(paidTime,interval 1 day)
9. 连接PowerBI
不可以跨数据库连接
尽量在获取数据的时候进行SQL查询,这样性能比较好