一、定義變量
--簡單賦值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 |
二、表、臨時表、表變量
--創(chuàng)建臨時表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查詢數(shù)據(jù)，填充至新生成的臨時表select * into #DU_User2 from ST_User where ID<8--查詢并聯(lián)合兩臨時表select * from #DU_User2 where ID<3 union select * from #DU_User1--刪除兩臨時表drop table #DU_User1drop table #DU_User2 |
--創(chuàng)建臨時表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,)--將查詢結(jié)果集（多條數(shù)據(jù)）插入臨時表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)--添加一列，默認填充全球唯一標識
|
