《数据库实验报告(sql语句)》由会员分享，可在线阅读，更多相关《数据库实验报告(sql语句)（12页珍藏版）》请在金锄头文库上搜索。
1、数据库实验姓名： 学号：班级：老师： 1.实验第一部分（1） 创建数据库并建立 6 张表Create Table book_category(category_id varchar(5) Primary key,category varchar(10);Create Table member_level(level varchar(6) Primary key,days smallint,numbers smallint,fee smallint);Create Table loss_reporting(reader_id varchar(5) Primary key,loss_date da
2、tetime);Create Table borrow(reader_id varchar(5),book_id varchar(5),date_borrow datetime,date_return datetime,loss char(2),Primary key(reader_id,book_id);Create Table books(book_id varchar(5) Primary key,book_name varchar(5),author varchar(20),publishing varchar(20),category_id varchar(5),price mone
3、y,date_in datetime,quantity_in int,quantity_out int,quantity_loss smallint,Foreign key (category_id) References book_category(category_id)Create Table reader(reader_id varchar(5) Primary key,reader_name varchar(20),sex char(2),birthday datetime,phone int,Mobile varchar(11),card_name varchar(8),card_
4、id varchar(18),level varchar(6),day datetime,Foreign key (level) References member_level(level)（2） 向这 6 张表输入数据Reader 表（外键先留空）：INSERT INTO reader(reader_id,reader_name,sex,birthday,phone,Mobile,card_name,card_id,day)VALUES(r001,李铭,男 ,19880307,62127790,13671100110,身份证,230106198803070178,20100801);INSE
5、RT INTO reader(reader_id,reader_name,sex,birthday,phone,Mobile,card_name,card_id,day)VALUES(r002,刘晓鸣, 男, 19900809,84778123,13671007896,身份证, 210103199008094326,20100801);INSERT INTO reader(reader_id,reader_name,sex,birthday,phone,Mobile,card_name,card_id,day)VALUES(r003,李明,男 ,20010221,84900581,139010
6、20111,身份证, 230106200102216634,20100801);INSERT INTO reader(reader_id,reader_name,sex,birthday,phone,Mobile,card_name,card_id,day)VALUES(r004,张鹰,女 ,19701112,51681212,13812669002,身份证, 230106197011120145,20100620);INSERT INTO reader(reader_id,reader_name,sex,birthday,phone,Mobile,card_name,card_id,day)
7、VALUES(r005,刘竟静,女 ,19991007,51681213,13756705671,身份证,230106199910070766,20090405);INSERT INTO reader(reader_id,reader_name,sex,birthday,phone,Mobile,card_name,card_id,day)VALUES(r006,刘成刚,男 ,19900518,82161100,13683304305,身份证, 230106199005180842,20100801);INSERT INTO reader(reader_id,reader_name,sex,b
8、irthday,phone,Mobile,card_name,card_id,day)VALUES(r007,王铭,男 ,20010924,82190703,13901229706,身份证,230106200109247092,20100515);INSERT INTO reader(reader_id,reader_name,sex,birthday,phone,Mobile,card_name,card_id,day)VALUES(r008,宣明尼,女 ,19980825,62220506,15851327667,身份证,230106199808258261,20081220);INSER
9、T INTO reader(reader_id,reader_name,sex,birthday,phone,Mobile,card_name,card_id,day)VALUES(r009,柳红利,女 ,19970709,62220712,15810034321,身份证,230106199707095578,20100801);Books 表（外键先留空）：INSERT INTO books(book_id,book_name,author,publishing,price,date_in,quantity_in,quantity_out,quantity_loss)VALUES (b001
10、,图像处理 ,王一, 北京大学出版社,21,20100307,10,3,0); INSERT INTO books(book_id,book_name,author,publishing,price,date_in,quantity_in,quantity_out,quantity_loss)VALUES (b002,苏州园林艺术 ,李白, 清华大学出版社,40,20100517,8,2,0); INSERT INTO books(book_id,book_name,author,publishing,price,date_in,quantity_in,quantity_out,quantit
11、y_loss)VALUES (b003,神奇的宇宙 ,刘力, 清华大学出版社,18,20091209,5,0,0); INSERT INTO books(book_id,book_name,author,publishing,price,date_in,quantity_in,quantity_out,quantity_loss)VALUES (b004,通讯原理 ,张扬, 邮电出版社,38,20100223,10,1,0); INSERT INTO books(book_id,book_name,author,publishing,price,date_in,quantity_in,quan
12、tity_out,quantity_loss)VALUES (b005,肿瘤防治 ,李小明, 人民卫生出版社,16,20090405,5,0,0); INSERT INTO books(book_id,book_name,author,publishing,price,date_in,quantity_in,quantity_out,quantity_loss)VALUES (b006,海参养殖技术 ,王平, 中国农业出版社,11,20100801,2,2,0);INSERT INTO books(book_id,book_name,author,publishing,price,date_i
13、n,quantity_in,quantity_out,quantity_loss)VALUES (b007,操作系统 ,陈东, 武汉大学出版社,32,20100613,8,0,0); Borrow 表（外键先留空）：INSERT INTO borrowVALUES (r001,b001,20100802,20100902,否 ); INSERT INTO borrowVALUES (r001,b002,20100802,20100902,否 ); INSERT INTO borrowVALUES (r002,b006,20100709,20100809,否 );INSERT INTO borr
14、owVALUES (r004,b001,20100802,20101102,否 );INSERT INTO borrowVALUES (r004,b006,20100810,20100910,否 ); INSERT INTO borrowVALUES (r004,b002,20100810,20100910,否 ); INSERT INTO borrowVALUES (r006,b001,20100810,20100910,否 );INSERT INTO borrowVALUES (r006,b004,20100624,20100824,否 );Book_category 表（将 Books
15、表的外键补全）：INSERT INTO book_categoryVALUES(ca01,计算机);INSERT INTO book_categoryVALUES(ca02,农林);INSERT INTO book_categoryVALUES(ca03,医学);INSERT INTO book_categoryVALUES(ca04,科普);INSERT INTO book_categoryVALUES(ca05,通信);INSERT INTO book_categoryVALUES(ca06,建筑);UPDATE booksSET category_id=(select category_id from book_category where category_id=ca01)WHERE book_id=b001
