#
第七十二章 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
}
}
```