数据库原理与SQL基础
实体关系图
实体关系图 (entity relationship diagram，ERD) 是查看数据库中数据的常用方式。下面是我们将用于 Parch & Posey （虚拟公司）数据库的 ERD。这些图可帮助你可视化正在分析的数据，包括:
- 表的名称。
- 每个表中的列。
- 表配合工作的方式。
你可以将下面的每个框看作一个电子表格。
image
在 Parch & Posey 数据库中，共有五个表（基本上是 5 个电子表格）:
- web_events
- accounts
- orders
- sales_reps
- region
你可以将每个表视为一个单独的电子表格。然后将每个电子表格中的列放在表名下面。例如，region 表有两列: id 和 name，而 web_events 表有四列。
image.png
将这些表格连接在一起的 "crow's foot" 表示法显示了一个表中的列与另一个表中的列之间的关联。在第一课中，你将学习使用 SQL 与单个表进行交互的基础知识。在下一节课中，你将进一步了解这些连接对于使用 SQL 和关系数据库的重要性。
关于 SQL 数据库中所存储数据的几个要点：
-
数据库中的数据存储在类似于 Excel 电子表格的表中。
大多数情况下，可以将数据库视为一堆 Excel 电子表格。每个电子表格都有行和列。每行保存有关交易、个人、公司等的数据。而每列所保存的数据与你关心的某一特定行相关，如名称、位置、唯一身份等。 -
同一列中的所有数据必须符合数据类型。
将整个列认为是定量离散的或是某种字符串。这说明如果特定列中有一行字符串，那么整个列可能会更改为文本数据类型。 如果想使用此列进行数学计算，这可能会非常糟糕！ -
列类型一致是快速使用数据库的主要原因之一。
数据库通常会存储海量数据。因此，知道这些列都是相同类型的数据意味着可快速从数据库获取数据。
数据库类型
SQL 数据库
数据库的类型有很多，都用于不同的用途。在本课中，我们将使用 Postgres，这是一个流行的开源数据库，具有非常完整的分析函数库。
一些最受欢迎的数据库包括：:
- MySQL
- Access
- Oracle
- Microsoft SQL Server
- Postgres
你也可以在其他编程框架中编写 SQL，如 Python、Scala 和 HaDoop。
细微差别
这些 SQL 数据库中的每一个可能在语法和可用函数上存在细微差异 -- 例如，MySQL 中没有像 Postgres 中类似的可用于修改日期的函数。你在 Postgres 中看到的大部分直接适用于在其他框架中和数据库环境中使用 SQL。如需了解存在的差异，可查看文档。大多数 SQL 环境都有很好的在线文档，通过快速的 Google 搜索便可轻松访问。
这里 的文章比较了三种最常见的 SQL 类型：SQLite、PostgreSQL 和 MySQL。尽管你在Lesson中使用的是 PostgreSQL，但在项目中你要使用 SQLite。
一旦你学会了如何在一个环境中编写 SQL，这些基本大都是可以转移到其他环境的。
语句
SQL 的关键是理解语句。SQL 语句是可以读取和处理数据的代码。但这不是一个真正的句子。 SQL 不区分大小写 - 这意味着可以在代码中的任何位置写入大写和小写。另外，还可以使用 分号 结束 SQL 语句，但某些 SQL 环境结尾时不需要分号。
常用语句包括：
- CREATE TABLE 是一个在数据库中创建新表的语句。
- DROP TABLE 是删除数据库中表的语句。
- SELECT 读取并显示数据。我们将这称为查询。
SELECT 语句是分析师使用的通用语句，被称为查询。 DROP 和 CREATE 语句实际上会更改数据库中的数据。在大多数公司，分析师没有权限使用这些类型的语句。这是一个不错的规定 - 原因是实际上更改数据库中的数据是一件需要很大权限的事情。通常仅数据库管理员具有这个权限。
SELECT 用于提醒查询要返回哪些列。
FROM 用于提醒查询在哪个表中查询。注意，这个表中需要有列。
SQL 语句 SELECT 和 FROM
Parch & Posey 数据库中的表存储在下面方框的后台。你会注意到 SCHEMA 图表 下的左侧列表（需要先点击刷新按钮）下面，是之前在 ERD 中显示的表列表。 与在大多数其他数据库环境中一样编写查询并运行，然后使用下表查看结果。在这节课中，我们一次只能访问一个表，但在后面的课程中，我们将增加表的数量并在各个表之间进行聚合。
image.png
开始时，可以试着运行上一个视频中看到的查询！ 可以在下面的环境中练习你自己的 SQL 代码。在左侧面板可以看到我们之前在 ERD 中看到的表。在右侧面板可以编写 SQL 代码，可以单击 EVALUATE 按钮就可以运行查询。这可能需要一会儿才能运行。
HISTORY 菜单将显示你以前运行的查询。 可以通过 MENU 从左侧面板删除 SCHEMA 和重置数据库。
SELECT *
FROM orders;
你会注意到 Derek 使用一个 demo 表（他在以后的课程中还会继续采用这种方式），但是你应该使用完全如左侧面板所示的表名来编写自己的查询。 我们将来还会在 Derek 所教授的课程中看到这些（删除了 demo）表格。
image.png
编写的每个查询至少要有两个部分：SELECT 和 FROM。 SELECT 语句用于放置要显示数据的列。FROM 语句用于放置要从中提取数据的表。
规定查询格式
大写
你可能已经注意到，我们大写了 SELECT 和 FROM，而将表和列名称小写。这是一个常见的格式惯例。大写命令（SELECT、FROM），小写查询中的其他内容是常见做法。这使得查询更容易读取，这在编写更复杂的查询时更为重要。准备编写查询时，这是一个很好的习惯。
表和变量名中不需要空格
通常在列名中使用下划线，避免使用空格。 在 SQL 中使用空格有点麻烦。 在 Postgres 中，如果列或表名称中有空格，就需要使用双引号括住这些列/表名称（例如：FROM "Table Name"，而不是 FROM table_name）。在其他环境中，可能会使用方括号（例如：FROM [Table Name]）。
在查询中使用空格
SQL 查询忽略空格，因此可以根据需要在代码之间添加尽可能多的空格和空行，并且查询结果是相同的。我们来看下面这个查询
SELECT account_id FROM orders
等价于这个查询:
SELECT account_id
FROM orders
SQL 不区分大小写
如果你已经使用过其他语言编程，那么可能会熟悉编程语言，如果没有区分大小写键入正确的字符，那么会非常麻烦。 SQL 不区分大小写。 我们来看看下面的查询：
SELECT account_id
FROM orders
和这个相同：
select account_id
from orders
也和这个相同：
SeLeCt AcCoUnt_id
FrOm oRdErS
但是，我会再次提醒你遵循上面讲述的完全大写命令的惯例，而将其他代码片段小写。
分号
根据 SQL 环境，查询结尾可能需要一个执行的分号。 这个"要求"在其他环境中比较灵活。我们认为在每个语句的末尾添加一个分号是最好的做法，如果环境能够一次显示多个结果，那么这样做还可以一次运行多个命令。
最好的做法：
SELECT account_id
FROM orders;
因为，我们这里的环境不需要分号，你会看到没有分号的解决方案：
SELECT account_id
FROM orders
SQL 语句 LIMIT
如果想要查看表的前几行时，LIMIT 语句就能派上用场。这可比加载整个数据集要快得多。
LIMIT 命令始终是查询的最后一部分。下面的例子仅显示 orders 表的前 10 行和所有列，OFFSET 表示从第几行开始：
SELECT *
FROM orders
LIMIT 10 OFFSET 5;
SQL 语句 ORDER BY
ORDER BY 语句可使我们按任意行排序表。如果熟悉 Excel，这与使用过滤器进行排序相似。
ORDER BY 语句始终在 SELECT 和 FROM 语句之后，但位于 LIMIT 语句之前。 学习其他命令时，这些语句的顺序将更为重要。 如果使用 LIMIT 语句，它将始终显示在最后。
提示
记住可以在ORDER BY 语句中的列之后添加 DESC，然后按降序排序，因为默认是按升序排序的。
示例
- 编写查询，返回
orders表的先下单的前 10 个订单。包含id、occurred_at和total_amt_usd。
SELECT id, occurred_at, total_amt_usd
FROM orders
ORDER BY occurred_at
LIMIT 10;
- 编写一个查询，返回
orders表里total_amt_usd最高的5个 订单。包括id、account_id和total_amt_usd。
SELECT id, account_id, total_amt_usd
FROM orders
ORDER BY total_amt_usd DESC
LIMIT 5;
- 编写一个查询，基于
total，返回orders表里的前 20 个 订单。包括id、account_id和total
SELECT id, account_id, total
FROM orders
ORDER BY total
LIMIT 20;
我们可以一次 ORDER BY 多列。这个语句可以按照从左至右列出的列进行排序。 我们也可以也可以使用 DESC 来翻转排序。
示例
- 查询
orders表格，按照订单日期的从新到旧降序排列，同时每个日期下的订单按照total_amt_usd降序排列，显示前5行。
SELECT *
FROM orders
ORDER BY occurred_at DESC, total_amt_usd DESC
LIMIT 5;
- 查询
orders表格，按照订单日期的从旧到新升序排列，同时每个日期下的订单按照total_amt_usd升序排列，显示前10行。
SELECT *
FROM orders
ORDER BY occurred_at, total_amt_usd
LIMIT 10;
SQL 语句 WHERE
我们可以基于必须满足的条件，使用 WHERE 语句来为表格创建子集。下面的视频介绍了如何使用这个语句，在接下来的课程中，我们将学习一些与 WHERE 语句一起使用的常见运算符。
WHERE 语句在 FROM 后，ORDER BY 和 LIMIT 前。
WHERE 语句中使用的常用符号包括：
-
>（大于） -
<（小于） -
>=（大于或等于） -
<=（小于或等于） -
=（等于） -
!=（不等于）
示例
- 从
orders表提取出gloss_amt_usd大于或等于 1000 美元的前五行数据（包含所有列）。
SELECT *
FROM orders
WHERE gloss_amt_usd >= 1000
LIMIT 5;
- 从
orders表提取出total_amt_usd小于 500 美元的前十行数据（包含所有列）。
SELECT *
FROM orders
WHERE total_amt_usd < 500
LIMIT 10;
你会注意到我们在使用这些 WHERE 语句时，不需要 ORDER BY，除非要实际整理数据。不必对数据进行排序，仍可继续执行条件。
WHERE 语句也可以与非数字数据一起使用。我们可以使用 = 和 ！= 运算符。 还需要确保在文本数据中使用单引号，而不是双引号（如果原始文本中有引号，就一定要注意）。
通常将 WHERE 与非数字数据字段一起使用时，我们会使用LIKE、NOT 或 IN 运算符。
LIKE 如果操作数匹配某个模式，则值为TRUE。
LIKE 通常与 %等通配符配合使用，例如LOVING%可以表示LOVING、LOVING you、LOVING？
NOT 对其他布尔运算符的值取反。
IN 如果操作数与一个表达式列表中的某个相等，则值为TRUE。
当NOT 、 AND、OR 出现在同一表达式中，优先级为NOT 、 AND、OR。
例如
3>5 OR 6>3 AND NOT 6>4 = FALSE
NOT 6>4 = FALSE6>3 AND FALSE = FALSE3>5 OR FALSE = FALSE
示例
- 从
accounts表格中筛选出Exxon Mobil的name（客户名称），同时包含website和primary point of contact(primary_poc) 等数据。
SELECT name, website, primary_poc
FROM accounts
WHERE name = 'Exxon Mobil';
SQL 语句 GROUP BY 和 HAVING
GROUP BY 通常与聚合函数如SUM、AVE等一起使用，每一组如“男”/“女” GROUP BY Sex 各产生一个平均值
HAVING 子句只能与 SELECT 一起使用，且通常在 GROUP BY子句中使用，语法如下：
HAVING <searching condition>
示例
SELECT 课程名， COUNT(课程名) AS 重复数量
FROM course
GROUP BY 课程名
HAVING COUNT(课程名)>1
ORDER BY 课程名
显示结果
| 课程名 | 重复数量 |
|---|---|
| C语言 | 2 |
| 计算机网络 | 3 |
| 软件测试 | 3 |
其他提示
尽管 SQL 不区分大小写（它不在乎你将语句全部大写还是小写），但我们讨论了一些最佳实践。关键词的顺序非常重要！ :
SELECT col1, col2
FROM table1
WHERE col3 > 5 AND col4 LIKE '%os%'
ORDER BY col5
LIMIT 10;
注意，你可以检索不同于 ORDER BY 和 WHERE 语句中所使用列的列。假定这些列名均以这样的方式（col1、col2、col3、col4、col5）存在于一个名为 table1 的表中，此查询便会很好地运行。
后续内容
在后续的 SQL 课程中，你将学习 JOIN（连接），聚合，子查询和临时表格。
JOIN 语句
Select query with INNER JOIN on multiple tables
SELECT column, another_table_column, …
FROM mytable
INNER JOIN another_table
ON mytable.id = another_table.id
WHERE condition(s)
ORDER BY column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;
Select query with LEFT/RIGHT/FULL JOINs on multiple tables
SELECT column, another_column, …
FROM mytable
INNER/LEFT/RIGHT/FULL JOIN another_table
ON mytable.id = another_table.matching_id
WHERE condition(s)
ORDER BY column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;
When joining table A to table B, a LEFT JOIN simply includes rows from A regardless of whether a matching row is found in B. The RIGHT JOIN is the same, but reversed, keeping rows in B regardless of whether a match is found in A. Finally, a FULL JOIN simply means that rows from both tables are kept, regardless of whether a matching row exists in the other table.
Select query with constraints on NULL values
SELECT column, another_column, …
FROM mytable
WHERE column IS/IS NOT NULL
AND/OR another_condition
AND/OR …;
正确的顺序
Complete SELECT query
SELECT DISTINCT column, AGG_FUNC(column_or_expression), …
FROM mytable
JOIN another_table
ON mytable.column = another_table.column
WHERE constraint_expression
GROUP BY column
HAVING constraint_expression
ORDER BY column ASC/DESC
LIMIT count OFFSET COUNT;
INSERT INTO 语句
Insert statement with values for all columns
INSERT INTO mytable
(column, another_column, …)
VALUES (value_or_expr, another_value_or_expr, …),
(value_or_expr_2, another_value_or_expr_2, …),
…;
UPDATE 语句
用来更新 table 中的数据
Update statement with values
UPDATE mytable
SET column = value_or_expr,
other_column = another_value_or_expr,
…
WHERE condition;
一般 condition 里对行进行约束
删除行
Delete statement with condition
DELETE FROM mytable
WHERE condition;
创建表格
CREATE TABLE IF NOT EXISTS mytable (
column DataType TableConstraint DEFAULT default_value,
another_column DataType TableConstraint DEFAULT default_value,
…
);
Table data types
| Data type | 描述 |
|---|---|
| INTEGER, BOOLEAN | 整数数据类型可以存储整数值，例如数字或年龄的计数。 在一些实现中，布尔值仅表示为仅0或1的整数值。 |
| FLOAT, DOUBLE, REAL | 浮点数据类型可以存储更精确的数值数据，如测量值或小数值。 根据该值所需的浮点精度，可以使用不同的类型。 |
| CHARACTER(num_chars), VARCHAR(num_chars), TEXT | 基于文本的数据类型可以在各种语言环境中存储字符串和文本。 在处理这些列时，各种类型之间的区别通常相当于数据库的效率。CHARACTER和VARCHAR（变量字符）类型都指定了它们可以存储的最大字符数（较长的值可能被截断），因此使用大表存储和查询会更有效。 |
| DATE, DATETIME | SQL还可以存储日期和时间戳，以跟踪时间序列和事件数据。 特别是在跨时区操纵数据时，它们可能很难处理。 |
| BLOB | SQL可以将二进制数据存储在数据库中的blobs中。这些值对数据库来说通常是不透明的，所以您通常必须用正确的元数据来存储它们以重新查询它们。 |
概括
命令
| 语句 | 用法 | 其他详情 |
|---|---|---|
SELECT |
SELECT Col1, DISTINCT Col2, ... |
提供你想要的列，DISTINCT 表示去除重复行 |
AS |
SELECT SUM(revenue) AS 总收入 |
使用 SUM 函数计算总收入 |
FROM |
FROM Table |
提供列存在的表 |
LIMIT |
LIMIT 10 |
限制返回的行数 |
ORDER BY |
ORDER BY Col |
根据列对表排序。与 DESC 一起使用。 |
GROUP BY |
GROUP BY Sex |
通常与聚合函数如SUM、AVE等一起使用，每一组如“男”/“女”各产生一个平均值 |
HAVING |
HAVING <searching condition> |
HAVING 子句只能与SELECT一起使用，且通常在 GROUP BY 子句中使用用以对 group 施加筛选条件 |
WHERE |
WHERE Col > 5 |
用于过滤结果的条件语句 |
AND |
WHERE Col1 = 5 AND Col2 = 3 |
过滤两个或多个条件必须为真的行 |
网友评论