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

(

  testidint,

  testname varchar(12),

  testtime  datetime

)

 

 

--循环累加

declare @i int , @sumint,@csumchar(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 , @sumint, @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

             gotolable

        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  onMytestfor insert

as

begin

declare

 @tsidint,

 @tsnamevarchar(10),

 @tstime datetime

begin

  select@tsid=testid,@tsname=testname,@tstime=testtimefrominserted

 begin

  insertintotest2 values(@tsid,@tsname,@tstime)

 end

end

end

 

 

 

--创建触发器,从Mytest Delete的时候把delete的数据Insert到test2

create trigger Deletetrigger  onMytestfor delete

as

begin

declare

 @tsidint,

 @tsnamevarchar(10),

 @tstime datetime

begin

  select@tsid=testid,@tsname=testname,@tstime=testtimefromdeleted

 begin

  insertintotest2 values(@tsid,@tsname,@tstime)

 end

end

end

 

 

--创建简单的视图

 

create view testview

as

select M.*,T.* from Mytest M,test2 TwhereM.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

(

  testidnumeric(8) Identity primarykey, --主键 自增

  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 intoutput

as

begin

set @tid = (select testidfromMytest where testname=@tname)

return

end

 

 

--返回值为varchar的存储过程

create proc testReturnT

@tid int,

@tname varchar(12)output

as

begin

set @tname = (select testname from Mytestwheretestid=@tid)

return

end

 

 

--可以正确执行

declare @tid int

exec testReturn 'testname', @tidoutput

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 intout

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(selecttestidfrom Mytest where testname=@tname)

   begin

   set@error= (selecttestid from Mytestwhere testname=@tname)

   return@error

   end

else

   begin

   set@error=-1

   return@error

   end

end

return

 

--执行

declare  @errorint

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;

 

namespaceSybaseTest

{

    classProgram

    {

        staticvoidMain(string[] args)

        {

            TestConn();

        }

 

        publicstaticvoidTestConn()

        {

            objectobj =null;

            OdbcConnection SybaseConn =null;

            OdbcCommand odbccmd =null;

            try

            {

                //连接串

                stringstrconn ="DSN=TEST;SRVR=TEST;DB=BFV752_T_JXC;UID=sa;PWD=;";

 

                SybaseConn =newOdbcConnection(strconn);

 

                SybaseConn.Open();

 

                stringstr ="update BFBHDD.Mytest set testname = 'testupdate' where testid=1";

 

                odbccmd =newOdbcCommand(str, SybaseConn);

 

                obj = odbccmd.ExecuteNonQuery(); 

 

            }

            catch(Exception ex)

            {

                Console.WriteLine(ex.Message);

            }

            finally

            {

                SybaseConn.Close();

            }

            Console.WriteLine(obj);

 

            Console.ReadKey();

        }

    }

}