Basic syntax for SQL Server programming

 

first, define variables

--简单赋值

declare@aint

set@a=5

print @a

--使用select语句赋值

declare@user1 nvarchar(50)

select@user1=‘张三‘

print @user1

declare@user2 nvarchar(50)

select@user2 = Name from ST_User whereID=1

print @user2

--使用update语句赋值

declare@user3 nvarchar(50)

updateST_User set @user3 = Name whereID=1

print @user3

Second, table, temporary table, table variable

--创建临时表1

createtable#DU_User1

(

     [ID] [int] NOTNULL,

     [Oid] [int]NOTNULL,

     [Login] [nvarchar](50)NOTNULL,

     [Rtx] [nvarchar](4)NOTNULL,

     [Name] [nvarchar](5)NOTNULL,

     [Password] [nvarchar](max)NULL,

     [State] [nvarchar](8)NOTNULL

);

--向临时表1插入一条记录

insertinto #DU_User1 (ID,Oid,[Login],Rtx,Name,[Password],State)values(100,2,‘LS‘,‘0000‘,‘临时‘,‘321‘,‘特殊‘);

--从ST_User查询数据，填充至新生成的临时表

select* into #DU_User2 from ST_User whereID<8

--查询并联合两临时表

select* from #DU_User2 where ID<3 union select * from#DU_User1

--删除两临时表

droptable#DU_User1

droptable#DU_User2

--创建临时表

CREATETABLE#t

(

    [ID] [int]NOTNULL,

    [Oid] [int]NOTNULL,

    [Login] [nvarchar](50)NOTNULL,

    [Rtx] [nvarchar](4)NOTNULL,

    [Name] [nvarchar](5)NOTNULL,

    [Password] [nvarchar](max)NULL,

    [State] [nvarchar](8)NOTNULL,

)

--将查询结果集（多条数据）插入临时表

insertinto #t select * fromST_User

--不能这样插入

--select * into #t from dbo.ST_User

--添加一列，为int型自增长子段

altertable #t add [myid] int NOT NULLIDENTITY(1,1)

--添加一列，默认填充全球唯一标识

altertable #t add [myid1] uniqueidentifier NOT NULLdefault(newid())

select* from#t

droptable#t

--给查询结果集增加自增长列

--无主键时：

selectIDENTITY(int,1,1)as ID, Name,[Login],[Password] into #t fromST_User

select* from#t

--有主键时：

select(select SUM(1) from ST_User where ID<= a.ID) as myID,* from ST_User aorder bymyID

--定义表变量

declare@ttable

(

    idint notnull,

    msg nvarchar(50)null

)

insertinto @tvalues(1,‘1‘)

insertinto @tvalues(2,‘2‘)

select* from@t

Third, circulation

--while循环计算1到100的和

declare@aint

declare@sumint

set@a=1

set@sum=0

while @a<=100

begin

    set@sum[email protected]

    set@a+=1

end

print @sum

Iv. conditional statements

--if,else条件分支

if(1+1=2)

begin

    print‘对‘

end

else

begin

    print‘错‘

end

--when then条件分支

declare@todayint

declare@week nvarchar(3)

set@today=3

set@week=case

    when@today=1 then‘星期一‘

    when@today=2 then‘星期二‘

    when@today=3 then‘星期三‘

    when@today=4 then‘星期四‘

    when@today=5 then‘星期五‘

    when@today=6 then‘星期六‘

    when@today=7 then‘星期日‘

    else‘值错误‘

end

print @week

V. Cursors

declare@IDint

declare@Oidint

declare@Loginvarchar(50)

--定义一个游标

declareuser_cur cursor for select ID,Oid,[Login] fromST_User

--打开游标

openuser_cur

while @@fetch_status=0

begin

--读取游标

    fetchnext from user_cur into@ID,@Oid,@Login

    print @ID

    --print @Login

end

closeuser_cur

--摧毁游标

deallocateuser_cur

VI. triggers

Temporary tables in triggers:

Inserted data stored for INSERT and update operations Deleted the data before the delete and update operations

--创建触发器

CreatetriggerUser_OnUpdate

    OnST_User

    forUpdate

As

    declare@msg nvarchar(50)

    [email protected]记录修改情况

    select@msg = N‘姓名从“‘ + Deleted.Name + N‘”修改为“‘ + Inserted.Name + ‘”‘ fromInserted,Deleted

    --插入日志表

    insertinto[LOG](MSG)values(@msg)

    

--删除触发器

droptriggerUser_OnUpdate

Vii. Stored Procedures

--创建带output参数的存储过程

CREATEPROCEDUREPR_Sum

    @aint,

    @bint,

    @sumintoutput

AS

BEGIN

    set@sum[email protected][email protected]

END

--创建Return返回值存储过程

CREATEPROCEDUREPR_Sum2

    @aint,

    @bint

AS

BEGIN

    Return@[email protected]

END

    

--执行存储过程获取output型返回值

declare@mysumint

executePR_Sum 1,2,@mysumoutput

print @mysum

--执行存储过程获取Return型返回值

declare@mysum2int

execute@mysum2= PR_Sum2 1,2

print @mysum2

Eight, custom functions

Classification of functions:

1) Scalar value function

2) Table-valued function

A: Inline table-valued functions

B: Multi-statement table-valued functions

3) System functions

--新建标量值函数

createfunctionFUNC_Sum1

(

    @aint,

    @bint

)

returnsint

as

begin

    return@[email protected]

end

--新建内联表值函数

createfunctionFUNC_UserTab_1

(

    @myIdint

)

returnstable

as

return(select * from ST_User whereID<@myId)

--新建多语句表值函数

createfunctionFUNC_UserTab_2

(

    @myIdint

)

returns@ttable

(

    [ID] [int]NOTNULL,

    [Oid] [int]NOTNULL,

    [Login] [nvarchar](50)NOTNULL,

    [Rtx] [nvarchar](4)NOTNULL,

    [Name] [nvarchar](5)NOTNULL,

    [Password] [nvarchar](max)NULL,

    [State] [nvarchar](8)NOTNULL

)

as

begin

    insertinto @t select * from ST_User whereID<@myId

    return

end

--调用表值函数

select* fromdbo.FUNC_UserTab_1(15)

--调用标量值函数

declare@sint

set@s=dbo.FUNC_Sum1(100,50)

print @s

--删除标量值函数

dropfunctionFUNC_Sum1

Talk about the differences between custom functions and stored procedures:

One, Custom function:

1. You can return table variables

2. There are a lot of restrictions, including

Output parameters cannot be used;

Temporary tables cannot be used;

The operation inside the function cannot affect the external environment;

Cannot return result set through select;

Not update,delete, database tables;

3. Must return a scalar value or table variable

Custom functions are generally used in a high degree of reuse, simple function, the fight against the strong place.

Second, the stored procedure

1. Cannot return table variable

2. Fewer restrictions, you can perform operations on database tables, you can return datasets

3. You can return a scalar value, or you can omit the return

Stored procedures are generally used in the implementation of complex functions, data manipulation.

[Reprint study]?

Basic syntax for SQL Server programming