Sybase IQ的使用心得(对比Oracle)

现在项目组使用的数据库是Sybase,之前都是用Oralce的,在熟悉Sybase时发现Sybase的资料还是很少的(尤其网上资料),摸索很长时间后才慢慢适应过来,闲余之时,总结了下Sybase IQ的使用心得,由于之前用的的Oralce,所以有的差异也是和Oralce作的比较。

希望能帮到Sybase初学者。

使用总结===========================================

1、字母大小写比对不敏感,也就是在值比对判断时大小写字母都一样;

2、等值,或<>判断,系统默认对等式两边比对值去右边空格再进行比较;

3、GROUP BY 可以根据SELECT字段或表达式的别名来 汇总,在编写时也尽量避免SELECT 语句的别名与FROM表中的字段有重复,不然会出现莫名其妙的错误; 4、FROM后的子查询 要定义别名才可使用;

5、存储过程要返回IQ系统错误信息 SQLCODE || ERRORMSG(*) :(两者都为EXCEPTION后第一条SQL语句才有效果);

6、IQ中若采用 FULL JOIN 连接则不能使用 WHERE 条件,否则FULL JOIN将失效,要筛选条件则用子查询先过滤记录后再FULL JOIN;

7、建表时,字段默认为非空;

8、UPDATE语句,如果与目标表关联的表有多条,则不会报错,而是随机取一条更新(第一条);

9、RANK() OVER(PARTITION BY .. ORDER BY ..) 分组分析函数,相同的ORDER BY值,返回顺序值一样,且PARTITION BY 只支持一个字段或一个字段组(需多个字段分组的则要用 || 拼为一个字段(待确认,该问题以前碰过一次,再次验证却不存在这问题))

10、返回可读的 全局唯一字符:UUIDTOSTR(NEWID())

11、存储过程隐式游标语法:

FOR A AS B CURSOR FOR SELECT ... FROM ... DO .... 过程语句 END FOR;

需要注意的时,这边的A 和 B 在 过程语句中都不能引用,所以为避免过程语句其他字段名与FOR SELECT 语句的字段名称重复,FOR SELECT 语句的字段最好都定义别名区分

12、根据SELECT 语句建立[临时]表的方法(ORACLE的CREATE TABLE)为 SELECT ..[*] INTO [#]table_name FROM ..; 其中如果在table_name加前缀#,则为会话级临时表,否则为实体表;

13、因Sybase为列存储模式,在执行上INSERT语句会比UPDATE语句慢,尤其表数据越多INSERT效率就越慢;所以在ETL时建议多用UPDATE而不是INSERT

14、虽说Sybase为列存储模式,每个字段上都有默认索引,但对于经常的两表的关联键还是要建立索引否则会经常报QUERY_TEMP_SPACE_LIMIT不足的错误;

15、存储过程中也可以显示的执行DDL语句,这点与Oracle不同;

16、空字符串''在Sybase中也是个字符而不是null值,这点要注意;

17、调整SESSION的临时空间 SET TEMPORARY OPTION QUERY_TEMP_SPACE_LIMIT = '150000'; 15000为大小,如写0则没限制大小 常用函数===========================================

字符串函数

1)ISNULL(EXP1,EXP2,EXP3,...) :返回第一个非空值,用法与COALESCE(exp1,exp2[,exp3...])相同

3)TRIM(exp) :去除两边空格

4)DATEFORMAT(date_exp,date_format) :日期型转字符型;

5)STRING(exp):转为字符型;

6)SUBSTRING(exp,int-exp1,[int-exp2]):截取exp从int-exp1开始,截取int-exp2个字符;

7)REPLACE(o-exp,search-exp,replace-exp):从o-exp搜索search-exp,替换为replace-exp;

8)SPACE(int_exp):返回int个空格;

9)UPPER(exp):转为大写字母,等价于UCASE(exp);

10)LOWER(exp):转为小写字母;

11)CHARINDEX(exp1,exp2):返回exp2字符串中exp1的位置!定位,exp1 查找的字符,exp2 被查找的字符串;

12)DATALENGTH(CHAR_EXPR):在char_expr中返回字符的长度值,忽略尾空;

13)RIGHT(char_expr,int_expr):返回char_expr右边的int_expr个字符;

14)LEFT(char_expr,int_expr):返回char_expr左边的int_expr个字符;

15)REPLICATE(char_expr,int_expr):重复char_expr,int_expr次;

16)STUFF(expr1,start,length,expr2):用expr2代替epxr1中start起始长为length的字符串;

17)REVERSE(char_expr):反写char_expr中的文本;

18)LTRIM(char_expr):删除头空;

19)RTRIM(char_expr):删除尾空;

20)STR(float_expr[,length[,decimal]]):进行数值型到字符型转换;

21)PATINDEX("%pattern%",expression):返回指定样式的开始位置,否则为0;

22)NULLIF(exp1,exp1):比较两个表达式,如果相等则返回null值,否则返回exp1;

23)NUMBER(*):返回序号,相当于ORACLE的rowid,但有区别;

其他函数=====================

1)RANK() OVER(PARTITION BY .. ORDER BY ..) 分组分析函数,相同的ORDER BY值,返回顺序值一样,且PARTITION BY 只支持一个字段或一个字段组(需多个字段分组的则要用 || 拼为一个字段(待确认))

2)返回可读的 全局ID UUIDTOSTR(NEWID())

3)COL_LENGTH(tab_name,col_name):返回定义的列长度;兼容性:IQ&ASE

4)LENGTH(exp):返回exp的长度;兼容性:IQ

转换函数============================

1)CONVERT(datetype,exp[,format-style]):字符转日期型 或DATE(exp);兼容性:IQ&ASE

format-style值 输出:

112 yyyymmdd 120 yyyy-mm-dd hh:nn:ss SELECT CONVERT(date,'20101231',112),CONVERT(varchar(10),getdate(),120) ;

--结果 2010-12-31 2011-04-07 2)CAST(exp AS data-type):返回转换为提供的数据类型的表达式的值; 兼容性:IQ

日期函数=================================

1)DAY(date_exp):返回日期天值,DAYS(date_exp,int):返回日期date_exp加int后的日期;MONTH与MONTHS、YEAR与YEARS同理;

2)DATE(exp):将表达式转换为日期,并删除任何小时、分钟或秒;兼容性:IQ

3)DATEPART(date-part,date-exp): 返回日期分量的对应值(整数);

4)GETDATE():返回系统时间;

5)DATENAME(datepart,date_expr):以字符串形式返回date_expr指定部分的值,转换成合适的名字;

6)DATEDIFF(datepart,date_expr1,date_expr2):返回date_expr2-date_expr1,通过指定的datepart度量;

7)DATEADD(date-part,num-exp,date-exp):返回按指定date-part分量加num-exp值后生成的date-exp值;兼容性:IQ&ASE

date-part日期分量代表值: 缩写 值 YY 0001-9999 QQ 1-4 MM 1-12 WK 1-54 DD 1-31 DY 1--366 DW 1-7(周日-周六) HH 0-23 MI 0-59 SS 0-59 MS 0-999

数值函数================================ 1)CEIL(num-exp):返回大于或等于指定表达式的最小整数;兼容性:IQ&ASE;

2)FLOOR(numeric_expr):返回小于或等于指定值的最大整数;

3)ABS(num-exp):返回数值表达式的绝对值;兼容性:IQ&ASE;

4)TRUNCNUM(1231.1251,2):截取数值;不四舍五入;

5)ROUND(numeric_expr,int_expr):把数值表达式圆整到int_expr指定的精度;

6)RAND([int_expr]):返回0-1之间的随机浮点数,可指定基值;

7)SIGN(int_expr):返回正+1,零0或负-1;

8)SQRT(float_expr):返回指定值的平方根;

9)PI():返回常数3.1415926;

10)POWER(numeric_expr,power):返回numeric_expr的值给power的幂;

11)EXP(float_expr):给出指定值的指数值;

常用DDL语句===========================================

Sybase中DDL语句不能修改字段的数据类型,只能修改空与非空: 1.删除列:

ALTER TABLE table_name DELETE column_name;

2.增加列:

ALTER TABLE table_name ADD (column_name DATA_TYPE [NOT] NULL);

3.修改列的空与非空:

ALTER TABLE table_name MODIFY column_name [NOT] NULL;

4.修改列名:

ALTER TABLE table_name RENAME old_column_name TO new_column_name;

5.快速建立临时表:

SELECT * INTO [#]table_name FROM .....;

6、修改表名:

ALTER TABLE old_table_name RENAME new_table_name;

7.增加主键约束:

ALTER TABLE tb_name ADD CONSTRAINT pk_name PRIMARY KEY(col_name,..);

8.删除主键约束:

ALTER TABLE tb_name DROP CONSTRAINT pk_name;

9.建立自增长字段,与Oracle的SEQUENCE类似:

CREATE TABLE TMP_001 (RES_ID INTEGER IDENTITY NOT NULL);

10.添加表注释:

COMMENT ON TABLE table_name IS '....';

11.创建索引:

CREATE INDEX index_name ON table_name(column_name);

与Oracle对比=====================

1.Oracle表名、字段名不区分大小写,Sybase严格区分

2.Oracle中to_char()/to_date() ---Sybase的convert()

--Oracle:to_char(sysdate,'dd-mm-yyyy day'),to_date(string,format)

--Sybase:convert(char(15),日期,108),convert(datetime,string,108) --末尾附 Sybase convert第三个参数介绍

3.Oracle中 substr() -- Sybase的 substring()

4.Oracle中 nvl() -- Sybase的 isnull()

5.Oracle取系统时间 sysdate -- Sybase的 getdate()

6.Oracle的decode -- Sybase的 case when then

--Oracle: select decode(param,null,resultA,resultB) from res; --Sybase:select case when param is null then resultA else resultB end from res;

7.外连接

--Oracle:select * from tableA a ,tableB b where a.id=b.id(+); --Sybase:select * from tableA a,tableB b where a.id*=b.id;

8.insert 方法

--Oracle:insert into tableA(select * from tableB where tableB.id=""); --Sybase:insert into tableA(id,name,age)(select * from tableB where tableB.id=""); insert的表必须写清列名 ————————————————

其他:

  1. 计算时间不同写法

    Oracle: ROUND(SYSDATE- AIAH.TRANS_DATE) TIME_OUT //得到天数 Sybase: DATEDIFF(DAY, AIAH.TRANS_DATE, GETDATE())

    Sybase计算时间差函数:

    DATEDIFF(darpart,时间1,时间2)

    darpart:

    day:天
    
    minute:分
    
    second:秒

    SELECT DATEDIFF(day,'2008-12-29','2008-12-30') AS DiffDate

  2. convert函数使用

    SELECT CONVERT(INT,' ') // 0

    SELECT CONVERT(VARCHAR,50) // 50 转换为字符

    SELECT CONVERT(FLOAT,50) //通用转换为数值型,类型根据表字段而定

  3. Sybase 中获取年、月、日

    select datepart(yy,getdate()) --year
    select datepart(mm,getdate()) --month
    select datepart(dd,getdate()) --day

评论