isql(sybase)　语法总结

关注

原创最新推荐文章于 2025-08-20 13:07:53 发布 4.2k 阅读 AI 写同款· GEO 优化 ›

理解isql是什么及基础用法 isql(维基百科) isql是数据库领域的多重指代的缩写。包括： isql, a Sybase client isql, a unixODBC program iSQL, an Altibase utility iSQL*Plus, a web-based interface to Oracle’s SQL*Plus ISQL, Informix SQL - an Informix too... 阅读详情

Transact-SQL(sybase sqlserver)

SQL Server是由Microsoft开发和推广的关系数据库管理系统（DBMS），它最初是由Microsoft、Sybase和Ashton-Tate三家公司共同开发的，
但后来随着两公司的合作结束，他们分别在自己的版本中加入了不同的扩展功能。

replace(@Country,'|', ''',''')|为','declare @sql varchar(5000)

select @sql = 'select '+ @param+'from....'

在拼字符串时，注意类型，类型不一致　@sql就为空

cast(cast('193.57' AS float) as int)
cast('193' AS int)
convert(int,'10',113)

charindex('Delta1', @business) > 0
convert(varchar(20),@count)=str(@count)
convert(date,@date)=date(@date)
实际的sql:convert(date,str(year)+'-'+str(month)+'-01')>'01/01/2011'
isql中拼字符串'convert(date,str(year)+''-''+str(month)+''-01'')>'''+dateformat(@startDate,'mm/dd/yyyy')'''

dateadd(dd,1, @tmpDate)
datepart(dy, getDate())
datediff(millisecond, @startTime, @endTime)

日期分量缩写值
Year yy 0001 – 9999
Quarter qq 1 – 4
Month mm 1 – 12
Week wk 1 – 54
Day dd 1 – 31
Dayofyear dy 1 – 366
Weekday dw 1 – 7 （周日– 周六）
Hour hh 0 – 23
Minute mi 0 – 59
Second ss 0 – 59
Millisecond ms 0 – 999

ISDATE ISNUMERIC NOW

select isnull(p.pnl,0) pnl ,其他列中和where,group,order by可以使用
select top 5 gpnum,isnull(segment,' ') segment from TblGeriTradesDateRange_rollup where segment is null
select top 5 gpnum,segment from TblGeriTradesDateRange_rollup where segment is null

case when ... then ... else .... end
if(...)begin...end else begin....end
while(...) begin....end

CREATE PROCEDURE spGetDateCommon(
    @sStartDate    varchar(30) = 'YTD' ,
    @sEndDate      varchar(30)='YTD' ,
    @startDate     date OUTPUT,
    @endDate       date OUTPUT
)

表字段默认为　not null

LastModifiedBy varchar(10) default 'BATCH',
LastModifiedOn datetime default getdate()
计算十五日平均值

create table #tmpTable(
      rowid int identity,
      dt varchar(100),
      TotalTurnoverUSD float null,
      GrossCommissions float null,
      MAVG        float null
   )
    declare @tmpDate date
    select @tmpDate=date('2012/01/01')
   while(@tmpDate <= date('2012/07/01'))
   begin
   if(datepart(dw,@tmpDate) > 1 and datepart(dw,@tmpDate) < 7)
   insert into #tmpTable( dt,TotalTurnoverUSD,GrossCommissions) values(DATEFORMAT(@tmpDate,'dd/mm/yyyy'),0,0)
   select @tmpDate = dateadd(dd,1, @tmpDate)
   end

    select * into #TblAvg from #tmpTable t where t.dt >=@startDate and t.dt <=@endDate
    update #TblAvg t set t.rowid=t.rowid-(select min(rowid) from #TblAvg)+1 进入#TblAvg表的值从１开始
    update #TblAvg t set t.GrossCommissions=(select GrossCommissions from rollup where dt=dateformat(t.dt,'dd/mm/yyyy'))

update #TblAvg t set MAVG=((select sum(GrossCommissions) from #TblAvg where rowid>(case when t.rowid>15 then t.rowid-15 else 0 end) and rowid<=t.rowid))/(case when t.rowid/15=0 then rowid else 15 end )

EXISTS(select * from SYSPROCEDURE where proc_name='someSP')

OBJECT_ID('someTable')

declare @sql varchar(1000),@drop varchar(100)
set @sql='select index_name into #tmp from sysindex i,sysusers u where i.index_name='''+someIndex+''' and u.name='''+@schema+''' and i.creator=u.uid'
execute(@sql)
exists(select * from #tmp)

下面的还包含schema,但需要管理员权限
sp_iqindex TblClientInfo,NULL,avita
sp_iqtable NULL,avita
sp_iqprocedure NULL,avita

select index_name from sysindex i,systable t where i.table_id=t.table_id and t.table_name='TblGeriResearch_rollup'
select * from SYSPROCEDURE p,sysuses u where p.creator=u.uid and p.proc_name='spGetClientCommissionOverview' and u.name='avita'

grant execute/select on getAvitaCntry to avita
select t.table_name,p.selectauth from SYSTABLEPERM p,systable t
where p.grantor='104' and p.grantee='160'
and p.ttable_id=t.table_id

select userid,number_connects from IQ_USER_LOGIN_INFO_TABLE where userid in ('sales','cp_dbo','avita')