SQL Server笔记

2021/4/8笔记

一、自增长设置

右击表设计，点击主键字段

二、创建存储过程

例子

三、查看前几条数据

--查看前两百条数据
select top(200)Roles.* from Roles;

四、单个执行语句

执行快捷键按F5

五、存储过程注意点

--存储过程
--判断是否存在
if(exists (select * from sys.objects where name='proc_get_User'))
	drop proc proc_get_User
go
create proc proc_get_User
as
	select * from Users;
	
--调用存储过程
exec proc_get_User;	

六、修改存储过程

--修改存储过程
ALTER proc proc_get_User
AS
select top(1)Users.* from Users;	

--调用存储过程
EXEC proc_get_User;

七、有参的存储过程

--带参储存过程
if(OBJECT_ID('proc_find_user','P')is not null)
	drop proc proc_find_user
GO
create proc proc_find_user(@userId int)
AS
	select * from Users where UserId = @userId;
GO	

--调用有参的存储过程
EXEC proc_find_user 2;	

八、带有通配符的存储过程

--带通配符参数的存储过程
if(OBJECT_ID('proc_findUserByUserName','P')is not null)
	drop proc proc_findUserByUserName
GO
create proc proc_findUserByUserName(@userName varchar(20) = '张%')
AS
	select * from Users where Username like @userName;
GO	

--执行带通配符的参数存储过程
exec proc_findUserByUserName;

九、带有输出参数的存储过程

--带通配符参数的存储过程
if(OBJECT_ID('proc_findUserByUserName','P')is not null)
	drop proc proc_findUserByUserName
GO
create proc proc_findUserByUserName(@userName varchar(20) = '张%')
AS
	select * from Users where Username like @userName;
GO	

--执行带通配符的参数存储过程
exec proc_findUserByUserName;

十、带有输入输出参数的存储过程

if (object_id('proc_getStudentRecord', 'P') is not null)
    drop proc proc_getStudentRecord
go
create proc proc_getStudentRecord(
    @id int, --默认输入参数
    @name varchar(20) out, --输出参数
    @age varchar(20) output--输入输出参数
)
as
    select @name = name, @age = age  from student where id = @id and sex = @age;
go

-- 
declare @id int,
        @name varchar(20),
        @temp varchar(20);
set @id = 7; 
set @temp = 1;
exec proc_getStudentRecord @id, @name out, @temp output;
select @name, @temp;
print @name + '#' + @temp;

十一、不缓存储存过程

--WITH RECOMPILE 不缓存
if(OBJECT_ID('proc_temp','P')is not null)
	drop proc proc_temp
GO
create proc proc_temp
WITH RECOMPILE 
AS
	select * from Roles;
GO

--执行存储过程
EXEC proc_temp;	

十二、加密的存储过程

--加密过的存储过程
--加密指令 WITH ENCRYPTION
--注意：这里的加密指的是不能查看这个存储过程的语句
if(OBJECT_ID('proc_temp_encryption','p') is not null)
 drop proc proc_temp_encryption
Go
create proc proc_temp_encryption
with encryption 
as 
	select * from Roles;
Go

--调用存储过程
EXEC proc_temp_encryption;	
--显示这个存储过程的语句
EXEC sp_helptext 'proc_temp';
--显示对象 'proc_temp_encryption' 的文本已加密
EXEC sp_helptext 'proc_temp_encryption';

十三、带有游标的存储过程

--带有游标参数的存储过程
if(OBJECT_ID('proc_cursor','p')is not null)
	drop proc proc_cursor
go
create proc proc_cursor
	@cur cursor varying output
as
	set @cur = cursor forward_only static for
	select RoleId,RoleName,UserId from Roles;
	open @cur --打开游标
Go
--声明参数
DECLARE @exec_cur cursor;
DECLARE @roleId int,
		@roleName varchar(30),
		@userId int;
exec proc_cursor @cur = @exec_cur output --调用存储过程
fetch next from @exec_cur into @roleId,@roleName,@userId; --提取游标
--注意这里打印出来的是，使用了while循环，当循环结束再赋值打印，出来结果都是一样，打印次数看数据条数
--解决：使用report循环进行解决
--roleId：2, roleName：部门经理, userId：2
--roleId：2, roleName：部门经理, userId：2
while(@@FETCH_STATUS = 0)
begin
	fetch next from @exec_cur into @roleId,@roleName,@userId;
	print 'roleId：' + convert(varchar,@roleId)+', roleName：'+@roleName +', userId：' + convert(varchar,@userId);	
end

close @exec_cur; --关闭游标
deallocate @exec_cur; --删除游标

十四、分页存储过程(两种)

--分页存储过程row_number完成分页，从第几条开始，返回几条数据
if(OBJECT_ID('pro_page','p') is not null)
	drop proc pro_page
go 
create proc pro_page
	@startIndex int,
	@endIndex int
as
	select count(*) from Users;	
	select * from (select ROW_NUMBER() over(order by userId) as rowId,* from Users) temp
	where temp.rowId between @startIndex and @endIndex
go

--调用
--从第几条开始，返回几条数据
exec pro_page 2,4;		

--分页存储过程
if(OBJECT_ID('pro_page_user','p') is not null)
	drop proc pro_page_user
go 
create proc pro_page_user(
	@pageIndex int, --第几页开始
	@pageSize int	--每页多少数据
	--例子：假设十条数据，每页两条数据，所以总共五页，第一页显示第一二条数据，第二页显示第三四条数据，以此类推
)
as
	declare @startRow int,@endRow int
	set @startRow = (@pageIndex - 1) * @pageSize + 1
	set @endRow = @startRow + @pageSize - 1
	print @startRow --第几条开始
	print @endRow	--第几条结束
	select * from(
		select  *,ROW_NUMBER() over (order by userId asc) as number from Users 	
		) u
		where u.number between @startRow and @endRow;
select COUNT(*) from Users;		
exec pro_page_user 3,2;			

十五、使用GUI执行存储过程

对象资源管理器栏：
		可编程性
			存储过程-list
				选中相对应的存储过程右键选择执行存储过程