数据库实验报告(SQL语句).docx

数据库实验姓名： 学号：班级：老师： 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 datetime);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 money,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_id varchar(18),level varchar(6),day datetime,Foreign key (level) References member_level(level)（2） 向这6张表输入数据Reader表（外键先留空）：INSERTINTO reader(reader_id,reader_name,sex,birthday,phone,Mobile,card_name,card_id,day)VALUES(r001,李铭,男,19880307,62127790身份证20100801);INSERTINTO reader(reader_id,reader_name,sex,birthday,phone,Mobile,card_name,card_id,day)VALUES(r002,刘晓鸣, 男,19900809,84778123身份证,20100801);INSERTINTO reader(reader_id,reader_name,sex,birthday,phone,Mobile,card_name,card_id,day)VALUES(r003,李明,男,20010221,84900581身份证,20100801);INSERTINTO reader(reader_id,reader_name,sex,birthday,phone,Mobile,card_name,card_id,day)VALUES(r004,张鹰,女,19701112,51681212身份证,20100620);INSERTINTO reader(reader_id,reader_name,sex,birthday,phone,Mobile,card_name,card_id,day)VALUES(r005,刘竟静,女,19991007,51681213身份证20090405);INSERTINTO reader(reader_id,reader_name,sex,birthday,phone,Mobile,card_name,card_id,day)VALUES(r006,刘成刚,男,19900518,82161100身份证,20100801);INSERTINTO reader(reader_id,reader_name,sex,birthday,phone,Mobile,card_name,card_id,day)VALUES(r007,王铭,男,20010924,82190703身份证20100515);INSERTINTO reader(reader_id,reader_name,sex,birthday,phone,Mobile,card_name,card_id,day)VALUES(r008,宣明尼,女,19980825,62220506身份证20081220);INSERTINTO reader(reader_id,reader_name,sex,birthday,phone,Mobile,card_name,card_id,day)VALUES(r009,柳红利,女,19970709,62220712身份证20100801);Books表（外键先留空）：INSERT INTO books(book_id,book_name,author,publishing,price,date_in,quantity_in,quantity_out,quantity_loss)VALUES (b001,图像处理,王一,北京大学出版社,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,quantity_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,quantity_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_in,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 borrowVALUES (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表的外键补全）：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;UPDATE booksSET category_id=(select category_id from book_category where category_id=ca06)WHERE book_id=b002;UPDATE booksSET category_id=(select category_id from book_category where category_id=ca04)WHERE book_id=b003;UPDATE booksSET category_id=(select category_id from book_category where category_id=ca05)WHERE book_id=b004;UPDATE booksSET category_id=(select category_id from book_category where category_id=ca03)WHERE book_id=b005;UPDATE booksSET category_id=(select category_id from book_category where category_id=ca02)WHERE book_id=b006;UPDATE booksSET category_id=(select category_id from book_category where category_id=ca01)WHERE book_id=b007;Member_level表（将readers表的外键补全）：Insert member_levelVALUES (普通,30,2,10);Insert member_levelVALUES (银卡,60,3,50);Insert member_levelVALUES (金卡,90,5,100);UPDATE readerSET level=(select level from member_level where level=普通)where reader_id=r001;UPDATE readerSET level=(select level from member_level where level=普通)where reader_id=r002;UPDATE readerSET level=(select level from member_level where level=普通)where reader_id=r003;UPDATE readerSET level=(select level from member_level where level=金卡)where reader_id=r004;UPDATE readerSET level=(select level from member_level where level=普通)where reader_id=r005;UPDATE readerSET level=(select level from member_level where level=银卡)where reader_id=r006;UPDATE readerSET level=(select level from member_level where level=普通)where reader_id=r007;UPDATE readerSET level=(select level from member_level where level=普通)where reader_id=r008;UPDATE readerSET level=(select level from member_level where level=普通)where reader_id=r009;Loss_reporting表Insert loss_reportingVALUES(r006,20100901);2. 实验第二部分1. 查找姓王的普通会员姓名Select reader.reader_nameFrom readerWhere reader.reader_name like 王% andreader.level=普通;2. 显示每个级别会员的会员人数Select level,Count(1)人数From readerGroup by level;3. 查找1980-1990年出生的会员的姓名Select reader.reader_nameFrom readerWhere datediff(year,birthday,1991)0and datediff(year,birthday,1979)2);6. 查找借了编号为“b001” 和“b002”的图书的会员编号Select Distinct(reader_id)From borrowwhere book_id=b001 or book_id=b002;4. 实验第四部分1. 用insert命令向会员级别表中添加记录“钻石卡，180 ，20，200）Insert into member_levelValues(钻石卡,180,20,200);2. 把计算机类图书的书名和入库数量存入另一个已知基本表computer-books(bname,quantity)中Create Table computer_books( bname varchar(50), quantity int);Insert into computer_books(bname,quantity)Select Distinct (books.book_name),books.quantity_inFrom books,book_categorywhere books.category_id=(select category_id from book_category where category=计算机);3. 将普通会员的会费提高10元Update member_levelSet fee=fee+10where level=普通;4. 用delete命令从会员级别表中删除钻石卡记录Delete From member_levelwhere level=钻石卡;5. 建立一个金卡会员的视图，显示会员姓名和年龄Create View 金卡会员视图表(member_name,member_age)AsSelect reader_name,year(getdate()-year(birthday) From readerwhere level=金卡;6. 建立一个分组视图，显示不同类别图书的出借数量Create View 图书出借情况(category,number)Asselect distinct (book_category.category),count(1)数量from book_category,books,borrowwhere books.book_id=borrow.book_id and book_category.category_id=books.category_idgroup by category;5. 实验第五部分1. 使用check子句定义如下约束条件（1）性别只能是“男”或“女”（2）读者编号是以r打头的4位字符,后3位只能是数字Create Table reader(reader_id varchar(5) Primary key,reader_name varchar(20),sex char(2) check (sex in(男,女),birthday datetime,phone int,Mobile var