数据库课程设计(图书借阅管理系统数据库的设计与实现)-文库吧

【正文】 ), publish_date datetime, price int, book_sum int, book_rem int ) create table student_borrow_infor (stu_id varchar(20) foreign key references student_infor(stu_id), book_id varchar(20) foreign key references book_infor(book_id), borrow_date datetime, return_date datetime, borrow_times int, stu_fine int, primary key(stu_id,book_id) ) create table teacher_borrow_infor (tea_id varchar(20) foreign key references teacher_infor(tea_id), book_id varchar(20) foreign key references book_infor(book_id), borrow_date datetime, return_date datetime, borrow_times int, tea_fine int, primary key(tea_id,book_id) ) 创建视图 create view stu_infor_view as select stu_id,stu_name from student_infor select * from stu_infor_view create view stu_borrow_view as select stu_id,book_id from student_borrow_infor select * from stu_borrow_view create view book_view as select book_id,book_name from book_infor 查询结果为 : 创建索引： create index stu_index on student_infor(stu_id) create index stu_bor_index on student_borrow_infor(stu_id,book_id) create index book_index on book_infor(book_id) exec sp_helpindex student_infor exec sp_helpindex student_borrow_infor exec sp_helpindex book_infor 查询结果为： 创建插入读者信息存储过程 : (1) create procedure stu_insert (@stu_id varchar(20), @stu_name char(6), @stu_age int , @stu_sex char(2), @stu_dept varchar(20), @stu_major varchar(20), @stu_class varchar(20)) as insert into student_infor values( @stu_id,@stu_name,@stu_age,@stu_sex,@stu_dept,@stu_major,@stu_class) exec stu_insert 39。11218101439。,39。jiangfan39。,39。2039。,39。male39。,39。software39。,39。softwareproject39。,39。112181039。 exec stu_insert 39。11218102739。,39。陶德发39。,39。2039。,39。male39。,39。software39。,39。softwareproject39。,39。112181039。 select * from student_infor select * from book_infor select * from student_borrow_infor 结果显示为： (2) create procedure tea_insert (@tea_id varchar(20), @tea_name char(6), @tea_sex char(2), @tea_age int , @tea_dept varchar(20), @tea_major varchar(20) ) as insert into teacher_infor values( @tea_id,@tea_name,@tea_sex,@tea_age,@tea_dept,@tea_major) drop procedure tea_insert exec tea_insert 39。11039。,39。吴晋哲 39。,39。男 39。,30,39。软件学院 39。,39。软件工程 39。 select * from teacher_infor 创建删除读者信息存储过程： (1) create procedure stu_delete (@stu_id char(20)) as delete from student_infor where stu_id=@stu_id (2) create procedure tea_delete (@tea_id char(20)) as delete from teacher_infor where tea_id=@tea_id 创建查找读者信息存储过程 (1) create procedure stu_select (@stu_id varchar(20)) as select * from student_infor where stu_id=@stu_id exec stu_select 39。11218101439。