SQL面试:知识点与技巧

【写在前面】
作为一个无推广的小博主,之前的文章收到了很多读者的热爱,在此多谢大家的支持。最近发现国内剽窃现象很严重啊……如果您对我的文章有兴趣,想要转载请先经过我的同意,要不然打洗你!

最近面试了一些Data Engineer和Data Scientist的求职者,考了一些SQL的问题。(没错有些时候还是要用SQL的。)近两年学术界和业界都很浮躁,很多人这tensor那reinforcement的不离嘴,却把最基础的SQL忘得干净。所以本文总结了一下SQL的常见考点和常用函数。笔者在工作中SQL用得很少,多用Hive等NoSQL,但是其实都是万变不离其宗。面试官考的还是普通的SQL。

本文仅供学习交流使用,不用于商业目的,转载须注明出处。

有些公司(比如Facebook)会考又老又不好用的MySQL。所以本文就以最不好用的MySQL为例。个人觉得MySQL最讨厌之处在于每一个derived table(中间产生的临时表)都要有一个alias,还有就是没有window function……

本文写得是一般面试考SQL逃不出的关键点(标题部分有【高频】为面试容易考到的知识点——根据北美面试与被面试的经验),而不是基础SQL教程。刚毕业的学生往往没有接触过SQL,应聘数据类的岗位往往会担心会不会考SQL。我建议还是自己学习一下再去面试,一是因为在校生没有工作经验,简历和面试内容很单薄,二是SQL是面试中最容易又最逃不过的一个环节了。由此看来学习SQL投入少、产出高,何乐而不为。

如果你连subquery(子查询)、join(表联合)、having还不知道,那么建议先去看一些基础的入门教程。以前我一般就推荐Teach Yourself SQL in 21 Days(《SQL21日自学通》)——这本书前8天的内容能涵盖大部分的面试内容,看起来非常快。但是后来我觉得这本书里写到的知识点还是太少了,而且有些知识陈旧了。所以现在我一般推荐这些免费学习SQL的网站:
The SQL Tutorial to Data Analysis
SQL Course
Practical SQL Interview Questions and Answers
如果在国内打不开这些网站,或者阅读英文吃力的话,还是建议看《SQL21日自学通》。


SQL 中的子句【必考】

子句和JOIN是必考的,但是说由于表的联合相关的学习资料实在是太多了,都快说吐了,在此就不赘述了。一般面试中,SELECT之后应该是FROM,在每次输入SELECT语句时FROM也应该输入,之后按顺序是:WHERE、GROUP BY、HAVING、ORDER BY。其余的子句包括UNION、UNION ALL、INTERSECT和MINUS。下面来讲讲WHERE、GROUP BY、HAVING、ORDER BY、UNION、UNION ALL、INTERSECT和MINUS的用法。

1)WHERE
使用WHERE子句是选定返回数据的条件,可以和表的联合一起使用,除非你确实是想对表中的所有记录进行联合,否则一定不要忘记使用WHERE子句。

WHERE有时候和从属运算IN或BETWEEN一起使用:
SELECT * FROM people WHERE state IN ('CA','NY');
SELECT * FROM inventory WHERE prince BETWEEN 50 AND 100;请注意BETWEEN操作将包括边界值(50和100)

WHERE也可以和LIKE表达式一起用:
在LIKE表达式中,%是一种通配符,表示可能的模糊信息。如果想查找在某一确定的位置上有字符的数据时,可以使用另一个通配符——下划线:
SELECT * FROM people WHERE firstname LIKE '_o%' 返回firstname中第二个字符为o的数据类似的还有STARTING WITH:STARTING WITH子句附加于WHERE子句上,它的作用与LIKE(%)相似。

2)ORDER BY
查询输出的结果按一定的排序规则来显示,ORDER BY可以使用多个字段,在ORDER BY后边的DESC表示用降序排列来代替默认的升序排列。如:SELECT * FROM customers ORDER BY consumption  # DESC
假如你已经知道了你想要进行排序的列是表中的第一列的话,那么你可以用ORDER BY 1 来代替输入列的名字。

3)GROUP BY
SQL无法把正常的列和汇总函数结合在一起,这时就需要GROUP BY子句,它可以对 SELECT 的结果进行分组后在应用汇总函数。当要求分组结果返回多个数值时不能在SELECT子句中使用除分组列以外的列,这将会导致错误的返回值,但是你可以使用在SELECT中未列出的列进行分组。无论在什么情况下进行分组,SELECT语句中出现的字段只能是在GROUP BY中出现过的才可以。

4)HAVING
对需要进行分组的数据进行限制,汇总函数不能用在 WHERE 子句中,而是要用HAVING 子句 。HAVING子句允许你将汇总函数作为条件。

5)UNION与UNION ALL合并
UNION将返回两个查询的结果并去除其中的重复部分,UNION ALL与UNION一样对表进行了合并,但是它不去掉重复的记录。

6)INTERSECT相交
返回两个表中共有的行。

7)MINUS相减
返回的记录是存在于第一个表中但不存在于第二个表中的记录。如果解释器不支持一般用table_name1 t2 LEFT OUTER JOIN table_name2 t2 WHERE t2.col_name IS NULL;


返回前几行【高频】

MySQL的LIMIT+数字和TOP子句是等价的,并非所有的数据库系统都支持 TOP 子句。所以我们来简单看一下limit+offset的用法。以下这两句都合乎语法,但是有区别:

select * from table_name limit 3,1;                # 跳过前3条数据,从数据库中第4条开始查询,取一条数据,即第4条数据
select * from table_name limit 3 offset 1;      # 从数据库中的第2条数据开始查询3条数据,即第2条到第4条

记住这两句,再加上order by column_name (desc) 就能应付“消费第二多的客户”、“点击量第5到20名”之类的问题的。


条件语句【中频】

条件语句考得并不多,但是如果很久不用MySQL就容易忘记。MySQL里常用的条件语句是Case。Case语句分为两种:简单Case函数和Case搜索函数。

- 简单Case函数:
CASE gender WHEN '0' THEN 'male' WHEN '1' THEN 'female' ELSE 'others' END
- Case搜索函数:
CASE WHEN  age < 18 THEN '未成年人' WHEN age < 60 THEN '成年人' ELSE '老年人' END

Case语句只返回第一个符合条件的结果,剩下的条件会被自动忽略,比如上例中一个数据的age为16,那么它就在第一个case中被返回,不会进入第二个when中进行判断,因此返回'未成年人'而不是'成年人'。


关于空值【高频】

SQL中的空值是NULL,空值是不能用等号来比较的,而是要用IS NULL或者IS NOT NULL来判断值是否为空值。

面试的时候往往需要在输出结果中对空值进行处理,这时候最好用的就是IFNULL函数和ISNULL函数了。IFNULL是当SQL查询某个字段为空的时候,查询结果中设置其值为默认值。ISNULL使用指定的替换值替换 NULL:
ISNULL (检查的对象, 如果为空值替换的值)

可惜,MySQL中ISNULL只是用来判断是否为空,不能实现替换功能,所以用IFNULL代替,语法和上面的ISNULL一样。


关于Window Function【中频】

面试的时候通常是用的MySQL,MySQL本身是不支持Window Function的(一般翻译为统计分析函数)。如果面试官不让你用window function却要写出cumulative sum(累积和)的话,那就是纯心恶心人了,这样的公司不去也罢。现在大部分的数据库语言都支持window function,尽管用便是。

除了COUNT/SUM/...+ (col_name) OVER (PARTITION BY col_name2 ORDER BY col_name3)之外,常见的window function还有RANK等。在这里着重讲一下RANK()、DENSE_RANK()、ROW_NUMBER()三者排序的不同:

RANK()返回的是不持续的编号,例如100, 101, 101, 102返回的编号将是1,2,2,4;
DENSE_RANK()返回的是持续的编号,例如100, 101, 101, 102返回的编号是1,2,2,3;
ROW_NUMBER()返回的是持续不重复的编号,例如100, 101, 101, 102返回的编号将是1,2,3,4;

可见RANK()和DENSE_RANK()排序的差异就是序列存在并列的情况下。


相关子查询【中频】

相关子查询可以接受外部的引用从而得到一些令人惊奇的结果。
Select * From orders o
Where '三轮车' = (Select type From inventory i Where i.SeriesNum = o.SeriesNum)
个人觉得这样的join很神奇。

WHERE与IN有些类似。IN只相当于多个等号的作用,而ANY、SOME、ALL则可以使用其他的比较运算符如大于或小于。ALL关键字的作用在于子查询中的所有结果均满足条件时它才会返回TRUE,因此常与不等号连用。如SELECT * FROM table_name1 WHERE name != ALL (SELECT name FROM table_name2 WHERE name = 'XXX')


随机抽样【低频】

有时候面试会考到如何在SQL表中随机抽样,这时候就要用到RAND()函数。

通常被面试者的第一反应是:SELECT * FROM table ORDER BY RAND() LIMIT 10000,这样来抽取一万个样本。但是如果数据量很大,上述的做法太慢了:它对于每一行都用了RAND()函数,这一步复杂度是O(n);然后再排序,这一步的复杂度O(nlogn)。

所以可以改用这个方法:SELECT * FROM table WHERE RAND() <= .3,这样可以抽出约30%的数据,然后再用TOP或者LIMIT子句。


操作数据【低频】

在下文的数据操作命令之后,数据会出现在表中,但是在使用COMMIT语句之前它并不会真正生效。事务处理工作可以由COMMIT确认或只是简单地放弃。如果在使用INSERT或者UPDATE语句之后发现了错误,可以使用ROLLBACK语句来取消更新操作。

1)INSERT VALUES语句
INSERT INTO table_name (col1, col2, ...) VALUES (value1, value2, ...)
如果要在表中插入唯一值:
IF NOT EXISTS (SELECT * FROM table_name WHERE col1 = value1) INSERT INTO table_name VALUES (value1, value2, ...);此处视解释器不同语法可能有不同

2)INSERT SELECT语句
INSERT INTO table_name (col1, col2, ...) SELECT col1, col2, ... FROM table_name WHERE search_condition
它是将一个SELECT语句的输出结果再输入到另一个表格中去。

3)UPDATE语句
UPDATE table_name SET col1 = value1 [, col2 = value2] ...
WHERE search_condition
例如UPDATE people SET age = 90, gender = 'female' WHERE type= '老奶奶';
下面的拓展对于当给定表需要更新的内容源自于其它多个表的时候(不是标准语法,视数据库支持):
UPDATE table_name SET col1 = value1 [, col2 = value2] ...
FROM table_list WHERE search_condition

4)DELETE语句
DELETE FROM table_name WHERE search_condition
DELETE语句只会删除记录,不会删除表,如果要删除表需使用DROP TABLE命令。


创建和操作表【低频】

这个部分是最不容易被面到的内容了,但是听朋友说面试中国的公司时还是会考这个知识点:

1)CREATE DATABASE语句
CREATE DATABASE database_name

2)CREATE TABLE语句
CREATE TABLE table_name (col_name1 datatype [NOT NULL], col_name2 datatype [NOT NULL], ...)

3)ALTER TABLE语句
ALTER TABLE语句可以在表创建以后修改它的结构:
ALTER TABLE table_name <ADD col_name1 datatype; | MODIFY col_name2 datatype;>


SQL函数

1)汇总函数(与GROUP BY连用):COUNT、SUM、AVG、MAX、MIN、STDDEV、VARIANCE
COUNT、MAX、MIN既可以处理数值也可以处理字符(字符中最大的是小写的z)。因为大写的字母代码比小写的小,所以大写的字符总是排在小写字符的前面。

2)日期与时间函数
ADD_MONTHS:将给定的日期增加一个月
LAST_DAY:指定月份的最后一天
MONTHS_BETWEEN:计算在给定的两个日期中有多少个月
NEW_TIME(datetime1, c1, c2):给出时间datetime1在c1时区对应c2时区的日期和时间
NEXT_DAY:求指定日期的下一个日期
SYSDATE:系统的日期和时间,也可以用CURRENT_TIMESTAMP()或CURRENT_DATE()

3)数学函数
ABS:给定数字的绝对值
CEIL:与给定参数相等或比给定参数大的最小整数
FLOOR:与给定参数相等或比给定参数小的最大整数
COS、COSH、SIN、SINH、TAN、TANH、EXP、LN、LOG三角函数和指数计算
MOD(A, B):取模运算,A与B相除后的余数,同A%B,将返回一个除法的余数部分,如:SELECT NUMERATOR%DENOMINATOR REMAINDER FROM table_name。
POWER:幂
SQRT:平方根
SIGN:求符函数,如果参数的值为负数,那么 SIGN 返回-1;如果参数的值为正数,那么 SIGN 返回 1;如果参数为零,那么 SIGN 也返回零。

4)字符函数
CHR:与所给数值参数等当的字符,返回的字符取决于数据库所依赖的字符集
CONCAT同||:字符串连接符号,比如CONCAT(firstname, lastname)或者 firstname || lastname
INITCAP:将第一个字母变为大写,此外其它的字母则转换成小写
LOWER:转换为全部小写字母
UPPER:全部转换成大写字母
LPAD与RPAD:左填充与右填充,第一个参数是需要处理的字符串,第二个参数是需要将字符串扩充的宽度,第三个参数表示加宽部分用什么字符来做填补
LTRIM与RTRIM:左剪除与右剪除,剪除所指定的字符
REPLACE:替换字符
SUBSTR:将目标字符串的一部份输出
TRANSLATE(目标字符串,源字符串,目的字符串):在目标字符串与源字符串中均出现的字符将会被替换成对应的目的字符串的字符
INSTR:对某个字符串判断其是否含有指定的字符,其语法为:INSTR(sourceString, destString, start, appearPosition),需要知道在一个字符串中满足特定的内容的位置
LENGTH:返回指定字符串的长度

5)转换函数
TO_CHAR:将一个数字转换为字符型
TO_NUMBER:将字符串型数字转换为数值型

6)其它函数
GREATEST与LEAST:返回几个表达式中最大的和最小的
USER:返回当前使用数据库的用户的名字

以上就是我总结的一些SQL常见的考点,现在面试中除了SQL的技术部分,也注重business sense,比如根据数据让你算出conversion rate(转换率)、click-through rate(点击率)、ROI(投资回报率)等等。

这些抽象的知识点要结合具体的例子一起学习才更有效,但是我已经敲不动了……所以我们下次再见!ヾ( ̄ ̄)Bye~Bye~

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 151,688评论 1 330
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 64,559评论 1 273
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 101,749评论 0 226
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 42,581评论 0 191
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 50,741评论 3 271
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 39,684评论 1 192
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 31,122评论 2 292
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 29,847评论 0 182
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 33,441评论 0 228
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 29,939评论 2 232
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 31,333评论 1 242
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 27,783评论 2 236
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 32,275评论 3 220
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 25,830评论 0 8
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,444评论 0 180
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 34,553评论 2 249
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 34,618评论 2 249

推荐阅读更多精彩内容