本文格式為Word版，下載可任意編輯——SQL試驗(yàn)指導(dǎo)書（含部分答案）'1985-12-31'Select*fromaa--查詢刪除后的結(jié)果2)刪除aa表中的全部記錄。

UsemydatabaseGoDeleteaa

4．刪除表aa

usemydatabasego

droptableaa

2

SQLServer2023試驗(yàn)指導(dǎo)書

三、試驗(yàn)內(nèi)容

1．按操作例如練習(xí)數(shù)據(jù)的編輯修改。2．用select命令建立一個(gè)與kcxxb表一致結(jié)構(gòu)的表，然后把記錄(’123456’,’英語’,180，10)添加到表中。

Select*intofromkcxxbwhere1=2

Insertintovalues(’123456’,’英語’,180，10)

3．把記錄數(shù)據(jù)(04651003,'李從良','男','1982-10-1','20-102',5555666)添加到表xsxxb中。

Insertintoxsxxbvalues(04651003,'李從良','男','1982-10-1','20-102',5555666)4．把記錄(04651004,'張中東','25-102',)添加到表xsxxb的xh(學(xué)號(hào))、xm(姓名)和address(地址)中。

Insertintoxsxxb(xh,xm,address)values(04651004,'張中東','25-102')5．把kcxxb表中課時(shí)（ks）大于70的添加到表中。

Insertselect*fromkcxxbwhereks>70

6．把表中所有課時(shí)（ks）等于70的數(shù)據(jù)改為72。

Updatesetks=72whereks=70

7．把xsxxb表中電話(phone)為空（null）的記錄的電話改為‘0000000’。updatexsxxbsetphone=’0000000’wherephoneisnull8．用公式“課時(shí)=學(xué)分*18）計(jì)算表中的課時(shí)。Udatesetks=xuefen*18

8．把表中課程名(kcm)為‘?dāng)?shù)學(xué)’的記錄刪除。deletewherekcm=’數(shù)學(xué)’

9．刪除表中的全部記錄。delete

10．用droptable命令刪除表。droptable

四、思考題

1．不指定字段插入記錄數(shù)據(jù)時(shí)，能否顛倒數(shù)據(jù)的順序？會(huì)有什么影響？2．插入數(shù)據(jù)到指定字段時(shí)可以顛倒數(shù)據(jù)的順序嗎？試試看。

3．假使要修改表的結(jié)構(gòu)（如添加/刪除列，修改列的名稱/大小/數(shù)據(jù)類型等）時(shí)，應(yīng)使用什么命令？請(qǐng)參考有關(guān)資料給xscjb表添加一列，修改其大小后再刪除。

3

SQLServer2023試驗(yàn)指導(dǎo)書

4．刪除記錄和刪除表有何區(qū)別？

5．各條命令中各部分內(nèi)容分別表示什么意思？能顛倒它們的順序嗎？自己試試看。

4

SQLServer2023試驗(yàn)指導(dǎo)書

試驗(yàn)四簡(jiǎn)單數(shù)據(jù)查詢

一、試驗(yàn)?zāi)康模?/p>

通過本試驗(yàn)理解查詢的概念和方法，把握SELECT語句在單表查詢中的應(yīng)用。

二、試驗(yàn)例如

1．還原bookstore數(shù)據(jù)庫

本次試驗(yàn)開始，我們要使用bookstore數(shù)據(jù)庫進(jìn)行數(shù)據(jù)查詢，因此按試驗(yàn)二還原數(shù)據(jù)庫的方法還原指定的bookstore數(shù)據(jù)庫。Bookstore數(shù)據(jù)庫是某書店倉庫管理使用的數(shù)據(jù)庫，其簡(jiǎn)單的E-R模型如圖4-1所示。

圖書供應(yīng)商進(jìn)貨員工銷售圖書客戶圖4-1bookstore數(shù)據(jù)庫的E-R模型書店倉庫管理員收集供應(yīng)商的信息進(jìn)行修改,根據(jù)供應(yīng)商提供的圖書信息進(jìn)行訂貨進(jìn)貨,每次進(jìn)貨都要記錄進(jìn)貨信息，同時(shí)修改圖書的庫存信息。

在銷售員銷售圖書時(shí)，減少圖書庫存量并記錄每次的銷售信息。系統(tǒng)管理員負(fù)責(zé)員工等各種信息的更新處理。各表的結(jié)構(gòu)如下所示：

表4-1圖書供應(yīng)商(provider)表結(jié)構(gòu)字段名字段類型長(zhǎng)度允許空描述providerIdint4N供應(yīng)商編號(hào)主鍵,遞增1的標(biāo)識(shí)providerNamechar40N供應(yīng)商名稱Addressvarchar50N地址Phonechar15Y電話Postcodechar6Y郵編Emailvarchar20Y電子郵箱WebaddressVarchar20Y網(wǎng)址

5

SQLServer2023試驗(yàn)指導(dǎo)書

表4-2圖書庫存(book)表結(jié)構(gòu)

字段名bookIdBookNameISBNAuthorAbstractnumberPricePublisherPubdate字段類型intchar(50)Char(20)Char(20)Char(500)intfloatchar(25)smalldatetime

字段名emIdnamesexbirthdayhiredayaddresstelephonefireday表4-3員工表（employeer）結(jié)構(gòu)字段類型長(zhǎng)度允許空描述IntcharCharsmalldatetimesmalldatetimeCharCharsmalldatetime41024450154NNNYNYYY

表4-4進(jìn)貨記錄表（stock）結(jié)構(gòu)字段名字段類型長(zhǎng)度允許空描述stockIDint4N進(jìn)貨號(hào)Bookidint4N書號(hào)provideridint4N供應(yīng)商號(hào)stockeridint4N進(jìn)貨員工號(hào)numberint4N進(jìn)貨冊(cè)數(shù)pricefloat8N進(jìn)價(jià)stockdatedatetime8N進(jìn)貨日期

主鍵,遞增1的標(biāo)識(shí)員工號(hào)員工名性別出生日期雇用日期住址電話號(hào)碼離職日期主鍵,遞增1的標(biāo)識(shí)默認(rèn)為男長(zhǎng)度450202050048254允許空NNNNYNNNN描述圖書編號(hào)書名書刊號(hào)摘要庫存數(shù)量?jī)r(jià)格出版社出版日期主鍵,遞增1的標(biāo)識(shí)6

SQLServer2023試驗(yàn)指導(dǎo)書

表4-5銷售記錄表（sales）結(jié)構(gòu)字段名字段類型長(zhǎng)度允許空描述saleidint4N銷售號(hào)Bookidint4N書號(hào)clientnchar8Y客戶名saleridint4N銷售員工號(hào)numberint4N銷售冊(cè)數(shù)pricefloat8N銷售價(jià)saledatedatetime8N銷售日期

主鍵,遞增1的標(biāo)識(shí)2．簡(jiǎn)單數(shù)據(jù)查詢

1）查詢所有記錄的所有信息

單擊開始->程序->MicrosoftSQLServer->查詢分析器，開啟查詢分析器，查看bookstore數(shù)據(jù)庫book表中所有信息。

usebookstorego

select*frombook

2）查詢所有記錄的部分列信息。查詢book表中所有記錄的書名(bookname)、(author)和出版社(publisher)信息。

usebookstorego

selectbookname,author,publisherfrombook

3)查詢時(shí)改變列標(biāo)題的顯示。查詢檢索book表所有記錄的書名(bookname)，(author)和出版社(publisher)信息并分別加上書名、出版號(hào)、的標(biāo)題信息。

usebookstorego

selectbooknameas'書名',author'',出版社=publisherfrombook

7

SQLServer2023試驗(yàn)指導(dǎo)書

4)使用TOP關(guān)鍵字查詢前面部分記錄。①從book表中檢索出前面20%的記錄。

usebookstorego

selecttop20percent*frombook

②從book表中查詢出前面5條記錄的bookname（書名）、author()、publisher(出版社)和pubdate(出版日期)的信息，并分別使用書名、、出版社和出版日期作為結(jié)果顯示標(biāo)題。

usebookstorego

selecttop5書名=bookname,authoras,publisher出版社,

pubdateas出版日期frombook

5)使用INTO子句創(chuàng)立新表。創(chuàng)立一個(gè)與book表結(jié)構(gòu)完全一致的表newtab.

usebookstorego

select*intonewtabfrombookselect*fromnewtab

6)使用distinct關(guān)鍵字消除重復(fù)的結(jié)果。如查詢book表中的書出自哪些出版社（publisher）：

usebookstorego

selectdistinctpublisherfrombook

三、試驗(yàn)內(nèi)容

1．完成例如中的所有例如操作。

2.分別查詢bookstore數(shù)據(jù)庫中5張表的所有數(shù)據(jù)信息。

8

SQLServer2023試驗(yàn)指導(dǎo)書

3．從圖書供應(yīng)商表（provider）中檢索出所有的供應(yīng)商的名稱（providername）、地址（address）和電話(phone)。

selectprovidername,address,phonefromprovider

4．查詢員工表（employeer表）中所有員工的姓名（name）、雇用日期(hireday)和電話(telephone)，并將標(biāo)題分別顯示為“姓名〞、“雇用日期〞和“電話〞。selectname姓名,hireday雇用日期,telephone電話fromemployeer

5．查詢銷售記錄表（sales表）中的銷售號(hào)（saleid）、銷售冊(cè)數(shù)(number)和銷售價(jià)格(price)，同時(shí)計(jì)算并顯示它的銷售總價(jià)。

selectsaleid,number,price,zj=number*pricefromsales

computesum(number*price)

6．從銷售記錄表（sales表）中檢索出前面20條記錄的所有信息。selecttop20*fromsales

7．查詢銷售記錄表（sales表）中的前面20%的記錄信息。selecttoppercent20fromsales

8．從進(jìn)貨記錄表（stock表）中檢索進(jìn)貨日期（stockdate）信息，要求消除重復(fù)記錄。

selectdistinctstockdatefeomstock

9．從員工表（employeer表）中檢索所有記錄的姓名(name)、性別(sex)、出生日期(birthday)和住址(address)信息，并保存到表embak中。

selectname,sex,birthday,addressintoembakfromemployeer

10．查詢員工表（employeer表）中所有記錄的姓名(name)和出生日期(birthday)，并按出生日期從大到小的順序排列查詢結(jié)果。

selectname,birthdayfromemployeerorderbybirthdaydesc

9

SQLServer2023試驗(yàn)指導(dǎo)書

四、思考題

1．select命令的基本格式是什么？能顛倒命令中的各部分內(nèi)容嗎？可以換行書寫嗎？

2．查詢命令select的使用有什么規(guī)律嗎？請(qǐng)找出來。

3．不指定查詢結(jié)果的標(biāo)題時(shí)，顯示的標(biāo)題是什么？指定查詢結(jié)果的標(biāo)題有幾種方式？

4．select命令可以顯示常量、變量或表達(dá)式的結(jié)果嗎？

5．假使指定查詢結(jié)果的列標(biāo)題后再用它新建一個(gè)表，則新表中的字段名和原來的有何區(qū)別？

6．假使要排序查詢結(jié)果，使用什么關(guān)鍵詞？要從大到小排序和從小到大排序有何區(qū)別？

10

SQLServer2023試驗(yàn)指導(dǎo)書

2）檢索book表中各出版社出版的圖書最早時(shí)間和最晚時(shí)間(最小值和最大值)。

usebookstorego

selectpublisher,最早時(shí)間=min(pubdate),max(pubdate)最晚時(shí)間frombookgroupbypublisher

3）查詢供應(yīng)商（provider表）中各地的供應(yīng)商數(shù)量。

usebookstorego

selectleft(address,2),count(*)fromprovidergroupbyleft(address,2)

4）使用having過濾結(jié)果。查詢供應(yīng)商（provider表）中各地的供應(yīng)商數(shù)量，并排除少于2個(gè)供應(yīng)商的城市。

usebookstorego

selectleft(address,2),count(*)fromprovidergroupbyleft(address,2)havingcount(*)>1

三、試驗(yàn)內(nèi)容

1．完成試驗(yàn)例如的操作練習(xí)。

2．根據(jù)銷售記錄表(sales)的數(shù)據(jù)計(jì)算2023年全年所銷售書的總量。selectsum(number)fromsaleswhereyear(saledate)=2023

3．根據(jù)bookstore數(shù)據(jù)庫中進(jìn)貨記錄表（stock）的數(shù)據(jù)統(tǒng)計(jì)進(jìn)貨的的圖書數(shù)量(即統(tǒng)計(jì)stock表中的進(jìn)貨數(shù)量)。selectsum(number)fromstock

4．統(tǒng)計(jì)圖書庫存表（book表）中的圖書平均價(jià)格、最低價(jià)格和最高價(jià)格。selectavg(price),min(price),max(price)frombook

16

SQLServer2023試驗(yàn)指導(dǎo)書

5．查詢員工表（employeer表）中姓李的人數(shù)（即name中第一個(gè)字是李的數(shù)量）。selectleft(name,1),count(*)fromemployeerwhereleft(name,1)='李'groupbyleft(name,1)

6.查詢圖書庫存（book表）中高等教育出版社出版的價(jià)格在15到20元之間的圖書種類。

selectcount(*)frombookwherepricebetween15and20

andpublisher=’高等教育出版社’

7．查詢圖書庫存（book表）中各出版社的圖書種類、總數(shù)和平均價(jià)格。selectpublisher,count(*),sum(number),avg(price)frombookgroupbypublisher

8．統(tǒng)計(jì)出book表各出版社出現(xiàn)的次數(shù)。排除小于3次的記錄。Selectpublisher,count(*)frombookgroupbypublisherhavingcount(*)>=39．統(tǒng)計(jì)出員工表（employeer表）中各姓的人數(shù)（即根據(jù)name中第一個(gè)字進(jìn)行分組統(tǒng)計(jì)）。

Selectleft(name,1),count(*)fromemployeergroupbyleft(name,1)10．統(tǒng)計(jì)員工表中男性和女性的人數(shù)。selectsex,count(*)fromemployeergroupbysex

四、思考題

1．groupby子句能和where子句一起使用嗎？它們能顛倒順序嗎？

2．函數(shù)sum、avg、min、max和count能做什么？它們可以出現(xiàn)在什么位置？3．在一個(gè)命令中能同時(shí)使用幾個(gè)統(tǒng)計(jì)函數(shù)嗎？4．groupby子句中的having子句和where子句一致嗎？

17

SQLServer2023試驗(yàn)指導(dǎo)書

試驗(yàn)七高級(jí)數(shù)據(jù)查詢

一、試驗(yàn)?zāi)康?/p>

1．學(xué)習(xí)在多個(gè)表之間進(jìn)行數(shù)據(jù)聯(lián)接查詢2．學(xué)習(xí)使用子查詢進(jìn)行數(shù)據(jù)查詢

二、試驗(yàn)例如操作

1．多表查詢

1）從sales、book兩個(gè)表中檢索所有銷售圖書的書名、和銷售價(jià)格。

usebookstorego

selectbook.bookname,book.author,sales.pricefrombook,sales

wherebook.bookid=sales.bookid

2)從stock和book表中檢索所有進(jìn)貨圖書的書名、進(jìn)貨價(jià)格和出版社。

usebookstorego

selectb.bookname書名,b.publisher出版社,s.price進(jìn)貨價(jià)

frombookasbjoinstocksonb.bookid=s.bookid

3)從sales、book和employeer三個(gè)表中檢索所有銷售圖書的員工名、書名、銷售價(jià)格和銷售數(shù)量。

usebookstorego

員工姓名,b.bookname書名,s.price銷售價(jià),s.number銷售量

18

SQLServer2023試驗(yàn)指導(dǎo)書

fromemployeerasejoinsalessone.emid=s.saleridjoinbookasbonb.bookid=s.bookid

2．子查詢

1）使用子查詢進(jìn)行比較測(cè)試：從book表中檢索數(shù)據(jù)，列出高于平均價(jià)格的圖書的書名、序列號(hào)、、價(jià)格及其出版社。

usebookstorego

selectbookname,isbn,author,price,publisherfrombook

whereprice>(selectavg(price)frombook)

2)使用子查詢進(jìn)行集成員測(cè)試：從employeer和sales表中檢索賣出了圖書的員工的信息。

usebookstorego

select*fromemployeerwhereemidin(selectsaleridfromsales)

3)使用子查詢進(jìn)行存在性測(cè)試：從sales表中檢索賣出了圖書的員工，然后在employeer表中查詢的該員工的信息。

usebookstorego

select*fromemployeere

whereexists(select*fromsalesswheres.salerid=e.emid)

三、試驗(yàn)內(nèi)容

1．完成例如操作的練習(xí)

2．查詢員工表（employeer表）年齡最小（birthday最大）的員工姓名(name)、性別（sex）和雇用日期（hireday）。

19

SQLServer2023試驗(yàn)指導(dǎo)書

selectname,sex,hiredayfromemployeerwherbirthday=(selectmax(birthday)fromemployeer)

2．從庫存表(book表)和銷售表(sales表)中查詢各類書籍的書名（bookname）、銷售數(shù)量(number)。

selectbookname,sum(sales.number)frombook,saleswherebook.bookid=sales.bookidgroupbybookname

3．從進(jìn)貨表（stock）、供應(yīng)商provider表和庫存表（book）中查詢所有進(jìn)貨的書名(bookname)、進(jìn)貨數(shù)量（number）和供應(yīng)商名稱(providername)。select

bookname,stock.number,providername

from

stock,provider,book

where

viderid=videridandbook.bookid=stock.bookid

4．從庫存表(book表)和銷售表(sales表)中查詢所有書籍的銷售數(shù)量(number)（顯示全部書名和銷售數(shù)量）。