后端开发教程 - Java、Spring Boot 实战 - msg200.com
SQL 概念从 A 到 Z
现在是破除一些术语的时候了。
我的职责很大一部分是引导和支持初级数据分析师。很多初级数据分析师刚刚开始使用 SQL，有的来自 Excel 分析领域，有的自学了 SQL 基础知识。
如果您需要复习，或者您生活中有初级分析师需要参考某些内容，以下是培训期间会出现的一些术语和概念。
别名
开始事务
CTE v 子查询
设计
ETL
函数
按
堆存储
完整性
连接
键
锁
大规模并行处理
规范化
OLTP v OLAP
权限
查询计划
灾难恢复
系统表
截断 v 删除
联合
视图
窗口函数
XML零
年
别名
在连接表时，我们需要声明要匹配哪个表中的哪一列，以及要在结果集中返回哪些列。如果存在同名的列，则需要明确指定要返回哪一列。
select
orders.item,
inventory.item,
inventory.unitprice
from
orders
inner join
inventory
on orders.order_item = inventory.inventory_item
为了加快输入速度，我们可以用更短的别名来给这两个表起别名。
select
o.item,
i.item,
i.unitprice
from
orders o
inner join
inventory i
on o.order_item = i.inventory_item
每次添加新列时，我们不必输入整个表名，而是可以使用字母“o”表示订单、字母“i”表示库存来设置别名。
在这篇适合初学者的文章中阅读有关 JOIN 和别名的更多信息：
开始交易
SQL 事务用于在对表进行更改时捕获错误。在执行 UPDATE 或 DELETE 语句期间，更改会自动提交。
通过将语句包装在事务中，当我们确定应该执行该语句或满足某个条件时，我们可以“回滚”或“提交”。
以下交易将在一个区块中运行，如果成功则提交。
begin transaction
update orders
set status = 'sent'
where order_id = '12345'
update orders
set status = 'sent'
where order_id = '54321'
commit transaction
详细了解其工作原理：
CTE 与子查询
CTE（通用表表达式）是一个临时的、命名的结果集，我们可以在单个 SELECT、INSERT、UPDATE、DELETE 或 MERGE 语句的范围内返回它。
例如，我在处理大型表时会使用它们：从“emailsent”表中获取我需要的所有列，然后从“emailunsubscribe”表中获取我需要的所有列。最后，将它们连接在一起。
; -- start the CTE with a semicolon to terminate anything above
with sent as -- here is where you name the dataset
(select
emailaddress,
emailid,
senddate
from
marketing.emailsent
where
senddate between '2018-01-01' and '2018-01-31'
), -- add a comma if you need to add a subsequent CTE
unsubs as
(select
emailaddress,
emailid,
senddate
from
marketing.emailunsubscribe
where
senddate between '2018-01-01' and '2018-01-31'
) -- no comma for the last CTE
select
'January' as [monthdelivered],
c.country,
count(distinct sent.emailaddress) as [countofdelivered],
count(distinct unsubs.emailaddress) as [countofunsubd]
from sent
left join
marketing.customers c on sent.email = unsubs.emailaddress
left join
unsubs on sent.email = unsubs.email
and sent.emailid = unsubs.emailid
Chidiebere撰写了一系列关于 CTE 及其与子查询的比较的优秀文章。
设计
星型和雪花型模式由两种类型的表组成。
- 事实——统计某件事发生的次数。
- 维度-（或 Dims）描述属性。
在星型模型中，我们可以将销售表作为事实数据的中心。商店、产品和位置的维度表像星型一样围绕着事实数据。
雪花与之类似，但维度更进一步。我们可能不仅有位置表，还有城市、国家和邮政编码表。所有维度都变成了雪花上的点。
详细了解两者的优缺点：
提取、加载 (ETL)
ETL 和 ELT 是将数据从源系统移动到目标系统所需的步骤。
-
提取——在提取步骤中，原始数据从源移动到临时或暂存区域。
-
转换——转换步骤将数据转换为与目标表匹配的数据。
-
加载——加载步骤将数据移动到最终目的地以进行分析或报告。
ETL 非常适合将数据放入正确的格式，删除不必要的列，并屏蔽与 GDPR 合规性相关的字段。
然而，当与数据湖架构结合使用时，ELT 已成为一种更受欢迎的方法。数据无需任何修改，因此可以快速到达。这样，数据科学家就可以只使用所需的数据，获得结果，并且不必担心转换步骤失败带来的延迟。
需要考虑原始数据的可靠性。每位数据科学家或最终用户在分析时都应应用相同的逻辑和业务规则，以保持结果的一致性。
有关 ETL、ELT 和数据管道的更多信息，请查看SeattleDataGuy的这篇文章，他撰写了有关所有数据方面的精彩文章。
功能
在 PostgreSQL 中，我们可以按计划执行代码块（称为函数）。它们可以像在数据库中临时运行的语句一样编写，也可以解析为变量以使其动态化。
阅读有关如何编写和执行函数的更多信息：
分组依据
聚合函数允许我们对字段进行计算。最常见的有 SUM、COUNT、MIN、MAX、AVERAGE。
例如，要查看订单表中每件商品的应付总额，我们可以使用 amount_due 列的 SUM 和 GROUP BY
select
order_item,
sum(amount_due)
from orders
group by order_item;
阅读有关聚合函数的更多信息：
堆积存储
堆存储是指数据库中没有聚集索引的表。数据以无特定顺序存储，新数据会随时添加。
索引是一种告诉数据库对数据进行排序或在哪里查找经常查询的数据的方式。
- 聚集索引就像一本书的目录页。应用这种索引就是告诉数据应该如何排列，就像书中的页面一样。
- 非聚集索引就像一本书的索引，页面并没有按照物理方式排列，但现在您可以通过查找更快地找到所需内容。
阅读这篇适合初学者的文章了解更多信息：
正直
这是指确保数据可追溯、可搜索和可恢复的数据质量和规则。
- 实体完整性-每个表必须具有唯一的主键
- 参照完整性 - 每个表上的外键引用另一个表上的主键或为 NULL
- 域完整性——每列都有特定的数据类型和长度。
阅读有关完整性和数据库设计的更多信息：
加入
由于我们的数据库包含经过规范化的表，您可能无法在单个表中找到所需的所有数据。为了以最合理的方式将数据重新组合在一起，我们使用 JOIN。这会将来自多个表的列添加到一个数据集中。
使用内连接（INNER JOIN，缩写为“JOIN”）来查找两个表之间的匹配项。您需要在两个表中都找到一个列作为连接依据，匹配过程就在此进行。任何不匹配的结果都将被丢弃。
当您想在两个表之间查找匹配项时，请使用 LEFT JOIN，但如果右表中没有匹配项，则显示 NULL。RIGHT JOIN 的作用相同，但方向相反。
与 INNER JOIN 类似，您需要一个列作为连接依据。与 INNER JOIN 不同的是，NULL 用于表示两个表之间不匹配。
凯蒂使用 Oracle 语法撰写了一篇关于“您将需要的每个 JOIN”的精彩文章。
钥匙
主键是能够最好地标识唯一行的列，并将每条记录标识为唯一，就像 ID
- 它确保没有重复
- 它不能是未知的（NULL）
- 每个表只能有一个主键
外键是与另一个表中的主键匹配并强制两者之间完整性的列。
要在 SQL Server 中创建主键，请在所选列的数据类型后添加保留字“主键”。
create table students (
id int not null primary key,
firstname varchar(255) not null,
lastname varchar(255) not null,
);
Lenique 通过这篇有关关系模型设计的文章将其付诸实践。
锁
当两个用户尝试同时查询或更新同一张表时，可能会导致锁定。就像两个人持有同一银行账户的 ATM 卡，试图从同一个银行账户中提取 100 美元一样，其中一个用户在第一笔交易完成时会被锁定。
Rhymes 很好地解释了它在数据库上的工作原理：
“...数据库锁的目的是保护对共享资源（表、行、数据）的访问。
在一个有数十个甚至数百个连接对同一数据集进行操作的系统中，必须有一个系统来避免两个连接使彼此的操作无效（或在其他情况下导致死锁）......
锁就是实现这一点的一种方法。一个操作进入数据库，声明它需要某个资源，完成修改后，释放该资源，以便下一个操作可以执行相同的操作。如果两个操作没有锁定各自的资源，可能会互相覆盖数据，从而造成灾难。
大规模并行处理
在像 Redshift 这样的大规模并行处理数据库中，数据被分布在多个计算节点上，每个节点都有内存来本地处理数据。
Redshift 将表的行分布到各个节点，以便并行处理数据。通过为每个表选择合适的分布键，可以平衡工作负载。
详细了解 Redshift，alexantra 撰写了 Redshift 完整指南：
正常化
数据库规范化提高了数据完整性，并允许在不改变底层结构的情况下添加新数据。
-
消除或尽量减少重复——在多个表中重复一个值意味着表会占用比实际需要更多的空间，从而增加存储成本。将客户的地址详细信息存储在一个表中，并使用与其订单关联的键，比在订单表的每一行中重复地址详细信息占用的空间更少。
-
简化数据更新——通过将值保存在一个表中，并将键保存在另一个表中，我们可以最大限度地降低更新时出错的风险。如果客户的电子邮件存储在两个地方，而只有一个地方更新，那么就会让人搞不清楚哪个地方是正确的。
-
简化查询- 如果表上没有重复，搜索和排序就会变得更容易。
阅读有关规范化的更多信息：
OLTP 与 OLAP
OLTP 和 OLAP 是指执行不同功能的数据库类型和工具。
- OLTP——在线事务处理——用于快速数据处理并立即响应查询。
- OLAP——在线分析处理——用于存储历史数据和数据挖掘。
阅读有关 OLTP 的更多信息：
权限
如果您打算与有权访问您架构的同事共享表，则需要授予他们访问权限。这样可以确保只有需要查看数据的人才能访问。
GRANT ALL ON <schemaname.tablename> TO <username>
-- if you would like them to SELECT, UPDATE and DELETE
GRANT SELECT ON <schemaname.tablename> TO <username>
-- if you would like them to be able to only SELECT
阅读有关权限以及您需要了解的有关数据库的所有其他信息：
查询计划
当我们运行查询时，SQL 引擎会考虑连接和索引，是否会扫描整个表或面临表锁定。
在 SQL Server 中，我们可以使用执行计划来可视化运行时信息和任何警告。
在 PostgreSQL 中，我们可以使用 EXPLAIN 命令检查查询计划：
EXPLAIN -- show the execution plan of a statement
EXPLAIN ANALYZE -- causes the query to be executed as well as explained
阅读有关每个查询的样子以及如何解释结果的更多信息：
恢复
数据库领域的灾难恢复指的是在一切正常运行时维护的备份、日志和复制实例。如果发生硬件故障、自然灾害甚至人为错误，这些操作可以启动、切换并进行分析。
-
故障转移- 设置多个集群，因此如果一个集群发生故障，另一个集群可以接管。
-
镜像- 在不同位置维护同一数据库的两个副本。其中一个处于离线模式，因此我们需要时，可以知道数据库的位置。
-
复制- 辅助数据库处于在线状态，可供查询。这不仅有利于灾难恢复，而且如果您使用一个实例进行报告，另一个实例进行实时查询，也会非常有用。如果您使用 AWS，只需单击几下即可完成设置。
系统表
在 SQL Server 中，这些通常被称为系统表和视图。它们位于 master 数据库中，该数据库保存着数据库的相关数据。
系统视图示例
- sys.objects - 显示每个对象、其类型和创建日期
- sys.indexes - 显示每个索引和类型
- information_schema.columns - 显示每一列、其位置和数据类型
在 PostgreSQL 中，可以在 information_schema 和 PostgreSQL 目录中找到类似的表集合。
目录对象的示例
- information_schema.tables - 显示每个对象、其类型和创建日期
- pg_index - 显示每个索引和类型
- information_schema.columns - 显示每一列、其位置和数据类型
截断 v 删除
这两个命令都会从表中删除数据，但方式不同。
- TRUNCATE 是一个 DDL 命令，它删除表的内容，但保留表的结构
truncate table marketing.emailcampaign
- DELETE 是一个 DML 命令，它删除给定 WHERE 子句的行
delete from
marketing.emailcampaign
where
month = 'January'
要了解哪些命令属于 DDL 以及哪些属于 DML 命令，请查看这篇文章。
联盟
JOIN 水平组合多行列，而 UNION 垂直组合结果。使用 UNION 会将两个查询的结果合并为一列，并删除重复项。如果查询包含多个列，则这些列的顺序必须相同才能完成 UNION。
select *
from
orders
union
select *
from
inventory
UNION ALL 将两个查询的结果组合在一起，与 UNION 相同，但保留结果中的重复项。
select *
from
orders
union all
select *
from
inventory
看法
视图不是表，它们是动态执行的查询，并用作从基表创建抽象级别的一种方式。
Joe 在他的帖子中完美地总结了这一点
视图是一种存储查询。创建数据库视图时，数据库会存储你指定的 SQL。然后，当你查询该视图时，数据库会获取存储的视图查询，添加针对该视图的查询的额外内容，并执行该查询。就是这样！
窗口函数
窗口函数之所以得名，是因为与聚合函数不同，它保持每一行完整并添加行号或运行总数。
下面是使用订单表的示例，该表使用 order_value 返回排名。
select
order_id,
order_name
order_date,
rank() over(order by amount_due desc) as rank
from
dbo.orders
XML
我们可以使用导入/导出向导将文件导入表中。但导入的文件不一定是 CSV 或 TXT 文件。只需几行代码，我们就可以导入 XML 文件。
阅读更多说明：
年
计算两个日期差值的语法因数据库而异。这会增加数据库迁移的复杂性，因此在下一个迁移项目中请务必牢记这一点。
零
NULL 表示值未知、非零且非空。这使得 NULL 与 NULL 之间的比较变得困难。
因为 NULL 不是一个值，所以我们不能使用比较运算符。相反，我们需要使用 IS 和 IS NOT 运算符：
select *
from
inventory
where
unitprice is not null
默认情况下，NULL 会显示为最大值，这会使排序充其量令人厌烦，甚至会产生误导。为了解决这个问题，我们可以使用 COALESCE 将 NULL 视为 0。
select
itemname,
coalesce(unitprice, 0)
from
inventory
order by 2
以上就是我们对 SQL 和数据库新手的关键术语、概念和术语的简要介绍。
文章来源：https://dev.to/helenanders26/sql-series-from-a-to-z-2pk9