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

1、西南石油大學(xué)實(shí)驗(yàn)報(bào)告課程名稱(chēng)：數(shù)據(jù)庫(kù)原理插入你的照片實(shí)驗(yàn)項(xiàng)目名稱(chēng)：實(shí)驗(yàn)3 sql數(shù)據(jù)定義語(yǔ)言 專(zhuān)業(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)建、修改、刪除、查詢(xún)視圖的方法。三、實(shí)驗(yàn)要求(1) 使用sql server 2008查詢(xún)分析器。(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;(插入查詢(xún)?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;(插入查詢(xún)?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;(插入查詢(xún)?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;(插入查詢(xún)?cè)撘晥D的sql窗口及結(jié)果)select * from fourthview;(5) 創(chuàng)建一個(gè)合并第3和第4小題視圖的select語(yǔ)句，以查詢(xún)每個(gè)empno對(duì)應(yīng)的工作數(shù)和課程數(shù)。你不需要重復(fù)創(chuàng)建視圖，只需要利用前面兩個(gè)小題中已經(jīng)創(chuàng)建好的視圖。如果某員工號(hào)對(duì)應(yīng)的課程數(shù)為0，則在查詢(xún)結(jié)果中應(yīng)顯示為null。提示：用外連接(插入該查詢(xún)的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'