數(shù)據(jù)庫(kù)實(shí)驗(yàn)三

1、西南石油大學(xué)實(shí)驗(yàn)報(bào)告課程名稱：數(shù)據(jù)庫(kù)原理插入你的照片實(shí)驗(yàn)項(xiàng)目名稱：實(shí)驗(yàn)3 SQL數(shù)據(jù)定義語(yǔ)言 專業(yè)年級(jí)班級(jí)、姓名、學(xué)號(hào)：電子郵件地址：實(shí)驗(yàn)所用機(jī)器名：實(shí)驗(yàn)時(shí)間地點(diǎn)：2015.12.11 明理樓實(shí)驗(yàn)指導(dǎo)教師：孫瑜成績(jī)批改人批改日期注意：在粘貼截圖時(shí)請(qǐng)保留窗口完整標(biāo)題，但只需保留關(guān)鍵界面，多余的空白界面請(qǐng)刪除。一、實(shí)驗(yàn)課時(shí)：4二、實(shí)驗(yàn)?zāi)康?1) 掌握使用T-SQL語(yǔ)句創(chuàng)建、刪除數(shù)據(jù)庫(kù)的方法。(2) 掌握使用T-SQL語(yǔ)句創(chuàng)建、修改、刪除表的方法。(3) 掌握使用T-SQL語(yǔ)句創(chuàng)建、刪除數(shù)據(jù)庫(kù)完整性約束條件的方法。(4) 掌握使用T-SQL語(yǔ)句對(duì)表添加、修改、刪除數(shù)據(jù)的方法。(5) 掌握使用T-SQ

2、L語(yǔ)句創(chuàng)建、修改、刪除、查詢視圖的方法。三、實(shí)驗(yàn)要求(1) 使用SQL Server 2008查詢分析器。(2) 嚴(yán)格依照操作步驟進(jìn)行。(3) 在本地服務(wù)器中創(chuàng)建和管理數(shù)據(jù)庫(kù)。四、實(shí)驗(yàn)環(huán)境(1) PC機(jī)。(2) SQL Server 2008。五、實(shí)驗(yàn)內(nèi)容及步驟（請(qǐng)?zhí)貏e注意實(shí)驗(yàn)步驟:第6項(xiàng)的第1小項(xiàng)，即“插入數(shù)據(jù)”操作必須在第4項(xiàng)以前執(zhí)行）    1使用Transact-SQL語(yǔ)句創(chuàng)建JOBS數(shù)據(jù)庫(kù)，數(shù)據(jù)庫(kù)名格式為JOBS_SunYu(即JOBS_你的中文名字拼音)CREATE Database JOBS_DengZhiPeng;2使用Transact-SQL語(yǔ)句

3、創(chuàng)建JOBS數(shù)據(jù)庫(kù)包含的所有表EMPLOYEECREATE TABLE EMPLOYEE(EMPNO SMALLINT NOT NULL,SUPNAME VARCHAR(50) NOT NULL,FORENAMES VARCHAR(50) NOT NULL,DOB DATE NOT NULL,ADDRESS VARCHAR(50) NOT NULL,TELNO CHAR(10) NOT NULL,DEPNO SMALLINT NOT NULL);JOBHISTORYCREATE TABLE JOBHISTORY(EMPNO SMALLINT NOT NULL,POSITION VARCHAR(

4、50) NOT NULL,STARDATE DATE NOT NULL,ENDDATE DATE NULL,SALARY INT NOT NULL);COURSECREATE TABLE COURSE(COURSENO SMALLINT NOT NULL,CNAME VARCHAR(50) NOT NULL,CDATE DATE NOT NULL);DEPARTMENTCREATE TABLE DEPARTMENT(DEPNO SMALLINT NOT NULL,DNAME VARCHAR(50) NOT NULL,LOCATION VARCHAR(10) NOT NULL,HEAD SMAL

5、LINT NOT NULL);EMPCOURSECREATE TABLE EMPCOURSE(EMPNO SMALLINT NOT NULL,COURSENO SMALLINT NOT NULL);3使用Transact-SQL語(yǔ)句創(chuàng)建JOBS數(shù)據(jù)庫(kù)包含表的主鍵、外鍵約束條件創(chuàng)建每個(gè)表的主鍵：ALTER TABLE EMPLOYEE ADD CONSTRAINT PK_EMPLOYEEPRIMARY KEY (EMPNO);ALTER TABLE JOBHISTORY ADD CONSTRAINT PK_JOBHISTORYPRIMARY KEY (EMPNO,POSITION,STARDAT

6、E);ALTER TABLE COURSE ADD CONSTRAINT PK_COURSEPRIMARY KEY (COURSENO);ALTER TABLE DEPARTMENT ADD CONSTRAINT PK_DEPARTMENTPRIMARY KEY (DEPNO);ALTER TABLE EMPCOURSE ADD CONSTRAINT PK_EMCOURSEPRIMARY KEY (EMPNO,COURSENO);創(chuàng)建關(guān)系：ALTER TABLE JOBHISTORY ADD CONSTRAINT FK_JOBHISTORY_EMPLOYEEFOREIGN KEY (EMPNO

7、)REFERENCES EMPLOYEE (EMPNO);ALTER TABLE EMPCOURSE ADD CONSTRAINT FK_EMPCOURSE_EMPLOYEEFOREIGN KEY (EMPNO)REFERENCES EMPLOYEE (EMPNO);ALTER TABLE EMPCOURSE ADD CONSTRAINT FK_EMPCOURSE_COURSEFOREIGN KEY (COURSENO)REFERENCES COURSE (COURSENO);ALTER TABLE EMPLOYEEADD CONSTRAINT FK_EMPLOYEE_DEPARTMENTFO

8、REIGN KEY (DEPNO)REFERENCES DEPARTMENT (DEPNO);ALTER TABLE DEPARTMENTADD CONSTRAINT FK_DEPARTMENT_EMPLOYEEFOREIGN KEY (HEAD)REFERENCES EMPLOYEE(EMPNO);具體關(guān)系：EXECUTE sp_helpconstraint DEPARTMENT;EXECUTE sp_helpconstraint EMPCOURSE;EXECUTE sp_helpconstraint JOBHISTORY;EXECUTE sp_helpconstraint EMPLOYEE

9、;(所有JOBS中表的約束圖。注意：必須如上圖所示清楚完整顯示約束的constraint_type、constraint_name、constraint_keys等信息。)4備份JOBS數(shù)據(jù)庫(kù) 5 使用Transact-SQL語(yǔ)句在JOBS數(shù)據(jù)庫(kù)里創(chuàng)建視圖(注意：在以下各個(gè)小題中，后續(xù)題目可以利用前面題目創(chuàng)建的視圖)(1) 創(chuàng)建一個(gè)名為“firstview”的視圖，列出不重復(fù)的所有選修了課程的empno (插入定義該視圖的SQL窗口)CREATE VIEW firstview(EMPNO)ASSELECT DISTINCT EMPNOFROM EMPCOURSE;(插入查詢?cè)撘晥D的SQL窗口及

10、結(jié)果)(2) 創(chuàng)建一個(gè)名為“secondview”的視圖，列出所有empno小于5的員工信息(插入定義該視圖的SQL窗口)CREATE VIEW secondview(EMPNO,SUPNAME,FORENAMES,DOB,ADDRESS,TELNO,DEPNO)ASSELECT *FROM EMPLOYEEWHERE EMPNO < 5;(插入查詢?cè)撘晥D的SQL窗口及結(jié)果)(3) 創(chuàng)建一個(gè)名為“thirdview”的視圖，列出每個(gè)empno及其相應(yīng)的選修課程數(shù)(插入定義該視圖的SQL窗口)CREATE VIEW thirdview(EMPNO,COURSENUM)ASSELECT EL

11、.EMPNO,COUNT(*)FROM EMPLOYEE EL JOINEMPCOURSE EC ON EL.EMPNO = EC.EMPNOGROUP BY EL.EMPNO;(插入查詢?cè)撘晥D的SQL窗口及結(jié)果)(4) 創(chuàng)建一個(gè)名為“fourthview”的視圖，列出每個(gè)empno及其已經(jīng)或正在從事的工作數(shù) (插入定義該視圖的SQL窗口)CREATE VIEW fourthview(EMPNO,JOBNUM)ASSELECT EL.EMPNO ,COUNT(*) FROM EMPLOYEE ELJOIN JOBHISTORY JO ON EL.EMPNO = JO.EMPNOGROUP BY

12、 EL.EMPNO;(插入查詢?cè)撘晥D的SQL窗口及結(jié)果)SELECT * FROM FOURTHVIEW;(5) 創(chuàng)建一個(gè)合并第3和第4小題視圖的SELECT語(yǔ)句，以查詢每個(gè)empno對(duì)應(yīng)的工作數(shù)和課程數(shù)。你不需要重復(fù)創(chuàng)建視圖，只需要利用前面兩個(gè)小題中已經(jīng)創(chuàng)建好的視圖。如果某員工號(hào)對(duì)應(yīng)的課程數(shù)為0，則在查詢結(jié)果中應(yīng)顯示為NULL。提示：用外連接(插入該查詢的SQL窗口及結(jié)果)SELECT EL.EMPNO,T.COURSENUM,F.JOBNUM FROM EMPLOYEE EL LEFT JOIN THIRDVIEW T ON T.EMPNO = EL.EMPNOJOIN FOURTHVIE

13、W F ON EL.EMPNO =F.EMPNO;  6使用Transact-SQL語(yǔ)句對(duì)表添加、修改、刪除數(shù)據(jù)(1) 插入數(shù)據(jù)按照ActiveSQL_JobsDB.rar文件里的數(shù)據(jù)庫(kù)狀態(tài)圖插入所有表的數(shù)據(jù)(如果試圖插入的數(shù)據(jù)將會(huì)違反第3步創(chuàng)建的約束條件，則可跳過(guò)該行數(shù)據(jù)的輸入)。INSERT INTO COURSE(COURSENO,CNAME,CDATE)VALUES (1,'Basic Accounting','1989-01-11');INSERT INTO COURSE(COURSENO,CNAME,CDATE)VALUES (2,'

14、;Further Accounting','1989-01-25');INSERT INTO COURSE(COURSENO,CNAME,CDATE)VALUES (3,'Issues In Administration','1988-09-27');INSERT INTO COURSE(COURSENO,CNAME,CDATE)VALUES (4,'More Administration','1988-10-16');select * from course;ALTER TABLE EMPLOYEE AL

15、TER COLUMN DEPNO SMALLINT NULL;INSERT INTO EMPLOYEE(EMPNO,SUPNAME,FORENAMES,DOB,ADDRESS,TELNO,DEPNO)VALUES (1,'Jones','Elizabeth Barbara','1944-01-05','26 Agnews Terrace,Shamrock Bay','2123372288', NULL);INSERT INTO EMPLOYEE(EMPNO,SUPNAME,FORENAMES,DOB,ADDRESS

16、,TELNO,DEPNO)VALUES (2,'Smith','Robert','1947-02-07','18 Marsh Street,Tollcross,Edinburgh','0317328972', NULL);INSERT INTO EMPLOYEE(EMPNO,SUPNAME,FORENAMES,DOB,ADDRESS,TELNO,DEPNO)VALUES (3,'White','Allan','1961-05-05','6 Remote Pla

17、ce,North Berwick','1215556622', NULL);INSERT INTO EMPLOYEE(EMPNO,SUPNAME,FORENAMES,DOB,ADDRESS,TELNO,DEPNO)VALUES (4,'Reid','Gordon','1963-08-10','9 Noble Road,Penicuik','6294246713', NULL);INSERT INTO EMPLOYEE(EMPNO,SUPNAME,FORENAMES,DOB,ADDRESS,T

18、ELNO,DEPNO)VALUES (5,'MacCallan','Claire','1958-09-18','25 Crisis Avenue,Leith,Edinburgh','0313374166', NULL);INSERT INTO EMPLOYEE(EMPNO,SUPNAME,FORENAMES,DOB,ADDRESS,TELNO,DEPNO)VALUES (6,'Murphy','Brian Charles','1954-06-30','9 Ro

19、berts Street,Biggar','3312294147', NULL);當(dāng)錄制完DEPARTMENT以后，再將NULL設(shè)置為相應(yīng)的DEPNO值。UPDATE EMPLOYEESET DEPNO = 1WHERE DEPNO IS NULL;最后的結(jié)果：INSERT INTO DEPARTMENT(DEPNO,DNAME,LOCATION,HEAD)VALUES (1,'accounts','floor3',1);INSERT INTO DEPARTMENT(DEPNO,DNAME,LOCATION,HEAD)VALUES (2,

20、'administration','floor2',1);INSERT INTO DEPARTMENT(DEPNO,DNAME,LOCATION,HEAD)VALUES (3,'software design','floor1',2);INSERT INTO DEPARTMENT(DEPNO,DNAME,LOCATION,HEAD)VALUES (4,'communications','floor4',3);select * from DEPARTMENT;INSERT INTO EMPCOURSE

21、(EMPNO,COURSENO)VALUES (1,1);INSERT INTO EMPCOURSE(EMPNO,COURSENO)VALUES (1,2);INSERT INTO EMPCOURSE(EMPNO,COURSENO)VALUES (2,1);INSERT INTO EMPCOURSE(EMPNO,COURSENO)VALUES (2,2);select * from EMPCOURSE;INSERT INTO JOBHISTORY(EMPNO,POSITION,STARDATE,ENDDATE,SALARY)VALUES (1,'Accounts Manager'

22、;,'1976-01-12',NULL,30000);INSERT INTO JOBHISTORY(EMPNO,POSITION,STARDATE,ENDDATE,SALARY)VALUES (1,'Assistant Accounts Manager','1972-02-11','1976-01-12',22000);INSERT INTO JOBHISTORY(EMPNO,POSITION,STARDATE,ENDDATE,SALARY)VALUES (1,'Accountant','1968-03-1

23、0','1972-02-11',15000);INSERT INTO JOBHISTORY(EMPNO,POSITION,STARDATE,ENDDATE,SALARY)VALUES (1,'Junior Accountant','1964-04-09','1968-03-10',6000);INSERT INTO JOBHISTORY(EMPNO,POSITION,STARDATE,ENDDATE,SALARY)VALUES (2,'Assistant Accounts Manager','197

24、6-05-08',NULL,25000);INSERT INTO JOBHISTORY(EMPNO,POSITION,STARDATE,ENDDATE,SALARY)VALUES (2,'Accountant','1971-06-07','1976-05-08',16000);INSERT INTO JOBHISTORY(EMPNO,POSITION,STARDATE,ENDDATE,SALARY)VALUES (2,'Junior Accountant','1967-07-06','1971-06

25、-07',8000);INSERT INTO JOBHISTORY(EMPNO,POSITION,STARDATE,ENDDATE,SALARY)VALUES (3,'Accountant','1981-08-05',NULL,16000);INSERT INTO JOBHISTORY(EMPNO,POSITION,STARDATE,ENDDATE,SALARY)VALUES (3,'Junior Accountant','1981-09-04','1984-08-05',8000);INSERT INTO

26、 JOBHISTORY(EMPNO,POSITION,STARDATE,ENDDATE,SALARY)VALUES (4,'Accountant','1989-10-05',NULL,16000);INSERT INTO JOBHISTORY(EMPNO,POSITION,STARDATE,ENDDATE,SALARY)VALUES (5,'Accountant','1980-11-02',NULL,16000);INSERT INTO JOBHISTORY(EMPNO,POSITION,STARDATE,ENDDATE,SALARY)VALUES (5,'Junior Accountant'