create database demo
go
use demo
go
/****收入财务报表***/
create table cw_image
(
cw_id int,
cw_year varchar(20),
cw_month int,
cw_money money
)
/****财务支出报表*****/
create table cw_image1
(
cwz_id int,
cwz_year varchar(20),
cwz_month int,
cwz_money money
)
select * from cw_image
/*****管理员表*******/
create table m_user
(
m_id int identity(1,1) primary key, /******管理员表ID******/
m_name varchar(20) not null,/******管理员用户名******/
m_psw varchar(20) not null, /******管理员密码******/
)
go
go
/*****CD类别表*******/
create table lb_cd
(
lcd_id int identity(1,1) primary key,/*****CD类别ID******/
lcd_name varchar(50) not null,/***********CD类别名称********/
lcd_remark varchar(50) not null /*****CD类别说明******/
)
/*******CD表***********/
create table c_cd
(
c_id int identity(1,1) primary key,/*****CD表ID******/
lcd_id int references lb_cd(lcd_id),/*****CD表引用CD类别表外键**************/
c_name varchar(50) not null,/********CD名称***********/
c_price money,/*********CD价格**********/
c_remark varchar(20) not null,/*******CD简介************/
c_img varchar(200),
c_flag varchar(6) /********CD状态***********/
)
go
/******DVD类别表*************/
create table lb_dvd
(
ldvd_id int identity(1,1) primary key,/*******DVD类别ID************/
ldvd_name varchar(50) not null,/*********DVD类别名称**********/
ldvd_remark varchar(50) not null/********DVD类别说明***********/
)
go
/******DVD表*************/
select lb_dvd.ldvd_name,d_dvd.* from lb_dvd,d_dvd where d_dvd.ldvd_id=lb_dvd.ldvd_id
create table d_dvd
(
d_id int identity(1,1) primary key,/*****DVD表ID******/
ldvd_id int references lb_cd(lcd_id),/*****DVD表引用DVD类别表外键**************/
d_name varchar(50) not null,/********DVD名称***********/
d_price money,/*********DVD价格**********/
d_remark varchar(20) not null,/*******DVD简介************/
d_img varchar(200),
d_flag varchar(6) /********DVD状态***********/
)
go
/**分页的存储过程*/
create PROCEDURE Ture_Page
@PageSize int, --每页的行数
@PageIndex int, --1 代表第一页
@Col varchar(250), --要显示的字段
@Table varchar(200), --所用到的表,复条的话就写from与where之间的内容
@Where varchar(200)='', --所用到的条件
@OKey varchar(50), --排序字段
@Order varchar(20)='ASC' --排序方式
as
declare @cmdstr varchar(2000)
declare @cmdstrcount varchar(2000)
set nocount on
set @cmdstr='select top '
set @cmdstr=@cmdstr+convert(nvarchar,@PageSize)
if @Order='DESC' and @PageIndex>1
set @cmdstr=@cmdstr+' '+@Col+' from '+@Table+' where '+@OKey+'<'
else if @PageIndex=1
set @cmdstr=@cmdstr+' '+@Col+' from '+@Table+' where '+@OKey+'>='
else
set @cmdstr=@cmdstr+' '+@Col+' from '+@Table+' where '+@OKey+'>'
if @PageIndex>1
begin
if @Order='ASC'
set @cmdstr=@cmdstr+'(select max ('+@OKey+') from (select top '
else
set @cmdstr=@cmdstr+'(select min ('+@OKey+') from (select top '
set @cmdstr=@cmdstr+convert(nvarchar,(@PageIndex-1)*@PageSize)
if @Where<>''
set @cmdstr=@cmdstr+' '+@OKey+' from '+@Table+' where '+@Where+' order by '+@OKey+' '+@Order+') as t) '
else
set @cmdstr=@cmdstr+' '+@OKey+' from '+@Table+' order by '+@OKey+' '+@Order+') as t) '
end
else
set @cmdstr=@cmdstr+' 0 ' --convert(nvarchar,0)
print @cmdstr
if @Where<>''
set @cmdstr=(@cmdstr+' and '+@Where+' order by '+@OKey+' '+@Order)
else
set @cmdstr=(@cmdstr+' order by '+@OKey+' '+@Order)
print @cmdstr
exec(@cmdstr)
set nocount off
GO
