【MySQL】入门语句学习

1. select：查找

• 查找表格内所有内容

SELECT * FROM data.datanalyst

• 查找表格内前10条

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：数据透视表

• 按city统计表格

SELECT city FROM data.datanalyst

group by city

• 按照Positionid计数

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

• 按照Positionid计数（去重）

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')

• 在时间上加1天

date_add(paidTime,interval 1 day)

9. 连接PowerBI

不可以跨数据库连接

尽量在获取数据的时候进行SQL查询，这样性能比较好