e @classid int ---班级ID
set @classid=(select classid from grade where classname=@classname) --通过参数班级名称获取对应的班级ID
select * from Student where Sex=@sex and ClassId=@classid
set @cnt= (select COUNT(*) from Student where Sex=@sex and ClassId=@classid) --获取指定班级和性别的总人数
set @totalnum=(select COUNT(*) from Student) ----获取总人数
go
--调用存储过程,获取指定性别的学员人数及总人数
declare @num int,@tnum int
exec usp_getCountBySexandClassName @cnt=@num output ,@totalnum=@tnum output , @className='八期班'
print @num
print @tnum
print '做完了'
---获取指定班级的人数
if exists(select * from sysobjects where name='usp_getCount')
drop proc usp_getCount
go
create procedure usp_getCount
@className nvarchar(50)='八期班'
as
declare @classid int=(select classid from grade where classname=@className)
declare @cnt int
set @cnt =(select COUNT(*) from Student where ClassId=@classid)
--return 只能返回int整数值
--return '总人数是'+cast(@cnt as varchar(2))
return @cnt
go
--调用存储过程，接收存储过程的返回值
declare @count int
--set @count=(exec usp_getCount)
exec @count=usp_getCount '八期班'
print @count
if exists(select * from sysobjects where name='usp_getClassList')
drop proc usp_getClassList
go
create procedure usp_getClassList
as
select classid,classname from grade
go
?
?
21.分页存储过程
if exists(select * from sysobjects where name='usp_getPageData')
drop proc usp_getPageData
go
create procedure usp_getPageData
@totalPage int output,--总页数
@pageIndex int =1 ,--当前页码，默认是第一页
@pageCount int =5 --每一页显示的记录数
as
select * from (select ROW_NUMBER() over(order by studentno) id,* from Student) temp where temp.id>(@pageindex-1)*@pagecount and temp.id<=(@pageindex*@pagecount)
set @totalPage=CEILING((select COUNT(*) from Student)*1.0/@pageCount)
go
?
?
?
22.索引
--创建局部临时表
create table #newGrade
(
classid int ,
classname nvarchar(50)
)
---局部临时表只有在当前创建它的会话中使用，离开这个会话临时表就失效.如果关闭创建它的会话，那么临时表就会消失
insert into #newGrade select * from grade
select * from #newGrade
select * into #newnewnew from grade
select * into newGrade from #newgrade
--创建全局临时表:只要不关闭当前会话，全局临时表都可以使用，但是关闭当前会话，全局临时表也会消失
create table ##newGrade
(
classid int ,
classname nvarchar(50)
)
drop table ##newGrade
select * into ##newGrade from grade
select * from ##newGrade
--创建表变量
declare @tb table(cid int,cname nvarchar(50))
insert into @tb select * from grade
select * from @tb
?
?
select * from sysindexes
?
--create ?index IX_Student_studentName
--on 表名(字段名)
?
--clustered index:聚集索引 ?nonclustered index--非聚集索引
if exists(select * from sysindexes where name='IX_Student_studentName')
?drop index student.IX_Student_studentName
go?
create clustered index IX_Student_studentName
on student(studentname)
?
--如果是先创建主键再创建聚集索引就不可以，因为主键默认就是聚集索引
--但是如果先创建聚集索引，那么还可以再创建主键，因为主键不一定需要是聚集的
?
?
23.临时表
?
?