數(shù)據(jù)庫(kù)試驗(yàn)一到試驗(yàn)七（邯院專版）

本文格式為Word版，下載可任意編輯——數(shù)據(jù)庫(kù)試驗(yàn)一到試驗(yàn)七（邯院專版）

2）分別統(tǒng)計(jì)雇員表Empl中各部門員工工資的平均數(shù)，并且列名分別用“部門編號(hào)〞及“各部門工資平均數(shù)〞表示。

useCOMPANY

selectavg(SALARY)as各部門工資平均數(shù),DNOas部門編號(hào)fromEMPLgroupbyDNO

10.使用分組子句GROUPBY與聚集函數(shù)COUNT進(jìn)行查詢

1）統(tǒng)計(jì)員工人數(shù)多于4人的部門信息，并且列名分別用“部門編號(hào)〞及“員工人數(shù)〞表示。

useCOMPANY

selectcount(ENAME)as員工人數(shù),DNOas部門編號(hào)fromEMPL

groupbyDNO

havingcount(ENAME)>4

2）分別統(tǒng)計(jì)工作表Job中各項(xiàng)目的參與開發(fā)的員工人數(shù)，并且列名分別用“項(xiàng)目編號(hào)〞及“員工人數(shù)〞表示。

useCOMPANY

selectPNOas項(xiàng)目編號(hào),count(PNO)as員工人數(shù)fromJOB

groupbyPNO

HavingPNOisnotnull

試驗(yàn)3數(shù)據(jù)庫(kù)的多表連接和子查詢

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

本試驗(yàn)的目的是使學(xué)生加深對(duì)SQL和SQL語(yǔ)言的查詢語(yǔ)句的理解。熟練把握數(shù)據(jù)庫(kù)的多表連接查詢和子查詢。二、試驗(yàn)時(shí)數(shù)2學(xué)時(shí)三、試驗(yàn)內(nèi)容㈠多表連接查詢：

1.對(duì)兩張數(shù)據(jù)表使用內(nèi)連接進(jìn)行查詢

1）查詢姓名為“王一偉〞的員工所在部門的名稱，并且列名用漢字表示。

usecompany

selectDEPA.DNAMEas部門名稱,EMPL.ENAMEas姓名fromEMPLinnerjoinDEPAonDEPA.DNO=EMPL.DNOwhereEMPL.ENAME='王一偉'

2）查詢編號(hào)為“1002〞的員工姓名及該員工所在部門的名稱，并且列名用漢字表示。use

company

selectEMPL.ENOas部門編號(hào),DEPA.DNAMEas部門名稱,EMPL.ENAMEas姓名fromEMPLinnerjoinDEPAonDEPA.DNO=EMPL.DNOwhereEMPL.ENo='1202'

2.對(duì)多張數(shù)據(jù)表使用內(nèi)連接進(jìn)行查詢

1）查詢姓名為“陳晨〞的員工參與過的所有項(xiàng)目的名稱，并且列名用漢字表示。

usecompany

selectEMPL.ENAMEas姓名,EMPL.ENOas員工編號(hào),JOB.PNOas項(xiàng)目名稱fromEMPLinnerjoinJOBonEMPL.ENO=JOB.ENOwhereEMPL.ENAME='李晨'

2）查詢編號(hào)為“1202〞的員工姓名及該員工參與過的所有項(xiàng)目的名稱，并且列名用漢字表示。

usecompany

selectEMPL.ENAMEas姓名,EMPL.ENOas員工編號(hào),JOB.PNOas項(xiàng)目名稱fromEMPLinnerjoinJOBonEMPL.ENO=JOB.ENO

whereEMPL.ENO='1202'

3.對(duì)兩張數(shù)據(jù)表使用左連接進(jìn)行查詢

1）使用左連接查詢所有員工的基本信息及參與項(xiàng)目開發(fā)狀況，假使某員工參與過項(xiàng)目開發(fā)，則列出該員工所參與的所有項(xiàng)目的編號(hào)，否則該項(xiàng)以空值表示。

usecompany

selectEMPL.ENOas員工編號(hào),EMPL.ENAMEas姓名,EMPL.BIRTHDATEas出生日期,EMPL.SALARYas工資,Empl.DNOas部門編號(hào),JOB.PNOas項(xiàng)目編號(hào)fromEMPLleftouterjoinJOBonEMPL.ENO=JOB.ENO

2）使用左連接查詢所有員工的基本信息及所在部門名稱，假使某員工尚未分派到任何部門，則該員工的部門名稱項(xiàng)以空值表示。

usecompany

selectEMPL.ENOas員工編號(hào),EMPL.ENAMEas姓名,EMPL.BIRTHDATEas出生日期,EMPL.SALARYas工資,Empl.DNOas部門編號(hào),DEPA.DNAMEas部門名稱fromEMPLleftouterjoinDEPAonEMPL.DNO=DEPA.DNO

4.對(duì)兩張數(shù)據(jù)表使用右連接進(jìn)行查詢

1）使用右連接查詢所有員工的基本信息及參與項(xiàng)目開發(fā)狀況，假使某員工參與過項(xiàng)目開發(fā)，則列出該員工所參與的所有項(xiàng)目的編號(hào)，否則該項(xiàng)以空值表示。

usecompany

selectEMPL.ENOas員工編號(hào),EMPL.ENAMEas姓名,EMPL.BIRTHDATEas出生日期,EMPL.SALARYas工資,Empl.DNOas部門編號(hào),JOB.PNOas項(xiàng)目編號(hào)fromEMPLRIGHTouterjoinJOBonEMPL.ENO=job.ENO

2）使用右連接查詢所有員所在部門名稱，假使某員工尚未分派到任何部門，則該員工的部門名稱項(xiàng)以空值表示。

usecompany

selectEMPL.ENOas員工編號(hào),EMPL.ENAMEas姓名,EMPL.BIRTHDATEas出生日期,EMPL.SALARYas工資,Empl.DNOas部門編號(hào),DEPA.DNAMEAS部門名稱fromEMPLrightouterjoinDEPAonEMPL.DNO=DEPA.DNO

㈡子查詢：

1.多表單值子查詢

1)查詢編號(hào)為1001的員工的部門名。

usecompanyselectDNAMEfromDEPA

whereDNO=(selectDNO

fromEMPL

whereENO='1001')

2)查詢‘客戶中心’部門的員工數(shù)。

usecompany

selectcount(DNO)as員工數(shù)fromEMPL

whereDNO=(selectDNO

fromDEPA

whereDNAME='客戶中心')

3)查詢陳晨所在的項(xiàng)目編號(hào)。

useCOMPANY

selectPNOas項(xiàng)目編號(hào)fromJOB

whereENO=(selectENOfromEMPL

whereENAME='李晨')2.多表多值子查詢

1)查詢1985年以后出生的員工在所在項(xiàng)目的工作天數(shù)。

useCOMPANY

selectDAYSas工作天數(shù)fromJOB

whereENOin(selectENOfromEMPL

whereBIRTHDATE>'1985')

2)查詢參與項(xiàng)目天數(shù)超過40天的員工姓名。

useCOMPANY

selectENAMEas員工姓名fromEMPL

whereENOin(selectENOfromJOBwhereDAYS>'40')

3)查詢工資低于2000的員工的所屬部門。

useCOMPANYSELECTDNAMEAS部門fromDEPA

whereDNOin(selectDNOfromEMPL

whereSALARY(selectavg(empl.salary)fromempl)and

empl.dnoin(selectdepa.dnofromdepawheredepa.dname='客戶中心')

2)查詢“開發(fā)系統(tǒng)〞項(xiàng)目的參與部門。

SELECTT1.DNAME

FROMPROJS1,JOBS2,EMPLS3,DEPAT1WHERET1.DNO=S3.DNOANDS3.ENO=S2.ENOANDS2.PNO=S1.PNOANDS1.PNAME='開發(fā)系統(tǒng)'

3)查詢'宣傳'部門雇員參與的項(xiàng)目名稱。SELECTT1.PNAME

FROMPROJT1,JOBS1,EMPLS2,DEPAS3WHERET1.PNO=S1.PNOANDS1.ENO=S2.ENOANDS2.DNO=S3.PNOANDS3.PNAME='宣傳'

4．相關(guān)子查詢

1)查詢參與兩個(gè)項(xiàng)目以上的員工姓名。

SELECTT1.ENO,T2.ENAMEFROM(

SELECTENO,COUNT(PNO)COUNTFROMJOBGROUPBYENO

HAVINGCOUNT>=2)T1,EMPLT2WHERET1.ENO=T2.ENO

2)查詢所有未參與項(xiàng)目的員工姓名。

SELECTT1.ENAMEFROMEMPLT1

WHERET1.ENONOTIN(SELECTENOFROMJOB)

試驗(yàn)4數(shù)據(jù)庫(kù)的視圖與索引定義

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

本試驗(yàn)的目的是使學(xué)生把握索引與視圖的定義與維護(hù)操作，加深對(duì)索引與視圖在關(guān)系數(shù)據(jù)庫(kù)中的作用的理解。二、試驗(yàn)時(shí)數(shù)2學(xué)時(shí)[相關(guān)知識(shí)]

建立索引是加快查詢速度的有效手段。用戶可以根據(jù)應(yīng)用環(huán)境的需要，在基本表上建立一個(gè)或多個(gè)索引，以提供多個(gè)存取路徑，加快查找速度。

視圖是根據(jù)子模式建立的虛擬表。一個(gè)視圖可以由一個(gè)表構(gòu)造，也可以由多個(gè)表構(gòu)造。查看和修改視圖就宛如對(duì)表的操作一樣，十分簡(jiǎn)單。三、試驗(yàn)內(nèi)容㈠索引1.創(chuàng)立索引

1）為PROJ表的PNAME列創(chuàng)立非聚集索引Pindex。

useCOMPANYgo

createnonclusteredindexPindexonPROJ(PNAME)

go

2）為DEPA表的DNAME（降序），ADDR兩列創(chuàng)立唯一非聚集索引Dindex。

useCOMPANYgo

createuniquenonclusteredindexDindexonDEPA(DNAMEdesc,ADDR)go

3）在PROJ表的PNAME列上創(chuàng)立唯一非聚集索引Pindex，若該索引已存在則刪除后重建。

useCOMPANYgo

createuniquenonclusteredindexPindexonPROJ(PNAME)go

2．查看索引

1）查看DEPA表的索引信息。

useCOMPANYgo

execsp_helpindexDEPA

2）查看數(shù)據(jù)庫(kù)COMPANY索引使用的空間信息

useCOMPANYgo

execsp_spaceused

3.索引更名

1）將DEPA表的索引Dindex名更改為Dindex1。UseCOMPANYGo

Sp_rename‘DEPA.Dindex’,’Dindex1’go

4.刪除索引

1）刪除DEPA表的索引Dindex1。

useCOMPANYgo

dropindexDEPA.Dindex1

㈡視圖1.創(chuàng)立視圖

1）創(chuàng)立14號(hào)部門員工的視圖EMPL_14（ENO，ENAME，SALARY）。

CREATEVIEWEMPL_14(ENO,ENAME,SALARY)AS

SELECTEN0,ENAME,SALARYFROMEMPLWHEREDN0='14'

2)創(chuàng)立包含項(xiàng)目名與員工名及工作天數(shù)的視圖Prjemp。

CREATEVIEWPrjempAS

SELECTPROJ.PNAME,EMPL.ENAME,J0B.DAYSFROMPROJ,J0B,EMPL

WHEREEMPL.EN0=J0B.EN0ANDJ0B.PN0=PROJ.PNO

3）在視圖EMPL_14上創(chuàng)立工資在3000以上的員工的視圖EMPL_14H3。

CREATEVIEWEMPL_14H3AS

SELECTENO,ENAME,SALARYFROMEMPL_14

WHERESALARY>3000

4）創(chuàng)立每個(gè)部門的平均工資的視圖E_AVG（DNO，SAVG）。

CREATEVIEWE_AVG(DNO,SAVG)AS

SELECTDN0,AVG(SALARY)FROMEMPLGROUPBYDN0

2.查詢視圖

1）根據(jù)視圖Prjemp查詢做'設(shè)計(jì)系統(tǒng)'項(xiàng)目的所有員工及工作天數(shù)。

SELECTENAME,DAYSFROMPrjemp

WHEREPNAME=N'設(shè)計(jì)系統(tǒng)'

2）根據(jù)視圖E_AVG查詢‘客戶中心’的平均工資。

SELECTDNO,SAVGFROME_AVG

WHEREDNO=(SELECTDN0FROMDEPA

WHEREDNAME=N'客戶中心')

3．更新視圖

1）在視圖EMPL_14中將1042號(hào)員工的姓名改為‘李立春’。

UPDATEEMPL_14SETENAME=N'李立春'WHEREENO=1402

2）向視圖EMPL_14中插入一條新記錄（1050，‘趙英’，3500）。

INSERTINTOEMPL(EN0,ENAME,SALARY,DN0)VALUES(1050,N'趙英',3500,14)

3）刪除視圖EMPL_14中1042號(hào)員工的記錄。

DELETEFROMEMPL_14WHEREENO=1402

4.刪除視圖

1）刪除視圖Prjemp。

DROPVIEWPrjemp

試驗(yàn)5數(shù)據(jù)庫(kù)的安全性控制

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

本試驗(yàn)的目的是通過試驗(yàn)使學(xué)生加深對(duì)數(shù)據(jù)安全性的理解，并把握SQLServer中有關(guān)用戶，角色及操作權(quán)限的管理方法。二、試驗(yàn)時(shí)數(shù)2學(xué)時(shí)三、試驗(yàn)內(nèi)容

分別使用SQL語(yǔ)言和對(duì)象資源管理器完成以下操作：

1.建立數(shù)據(jù)庫(kù)登錄帳戶S1和D1，密碼均為1234，登錄帳戶S1默認(rèn)數(shù)據(jù)庫(kù)分別為COMPANY，其它保持默認(rèn)值。

EXECsp_addlogin'S1','1234','COMPANY'EXECsp_addlogin'D1','1234'

2.

GO

將S1映射為COMPANY數(shù)據(jù)庫(kù)的同名數(shù)據(jù)庫(kù)用戶。

USE[COMPANY]

CREATEUSER[S1]FORLOGIN[S1]GO

3.將D1映射為COMPANY數(shù)據(jù)庫(kù)的數(shù)據(jù)庫(kù)用戶，數(shù)據(jù)庫(kù)用戶名為S2。

USE[COMPANY]GO

CREATEUSER[S2]FORLOGIN[D1]GO

3．對(duì)數(shù)據(jù)庫(kù)用戶授予權(quán)限

1）授予S1對(duì)EMPL表插入數(shù)據(jù)的權(quán)限，并驗(yàn)證該權(quán)限(方法：通過登錄帳戶S1登錄SQLServer，然后插入一條數(shù)據(jù)來驗(yàn)證該權(quán)限)。

GRANTINSERTONEMPLTOS1；

2）授予S2創(chuàng)立表的權(quán)限，并驗(yàn)證該權(quán)限(方法：通過登錄帳戶D1登錄SQLServer，然后創(chuàng)立-張表來驗(yàn)證該權(quán)限)。

GRANTcreatetableTOS2；

3）收回S1對(duì)EMPL表插入數(shù)據(jù)的權(quán)限，并驗(yàn)證該權(quán)限(方法：通過登錄帳戶S1登錄SQLServer，然后插入一條數(shù)據(jù)來驗(yàn)證該權(quán)限)。

REVOKEINSERTonEMPLfromS1

4.創(chuàng)立用戶角色

1）在COMPANY數(shù)據(jù)庫(kù)中創(chuàng)立角色MYROLE，授予該角色查詢和修改EMPL表的權(quán)限。

CREATEROLEMYROLEGRANTSELECT,UPDATEONEMPLTOMYROLE

2）將S1添加為角色MYROLE的成員，并驗(yàn)證s1是否能夠查詢EMPL表。

execsp_addrolememberMYROLE,S1

3）將S2添加為角色MYROLE的成員，并驗(yàn)證s2是否能夠修改EMPL表。

execsp_addrolememberMYROLE,S2

5．收回?cái)?shù)據(jù)庫(kù)用戶權(quán)限

1）采用用戶角色方法收回S1和S2修改EMPL表的權(quán)限，并驗(yàn)證S1是否還能修改EMPL表。

REVOKEUPDATEONEMPL

FROMMYROLE

2）拒絕S2查詢EMPL表的權(quán)限，并驗(yàn)證S2是否還能查詢EMPL表。

REVOKESELECTONEMPLFROMS2

試驗(yàn)6數(shù)據(jù)完整性

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

本試驗(yàn)的目的是通過試驗(yàn)使學(xué)生加深對(duì)數(shù)據(jù)完整性的理解，把握數(shù)據(jù)完整性的約束條件，學(xué)會(huì)創(chuàng)立和使用觸發(fā)器。二、試驗(yàn)時(shí)數(shù)2學(xué)時(shí)[相關(guān)知識(shí)]

SQLServer實(shí)現(xiàn)數(shù)據(jù)完整性的具體方法有4種：約束、缺省、規(guī)則和觸發(fā)器。觸發(fā)器是實(shí)施繁雜完整性的特別類型的存儲(chǔ)類型。觸發(fā)器不需要專門語(yǔ)句調(diào)用，當(dāng)對(duì)它所保護(hù)數(shù)據(jù)進(jìn)行修改時(shí)自動(dòng)激活，以防止對(duì)數(shù)據(jù)進(jìn)行不正確，未授權(quán)或不一致的修改。三、試驗(yàn)內(nèi)容

1.創(chuàng)立包含非空約束字段的表

創(chuàng)立一個(gè)員工聯(lián)系信息表Contact，一共有3個(gè)字段：雇員號(hào)（Eno）數(shù)據(jù)類型為4位長(zhǎng)定長(zhǎng)字符串；電話（Tel）數(shù)據(jù)類型為8位長(zhǎng)定長(zhǎng)字符串；地址（Addr）數(shù)據(jù)類型為20位變長(zhǎng)字符串；要求雇員號(hào)和電話不能為空。

useCOMPANYcreatetableMail(Enochar(4)notnull,Mailchar(8)notnullAddrvarchar(20))

2.創(chuàng)立包含唯一約束字段的表

創(chuàng)立員工郵箱表Mail，一共有2個(gè)字段：雇員號(hào)（Eno）數(shù)據(jù)類型為4位長(zhǎng)定長(zhǎng)字符串；郵箱地址（Mail）數(shù)據(jù)類型為20位變長(zhǎng)字符串；要求雇員號(hào)非空，郵箱地址唯一。

useCOMPANYcreatetableMail

(Enochar(4)notnull,Mailvarchar(20))

altertableMailaltertableMail

addconstraintEmunique(Mail)

3.創(chuàng)立包含主碼約束字段的表

創(chuàng)立一個(gè)員工卡信息表Card，一共有3個(gè)字段：雇員號(hào)（Eno）數(shù)據(jù)類型為4位長(zhǎng)定長(zhǎng)字符串；卡號(hào)（Cno）數(shù)據(jù)類型為8位長(zhǎng)定長(zhǎng)字符串；開卡日期（Startday）日期格式；要求雇員號(hào)和卡號(hào)聯(lián)合做主鍵。

useCOMPANYcreatetableCard(Enochar(4)notnull,Cnochar(8)notnull,Startdaydatetime)altertableCard

addconstraintCaprimarykey(Eno,Cno)

4.創(chuàng)立包含檢查約束字段的表

完成百分比用小數(shù)表示且數(shù)值小于1；

創(chuàng)立包含檢查約束字段的表

，完成百分比用小數(shù)表示且數(shù)值小于1；

創(chuàng)立包含檢查約束字段的表3個(gè)字段：

項(xiàng)目號(hào)（Pno）數(shù)據(jù)類型為4位長(zhǎng)定長(zhǎng)字符串；

完成百分比用小數(shù)表示且數(shù)值小于1；

創(chuàng)立包含檢查約束字段的表

（Percentage）數(shù)據(jù)類型為DECIMAL(3,2)；已用費(fèi)用（Pem）數(shù)據(jù)類型為INT；

要求：⑴完成百分比用小數(shù)表示且數(shù)值小于1；

創(chuàng)立包含檢查約束字段的表1；

⑵已用費(fèi)用的取值為0-100000（用規(guī)則實(shí)現(xiàn)）

useCOMPANY

createtableProcess(Pnochar(4)notnull,

Percentagedecimal(3,2)notnull,Pemint)

altertableProcessadd

constraintPrcheck(Percentage=0and@Pem

2)創(chuàng)立部門收支表CHARGE，一共有2個(gè)字段：部門號(hào)（Dno）數(shù)據(jù)類型為4位長(zhǎng)定長(zhǎng)字符串收支（CHARGE）數(shù)據(jù)類型為DECIMAL（7，2）要求部門號(hào)是外鍵

useCOMPANY

createtablecharge(Dnochar(4)notnull,CHARGEdecimal(7,2))altertablechargeadd

constraintMg1foreignkey(Dno)referencesMgr(Dno)

7．創(chuàng)立觸發(fā)器

在表EMPL上創(chuàng)立一個(gè)觸發(fā)器EMPL_date,當(dāng)更改員工編號(hào)時(shí)同步更改Job表中對(duì)應(yīng)的員工編號(hào)。

試驗(yàn)7數(shù)據(jù)庫(kù)設(shè)計(jì)

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

使學(xué)生能夠?qū)o定的實(shí)際需求，選擇一種編程語(yǔ)言或開發(fā)工具，完成數(shù)據(jù)庫(kù)設(shè)計(jì)，并實(shí)現(xiàn)數(shù)據(jù)的查詢等操作，從而把握數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)的設(shè)計(jì)方法和步驟。熟悉大型數(shù)據(jù)庫(kù)管理系統(tǒng)的結(jié)構(gòu)與組成；熟悉數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)的設(shè)計(jì)方法和開發(fā)過程；

把握一種大型數(shù)據(jù)庫(kù)管理系統(tǒng)(SQLSERVER或ORACLE)的應(yīng)用技術(shù)和開發(fā)工具的使用；

熟悉數(shù)據(jù)庫(kù)設(shè)計(jì)工具的使用；熟悉數(shù)據(jù)庫(kù)安全的相關(guān)知識(shí)和技術(shù)；熟悉數(shù)據(jù)庫(kù)系統(tǒng)的管理和維護(hù)。二、試驗(yàn)時(shí)數(shù)4學(xué)時(shí)三、試驗(yàn)內(nèi)容

完整實(shí)踐數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)設(shè)計(jì)的工程過程:需求分析\\概念設(shè)計(jì)\\規(guī)律設(shè)計(jì)\\物理設(shè)計(jì)\\實(shí)施\\編寫應(yīng)用程序：