【symfoware NATIVE】数据库操作

浏览数：197 / 时间：2015年06月12日

Symfoware NATIVE系

一、共同要素的语法法则
字符：数字（0~9）、英字（a~z、A~Z）、特殊字符(，、（、=、%)、扩展文字(@、￥、#)、各国字符；
常量：字符串常量、各国语字符串常量、日期时间常量、时间间隔常量、带符号常量；
注释：“--”或“/*...*/”；

二、数据类型
字符串类型：
CHARACTER(n)
CHAR(n)
CHARACTER VARYING(n)
CHAR VARYING(n)
VARCHAR(n)
n:1～32000，默认为1；

各国语字符串类型：
NATIONAL CHARACTER(n)
NATIONAL CHAR(n)
NCHAR(n)

NATIONAL CHARACTER VARYING(n)
NATIONAL CHAR VARYING(n)
NCHAR VARYING(n)
n:1～32000，默认为1；

整数：
NUMERIC(p,q)
10进制数；
p:1～18 q:0～p

DECIMAL(p,q)
DEC(p,q)
10进制数；
p:1～18 q:0～p

INTEGER
INT
-2的31次方～2的31次方-1的整数

SMALLINT -2的15次方～2的15次方-1的整数

浮点数：
FLOAT(p)
尾数为-2的p次方～2的p次方的概数
p:1～52
p=１～23时对应REAL
p=24～52时对应DOUBLE PRECISION
REAL 四字节浮点数
DOUBLE PRECISION 8字节浮点数

日期时间：
DATE 年月日 10字节存储
TIME 小时分钟秒 8字节存储
TIMESTAMP 19字节时间戳

BLOB：
BINARY LARGE
OBJECT(n单位)
BLOB(n单位)
二进制存储，单位为K、M、G
单位不可省略
K) n:1 ～ 2097152
M) n:1 ～ 2048
G) n:1 或 2

n:位数
p:精度
q：定位数

三、集合函数
COUNT（*）、AVG、MAX、MIN、SUM、COUNT、DISTINCT、ALL

四、数值函数
POSITION式、EXTRACT式、LENGTH式、ABS式、CEIL、FLOOR式、ROUND式、TRUNC式、SPAN_DATE函数、
ACOS函数、ASIN函数、ATAN函数、ATAN2函数、COS函数、EXP函数、LN函数、POWER函数、SIGN函数、SIN函数、SQRT函数、TAN函数、ASCII函数。

五、数据列值函数
SUBSTRING或OCTET_SUBSTRING（字符串截取函数）、UPPER或LOWER（大小写转换）、ＴＲＩＭ函数（删除指定字符）、LTRIM函数、RTRIM函数、LPAD式、RPAD式、REPLACE式、REPLICATE式、REVERSE式、CNV_CHAR函数、CHR函数、

七、日期时间函数
CURRENT DATE值函数：返回当前日期（2014-12-11）；
CURRENT TIME值函数：返回当前时刻（15:58:57）；
CURRENT TIMESTAMP值函数：返回当前时刻（2007-04-10 15:58:59）；
ROUND_DATE函数：（不常用）
TRUNC_DATE：根据时间和参数获得一个开始时间
示例：
TRUNC_DATE(DATE‘2007-08-22‘,‘YEAR‘) →‘2007-01-01‘
TRUNC_DATE(DATE‘2007-02-15‘,‘JHALF‘) →‘2006-10-01‘

ADD_DATE函数：增加时间间隔后返回
示例：
ADD_DATE(DATE‘2006-08-28‘,6,‘MONTH‘) →‘2007-02-28‘
ADD_DATE(DATE‘2007-08-31‘,1,‘MONTH‘) →‘2007-09-30‘
ADD_DATE(DATE‘2007-08-28‘,-2,‘DAY‘) →‘2007-08-26‘

LAST_DAY函数：返回某月最后一天；
CNV_DATE函数、CNV_DATE函数、CNV_TIME函数、CNV_TIMESTAMP函数

八、CASE式
CASE简写
1、NULLIF
示例：NULLIF(V1,V2) == CASE WHEN V1=V2 THEN NULL ELSE V1 END

2、COALESCE
示例：COALESCE (V1,V2) == CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END
COALESCE(V1,V2,Vn) ==
CASE WHEN V1 IS NOT NULL THEN V1 ELSE COALESCE(V2,,Vn) END

CASE指定
CASE V1 WHEN V2 THEN V3
WHEN V4 THEN V5
ELSE V6 END
↓相同
CASE WHEN V1=V2 THEN V3
WHEN V1=V4 THEN V5
ELSE V6 END

case使用示例
1、SELECT 名称,NULLIF(昵称,姓名) AS 昵称
FROM 利用者表

2、SELECT 利用者,COUNT(CASE 服务 WHEN 1
THEN 服务 ELSE NULL END) AS 服务1
,COUNT(CASE 服务 WHEN 2
THEN 服务 ELSE NULL END) AS 服务2
,COUNT(CASE 服务 WHEN 3
THEN 服务 ELSE NULL END) AS 服务3
FROM 历史表 GROUP BY 利用者

九、CAST指定
示例：
1、SMALLINT：CAST(10.13 AS SMALLINT) → 10；
2、CHAR：CAST(-99.99 AS CHAR(6)) → -99.99；
3、CHAR：CAST(INTERVAL ‘20:30‘ MINUTE TO SECOND AS CHAR(5)) → 20:30；

十、顺序
1、CURRVAL：返回最后生成的值；
2、NEXTVAL：返回下一个值。

十一、ROWNUM（行号）
示例：取头五行数据
SELECT a,b,c
FROM table WHERE a <= 100 AND ROWNUM < 6

【基本的SQL】
*ALTER DSI：修改DSI
DSI名:DSI名称；
USING：指定分割值；
ADD ALLOCATE：数据库空间指定。
示例：
1、更改DSI1分割值
CREATE DSO DSO1 … WHERE (年月日) BETWEEN (?) AND (?);
CREATE DSI DSI1 … USING (DATE‘2005-8-1‘,DATE‘2005-8-31‘),
(DATE‘2006-8-1‘,DATE‘2006-8-31‘);
：
：
ALTER DSI DSI1 ALTER USING (DATE‘2006-8-1‘,DATE‘2006-8-31‘),
(DATE‘2007-8-1‘,DATE‘2007-8-31‘);

2、扩展DSI1空间
ALTER DSI DSI1 ADD ALLOCATE ON DBSP1 SIZE 1M;

*ALTER TABLE：修改表
*ALTER USER：修改用户信息
*APPLY SCOPE：更改适用范围
APPLY SCOPE 东京SCP TO YAMADA
*CALL：函数调用
*CLOSE：关闭指针
*COMMIT：提交
*CONNECT：连接
*CREATE DATABASE：创建数据库
*CREATE DBSPACE：创建数据库空间
示例：
CREATE DBSPACE RDBS01 ALLOCATE FILE C:\RDB2\DBSP1 ATTRIBUTE SPACE(2M)

*CREATE DSO:创建indexDSO
*CREATE DSO:创建DSO
*CREATE DSI：创建indexDSI
*CREATE DSI：创建DSI
以上四个命令示例：
CREATE DSO companyDSO
FROM ADMINISTRATOR.company
TYPE SEQUENTIAL(PAGESIZE(4),ORDER(1));

CREATE DSI companyDSI
DSO companyDSO
ALLOCATE DATA ON DBSPACE1 SIZE 280K;

CREATE DSO companynumIXDSO
INDEX ON ADMINISTRATOR.company (companynum)
TYPE BTREE ( PAGESIZE1(16),PAGESIZE2(1), REALIGNMENT) BY ADDRESS;

CREATE DSI companynumIXDSI
INDEX
DSO companynumIXDSO
ALLOCATE BASE ON DBSPACE1 SIZE 200K,
INDEX ON DBSPACE1 SIZE 40K;

*CREATE FUNCTION：定义函数
示例（windows）:
CREATE FUNCTION S1.FUNC1
(IN INTEGER,IN INTEGER)
RETURNS INTEGER
LANGUAGE C NAME ‘user_apl1‘
LIBRARY ‘D:\FORSYMFO\FUNCLIB\USERFUNC001.DLL‘

*CREATE INDEX:定义索引
CREATE TABLE S1.T1(C1 INT NOT NULL,
C2 INT NOT NULL,
C3 INT NOT NULL) ON DBSPACE1;
CREATE INDEX S1.T1.IXA KEY(C1) ON DBSPACE2;

*CREATE PROCEDURE：创建存储过程
*CREATE ROLE：创建角色
*CREATE SCHEMA：创建模式
*CREATE SCOPE：创建范围
*CREATE SEQUENCE：创建序列
*CREATE TABLE：创建表
*CREATE TRIGGER：创建触发器
*CREATE USER：创建用户
*CREATE VIEW：创建视图
*DECLARE CURSOR：删除指针
*DECLARE TABLE：删除表
*DELETE（基于位置）：
示例：DELETE FROM TBL1 WHERE CURRENT OF CSR1 删除当前游标所指行。
*DELETE（基于检索）：
示例：DELETE FROM TBL1 WHERE COL2 = ‘R‘ AND COL3 = 1

*DISCONNECT：断开连接
*DROP DATABASE:删除数据库
*DROP DBSPACE:删除数据库空间
*DROP DSI：删除DSI
*DROP DSO：删除DSO
*DROP FUNCTION：删除函数
*DROP INDEX:删除索引
*DROP PROCEDURE：删除存储过程
*DROP ROLE：删除角色
*DROP SCHEMA：删除模式
*DROP SCOPE:删除范围
*DROP SEQUENCE：删除序列
*DROP TABLE：删除表
*DROP TRIGGER:删除触发器
*DROP USER：删除用户
*DROP VIEW：删除视图
*FETCH：
示例：
1、为了检索T1，定义CSR1
DECLARE CSR1 CURSOR FOR
SELECT COL1,COL2,COL3 FROM T1 WHERE COL4 > 10
ORDER BY COL2 DESC,COL3 ASC
CURSOR为申明语句而非执行语句，可以使用OPEN、FETCH或CLOSE。
2、打开CSR1
OPEN CSR1
3、执行操作
FETCH CSR1 INTO :TGT1,:TGT2,:TGT3
4、关闭CSR1
CLOSE CSR1

*GRANT：给予权限
示例：
GRANT ALL PRIVILEGES ON TABLE STOCKS.在庫表 TO YAMADA,TANAKA

*INSERT：插入数据
*OPEN：打开CURSOR（游标）
*PRINT STATISTICS：输出最新设置信息
*RELEASE SCOPE：接触范围
示例：RELEASE SCOPE ＳＣＰ1 FROM YAMADA

*RELEASE TABLE:删除临时表信息，释放临时表空间
*REVOKE：回收权限
*ROLLBACK：回滚
*SET CATALOG：更改数据库名称
*SET CONNECTION：更改连接
示例：
CONNECT TO ‘SV1‘ AS ‘C1‘ USER ‘USER1/PASS1‘
CONNECT TO ‘SV2‘ AS ‘C2‘ USER ‘USER2/PASS2‘
：
SET CONNECTION ‘C1‘

*SET ROLE：设置角色
*SET SCHEMA：设置模式
*SET SESSION AUTHORIZATION：变更使用者
*SET STATISTICS
*SET SYSTEM PARAMETER
*SET TRANSACTION
*SET USER PASSWORD
*Single row SELECT
*SWAP TABLE
*UPDATE（基于位置）
示例：
UPDATE TBL1
SET COL2 = ‘A‘,COL3 = NULL
WHERE CURRENT OF CSR1
*UPDATE（基于检索）
UPDATE TBL1
SET COL1 = 100, COL2 = NULL
WHERE COL1 = 20 OR COL3 = 5