来源:Http://www.postgres.cn/docs/11/ 6.4. 从修改的行中返回数据 有时在修改行的操作过程中获取数据很有用。INSERT、 UPDATE和DELETE命令都有一个支持这个的可选的 RE
有时在修改行的操作过程中获取数据很有用。INSERT
、 UPDATE
和DELETE
命令都有一个支持这个的可选的 RETURNING
子句。使用RETURNING
可以避免执行额外的数据库查询来收集数据,并且在否则难以可靠地识别修改的行时尤其有用。
如果目标表上有触发器(第 39 章),可用于RETURNING
的数据是被触发器修改的行。因此,检查由触发器计算的列是 RETURNING
的另一个常见用例。
交叉连接
T1 CROSS JOIN T2
对来自于T1
和T2
的行的每一种可能的组合(即笛卡尔积),连接表将包含这样一行:它由所有T1
里面的列后面跟着所有T2
里面的列构成。如果两个表分别有 N 和 M 行,连接表将有 N * M 行。FROM
等效于T1
CROSS JOIN T2
FROM
(见下文)。它也等效于T1
INNER JOIN T2
ON TRUEFROM
。T1
,T2
当多于两个表出现时,后一种等效并不严格成立,因为JOIN
比逗号绑得更紧。例如FROM
和T1
CROSS JOIN T2
INNER JOIN T3
ON condition
FROM
并不完全相同,因为第一种情况中的T1
,T2
INNER JOIN T3
ON condition
condition
可以引用T1
,但在第二种情况中却不行。
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON boolean_expression
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 USING ( join column list )
T1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2
INNER
和OUTER
对所有连接形式都是可选的。INNER
是缺省;LEFT
、RIGHT
和FULL
指示一个外连接。
连接条件在ON
或USING
子句中指定, 或者用关键字NATURAL
隐含地指定。连接条件决定来自两个源表中的哪些行是“匹配”的,这些我们将在后文详细解释。
可能的条件连接类型是:
INNER JOIN
对于 T1 的每一行 R1,生成的连接表都有一行对应 T2 中的每一个满足和 R1 的连接条件的行。
LEFT OUTER JOIN
首先,执行一次内连接。然后,为 T1 中每一个无法在连接条件上匹配 T2 里任何一行的行返回一个连接行,该连接行中 T2 的列用空值补齐。因此,生成的连接表里为来自 T1 的每一行都至少包含一行。
RIGHT OUTER JOIN
首先,执行一次内连接。然后,为 T2 中每一个无法在连接条件上匹配 T1 里任何一行的行返回一个连接行,该连接行中 T1 的列用空值补齐。因此,生成的连接表里为来自 T2 的每一行都至少包含一行。
FULL OUTER JOIN
首先,执行一次内连接。然后,为 T1 中每一个无法在连接条件上匹配 T2 里任何一行的行返回一个连接行,该连接行中 T2 的列用空值补齐。同样,为 T2 中每一个无法在连接条件上匹配 T1 里任何一行的行返回一个连接行,该连接行中 T1 的列用空值补齐。
USING
是个缩写符号,它允许你利用特殊的情况:连接的两端都具有相同的连接列名。它接受共享列名的一个逗号分隔列表,并且为其中每一个共享列构造一个包含等值比较的连接条件。例如用USING (a, b)
连接T1
和T2
会产生连接条件ON
。T1
.a = T2
.a AND T1
.b = T2
.b
更进一步,JOIN USING
的输出会废除冗余列:不需要把匹配上的列都打印出来,因为它们必须具有相等的值。不过JOIN ON
会先产生来自T1
的所有列,后面跟上所有来自T2
的列;而JOIN USING
会先为列出的每一个列对产生一个输出列,然后先跟上来自T1
的剩余列,最后跟上来自T2
的剩余列。
最后,NATURAL
是USING
的缩写形式:它形成一个USING
列表, 该列表由那些在两个表里都出现了的列名组成。和USING
一样,这些列只在输出表里出现一次。如果不存在公共列,NATURAL JOIN
的行为将和JOIN ... ON TRUE
一样产生交叉集连接。
USING
对于连接关系中的列改变是相当安全的,因为只有被列出的列会被组合成连接条件。NATURAL
的风险更大,因为如果其中一个关系的模式改变会导致出现一个新的匹配列名,就会导致连接将新列也组合成连接条件。
例
别名成为当前查询的表引用的新名称 — 我们不再能够用该表最初的名字引用它了。因此,下面的用法是不合法的:
SELECT * FROM my_table AS m WHERE my_table.a > 5; -- 错误
表别名主要用于简化符号,但是当把一个表连接到它自身时必须使用别名,例如:
SELECT * FROM people AS mother JOIN people AS child ON mother.id = child.mother_id;
子查询指定了一个派生表,它必须被包围在圆括弧里并且必须被赋予一个表别名。
一个子查询也可以是一个VALUES
列表:FROM (VALUES ("anne", "smith"), ("bob", "jones"), ("joe", "blow")) AS names(first, last)
再次的,这里要求一个表别名。为VALUES
列表中的列分配别名是可选的,但是选择这样做是一个好习惯。
表函数是那些生成一个行集合的函数,这个集合可以是由基本数据类型(标量类型)组成, 也可以是由复合数据类型(表行)组成。
更多
GROUP BY
和HAVING
子句在严格的 sql 里,GROUP BY
只能对源表的列进行分组,但postgresql把这个扩展为也允许GROUP BY
去根据选择列表中的列分组。也允许对值表达式进行分组,而不仅是简单的列名。
GROUPING SETS
、CUBE
和ROLLUP
使用分组集的概念可以实现比上述更加复杂的分组操作。由 FROM
和WHERE
子句选出的数据被按照每一个指定的分组集单独分组,按照简单GROUP BY
子句对每一个分组计算 聚集,然后返回结果。
如果查询包含任何窗口函数(见第 3.5 节、第 9.21 节和第 4.2.8 节),这些函数将在任何分组、聚集和HAVING
过滤被执行之后被计算。也就是说如果查询使用了任何聚集、GROUP BY
或HAVING
,则窗口函数看到的行是分组行而不是来自于FROM
/WHERE
的原始表行。
DISTINCT
如果两行里至少有一个列有不同的值,那么我们认为它是可区分的。空值在这种比较中被认为是相同的。
两个查询的结果可以用集合操作并、交、差进行组合。
为了计算两个查询的并、交、差,这两个查询必须是“并操作兼容的”,也就意味着它们都返回同样数量的列, 并且对应的列有兼容的数据类型,如第 10.5 节中描述的那样。
NULLS FIRST
和NULLS LAST
选项将可以被用来决定在排序顺序中,空值是出现在非空值之前或者出现在非空值之后。
默认情况下,排序时空值被认为比任何非空值都要大,即NULLS FIRST
是DESC
顺序的默认值,而不是NULLS LAST
的默认值。
注意顺序选项是对每一个排序列独立考虑的。例如ORDER BY x, y DESC
表示ORDER BY x ASC, y DESC
,而和ORDER BY x DESC, y DESC
不同。
ORDER BY
可以被应用于UNION
、INTERSECT
或EXCEPT
组合的结果,但是在这种情况中它只被允许根据输出列名或编号排序,而不能根据表达式排序。
LIMIT
和OFFSET
LIMIT ALL
的效果和省略LIMIT
子句一样,就像是LIMIT
带有 NULL 参数一样。
OFFSET
说明在开始返回行之前忽略多少行。OFFSET 0
的效果和省略OFFSET
子句是一样的,就像是OFFSET
带有 NULL 参数一样。
如果OFFSET
和LIMIT
都出现了, 那么在返回LIMIT
个行之前要先忽略OFFSET
行。
如果使用LIMIT
,那么用一个ORDER BY
子句把结果行约束成一个唯一的顺序是很重要的。否则你就会拿到一个不可预料的该查询的行的子集。你要的可能是第十到第二十行,但以什么顺序的第十到第二十?除非你指定了ORDER BY
,否则顺序是不知道的。
WITH
查询(公共表表达式)WITH
提供了一种方式来书写在一个大型查询中使用的辅助语句。这些语句通常被称为公共表表达式或CTE,它们可以被看成是定义只在一个查询中存在的临时表。在WITH
子句中的每一个辅助语句可以是一个SELECT
、INSERT
、UPDATE
或DELETE
,并且WITH
子句本身也可以被附加到一个主语句,主语句也可以是SELECT
、INSERT
、UPDATE
或DELETE
。
WITH
中的SELECT
递归:可选的RECURSIVE
修饰符将WITH
从单纯的句法便利变成了一种在标准SQL中不能完成的特性。通过使用RECURSIVE
,一个WITH
查询可以引用它自己的输出。
WITH
中的数据修改语句你可以在WITH
中使用数据修改语句(INSERT
、UPDATE
或DELETE
)。
如果一个WITH
中的数据修改语句缺少一个RETURNING
子句,则它形不成临时表并且不能在剩余的查询中被引用。
更多
--结束END--
本文标题: PosegreSQL基础回顾(第 6 章 数据操纵、第 7 章 查询)
本文链接: https://lsjlt.com/news/5946.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
2024-10-23
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0