1.高效分页sql和储存过程
select top 每页条数 * from ( select ROW_NUMBER() over (order by id)as nid ,* from table01 ) as a where a.nid>(每页条数*(当前页数-1))
储存过程
if(exists(select * from sys.procedures where name='proc_MyPage')) drop proc proc_MyPage go Create proc proc_MyPage ( @PageSize int, @CurrentPage int ) as select top (@PageSize) * from ( select ROW_NUMBER()over(order by id) as nId ,* from table1 )as a where nId>((@PageSize)*((@CurrentPage)-1)
2.临时表
不创建临时表,直接向表中插入带自增Id的数据
select id=IDENTITY(int,1,1) ,* into #tempTb from table1--向临时表中插入table1的所以的数据,并有自增id if OBJECT_ID('tempdb..#tempTb') is not null --判断临时表是否存在 Begin truncate table #Tmp drop table #tempTb --删除临时表#tempTb End
3.视图
创建视图
create view v_table01 as select * from table01 --直接写sql即可
DROP VIEW v_table01 --删除视图
4.触发器
两张表calsses和student,向表calass表中插入数据,触发器先表student插入数据
插入表(instered表)和删除表(deleted表)。这两张是逻辑表也是虚表。有系统在内存中创建者两张表,不会存储在数据库中。而且两张表的都是只读的,只能读取数据而不能修改数据。这两张表的结果总是与被改触发器应用的表的结构相同。
if(OBJECT_ID('tgr_Classes_Insert','tr') is not null) drop trigger tgr_Classes_Insert go create trigger tgr_Classes_Insert on Classes for insert --插入触发 as declare @id int,@Cname varchar(20),@stuNo int select @id=id,@Cname=calssName, from inserted;--inserted表的机构和Classes(被触发应用的表)的表的结构相同
set @Cname=@Cname+CONVERT(nvarchar,@id);
set @stuNo=@id/2;
insert into student values(@id,'',@stuNo,18+@id,@Cname);
print '添加成功!';
go
insert into Classes values(2,'二班',8,8094) --插入数据触发触发器
--查询两张表的情况,查询结构如下图
select * from Classes
select * from student