#
第八十八章 SQL命令 WHERE(一) 指定一个或多个限制性条件的`SELECT`子句。 # 大纲 ```sql SELECT fields FROM table WHERE condition-expression ``` ## 参数 - `condition-expression` - 由一个或多个布尔谓词组成的表达式,该谓词控制要检索的数据值。 # 描述 可选的`WHERE`子句可以用于以下目的: - 指定限制要返回哪些数据值的谓词。 - 指定两个表之间的显式连接。 - 指定基表和另一个表中的字段之间的隐式连接。 `WHERE`子句最常用于指定一个或多个谓词,这些谓词用于限制`SELECT`查询或子查询检索到的数据(过滤出行)。 还可以在`UPDATE`命令、`DELETE`命令或`INSERT`(或`INSERT or UPDATE`)命令的结果集中使用`WHERE`子句。 `WHERE`子句限定或取消查询选择中的特定行。 符合条件的行是那些条件表达式为真的行。 条件表达式可以是一个或多个逻辑测试(谓词)。 多个谓词可以通过`AND`和`OR`逻辑操作符链接。 如果谓词包含除法,并且数据库中有任何值可以生成值为零或`NULL`的除法,则不能依赖求值顺序来避免被零除法。 相反,使用`CASE`语句来抑制风险。 `WHERE`子句可以指定包含子查询的条件表达式。子查询必须用圆括号括起来。 `WHERE`子句可以使用`=`(内部连接)符号连接操作符指定两个表之间的显式连接。 `WHERE`子句可以使用箭头语法(`- >`)操作符在基表和来自另一个表的字段之间指定隐式连接。 ## 指定字段 `WHERE`子句最简单的形式是指定一个比较字段和值的谓词,例如`WHERE Age > 21`。 有效的字段值包括以下:列名(`WHERE Age > 21`); `%ID`, `%TABLENAME`,或`%CLASSNAME`; 标量函数指定列名(`WHERE ROUND(Age,-1)=60`),一个排序规则函数指定列名(`WHERE %SQLUPPER(name) %STARTSWITH ' AB'`)。 不能按列号指定字段。 因为重新编译表时`RowID`字段的名称可能会改变,`WHERE`子句应该避免通过名称引用`RowID`(例如,`WHERE ID=22`)。 相反,使用`%ID`伪列名来引用`RowID`(例如,`WHERE %ID=22`)。 不能通过列别名指定字段; 尝试这样做会产生`SQLCODE -29`错误。 但是,可以使用子查询来定义列别名,然后在`WHERE`子句中使用该别名。 例如: ```sql SELECT Interns FROM (SELECT Name AS Interns FROM Sample.Employee WHERE Age<21) WHERE Interns %STARTSWITH 'A' ``` 不能指定聚合字段; 尝试这样做将生成`SQLCODE -19`错误。 但是,可以通过使用子查询向`WHERE`子句提供聚合函数值。 例如: ```sql SELECT Name,Age,AvgAge FROM (SELECT Name,Age,AVG(Age) AS AvgAge FROM Sample.Person) WHERE Age < AvgAge ORDER BY Age ``` ## 整型和字符串 如果将定义为整数数据类型的字段与数值进行比较,则在执行比较之前将数值转换为规范形式。 例如,`WHERE Age=007.00`解析为`WHERE Age=7`。 这种转换发生在所有模式中。 如果将定义为整数数据类型的字段与`Display`模式下的字符串值进行比较,则将该字符串解析为数值。 例如,与任何非数字字符串一样,空字符串(`"`)被解析为数字0。 这种解析遵循将字符串处理为数字的ObjectScript规则。 例如,WHERE Age='twenty'`解析为`WHERE Age=0`; `WHERE Age='20something'`解析为`WHERE Age=20`。 SQL只在Display模式下执行解析; 在逻辑或ODBC模式下,将整数与字符串值进行比较将返回`null`。 要比较字符串字段和包含单引号的字符串,请使用双引号。 例如,`WHERE Name %STARTSWITH 'O'''`,返回的是 `O’Neil and O’Connor`, 而不是 `Obama`. ## 日期和时间 SQL日期和时间使用逻辑模式内部表示进行比较和存储。 它们可以以逻辑模式、显示模式或ODBC模式返回。 例如,`1944年9月28日`表示为:逻辑模式`37891`,显示模式`09/28/1944`,ODBC模式`194409-28`。 在条件表达式中指定日期或时间时,可能由于SQL模式与日期或时间格式不匹配,或由于无效的日期或时间值而发生错误。 `WHERE`子句条件表达式必须使用与当前模式相对应的日期或时间格式。 例如,在逻辑模式下,要返回出生日期为`2005`年的记录,`WHERE`子句将出现如下:`WHERE DOB BETWEEN 59901 AND 60265` 当在显示模式下,同样的`WHERE`子句会出现如下:`WHERE DOB BETWEEN '01/01/2005' AND '12/31/2005'` 如果条件表达式的日期或时间格式与显示模式不匹配,将导致错误: - 在显示模式或ODBC模式下,以不正确的格式指定日期数据将产生`SQLCODE -146`错误。 以不正确的格式指定时间数据将产生`SQLCODE -147`错误。 - 在逻辑模式下,以不正确的格式指定日期或时间数据不会产生错误,但要么不返回数据,要么返回非预期的数据。 这是因为逻辑模式不会将显示或ODBC格式的日期或时间解析为日期或时间值。 `WHERE DOB BETWEEN 37500 AND 38000 AND DOB <> '1944-09-28' '`返回一系列`DOB`值,包括`DOB=37891(1944年9月28日)`,这是`<>`谓词试图忽略的。 无效的日期或时间值还会生成`SQLCODE -146`或`-147`错误。 无效日期是可以在显示模式/ODBC模式中指定的日期,但 IRIS不能转换为等效的逻辑模式。 例如,在ODBC模式下,以下命令会产生`SQLCODE -146`错误:`WHERE DOB > '1830-01-01'`,因为 IRIS无法处理1840年12月31日之前的日期值。 以下在ODBC模式下也会产生`SQLCODE -146`错误:`WHERE DOB BETWEEN '2005-01-01' AND '2005-02-29'`,因为2005不是闰年。 在逻辑模式下,`Display`模式或ODBC模式值不会被解析为日期或时间值,因此不会对其值进行验证。 因此,在逻辑模式下,`WHERE`子句(例如`WHERE DOB > '1830-01-01'`)不会返回错误。 ## 流字段 在大多数情况下,不能在WHERE子句谓词中使用流字段。 这样做将导致`SQLCODE -313`错误。 但是,在`WHERE`子句中允许使用流字段: - 流空测试:可以指定流字段`IS null`或流字段`IS NOT null`。 - 流长度测试:可以在`WHERE`子句谓词中指定`CHARACTER_LENGTH`(流字段)、`CHAR_LENGTH`(流字段)或`DATALENGTH`(流字段)函数。 - 流子串测试:可以在`WHERE`子句谓词中指定`substring (streamfield,start,length)`函数。 ## List结构 IRIS支持列表结构数据类型`%list`(数据类型类`%Library.List`)。 这是一种压缩的二进制格式,并不映射到 SQL的相应本机数据类型。 它对应的数据类型为`VARBINARY`,默认`MAXLEN`为`32749`。 因此,动态SQL不能在`WHERE`子句比较中使用`%List`数据。 要引用结构化列表数据,请使用`%INLIST`谓词或`FOR SOME %ELEMENT`谓词。 要在条件表达式中使用列表字段的数据值,可以使用`%EXTERNAL`将列表值与谓词进行比较。 例如,要返回`FavoriteColors`列表字段值由单个元素`'Red'`组成的所有记录: ```sql SELECT Name,FavoriteColors FROM Sample.Person WHERE %EXTERNAL(FavoriteColors)='Red' ``` 当`%EXTERNAL`将列表转换为`DISPLAY`格式时,显示的列表项似乎由一个空格分隔。 这个“空格”实际上是两个非显示字符`CHAR(13)`和`CHAR(10)`。 要对列表中的多个元素使用条件表达式,必须指定这些字符。 例如,要返回`FavoriteColors`列表字段值由两个元素`'Orange'`和`'Black'`(按顺序)组成的所有记录: ```sql SELECT Name,FavoriteColors FROM Sample.Person WHERE %EXTERNAL(FavoriteColors)='Orange'||CHAR(13)||CHAR(10)||'Black' ``` ## 变量 `WHERE`子句谓词可以指定: `%TABLENAME`或`%CLASSNAME`伪字段变量关键字。 `%TABLENAME`返回当前表名。 `%CLASSNAME`返回当前表对应的类名。 如果查询引用多个表,可以在关键字前加上表别名。 例如,`t1.%TABLENAME`。 一个或多个ObjectScript特殊变量(或它们的缩写):`$HOROLOG`, `$JOB`, `$NAMESPACE`, `$TLEVEL`, `$USERNAME`, `$ZHOROLOG`, `$ZJOB`, `$ZNSPACE`, `$ZPI`, `$ZTIMESTAMP`, `$ZTIMEZONE`, `$ZVERSION`。 ## 谓词列表 SQL谓词可分为以下几类: - Equality Comparison 谓词 - BETWEEN 谓词 - IN and %INLIST 谓词 - %STARTSWITH Predicate and Contains Operator - NULL Predicate - EXISTS Predicate - FOR SOME Predicate - FOR SOME %ELEMENT Predicate - LIKE, %MATCHES, and %PATTERN Predicates - %INSET and %FIND Predicates ## 谓词区分大小写 谓词使用为字段定义的排序规则类型。 默认情况下,字符串数据类型字段是用`SQLUPPER`排序规则定义的,它不区分大小写。 `%INLIST`、`Contains`操作符(`[`)、`%MATCHES`和`%PATTERN`谓词不使用字段的默认排序规则。 它们总是使用区分大小写的`EXACT`排序法。 两个字面值字符串的谓词比较总是区分大小写的。 ## 谓词条件和`%NOINDEX` 可以使用`%NOINDEX`关键字作为谓词条件的前缀,以防止查询优化器在该条件上使用索引。 这在指定绝大多数行都满足的范围条件时非常有用。 例如,`WHERE %NOINDEX Age >= 1`。 ## 离群值的谓词条件 如果动态SQL查询中的`WHERE`子句选择了一个非空的离群值,可以通过将离群值文字括在双括号中来显著提高性能。 这些双括号导致动态SQL在优化时使用离群值选择性。 例如,如果企业位于马萨诸塞州,那么很大一部分员工将居住在马萨诸塞州。 对于`Employees`表`Home_State`字段,`'MA'`是离群值。 要最优地选择这个值,应该指定`WHERE Home_State=(('MA'))`。 在嵌入式SQL或视图定义中不应使用此语法。 在嵌入式SQL或视图定义中,总是使用离群值选择,不需要特殊编码。 动态SQL查询中的`WHERE`子句会自动针对空离群值进行优化。 例如,`WHERE FavoriteColors IS NULL`这样的子句。 当`NULL`是离群值时,`is NULL`和`is NOT NULL`谓词不需要特殊编码。 离群值选择性由运行调优表实用程序决定。