分类： DB2/Informix
2008-05-31 18:24:37
SQL 内置函数
一条 SQL 语句不仅包含操作符和列名，还包含一个或多个函数。通过使用函数，可以执行更加复杂的数据库查询和数据操作。您可以使用提供的内置函数，或编写自己的函数。本节将介绍一些内置函数，包括聚合函数、时间函数、数据转换函数、基数函数、智能大对象函数、字符串操作函数、数据加密函数和其他函数。将针对查询中的每一行计算函数表达式。每个函数都需要提供参数。
以下是 5 种常见内置函数：聚合通常用于描述有关表行的信息。
Informix Dynamic Server 支持以下聚合函数：
AVG
COUNT
MAX
MIN
RANGE
STDEV
SUM
VARIANCE
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 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 |
SELECT、UPDATE 或 INSERT 语句中。
FILETOCLOB()
FILETOBLOB()
LOCOPY()
LOTOFILE()
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 使用大写字母替换字符串中的第一个字符。
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 |
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 ----- |
LENGTH
USER
CURRENT
TODAY
DBSERVERNAME
HEX
DBINFO
DECODE
NVL
TODAY 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 |
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 |
其结果如下所示：
name (expression) Joe Miller Joey Susan Penrose Sue Jim Stevens Jimmy Robert Barker no nickname |
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 时由函数返回。