Informix IDS 11系统管理(918考试)认证指南,第8部分:面向管理员的SQL特性(2) - informix

SQL 内置函数

一条 SQL 语句不仅包含操作符和列名，还包含一个或多个函数。通过使用函数，可以执行更加复杂的数据库查询和数据操作。您可以使用提供的内置函数，或编写自己的函数。本节将介绍一些内置函数，包括聚合函数、时间函数、数据转换函数、基数函数、智能大对象函数、字符串操作函数、数据加密函数和其他函数。将针对查询中的每一行计算函数表达式。每个函数都需要提供参数。

以下是 5 种常见内置函数：

聚合函数
时间函数
智能大对象函数
字符串操作函数
其他函数

聚合函数

聚合通常用于描述有关表行的信息。

Informix Dynamic Server 支持以下聚合函数：

AVG
COUNT
MAX
MIN
RANGE
STDEV
SUM
VARIANCE

Listing清单 1. 聚合函数示例 - MAX

                    
SELECT item_num, MAX (quantity) FROM items GROUP BY item_num

Selects the item_num together with the maximum quantity ordered 

item_num  (max)

       1     10
       2     10
       3      5
       4      4
       5      1
       6      1

时间函数

您可以使用以下的内置时间函数：

DAY
MDY
MONTH
WEEKDAY
YEAR
CURRENT
EXTEND

清单 2. 时间函数示例 - DATE

                    
Query to select the resolution time in days for all resolved calls together 
with the user_id of the person who logged the call.

SELECT user_id, DATE (call_dtime) called, DATE (res_dtime) resolved,
(DATE(res_dtime) - DATE (call_dtime)) restime
FROM cust_calls
WHERE (DATE(res_dtime) - DATE (call_dtime)) >= 0;

user_id            called     resolved       restime

maryj              06/12/1998 06/12/1998        0
richc              07/07/1998 07/07/1998        0
richc              07/01/1998 07/02/1998        1
maryj              07/10/1998 07/10/1998        0
mannyn             11/28/1997 11/28/1997        0
mannyn             12/21/1997 12/27/1997        6

智能大对象函数

IDS 提供了四个函数供您导入和导出智能大对象。这些函数可用于任何 SELECT、UPDATE 或 INSERT 语句中。

FILETOCLOB()
FILETOBLOB()
LOCOPY()
LOTOFILE()

清单 3. 智能大对象函数示例 - FILETOBLOB()

                    
Assume to create a table like the following:

CREATE TABLE pictab
(
 number integer,
 picture BLOB
);

Insert a file picture.jpg that is located on the client computer into this table

INSERT INTO pictab VALUES (1, FILETOBLOB ('c:\temp\picture1.jpg' , 'client'));

字符串操作函数

字符串操作函数接受 CHAR、VARCHAR、LVARCHAR、NCHAR 和 NVARCHAR 类型的参数。

IDS 支持以下内置函数：

LOWER
UPPER
INITCAP
REPLACE
SUBSTR
SUBSTRING
LPAD
RPAD

LOWER 和 UPPER 转换大小写字母，其中 INITCAP 使用大写字母替换字符串中的第一个字符。

清单 4. 字符串操作函数示例 - INITCAP

                    
SELECT description, INITCAP(description) FROM stock WHERE stock_num < 5;

description     (expression)

baseball gloves Baseball Gloves
baseball gloves Baseball Gloves
baseball gloves Baseball Gloves
baseball        Baseball
baseball bat    Baseball Bat
baseball bat    Baseball Bat
football        Football
football        Football

清单 5. 字符串操作示例 - RPAD

                    
The first five positions to the right of this row that have a length of 15 
are padded with -

SELECT RPAD (lname, 20, '-') FROM customer

(expression)

Pauli          -----
Sadler         -----
Currie         -----
Higgins        -----
Vector         -----
Watson         -----
Ream           -----
Quinn          -----
Miller         -----
Jaeger         -----
Keyes          -----
Lawson         -----
Beatty         -----
Albertson      -----
Grant          -----
Parmelee       -----

其他函数

Informix Dynamic Server 中可用的其他函数包括：

LENGTH
USER
CURRENT
TODAY
DBSERVERNAME
HEX
DBINFO
DECODE
NVL
TODAY

DECODE 可用来转换值的表示方法。

清单 6. DECODE 函数示例

                    
SELECT name, evaluation, DECODE (evaluation, 'poor', 0, 'fair', 25, 'good' 50, 
'very good', 75, 'excellent', 100)

Name 	                 evaluation                   expression
John		             0                            poor
Brian                    100                          excellent
Susan                    75                           very good

清单 7. 示例

                    
SELECT name, nickname FROM persons 

Gives the following result

name                 nickname

Joe Miller           Joey
Susan Penrose        Sue
Jim Stevens          Jimmy
Robert Barker        NULL

如果没有昵称，要想获得字符串 “no nickname”，可以使用 NVL 函数：

 
SELECT name, NVL(nickname, 'no nickname') FROM PERSONS

其结果如下所示：

清单 8. NVL 函数结果

                    
name                 (expression)

Joe Miller           Joey
Susan Penrose        Sue
Jim Stevens          Jimmy
Robert Barker        no nickname

清单 9. TODAY 函数示例

                    
Update the ship_date in orders to the current date for order with order_num = 1002

UPDATE orders SET ship_date = TODAY where order_num = 1002

NVL 可用于对计算结果为 NULL 的表达式进行转换，将结果转换为您指定的值。

NVL 函数包含两个值。第一个值指定要进行转换的列；第二个值在函数结果不为 NULL 时由函数返回。