关系数据库表与Pandas数据框非常相似,每一行对应一个特定的对象,而列则用于存储该对象的不同特征。例如,一个网站可能在其数据库中有一个表,用于存储其用户信息(用户名,名字,姓氏等)。
ID 名字 姓 用户名 密码
1个 杰里米 约旦 密码 密码
2 约翰 雪 冬天来了 自我支撑
3 安德森 帕克 是的 breezylovejoy
当我们想从关系数据库中获取信息时,我们可以通过查询(询问)来实现。具体来说,我们使用结构化查询语言(SQL)与数据库对话并访问数据。甲架构定义信息如何被存储在表内,实质上描述什么类型的信息被存储在每一列。
SQL能够搜索,过滤和合并可能存储在多个表中的数据。在本文中,我将讨论用于数据分析的常见SQL命令。
快速链接供参考
基本SQL命令
SELECT/FROM:指定感兴趣的列和表
LIMIT:限制查询结果
ORDER BY:指定返回的订单结果
WHERE:使用比较或逻辑运算符过滤数据
LIKE:用于匹配相似的值,而不是严格相同的值
BETWEEN:仅选择指定范围内的行
IN:指定要包含的值列表
AND:选择满足两个指定条件的行
OR:选择满足指定条件之一的行
NOT:选择不满足指定条件的行
IS NULL:选择指定列中具有空值的行
中级SQL命令
聚合函数(基于列)
COUNT(column):计算指定列中的行数
SUM(column):将所有值添加到指定列中
MIN/MAX(column):返回指定列中的最低/最高值
AVG(column):计算指定列中的平均值
GROUP BY:将记录分成组
HAVING:用于使用比较或逻辑运算符过滤组
DISTINCT:仅返回唯一值,不重复
CASE (WHEN/THEN/ELSE/END):SQL的if / then逻辑
数据联接
JOIN (ON):合并来自多个表的数据
INNER JOIN :排除所有不匹配的记录
LEFT JOIN:排除在联接表中找到的不匹配记录
RIGHT JOIN:排除在原始表中找到的不匹配记录
FULL OUTER JOIN:返回每个表中匹配和不匹配的所有记录
UNION (ALL):合并多个查询的结果
SQL命令的操作顺序
SQL数据类型(外部链接)
基本SQL命令
每个SQL查询绝对需要两个命令,即想要的数据(SELECT)和该数据的存储位置(FROM)。使用该SELECT命令,您可以指定要检索的表的列,而FROM指定要从中检索数据的表。“ *”符号是“ all”的简写,因此,如果您想从上面的示例表中获取所有用户字段,则可以执行类似的查询SELECT * FROM users。
的AS关键字将创建别名为在检索到的数据的列。
- SELECT first_name AS "First Name"
- FROM users
- SELECT unread_count + read_count AS total_count
- FROM emails
- SELECT *
- FROM users
- LIMIT 100
您可以使用命令ORDER BY指定列以对返回的结果进行排序。默认顺序是升序,但是您可以DESC在指定列后返回结果的降序后添加关键字。您还可以指定多个列作为排序依据。
- SELECT *
- FROM users
- ORDER BY year, month DESC
如果要过滤结果,则可以使用WHERE命令指定想要的结果,然后是要过滤的列以及比较或逻辑表达式。例如,您可以在数据库中查询用户年龄大于21岁的记录。
- SELECT *
- FROM users
- WHERE age > 21
逻辑运算符
如果要过滤相似记录的集合,则可以使用来指定相似性LIKE(或ILIKE对于某些类型的SQL忽略字符串中的大小写)。该%符号可用作通配符,它可以表示任何字符或一组字符。要将通配符限制为仅一个字符,可以使用_符号。
- SELECT *
- FROM users
- WHERE name LIKE 'Bill%' -- returns for Bill, Billy, Billie, etc.
- SELECT *
- FROM users
- WHERE age BETWEEN 20 AND 30
- SELECT *
- FROM users
- WHERE age IN (20, 22, 24, 26)
NOT 可用于否定任何表达式。
IS NULL将检查指定列中的数据是否为空。通常,它与NOT关键字结合使用以仅访问不包含空值的记录。
- SELECT *
- FROM users
- WHERE age IS NOT NULL
中级SQL命令
汇总功能
COUNT(column)用于返回给定列中包含非空值的行数。您可以通过对索引列进行计数或使用来返回总行数*。
- -- returns total number of users
- SELECT COUNT(*)
- FROM users
- -- returns number of users where last_name is not blank
- SELECT COUNT(last_name)
- FROM users
SUM(column) 提供指定列中所有数值的总和。
MIN(column)在列中提供最小值。对于包含文本的列,“ A”被认为是最小值。MAX(column)在列中提供最大值。对于包含文本的列,“ Z”被认为是最大值。
AVG(column) 计算数字列的算术平均值,忽略空值。
到目前为止,我们仅在整个数据集上使用了聚合函数。但是,可以将数据分为几组并独立地汇总每个组。假设您有一张表格,其中包含过去几年公司的每日股票价格,并且您想查看该时间段内每月的股票平均价格。您可以通过按年和月(GROUP BY)对记录进行分组来实现此目的,然后合计每组中的各个记录以找到平均值(AVG)。
- SELECT year,
- month,
- AVG(daily_open) AS monthly_avg
- FROM google_stock_price
- GROUP BY year, month
- ORDER BY month, year
- SELECT year,
- month,
- AVG(daily_open) AS monthly_avg
- FROM google_stock_price
- GROUP BY year, month
- HAVING MAX(daily_open) - MIN(daily_open) > 50
- ORDER BY month, year
如果您的表在记录之间有重复的值,则可以用于DISTINCT返回一组唯一值,或者在DISTINCT用于多列的情况下返回值的唯一组合。您还可以放置DISTINCT在聚合函数内部,以仅对唯一的一组值执行聚合。
- -- example using DISTINCT for multiple columns
- SELECT DISTINCT year, month
- FROM google_stock_price
- -- example using DISTINCT within an aggregator function (suppose there are multiple stock price records per day)
- SELECT year,
- month,
- COUNT(DISTINCT day) AS days_in_month
- FROM google_stock_price
案例陈述可以在SELECT和GROUP BY陈述中使用。
- SELECT first_name,
- last_name,
- CASE WHEN age < 18
- THEN 'child'
- WHEN age >= 18
- THEN 'adult'
- ELSE NULL
- END AS legal_age_classification
- FROM users
- -- count the number of records in each age bracket
- SELECT CASE WHEN age >= 7 AND age < 22 THEN 'gen_z'
- WHEN age >= 22 AND age < 38 THEN 'gen_y'
- WHEN age >= 38 AND age < 53 THEN 'gen_x'
- WHEN age >= 53 THEN 'baby_boomer'
- ELSE 'unassigned'
- END AS age_group,
- COUNT(1) AS count
- FROM users
- GROUP BY age_group
- -- reorient the age bracket count horizontally (like a pivot table)
- SELECT COUNT(CASE WHEN age >= 7 AND age < 22 THEN 1 ELSE NULL END) AS gen_z_count,
- COUNT(CASE WHEN age >= 22 AND age < 38 THEN 1 ELSE NULL END) AS gen_y_count,
- COUNT(CASE WHEN age >= 38 AND age < 53 THEN 1 ELSE NULL END) AS gen_x_count,
- COUNT(CASE WHEN age >= 53 THEN 1 ELSE NULL END) AS baby_boomer_count
- FROM users
数据联接用于合并存储在多个表中的相关信息。外键用于描述一个表中的记录如何与另一表中的信息相关。
如下所述,有许多不同的方法可以在两个表之间组合信息。这些方法定义了如何处理没有关联到其他表中的记录的记录。要了解以下如何加入讨论的工作,请查看此交互式演示。
注意:如果两个表具有相同的列名,则需要为结果中的列创建别名。这是通过完成的SELECT column AS alias。
为了合并来自多个表的数据,必须有一个公共字段将一个表中的记录与另一个表中的记录相关联。在这种情况下,我们可以通过使用JOIN指定包含相关信息的表并ON提供两个表之间的关系来合并记录。这就是说,当原始表中的记录与联接表中的记录匹配时,将两个记录合并。您还可以在ON语句中提供其他逻辑,以进一步控制要联接的记录。最后,您还可以指定要匹配的多个外键;如果在两个表之间共有两列,则同时使用这两个键有时可以提高查询的准确性。
- SELECT *
- FROM users
- JOIN emails
- ON users.id = emails.user_id
- SELECT *
- FROM really_long_name_for_table_of_users users -- here, users is an alias
- JOIN emails
- ON users.id = emails.user_id AND users.join_date > '20150214'
- -- example of additional logic used in the ON statement
默认情况下,SQL执行内部联接。对于这种方法,仅返回与两个表相关的结果(不包括所有其他记录)。您也可以使用INNER JOIN代替显式定义内部联接JOIN,尽管这两个命令是同义词。
所有联接都会返回通过联接条件匹配的行,但是内部联接会排除所有不匹配的行。但是,即使记录与另一个表中的任何记录都不匹配,也可以包含记录-这被称为外部联接。不匹配的记录将包含通常从联接表中检索到的值的空值。
一个左连接(LEFT JOIN)返回原始表(通过指定的所有匹配的记录FROM),除了匹配的记录。不匹配的记录将包含从第二个表中检索到的字段的空值(通过指定JOIN)。
一个右连接(RIGHT JOIN)则正好相反,返回第二个表(通过指定的所有匹配的记录JOIN),除了匹配的记录。不匹配的记录将包含原始表(通过中指定FROM)中找不到的字段的空值。
一个完整的外部联接(FULL JOIN)返回的所有记录,匹配和不匹配,两个表。
您可以组合多个查询,通过创建一个将一个查询的结果堆叠在另一个查询的顶部UNION。默认情况下,UNION不会在结果中重复任何相同的行。如果要在查询中完全添加所有结果,则必须使用进行指定UNION ALL。为了使两个查询与一个联合兼容,它们必须具有相同的列数,并且在两个查询中每一列应具有相同的数据类型。
- SELECT *
- FROM table1
- UNION ALL
- SELECT *
- FROM table2
操作顺序
建立SQL查询时,请务必记住命令的执行顺序。人们经常说,SQL命令的词汇顺序和逻辑顺序是不同的。它们的意思是我们用简单的英语(词汇)构成查询的方式并不总是与计算机执行查询所需的信息(逻辑)顺序相匹配。
- FROM
- ON
- WHERE
- GROUP BY
- Aggregation functions (COUNT, SUM, MIN/MAX, AVG)
- HAVING
- SELECT
- DISTINCT
- UNION, INTERSECT, EXCEPT
- ORDER BY
- LIMIT, TOP