学习SQL之前请先安装好数据库和客户端(Navicat 或者 DBeaver):
SQL是数据库的查询语言,语法结构简单,相信本文会让你从入门到熟练。
掌握SQL后,不论你是产品经理、运营人员或者数据分析师,都会让你分析的能力边界无限拓展。
以下的语句都在SequelPro的Query页面运行,其他操作页面不会有太大差异。标点符号必须为英文,这是新人很容易犯的错误。
SQL最小化的查询结构如下:
select column from table
table是我们的表名,column是我们想要查询的字段/列,column可以用 * 代替,指代全部字段,意为从table表查询所有数据。
where 是基础查询语法,用于条件判断。
select * from DataAnalyst
where city = '上海'
上图是最简化的查询语句,将所有城市为上海的职位数据过滤出来。我们也可以用 and 进行多条件判断。
select * from DataAnalyst
where city = '上海' and positionName = '数据分析师'
or 语句则是或的关系
select * from DataAnalyst
where city = '上海' or positionName = '数据分析师'
查找城市为上海,或者职位名称是数据分析师的数据,它们是并集。
当我们涉及到非常复杂的与或逻辑判断,应该怎么办?比如即满足条件AB,又要满足条件C,或者是满足条件DE。此时需要用括号明确逻辑判断的优先级。
select * from DataAnalyst
where (city = '上海' and positionName = '数据分析师')
or (city = '北京' and positionName = '数据产品经理')
这条语句的含义是查找出上海的数据分析师或者是北京的产品经理。当有括号时,会优先进行括号内的判断,当有多个括号时,对最内层括号先进行判断,然后依次往外。
接下来的问题来了,当我们要查询多个条件,比如北京上海广州深圳南京这些城市,难道一个个用and关联起来?这太麻烦了,我们可以使用 in 。
select * from DataAnalyst
where city in ('北京','上海','广州','深圳','南京')
当我们遇到字段数据类型是数值时,也可以使用符号> 、>=、< 、<=、!= 进行逻辑判断,!= 指的是不等于,等价于 <> 。
select * from DataAnalyst
where companyId >= 10000
上例是筛选出公司ID >= 10000的职位,为数值时,不需要像字符串一样加引号。
当我们需要取区间数值时,使用 between and
select * from DataAnalyst
where companyId between 10000 and 20000
between and 包括数值两端的边界,等同于 companyId >=10000 and companyId <= 20000。
如果要模糊查找,能用like。
select * from DataAnalyst
where positionName like '%数据分析%'
语句的含义是在positionName列查找包含「数据分析」字段的数据,%代表的是通配符,含义是无所谓「数据分析」前面后面是什么内容。如果是 ‘数据分析%’ ,则代表字段必须以数据分析开头,无所谓后面是什么。
除了上面所讲,还有一个常用的语法是not,代表逻辑的逆转,常见not in、not like、not null等。
接下来我们学习group by,它是数据分析中常见的语法,目的是将数据按组/维度划分。类似于Excel中的数据透视表,我们以city为例。
select * from DataAnalyst
group by city
它将城市划分成几组,通过group by 可以快速的浏览数据有哪些城市。我们看一下它的高阶用法。
select city,count(1) from DataAnalyst
group by city
上述语句,使用count函数,统计计数了每个城市拥有的职位数量。括号里面的1代表以第一列为计数标准。这里出现新的问题,当我们遇到重复数据怎么办?在DataAnalyst 这张表中,北京职位包含重复的职位ID,我们需要去重。
select city,count(distinct positionId) from DataAnalyst
group by city
北京的数据一下子少了2000,多余的重复值被排除在外。distinct 是去重函数,distinct positionId 会只计算唯一的positionId个数。日常工作中,活跃用户数、文章UV,都是用distinct 计算获得,这是唯一标示符ID的重要作用。
除了count,还有max,min,sum,avg等函数,也叫做聚合函数。用法和Excel没什么区别。
当我们在group by 添加多个字段,它将以多维的形式进行数据聚合。
select city,workYear,count(distinct positionId) from DataAnalyst
group by city,workYear
这就是数据分析师常用的多维分析法,通过group by 切分不同的维度进行对比,在不利用BI的情况下,通过SQL进行快速数据分析。
接下来学习逻辑判断,SQL也有if函数,和Excel的用法一摸一样,通过它我们能进行复杂的运算。比如我想统计各个城市中有多少数据分析职位,其中,电商领域的职位有多少,在其中的占比?
industryField是公司的行业领域,虽然我们能用where like 计算出有几个电商的数据分析师,但是占比的计算会比较麻烦,此时可以用if。
select if(industryField like '%电子商务%',1,0) from DataAnalyst
上面的公式利用if判断出哪些是电商行业的数据分析师,哪些不是。if函数中间的字段代表为true时返回的值,不过因为包含重复数据,我们需要将其改成positionId。图片中第二个count我漏加distinct了。之后,用它与group by 组合就能达成目的了。
select city,
count(distinct positionId),
count(distinct if(industryField like '%电子商务%',positionId,null))
from DataAnalyst
group by city
第一列数字是职位总数,第二列是电商领域的职位数,相除就是占比。记住,count是不论0还是1都会纳入计数,所以第三个参数需要写成null,代表不是电商的职位就排除在计算之外。
接下来是新的问题,如果我想找出各个城市,数据分析师岗位数量在500以上的城市有哪些,应该怎么计算?有两种方法,第一种,是使用having语句,它对聚合后的数据结果进行过滤。
select city,count(distinct positionId) from DataAnalyst
group by city having count(distinct positionId) >= 500
第二种,是利用嵌套子查询。
我们将第一次查询获得的城市职位数的结果,看作一张新的表,利用as 将它命名为t1( table1 的简写),将职位数命名为一个新的字段counts。然后外面再套一层select 过滤出counts >=500。
这种查询方式就叫嵌套子查询,使用场景比较广泛,where 后面也能跟子查询。
很多时候,数据是凌乱的,我们希望结果能够呈现一定的顺序,这时候就用到order by语句。
select city,count(distinct positionId) as counts from DataAnalyst
group by city
order by counts
看,数据就按照统计结果升序排列,如果需要降序,则是order by counts desc,后面加一个desc就好了。如果是多个字段,按逗号分隔即可。
我们再来熟悉SQL的常用函数,首先是时间。因为我们的练习数据中没有时间,首先用now创建出一个时间字段。
select now()
直接执行它,就能获得当前的系统时间,精确到秒。其实select不一定后面要跟from。
select date(now())
它代表的是获得当前日期,week函数获得当前第几周,month函数获得当前第几个月。其余还包括,quarter,year,day,hour,minute。
时间函数也包含各种参数,比如week,因为中西方计算第几天是不一样的,西方把周日算作一周中的第一天,而我们习惯周一。
select week(now(),0)
除了以上的日期表达,也可以使用dayofyear、weekofyear 的形式计算。它和上面的部分函数等价。
怎么对时间进行加减法呢?这时候靠date_add函数出马。
select date_add(date(now()) ,interval 1 day)
我们可以改变1为负数,达到减法的目的,也能更改day为week、year等,进行其他时间间隔的运算。如果是求两个时间的间隔,则是datediff(date1,date2)或者timediff(time1,time2)。
时间函数的运用比较灵活,没有特殊限定,网络上的文档和教程也不少,可以深入学习。
最后是数据清洗类的函数。
select left(salary,1) from DataAnalyst
MySQL支持left、right、mid等函数,这里又和Excel一样。我们通过salary计算数据分析师的工资吧(这一步骤,在曾经的文章中已经用Excel和BI多次讲解,所以我就不多赘述了,只讲过程,不熟悉的同学可以看历史内容)。
首先利用locate函数查找第一个k所在的位置。
select locate("k",salary),salary from DataAnalyst
然后使用left函数截取薪水的下限。
select left(salary,locate("k",salary)-1),salary from DataAnalyst
为了获得薪水的上限,要用substr函数,或者mid,两者等价。
substr(字符串,从哪里开始截,截取的长度)
薪水上限的开始位置是「-」位置往后推一位。截取长度是整个字符串减去「-」所在位置,刚好是后半段我们需要的内容,不过这个内容是包含「K」的,所以最后结果还得再减去1。
这里不了解不要紧,可以将计算过程分步骤运行。基本上,了解了上面写法的含义,文本清洗这块就没有问题了(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
尼玛,知乎的编辑器对齐不方便啊……
上面语句,我们用了文本清洗、子查询嵌套、分组聚合、排序等多种用法,属于较复杂的查询。重复数据的问题,因为我是复制了一份北京数据,数量刚好乘二,对平均数没有影响,感兴趣的朋友可以再加一步清洗掉它。
下面是三道思考题:
- 查询出哪家公司招聘的岗位数最多;
- 查询出O2O、电子商务、互联网金融这三个行业,哪个行业的平均薪资最高;
- 查询出各城市的最高薪水Top3是哪家公司哪个岗位。
做完上面的题目,你已经神功初成,数据分析的SQL意见没有大问题了。更复杂的查询,也无非是嵌套更多的内容,本质思路是一样的。
我们知道,数据库由多张表组成,表与表之间可以实现关联。
上图就是一个简单的关联模型:
Students.addressId = Address.id
Students.id = Scores.studentId
Scores.courseId = Courses.id
那么,如何在SQL查询语句中将两个表联接起来?我们将运用最重要的语法Join。
select * from Students
join Address on Students.addressId = Address.id
上面语句,join将Students和Address两表关联,关联需要一个或多个字段作为联接桥梁。例子中的桥梁就是addressid,我们使用on语句,将Students表的addressId字段和Address的id字段匹配。
这里需要注意的是,因为字段可能重名,所以一旦使用了Join,字段前应该加上表名,如Students.addressId和Address.id ,这种用法是为了字段的唯一性,否则遇到重名,系统不知道使用哪个字段,就会报错。
select * from Students as s
join Address as a on s.addressId = a.id
上图是更优雅的写法,将表命名为一个缩略的别名,避免了语句过于冗余。不要使用拼音做别名,不是好习惯。
Join语法有很多不同的变形,Left Join,Outer Join等,新人很容易混淆。这个我们可以用数学中的交集和并集掌握。
上图很清晰地解释了各Join语法。
Inner Join最常见,叫做内联接,可以缩写成Join,找的是两张表共同拥有的字段。
Left Join叫做左联接,以左表(join符号前的那张表)为主,返回所有的行。如果右表有共同字段,则一并返回,如果没有,则为空。
我们以W3School上的数据为例:
select Persons.LastName, Persons.FirstName, Orders.OrderNo
from Persons
left join Orders on Persons.Id_P=Orders.Id_P
order by Persons.LastName
于是输出结果为:
结果集中,Bush那一行的OrderNo为空,就是因为Id_P无法匹配上,返回了Null。如果改成Inner join,则不会返回整个Bush所在行。这是Inner Join和Left Join的区别,也是面试中经常会问到的题目。
Right Join和Left Join没有区别,A Left Join B 等价于 B Right Join A。
Full Join叫做全联接,也叫做Full Outer Join,意思是不管有的没的,只要存在,就返回。
还是以之前的例子演示,下面是Full Join:
最后两行就是所谓的「不管有的没的,只要存在字符串,就返回」的结果,它们Id_P并没有匹配上,但还是给出了返回,只是为空字段不同。
这三者的关系,我们可以理解为:A Full Join B = A Left Join B + A Right Join B – A Inner Join B,这就是数学上的集合运算,虽然SQL的表并不能加减法。如果还一知半解,看最上面的Join示例图,用面积的角度看也明白了。
通过上面的例子,我们已经掌握了Join的主流语法,其他无非是变种。比如加约束条件 where XX is null,这里的XX可以是结果为空的字段。拿上文Left Join的例子演示:
select Persons.LastName, Persons.FirstName, Orders.OrderNo
from Persons
left join Orders
on Persons.Id_P=Orders.Id_P
where Orders.Id_P is Null
最终返回的结果就是Bush这一行。
当我们有多个字段要匹配时,on后面可以通过 and 进行多项关联。
select * from A
join B on A.name = B.name and A.phone = B.phone
上图就是一个简单的适用场景,将用户姓名和手机号进行多项关联。它也可以加入其他的条件判断。
select * from A
join B on A.name = B.name and A.phone = B.phone and B.sex = '男'
我们再加一个and,将B表的用户性别限定为男。这种用法等价于where B.sex = ‘男’。当数据量大到一定程度,通过这种约束条件,能优化查询性能。
到这里,SQL的常用语法已经讲解的差不多了,我们进行实战吧。http://leetcode.com网站是知名的算法竞赛题,去上面刷SQL吧。
注册完后进入http://leetcode.com/problemset/database页面。那里有几道MySQL题目。因为时间关系,我只讲解Join相关,大家有兴趣可以刷其他题,都不难的。SQLZoo也能刷,就是页面丑了点,所以我十分感动地拒绝了它。
我们从Easy开始,选择题目Combine Two Tables。
红色字符是表名,第一列是字段名,第二列是数据类型。题目希望我们通过两张表输出:FirstName, LastName, City, State四个字段。
单纯的Left Join就能完成了。记住噢,答案需要完全一致,也就是说最终的结果必须是四个字段,不能多不能少,顺序也不能乱,大小写要严格。这一题大家自己做吧。通过后会有个绿色的Accepted提示。
接下来选择Medium难度的Department Highest Salary。
这里有两张表,员工表和部门表,我们希望找出各个部门的最高薪水。
部门信息单独为一张表,首先我们需要Join关联起来,将部门分组求出最大值:
select d.Id, #这是部门ID
d.Name as Name, #这是部门名字
max(e.Salary) as Salary #这是最高薪水
from Department d
join Employee e
on e.DepartmentId = d.Id
group by d.Id
上述的查询语句找出了最高薪水的部门,我们是否能直接使用其作为答案?不能。这里有一个逻辑的小陷阱,当最高薪水非单个时,使用max会只保留第一个,而不是列举所有,所以我们需要更复杂的查询。
因为已经有了各部门最高薪水的数据,可以将它作为一张新表,用最高薪水关联雇员表,获得我们最终的答案。
上面就是最终解法(#是解释给你们看的,中文会报错的),当然解法应该不是唯一的,大家有兴趣可以继续研究。
最终,我们选Hard模式的Department Top Three Salaries。
范例数据没有一丁点变化,它需要我们求出各部门薪水前三的数据。如果最高薪水只有两个,则输出两个。
上图是给的范例结果。
排名前三的数据,我们可以使用order by 降序排列出来,然后通过limit 限定为3,但是新的问题是:既要各部门前三,也存在排名并列的情况。此时order by就无能为力了。
如果是SQL Server或者Oracle,我们可以使用row_number分组排序函数,但是MySQL没有,其中的一种思路是利用set语法设置变量,间接应用row_number。我们还能使用另外一种思路。
select * from Employee as e
where (
select count(distinct e1.Salary)
from Employee e1
where e1.Salary > e.Salary
and e1.DepartmentId = e.DepartmentId
) < 3
上述的例子巧妙地借用了子查询。在where语句中,我们用子表e1与父表(外表)e进行比对。SQL是允许子查询的表和父查询的表进行运算的。
e1.DepartmentId = e.DepartmentId作为条件约束,避免跨部门。e1.Salary > e.Salary则是逻辑判断,通过count函数,逐行计算出e表中有多少薪水比e1的薪水低。
因为e1表和e表实际上是等价的。所以返回的count(distinct e1.Salary) 代表e1表有中多少薪水比e表的高,上图的例子,答案是2(90000和85000比它高)。如果是0,则代表e表中该行薪水最高(没有比它高的),1代表第二高,2代表第三高。于是便过滤出Top 3的薪水。最后通过join计算出结果。
在实际查询过程中,不建议大家使用这种运算方式,因为运算效率不会快。其实换我,我更可能group by后导出结果用Excel处理。
到这里,大家对Join已经有一个大概的了解了吧。真实的数据查询场景中,Join会用到很多,业务复杂用五六个Join也是常态,如果算上各类逻辑处理,SQL代码行数可以破百。这时候,考验的就是熟练度了。
SQL只要多加训练,并不是一门很难掌握的语言。除了技巧,还要看你对业务表的熟悉程度,一般公司发展大了,百来张表很正常,各类业务逻辑各种Join,各字段的含义,这是同样要花费时间的苦功夫。
希望大家对SQL已经有一个初步的掌握了。SQL学好了,以后应用大数据的Hive和SparkSQL也是轻而易举的。
——————
欢迎关注我的公众号:tracykanc