實(shí)驗(yàn)5sql語句練習(xí)——圖書館數(shù)據(jù)庫答案

1、實(shí)驗(yàn)5sql語句練習(xí)一一圖書館數(shù)據(jù)庫實(shí)驗(yàn)5 sql語句練習(xí)一一圖書館數(shù)據(jù)庫實(shí)驗(yàn)?zāi)康模?）了解SQL Server數(shù)據(jù)庫的邏輯結(jié)構(gòu)和物理結(jié)構(gòu);（2）了解表的結(jié)構(gòu)特點(diǎn)；（3）了解SQL Server的基本數(shù)據(jù)類型；（4 ）了解空值概念；（5）學(xué)會(huì)在企業(yè)管理器中創(chuàng)建數(shù)據(jù)庫和表；（6）學(xué)會(huì)使用T-SQL語句創(chuàng)建數(shù)據(jù)庫和表。（7）學(xué)會(huì)使用T-SQL語句更新數(shù)據(jù)。（7）學(xué)會(huì)使用T-SQL語句創(chuàng)建多種查詢。實(shí)驗(yàn)準(zhǔn)備首先要明確，能夠創(chuàng)建數(shù)據(jù)庫的用戶必須是系統(tǒng)管理員，或是被授權(quán)使用 CREATE DATABASE語句的用戶。其次創(chuàng)建數(shù)據(jù)庫必須要確定數(shù)據(jù)庫名、數(shù)據(jù)庫大?。ㄗ畛醯拇笮?、最大的大小、是否允許增長及增長

2、方式） 和存儲(chǔ)數(shù)據(jù)庫的文件。然后，確定數(shù)據(jù)庫包含哪些表，以及所包含的各表的結(jié)構(gòu)，還要了解SQL Server的常用數(shù)據(jù)類型，以創(chuàng)建數(shù)據(jù)庫的表。此外還要了解兩種常用的創(chuàng)建數(shù)據(jù)庫、表的方法，即在企業(yè)管理器中創(chuàng)建和使用T-SQL的CREATEDATABASE 語句。實(shí)驗(yàn)內(nèi)容bookboolctd借閱養(yǎng)readerbooldfameC-readerIdbooOri£b cvokCdreadtrlTftmebaokFublishcheckOutTime de-rSexbookPriceche cklnl irertadtrOffictTtlISBN假設(shè)有5本書設(shè)有一圖書館數(shù)據(jù)庫，其中包括3個(gè)表

3、，即圖書表、讀者表和借閱表。三個(gè)表的結(jié)構(gòu)如圖:圖書表結(jié)構(gòu)列名說明數(shù)據(jù)類型約束說明書號(hào)圖書唯一的編號(hào)定長字符串，長度為 10主鍵書名圖書的名稱定長字符串，長度為 50空值作者:圖書的編著者名定長字符串，長度為 30空值出版社圖書的出版社定長字符串，長度為 30空值單價(jià)出版社確定的圖書的單價(jià)浮點(diǎn)型，F(xiàn)loat空值讀者表結(jié)構(gòu)假設(shè)有10位讀者列名|說明數(shù)據(jù)類型約束說明、-H口讀者號(hào)讀者唯一的編號(hào)定長字符串，長度為 10主鍵姓名讀者姓名定長字符串，長度為 8非空值性別讀者性別定長字符串，長度為 2非空值辦公電話:讀者辦公電話定長字符串，長度為 8空值部門讀者所在部門定長字符串，長度為 30空值借閱表結(jié)構(gòu)

4、列名說明數(shù)據(jù)類型約束說明、-H口讀者號(hào)讀者的唯一編號(hào)定長字符串，長度為 10外碼，引用讀者表的 主鍵書號(hào)圖書的唯一編號(hào)定長字符串，長度為 20外碼，引用圖書表的 主鍵借出日期借出圖書的日期定長字符串，長度為 8非空值歸還日期歸還圖書的日期定長字符串，長度為 8空值主鍵為：(讀者號(hào)，圖書號(hào))(1) 用Sql語句創(chuàng)建圖書館數(shù)據(jù)庫Create database Lab05(2) 用Sql語句創(chuàng)建上述 3個(gè)表create table book(bookId char(10)primary key, bookName varchar(50), bookWriter varchar(30), bookPu

5、blish varchar(30), bookPrice float)create table reader(readerId char(10) primary key, readerName varchar(8)not null, readerSex char(2)not null, readerOfficeTel char(8), readerDepartment varchar(30)create table 借閱表(readerId char(10), bookId char(10), checkOutTime char(8), checkInTime char(8), primary

6、 key(readerld,bookld), foreign key (readerId) references reader(readerId), foreign key (bookId) references book(bookId),)(3) 基于圖書館數(shù)據(jù)庫的3個(gè)表，用sql語言完成一下操作:1) 為圖書表增加一列“ ISBN ”，數(shù)據(jù)類型為 CHAR(10)alter table book add ISBN char(10)2) 為剛添加的 ISBN 列增加缺省值約束，約束名為 ISBNDEF ，缺省值為 7111085949' ALTER TABLE book ADD CO

7、NSTRAINT ISBNDEF DEFAULT ('7111085949') FOR ISBN3) 刪除圖書表中 ISBN 列增加的缺省值約束 alter table book drop ISBNDEF4) 刪除圖書表中新增的 ISBN 列 ALTER TABLE book DROP COLUMN ISBN5) 查詢?nèi)w圖書的圖書號(hào)、書名、作者、出版社和單價(jià) select bookId,bookName,bookWriter,bookPublish,bookPrice from book6) 查詢?nèi)w圖書的信息，其中單價(jià)打8 折，并設(shè)置該列的別名為打折價(jià)'select

8、 bookId,bookName,bookWriter,bookPublish,(bookPrice*0.8) as 打折價(jià) from book7) 顯示所有借閱者的讀者號(hào)，并去掉重復(fù)行 select distinct readerId from 借閱表8) 查詢所有單價(jià)在 2030 元之間的圖書信息select bookId,bookName,bookWriter,bookPublish,bookPrice,ISBN from bookwhere bookPrice between 20 and 309) 查詢機(jī)械工業(yè)出版社、科學(xué)出版社、人民郵電出版社的圖書信息select bookId,b

9、ookName,bookWriter,bookPublish,bookPrice,ISBN from bookwhere bookPublish in(' 機(jī)械工業(yè)出版社 ' , '科學(xué)出版社 ',' 人民郵電出版社 ')10 )查詢既不是機(jī)械工業(yè)出版社、 人民郵電出版社、 也不是科學(xué)出版社出版的圖書信息select bookId,bookName,bookWriter,bookPublish,bookPrice,ISBN from bookwhere bookPublish not in(' 機(jī)械工業(yè)出版社 ' , '

10、科學(xué)出版社 ','人民郵電出版社 ')11)查詢姓名的第二個(gè)字符是 '建'，并且只有 2 個(gè)字的讀者的讀者號(hào)及姓名select readerId,readerName from reader where readerName like '_ 建 '12 )查詢姓名不是以王' 、張'或李'開頭的所有讀者的讀者號(hào)及姓名【方式一】查詢出來的結(jié)果有問題！ select readerId,readerName from reader where readerName not in (' 王%','張

11、%','李%') 【方式二】 select readerId,readerName from reader where readerName not in (select readerNamefrom readerwhere readerName like'王'or readerName like'張'or readerName like'李%' )13 )查詢無歸還日期的借閱信息select book.bookId,book.bookName,reader.readerId,reader.readerName, 借閱表

12、.checkOutTime as 借書時(shí)間 , 借閱表 .checkInTime as 還書時(shí)間from 借閱表 ,book,readerwhere 借閱表 .bookId = book.bookIdand借閱表 .readerId = reader.readerIdand借閱表 .checkInTime is null14 )查詢機(jī)械工業(yè)出版社圖書的平均價(jià)格、最高價(jià)、最低價(jià)select avg(bookPrice)as 平均價(jià)格 ,max(bookPrice) as 最高價(jià) ,min(bookPrice) as 最低價(jià) from bookwhere bookPublish = ' 機(jī)

13、械工業(yè)出版社圖書 '15 )查詢讀者的基本信息及借閱情況select reader.readerId,reader.readerName , 借閱表 .bookId,book.bookName,book.bookPublish from reader , 借閱表 ,bookwhere reader.readerId = 借閱表 .readerId and book.bookId = 借閱表 .bookId and 借閱表 .readerId ='1000000007'16)查詢至少借閱過 1 本機(jī)械工業(yè)出版社出版的圖書的讀者的讀者號(hào)、 姓名、書名及借閱本數(shù)， 并按借閱本

14、書多少降序排列select r.Rno,Rname,count(borrow.Bno) 借閱冊(cè)數(shù)from borrow,b,rwhere b.bno=borrow.bno and press=' 機(jī)械工業(yè)出版社 ' and borrow.rno=r.rno group by r.rno,Rnameorder by count(borrow.Bno) desc17 )查詢與王小平'的辦公電話相同的讀者的姓名/* 使用“自連接方式”求解 */select b.readerName,b.readerId,a.readerOfficeTelfrom reader a, read

15、er bwhere a.readerName like ' 王小平 'and a.readerOfficeTel = b.readerOfficeTel/*18 )查詢所有單價(jià)小于平均單價(jià)的圖書的書號(hào)、書名及出版社select bookId,bookName,bookPublish,bookPrice from book where bookPrice <(select avg(bookPrice) as averagePricefrom book)19 )查詢科學(xué)出版社'的圖書單價(jià)比機(jī)械工業(yè)出版社'最高單價(jià)還高的圖書書名及單價(jià)select bookId,

16、bookName,bookPublish,bookPrice from bookwhere bookPublish like '科學(xué)出版社 ' and bookPrice >(select max(bookPrice)from bookwhere bookPublish = ' 機(jī)械工業(yè)出版社 ')20)查詢科學(xué)出版社'的圖書中單價(jià)比機(jī)械工業(yè)出版社'最低單價(jià)高的圖書書名及單價(jià)select bookId,bookName,bookPublish,bookPricefrom bookwhere bookPrice <(select max(bookPrice)from bookwhere bookPublish = ' 機(jī)械工業(yè)出版社 ')and bookPublish like ' 科學(xué)出版社 '21 )創(chuàng)建機(jī)械工業(yè)出版社圖書的視圖CREATE VIEW 機(jī)械工業(yè)出版社 ViewASSELECT bookId,bookName,bookPriceFROM bookWHERE bookPublish = ' 機(jī)械工業(yè)出版社 '22 )創(chuàng)建一個(gè)借閱統(tǒng)計(jì)視圖，名為 CountView ，包含讀