文章目錄
- 第七十章 SQL指令 SELECT(二)
- select-item
select-item
這是所有
SELECT
語句的必選元素。
通常,選擇項指的是
FROM
子句中指定的表中的一個字段。
選擇項由下列一個或多個項組成,多個項之間用逗号分隔:
- 列名(字段名),帶或不帶表名别名:
SELECT Name,Age FROM Sample.Person
字段名不區分大小寫。
但是,結果集中與字段關聯的标簽使用表定義中指定的
SqlFieldName
的字母大小寫,而不是選擇項中指定的字母大小寫。
包含一個或多個下劃線的字段名引用嵌入的串行對象屬性。
例如,對于字段名
Home_City
,表包含一個引用字段
Home
,該字段引用定義屬性
City
的嵌入式串行對象。
對于字段名
Home_Phone_AreaCode
,該表包含一個引用字段
Home
,該字段引用嵌入式串行對象屬性
Phone
,該屬性引用定義
AreaCode
屬性的嵌套嵌入式串行對象。
如果選擇一個引用字段,如
Home
或
Home_Phone
,則以
%List
資料類型格式接收串行對象中所有屬性的值。
要顯示
RowID
(記錄
ID
),可以使用
%ID
僞字段變量别名,該别名顯示
RowID
,而不管它被配置設定的名稱是什麼。
預設情況下,
RowID
的名稱是
ID
,但如果存在使用者定義的名為
ID
的字段, IRIS可能會重命名它。
RowID
是一個隐藏字段。
stream
字段上的
SELECT
傳回打開的
stream
對象的
oref
(對象引用):
SELECT Name,Picture FROM Sample.Employee WHERE Picture IS NOT NULL
當
FROM
子句指定多個表或視圖時,必須使用句點将表名(或表名别名)作為選擇項的一部分,如下面的兩個示例所示:
SELECT Sample.Person.Name,Sample.Employee.Company
FROM Sample.Person, Sample.Employee
表名别名:
SELECT p.Name, e.Company
FROM Sample.Person AS p, Sample.Employee AS e
但是,如果已為該表名配置設定了别名,則不能将完整表名作為選擇項的一部分。
嘗試這樣做會導緻
SQLCODE -23
錯誤。
可以使用排序函數指定選擇項字段的排序和顯示。
可以提供不帶括号(
SELECT %SQLUPPER Name
)或帶括号(
SELECT %SQLUPPER(Name)
)的排序規則函數。
如果排序規則函數指定了截斷,則括号是必需的(
SELECT %SQLUPPER(Name,10)
)。
當選擇項引用嵌入的串行對象屬性(嵌入的串行類資料)時,使用下劃線文法。
下劃線文法由對象屬性的名稱、下劃線和嵌入對象中的屬性組成:例如,
Home_City
和
Home_State
。
(在其他上下文中,例如索引表,它們使用點文法表示:
Home.City
。)
SELECT Home_City,Home_State FROM Sample.Person
可以使用SELECT直接查詢引用字段(例如
Home
),而不是使用下劃線文法。
因為傳回的資料是清單格式的,是以可能需要使用
$LISTTOSTRING
$LISTGET
函數來顯示資料。
例如:
SELECT $LISTTOSTRING(Home,'^') AS HomeAddress FROM Sample.Person
-
子查詢。
子查詢傳回指定表中的單個列。
這個列可以是單個表字段(
)的值,也可以是作為單個列傳回的多個表字段的值,可以使用連接配接(SELECT Name
)或指定容器字段(SELECT Home_City||Home_State
SELECT Home
子查詢可以使用隐式連接配接(箭頭文法)。
子查詢不能使用星号文法,即使在子查詢中引用的表隻有一個資料字段。
子查詢的一個常見用法是指定不受
GROUP BY
子句限制的聚合函數。
在下面的示例中,
GROUP BY
子句按幾十年(例如,
25
到
34
)對年齡進行分組。
AVG(Age)
選擇項給出了由
group by
子句定義的每個組的平均年齡。
為了獲得所有組中所有記錄的平均年齡,它使用了一個子查詢:
SELECT Age AS Decade,
COUNT(Age) AS PeopleInDecade,
AVG(Age) AS AvgAgeForDecade,
(SELECT AVG(Age) FROM Sample.Person) AS AvgAgeAllDecades
FROM Sample.Person
GROUP BY ROUND(Age,-1)
ORDER BY Age
- 箭頭文法,用于通路
from
子句表以外的表中的字段。
這被稱為隐式連接配接。
在下面的示例中,示例。
表包含Employee
字段,其中包含Company
中對應公司名稱的Sample
RowID
公司表。
箭頭文法從表中檢索公司名稱:
SELECT Name,Company->Name AS CompanyName
FROM Sample.Employee
在這種情況下,必須擁有被引用表的
SELECT
特權:對于被引用表的字段和
RowID
列,可以是表級的
SELECT
特權,也可以是列級的
SELECT
特權。
- 星号文法(
),按列号順序選擇表中的所有列:*
SELECT TOP 5 * FROM Sample.Person
星号文法選擇嵌入的串行對象屬性(字段),包括嵌套在串行對象中的串行對象的屬性。
沒有選擇引用串行對象的字段。
例如,選擇來自嵌入式串行對象的
Home_City
屬性,但是使用
Home
引用字段通路
Sample
沒有選擇位址嵌入串行類(包含City屬性)。
星号文法不選擇隐藏字段。
RowID
是隐藏的(不通過
SELECT *
顯示)。
但是,如果表定義為
%PUBLICROWID
,則
SELECT *
傳回
RowID
字段和所有非隐藏字段。
預設情況下,該字段的名稱為
ID
,但如果存在使用者自定義的
ID
字段,IRIS可能會對其進行重命名。
在下面的示例中,
select-item
由一個非限定星号文法組成,用于從表中選擇所有列。
注意,你也可以指定重複的列名(在本例中是
Name
)和非列的
select-item
元素(在本例中是
{fn NOW}
):
SELECT TOP 5 {fn NOW} AS QueryDate,
Name AS Client,
*
FROM Sample.Person
select-item
由限定星号文法組成,用于從一個表中選擇所有列,以及從另一個表中選擇列名清單。
SELECT TOP 5 E.Name AS EmpName,
C.*,
E.Home_State AS EmpState
FROM Sample.Employee AS E, Sample.Company AS C
注意:
SELECT *
是 SQL完全支援的一部分,在應用程式開發和調試期間,它非常友善。
但是,在生産應用程式中,首選的程式設計實踐是顯式列出所選字段,而不是使用星号文法形式。
顯式列出字段可以使應用程式更清晰、更容易了解、更容易維護,并更容易按名稱搜尋字段。
- 包含一個或多個SQL聚合函數的選擇項。 聚合函數總是傳回單個值。 聚合函數的參數可以是下列任何一種:
- 單個列名計算查詢所選行的所有非空值的聚合:
SELECT AVG(Age) FROM Sample.Person
- 也允許使用标量表達式來計算聚合:
SELECT SUM(Age) / COUNT(*) FROM Sample.Person
- 星号文法(
)-與COUNT函數一起使用,用于計算表中的行數:*
SELECT COUNT(*) FROM Sample.Person
- 一個選擇不同的函數-通過消除備援值計算聚合:
SELECT COUNT(DISTINCT Home_State) FROM Sample.Person
- 雖然ANSI SQL不允許在單個
語句中組合列名和聚合函數,但SQL擴充了這一标準,允許這樣做:SELECT
SELECT Name, COUNT(DISTINCT Home_State) FROM Sample.Person
```sql
- 使用`%FOREACH`的聚合函數。
這将導緻對一個或多個列的每個不同值計算聚合:
SELECT DISTINCT Home_State, AVG(Age %FOREACH(Home_State))
FROM Sample.Person
- 使用
%AFTERHAVING
的聚合函數。
這導緻在
子句指定的子HAVING
上計算聚合:population
SELECT Name,AVG(Age %AFTERHAVING)
FROM Sample.Person
HAVING (Age > AVG(Age))
将傳回年齡大于平均年齡的那些記錄,給出年齡高于資料庫中所有人平均年齡的那些人的平均年齡。
-
文法,支援基于特定于該行的“視窗架構”為每一行計算聚合、排名和其他函數。 支援以下文法Window function
window-function() OVER (PARTITION BY partfield ORDER BY orderfield)
-
:支援如下視窗函數:window-function
, ROW_NUMBER()
RANK()
PERCENT_RANK()
FIRST_VALUE(field)
SUM(field)
-
:必須在OVER
OVER
關鍵字後面加上括号。
括号中的子句是可選的。
-
:可選子句,根據指定的PARTITION BY partfield
partfield
對行進行分區。
部分字段可以是單個字段,也可以是用逗号分隔的字段清單。
如果指定了,
必須在PARTITION BY
之前指定。ORDER BY
-
ORDER BY orderfield
對行進行排序。orderfield
可以是單個字段,也可以是逗号分隔的字段清單。Orderfield
在
Window function
中指定的字段可以接受表别名字首。
Window function
可以指定列别名。
預設情況下,列被标記為
Window_n
-
作為過程存儲的使用者定義的類方法。
可以是非限定方法名,也可以是限定方法名。
以下是所有有效的類方法名:
Sample.RandLetter()
一個限定的類方法名;
和
調用類方法Sample.Rand_Letter()
在下面的例子中,“Rand_Letter”()
是一個傳回随機字母的類方法,可以是大寫字母(RandCaseLetter()
),也可以是小寫字母(' U '
'L'
SELECT RandCaseLetter('U')
該方法的傳回值将自動從
Logical
格式轉換為
Display/ODBC
格式。
預設情況下,該方法的輸入值不會從
Display/ODBC
Logical
但是,可以使用
$SYSTEM.SQL.Util.SetOption("SQLFunctionArgConversion")
方法在系統範圍内配置輸入顯示到邏輯的轉換。
可以使用
$SYSTEM.SQL.Util.GetOption("SQLFunctionArgConversion")
來确定該選項的目前配置。
如果指定的方法在目前名稱空間中不存在,系統将生成
SQLCODE -359
如果指定的方法不明确(可能引用多個方法),系統将生成
SQLCODE -358
- 對資料庫列進行操作的使用者提供的
函數調用(外部函數):ObjectScript
SELECT $$REFORMAT(Name)FROM MyTable
如果在系統範圍内配置了“允許SQL語句中的外部函數”選項,則隻能在SQL語句中調用使用者提供的(外部)函數。
預設為“
No
”;
預設情況下,嘗試調用使用者提供的函數會産生
SQLCODE -372
不能使用使用者提供的函數來調用
%routine
(以
%
字元開頭的例程)。
嘗試這樣做會産生
SQLCODE -373
- 對字段值應用額外處理的選擇項:
算術運算:
SELECT Name, Age,Age-AVG(Age) FROM Sample.Person
如果選擇項算術運算包括除法,并且資料庫中該字段的任何值都可能産生值為零或
NULL
的除數,則不能依賴測試順序來避免被零除法。
相反,使用
case
語句來抑制風險。
SQL函數:
SELECT Name,$LENGTH(Name) FROM Sample.Person
SQL
case
轉換函數:
SQL case轉換函數:
一個
XMLELEMENT
、
XMLFOREST
XMLCONCAT
函數,它在從指定列名檢索的資料值周圍放置
XML
(或HTML)标記。
- 為所有記錄傳回相同值的選擇項。當所有
都不引用表資料時,select-items
子句是可選的。 如果包含FROM
子句,則指定的表必須存在。FROM
SELECT 7 * 7, 7 * 8 FROM Sample.Person
SELECT Name, Age, 9 - 6 FROM Sample.Person
- 字元串字面值或操作字元串字面值的函數:
SELECT UCASE('fred') FROM Sample.Person
字元串字面量可以用來産生更可讀的輸出,如下面的示例所示:
SELECT TOP 10 Name,'was born on',%EXTERNAL(DOB)
FROM Sample.Person
-
%TABLENAME
僞字段變量關鍵字。%CLASSNAME
傳回目前表名。%TABLENAME
%CLASSNAME
傳回目前表對應的類名。
如果查詢引用多個表,可以在關鍵字前加上表别名。
例如,
t1.%TABLENAME
- 以下ObjectScript特殊變量之一(或其縮寫):
$HOROLOG
$JOB
$NAMESPACE
$TLEVEL
$USERNAME
$ZHOROLOG
$ZJOB
$ZNSPACE
$ZPI
$ZTIMESTAMP
$ZTIMEZONE
$ZVERSION