- T-SQL
- 变量
- 流程控制语句
- 存储过程
一、T-SQL(transact sql)
(1)数据定义部分(DDL Data Definition Language)
1)create
2)alter
3)drop
(2)数据操作部分(DML Data Manipulation Language)
1)insert
2)delete
3)update
4)select
(3)数据控制部分(DCL Data Control Language):权限控制
1)grant
2)revoke
(4)流程控制语句部分
1)begin
2)end
3)if,else
4)else if
5)while
2.全局变量(系统已定义):
select @@+变量名
3. 局部变量
- 定义局部变量:declare @+变量名
- 给局部变量赋值:set @变量名=
- 输出变量:print @变量名
4. 流程控制语句
if else
If 判断条件
Begin
执行语句
End
Else
Begin
执行语句
End
waitfor
1)Waitfor delay ’00:00:05’ 延迟5秒执行语句
2)waitfor time ’10:17’ 系统时间为10:17时执行语句
while循环
While 判断条件
Begin
执行语句
End
5. 存储过程:实现某种功能,已编译的语句块集合
作用
1)提高效率
2)减少网络流量的产生
类型
1)系统存储过程:sp_存储过程名
2)可拓展存储过程:xp_存储过程名
3)自定义存储过程:自定义
创建存储过程
Creale procedure 存储过程名
(参数)
As
功能语句
执行存储过程 exec
Exec 存储过程名
删除存储过程
Drop procedure 存储过程名
create table userTable(
userName varchar(10) primary key,
userPasswd varchar(10),
userCheckPasswd varchar(10))
create procedure pro_zhuce3
@username varchar(30),
@checkpassword1 varchar(30),
@checkpassword2 varchar(30)
as
declare @number int
set @number=(select COUNT(*) from userTable where userName=@username)
if @number=1
begin
print '该用户已存在'
end
else if @checkpassword1=@checkpassword2
begin
insert into userTable values(@username,@checkpassword1,@checkpassword2)
end
else
begin
print '两次密码输出不正确'
end
exec pro_zhuce3 steven123,12345,12345
select * from userTable
create procedure pro_denglu1
@username varchar(30),
@password varchar(30)
as
declare @number int,@code varchar(30)
set @number=(select COUNT(*) from userTable where userName=@username)
set @code=(select userPasswd from userTable where userName=@username)
if @number=1 and @code=@password
begin
print '登陆成功'
end
else if @number!=1
begin
print '用户不存在'
end
else
begin
print '密码错误'
end
exec pro_denglu1 steven4,12345
create procedure pro_xiugai2
@username varchar(30),
@password varchar(30),
@newPassword varchar(30)
as
declare @number int,@code varchar(30)
set @number=(select COUNT(*) from userTable where userName=@username)
set @code=(select userPasswd from userTable where userName=@username)
if @number=1 and @code=@password
begin
update userTable
set userPasswd=@newPassword
where userName=@username
print '修改成功'
end
else if @number!=1
begin
print '用户不存在'
end
else
begin
print '密码错误'
end
exec pro_xiugai2 123123,12345,19931016
create procedure pro_delete1
@username varchar(30),
@password varchar(30)
as
declare @number int,@code varchar(30)
set @number=(select COUNT(*) from userTable where userName=@username)
set @code=(select userPasswd from userTable where userName=@username)
if @number=1 and @code=@password
begin
delete from userTable
where userName=@username
print '删除成功'
end
else if @number!=1
begin
print '用户不存在'
end
else
begin
print '密码错误'
end
exec pro_delete1 '123nine',123123
select * from userTable