SQL应知应会

If you want your life to be a magnificent story, then begin by realising that you are the author.

本文就Udacity数据分析入门课程中的SQL入门(P1阶段)和SQL进阶(P3阶段)的知识点进行总结。SQL的主要功能不外乎增、删、改、查四个,对于数据分析师来说,只需要掌握就可以了。(因为增删改往往超出了数据分析师的职能范围)

注意:本文是总结性质的,只能提供复习或者速查的功能,讲解得不会很详细,若想学习,还是要在教室内逐章学习。

SQL简介

SQL是Structured Query Language的简写,也就是结构化查询语言。SQL 最受欢迎的功能是与数据库交互。

使用传统关系数据库与 SQL 交互有一些主要优点。最明显的 5 个优点是:

  • SQL 很容易理解。
  • 传统的数据库允许我们直接访问数据。
  • 传统的数据库可使我们审核和复制数据。
  • SQL 是一个可一次分析多个表的很好工具。
  • 相对于 Google Analytics 等仪表板工具,SQL 可使我们分析更复杂的问题。

为什么企业喜欢使用数据库

  1. 只有输入了需要输入的数据,以及只有某些用户能够将数据输入数据库,才能保证数据的完整性
  2. 可以快速访问数据 - SQL 可使我们从数据库中快速获取结果。 可以优化代码,快速获取结果。
  3. 可以很容易共享数据 - 多个人可以访问存储在数据库中的数据,所有访问数据库的用户获得的数据都是一样。

SQL 与 NoSQL

你可能听说过 NoSQL,它表示 Not only SQL(不仅仅是 SQL)。使用 NoSQL 的数据库时,你编写的数据交互代码会与本节课所介绍的方式有所不同。NoSQL 更适用于基于网络数据的环境,而不太适用于我们现在要介绍的基于电子表格的数据分析。最常用的 NoSQL 语言之一是 MongoDB

SQL入门

SQL书写规则

  • SQL语句不区分大小写,因此SELECT与select甚至是SeLect的效果是相同的,但是要对命令和变量进行区分,所以默认命令需要大写,其他内容如变量等则需要小写
  • 表和变量名中不要出现空格,可使用下划线_替代。
  • 查询语句中,使用单一空格隔开命令和变量
  • 为提高代码的可移植性,请在查询语句结尾添加一个分号

SQL中的注释

  • 行内注释

    使用两个连字符-,添加注释。

    1
    2
    SELECT col_name -- 这是一条注释
    FROM table_name;
  • 多行注释

    多行注释以/*起始,以*/结尾。

    1
    2
    3
    4
    /*SELECT col_name 
    FROM table_name;*/
    SELECT col_2
    FROM table_name;

检索数据(SELECT FROM LIMIT )

检索数据主要用的语句为:SELECT

检索单列

1
2
SELECT col_name
FROM table_name;

从table_name表中检索col_name列。

检索多列

1
2
SELECT col_1,col_2,col_3
FROM table_name;

从table_name表中检索col_1,col_2和col_3列。

检索所有列

1
2
SELECT *
FROM table_name;

使用通配符*,返回table_name表中的所有列;

检索某列中不同的值

1
2
SELECT DISTINCT col_1
FROM table_name;

检索col_1中具有唯一性的行,即唯一值。

限制检索的结果

使用LIMIT语句可以限制返回的行数。

1
2
3
SELECT col_1
FROM table_name
LIMIT 10;

返回前10行(即第0-第9行)。

也可以添加OFFSET语句,设置返回数据的起始行:

1
2
3
SELECT col_1
FROM table_name
LIMIT 10 OFFSET 5;

从第五行之后,返回十行数据(即第5-第14行)。

排序检索数据(ORDER BY)

  • ORDER BY 语句用于根据指定的单列或多列对结果集进行排序。

  • ORDER BY 语句默认按照升序对记录进行排序。(从小到大,从a到z)

  • 如果您希望按照降序对记录进行排序,可以使用 DESC 关键字。

  • 在指定一条ORDER BY子句时,应该保证它是SELECT语句中的最后一条子句。

按列排序

1
2
3
SELECT col_name
FROM table_name
ORDER BY col_name;

返回的数据会按照col_name列进行升序排序,这里col_name可以是单列也可以是多列,当然也可以使用非检索的列进行排序。

降序排序

1
2
3
SELECT col_1,col_2
FROM table_name
ORDER BY col_2 DESC,col_3;

返回的数据会按照col_2列降序,col_3列升序对col_1和col_2两列进行排序。

这里可以看出,DESC关键字的用法:只对跟在语句前面的变量有效。所以,想要对多列进行降序排序时,需要对每一列都指定DESC关键字。

过滤数据(WHERE)

  • WHERE子句应该在表名(即FROM子句)之后给出。

  • WHERE子句应在ORDER BY子句之前。

  • 在过滤条件中的value是区分大小写的。

使用方法

1
2
3
SELECT col_1
FROM table_1
WHERE col_1 运算符 value;

运算符

运算符 描述
= 等于
<> 不等于
> 大于
< 小于
>= 大于等于
<=< td> 小于等于
BETWEEN…AND… 在指定的两值之间
IS NULL 为NULL值
AND 逻辑运算符:与
OR 逻辑运算符:或
IN 制定条件范围筛选,可以简化OR的工作
NOT 逻辑运算符:非

注意:

  • SQL的版本不同,可能导致某些运算符不同(如不等于可以用!=表示),具体要查阅数据库文档。
  • 在同时输入AND和OR时,SQL会优先处理AND语句,你可以使用小括号来进行分组操作。

用通配符进行过滤(LIKE)

通配符是用来匹配值的一部分的特殊字符,跟在LIKE关键字后面进行数据过滤

通配符 描述
% 表示任何字符出现任意次数
_ 表示任何字符出现一次
[] 指定一个字符集,它必须匹配该位置的一个字符
^ 在[]中使用,表示否定

示例:

1
2
3
4
SELECT col_1
FROM table_1
WHERE col_1 LIKE '_[^JM]%'
ORDER BY col_1;

如上筛选出的是,第二个字符为非J或M的数据。

创建计算字段

其实就是在检索数据的同时进行计算,并使用关键字AS将结果保存为某一列。

  • 数值类型的计算
1
2
3
SELECT prod_id,quantity,item_price,quantity*item_price AS expanded_price
FROM orderitems
WHERE order_num = 200008;

输出:

1
2
3
4
prod_id         quantity       item_price      expanded_price
--------------------------------------------------------------
RGAN01 5 4.9900 24.9500
BR03 5 11.9900 59.9500

这里实现的就是使用quantity*item_price创建一个名为expanded_price的计算字段,也就是一个新列。

同样适用于计算的操作符有+(加),-(减)和/(除)。

  • 字符类型的拼接
1
2
3
SELECT RTRIM(col_name) + '('+RTRIM(col_country)+')' AS col_title
FROM table_name
ORDER BY col_name;

输出:

1
2
3
4
5
col_title
------------------------
Bear Emporium(USA)
Bears R Us(USA)
Jouets et ours(France)

这里实现的就是将col_name列与col_country列进行了拼接,新列的名字叫做col_title。

RTRIM()函数是去掉右边的所有空格,LTRIM()是去掉左边的所有空格,TRIM()是去掉两边的所有空格。

使用别名

在上一节中我们使用AS来为变量设置别名,你可能也见过如下所示的语句:

1
SELECT col1 + col2 AS total, col3

当然没有 AS 的语句也可以实现使用别名:

1
FROM tablename t1

以及

1
SELECT col1 + col2 total, col3

将col1+col2的结果设置名为total的列。

代码总结

语句 使用方法 其他详细信息
SELECT SELECT Col1, Col2, … 提供你需要的列
FROM FROM Table 提供列所在的表格
LIMIT LIMIT 10 限制返回的行数
ORDER BY ORDER BY Col 根据列命令表格。与 DESC 一起使用。
WHERE WHERE Col > 5 用于过滤结果的一个条件语句
LIKE WHERE Col LIKE ‘%me%’ 仅提取出列文本中具有 ‘me’ 的行
IN WHERE Col IN (‘Y’, ‘N’) 仅过滤行对应的列为 ‘Y’ 或 ‘N’
NOT WHERE Col NOT IN (‘Y’, “N’) NOT 经常与 LIKEIN 一起使用。
AND WHERE Col1 > 5 AND Col2 < 3 过滤两个或多个条件必须为真的行
OR WHERE Col1 > 5 OR Col2 < 3 过滤一个条件必须为真的行
BETWEEN WHERE Col BETWEEN 3 AND 5 一般情况下,语法比使用 AND 简单一些

SQL进阶

链接表

基本链接(JOIN)

SQL最强大的功能之一就是能在数据查询的执行中进行表的链接(JOIN)。

在关系数据库中,将数据分解为多个表能更有效地存储,更方便地处理,但这些数据储存在多个表中,怎样用一条SELECT语句就检索出数据呢?那就要使用链接。

创建链接的方式很简单,如下便是使用WHERE创建链接:

1
2
3
SELECT col_1,col_2,col_3
FROM table_1,table_2
WHERE table_1.id = table2.id;

如上,col_1和col_2属于table_1表中,col_3属于table_2表中,而这两个表使用相同的id列进行匹配。这种方法被称为等值链接,也就是内链接,我们可以使用如下的语句,更直观地实现内连接:

1
2
3
SELECT col_1,col_2,col_3
FROM table_1 INNER JOIN table_2
ON table_1.id = table2.id;

当然你也可以使用别名,简化输入,并且标明各列与表的隶属关系:

1
2
3
SELECT t1.col_1,t1.col_2,t2.col_3
FROM table_1 t1 INNER JOIN table_2 t2
ON t1.id = t2.id;

如上代码同样适用于左链接、右链接和外链接:

  • LEFT JOIN - 用于获取 FROM 中的表格中的所有行,即使它们不存在于 JOIN 语句中。

  • RIGHT JOIN - 用于获取 JOIN 中的表格中的所有行,即使它们不存在于 FROM 语句中。

  • FULL JOIN: 只要其中一个表中存在匹配,就返回行。

自链接

自链接经常用于对子查询的简化,如下示例:

假如要给Jim同一公司的所有顾客发送一封邮件,需要你先筛选出Jim的公司,然后再根据该公司筛选出所有的顾客。使用子查询的方式如下:

1
2
3
4
5
SELECT cust_id,cust_name,cust_contact
FROM customers
WHERE cust_name = (SELECT cust_name
FROM customers
WHERE cust_contact = 'Jim')

如果改为自链接的方式如下:

1
2
3
4
SELECT c1.cust_id,c1.cust_name,c1.cust_contact
FROM customers c1,customers c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_name = 'Jim';

结果是一样的,但是使用自链接的处理速度比子查询要快得多。

组合查询(UNION)

UNION 操作符用于合并两个或多个 SELECT 语句的结果集,使用方法也很简单,只要在多条SELECT语句中添加UNION关键字即可。

多数情况下,组合相同表的多个查询所完成的任务与具有多个WHERE子句的一个查询是一样的。

注意:UNION 内部的 SELECT 语句必须拥有相同数量的列,列也必须拥有相似的数据类型。而且UNION返回的结果只会选取不同的值(即唯一值)。

使用UNION的场合情况:

  • 在一个查询中从不同的表返回结果;
  • 对一个表执行多个查询返回结果。

示例:如下三个语句的结果是一致的。

  • 原始语句
1
2
3
4
5
6
7
8
9
-- 查询一
SELECT cust_name,cust_email
FROM customers
WHERE cust_state IN ('str1','str2');

--查询二
SELECT cust_name,cust_email
FROM customers
WHERE cust_name = 'str3';
  • 使用UNION链接
1
2
3
4
5
6
7
8
SELECT cust_name,cust_email
FROM customers
WHERE cust_state IN ('str1','str2')
UNION
SELECT cust_name,cust_email
FROM customers
WHERE cust_name = 'str3'
ORDER BY cust_name;

在最后添加了ORDER BY对所有SELECT语句进行排序,这里只是为了示例在使用UNION时如何进行排序。

  • 使用WHERE
1
2
3
4
SELECT cust_name,cust_email
FROM customers
WHERE cust_state IN ('str1','str2')
OR cust_name = 'str3';

这里看起来使用UNION比WHERE更复杂,但对于较复杂的筛选条件,或者从多个表中检索数据时,使用UNION更简单一些。

  • UNION ALL 命令和 UNION 命令几乎是等效的,不过 UNION ALL 命令会列出所有的值。

SQL聚合

有时候我们只是需要获取数据的汇总信息,比如说行数啊、平均值啊这种,并不需要吧所有数据都检索出来,为此,SQL提供了专门的函数,这也是SQL最强大功能之一。

聚合函数

SQL的聚合函数如下所示:

函数 说明
AVG() 返回某列的均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列的和

使用示例:

1
2
SELECT AVG(col_1) AS avg_col_1
FROM table_1;

注意:聚合函数都会忽略列中的NULL值,但是COUNT(*)也就是统计全部数据的行数时,不会忽略NULL值。

聚合不同值

当添加DISTINCT参数时,就可以只对不同值(也就是某列中的唯一值)进行函数操作。

使用示例:

1
2
SELECT AVG(DISTINCT col_1) AS avg_col_1
FROM table_1;

数据分组

创建分组(GROUP BY)

前面的函数操作都是基于整个表去进行的,那如果想要依据某列中的不同类别(比如说不同品牌 不同性别等等)进行分类统计时,就要用到数据分组,在SQL中数据分组是使用GROUP BY子句建立的。

在使用GROUP BY时需要注意的几点:

  • GROUP BY子句可以包含任意数量的列,因而可以对分组进行多重嵌套,类似于Pandas中的多重索引;
  • GROUP BY子句必须出现在WHERE子句之后,ORDER BY之前。

使用示例:

1
2
3
SELECT col_1,COUNT(*) AS num_col
FROM table_1
GROUP BY col_1;

以上即可实现按col_1列中的不同类目进行行数统计。

过滤分组(HAVING)

在SQL入门中我们学过WHERE,它是对行数据进行筛选过滤的,那么,如果我想对创建的分组数据进行筛选过滤呢?这时候,你就要用到HAVING子句了,它与WHERE的操作符一致,只是换了关键字而已。

使用示例:

1
2
3
4
SELECT col_1,COUNT(*) AS num_col
FROM table_1
GROUP BY col_1
HAVING COUNT(*) >= 2;

这里我们就筛选出了具有两个以上类别的分组。

注意:使用HAVING时应该结合GROUP BY子句。

时间序列的处理(DATE)

在SQL中有一套专门的内置函数,用来处理时间序列,那就是DATE函数。

SQL Date 数据类型

先了解一下在不同的数据库中的时间序列的表示。(了解即可)

MySQL 使用下列数据类型在数据库中存储日期或日期/时间值:

  • DATE - 格式:YYYY-MM-DD
  • DATETIME - 格式:YYYY-MM-DD HH:MM:SS
  • TIMESTAMP - 格式:YYYY-MM-DD HH:MM:SS
  • YEAR - 格式:YYYY 或 YY

SQL Server 使用下列数据类型在数据库中存储日期或日期/时间值:

  • DATE - 格式:YYYY-MM-DD
  • DATETIME - 格式:YYYY-MM-DD HH:MM:SS
  • SMALLDATETIME - 格式:YYYY-MM-DD HH:MM:SS
  • TIMESTAMP - 格式:唯一的数字

DATE_TRUNC函数

DATE_TRUNC 使你能够将日期截取到日期时间列的特定部分。常见的截取依据包括日期月份年份

语法:

1
DATE_TRUNC('datepart', timestamp)

其中datepart即为你的截取依据,后面的timestamp类型可以参考上面的Date数据类型。

我总结了一份SQL的datepart速查表放在了下面。

使用示例:

1
2
3
4
5
SELECT DATE_TRUNC('y',col_date) col_year
FROM table_1
GROUP BY 1
ORDER BY 1 DESC
LIMIT 10;

如上,我们将col_date列按照年(’y’)进行了分组,并按由大至小的顺序排序,取前10组数据。

DATE_PART函数

DATE_PART 可以用来获取日期的特定部分,如获取日期2018-10-6的月份,只会获得一个结果6,这是它与DATE_TRUNC的最大区别。

语法:

1
DATE_PART ('datepart', date或timestamp)

其中datepart即为你的截取依据,后面的timestamp类型可以参考上面的Date数据类型。

使用示例:

1
2
3
SELECT DATE_PART('y',col_date) col_year
FROM table_1
GROUP BY 1;

如上,我们筛选了col_date列的年份,并依据它做了分组。

想了解更多DATE函数,可以戳SQL日期和时间函数参考

datepart总结

如下给了很多的缩写,只记住最简单的即可。

日期部分或时间部分 缩写
世纪 c、cent、cents
十年 dec、decs
y、yr、yrs
季度 qtr、qtrs
mon、mons
w,与 DATE_TRUNC一起使用时将返回离时间戳最近的一个星期一的日期。
一周中的日 ( DATE_PART支持) dayofweek、dow、dw、weekday 返回 0–6 的整数(星期日是0,星期六是6)。
一年中的日 ( DATE_PART支持) dayofyear、doy、dy、yearday
d
小时 h、hr、hrs
分钟 m、min、mins
s、sec、secs
毫秒 ms、msec、msecs、msecond、mseconds、millisec、millisecs、millisecon

CASE语句

CASE语句其实就相当于python中的if语句,是用来做条件的。

需要注意的几点:

  • CASE 语句始终位于 SELECT 条件中。
  • CASE 必须包含以下几个部分:WHEN、THEN 和 END。ELSE 是可选组成部分,用来包含不符合上述任一 CASE 条件的情况。
  • 你可以在 WHEN 和 THEN 之间使用任何条件运算符编写任何条件语句(例如 WHERE),包括使用 AND 和 OR 连接多个条件语句。
  • 你可以再次包含多个 WHEN 语句以及 ELSE 语句,以便处理任何未处理的条件。

使用示例:

1
2
3
4
SELECT account_id, CASE WHEN standard_qty = 0 OR standard_qty IS NULL THEN 0
ELSE standard_amt_usd/standard_qty END AS unit_price
FROM orders
LIMIT 10;

如上,我们使用CASE WHEN.(条件一).THEN.(条件一的结果).ELSE.(其他不符合条件一的结果).END语句,设立的两个条件,即当standard_qty为0或者不存在时我们返回0,当standard_qty不为0时进行计算,并储存为新列unit_price。

子查询与临时表格

我们之前所涉及到的都是从数据库表中检索数据的单条语句,但当我们想要检索的数据并不能直接从数据库表中获取,而是需要从筛选后的表格中再度去查询时,就要用到子查询和临时表格了。

子查询与临时表格所完成的任务是一致的,只不过子查询是通过嵌套查询完成,而另一种是通过WITH创建临时表格进行查询。

构建子查询

构建子查询十分简单,只需将被查询的语句放在小括号里,进行嵌套即可,但在使用时一定要注意格式要清晰。

使用示例:

1
2
3
4
5
6
7
SELECT *
FROM (SELECT DATE_TRUNC('day',occurred_at) AS day,channel, COUNT(*) AS events
FROM web_events
GROUP BY 1,2
ORDER BY 3 DESC) sub
GROUP BY channel
ORDER BY 2 DESC;

如上,我们创建了一个子查询,放在小括号里,并将其命名为sub。在子查询中也注意到了各个子句上下对齐,这样条例更清晰。

临时表格(WITH)

这种方法,就是使用WITH将子查询的部分创建为一个临时表格,然后再进行查询即可。

我们还是使用上面子查询的例子,这次用临时表格的形式实现:

1
2
3
4
5
6
7
8
9
10
WITH sub AS(
SELECT DATE_TRUNC('day',occurred_at) AS day,channel, COUNT(*) AS events
FROM web_events
GROUP BY 1,2
ORDER BY 3 DESC)

SELECT *
FROM sub
GROUP BY channel
ORDER BY 2 DESC;

如上,我们将被嵌套的子查询单独拎出来,用WITH创建了一个临时表格,再之后又使用SELECT根据该表格进行查询。

SQL数据清理

这一节主要针对数据清理讲解了几个SQL中的常用函数,一般来说,也都是用在筛选阶段,更详尽的数据清理还是要放在python中去进行。

字符串函数

  • LEFT、RIGHT、LENGTH

LEFT和RIGHT相当于是字符串截取,LEFT 是从左侧起点开始,从特定列中的每行获取一定数量的字符,而RIGHT是从右侧。

LENGTH就是获取字符串的长度,相当于python中的len()。

语法:

1
2
3
LEFT(phone_number, 3) -- 返回从左侧数,前三个字符
RIGHT(phone_number, 8)
LENGTH(phone_number)
  • POSITIONSTRPOSSUBSTR

这三个函数都是与位置相关的函数。

POSITIONSTRPOS 可以获取某一字符在字符串中的位置,这个位置是从左开始计数,最左侧第一个字符位置为1,但他俩的语法稍有不同。

SUBSTR可以筛选出指定位置后指定数量的字符。

语法:

1
2
3
POSITION(',' IN city_state)
STRPOS(city_state, ‘,’) --跟上面的语句等价
SUBSTR(city_state,4,5) -- 返回city_state字符串中,以第4个字符开头的5个字符。
  • 字符串拼接(CONCAT)

顾名思义,就是将两个字符串进行拼接。

语法:

1
2
3
CONCAT(first_name, ' ', last_name) -- 结果为:first_name last_name
--或者你也可以使用双竖线来实现上述任务
first_name || ' ' || last_name

更改数据格式

  • TO_DATE函数

TO_DATE函数可以将某列转为DATE格式,主要是将单独的月份或者年份等等转换为SQL可以读懂的DATE类型数据。

语法:

1
2
TO_DATE(col_name,'datepart') 
TO_DATE('02 Oct 2001', 'DD Mon YYYY');

这里是将col_name这列按照datepart转化为DATE类型的数据,datepart可以参考之前的总结。

  • CAST函数

CAST函数是SQL中进行数据类型转换的函数,但经常用于将字符串类型转换为时间类型。

语法:

1
2
3
CAST(date_column AS DATE)
-- 你也可以写成这样
date_column::DATE

这里是将date_column转换为DATE格式的数据,其他时间相关的数据类型与样式对照可以参考上面写过的SQL Date数据类型,确保你想转换的数据样式与数据类型对应。

缺失值的处理

之前有提到过如何筛选出缺失值,即使用WHERE加上IS NULL或者IS NOT NULL

那么如何对缺失值进行处理呢?(其实这里可以直接无视,筛选出来后在python中再进行处理)

SQL中提供了一个替换NULL值的函数COALESCE

使用示例:

1
2
COALESCE(col_1,0) -- 将col_1中的NULL值替换为0
COALESCE(col_2,'no DATA') -- 将col_2中的NULL值替换为no DATA

总结

好啦,至此课程中的所有SQL知识点已经总结完了,并且也给大家做了适当的补充,希望大家能够用得上。未来的数据分析师之路,还要继续加油呀!

附:SELECT子句顺序

下表中列出了全文中涉及到的子句,在进行使用时,应严格遵循下表中从上至下的顺序。

子句 说明 是否必须使用
SELECT 要返回的列或表达式
FROM 用于检索数据的表 仅在从表中选择数据时使用
JOIN…ON… 用于链接表 仅在需要链接表时使用
WHERE 过滤行数据
GROUP BY 分组数据 仅在按组计算时使用
HAVING 过滤分组
ORDER BY 对输出进行排序
LIMIT 限制输出的行数
Allen Jia wechat
扫一扫订阅我的公众号
听说你要请我喝杯咖啡?