--定义变量
declare @a int;
--赋值
set @a = 123456;
--输出
print @a;
--使用select语句赋值
declare @student nvarchar(50);
select @student = Sname from student where sid = 1001;
print @student
--使用update语句赋值
declare @user nvarchar(50);
update student set @user=sname where sid = 1002;
print @user
select * from student;
--更新语句
declare @user2 nvarchar(50);
set @user2 = 'dddd'
update student set sname = @user2 where sid = 1002;
print @user2
select * from student;
--创建临时表
create table #users(
[uid] [int] not null,
[uname] [nvarchar](50) not null,
[uage] [int] null
);
--向临时表插入一条数据
insert into #users(uid,uname) values(1003,'zizi');
select * from #users;
insert into #users(uid,uname) values(1,'11');
insert into #users(uid,uname) values(2,'22');
insert into #users(uid,uname) values(3,'33');
insert into #users(uid,uname) values(4,'44');
insert into #users(uid,uname) values(5,'55');
insert into #users(uid,uname) values(6,'66');
insert into #users(uid,uname) values(7,'77');
create table #users2(
[uid] [int] not null,
[uname] [nvarchar](50) not null,
[uage] [int] null
);
insert into #users2 values(1008,'lll',20);
--注意：从#users表中查询出数据保存到#users3中，#users3不用自己创建
select * into #users3 from #users where uid<5;
select * from #users3;
--查询并联合两个临时表
select * from #users where uid<5 union select * from #users2
select * from #users where uid<5 union select * from student
--删除临时表
drop table #users;
drop table #users2;
drop table #users3;
CREATE TABLE #t(
[EmployeeID] [int] NOT NULL, -- 员工ID
[EmployeeName] [nvarchar](50) NULL, --员工姓名
[OTHours] [int] NULL, --这天加班小时数
[OTDate] [date] NULL, --加班日期
[PayPerHour] [int] NULL --每小时的加班费
) ON [PRIMARY]
--将查询结果集（多条数据）插入临时表
insert into #t select * from OTList;
select * from #t;
--另一种方式
select * into #tt from OTList;
select * from #tt
--添加一列，为int型自增长字段，从1开始递增，每次自增1
alter table #t add [myid] int not null identity(1,1)
--添加一列，默认填充全球唯一标识
alter table #t add [myid1] uniqueidentifier not null default(newid())
--newid() 创建类型uniqueidentifier的唯一值。
drop table #t;
drop table #tt;
--给查询结果集增加自增长列
--无主键时
select identity(int,1,1) as id,[sname],[sage] into #t from student;
select * from #t;
--有主键时
select (select sum(1) from OTList where EmployeeID <= o.EmployeeID)as myID,* from OTList o order by myID;
select * from OTList;
--定义表变量
declare @t table
(
id int not null,
msg nvarchar(50) null
)
insert into @t values(1,'1')
insert into @t values(2,'2')
select * from @t
--while循环计算1到100的和
declare @i int
declare @sum int
set @i=1
set @sum=0
while @i<=100
begin
set @sum+=@i
set @i+=1
end
print @sum
--if,else条件分支
if(1+1=2)
begin
print 'right'
end
else
begin
print 'wrong'
end
--when then条件分支
declare @today int
declare @week nvarchar(20)
set @today=3
set @week=case
when @today=1 then 'Monday'
when @today=2 then 'Tuesday'
when @today=3 then 'Wednesday'
when @today=4 then 'Thursday'
when @today=5 then 'Friday'
when @today=6 then 'Saturday'
when @today=7 then 'Sunday'
else 'error'
end
print @week
--创建带output参数的存储过程
CREATE PROCEDURE PR_Sum
@a int,
@b int,
@sum int output
AS
BEGIN
set @sum=@a+@b
END
--创建Return返回值存储过程
CREATE PROCEDURE PR_Sum2
@a int,
@b int
AS
BEGIN
Return @a+@b
END
--执行存储过程获取output型返回值
declare @mysum int
execute PR_Sum 1,2,@mysum output
print @mysum
--执行存储过程获取Return型返回值
declare @mysum2 int
execute @mysum2= PR_Sum2 1,2
print @mysum2
/*
自定义函数
函数的分类：
1）标量值函数
2）表值函数
a:内联表值函数
b:多语句表值函数
3）系统函数
*/
--新建标量值函数
create function FUNC_Sum1
(
@a int,
@b int
)
returns int
as
begin
return @a+@b
end
--新建内联表值函数
create function FUNC_UserTab_1
(
@myId int
)
returns table
as
return (select * from ST_User where ID<@myId)
--新建多语句表值函数
create function FUNC_UserTab_2
(
@myId int
)
returns @t table
(
[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
)
as
begin
insert into @t select * from ST_User where ID<@myId
return
end
--调用表值函数
select * from dbo.FUNC_UserTab_1(15)
--调用标量值函数
declare @s int
set @s=dbo.FUNC_Sum1(100,50)
print @s
--删除标量值函数
drop function FUNC_Sum1
