1、-. z.數(shù)據(jù)庫(kù)實(shí)驗(yàn)報(bào)告 2012 / 2013 學(xué)年 第 二 學(xué)期學(xué) 號(hào)姓 名指導(dǎo)教師成 績(jī)一、數(shù)據(jù)庫(kù)原理第一次實(shí)驗(yàn)【一】實(shí)驗(yàn)內(nèi)容：數(shù)據(jù)庫(kù)表的建立與管理【二】、實(shí)驗(yàn)?zāi)康模簩W(xué)習(xí)數(shù)據(jù)庫(kù)及表的建立、刪除、更新等操作。注：本次實(shí)驗(yàn)題目，除了特殊要求，以T-SQL為主，并將所有語(yǔ)句標(biāo)注好題號(hào)，留存在查詢界面上，方便檢查?！救?、實(shí)驗(yàn)題目及其解答：1、創(chuàng)立一名為test的數(shù)據(jù)庫(kù);Solution：CREATEDATABASEtest2、在test數(shù)據(jù)庫(kù)中新建一*部門表 部門，輸入列：namechar，10位，IDchar，7位，manager (char,10位)各列均不能為空值。Solution：us

2、etestCREATETABLE部門(IDCHAR(7)NOTNULL,nameCHAR(10)NOTNULL,managerCHAR(10)NOTNULL)結(jié)果：3、在test數(shù)據(jù)庫(kù)中新建一*員工表，命名為員工。在表中輸入以下各列： namechar，10位，personIDchar，7位，Se*char，7位，birthdaydatetime，deptIDchar，7位，各列均不能為空值。Solution：CREATETABLE員工(nameCHAR(10)NOTNULL,personIDCHAR(7)NOTNULL,se*CHAR(7)NOTNULL,birthdaydatetimeNO

3、TNULL,deptIDCHAR(7)NOTNULL)結(jié)果：4、修改表的操作練習(xí)：1將部門表中的列ID設(shè)為主鍵；2將員工表中personID設(shè)為主鍵，并將deptID設(shè)置為外鍵，關(guān)聯(lián)到部門表上的ID列；3在部門表中，添加列quantity(char, 5)； 4) 刪除員工表中的列se*； 5修改員工表中列name為varchar,8Solution：ALTERTABLE部門ADDCONSTRAINT C1 PRIMARYKEY(ID)ALTERTABLE員工ADDCONSTRAINT C2 PRIMARYKEY(personID)ALTERTABLE員工ADDCONSTRAINT C3 FO

4、REIGNKEY(deptID)REFERENCES部門(ID)ALTERTABLE部門ADDquantityCHAR(5)ALTERTABLE員工DROPCOLUMNse*ALTERTABLE員工ALTERCOLUMNnameVARCHAR(8)結(jié)果：5、1在數(shù)據(jù)庫(kù)test中新建表scores,輸入以以下：ID char，8位 主鍵, C語(yǔ)言numeric(3,1) ，IT英語(yǔ) numeric(3,1) ，數(shù)據(jù)庫(kù) numeric(3,1) ，軟件根底 numeric(3,1) ，平均成績(jī) 。四門學(xué)科都不能為空，并且平均成績(jī)?yōu)樗拈T學(xué)科的平均分； 2為表scores中的四項(xiàng)成績(jī)添加default

5、約束：使其默認(rèn)值為0； 3為表scores中的四項(xiàng)成績(jī)添加check約束：是每項(xiàng)成績(jī)?cè)?到100之間。Solution：CREATETABLEscores(IDCHAR(8)PRIMARYKEYNOTNULL,C語(yǔ)言NUMERIC(3,1)NOTNULL,IT英語(yǔ)NUMERIC(3,1)NOTNULL,數(shù)據(jù)庫(kù)NUMERIC(3,1)NOTNULL,軟件根底NUMERIC(3,1)NOTNULL,平均成績(jī)AS(C語(yǔ)言+IT英語(yǔ)+數(shù)據(jù)庫(kù)+軟件根底)/4)ALTERTABLEscoresADDCONSTRAINT C語(yǔ)言DEFAULT0FORC語(yǔ)言ALTERTABLEscoresADDCONSTRA

6、INT IT英語(yǔ)DEFAULT0FORIT英語(yǔ)ALTERTABLEscoresADDCONSTRAINT 數(shù)據(jù)庫(kù)DEFAULT0FOR數(shù)據(jù)庫(kù)ALTERTABLEscoresADDCONSTRAINT 軟件根底DEFAULT0FOR軟件根底ALTERTABLEscoresADDCONSTRAINT CK1 CHECK(C語(yǔ)言=0 AND C語(yǔ)言=0 AND IT英語(yǔ) =0 AND 數(shù)據(jù)庫(kù)=0 AND 軟件根底=0 AND C語(yǔ)言=0 AND IT英語(yǔ) =0 AND 數(shù)據(jù)庫(kù)=0 AND 軟件根底(SELECT收入FROMSalaryWHERE編號(hào)=102201);結(jié)果：4數(shù)據(jù)匯總1求財(cái)務(wù)部員工的平

7、均凈收入2求財(cái)務(wù)部雇員的總?cè)藬?shù)Solution：SELECTAVG(b.收入-b.支出)AS財(cái)務(wù)部員工的平均凈收入FROMEmployeesASaINNERJOINSalaryASbONa.編號(hào)=b.編號(hào)WHERE部門號(hào)=1;SELECTCOUNT(編號(hào))AS財(cái)務(wù)部雇員的總?cè)藬?shù)FROMEmployeesWHERE部門號(hào)=1;結(jié)果：5GROUP BY ，ORDER BY 子句的使用1求各部門的雇員數(shù)2將各雇員的情況按薪水由低到高排列Solution：SELECTb.部門名稱,COUNT(a.編號(hào))AS部門人數(shù)FROMEmployeesASaJOINDepartmentsASbONa.部門號(hào)=b.

8、部門編號(hào)groupby部門名稱;SELECTa.*,b.收入FROMEmployeesASaINNERJOINSalaryASbONa.編號(hào)=b.編號(hào)orderby收入;結(jié)果：6、選做題查找比所有財(cái)務(wù)部的員工的收入都高的員工的*；Solution：SELECTFROMEmployeesWHERE編號(hào)IN(SELECT編號(hào)FROMSalaryWHERE收入All(SELECT收入FROMSalaryWHERE編號(hào)IN(SELECT編號(hào)FROMEmployeesWHERE部門號(hào)=1)結(jié)果：【四】實(shí)驗(yàn)二完整的代碼：CREATEDATABASEpyusepyCREATETABLEEmployees(編

9、號(hào)CHAR(10)NOTNULLPRIMARYKEY,VARCHAR(10)NOTNULL,地址CHAR(10)NOTNULL, CHAR(6),CHAR(11)NOTNULL,部門號(hào)CHAR(3)NOTNULL,出生日期DATENOTNULL,性別CHAR(2)NOTNULL);CREATETABLEDepartments(部門編號(hào)CHAR(3)NOTNULLPRIMARYKEY,部門名稱CHAR(20)NOTNULL,備注te*t);CREATETABLESalary(編號(hào)CHAR(10)NOTNULLPRIMARYkey,收入intdefault0,支出intdefault0);CREA

10、TETABLEPurchase(員工編號(hào)CHAR(10)NOTNULL,商品CHAR(4)NOTNULL,購(gòu)置數(shù)量CHAR(4)NOTNULL,PRIMARYKEY(員工編號(hào),商品);INSERTINTODepartmentsVALUES(1,財(cái)務(wù)部,NULL);INSERTINTODepartmentsVALUES(2,人力資源部,NULL);INSERTINTODepartmentsVALUES(3,生產(chǎn)部,NULL);INSERTINTODepartmentsVALUES(4,采購(gòu)部,NULL);INSERTINTODepartmentsVALUES(5,銷售部,NULL);INSERT

11、INTOEmployeesVALUES(000001,*曉強(qiáng),230027,3333556,2,1956-1-1,1)INSERTINTOEmployeesVALUES(010008,柳樹,230027,3131359,1,1966-1-1,1)INSERTINTOEmployeesVALUES(002001,許靜,230026,3132359,1,1972-1-1,1)INSERTINTOEmployeesVALUES(020018,李可,230026,3693564,1,1950-1-1,0)INSERTINTOEmployeesVALUES(102201,*三豐,123456,36353

12、64,5,1962-1-1,0)INSERTINTOEmployeesVALUES(102208,白眉,123456,3458965,5,1955-1-1,1)INSERTINTOEmployeesVALUES(108991,胡適,123456,4568921,3,1969-1-1,1)INSERTINTOEmployeesVALUES(111006,成吉思汗,123456,3458145,5,1964-1-1,1)INSERTINTOEmployeesVALUES(210678,蕭然,123456,3454565,3,1967-1-1,1);INSERTINTOSalaryVALUES(00

13、0001,2100,123)INSERTINTOSalaryVALUES(010008,1582,88)INSERTINTOSalaryVALUES(002001,2569,185)INSERTINTOSalaryVALUES(020018,1987,79)INSERTINTOSalaryVALUES(102201,2066,108)INSERTINTOSalaryVALUES(102208,2980,210)INSERTINTOSalaryVALUES(108991,3259,281)INSERTINTOSalaryVALUES(111006,2860,198)INSERTINTOSalar

14、yVALUES(210678,2347,180);INSERTINTOPurchaseVALUES(000001,01,3);INSERTINTOPurchaseVALUES(000001,03,1);INSERTINTOPurchaseVALUES(002001,01,5);INSERTINTOPurchaseVALUES(020018,03,6);INSERTINTOPurchaseVALUES(108991,11,1);INSERTINTOPurchaseVALUES(000001,11,3);INSERTINTOPurchaseVALUES(020018,01,5);INSERTINT

15、OPurchaseVALUES(201678,02,1);INSERTINTOPurchaseVALUES(020018,11,2);SELECT*FROMEmployeesWHERE出生日期LIKE%1956%or出生日期LIKE%1962%or出生日期LIKE%1964%;SELECT*FROMEmployeesWHERE性別=1;SELECT*FROMEmployeesWHERELIKE%白%;SELECT*FROMEmployeesWHEREnotLIKE%ANDnotLIKE胡%ANDLIKE_;SELECTEmployees.*,Salary.收入FROMEmployeesINNE

16、RJOINSalaryONEmployees.編號(hào)=Salary.編號(hào)SELECTa.*,b.收入FROMEmployeesASaJOINsalaryASbONa.編號(hào)=b.編號(hào)WHERE收入(SELECT收入FROMSalaryWHERE編號(hào)=102201);SELECTAVG(b.收入-b.支出)AS財(cái)務(wù)部員工的平均凈收入FROMEmployeesASaINNERJOINSalaryASbONa.編號(hào)=b.編號(hào)WHERE部門號(hào)=1;SELECTCOUNT(編號(hào))AS財(cái)務(wù)部雇員的總?cè)藬?shù)FROMEmployeesWHERE部門號(hào)=1;SELECTb.部門名稱,COUNT(a.編號(hào))AS部門人數(shù)

17、FROMEmployeesASaJOINDepartmentsASbONa.部門號(hào)=b.部門編號(hào)groupby部門名稱;SELECTa.*,b.收入FROMEmployeesASaINNERJOINSalaryASbONa.編號(hào)=b.編號(hào)orderby收入;SELECTFROMEmployeesWHERE編號(hào)IN(SELECT編號(hào)FROMSalaryWHERE收入All(SELECT收入FROMSalaryWHERE編號(hào)IN(SELECT編號(hào)FROMEmployeesWHERE部門號(hào)=1)三、數(shù)據(jù)庫(kù)原理第三次實(shí)驗(yàn)【一】、實(shí)驗(yàn)內(nèi)容：數(shù)據(jù)庫(kù)的表中數(shù)據(jù)的操作。【二】、實(shí)驗(yàn)?zāi)康模?、學(xué)習(xí)SQL語(yǔ)句進(jìn)展

18、表中的數(shù)據(jù)的插入、更新和刪除；2、學(xué)會(huì)含有子查詢語(yǔ)句的數(shù)據(jù)更新操作；2、理解表中列的屬性對(duì)表中數(shù)據(jù)的影響?！救?、實(shí)驗(yàn)準(zhǔn)備試用上次實(shí)驗(yàn)創(chuàng)立的員工管理數(shù)據(jù)庫(kù)py及相應(yīng)的四*表具體見實(shí)驗(yàn)2：Employees：?jiǎn)T工信息表Departments：部門信息表Salary：?jiǎn)T工薪水信息表Purchase：?jiǎn)T工購(gòu)物信息表【四】、實(shí)驗(yàn)題目及其解答：向Employees表中參加一列：年齡，其值為當(dāng)前時(shí)間與出生日期差值提示：年齡的計(jì)算可以使用getdate及year兩個(gè)日期函數(shù)，具體使用參考聯(lián)機(jī)叢書Transact SQL參考。SolutionusepyALTERTABLEEmployeesADD年齡ASdat

19、ediff(year,出生日期,getdate()；2、數(shù)據(jù)更新：1將編號(hào)為102201的雇員地址改為；2將編號(hào)為102208的雇員收入降低10；3更改salary表，如果收入和支出的差值小于2000，則將收入提高1000；Solution：SELECT*FROMEmployeesUPDATEEmployeesSET地址=WHERE編號(hào)=102201SELECT*FROMEmployeesUPDATESalarySET收入=收入*0.9 WHERE編號(hào)=102208SELECT*FROMSalaryUPDATESalarySET收入=收入+1000WHEREabs(收入-支出)2000;結(jié)果：

20、使用SELECTINTO進(jìn)展多行插入： 將每個(gè)部門的編號(hào)，名稱，雇員數(shù)，插入到新表DepartmentStatic中。Solution：SELECT*FROMSalarySELECTDepartments.部門編號(hào),Departments.部門名稱,COUNT(Employees.編號(hào))AS部門人數(shù)INTODepartmentStaticFROMDepartmentsINNERJOINEmployeesonDepartments.部門編號(hào)=Employees.部門號(hào)GROUPBY部門名稱,部門編號(hào)SELECT*FROMDepartmentStatic;結(jié)果 4、含子查詢的數(shù)據(jù)更新1將地址為*的

21、雇員收入增加52將收入小于平均收入的雇員的收入增加500元3將財(cái)務(wù)部的雇員收入減少100Solution：UPDATESalarySET收入=收入*1.05WHERE編號(hào)IN(SELECT編號(hào)FROMEmployeesWHERE地址=)SELECTEmployees.編號(hào),Employees.,Employees.地址,Salary.收入FROMEmployeesINNERJOINSalaryONEmployees.編號(hào)=Salary.編號(hào)UPDATESalarySET收入=收入+500WHERE收入6000)SELECTEmployees.編號(hào),Employees.,Employees.部門

22、號(hào),Salary.收入,Salary.支出FROMEmployeesINNERJOINSalaryONEmployees.編號(hào)=Salary.編號(hào)DELETEFROMEmployeesWHERE編號(hào)IN(SELECT編號(hào)FROMSalaryWHERE收入(SELECTAVG(收入)FROMSalary)DELETEFROMSalaryWHERE編號(hào)IN(SELECT編號(hào)FROMSalaryWHERE收入(SELECTAVG(收入)FROMSalary)SELECT*FROMEmployeesSELECT*FROMSalary;結(jié)果：【四】實(shí)驗(yàn)三完整的代碼：usepyALTERTABLEEmpl

23、oyeesADD年齡ASdatediff(year,出生日期,getdate()SELECT*FROMEmployeesUPDATEEmployeesSET地址=WHERE編號(hào)=102201SELECT*FROMEmployeesUPDATESalarySET收入=收入*0.9 WHERE編號(hào)=102208SELECT*FROMSalaryUPDATESalarySET收入=收入+1000WHEREabs(收入-支出)2000SELECT*FROMSalarySELECTDepartments.部門編號(hào),Departments.部門名稱,COUNT(Employees.編號(hào))AS部門人數(shù)INT

24、ODepartmentStaticFROMDepartmentsINNERJOINEmployeesonDepartments.部門編號(hào)=Employees.部門號(hào)GROUPBY部門名稱,部門編號(hào)SELECT*FROMDepartmentStaticUPDATESalarySET收入=收入*1.05WHERE編號(hào)IN(SELECT編號(hào)FROMEmployeesWHERE地址=)SELECTEmployees.編號(hào),Employees.,Employees.地址,Salary.收入FROMEmployeesINNERJOINSalaryONEmployees.編號(hào)=Salary.編號(hào)UPDATE

25、SalarySET收入=收入+500WHERE收入6000)SELECTEmployees.編號(hào),Employees.,Employees.部門號(hào),Salary.收入,Salary.支出FROMEmployeesINNERJOINSalaryONEmployees.編號(hào)=Salary.編號(hào)DELETEFROMEmployeesWHERE編號(hào)IN(SELECT編號(hào)FROMSalaryWHERE收入(SELECTAVG(收入)FROMSalary)DELETEFROMSalaryWHERE編號(hào)IN(SELECT編號(hào)FROMSalaryWHERE收入(SELECTAVG(收入)FROMSalary)

26、SELECT*FROMEmployeesSELECT*FROMSalary四、數(shù)據(jù)庫(kù)原理第四次實(shí)驗(yàn)【一】、實(shí)驗(yàn)內(nèi)容：視圖練習(xí)【二】、實(shí)驗(yàn)?zāi)康模?、練習(xí)視圖的建立與使用；2、理解視圖的優(yōu)點(diǎn)和作用。【三】、實(shí)驗(yàn)準(zhǔn)備：創(chuàng)立員工管理數(shù)據(jù)庫(kù)pany及相應(yīng)的四*表：Employees：?jiǎn)T工信息表Departments：部門信息表Salary：?jiǎn)T工薪水信息表purchase表：購(gòu)置信息表【四】、實(shí)驗(yàn)題目及其解答：1、建立視圖建立視圖view1， 查詢所有財(cái)務(wù)部的職工的編號(hào)、*和工資收入，顯示前5項(xiàng)， 并按照收入排序。Solution:usepyCREATEVIEWview1(編號(hào),收入)AS(SELECTE

27、mployees.編號(hào),收入FROMEmployeesINNERJOINSalaryONSalary.編號(hào)=Employees.編號(hào)WHERE部門號(hào)=(SELECT部門編號(hào)FROMDepartmentsWHERE部門名稱=財(cái)務(wù)部);SELECTTOP 5 編號(hào),收入FROMview1ORDERBY收入;建立視圖view2，查詢所有職工的職工編號(hào)、*和盈余；Solution:CREATEVIEWview2(編號(hào),盈余)AS(SELECTEmployees.編號(hào),(收入-支出)AS盈余FROMEmployeesINNERJOINSalaryONEmployees.編號(hào)=Salary.編號(hào))SELE

28、CT編號(hào),盈余FROMview2在建立視圖view1上建立視圖view3，要求為：所有財(cái)務(wù)部的，收入大于2000的職工編號(hào)、*和工資收入；Solution:CREATEVIEWview3(編號(hào),收入)ASSELECT編號(hào),收入FROMview1WHERE收入2000SELECT*fromview3建立市場(chǎng)部購(gòu)置商品1的員工的信息的視圖view4，并在視圖view4上查詢收入大于3000的員工信息；Solution:CREATEVIEWview4ASSELECT*FROMEmployeesINNERJOINPurchaseonEmployees.編號(hào)=Purchase.員工編號(hào)WHERE商品=0

29、1SELECT*FROMview4WHERE編號(hào)IN(SELECT編號(hào)FROMSalaryWHERE收入3000)建立關(guān)于年齡小于40的所有員工的編號(hào)、地址、年齡的視圖view5；Solution:CREATEVIEWview5(編號(hào),地址,年齡)ASSELECT編號(hào),地址,年齡FROMEmployeesWHERE年齡3000SELECT部門號(hào)FROMEmployeesWHERE編號(hào)in(SELECTview2.編號(hào)FROMview2WHERE盈余=(SELECTMA*(盈余)FROMview2)3、更新視圖1將視圖view5中員工編號(hào)為 002001的改為8263137；2在視圖view6上

30、添加一個(gè)新的員工記錄：010076 ，祝無(wú)雙，3865730，2,1985-11-11，1Solution:UPDATEview5SET=8263137WHERE編號(hào)=002001SELECT*FROMview5INSERTINTOview6values(010076,祝無(wú)雙,3865730,2,1985-11-11,1)SELECT*FROMview6【四】實(shí)驗(yàn)四完整的代碼：usepyCREATEVIEWview1(編號(hào),收入)AS(SELECTEmployees.編號(hào),收入FROMEmployeesINNERJOINSalaryONSalary.編號(hào)=Employees.編號(hào)WHERE部門號(hào)=(SELECT部門編號(hào)FROMDepartmentsWHERE部門名稱=財(cái)務(wù)部);SELECTTOP 5 編號(hào),收入FROMview1ORDERBY收入CREATEVIEWview2(編號(hào),盈余)AS(SELECTEmployees.編號(hào),(收入-支出)AS盈余FROMEmployeesINNERJOINSalaryONEmployees.編號(hào)=Salary.編號(hào))SELECT編號(hào),盈余FROMview2CREATEVIEWview3(編號(hào),收入)ASSELECT編號(hào),收入FROMview1W