數(shù)據(jù)庫(kù)原理及應(yīng)用-崔巍書(shū)后上機(jī)實(shí)驗(yàn)

數(shù) 據(jù) 庫(kù) 上 機(jī) 實(shí) 驗(yàn) 報(bào) 告 1一、實(shí)驗(yàn)?zāi)康模豪斫釹QLServer數(shù)據(jù)庫(kù)的存儲(chǔ)結(jié)構(gòu)，掌握SQLServer數(shù)據(jù)庫(kù)的建立方法和維護(hù)方法。二、實(shí)驗(yàn)內(nèi)容：在 SQLServer環(huán)境下建立數(shù)據(jù)庫(kù)和維護(hù)數(shù)據(jù)庫(kù)。三、程序源代碼：--1CREATEDATABASEtest1ON(NAME=test1_dat,FILENAME='f:\DB\data\test1dat.mdf',SIZE=10,MAXSIZE=50,FILEGROWTH=5)LOGON(NAME=order_log,FILENAME='f:\DB\data\test1log.ldf',SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=5MB)--2createdatabasetest2onprimary(name=test2_dat1,filename='f:\DB\data\test2dat1.mdf'),(name=test2_dat2,filename='f:\DB\data\test2dat2.ndf'),(name=test2_dat3,filename='f:\DB\data\test2dat3.ndf')logon(name=test2_log1,filename='f:\DB\data\test2log1.ldf'),(name=test2_log2,filename='f:\DB\data\test2log2.ldf')--3createdatabasetest3onprimary(name=test3_dat1,filename='f:\DB\data\test3dat1.mdf'),(name=test3_dat2,filename='f:\DB\data\test3dat2.mdf'),filegroupg2(name=test3_dat3,filename='d:\DB\data\test3dat3.ndf'),(name=test3_dat4,filename='d:\DB\data\test3dat4.ndf'),filegroupg3(name=test3_dat5,filename='e:\DB\data\test3dat5.ndf'),(name=test3_dat6,filename='e:\DB\data\test3dat6.ndf')logon(name=test3_log,filename='f:\DB\data\test3log.ldf')--4alterdatabasetest1addfile(name=test1new_dat,filename='f:\DB\data\test1newdat.ndf',size=5MB)--5alterdatabasetest1modifyfile(name=test1_dat,size=15MB)--6dropdatabasetest3四、實(shí)驗(yàn)數(shù)據(jù)、結(jié)果分析：若沒(méi)有指定size，則默認(rèn)為1MB，沒(méi)有指定Maxsize，文件可以增長(zhǎng)到磁盤(pán)滿(mǎn)為止，沒(méi)有指定Filegrowth，則默認(rèn)為10%。五、總結(jié)：CREATEDATABASEdataname創(chuàng)建數(shù)據(jù)庫(kù)ALTERDATABASEdatabase修改數(shù)據(jù)庫(kù)DROPDATABASEdataname刪除數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù)系統(tǒng)及應(yīng)用 上機(jī)實(shí)驗(yàn)報(bào)告2一、實(shí)驗(yàn)?zāi)康模赫莆毡砗退饕慕⒎椒氨斫Y(jié)構(gòu)的修改方法，并實(shí)踐DBMS提供的數(shù)據(jù)完整性功能，加深對(duì)數(shù)據(jù)完整性的理解。二、實(shí)驗(yàn)內(nèi)容：建立表，修改表結(jié)構(gòu)，建立索引，數(shù)據(jù)完整性描述。三.、程序源代碼：createdatabase 訂單管理createtable

客戶(hù)(客戶(hù)號(hào)char(8)primarykeycheck(客戶(hù)名稱(chēng)varchar(40)notnull,聯(lián)系人char(8),地址varchar(40),郵政編碼char(6)check( 郵政編碼

客戶(hù)號(hào)like'[A-z]%'),like'[0-9][0-9][0-9][0-9][0-9][0-9]'),電話(huà)char(12)check(電話(huà)like'1[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))createtable產(chǎn)品(產(chǎn)品號(hào)char(8)primarykeycheck(產(chǎn)品號(hào)like'[A-Z][A-Z]%'),產(chǎn)品名稱(chēng)varchar(40),規(guī)格說(shuō)明char(40)constraintuniunique,單價(jià)smallmoneyconstraintdjcheck(單價(jià)>0))createtable訂購(gòu)單(客戶(hù)號(hào)char(8)notnullforeignkeyreferences客戶(hù),訂單號(hào)char(8)primarykey,訂購(gòu)日期datetimedefaultgetdate())createtable訂單名細(xì)(訂單號(hào)char(8)foreignkeyreferences訂購(gòu)單,序號(hào)tinyint,產(chǎn)品號(hào)char(8)notnullforeignkeyreferences產(chǎn)品,數(shù)量smallintconstraintslcheck(數(shù)量>0),primarykey(訂單號(hào),序號(hào)))修改產(chǎn)品表--1altertable

產(chǎn)品

dropconstraintunialtertablealtertable

產(chǎn)品產(chǎn)品

altercolumn 規(guī)格說(shuō)明varchar(40)addconstraintuniunique( 規(guī)格說(shuō)明)--2altertable

訂購(gòu)單

add

完成日期

datetimedefaultnull--3altertable

訂單名細(xì)

dropconstraintslaltertable

訂單名細(xì)

addconstraintslcheck(

數(shù)量>0and

數(shù)量<1000)建立索引--1createindexcus_idxon 客戶(hù)(客戶(hù)名稱(chēng))--2createuniqueindexgdn_nuion 產(chǎn)品(產(chǎn)品名稱(chēng))--3創(chuàng)建表時(shí)，primarykey 隱式地創(chuàng)建了聚集索引，而一個(gè)表中只能有一個(gè)聚集索引。createclusteredindexoid_cluson 訂購(gòu)單(訂單號(hào))-- 失敗--4createindexitem_idxon 訂單名細(xì)(訂單號(hào),序號(hào),數(shù)量desc)數(shù)據(jù)庫(kù)系統(tǒng)及應(yīng)用 上機(jī)實(shí)驗(yàn)報(bào)告3一、實(shí)驗(yàn)?zāi)康模簽閷?shí)驗(yàn)2建立的表設(shè)計(jì)一組數(shù)據(jù)進(jìn)行插入、刪除、修改等操作，并體會(huì)數(shù)據(jù)完整性約束的作用，加深對(duì)數(shù)據(jù)完整性及其約束的理解。二、實(shí)驗(yàn)內(nèi)容：數(shù)據(jù)的插入、更新和刪除。.、程序源代碼：--1、部分記錄insertinto 客戶(hù)(客戶(hù)號(hào),客戶(hù)名稱(chēng))values('C009','Jinsertinto 產(chǎn)品values('GD001','iphone','4s','5000')insertinto 產(chǎn)品values('GD002','ipad','2g','5500')

商場(chǎng)')insertinto產(chǎn)品(產(chǎn)品號(hào),產(chǎn)品名稱(chēng))values('GD010','MacPro')insertinto訂購(gòu)單(客戶(hù)號(hào),訂單號(hào))values('C001','or01')insertinto訂購(gòu)單(客戶(hù)號(hào),訂單號(hào))values('C001','or02')insertinto訂購(gòu)單(訂單號(hào))values('or19')insertinto訂單名細(xì)values('or01','1','GD001','4')insertinto訂單名細(xì)values('or01','2','GD001','3')insertinto訂單名細(xì)values('or01','3','GD002','6')insertinto訂單名細(xì)(訂單號(hào),序號(hào),產(chǎn)品號(hào))values('or14','28','GD006')--2deletefrom客戶(hù)where客戶(hù)號(hào)='C001'--受參照完整性約束deletefrom訂購(gòu)單where訂單號(hào)='or01'--受參照完整性約束--3update訂購(gòu)單set訂單號(hào)=nullwhere客戶(hù)號(hào)='C001'-- 受實(shí)體完整性約束update訂購(gòu)單set客戶(hù)號(hào)='C011'where訂單號(hào)='or04'-- 受參照完整性約束update訂購(gòu)單set客戶(hù)號(hào)='C009'where訂單號(hào)='or07'-- 更新成功update訂單名細(xì)set數(shù)量=0where序號(hào)='9'-- 受用戶(hù)定義完整性約束--4update訂單名細(xì)set數(shù)量=數(shù)量+10from訂購(gòu)單where訂購(gòu)單.訂單號(hào)=訂單名細(xì).訂單號(hào)and客戶(hù)號(hào)='C002'--5deletefrom 訂單名細(xì)from訂購(gòu)單where訂購(gòu)單.訂單號(hào)=訂單名細(xì).訂單號(hào)and客戶(hù)號(hào)='C002'四、實(shí)驗(yàn)數(shù)據(jù)、結(jié)果分析：--2在刪除客戶(hù)號(hào)為“C002”的記錄時(shí)無(wú)法刪除，因?yàn)榭蛻?hù)表被訂購(gòu)單表參照。刪除訂購(gòu)單號(hào)為“or01”的記錄時(shí)無(wú)法刪除，因?yàn)橛唵蚊?xì)表參照訂購(gòu)單表。--3第一個(gè)更新根據(jù)實(shí)體完整性約束，訂單號(hào)是主關(guān)鍵字，不能為空值。第二個(gè)更新根據(jù)參照完整性約束，訂購(gòu)單表參照客戶(hù)表，而客戶(hù)表中沒(méi)有客戶(hù)號(hào)為“C011”的客戶(hù)。第四個(gè)更新根據(jù)用戶(hù)定義完整性約束，數(shù)量必須為正整數(shù)。--4使客戶(hù)號(hào)為C002的訂購(gòu)單的訂購(gòu)數(shù)量增加 10.--5刪掉客戶(hù)號(hào)為C002的訂單名細(xì)記錄。五、總結(jié)：插入:INSERTINTO<表名>[(<列名>[,<列名>?])]values(<表達(dá)式>[,<表達(dá)式>?])更新：update<表名>set<列名>=<表達(dá)式>[,<列名>=<表達(dá)式>?][[from<表名>]where<邏輯表達(dá)式>]刪除：DELETEFROM表<名>[[FROM<表名>]WHERE邏<輯表達(dá)式>]完整性約束影響插入、更新和刪除等操作數(shù)據(jù)庫(kù)系統(tǒng)及應(yīng)用 上機(jī)實(shí)驗(yàn)報(bào)告4一、實(shí)驗(yàn)?zāi)康模菏炀氄莆?SQLSELECT語(yǔ)句，能夠運(yùn)用該語(yǔ)句完成各種查詢(xún)。二、實(shí)驗(yàn)內(nèi)容：用 SQLSELECT語(yǔ)句完成各種數(shù)據(jù)查詢(xún)。三.、程序源代碼：--1select*from客戶(hù)--2select客戶(hù)號(hào)from訂購(gòu)單--3select*from產(chǎn)品where單價(jià)>=5000--4select*from產(chǎn)品where單價(jià)>5000and產(chǎn)品名稱(chēng)='Macbook'--5select*from產(chǎn)品where單價(jià)>6000and產(chǎn)品名稱(chēng)in('Macbook','ipad')--6selectc.客戶(hù)名稱(chēng),c.聯(lián)系人,c.電話(huà),o.訂單號(hào)from客戶(hù)c,訂購(gòu)單owhereo.訂購(gòu)日期between'2011-10-30'and'2011-12-1'andc.客戶(hù)號(hào)=o.客戶(hù)號(hào)--7selectdistinct客戶(hù)名稱(chēng),聯(lián)系人,電話(huà)from客戶(hù)c,產(chǎn)品g,訂購(gòu)單o,訂單名細(xì)dwhere產(chǎn)品名稱(chēng)='iphone'andg.產(chǎn)品號(hào)=d.產(chǎn)品號(hào)andd.訂單號(hào)=o.訂單號(hào)ando.客戶(hù)號(hào)=c.客戶(hù)號(hào)--8select*from訂單名細(xì)where產(chǎn)品號(hào)in(select產(chǎn)品號(hào)from產(chǎn)品where產(chǎn)品名稱(chēng)='Macbook')--9select*from訂購(gòu)單where訂單號(hào)in(select訂單號(hào)from訂單名細(xì)where數(shù)量>10)--10select*from產(chǎn)品where單價(jià)=(select單價(jià)from產(chǎn)品where規(guī)格說(shuō)明='4s')--11select*from產(chǎn)品where單價(jià)between1000and5000--12select*from客戶(hù)where客戶(hù)名稱(chēng)like'%集團(tuán)'--13select*from 客戶(hù)where 客戶(hù)名稱(chēng)--14select*from 產(chǎn)品orderby 單價(jià)--15select*from產(chǎn)品

notlike'%

商場(chǎng)'orderby 產(chǎn)品名稱(chēng),單價(jià)--16selectCOUNT(產(chǎn)品號(hào))from產(chǎn)品--17selectSUM(數(shù)量)from訂單名細(xì)where產(chǎn)品號(hào)=(select 產(chǎn)品號(hào)from產(chǎn)品where產(chǎn)品名稱(chēng)

='ipad')--18selectSUM(數(shù)量*單價(jià))總金額from產(chǎn)品,訂單名細(xì)where產(chǎn)品.產(chǎn)品號(hào)=訂單名細(xì).產(chǎn)品號(hào)and產(chǎn)品名稱(chēng)='ipodnano'--19selectCOUNT(distinct訂單號(hào))as訂購(gòu)單個(gè)數(shù),AVG(數(shù)量*單價(jià))as平均金額from產(chǎn)品,訂單名細(xì)where產(chǎn)品.產(chǎn)品號(hào)=訂單名細(xì).產(chǎn)品號(hào)--20select訂單號(hào),COUNT(訂單號(hào))項(xiàng)目數(shù),SUM(g.單價(jià)*o.數(shù)量)總金額from產(chǎn)品g,訂單名細(xì)owhereg.產(chǎn)品號(hào)=o.產(chǎn)品號(hào)groupby 訂單號(hào)--21selecti. 訂單號(hào),MAX(數(shù)量*單價(jià))最高金額,MIN(數(shù)量*單價(jià)) 最低金額from訂購(gòu)單o,產(chǎn)品g,訂單名細(xì)iwhereo.訂單號(hào)=i.訂單號(hào)andg.產(chǎn)品號(hào)=i.產(chǎn)品號(hào)and產(chǎn)品名稱(chēng)='iphone'groupbyi. 訂單號(hào)--22select 訂單號(hào),COUNT(*)項(xiàng)目數(shù),AVG(數(shù)量*單價(jià))平均金額from產(chǎn)品g,訂單名細(xì)iwhereg.產(chǎn)品號(hào)=i.產(chǎn)品號(hào)groupby 訂單號(hào)havingCOUNT(*)>=2--23select 客戶(hù)名稱(chēng),聯(lián)系人,電話(huà),訂單號(hào)from客戶(hù)c,訂購(gòu)單owherec.客戶(hù)號(hào)=o.客戶(hù)號(hào)and訂購(gòu)日期isnull--24select客戶(hù)名稱(chēng),聯(lián)系人,電話(huà),訂單號(hào),訂購(gòu)日期from客戶(hù),訂購(gòu)單where客戶(hù).客戶(hù)號(hào)=訂購(gòu)單.客戶(hù)號(hào)and訂購(gòu)日期>'2011-10-10'--25select*from產(chǎn)品outawhere單價(jià)=(selectMAX(單價(jià))from產(chǎn)品innerawhereouta.產(chǎn)品名稱(chēng)=innera.產(chǎn)品名稱(chēng))--26select客戶(hù)號(hào)from客戶(hù)wherenotexists(select*from訂購(gòu)單where客戶(hù).客戶(hù)號(hào)=訂購(gòu)單.客戶(hù)號(hào))--27select*from客戶(hù)whereexists(select*from訂購(gòu)單where客戶(hù).客戶(hù)號(hào)=訂購(gòu)單.客戶(hù)號(hào))--28select產(chǎn)品名稱(chēng)from產(chǎn)品where單價(jià)=any(select單價(jià)/2from產(chǎn)品)--29select產(chǎn)品名稱(chēng)from產(chǎn)品where單價(jià)>all(selectmax(單價(jià))from產(chǎn)品)--30--crossselect*from客戶(hù)crossjoin訂購(gòu)單where客戶(hù).客戶(hù)號(hào)=訂購(gòu)單.客戶(hù)號(hào)--innerselect*from客戶(hù)innerjoin訂購(gòu)單on客戶(hù).客戶(hù)號(hào)=訂購(gòu)單.客戶(hù)號(hào)--leftselect客戶(hù).*,訂單號(hào),訂購(gòu)日期from客戶(hù)leftjoin訂購(gòu)單on客戶(hù).客戶(hù)號(hào)=訂購(gòu)單.客戶(hù)號(hào)--rightselect 客戶(hù).*,訂單號(hào),訂購(gòu)日期from客戶(hù)rightjoin 訂購(gòu)單on客戶(hù).客戶(hù)號(hào)=訂購(gòu)單.客戶(hù)號(hào)--fullselect 客戶(hù).*,訂單號(hào),訂購(gòu)日期from客戶(hù)fulljoin 訂購(gòu)單on客戶(hù).客戶(hù)號(hào)=訂購(gòu)單.客戶(hù)號(hào)數(shù)據(jù)庫(kù)系統(tǒng)及應(yīng)用 上機(jī)實(shí)驗(yàn)報(bào)告5一、實(shí)驗(yàn)?zāi)康模豪斫庖晥D的概念，掌握視圖的使用方法。二、實(shí)驗(yàn)內(nèi)容：定義視圖，并在視圖上完成查詢(xún)、插入、更新和刪除操作。.、程序源代碼：--1--1)、基于單個(gè)表按投影操作定義視圖createviewv_cusasselect客戶(hù)號(hào),客戶(hù)名稱(chēng)from客戶(hù)--使用select*fromv_cus--2)、基于單個(gè)表按選擇操作定義視圖createviewv_orderasselect*from訂購(gòu)單where客戶(hù)號(hào)='C001'使用select*fromv_order--3)、基于單個(gè)表按選擇和投影操作定義視圖createviewv_cussasselect 客戶(hù)名稱(chēng),聯(lián)系人,電話(huà)from客戶(hù)where客戶(hù)號(hào)='C003'使用select*fromv_cuss--4)、基于多個(gè)表根據(jù)連接操作定義視圖createviewv_joinasselect 客戶(hù).*,訂單號(hào),訂購(gòu)日期from客戶(hù)join 訂購(gòu)單on客戶(hù).客戶(hù)號(hào)=訂購(gòu)單.客戶(hù)號(hào)使用select*fromv_join--5)、基于多個(gè)表根據(jù)嵌套查詢(xún)定義視圖createviewv_itemasselect*from 訂單名細(xì)where產(chǎn)品號(hào)in(select 產(chǎn)品號(hào)from產(chǎn)品where產(chǎn)品名稱(chēng)='Macbook')--使用select*fromv_item--6)、查定義含有虛字段的視圖createviewv_items( 訂單號(hào),序號(hào),產(chǎn)品號(hào),數(shù)量,總金額)asselecti.*,i. 數(shù)量*g.單價(jià)from訂單名細(xì)i, 產(chǎn)品gwherei. 產(chǎn)品號(hào)=g.產(chǎn)品號(hào)--使用select*fromv_items--2--在視圖上查詢(xún)select*fromv_items--在視圖上插入insertintov_cusvalues('C012','Google')--在視圖上更新updatev_orderset 訂購(gòu)日期='2011-11-29'--在視圖上刪除deletefromv_cusw