数据库

set identity_insert dbo.tbl_usreInfo on
declare @i int
set @i=1
while @i<=20000
begin
insert into dbo.tbl_usreInfo([users_id],users_name,users_password) values(@i,'htzer','htzer')
set @i=@i+1
end
set identity_insert dbo.tbl_usreInfo off

select * from dbo.tbl_usreInfo

--用not in分页
select top 10 * from dbo.tbl_usreInfo where
(users_id not in (select top 50 users_id from dbo.tbl_usreInfo order by users_id))
order by users_id

delete from dbo.tbl_usreInfo where users_id = 45

--用ID大于多少分页
select top 10 *
from dbo.tbl_usreInfo where
(users_id > (select max(users_id) from
(select top 50 users_id from dbo.tbl_usreInfo order by users_id)as t))
order by users_id


select top 10 * from tbl_usreInfo where (users_id > (select max(users_id) from (select top 50 users_id from tbl_usreInfo order by users_id) as t)) order by users_id


--利用存储过程分页
drop procedure page
create procedure page -- 创建存储过程
@sqlstr varchar(4000), --查询字符串
@currentpage int, --第几页
@pagesize int --每页几条数据
as
set nocount on
declare @p1 int,--p1是游标的ID
@rowcount int
exec sp_cursoropen @p1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
select ceiling(1.0*@rowcount/@pagesize) as 总页数,@rowcount as 总行数,@currentpage as 当前页
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @p1,16,@currentpage,@pagesize
exec sp_cursorclose @p1
set nocount off


exec page 'select * from tbl_usreInfo',1,10

create table tbl_tempa
(
id int identity primary key,
departname varchar(20),
departNo int,
persion varchar(20)
)
create table tbl_tempb
(
id int identity primary key,
departname varchar(20),
departNo int,
persion varchar(20)
)

insert into tbl_tempa values('100',100,'张三')
insert into tbl_tempb values('110',110,'李四')
drop trigger tb_update
create trigger tb_update on tbl_tempa for update
as
begin
update tbl_tempb set tbl_tempb.departname = tbl_tempa.departname,
tbl_tempb.persion = tbl_tempa.persion from
inserted tbl_tempa inner join tbl_tempb on tbl_tempa.id = tbl_tempb.id
end
select * from tbl_tempa
select * from tbl_tempb
update tbl_tempa set departname = '120',persion ='王五' where id=1