搜索
实验5sql语句练习——图书馆数据库实验5sql语句练习——图书馆数据库实验目的（1）了解SQLServer数据库的逻辑结构和物理结构；（2）了解表的结构特点；（3）了解SQLServer的基本数据类型；（4）了解空值概念；（5）学会在企业管理器中创建数据库和表；（6）学会使用T-SQL语句创建数据库和表。（7）学会使用T-SQL语句更新数据。（7）学会使用T-SQL语句创建多种查询。实验准备首先要明确，能够创建数据库的用户必须是系统管理员，或是被授权使用CREATEDATABASE语句的用户。其次创建数据库必须要确定数据库名、数据库大小（最初的大小、最大的大小、是否允许增长及增长方式）和存储数据库的文件。然后，确定数据库包含哪些表，以及所包含的各表的结构，还要了解SQLServer的常用数据类型，以创建数据库的表。此外还要了解两种常用的创建数据库、表的方法，即在企业管理器中创建和使用T-SQL的CREATEDATABASE语句。实验内容假设有5本书设有一图书馆数据库，其中包括3个表，即图书表、读者表和借阅表。三个表的结构如图：图书表结构列名说明数据类型约束说明书号图书唯一的编号定长字符串，长度为10主键书名图书的名称定长字符串，长度为50空值作者图书的编著者名定长字符串，长度为30空值出版社图书的出版社定长字符串，长度为30空值单价出版社确定的图书的单价浮点型，Float空值读者表结构假设有10位读者列名说明数据类型约束说明读者号读者唯一的编号定长字符串，长度为10主键姓名读者姓名定长字符串，长度为8非空值性别读者性别定长字符串，长度为2非空值办公电话读者办公电话定长字符串，长度为8空值部门读者所在部门定长字符串，长度为30空值借阅表结构列名说明数据类型约束说明读者号读者的唯一编号定长字符串，长度为10外码，引用读者表的主键书号图书的唯一编号定长字符串，长度为20外码，引用图书表的主键借出日期借出图书的日期定长字符串，长度为8非空值归还日期归还图书的日期定长字符串，长度为8空值主键为：（读者号，图书号）（1）用Sql语句创建图书馆数据库CreatedatabaseLab05（2）用Sql语句创建上述3个表createtablebook(bookIdchar(10)primarykey,bookNamevarchar(50),bookWritervarchar(30),bookPublishvarchar(30),bookPricefloat)createtablereader(readerIdchar(10)primarykey,readerNamevarchar(8)notnull,readerSexchar(2)notnull,readerOfficeTelchar(8),readerDepartmentvarchar(30))createtable借阅表(readerIdchar(10),bookIdchar(10),checkOutTimechar(8),checkInTimechar(8),primarykey(readerId,bookId),foreignkey(readerId)referencesreader(readerId),foreignkey(bookId)referencesbook(bookId),)（3）基于图书馆数据库的3个表，用sql语言完成一下操作：1）为图书表增加一列“ISBN”，数据类型为CHAR(10)altertablebookaddISBNchar(10)2）为刚添加的ISBN列增加缺省值约束，约束名为ISBNDEF，缺省值为‘7111085949’ALTERTABLEbookADDCONSTRAINTISBNDEFDEFAULT('7111085949')FORISBN3）删除图书表中ISBN列增加的缺省值约束altertablebookdropISBNDEF4）删除图书表中新增的ISBN列ALTERTABLEbookDROPCOLUMNISBN5）查询全体图书的图书号、书名、作者、出版社和单价selectbookId,bookName,bookWriter,bookPublish,bookPricefrombook6）查询全体图书的信息，其中单价打8折，并设置该列的别名为‘打折价’selectbookId,bookName,bookWriter,bookPublish,(bookPrice*0.8)as打折价frombook7）显示所有借阅者的读者号，并去掉重复行selectdistinctreaderIdfrom借阅表8）查询所有单价在20—30元之间的图书信息selectbookId,bookName,bookWriter,bookPublish,bookPrice,ISBNfrombookwherebookPricebetween20and309）查询机械工业出版社、科学出版社、人民邮电出版社的图书信息selectbookId,bookName,bookWriter,bookPublish,bookPrice,ISBNfrombookwherebookPublishin('机械工业出版社','科学出版社','人民邮电出版社')10）查询既不是机械工业出版社、人民邮电出版社、也不是科学出版社出版的图书信息selectbookId,bookName,bookWriter,bookPublish,bookPrice,ISBNfrombookwherebookPublishnotin('机械工业出版社','科学出版社','人民邮电出版社')11）查询姓名的第二个字符是’建’，并且只有2个字的读者的读者号及姓名selectreaderId,readerNamefromreaderwherereaderNamelike'_建'12）查询姓名不是以‘王’、‘张’或‘李’开头的所有读者的读者号及姓名【方式一】查询出来的结果有问题！selectreaderId,readerNamefromreaderwherereaderNamenotin('王%','张%','李%')【方式二】selectreaderId,readerNamefromreaderwherereaderNamenotin(selectreaderNamefromreaderwherereaderNamelike'王%'orreaderNamelike'张%'orreaderNamelike'李%')13）查询无归还日期的借阅信息selectbook.bookId,book.bookName,reader.readerId,reader.readerName,借阅表.checkOutTimeas借书时间,借阅表.checkInTimeas还书时间from借阅表,book,readerwhere借阅表.bookId=book.bookIdand借阅表.readerId=reader.readerIdand借阅表.checkInTimeisnull14）查询机械工业出版社图书的平均价格、最高价、最低价selectavg(bookPrice)as平均价格,max(bookPrice)as最高价,min(bookPrice)as最低价frombookwherebookPublish='机械工业出版社图书'15）查询读者的基本信息及借阅情况selectreader.readerId,reader.readerName,借阅表.bookId,book.bookName,book.bookPublishfromreader,借阅表,bookwherereader.readerId=借阅表.readerIdandbook.bookId=借阅表.bookIdand借阅表.readerId='1000000007'16）查询至少借阅过1本机械工业出版社出版的图书的读者的读者号、姓名、书名及借阅本数，并按借阅本书多少降序排列selectr.Rno,Rname,count(borrow.Bno)借阅册数fromborrow,b,rwhereb.bno=borrow.bnoandpress='机械工业出版社'andborrow.rno=r.rnogroupbyr.rno,Rnameorderbycount(borrow.Bno)desc17）查询与‘王小平’的办公电话相同的读者的姓名/*使用“自连接方式”求解*/selectb.readerName,b.readerId,a.readerOfficeTelfromreadera,readerbwherea.readerNamelike'王小平'anda.readerOfficeTel=b.readerOfficeTel/*18）查询所有单价小于平均单价的图书的书号、书名及出版社selectbookId,bookName,bookPublish,bookPricefrombookwherebookPrice(selectavg(bookPrice)asaveragePricefrombook)19）查询‘科学出版社’的图书单价比‘机械工业出版社’最高单价还高的图书书名及单价selectbookId,bookName,bookPublish,bookPricefrombookwherebookPublishlike'科学出版社'andbookPrice(selectmax(bookPrice)frombookwherebookPublish='机械工业出版社')20）查询‘科学出版社’的图书中单价比‘机械工业出版社’最低单价高的图书书名及单价selectbookId,bookName,bookPublish,bookPricefrombookwherebookPrice(selectmax(bookPrice)frombookwherebookPublish='机械工业出版社')andbookPublishlike'科学出版社'21）创建机械工业出版社图书的视图CREATEVIEW机械工业出版社ViewASSELECTbookId,bookName,bookPriceFROMbookWHEREbookPublish='机械工业出版社'22）创建一个借阅统计视图，名为CountView，包含读者的读者号和总借阅本数CREATEVIEWCountViewASSELECTreaderIdas读者号,count(*)as总借阅本数FROM借阅表GROUPBYreaderId23）创建一个借阅统计视图，名为CountView10,包含借阅总本数打于2的读者号和总借阅本数CREATEVIEW借阅统计视图ViewASSELECTreaderIdas读者号,count(*)as总借阅本数FROM借阅表GROUPBYreaderIdHAVINGCOUNT(*)2