sysbase基础语法

SELECT * FROM sales_order
SELECT name,hierarchy FROM systypes ORDER BY hierarchy
SELECT char_length(type),datalength(type) FROM systypes
--反转
SELECT reverse(name) FROM systypes
SELECT * FROM dbo.sysindexes
--索引
SELECT * FROM sys.sysindexes where tname='sales_order'
--表名
SELECT * FROM dbo.sysobjects where name='sales_order'
use sys
exec sp_helpindex 'sales_order'

Select * from sys.sysqifile

--如果measure_id='1030001'则返回null，否则为measure_id
SELECT NULLIF(measure_id,'1030001') FROM dw.dwd_measure
--如果为null则返回替换的值
SELECT ISNULL(measure_name,'0') FROM dw.dwd_measure

--当前时间
SELECT current timestamp FROM dw.dwd_measure
SELECT current date
SELECT now()
SELECT dateformat(current timestamp,'yyyy/MM/dd hh:mm:ss')
current user 当前连接的用户ID
--全局变量
SELECT @@version
SELECT @@connections
SELECT @@error
SELECT @@procid
SELECT @@servername

select * from dwdev.dwf_prd_ind_perform
SELECT USER,today(*),now() FROM sys.dummy
--ceil>=指定表达式的最小值
SELECT birthday,birthday+31,cast('2000-10-31' as date),cast('2000-10-31' as date),ceil(12.1)  FROM "dw"."DWD_AGENT"
--coalesce 返回第一个非空表达式
select char(89),char_length('aaaa'),coalesce(null,'b') from "dw"."DWD_AGENT"
--convert
select convert(date,'2000-10-31')
--datediff(hour/month/day/week,exp1,exp2)
select datediff(month,'2011-07-11','2011-10-31'),cast('2011-07-31' as date)-cast('2011-07-01' as date)
--dow[date and time]指定日期星期几,星期日=1
select dow('2011-07-21')

--月份加1 year | quarter | month | week | day | hour | minute | second | millisecond
select dateformat(dateadd(month,1,'2011-09-30'),'yyyy-mm-dd')


--day ,days,hour,hours ;insertstr结果数据类型为longvarchar 再使用需要cast进行转换
select hour('21:11:11'),days('2011-07-21','2011-07-31'),ifnull(null,0),cast(insertstr(0,'a','b') as char),
--isdate 成功返回1，失败返回0 isnumeric
select isdate(null),lcase('ABC'),newid()

select ymd(year(now()),month(now()),11)
--sybase IQ比较时候区分大小写
select round(12.12277,2),case when Ucase('a')='A' then '0' else '1' end
select datepart(dd,now()),datepart(month,now()),getdate(),now()

select * from DWDEV.CTL_PRD_LOG
select * from sys.sysinfo
select * from sys.systable
sp_iqwho

--drop用户连接
sp_iqlocks
drop connection 1651

--sp_iqtablesize(table_owner.table_name) 返回指定表的大小
sp_iqtablesize('DWDEV.CTL_PRD_LOG')
sp_iqlocks
sp_iqwho

--子查询需对子查询结果指定别名
select * from (
    (select * from dw.dwt_policy_fee) as a
    )
   
--查询系统存储过程名称及源代码
select * from sysprocedure

--查询用户表名称及表字段
select * from systable where table_name like upper('%t_policy_fee%')
select * from syscolumn where table_id=591