一、定义变量
--简单赋值declare @a intset @a=5PRint @a--使用select语句赋值declare @user1 nvarchar(50)select @user1='张三'print @user1declare @user2 nvarchar(50)select @user2 = Name from ST_User where ID=1print @user2--使用update语句赋值declare @user3 nvarchar(50)update ST_User set @user3 = Name where ID=1print @user3 |
二、表、临时表、表变量
--创建临时表1create table #DU_User1([ID] [int] NOT NULL,[Oid] [int] NOT NULL,[Login] [nvarchar](50) NOT NULL,[Rtx] [nvarchar](4) NOT NULL,[Name] [nvarchar](5) NOT NULL,[PassWord] [nvarchar](max) NULL,[State] [nvarchar](8) NOT NULL);--向临时表1插入一条记录insert into #DU_User1 (ID,Oid,[Login],Rtx,Name,[Password],State) values (100,2,'LS','0000','临时','321','特殊');--从ST_User查询数据，填充至新生成的临时表select * into #DU_User2 from ST_User where ID<8--查询并联合两临时表select * from #DU_User2 where ID<3 union select * from #DU_User1--删除两临时表drop table #DU_User1drop table #DU_User2 |
--创建临时表CREATE TABLE #t([ID] [int] NOT NULL,[Oid] [int] NOT NULL,[Login] [nvarchar](50) NOT NULL,[Rtx] [nvarchar](4) NOT NULL,[Name] [nvarchar](5) NOT NULL,[Password] [nvarchar](max) NULL,[State] [nvarchar](8) NOT NULL,)--将查询结果集（多条数据）插入临时表insert into #t select * from ST_User--不能这样插入--select * into #t from dbo.ST_User--添加一列，为int型自增长子段alter table #t add [myid] int NOT NULL IDENTITY(1,1)--添加一列，默认填充全球唯一标识
|
