Sybase 部分SQL语句介绍

--使用该数据库 use testDB   --设置该用户为当前用户 setuser 'testuser'   --查看表结构 sp_help tableName   --查看视图/触发器语句 sp_helptext viewname/triggerName   --查看前十行数据 set rowcount 10 select * from tableName set rowcount 0   --创建表   create table Mytest (    testid int ,    testname  varchar (12),    testtime  datetime )     --循环累加 declare @i int , @ sum int ,@csum char (10)   set @i=1, @ sum =0   while @i<=1000     begin     set @ sum = @ sum +@i     set @i=@i+1 if @i>1000    select @csum= convert ( char ,@ sum ) print @csum     end       --循环累加 declare @i int , @ sum int , @csum char (10)   set @i=1, @ sum =0   lable:if @i<=1000          begin            set @ sum = @ sum +@i            set @i=@i+1            if @i>1000               begin               set @csum= convert ( char ,@ sum )               print @csum               end            else               goto lable          end       --定义常量 并赋值 打印 declare @i1 int , @i2 int   set @i1=123,@i2=321   print "@i=%1!,@i2=%2!" ,@i1,@i2

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43

--创建触发器,向Mytest Insert的时候同时向test2 Insert create trigger Inserttrigger  on Mytest for insert as begin declare   @tsid int ,   @tsname varchar (10),   @tstime datetime begin    select @tsid=testid,@tsname=testname,@tstime=testtime from inserted   begin    insert into test2 values (@tsid,@tsname,@tstime)   end end end       --创建触发器,从Mytest Delete的时候把delete的数据Insert到test2 create trigger Deletetrigger  on Mytest for delete as begin declare   @tsid int ,   @tsname varchar (10),   @tstime datetime begin    select @tsid=testid,@tsname=testname,@tstime=testtime from deleted   begin    insert into test2 values (@tsid,@tsname,@tstime)   end end end     --创建简单的视图   create view testview as select M.*,T.* from Mytest M,test2 T where M.testid = T.test2id   --测试视图 select * from testview

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

--测试表 create table test (    testid numeric (8) Identity  primary key ,  --主键 自增    testname  varchar (12) )   go   --系统自增1 insert into test values ( 'testname2' ) go   select * from test   --自己手动输入 --Insert的时候列名一定要写上 否则出错 set identity_insert test on go insert into test(testid,testname) values (5, 'testname3' )

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

--不带参数的存储过程 create proc testproc as begin    select * from testview end     --带一个参数 create proc testproc2 @tid int as begin select * from Mytest where testid=@tid end     --带一个参数 create proc testproc3 @tname varchar (12) as begin select * from Mytest where testname=@tname end   --带两个参数 create proc testproc4 @tid int , @tname varchar (12) as begin select * from Mytest where testid = @tid and testname=@tname end     --执行 exec  testproc4 @tid=4,@tname= 'test'

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92

--返回值为int的存储过程 create proc testReturn @tname varchar (12) , @tid int output as begin set @tid = ( select testid from Mytest where testname=@tname) return end     --返回值为varchar的存储过程 create proc testReturnT @tid int , @tname varchar (12) output as begin set @tname = ( select testname from Mytest where testid=@tid) return end     --可以正确执行 declare @tid int exec testReturn 'testname' , @tid output select @tid        --正确的执行方法 declare @tname varchar (12) declare @tid int exec @tid = testReturnT 3,@tname output select @tid select @tname   --正确执行 declare @tname varchar (12) exec testReturnT 3,@tname output select @tname   --注意:Sybase存储过程执行之后 返回值的存储过程成功与否的Int值      --查询 返回单个输出参数值 create proc selectproc @tid int out as begin select @tid=testid from Mytest where testname= 'test9' end   --执行 declare @tid int exec selectproc @tid output select @tid     --查询 返回一个结果集 create proc selectall as begin select * from Mytest end   --执行 exec selectall     --返回错误值 create proc testprocreturn @tname varchar (12) as begin declare @tid int ,@error int if exists( select testid from Mytest where testname=@tname)     begin     set @error= ( select testid from Mytest where testname=@tname)     return @error     end else     begin     set @error=-1     return @error     end end return   --执行 declare   @error int exec @error= testprocreturn  'test9' select @error

测试连接(ODBC)：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51

using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.Odbc; using System.Data.OleDb;   namespace SybaseTest {      class Program      {          static void Main( string [] args)          {              TestConn();          }            public static void TestConn()          {              object obj = null ;              OdbcConnection SybaseConn = null ;              OdbcCommand odbccmd = null ;              try              {                  //连接串                  string strconn = "DSN=TEST;SRVR=TEST;DB=BFV752_T_JXC;UID=sa;PWD=;" ;                    SybaseConn = new OdbcConnection(strconn);                    SybaseConn.Open();                    string str = "update BFBHDD.Mytest set testname = 'testupdate' where testid=1" ;                    odbccmd = new OdbcCommand(str, SybaseConn);                    obj = odbccmd.ExecuteNonQuery();                 }              catch (Exception ex)              {                  Console.WriteLine(ex.Message);              }              finally              {                  SybaseConn.Close();              }              Console.WriteLine(obj);                Console.ReadKey();          }      } }