#
第七十二章 SQL命令 SELECT(四) # WHERE子句 `WHERE`子句限定或取消查询选择中的特定行。 符合条件的行是那些条件表达式为真的行。 条件表达式是逻辑测试(谓词)的列表,可以通过`AND`和`OR`逻辑操作符链接这些测试(谓词)。 这些谓词可以使用`NOT`一元逻辑操作符进行反转。 SQL谓词可分为以下几类: - `Comparison` 谓词 - `BETWEEN` 谓词 - `LIKE` 谓词 - `NULL` 谓词 - `IN and %INLIST` 谓词 - `EXISTS` 谓词 - `FOR SOME` 谓词 - `FOR SOME %ELEMENT` 谓词 条件表达式不能包含聚合函数。 如果希望使用聚合函数返回的值指定选择条件,请使用`HAVING`子句。 `WHERE`子句可以使用=(内部连接)符号连接操作符指定两个表之间的显式连接。 `WHERE`子句可以使用箭头语法(`- >`)操作符在基表和来自另一个表的字段之间指定隐式连接。 # GROUP BY子句 `GROUP BY`子句接受查询的结果行,并根据一个或多个数据库列将它们分成单独的组。 当将`SELECT`与`GROUP BY`结合使用时,将为`GROUP BY`字段的每个不同值检索一行。 `GROUP BY`子句在概念上类似于 IRIS扩展`%FOREACH`,但是`GROUP BY`操作整个查询,而`%FOREACH`允许在子填充上选择聚合,而不限制整个查询填充。 例如: ```sql SELECT Home_State, COUNT(Home_State) AS Population FROM Sample.Person GROUP BY Home_State ``` 这个查询为每个不同的`Home_State`返回一行。 # HAVING 子句 `HAVING`子句类似于对组进行操作的WHERE子句。 它通常与`GROUP BY`子句或`%AFTERHAVING`关键字一起使用。 `HAVING`子句限定或取消查询选择中的特定行。 符合条件的行是那些条件表达式为真的行。 条件表达式是逻辑测试(谓词)的列表,可以通过`AND`和`OR`逻辑操作符链接这些测试(谓词)。 条件表达式可以包含聚合函数。 # ORDER BY 子句 `ORDER BY`子句由`ORDER BY`关键字后面跟着一个选择项或一个以逗号分隔的项列表组成,该列表指定显示行的顺序。 每个项目可以有一个可选的`ASC`(升序)或`DESC`(降序)。 默认为升序。 `ORDER BY`子句应用于查询的结果,并且经常与`TOP`子句配对。 下面的示例返回数据库中所有行的选定字段,并按年龄升序排列这些行: ```sql SELECT Home_State, Name, Age FROM Sample.Person ORDER BY Age ``` # SELECT和事务处理 执行查询的事务被定义为`READ COMMITTED`或`READ UNCOMMITTED`。 默认是`READ UNCOMMITTED`。 不在事务中的查询定义为`READ UNCOMMITTED`。 - 如果`READ UNCOMMITTED`,则`SELECT`返回数据的当前状态,包括未提交的正在进行的事务对数据所做的更改。 这些更改可能随后被回滚。 - 如果`READ COMMITTED`,则行为取决于`SELECT`语句的内容。 通常,在`read committed`模式下的`SELECT`语句只会返回对已提交数据的插入和更新更改。 已被正在进行的事务删除的数据行不会返回,即使这些删除尚未提交并可能回滚。 但是,如果`SELECT`语句包含`%NOLOCK`关键字、`DISTINCT`子句或`GROUP BY`子句,则`SELECT`返回数据的当前状态,包括当前事务中尚未提交的对数据的更改。 `SELECT`中的聚合函数还返回指定列的数据的当前状态,包括未提交的更改。 # Query Metadata 可以使用`Dynamic SQL`返回关于查询的元数据,例如查询中指定的列数、查询中指定的列的名称(或别名)以及查询中指定的列的数据类型。 # 示例 在下面的示例中,在`Sample.Person`中的所有记录上计算`AvgAge computed`字段。 `HAVING`子句管理`AvgMiddleAge computed`字段,从`Sample.Person`中的所有记录中计算那些超过40岁的人的平均年龄。 因此,`AvgAge`和`AvgMiddleAge`的每一行都有相同的值。 `ORDER BY`子句按照`Home_State`字段值的字母顺序对行进行显示。 ```sql SELECT Name,Home_State,Age,AVG(Age) AS AvgAge, AVG(Age %AFTERHAVING) AS AvgMiddleAge FROM Sample.Person HAVING Age > 40 ORDER BY Home_State ``` ## WHERE/HAVING/ORDER BY 在下面的示例中,`WHERE`子句将选择限制在七个指定的东北部州。 `AvgAge computed`字段是根据来自那些`Home_States`的记录计算的。 `HAVING`子句管理`AvgMiddleAge computed`字段,从指定`Home_States`的记录中计算40岁以上的人的平均年龄。 因此,`AvgAge`和`AvgMiddleAge`的每一行都有相同的值。 `ORDER BY`子句按照`Home_State`字段值的字母顺序对行进行显示。 ```sql SELECT Name,Home_State,Age,AVG(Age) AS AvgAge, AVG(Age %AFTERHAVING) AS AvgMiddleAge FROM Sample.Person WHERE Home_State IN ('ME','NH','VT','MA','RI','CT','NY') HAVING Age > 40 ORDER BY Home_State ``` ## GROUP BY/HAVING/ORDER BY `GROUP BY`子句导致对每个`Home_State`组分别计算`AvgAge computed`字段。 `GROUP BY`子句还将输出显示限制为从每个`Home_State`遇到的第一个记录。 `HAVING`子句管理`AvgMiddleAge computed`字段,计算每个`Home_State`组中40岁以上人群的平均年龄。 `ORDER BY`子句按照`Home_State`字段值的字母顺序对行进行显示。 ```sql SELECT Name,Home_State,Age,AVG(Age) AS AvgAge, AVG(Age %AFTERHAVING) AS AvgMiddleAge FROM Sample.Person GROUP BY Home_State HAVING Age > 40 ORDER BY Home_State ``` ## WHERE/GROUP BY/HAVING/ORDER BY `WHERE`条款限制了对东北部七个州的选择。 `GROUP BY`子句导致对这七个`Home_State`组中的每一个单独计算`AvgAge computed`字段。 `GROUP BY`子句还将输出显示限制为从每个指定的`Home_State`遇到的第一个记录。 `HAVING`子句管理`AvgMiddleAge computed`字段,计算7个`Home_State`组中每个组中40岁以上人群的平均年龄。 `ORDER BY`子句按照`Home_State`字段值的字母顺序对行进行显示。 ```sql SELECT Name,Home_State,Age,AVG(Age) AS AvgAge, AVG(Age %AFTERHAVING) AS AvgMiddleAge FROM Sample.Person WHERE Home_State IN ('ME','NH','VT','MA','RI','CT','NY') GROUP BY Home_State HAVING Age > 40 ORDER BY Home_State ``` ## 嵌入式SQL和动态SQL示例 嵌入式SQL和动态SQL可用于从ObjectScript程序中发出`SELECT`查询。 下面的嵌入式SQL程序从一条记录中检索数据值,并将它们放在`INTO`子句中指定的输出主机变量中。 ```sql ClassMethod Select2() { n SQLCODE,%ROWCOUNT &sql( SELECT Home_State, Name, Age INTO :a, :b, :c FROM Sample.Person) if SQLCODE=0 { w !," Name=",b w !," Age=",c w !," Home State=",a w !,"Row count is: ",%ROWCOUNT } else { w !,"SELECT 失败, SQLCODE=",SQLCODE } } ``` ```sql DHC-APP>d ##class(PHA.TEST.SQLCommand).Select2() Name=yaoxin Age=31 Home State=WI Row count is: 1 ``` 这个程序检索(最多)一行,因此`%ROWCOUNT`变量被设置为0或1。 要检索多行,必须声明游标并使用`FETCH`命令。 下面的动态SQL示例首先测试所需的表是否存在,并检查当前用户对该表的`SELECT`特权。 然后执行查询并返回结果集。 它使用`WHILE`循环对结果集的前10条记录重复调用`%Next`方法。 它使用`%GetData`方法显示三个字段值,这些方法指定了`SELECT`语句中指定的字段位置: ```sql ClassMethod Select3() { #; s tname="Sample.Person" #; if $SYSTEM.SQL.TableExists(tname) & $SYSTEM.SQL.CheckPrivilege($USERNAME, "1," _ tname, "s"){ #; GOTO SpecifyQuery #; } else { #; w "Table unavailable" #; q #; } #;SpecifyQuery s myquery = 3 s myquery(1) = "SELECT Home_State,Name,SSN,Age" s myquery(2) = "FROM Sample.Person" s myquery(3) = "ORDER BY Name" s tStatement = ##class(%SQL.Statement).%New() s qStatus = tStatement.%Prepare(.myquery) if qStatus '= 1 { w "%Prepare failed:" d $System.Status.DisplayError(qStatus) q } s rset = tStatement.%Execute() if rset.%SQLCODE=0 { s x=0 while x < 10 { s x = x + 1 s status=rset.%Next() w rset.%GetData(2)," " /* Name field */ w rset.%GetData(1)," " /* Home_State field */ w rset.%GetData(4),! /* Age field */ } w !,"End of Data" w !,"SQLCODE=",rset.%SQLCODE," Row Count=",rset.%ROWCOUNT } else { w !,"SELECT 失败, SQLCODE=",rset.%SQLCODE } } ```