图书管理系统--创建数据库和表

1、/*1管理员表（l_administrator）字段名字段说明数据类型约束备注a_id管理员编号intprimary keyidentity(1000,1)a_name管理员姓名nvarchar(20)not nulla_pwd管理员密码varchar(20)not null */ use library gocreate table l_administrator(a_id int not null primary key identity(1000,1),a_name nvarchar(20) not null,a_pwd varchar(20) not null);/*2职务类型表（l_

2、duty）字段名字段说明数据类型约束备注d_id职务编号intprimary keyidentity(1000,1)d_name职务名称nvarchar(20)not nulld_maxcount最大借阅数量tinyintnot null*/use librarygocreate table l_duty(d_id int not null primary key identity(1000,1),d_name nvarchar(20) not null,d_maxcount tinyint not null);/*3读者表（l_reader）字段名字段说明数据类型约束备注r_id读者编号bi

3、gintprimary keyr_name读者姓名nvarchar(20)not nullr_pwd读者密码varchar(20)not nullr_sex读者性别bitnot nullr_typeid职务类型intforeign key职务类型表的主键r_academy所在院系nvarchar(20)r_major专业nvarchar(20)r_contact联系方式varchar(20)r_email邮箱nvarchar(20)r_photo读者照片nvarchar(100)存的是读者照片的路径*/use librarygocreate table l_reader(r_id bigint

4、 not null primary key,r_name nvarchar(20) not null,r_pwd varchar(20) not null,r_sex bit not null,r_typeid int not null,r_academy nvarchar(20),r_major nvarchar(20),r_contact varchar(20),r_email varchar(20),r_photo nvarchar(100);alter table l_reader add constraint fk_dtypeid foreign key(r_typeid) refe

5、rences l_duty(d_id)on delete cascadeon update cascade;/*创建一个存储过程*/use librarygocreate procedure readerr_id bigint,r_name nvarchar(20),r_pwd varchar(20),r_sex bit,r_typeid int,r_academy nvarchar(20),r_major nvarchar(20),r_contact varchar(20),r_email varchar(20),r_photo nvarchar(100)asbegininsert into

6、 l_reader(r_id,r_name,r_pwd,r_sex,r_typeid,r_academy,r_major,r_contact,r_email,r_photo)values(r_id,r_name,r_pwd,r_sex,r_typeid,r_academy,r_major,r_contact,r_email,r_photo);end/*4图书类型表（l_booktype）字段名字段说明数据类型约束备注bt_id类型编号intprimary keyidentity(1000,1)bt_name类型名称nvarchar(20)not null*/use librarygocreat

7、e table l_booktype(bt_id int not null primary key identity(1000,1),bt_name nvarchar(20) not null);/*5出版社信息表（l_publishing）字段名字段说明数据类型约束备注isbn国际标准图书编码char(13)primary keyp_name出版社名称nvarchar(30)not null*/use librarygocreate table l_publishing(isbn char(13) not null primary key,p_name nvarchar(30) not nu

8、ll); /*6图书信息表（l_book）字段名字段说明数据类型约束备注b_id图书编号varchar(30)primary keyidentity(1000,1)b_name图书名称nvarchar(30)not nullisbn国际标准图书编码char(13)foreign key13位数字组成b_bkcaseid书架编号varchar(20)b_price定价numeric(10,2)b_author作者nvarchar(20)b_typeid类型编号intforeign keyb_intime入库时间datetimeb_synopsis图书简介nvarchar(500)b_state图

9、书状态bit0-借出，1-没有借出b_photo封面图片nvarchar(100)存的是路径*/use librarygocreate table l_book(b_id varchar(20) not null primary key ,b_name nvarchar(30) not null,isbn char(13),b_bkcaseid varchar(20),b_price numeric(10,2) not null,b_author nvarchar(20),b_typeid int,b_intime datetime,b_synopsis nvarchar(1000),b_st

10、ate bit not null default 0,b_photo nvarchar(100);alter table l_book add constraint fk_btypeid foreign key(b_typeid) references l_booktype(bt_id) on delete cascadeon update cascade;alter table l_book add constraint fk_bisbn foreign key(isbn) references l_publishing(isbn) on delete cascadeon update ca

11、scade;alter table l_book drop column b_bkcaseid/*创建存储过程*/use librarygocreate procedure bookb_name nvarchar(30),isbn char(13),b_bkcaseid varchar(20),b_price numeric(10,2),b_author nvarchar(20),b_intime datetime,b_synopsis nvarchar(1000),b_photo nvarchar(100)asbegininsert into l_book(b_name,isbn,b_bkc

12、aseid,b_price,b_author,b_intime,b_synopsis,b_photo)values(b_name,isbn,b_bkcaseid,b_price,b_author,b_intime,b_synopsis,b_photo);end/*7借阅管理表（l_borrow）字段名字段说明数据类型约束备注bw_id借阅编号intprimary keyidentity(1,1)bw_bookid图书编号varchar(20)foreign keybw_readerid读者编号intforeign keybw_outtime借出日期datetimenot nullbw_endt

13、ime到期日期datetimenot nullbw_backtime归还日期datetimebw_isexpired是否过期bitnot null默认为0-不过期bw_fine罚款数目numeric(10,2)过期后才计算罚款数目*/use librarygocreate table l_borrow(bw_id int not null primary key identity(1,1),bw_bookid varchar(20),bw_readerid bigint ,bw_outtime datetime not null,bw_endtime as dateadd(d,30,bw_ou

14、ttime),bw_backtime datetime,bw_isexperied bit default 0,bw_fine numeric(10,2) default 0.00);alter table l_borrow add constraint fk_bookid foreign key(bw_bookid) references l_book(b_id)on delete cascadeon update cascade;alter table l_borrow add constraint fk_readerid foreign key(bw_readerid) referenc

15、es l_reader(r_id)on delete cascadeon update cascade;/*8图书资源表（l_resource）字段名字段说明数据类型约束备注rs_id资源编号intprimary keyidentity(1000,1)rs_name资源名称nvarchar(30)not nullrs_synopsis资源简介nvarchar(500)rs_amount资源大小int单位为kb或是mbrs_type资源类型varchar(20)类似于doc、xsl、ppt、pdf、zip、rar、mp3、wmv等常用格式*/use librarygocreate table l

16、_resource(rs_id int not null primary key identity(1000,1),rs_name nvarchar(30) not null,rs_synopsis nvarchar(500),rs_amount bigint,rs_type varchar(20);/*9图书评论表（l_bookmarks）字段名字段说明数据类型约束备注isbn国际标准图书编码char(13)foreign keybm_contents评论内容nvarchar(500)not nullbm_time评论时间datetimenot null*/use librarygocreate table l_bookmarks(isbn char(13) not null,bm_contents nvarchar(500) not null,bm_time datetime not null);alter table l_bookmarks add constraint fk_bmisbn foreign key(isbn) references l_publishing(isbn)on delete cascadeon update cascade;/*10书架信息表（l_bookcase）字段名字段说明数据类型约束备注bc_id书架编号int