第二章 SQL命令参考-VALUES

最新推荐文章于 2025-02-22 20:04:25 发布

kygoal

最新推荐文章于 2025-02-22 20:04:25 发布

阅读量6.7k

点赞数

分类专栏： Greenplum-RefGuide

本文链接：https://blog.youkuaiyun.com/kygoal/article/details/78598252

版权

Greenplum-RefGuide 专栏收录该内容

73 篇文章

订阅专栏

VALUES

Computes a setof rows.

概要

VALUES( expression [, ...] ) [, ...]

[ORDER BY sort_expression [ASC | DESC | USINGoperator] [, ...]]

[LIMIT{count | ALL}] [OFFSET start]

描述

VALUES计算由值表达式指定的行值或一组行值。它通常用于在更大的命令中生成“常量表”，但可以单独使用。

当指定多个行时，所有行必须具有相同数量的元素。生成的表格列的数据类型是通过组合显示或推断出现在该列中的表达式的类型来确定的，使用与UNION相同的规则。

在较大的命令中，VALUES在SELECT的任何位置都可以在语法上被允许。由于语法将其视为SELECT，因此可以将ORDERBY，LIMIT和OFFSET子句与VALUES命令一起使用。

parameter

expression

一个常量或表达式，用于在结果表（行集）中的指定位置计算和插入。在出现在INSERT顶级的VALUES列表中，可以用DEFAULT替换表达式来指示应该插入目标列的默认值。当VALUES出现在其他上下文中时，不能使用DEFAULT。

sort_expression

指示如何对结果行进行排序的表达式或整数常量。该表达式可以将VALUES结果的列引用为column1，column2等。有关更多详细信息，请参阅SELECT的参数中的“ORDER BY子句”。

operator

排序操作符有关更多详细信息，请参阅参数中的“ORDER BY子句”

LIMIT count OFFSET start

LIMIT计数OFFSET开始.要返回的最大行数。有关更多详细信息，请参阅SELECT参数中的“LIMIT子句”。

Notes

应避免使用大量行的VALUES列表，因为您可能会遇到内存不足或性能不佳的情况。在INSERT中出现的VALUES是一种特殊情况（因为所需的列类型从INSERT的目标表中是已知的，并且不需要通过扫描VALUES列表来推断），所以它可以处理比其他上下文中实际更大的列表。

示例

VALUES(1, 'one'), (2, 'two'), (3, 'three');

will return atable of two columns and three rows. It is effectively equivalent to:

SELECT1 AS column1, 'one' AS column2

UNIONALL

SELECT2, 'two'

UNIONALL

SELECT3, 'three';

More usually, VALUES is used within a larger SQL command. The most commonuse is in INSERT:

INSERTINTO films (code, title, did, date_prod, kind)

VALUES('T_601', 'YoJimbo', 106, '1961-06-16', 'Drama');

In the context of INSERT, entries of aVALUES list can be DEFAULT to indicate that the column default should

be used hereinstead of specifying a value:

INSERTINTO films VALUES

('UA502','Bananas', 105, DEFAULT, 'Comedy', '82

minutes'),

('T_601','YoJimbo', 106, DEFAULT, 'Drama', DEFAULT);

VALUES canalso be used where a sub-SELECT mightbe written, for example in a FROM clause:

SELECTf.* FROM films f, (VALUES('MGM', 'Horror'), ('UA',

'Sci-Fi'))AS t (studio, kind) WHERE f.studio = t.studio AND

f.kind= t.kind;

UPDATEemployees SET salary = salary * v.increase FROM

(VALUES(1,200000, 1.2), (2, 400000, 1.4)) AS v (depno,

target,increase) WHERE employees.depno = v.depno AND

employees.sales>= v.target;

Note that an AS clause is required when VALUES is used in a FROM clause, just as istrue for SELECT. It is not

required that the ASclause specify names for all the columns, but it is good practice to do so. Thedefault

column names for VALUES are column1, column2, etc. in Greenplum Database, but these names might be

different inother database systems.

When VALUES is used in INSERT, thevalues are all automatically coerced to the data type of the

correspondingdestination column. When it is used in other contexts, it may be necessary tospecify the

correct datatype. If the entries are all quoted literal constants, coercing the first issufficient to determine

the assumed typefor all:

SELECT* FROM machines WHERE ip_address IN

(VALUES('192.168.0.1'::inet),('192.168.0.10'),

('192.168.1.43'));

Note: Forsimple IN tests, it is better to rely on thelist-of-scalars form of IN than to write a VALUES

query as shownabove. The list of scalars method requires less writing and is often moreefficient.

兼容性

values conforms to the SQL standard, except thatlimit and offset are Greenplum Database extensions.