數(shù)據(jù)庫上機實驗

1、精選優(yōu)質(zhì)文檔-傾情為你奉上數(shù)據(jù)庫原理上機實驗報告學 號： 6姓 名： 易磊班 級： 自動化142指導教師： 張光輝昆明理工大學信息工程與自動化學院2006年12月一、實驗目的與要求：l 熟練使用SQL定義子語言、操縱子語言命令語句l 掌握關系模型上的完整性約束機制l 掌握一定的數(shù)據(jù)庫管理技術l 能完成簡單的數(shù)據(jù)庫應用開發(fā)二、實驗內(nèi)容及學時安排（總學時：8）（一）數(shù)據(jù)定義子語言實驗（2學時）實驗1：利用SQL語句創(chuàng)建Employee數(shù)據(jù)庫CREATE DATABASE Employee;實驗2：利用SQL語句在Employee數(shù)據(jù)庫中創(chuàng)建人員表person、月薪表salary及部門表dept。要

2、求：按表1、表達、表3中的字段說明創(chuàng)建表1 person表結(jié)構(gòu)字段名數(shù)據(jù)類型字段長度允許空否字段說明及備注備注P_noChar6Not Null工號，主鍵P_nameVarchar10Not Null姓名SexChar2Not Null性別BirthdateDatetime8Null出生日期ProfVarchar10Null職稱DeptnoChar4Not Null部門代碼實驗12以后作為外鍵（參照dept表）表2 salary表結(jié)構(gòu)字段名數(shù)據(jù)類型字段長度允許空否字段說明及備注P_noChar6Not Null工號，主鍵 實驗12以后作為外鍵（參照person表）BaseDec5Null基本工

3、資BonusDec5Null獎金，要求>50FactDec5Null實發(fā)工資=基本工資+獎金MonthInt2Not Null月份表3 dept表結(jié)構(gòu)字段名數(shù)據(jù)類型字段長度允許空否字段說明DeptnoChar4Not Null部門代碼，主鍵，DnameVarchar10Not Null部門名稱CREATE TABLE dept(Deptno CHAR(4) NOT NULL PRIMARY KEY,Dname VARCHAR(10) NOT NULL);CREATE TABLE person(P_no CHAR(6) NOT NULL PRIMARY KEY,P_name VARCHAR

4、(10) NOT NULL,Sex CHAR(2) NOT NULL,Birthday Datetime,Prof VARCHAR(10),Deptno CHAR(4) NOT NULL);CREATE TABLE salary(P_no CHAR(6) NOT NULL PRIMARY KEY,Base Dec(5),Bonus Dec(5) CHECK(Bonus > 50),Fact AS Base + Bonus,Month int,);（二）數(shù)據(jù)操縱子語言實驗（4學時）實驗3：利用SQL語句向表person、salary和dept中插入數(shù)據(jù)。要求：按表4、表5、表6中的數(shù)據(jù)插入

5、。表4 表person中的數(shù)據(jù)P_noP_nameSexBirthDateProfDeptno磊少男1973-4-7中級0001謝文東男1975-2-14中級0001李勇男1970-8-25高級0002廖小靈女1979-8-6初級0002梁晨晨女1970-8-25中級0003羅向冬男1979-5-11初級0003肖家寨男1963-7-14高級0003/*CREATE DATABASE Employee;*/*CREATE TABLE person(P_no CHAR(6) NOT NULL PRIMARY KEY,P_name VARCHAR(10) NOT NULL,Sex CHAR(2)

6、NOT NULL,Birthday Datetime,Prof VARCHAR(10),Deptno CHAR(4) NOT NULL);CREATE TABLE salary(P_no CHAR(6) NOT NULL PRIMARY KEY,Base Dec(5),Bonus Dec(5) CHECK(Bonus > 50),Fact AS Base + Bonus,Month int,);CREATE TABLE dept(Deptno CHAR(4) NOT NULL PRIMARY KEY,Dname VARCHAR(10) NOT NULL);*/INSERT INTO pe

7、rson (P_no, P_name, Sex, BirthDate, Prof, Deptno)VALUES('','磊少', '男', '1973-4-7', '中級', '0001');INSERT INTO person (P_no, P_name, Sex, BirthDate, Prof, Deptno)VALUES('','謝文東', '男', '1975-2-14', '中級', '0001

8、9;);INSERT INTO person (P_no, P_name, Sex, BirthDate, Prof, Deptno)VALUES('','李勇', '男', '1970-8-25', '高級', '0002');INSERT INTO person (P_no, P_name, Sex, BirthDate, Prof, Deptno)VALUES('','廖小靈', '女', '1979-8-6', '初級&

9、#39;, '0002');INSERT INTO person (P_no, P_name, Sex, BirthDate, Prof, Deptno)VALUES('','梁晨晨', '女', '1970-8-25', '中級', '0003');INSERT INTO person (P_no, P_name, Sex, BirthDate, Prof, Deptno)VALUES('','羅向冬', '男', '1979

10、-5-11', '初級', '0003');INSERT INTO person (P_no, P_name, Sex, BirthDate, Prof, Deptno)VALUES('','肖家寨', '男', '1963-7-14', '高級', '0003');答案：INSERT INTO person (P_no, P_name, Sex, BirthDate, Prof, Deptno)VALUES(, 磊少, 男, 1973-4-7, 中級, 00

11、01);答案：INSERT INTO personVALUES(, 磊少, 男, 1973-4-7, 中級, 0001);表5 表salary中的數(shù)據(jù)P_noBaseBonusFactS_month21003001180030012800280125002501230027511750130124002101答案：INSERT INTO salary (P_no, Base, Bonus, Fact, Month)VALUES(, 2100, 300, NULL, 1);答案：INSERT INTO salaryVALUES(, 2100, 300, NULL, 1);答案3：INSERT I

12、NTO salary (P_no, Base, Bonus, Month)VALUES(, 2100, 300, 1);表6 表dept中的數(shù)據(jù)DeptnoDname0001人事部0002財務部0003市場部答案：INSERT INTO dept (Deptno, Dname)VALUES(001, 人事部);答案：INSERT INTO deptVALUES(001, 人事部);實驗4：（1）利用SQL語句修改表中的數(shù)據(jù)。要求：將salary表中工號為的員工工資增加為1800元，獎金增加為160元。UPDATE salary SET Base = 1800, Bonus = 160 WHER

13、E P_no = ;（2）利用SQL語句刪除表中的數(shù)據(jù)。要求：刪除 person表中工號為的員工數(shù)據(jù)。DELETE FROM person WHERE P_no = ;（3）利用SQL語句查詢person表中的所有數(shù)據(jù)。SELECT * FROM person;實驗5：（1）更新視圖要求：將員工視圖PersonView中姓名為王云的員工職稱改為高職CREATE VIEW PersonViewASSELECT *FROM personUPDATE PersonViewSET prof =高職WHERE P_name = 磊少;（2）向視圖中插入數(shù)據(jù) 要求：向視圖PersonView中插

14、入一行數(shù)據(jù)（，劉美萍，中級，00002）INSERTINTO PersonView(P_no, P_name, prof, Deptno)VALUES(, 女, 中級, 00002);（3）刪除視圖 要求：將視圖PersonView刪除DROP VIEW PersonView;實驗6：條件查詢 要求：（1） 查詢person表中所有不重復的職稱。SELECT DISTINCT prof FROM Person;（2） 查詢person表中職稱為中級的所有員工數(shù)據(jù)。SELECT * FROM person WHERE Prof = 中級;（3） 查詢person表中具有高級職稱的男員工信息。SE

15、LECT * FROM person WHERE Prof = 高級 AND Sex = 男;（4） 查詢person表中姓名為王云、謝志文、羅向東的員工數(shù)據(jù)。SELECT * FROM person WHERE P_name= 磊少 OR P_name= 謝文東 OR P_name= 羅向冬;實驗7：使用ORDER BY排序 要求：利用SQL語句將工號在和之間的員工的月收入按實發(fā)工資升序排序。SELECT * FROM Salary WHERE P_no BETWEEN AND ORDER BY Fact ASC;實驗8：利用SQL語句查詢各部門的實發(fā)工資總數(shù)。SELECT detp.Dna

16、me, sum(Fact) AS “部門實發(fā)工資總數(shù)” FROM dept, salary, personWHERE person.P_no = salary.P_no AND person.Deptno = dept.DetpnoGROUP BY dept.Dname;實驗9：利用SQL語句查詢?nèi)耸虏克袉T工信息。SELECT * FROM Person WHERE Deptno = 0001;實驗10：表的內(nèi)連接查詢：要求：利用SQL語句查詢person表中職稱為中級的員工信息。SELECT * FROM Person WHERE prof = 中級;實驗11：表的外連接查詢：要求：利用S

17、QL語句查詢每個員工1 月份的工資和獎金SELECT Salary.Base, Salary.Bonus FROM Person JOIN Salary ON Person.P_no = Salary.P_no;實驗12：子查詢：要求：利用SQL語句查詢比工號為的員工實發(fā)工資高的所有員工信息。SELECT * FROM Person, Salary WHERE Fact > (SELECT Fact FROM Salary WHERE P_no = );（三） 數(shù)據(jù)完整性實驗（1學時）實驗12：定義外鍵約束 要求：（1） 創(chuàng)建表時將person表的deptno列定義為外鍵，并參考dept

18、表的列deptno。CREATE TABLE person(P_no CHAR(6) NOT NULL PRIMARY KEY,P_name VARCHAR(10) NOT NULL,Sex CHAR(2) NOT NULL,Birthday Datetime,Prof VARCHAR(10),Deptno CHAR(4) NOT NULL,FOREIGN KEY (Deptno) REFERENCES dept(Deptno);ALTER TABLE person FOREIGN KEY (Deptno) REFERENCES dept(Deptno) ON DELETE NO ACTION

19、 ON UPDATE NO ACTION（2） 將salary表中的P_no設為外鍵，并使其參照person表中的列P_no。CREATE TABLE salary(P_no CHAR(6) NOT NULL PRIMARY KEY,Base Dec(5),Bonus Dec(5) CHECK(Bonus > 50),Fact AS Base + Bonus,Mon int,FOREIGN KEY (P_no) REFERENCES person(P_no);實驗13：測試對主表進行插入、更新及刪除操作時的影響。（請寫明原因） 要求：（1） 向表dept中插入一行數(shù)據(jù)（0004，研發(fā)部），測試是否影響從表。INSERT INTO dept(Deptno, Dname) VALUES(0004, 研發(fā)部);不影響（2） 將表dept中的部門號0003改為0006，測試是否影響從表。UPDATE dept SET Deptno =0006 WHERE Deptno = 0003;影響（3） 刪除表dept中部門號為0001的員工的數(shù)據(jù)，測試是否影響從表。DELETE FROM dept WHERE Deptno = 0001;影響實驗14：測試對從表進行插入、更新及刪除操作時的影響。（請寫明原因）要求：（1） 向表person中