informix 參考網站

infoemix sql 彙總
informix 資料庫語法
informix sql 語法
資料形態選擇圖
cognos評估

informix SQL 語句詳解(一)

informix SQL 語句詳解(一)


1.CREATE DATABASE database_name [WITH LOG IN “pathname”] 建立資料庫。

database_name：資料庫名稱。
“pathname”：事務處理日誌文件。

建立一database_name.dbs目錄，存取權限由GRANT設定，無日誌文件就不能使用
BEGIN WORK等事務語句（可用START DATABASE語句來改變）。
可選定當前資料庫的日誌文件。
如：select dirpath form systables where tabtype = “L”;

例：create databse customerdb with log in “/usr/john/log/customer.log”;

2.DATABASE databse-name [EXCLUSIVE] 選擇資料庫。

database_name：資料庫名稱。
EXCLUSIVE：獨佔狀態。
存取當前目錄和DBPATH中指定的目錄下的資料庫，事務中處理過程中不要使用此語句。

例：dtabase customerdb;

3.CLOSE DATABASE關閉當前資料庫。
 
database_name：資料庫名稱。
此語句之，只有下列語句合法：
CREATE DATABASE； DATABASE；
DROP DATABSE； ROLLFORWARD DATABASE；

刪除資料庫前必須使用此語句。

例：close database; 

4.DROP DATABASE database_name 刪除指定資料庫。
database_name：資料庫名稱。
用戶是DBA或所有表的擁有者；刪除所有文件，但不包括資料庫目錄；不允許刪除當前數據
庫（須先關閉當前資料庫）；事務中處理過程中不能使用此語句，通過ROLLBACK WORK 也
不可將資料庫恢復。

例：drop databse customerdb;

5.CREATE [TEMP] TABLE table-name (column_name datatype [NOT NULL], …)[IN “pathname”]建立表或臨時表。

table-name ：表名稱。
column_name：字段名稱。
data-type：字段數據類型。
path-name：指定表的存放位置
TEMP用指定建立臨時表；表名要唯一，字段要唯一；有CONNECT權限的用戶可建立臨時表；
建立的表缺省允許CONNECT用戶存取，但不可以ALTER。

例：create table user
( c0 serial not null, c1 char (10),

c2 char(2),
c3 smallint,
c4 decimal(6,3),
c5 date

) in “usr/john/customer.dbs/user;

6.ALTER TABLE 修改表結構。

ALTER TABLE table-name
{ADD (newcol_name newcol_type [BEFORE oldcol_name], …) | DROP (oldcol_name, …)
| MODIFY (oldcol_name newcol_type [NOT NULL], … )}, …

table-name：表名稱。
newcol_name：新字段名稱
newcol_type：新字段類型
oldcol_name：老字段名稱
可以使用單個或多個ADD子句、DROP子句、MODIFY子句，但某個字句失敗，操作即中止；原字
段是NULL，不允許MODIFY為NOT NULL，除非所有NULL字段中均非空，反之可以；ALTER使用者
是表的擁有者或擁有DBA權限，或被授權；事務中處理過程中不要使用此語句。

例：alter table user

add ( c6 char(20) before c5); 

7.RENAME TABLE oldname TO newname 修改表名。

oldname：原名稱。
newname：新名稱。

RENAME使用者是表的擁有者或擁有DBA權限，或被授權；事務中處理過程中不要使用此語句。

例：rename user to bbb;

8.DROP TABLE table-name 刪除表。

table-name：表名稱。
刪除表意味著刪除其中所有數據、各欄位上的索引及對表的賦權、視圖等；用戶不能刪除任何
系統目錄表；語句使用者是表擁有者或擁有DBA權限，事務中處理過程中不要使用此語句。

9.RENAME COLUMN table.oldcolumn， TO newcolumn 修改欄位名稱

table.oldcolumn：表名及原欄位名稱
newcolumn：新欄位名稱。

語句使用者是表的擁有者或擁有DBA權限或有ALTER權限的用戶，事務中處理過程中不要使用此語句。

例：rename column user.c6 to c7;

10.CREATE VIEW view-name column-list 建立視圖。

CREATE VIEW view-name column-list AS select_statement [WITH CHECK OPTION]

view-name：視圖名稱。
column-list：欄位列表。
select_statement：SELECT語句。

以下語句不使用視圖：ALTER TABLE，DROP INDEX，ALTER INDEX，LOCK TABLE，CREATE INDEX，
RENAME TABLE；視圖將延用基表的欄位名，對表達式等虛欄位和多表間欄位重名必須指明標識其
欄位名；若對視圖中某些欄位命名，則所有欄位都必須命名；視圖中數據類型延用基表中的數據
類型，虛欄位起訣表達式；不能使用ORDER BY和UNION子句；對視圖中所有的欄位要有SELECT權
限；事務中處理過程中使用此語句，即使事務回滾，視圖也將建立，不能恢復。

例：create view v_user as select * from user where c1 = “B1”;

11. DROP VIEW view-name 刪除視圖。

view-name：視圖名稱。
用戶可刪除自己建立的視圖；視圖的代視圖也被刪除；事務中處理中不要使用此語句。
例：drop view v_user;  

12. CREATE INDEX 建立索引。

CREATE [UNIQUE/DISTINCT] [CLUSTER] INDEX index_name ON table_name

([column_name ASC/DESC],…)

index_name：索引名稱。
table_name：表名稱。
column_name：欄位名稱。

UNIQUE/DISTINCT：唯一索引。
CLUSTER：使表的物理存放順序按索引排列。
ASC/DESC：升序或降序，缺省升序。

語句執行時，將表的狀態置為EXCLUSIVE；復合索引最多包含8個欄位，所有欄位長度和不
得大120字節；事務中處理過程中使用此語句，即使事務回滾，索引將建立，不能恢復。

例：create cluster index ix_user on user(c5);

Informix SQL函數的詳細用法

一、內部函數
　 1、內部合計函數
　　　 1）COUNT（*）　　　　　　　　　　返回行數
　　　 2）COUNT（DISTINCT COLNAME）　　 返回指定列中唯一值的個數
　　　 3）SUM（COLNAME/EXPRESSION）　　 返回指定列或運算式的數值和；
　　　 4）SUM（DISTINCT COLNAME）　　　 返回指定列中唯一值的和
　　　 5）AVG（COLNAME/EXPRESSION）　　 返回指定列或運算式中的數值平均值
　　　 6）AVG（DISTINCT COLNAME）　　　 返回指定列中唯一值的平均值
　　　 7）MIN（COLNAME/EXPRESSION）　　 返回指定列或運算式中的數值最小值
　　　 8）MAX（COLNAME/EXPRESSION）　　 返回指定列或運算式中的數值最大值
　 2、日期與時間函數
　　　 1）DAY（DATE/DATETIME EXPRESSION）　　　返回指定運算式中的當月幾號
　　　 2）MONTH（DATE/DATETIME EXPRESSION）　　返回指定運算式中的月份
　　　 3）YEAR（DATE/DATETIME EXPRESSION）　　 返回指定運算式中的年份
　　　 4）WEEKDAY（DATE/DATETIME EXPRESSION）　返回指定運算式中的當周星期幾
　　　 5）DATE（NOT DATE EXPRESSION）　　　　　返回指定運算式代表的日期值
　　　 6）TODAY　　　　　　　　　　　　　　　　返回當前日期的日期值
　　　 7）CURRENT[first to last]　　　　　　　 返回當前日期的日期時間值
　　　 8）COLNAME/EXPRESSION UNITS PRECISION　 返回指定精度的指定單位數
　　　 9）MDY（MONTH，DAY，YEAR）　　　　　　　返回標識指定年、月、日的日期值
　　　 10）DATETIME（DATE/DATETIME EXPRESSION）FIRST TO LAST 返回運算式代表的日期時間值
　　　 11）INTERVAL（DATE/DATETIME EXPRESSION）FIRST TO LAST 返回運算式代表的時間間隔值
　　　 12）EXTEND（DATE/DATETIME EXPRESSION，[first to last]）返回經過調整的日期或日期時間值
　　　 例子1、和UNITS合用，指定日期或時間單位(year,month,day,hour,minute,seond,fraction)：
　　　　　　　let tmp_date = today + 3 UNITS day
　　　 例子2、let tmp_date = MDY(10,30,2002)　　-- 2002-10-30
　　　 例子3、let tmp_date = today + interval(7) day to day　--當前時間加上7天；
　　　　　　　注：該功能與1相似；
　　　 例子4、EXTEND轉換日期或日期時間值
　　　　　　　let tmp_inthour = extend(datetime1,hour to hour)
　 3、代數函數
　　　1）ABS(COLNAME/EXPRESSION)：　　　　　　 取絕對值
　　　2）MOD（COLNAME/EXPRESSION，DIVISOR）　　返回除以除數後的模（餘數）
　　　3）POW（COLNAME/EXPRESSION，EXPONENT）　 返回一個值的指數冥
　　　　 例子：let tmp_float = pow(2,3)　--8.00000000
　　　4）ROOT（COLNAME/EXPRESSION，[index]）　 返回指定列或運算式的根值
　　　5）SQRT（COLNAME/EXPRESSION）　　　　　　返回指定列或運算式的平方根值
　　　6）ROUND（COLNAME/EXPRESSION，[factor]） 返回指定列或運算式的圓整化值
　　　7）TRUNC（COLNAME/EXPRESSION，[factor]） 返回指定列或運算式的截尾值
　　　　 說明：上兩者中FACTOR指定小數位數，若不指定，則為0；若為負數，則整化到小數點左邊；
　　　　 注：ROUND是在指定位上進行4舍5入；TRUNC是在指定位上直接截斷；
　　　　　let tmp_float = round(4.555,2) --4.56
　　　　　let tmp_float = trunc(4.555,2) --4.55
　 4、指數與對數函數
　　　1）EXP（COLNAME/EXPRESSION）　　　　返回指定列或運算式的指數值
　　　2）LOGN（COLNAME/EXPRESSION）　　　 返回指定列或運算式的自然對數值
　　　3）LOG10（COLNAME/EXPRESSION）　　　返回指定列或運算式的底數位10的對數值
　 5、三角函數
　　　1）COS（RADIAN EXPRESSION）　　　　　返回指定弧度運算式的余弦值
　　　2）SIN（RADIAN EXPRESSION）　　　　　正弦
　　　3）TAN（RADIAN EXPRESSION）　　　　　正切
　　　4）ACOS（RADIAN EXPRESSION）　　　　 反余弦
　　　5）ASIN（RADIAN EXPRESSION）　　　　 反正弦
　　　6）ATAN（RADIAN EXPRESSION）　　　　 反正切
　　　7）ATAN2（X，Y）　　　　　　　　　　 返回座標（X，Y）的極座標角度組件
　 6、統計函數
　　　1）RANGE（COLNAME）　　　 返回指定列的最大值與最小值之差 = MAX（COLNAME）-MIN
（COLNAME）
　　　2）VARIANCE（COLNAME）　　返回指定列的樣本方差；
　　　3）STDEV（COLNAME）　　　 返回指定列的標準偏差；
　 7、其他函數
　　　1）USER　　　　　　　　　　　　　　返回當前用戶名
　　　2）HEX（COLNAME/EXPRESSION）　　　 返回指定列或運算式的十六進位值
　　　3）LENGTH（COLNAME/EXPRESSION）　　返回指定字元列或運算式的長度
　　　4）TRIM（COLNAME/EXPRESSION）　　　刪除指定列或運算式前後的字元
　　　5）COLNAME/EXPRESSION || COLNAME/EXPRESSION　返回並在一起的字元；
二、IDS內部函數
　 1、DBSERVERNAME　　　返回資料庫伺服器名　let tmp_char=DBSERVERNAME
　 2、SITENAME　　　　　返回資料庫伺服器名　let tmp_char=SITENAME
　　　說明：兩者功能相同；
　 3、DBINFO（‘SPECIAL_KEYWORD')　　　返回只關鍵字值
　　　例子1：返回資料中每個表的DBSPACE名稱
　　　　 select dbinfo('dbspace',partnum),tabname from systables
　　　　　where tabid>99 and tabtype='T'　(OK)
　　　例子2：返回任何表中插入的最後一個SERIAL值
　　　　　select dbinfo('sqlca.sqlerrd1') from systables where tabid = 1
　　　例子3：返回最後一個SELECT，INSERT，UPDATE，DELETE或EXECUTE PROCEDURE語句處理的行數；
　　　　　select dbinfo('sqlca.sqlerrd2') from systables where tabid=1;