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();          }      } }