9.2. Comparison Functions and Operators

9.2. Comparison Functions and Operators 9.2.比较函数和运算符 The usual comparison operators are available, as shown in Table 9.1. 常用的比较运算符,如表9.1所示。 9.2. Comparison Functions and OperatorsNote  SQL标准中,不等于为<>,!=是一个别名。在解析阶段,!=会解析为<>。!=和<>总是等效。   这些比较运算符对于那些具有排序的内置数据类型均可用,包含数据类型、字符串及日期/时间类型。另外,如果数组、复合类型和范围类型的组成数据类型为相关类型,则也可以比较。   一般相关的数据类型都可以比对,例如integer>bigint。此类情况有时是由“交叉类型”比较运算符直接实现的,但是如果没有此类运算符可用,则解析器将把较不通用的类型强制转换为较通用的类型,并应用后者的比较运算符。   如上所示,所有的比较运算符均为返回Boolean类型的运算符。因此,类似于1<2<3这样的表达式是不正确的(因为没有与三比较的值)。使用BETWEEN进行范围测试。   如表9.2所示,还有许多比较谓词。其行为类似操作符,但有其自己的语法。   9.2. Comparison Functions and Operators9.2. Comparison Functions and Operators9.2. Comparison Functions and Operators9.2. Comparison Functions and Operators9.2. Comparison Functions and Operators   The BETWEEN predicate simplifies range tests: BETWEEN谓词简化了范围测试:   a BETWEEN x AND y   is equivalent to 等效于:   a >= x AND a <= y   请注意,BETWEEN将端点值视为包含在范围内。BETWEEN SYMMETRIC类似于BETWEEN,不同点在于,它不要求两边的值必须左边小于右边。如果出现左边值大于右边的情况,BETWEEN SYMMETRIC会将其重新排序。   BETWEEN对于可比较的数据类型均适用。   注: BETWEEN中的AND与逻辑操作中的AND容易混淆。为解决此问题,限定仅部分表达式类型可以作为BETWEEN的第二个参数。如果需要在BETWEEN中写一个更复杂的子表达式,建议使用括号。   普通比较运算符当输入为null时,输出null(以为“unknown”)。例如,7=null返回null;7<>null返回null。若想避免此行为,使用IS [NOT] DISTINCT FROM: a IS DISTINCT FROM b a IS NOT DISTINCT FROM b   For non-null inputs, IS DISTINCT FROM is the same as the <> operator. However, if both inputs  are null it returns false, and if only one input is null it returns true. Similarly, IS NOT DISTINCT  FROM is identical to = for non-null inputs, but it returns true when both inputs are null, and false when  only one input is null. Thus, these predicates effectively act as though null were a normal data value, rather than “unknown”. 对于非空输入,IS DISTINCT FROM与<>运算符相同。 但是,如果两个输入均为null,则返回false,如果只有一个输入为null,则返回true。 类似地,对于非空输入,IS NOT DISTINCT FROM与=相同,但是当两个输入均为空时,它返回true;而当只有一个输入为空时,它返回false。 因此,这些谓词有效地表现为空值是正常数据值,而不是“未知”。   To check whether a value is or is not null, use the predicates: 要检查值是否为空,请使用谓词:   expression IS NULL expression IS NOT NULL   or the equivalent, but nonstandard, predicates: 或等同,但非标准的谓词:   expression ISNULL expression NOTNULL   Do not write expression = NULL because NULL is not “equal to” NULL . (The null value represents  an unknown value, and it is not known whether two unknown values are equal.) 不要写expression = NULL,因为NULL不等于NULL。(空值表示一个未知值,而两个未知值不知道是否相等。)   Tip 小贴士 Some applications might expect that expression = NULL returns true if expression  evaluates to the null value. It is highly recommended that these applications  be modified to comply with the SQL standard. However, if that cannot be done the  transform_null_equals configuration variable is available. If it is enabled, PostgreSQL  will convert x = NULL clauses to x IS NULL . 如果表达式的计算结果为空值,则某些应用程序可能期望expression = NULL返回true。强烈建议修改这些应用程序以符合SQL标准。但是,如果不能修改,则可以使用transform_null_equals配置变量。如果启用该变量,则PostgreSQL会将x = NULL子句转换为x IS NULL。   If the expression is row-valued, then IS NULL is true when the row expression itself is null  or when all the row's fields are null, while IS NOT NULL is true when the row expression itself  is non-null and all the row's fields are non-null. Because of this behavior, IS NULL and IS NOT  NULL do not always return inverse results for row-valued expressions; in particular, a row-valued  expression that contains both null and non-null fields will return false for both tests. In some cases, it may be preferable to write row IS DISTINCT FROM NULL or row IS NOT DISTINCT  FROM NULL , which will simply check whether the overall row value is null without any additional  tests on the row fields. 如果表达式为行值,则当行表达式本身为null或所有行的字段为null时,IS NULL为true;而当行表达式本身为非null且所有行的字段为非null时,IS NOT NULL为true。由于这种行为,IS NULL和IS NOT NULL并不总是返回行值表达式的反结果。特别是,同时包含null和non-null字段的行值表达式对于两个测试都将返回false。在某些情况下,最好将行IS DISTINCT FROM NULL或行IS NOT DISTINCT FROM NULL写入,这将简单地检查整个行值是否为null,而无需对行字段进行任何其他测试。   Boolean values can also be tested using the predicates 布尔值也可以使用谓词进行测试:   boolean_expression IS TRUE boolean_expression IS NOT TRUE boolean_expression IS FALSE boolean_expression IS NOT FALSE boolean_expression IS UNKNOWN boolean_expression IS NOT UNKNOWN   These will always return true or false, never a null value, even when the operand is null. A null input  is treated as the logical value “unknown”. Notice that IS UNKNOWN and IS NOT UNKNOWN are  effectively the same as IS NULL and IS NOT NULL , respectively, except that the input expression  must be of Boolean type. 这些将始终返回true或false,即使操作数为null,也永远不会返回null值。空输入被视为逻辑值“未知”。 请注意,IS UNKNOWN和IS NOT UNKNOWN实际上分别与IS NULL和IS NOT NULL相同,只是输入表达式必须为布尔类型。   Some comparison-related functions are also available, as shown in Table 9.3. 如表9.3所示,还提供了一些与比较相关的函数。   9.2. Comparison Functions and Operators9.2. Comparison Functions and Operators  
上一篇:用golang实现一个小程序商城后台系统(moshopserver)


下一篇:Java官方教程(三-1)运算符 operator(2020.12.18)