数据库编程2

发表于：2006年10月13日 13时26分15秒
举报本文链接：http://user.qzone.qq.com/183481361/blog/15

数据库编程2
--title1
use test
go
if exists(select name from sysobjects where name='xscjzb' and type='U')
drop table xscjzb
go
if exists(select name from sysobjects where name='xj_yxjjbxx' and type='U')
drop table xj_yxjjbxx
go
if exists(select name from sysobjects where name='class' and type='U')
drop table class
go
if exists(select name from sysobjects where name='major' and type='U')
drop table major
go
if exists(select name from sysobjects where name='department' and type='U')
drop table department
go
create table department
(yxbh varchar(10) not null primary key,
yxmc varchar(50),
)
go
create table major
(zybh varchar(10) not null primary key,
zymc varchar(50),
yxbh varchar(10) not null constraint fk_major_department foreign key references department(yxbh) on update cascade)
go
create table class
(bjbh varchar(10) not null primary key,
bjjc varchar(50),
rxnf varchar(20) not null default '2006',
bjrs int,
zybh varchar(10) not null constraint fk_class_major foreign key references major(zybh) on update cascade)
go
create table xj_yxjjbxx
(xh varchar(10) not null primary key,
xm varchar(50),
xb varchar(2),
bjbh varchar(10) not null constraint fk_class1 foreign key references class(bjbh) on update cascade)
go
create table xscjzb
(xnxqh varchar(11) not null,
xh varchar(20) not null,
kcbm varchar(11) not null,
ksxzcode varchar(10) not null,constraint pk_lld primary key(xnxqh,xh,kcbm,ksxzcode),
zcj money)
go
insert into department values('01','电子与信息工程学院')
insert into department values('02','教务处')
go
insert into major values('1','计算机科学与技术','01')
insert into major values('2','外国语学院','02')
insert into major values('3','电子','01')
go
insert into class values('0201','计1','2006',35,'1')
insert into class values('0202','计2','2006',32,'1')
insert into class values('0203','计3','2006',36,'1')
insert into class values('0204','电1','2006',32,'3')
insert into class values('0205','电1','2006',29,'3')
insert into class values('0206','英1','2006',30,'2')
go
insert into xj_yxjjbxx values('20020300','邱磊','男','0203')
insert into xj_yxjjbxx values('20020301','邱磊1','男','0203')
insert into xj_yxjjbxx values('20020302','邱磊2','男','0203')
insert into xj_yxjjbxx values('20020303','邱磊3','男','0203')

insert into xj_yxjjbxx values('20020304','张三','男','0201')
insert into xj_yxjjbxx values('20020305','张三1','男','0201')
insert into xj_yxjjbxx values('20020306','张三2','男','0201')
insert into xj_yxjjbxx values('20020307','张三3','男','0201')

insert into xj_yxjjbxx values('20020308','李四','男','0202')
insert into xj_yxjjbxx values('20020309','李四1','男','0202')

insert into xj_yxjjbxx values('20020310','王五','男','0206')
insert into xj_yxjjbxx values('20020311','王五1','男','0206')
insert into xj_yxjjbxx values('20020312','王五2','男','0206')
insert into xj_yxjjbxx values('20020313','王五3','男','0206')
insert into xj_yxjjbxx values('20020314','王五','男','0206')

if exists(select name from sysobjects where name='c_m_d_views' and type='V')
drop view c_m_d_views
go
create view c_m_d_views
as
select class.bjbh as 班级编号,major.zybh as 专业编号,major.yxbh as 院系编号,class.bjjc as 班级简称,class.rxnf as 入学年份,class.bjrs as 班级人数,major.zymc as 专业名称,department.yxmc as 院系名称
from
class left outer join major on class.zybh=major.zybh left outer join department on major.yxbh=department.yxbh
go
select * from c_m_d_views
go


if exists(select name from sysobjects where name='p_addclass' and type='P')
drop procedure p_addclass
go
create procedure p_addclass
(@bjbh varchar(10),
@zybh varchar(10),
@bjjc varchar(50),
@rxnf varchar(20),
@bjrs int)
as
insert into class(bjbh,zybh,bjjc,rxnf,bjrs)
values(@bjbh,@zybh,@bjjc,@rxnf,@bjrs)
go
exec p_addclass '7','2','法2','2006',32
go
select * from c_m_d_views
go

--分离database
use master
go
--分离数据库
exec sp_detach_db test
GO
--还原数据库
CREATE DATABASE test1
ON PRIMARY (FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\test_data.mdf')
FOR attach
GO

--分页1
use test
go
if exists(select name from sysobjects where type='p' and name='pageindex')
drop procedure pageindex
go
create procedure pageindex
(
@pagesize int,
@page int,
@zybh varchar(10)
)
as
begin
SET nocount ON
declare @tableindex table(id int identity(1,1),sid varchar(10))
declare @pagelowerbound int
declare @pageupperbound int
set @pagelowerbound=(@page-1)*@pagesize
set @pageupperbound=@pagelowerbound+@pagesize
if @zybh<>''
insert into @tableindex(sid) select bjbh from class where zybh = @zybh order by BJBH
else
insert into @tableindex(sid) select bjbh from class order by BJBH
select * from class inner join @tableindex as a on class.bjbh=a.sid where id>@pagelowerbound and id<=@pageupperbound and class.zybh=@zybh
--select rc=@@rowcount
set nocount off
end
GO
select * from class
go
exec pageindex @pagesize=3,@page=1,@zybh='1'
GO

--分页2
if exists(select name from sysobjects where type='P' and name='pagination3')
drop procedure pagination3
go
CREATE PROCEDURE pagination3
(
@tblName varchar(255), -- 表名
@strGetFields varchar, -- 需要返回的列
@fldName varchar(255), -- 排序的字段名
@PageSize int, -- 页尺寸
@PageIndex int = 1, -- 页码
@doCount bit, -- 返回记录总数, 非 0 值则返回
@OrderType bit, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) -- 查询条件 (注意: 不要加 where)
)
AS
declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型

if @doCount != 0
begin
if @strWhere <>''
set @strSQL = 'select + count(*) as Total from [' + @tblName + '] where' +@strWhere
else
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'
end
--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况：

else
begin
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @fldName +'] desc'
--如果@OrderType不是0，就执行降序，这句很重要！

end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @fldName +'] asc'
end
if @PageIndex = 1
begin
if @strWhere != ''

set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '
　　　　　　　　from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder
else

set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '
　　　　　　　　from ['+ @tblName + '] '+ @strOrder
--如果是第一页就执行以上代码，这样会加快执行速度

end
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['+ @fldName + '])
　　　　　　from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['+ @fldName + ']
　　　　　　from [' + @tblName + ']' + @strOrder + ') as tblTmp)'+ @strOrder

if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
end
end
go

--exec (@strSQL)