严格来说,SQL并不是一门编程语言,只是一个取数工具,与它的原意(结构化查询语言)比较贴切。和很多初学者一样,我学习SQL最大的门槛并非这门语言本身的难易,而是缺乏一个科学有效的学习路径。 我尝试过看书(《Head First SQL》,《SQL必知必会》等系统性的书籍),也在一个月内准备并通过了数据库二级、三级的计算机等级考试,更看过形形色色的SQL题目,然而成效甚微。但是在我进入一家互联网公司实习后,每天都需要写大量的SQL且有大牛细心指导,我在短短几天内就能独立对接SQL需求。
在没有实习练手机会的情况下,如何在短时间快速上手SQL对于在校学生或者非技术人员都是相当重要的。
本篇文章的目的主要是帮助初学者在初步知晓SQL语句的情况下在短时间内系统入门SQL,从而解决80%的sql查询问题。
上篇介绍SQL的语法顺序和执行顺序的区别并仔细剖析SQL的执行顺序;中篇详细介绍条件子句、分组查询和排序的细节;下篇会介绍表的连接和其他常用关键字。
希望学完这三篇后能助你系统地入门SQL~
学生表student结构:
学生表
先看一个查询例子:查询表中所有学号小于8的男学生的学号和姓名:
select
sid,sname ##需要查询出来的字段
from student ##从哪张表中取数
where ssex=’男’ and sid<8 ##设置查询的条件,两个条件用and(和)/or(或)连接
暂时我们没有对字段做处理,如果你需要对选择出来的结果进行处理,需要使用函数和order by,再看一个例子:查询每个男性学生的学号、姓名和年龄,并按照学号降序排列。
select
sid,sname
,year(curdate())-year(sage) as age ##当前年份减去出生年份得到年龄
from student
where ssex=’男’
order by sid desc ##order by 按照字段排序,desc 表示降序
学生表student:
学生表
成绩表sc:
成绩表
通过join连接两张表:查询每个赵雷每门课的成绩。
select
sname,cid,score
from student
left join sc
on student.sid=sc.sid
where sc.sname=’赵雷’
为方便学习,我们仍然使用之前用到的学生表student和成绩表sc,为了模拟业务中复杂的查询任务,我们再引入课程表course和教师表teacher。 熟练使用SQL的前提一定是先了解你的数据库表,现在花点时间看看这四张表的字段信息(描述每个字段的意义)和数据样例(给出部分真实数据),关于业务中用到表的结构可以找数据小哥拿。
Student(SId,Sname,Sage,Ssex) SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
Course(CId,Cname,TId) CId 课程编号,Cname 课程名称,TId 教师编号
Teacher(TId,Tname) TId 教师编号,Tname 教师姓名
SC(SId,CId,score) SId 学生编号,CId 课程编号,score 分数
学生表
课程表
教师表
成绩表
本篇文章关于SQL语法的部分会讲到条件子句(where子句)、分组查询(group by子句和having子句)、结果呈现(order by和limit)和连接查询(left/right/inner join)。 看到这你可能会瞬间头大,但是由于SQL语言是有执行优先级的,这给了我们分块讲解的机会,私以为这也是SQL语言易学的重要原因。 为了解释清楚SQL语言的执行顺序和语法顺序,让我们先看看下面这个Hive单表查询的完整结构。
语法顺序
任何一个单表查询的SQL都可以分解成上述格式,实际上抽象化后的多表连接查询也可以分解成如上格式。从上到下是SQL的语法顺序(即你书写SQL的格式),而SQL真实的执行顺序如下:
执行顺序
举个简单的例子加深理解:SQL的语法顺序就像小说的插叙,而SQL真正的执行顺序就是小说的时间顺序。
上面的讲解可能让你一知半解,在正式介绍各部分SQL语法前我们先通过一个实例复习上面SQL的执行顺序。 例如,有这么一个业务查询任务:在限定学生表学号小于等于6的一批学生中,查询每门课的最高成绩(最高成绩低于70分的课程不显示),然后根据课程最高成绩降序排列取前两条记录。查询的SQL如下:
查询SQL
为方便阅读,下面列出被查询的成绩表sc和查询后的结果:
查询结果
where sid<=6 ##限制只查询学号小于等于6的学生成绩
查询结果
如果用过数据透视表的话应该比较容易理解分组查询的概念,分组查询一般和聚合函数一起实现,例如查看每个班的平均成绩、查看每个学生的最高成绩或者查看每个班的最低成绩等分组信息。
我们仍然用直观的数据变化来展示分组查询:
group by cid –按照课程分组查看每门课的聚合信息 max(score) –搭配group by子句使用的聚合函数,表示每门课的最高成绩 having max(score) >=70 –对分组后的结果筛选,选取最高成绩>=70的课程
查询结果
在group by分组后紧跟着我们会选择需要呈现的字段,为了方便讲解,其实分组查询中呈现的图片已经是select的结果了。
order by和limit都是为了修改最终呈现结果。order by首先执行,按照某个字段进行排序(desc 关键字表示降序),这部分和excel的排序很相似。最后我们使用limit来修改结果展示的条数。
查询结果
之前我们已经用一个例子介绍了SQL的语法顺序和执行顺序,想必现在你已经知道了一个完整的SQL包括条件子句(关键字where)、分组查询(关键字group by)、字段选择(关键字select)和结果呈现(关键字order by)。本部分我们旨在将实务中最常出现的详细情况进行说明,从而解决初学者80%的sql查询任务。
比较运算符包括=(等于),>=(大于等于),<=(小于等于),!=(不等于),>(大于),<(小于)。 例如:查询年龄sage小于30的学生
where sage < 30
between … and …为取值限定了一个范围。 例如:查询年龄大于等于10小于等于20的学生
where sage between 10 and 20
例如:插入年龄为10,20,30的学生
where sage in (10,20,30)
##in可以和not一起使用,表示不在这个区间的值
##where sage not in (10,20,30)
通过like关键字和正则表达式匹配,常用的通配符有%(任意个字符)和_(一个字符)。 例如:查询名字sname带“王”的学生
where sname like “%王%”
通过is null关键字判断值是否为空。 例如:查询姓名sname不为空的学生
where sname is not null
用and(两个条件同时满足)和or(两个条件满足一个即可) 例如:查询年龄sage小于20且性别ssex为男的学生where sage<20 and ssex=’男’
分组查询实现了类似excel中数据透视表的功能,可以帮助我们对数据进行分层汇总,而我们对分层后的数据进行统计的时候需要用到聚合函数(也就是平均值、求和、最大值和最小值等),最后我们对分层之后的数据筛选的时候需要用到having子句。
where子句是对原始表做筛选的 having子句是对分层汇总之后的结果做筛选的
回顾我们上一篇讲过的例子:在限定学生表学号小于等于6的一批学生中,查询每门课的最高成绩(最高成绩低于70分的课程不显示),然后根据课程最高成绩降序排列取前两条记录。 查询的SQL如下:
查询SQL
回顾一下执行顺序,首先我们用where子句对原始数据做了学号id需要小于等于6的限制。然后我们用group by和max(score)聚合函数实现了对课程进行分层,求出每门课的最高成绩,为了对聚合之后的结果作限制,我们用having子句只展示最高分数大于等于70的记录。 关于这个例子详细解释可以回顾上篇文章,下面我们详细介绍每个部分的常用语句。
group by不仅可以对一个字段进行分组,还能对多个字段进行分组。这和excel中的数据透视表一致。
聚合函数
和where子句一致,只需注意是对聚合后的结果作限制。
select比较灵活,我们不单单能选择原始数据表的字段,还能使用函数对字段进行计算,正如我们第一篇提到的,函数并不是重点,当你需要的时候百度或者问技术小哥就知道了。我们这里只简单介绍一下可以对字段进行计算。 例如:查询各学生的年龄(通过公式计算年龄)
字段选择
例如:查询学生id和年龄,并先按照学号sid降序,再按照年龄sage升序排列
查询SQL
我们前面已经介绍过通过等值连接join实现两个及两个以上表的查询需求,sql表连接包括内连接、外连接和交叉连接,我们通过一个例子简单介绍三种连接的异同。
现在有一张A表和B表,A表记录学生学号(sid)和对应的姓名(name),B表记录学生学号(sid)和对应的分数(score)。
待连接的表信息
内连接即通过对某个字段进行等值匹配从而将两个表联合起来,比方说我们需要获取两张表中同一个学号对应的姓名和成绩,使用的就是inner join,结果如下:
内连接结果
一般情况下,“A表左连接B表”的结果与“B表右连接A表”的结果相同,为了保证SQL代码的易阅读性,一般用左连接即可。
左连接指的是将左表作为基准表,保留表中的所有行,将右表根据某个字段进行等值匹配,如果找不到右表中匹配的行则显示为NULL。结果如下:
左连接与右连接
当然,还有全连接,在某些用途下也有用处,这里就不展开说了
没有连接条件的表连接将产生笛卡尔积,即连接结果行数=A表行数*B表行数,可以理解为两个表的记录两两配对产生的结果。结果如下:
交叉连接
列举一些在hive取数时常用的关键字。
根据字段的不同值进行不同的操作,存在大量的变形操作可以实现不同的功能,最简单的情形如下:
#sex字段为1和2,现在要转化为更为直观的文字形式
case sex
when '1' then '男'
when '2' then '女'
else '未知'
end as sex
#统计成绩单中及格同学的人数(单个学号可能出现多条记录)
count(distinct(if(score >= 60,sid,null)))
#获取男性学生的总成绩
sum(if(sex = '男', score, 0))
整理了一下思维导图,方便我自己看,顺便放到这里来
SQL思维导图