MySQL查询常用实例

作者：xxw9485
时间：2018/3/15
来源：如何七周成为数据分析师

---

这是以下练习题使用的数据

• 将所有城市为上海的职位数据过滤出来：

select * from DataAnalyst
where city = '上海'

• 查找城市为上海，或者职位名称是数据分析师的数据：

select * from DataAnalyst
where city = '上海' or positionName = '数据分析师'

• 查找出上海的数据分析师或者是北京的产品经理：

select * from DataAnalyst
where (city = '上海' and positionName = '数据分析师') or (city = '北京' and positionName = '数据产品经理')

• 查找北京上海广州深圳南京这些城市的数据：

select * from DataAnalyst
where  city in ('北京','上海','广州','深圳','南京')

• 筛选出公司ID >= 10000的职位：

select * from DataAnalyst
where companyId >= 10000

• 筛选出公司ID在10000至20000的职位：

select * from DataAnalyst
where companyId between 10000 and 20000

• 在positionName列查找包含「数据分析」字段的数据：

select * from DataAnalyst
where positionName like '%数据分析%'

• 使用count函数，统计计数了每个城市拥有的职位数量：

# 括号里面的1代表以第一列为计数标准
select city,count(1) from DataAnalyst
group by city

• 使用count函数，统计计数了每个城市拥有的不重复职位数量：

select city,count(distinct positionId) from DataAnalyst
group by city

• 统计计数了每个城市、不同workyear的不重复职位数量：

select city,workYear,count(distinct positionId) from DataAnalyst
group by city,workYear

• 利用if判断出哪些是电商行业的数据分析师，哪些不是：

select if(industryField like '%电子商务%',1,0) from DataAnalyst

• 统计各个城市中有多少数据分析职位，其中，电商领域的职位有多少：

select city,
          count(distinct positionId),
          count(if(industryField like '%电子商务%',positionId,null)) 
from DataAnalyst
group by city

• 找出各个城市，数据分析师岗位数量在500以上的城市有哪些：

# 1、使用having语句，它对聚合后的数据结果进行过滤
select city,count(distinct positionId) from DataAnalyst
group by city having count(distinct positionId) >= 500

# 2、利用嵌套子查询
select * from (
select city, count(distinct positionid) as counts from DataAnalyst group by city
) as t1 
where counts >=500

• 统计计数了每个城市拥有的不重复职位数量，并进行升序排序：

select city,count(distinct positionId) as counts from DataAnalyst
group by city
order by counts

• 用now创建出一个时间字段，获得当前的系统时间：

# 精确到秒
select now()
# 精确到天
select date(now())

• 对当前时间进行加减法运算：

# 当前日期+1
select date_add(date(now()) ,interval 1 day)
# 当前日期-1
select date_add(date(now()) ,interval -1 day)

• 提取salary字段值的左边起一个数字or字母：

select left(salary,1) from DataAnalyst

• 利用locate函数查找第一个k所在的位置：

select locate("k",salary),salary from DataAnalyst

• 使用left函数截取薪水的下限：

select left(salary,locate("k",salary)-1),salary from DataAnalyst

• 计算各岗位工资的上下限：

select left(salary,locate("k",salary)-1),
substr(salary, locate('-', salary)+1, length(salary)-locate('-', salary)-1),
salary from DataAnalyst
where salary not like '%以上%'

• 计算不同城市不同工作年限的平均薪资：

select city, workyear, avg((bottomsalary+topsalary)/2) as avgsalary from(
    select left(salary,locate("k",salary)-1) as bottomsalary,
    substr(salary, locate('-', salary)+1, length(salary)-locate('-', salary)-1) as topsalary,
    city, positionid, workyear
    from DataAnalyst
    where salary not like '%以上%') as t1
group by city, workyear
order by city, avgsalary

补充：

• 通过Students表的addressId字段和Address的id字段，将Students和Address两表关联：

select * from Students
join Address on Students.addressId = Address.id

• 将用户姓名和手机号进行多项关联：

select * from A
join B on A.name = B.name and A.phone = B.phone

Inner Join最常见，叫做内联接，可以缩写成Join，找的是两张表共同拥有的字段。
Left Join叫做左联接，以左表（join符号前的那张表）为主，返回所有的行。如果右表有共同字段，则一并返回，如果没有，则为空。
相互关系：
A Full Join B = A Left Join B + A Right Join B - A Inner Join B

进阶之路

可以到leetcode在线刷题。