Sybase iq数据库函数整理文档.docx

1、Sybase iq数据库函数整理文档 Sybase iq 数据库函数总结 建数据库脚本：CREATE DATABASE e:Program FilesSybaseASIQ-12_7myiqmyiq.db TRANSACTION LOG ON COLLATION 936ZHO PAGE SIZE 4096 JAVA ON JCONNECT ON CASE RESPECT PASSWORD CASE RESPECT IQ PATH myiq.iq IQ SIZE 100 IQ PAGE SIZE 131072 TEMPORARY PATH myiq.iqtmp TEMPORARY SIZE 10S

2、ET OPTION public.MINIMIZE_STORAGE = ON ;这个可以在数据库中设置一下，这样减轻字段存储空间的压力-select * from all_ind_columns where table_name=TB_CM_SERV and table_owner=IODATA_GZ;数据文件的使用率：Create procedure myspace()BeginDeclare mt unsigned bigint;Declare mu unsigned bigint;Declare tt unsigned bigint;Declare tu unsigned bigint;

3、Call sp_iqspaceused (mt,mu,tt,tu);Select cast(mt/1024 as unsigned bigint) as mainMB, Cast(mu/1024 as unsigned bigint) as mainusedMB, Mu*100/mt as mainPerCent, Cast(tt/1024 as unsigned bigint) as tempMB, Cast(tu/1024 as unsigned bigint) as tempusedMB, Tu*100/tt as tempPerCent;End一、 数据类型1.1 日期及时间数据类型D

4、ATE 日历日期，如年、月、日。年可以从 0001 到 9999。日必须是非零值，以便最早日期为 0001-01-01。DATE 值需要 4 个字节的存储空间。DATETIME 一个以 TIMESTAMP 的形式实现的域。之所以提供 DATETIME，主要是为了与 Adaptive Server Enterprise 兼容。有关例外，请参见“字符串到日期时间转换的兼容性”。SMALLDATETIME 一个以 TIMESTAMP 的形式实现的域。TIME 一天中的时间，包含小时、分钟、秒，以及秒的小数部分。分数存储为 6 位小数。TIME 值需要 8 个字节的存储空间。（ODBC 标准将 TIM

5、E数据类型限制为精确到秒。因此，不要在要求的精度比秒更高的 WHERE子句比较中使用 TIME 数据类型。）TIMESTAMP 时间点，包含年、月、日、小时、分钟、秒，以及秒的小数部分。分数存储为 6 位小数。日必须为非零值。TIMESTAMP 值需要 8 个字节的存储空间。1.2 日期和时间格式以下是用于指定时间的有效而明确的字符串：21:35 - 24 小时制（如果未指定 am 或 pm）10:00pm - 已指定 pm，因此为 12 小时制10:00 - 不带 pm，因此是指 10:00am10:23:32.234 - 包括秒以及秒的小数部分在向数据库发送时间是，小时、分钟和秒必须用冒号

6、分隔开（格式为 hh:mm:ss:sss），但可以显示在字符串中的任意位置。可以选择用句点将秒与秒的小数部分相互分隔开，如hh:mm:ss.sss。将日期以字符串形式发送到数据库时，其到日期数据类型的转换是自动发生的。您可以通过下面两种方式之一提供字符串： 以 yyyy/mm/dd 或 yyyy-mm-dd 格式的字符串形式，其格式由数据库明确解释二、 函数1.3 Rank 窗口化集合函数 分区函数的用法select rank() over(partition by dept_id order by salary) from emp1; Partition by 后一定要加order by1.

7、4 Now 函数select now(*) from emp11.5 ARGN 函数 Miscellaneous从参数列表中返回所选参数。语法： ARGN ( integer-expression, expression , . )SELECT ARGN( 4, 1,2,3,7,5,6 ) FROM iq_dummy;SELECT ARGN( 4, sdf,asd,sdfsdrg,asrewryry,345sdfs) FROM iq_dummy;1.6 ARGN 函数 Miscellaneous返回字符串表达式中第一个字节的整数 ASCII 值。语法：ASCII ( string-expres

8、sion )SELECT ASCII( Z ) FROM iq_dummy1.7 AVG 函数 Aggregate为一组行计算数值表达式的平均值，或计算一组唯一值的平均值。语法：AVG ( numeric-expression | DISTINCT column-name )1.8 ABIGNTTOHEX 函数Data type conversion在 VARCHAR(16) 中返回十进制整数的十六进制等效数字。语法：BIGINTTOHEX ( integer-expression )下面的语句返回值 0000000000000009：SELECT BIGINTTOHEX(9) FROM iq

9、_dummy;下面的语句返回值 FFFFFFFFFFFFFFF7：SELECT BIGINTTOHEX(-9) FROM iq_dummy;1.9 BIT_LENGTH 函数 String返回字符串中的位数，即字符数*8select BIT_LENGTH(name),name from emp1;1.10 BYTE_LENGH 函数 String返回字符串中的字节数。语法：BYTE_LENGTH ( string-expression )1.11 CAST 函数 Data type conversion返回转换为提供的数据类型的表达式的值。语法：CAST ( expression AS dat

10、a type )下面的函数可确保使用字符串作为日期：CAST( 2000-10-31 AS DATE )计算表达式 1 + 2 的值，并将结果转换为单字符字符串（即数据服务器分配的长度）：CAST( 1 + 2 AS CHAR )可以使用 CAST 函数缩短字符串：SELECT CAST( lname AS CHAR(5) ) FROM customer用法如果未指定字符串类型的长度，Sybase IQ 将选择适当的长度。如果没有为十进制转换指定精度和标度，则数据库服务器将选择适当的值。如果针对 NULL 到 NUMERIC 的显式转换既未指定精度也未指定标度，则缺省值为 NUMERIC(1,

11、0)。例如，SELECT CAST( NULL AS NUMERIC ) A,CAST( NULL AS NUMERIC(15,2) ) B说明为：A NUMERIC(1,0)B NUMERIC(15,2)1.12 CEIL 函数 Numeric返回大于或等于指定表达式的最小整数。1.13 ceiling 函数 Numeric返回一个数字的上限（不小于的最小整数）。 与ceil为同义词。语法：CEILING ( numeric-expression )下面的语句返回值 60.00000：SELECT CEILING( 59.84567 ) FROM iq_dummy下面的语句返回值 123：S

12、ELECT CEILING( 123 ) FROM iq_dummy下面的语句返回值 124.00：SELECT CEILING( 123.45 ) FROM iq_dummy下面的语句返回值 -123.00：SELECT CEILING( -123.45 ) FROM iq_dummy1.14 CHAR 函数 String返回含数字所表示的 ASCII 值的字符。语法：CHAR ( integer-expression )下面的语句返回值“Y”：SELECT CHAR( 89 ) FROM iq_dummy下面的语句返回值“S”：SELECT CHAR( 83 ) FROM iq_dummy

13、对于值大于 255 或小于 0 的整数表达式， CHAR 返回 NULL。1.15 CHAR_LENGTH 函数 String返回字符串中的字符数。语法：CHAR_LENGTH ( string-expression )下面的语句返回值 8：SELECT CHAR_LENGTH( Chemical ) FROM iq_dummy;1.16 CHARINDEX 函数 String返回一个字符串在另一个字符串中第一次出现的位置。语法：CHARINDEX ( string-expression1, string-expression2 )SELECT emp_lname, emp_fname FRO

14、M employee WHERE CHARINDEX(K, emp_lname ) = 1select charindex(upper(a),name),name from emp1;1.17 COALESCE 函数 Miscellaneous返回列表中的第一个非空表达式。语法：COALESCE ( expression, expression , . )1.18 COL_NAME 函数 System返回列名。语法：COL_NAME ( table-id, column-id , database-id )下面的语句返回值“Y”：SELECT CHAR( 89 ) FROM iq_dummy下

15、面的语句返回值“S”：SELECT CHAR( 83 ) FROM iq_dummy对于值大于 255 或小于 0 的整数表达式， CHAR 返回 NULL。1.19 CONNECTION_PROPERTY 函数 System以字符串形式返回给定连接属性的值。语法：CONNECTION_PROPERTY ( integer-expression1 | string-expression . , integer-expression2 )SELECT connection_property( PrepStmt ) FROM test;1.20 CONVERT 函数 Data type conve

16、rsion返回转换成提供的数据类型的表达式。语法：CONVERT ( data-type, expression , format-style )0、9 表示都是含世纪的，其他如1、2都是可以不含世纪的。SELECT CONVERT( CHAR( 20 ), order_date, 4),CONVERT( CHAR( 20 ), order_date, 104),order_date FROM sales_order；1.21 DATALENGTH 函数 System返回表达式的长度（以字节为单位）。语法：DATALENGTH ( expression )下面的语句返回值 35，即 compa

17、ny_name 列中最长的字符串：SELECT MAX( DATALENGTH( company_name ) ) FROM customer1.22 DATE 函数 Date and time将表达式转换为日期，并删除任何小时、分钟或秒。语法：DATE ( expression )下面的语句返回值 1988-11-26 作为日期。SELECT DATE( 1988-11-26 21:20:53 ) FROM iq_dummy1.23 DATEADD 函数 Date and time返回通过将指定数量的指定日期分量添加到日期中而生成的日期。语法：DATEADD ( date-part, num

18、eric-expression, date-expression )date part 要添加到日期中的日期分量。numeric-expression 要添加到日期中的日期分量的数量。numeric-expression 可以是任何数值类型；值截断为整数。date-expression 要修改的日期。下面的语句返回值 1995-11-02 00:00:00.000：SELECT DATEADD( month, 102, 1987/05/02 ) FROM iq_dummy1.24 DATEDIFF 函数 Date and time返回两个日期之间的时间间隔。语法：DATEDIFF ( date

19、-part, date-expression1, date-expression2 )date-expression1 间隔的开始日期。从 date-expression-2 中减去该值，以返回两个参数之间日期分量的数量。date-expression2 时间间隔的结束日期。从此值中减去 date-expression1以返回两个参数间的日期分量的数量。下面的语句返回 1：SELECT DATEDIFF( hour, 4:00AM, 5:50AM ) FROM iq_dummy;下面的语句返回 102：SELECT DATEDIFF( month, 1987/05/02, 1995/11/15

20、 ) FROM iq_dummy;下面的语句返回 0：SELECT DATEDIFF( day, 00:00, 23:59 ) FROM iq_dummy ;下面的语句返回 4：SELECT DATEDIFF( day, 1999/07/19 00:00, 1999/07/23:23:59 ) FROM iq_dummy;下面的语句返回 0：SELECT DATEDIFF( month, 1999/07/19, 1999/07/23 ) FROM iq_dummy;下面的语句返回 1：SELECT DATEDIFF( month, 1999/07/19, 1999/08/23 ) FROM i

21、q_dummy;1.25 DATEFORMAT 函数 Date and time以指定的格式返回表示日期表达式的字符串。语法：DATEFORMAT ( datetime-expression, string-expression )datetime-expression 要转换的日期/时间。必须是日期、时间、时间戳或字符串。string-expression 转换后的日期格式。下面的语句返回类似“Jan 01, 1989”的字符串值：SELECT DATEFORMAT( start_date, Mmm dd, yyyy ) from employee;下面的语句返回字符串“Feb 19, 19

22、87”。SELECT DATEFORMAT( CAST ( 1987/02/19 AS DATE ), MmmDd, yyyy ) FROM iq_dummy1.26 DATEPART 函数 Date and time返回日期/时间值的指定部分的整数值。语法：date-part 要返回的日期分量。有关允许的日期分量的完整列表，请参见“日期分量”（第 235 页）。date-expression 要返回其日期分量的日期。日期必须包含 date-part字段。SELECT DATEPART( month , 1987/05/02 ) FROM iq_dummy;1.27 DATENAME 函数 D

23、ate and time以字符串形式返回日期/时间值中指定部分的名称（如月份“June”）语法：DATENAME ( date-part, date-expression )date-part 要指定的日期分量。date-expression 要返回其日期分量名称的日期。日期必须包含请求的date-part。下面的语句返回值 May：SELECT datename( month , 1987/05/02 ) FROM iq_dummy1.28 DATETIME 函数 Date and time将表达式转换成时间戳。语法：DATETIME ( expression )expression 要转换

24、的表达式。表达式通常是字符串。可能会报告转换错误。SELECT DATETIME( 1998-09-09 12:12:12.000 ) FROMiq_dummy返回含有值 1998-09-09 12:12:12.000 的时间戳：1.29 DAY 函数 Date and time返回 1 到 31 之间的一个整数，该整数对应于指定日期中的每月几号。语法：DAY ( date-expression )下面的语句返回值 12：SELECT DAY( 2001-09-12 ) FROM iq_dummy1.30 DAYNAME 函数 Date and time返回指定日期中的星期几的名称。语法：CO

25、ALESCE ( expression, expression , . )下面的语句返回值 Saturday：SELECT DAYNAME ( 1987/05/02 ) FROM iq_dummy1.31 DAYS 函数 Date and time返回自任意开始日期以来的天数，返回两个指定日期之间的天数，或者将指定的 integer-expression 数量的天添加到给定日期中。语法DAYS ( datetime-expression )| ( datetime-expression, datetime-expression )| ( datetime-expression, integer

26、-expression )参数datetime-expression 日期和时间。integer-expression 要添加到 datetime-expression 中的天数。如果integer-expression 是负数，则从日期/时间中减去相应的天数。如果提供整数表达式，必须将 datetime-expression 显式转换为日期。DAYS 忽略小时、分钟和秒。示例下面的语句返回整数值 729948：SELECT DAYS( 1998-07-13 06:07:12 ) FROM iq_dummy下面的语句返回整数值 -366，这是两个日期之间的差值：SELECT DAYS( 199

27、8-07-13 06:07:12,1997-07-12 10:07:12 ) FROM iq_dummy下面的语句返回值 1999-07-14：SELECT days(cast(2009-01-03 as date),3) ,days(date(2009-01-03),3)from iq_dummy1.32 DB_ID 函数 System返回数据库 ID 号。语法：DB_ID ( database-name )database-name 包含数据库名的字符串表达式。如果 database-name 是字符串常量，则它必须用引号引起来。如果未提供 database-name，则返回当前数据库的

28、ID 号。如果 asiqdemo 是唯一正在运行的数据库，则下面的语句返回值 0：SELECT DB_ID( asiqdemo ), DB_ID( ) FROM iq_dummy1.33 DB_NAME 函数 System返回数据库名。语法：DB_NAME ( database-id )database-id 数据库的 ID。database-id 必须是数值表达式。示例下面的语句在对样本数据库执行时返回数据库名 asiqdemo。SELECT DB_NAME( 0 ) FROM iq_dummy1.34 DB_PROPERTY 函数 System返回给定属性的值。语法：COALESCE (

29、expression, expression , . )DB_PROPERTY ( property-id | property-name , database-id | database-name )property-id 数据库属性 ID。property-name 数据库属性名称。database-id 数据库 ID 号，由 DB_ID 返回。通常情况下，使用数据库名称。database-name 数据库的名称，由 DB_NAME 返回。下面的语句返回当前数据库的页大小（以字节为单位）。SELECT DB_PROPERTY( PAGESIZE ) FROM iq_dummy1.35 DE

30、GREES 函数 Numeric将数字从弧度转换为度数语法：DEGREES ( numeric-expression )参数numeric-expression 以弧度表示的角度。示例下面的语句返回值 29.793805：SELECT DEGREES( 0.52 ) FROM iq_dummy1.36 DIFFERENCE 函数 String比较两个字符串，评估它们之间的相似性，并返回 0 到 4 之间的一个值。最佳匹配值是 4。语法：DIFFERENCE ( string-expression1, string-expression2 )参数string-expression1 要比较的第一个字符串。string-expression2 要比较的第二个字符串。Select difference(huyong,huyong), difference(huyong,jiayong),difference(huyong,jiahen