数据库实验三

1、西南石油大学实验报告课程名称：数据库原理插入你的照片实验项目名称：实验3 sql数据定义语言 专业年级班级、姓名、学号：电子邮件地址：实验所用机器名：实验时间地点：2015.12.11 明理楼实验指导教师：孙瑜成绩批改人批改日期注意：在粘贴截图时请保留窗口完整标题，但只需保留关键界面，多余的空白界面请删除。一、实验课时：4二、实验目的(1) 掌握使用t-sql语句创建、删除数据库的方法。(2) 掌握使用t-sql语句创建、修改、删除表的方法。(3) 掌握使用t-sql语句创建、删除数据库完整性约束条件的方法。(4) 掌握使用t-sql语句对表添加、修改、删除数据的方法。(5) 掌握使用t-sq

2、l语句创建、修改、删除、查询视图的方法。三、实验要求(1) 使用sql server 2008查询分析器。(2) 严格依照操作步骤进行。(3) 在本地服务器中创建和管理数据库。四、实验环境(1) pc机。(2) sql server 2008。五、实验内容及步骤（请特别注意实验步骤:第6项的第1小项，即“插入数据”操作必须在第4项以前执行）    1使用transact-sql语句创建jobs数据库，数据库名格式为jobs_sunyu(即jobs_你的中文名字拼音)create database jobs_dengzhipeng;2使用transact-sql语句

3、创建jobs数据库包含的所有表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语句创建jobs数据库包含表的主键、外键约束条件创建每个表的主键：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);创建关系：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);具体关系：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数据库 5 使用transact-sql语句在jobs数据库里创建视图(注意：在以下各个小题中，后续题目可以利用前面题目创建的视图)(1) 创建一个名为“firstview”的视图，列出不重复的所有选修了课程的empno (插入定义该视图的sql窗口)create view firstview(empno)asselect distinct empnofrom empcourse;(插入查询该视图的sql窗口及

10、结果)(2) 创建一个名为“secondview”的视图，列出所有empno小于5的员工信息(插入定义该视图的sql窗口)create view secondview(empno,supname,forenames,dob,address,telno,depno)asselect *from employeewhere empno < 5;(插入查询该视图的sql窗口及结果)(3) 创建一个名为“thirdview”的视图，列出每个empno及其相应的选修课程数(插入定义该视图的sql窗口)create view thirdview(empno,coursenum)asselect el

11、.empno,count(*)from employee el joinempcourse ec on el.empno = ec.empnogroup by el.empno;(插入查询该视图的sql窗口及结果)(4) 创建一个名为“fourthview”的视图，列出每个empno及其已经或正在从事的工作数 (插入定义该视图的sql窗口)create view fourthview(empno,jobnum)asselect el.empno ,count(*) from employee eljoin jobhistory jo on el.empno = jo.empnogroup by

12、 el.empno;(插入查询该视图的sql窗口及结果)select * from fourthview;(5) 创建一个合并第3和第4小题视图的select语句，以查询每个empno对应的工作数和课程数。你不需要重复创建视图，只需要利用前面两个小题中已经创建好的视图。如果某员工号对应的课程数为0，则在查询结果中应显示为null。提示：用外连接(插入该查询的sql窗口及结果)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语句对表添加、修改、删除数据(1) 插入数据按照activesql_jobsdb.rar文件里的数据库状态图插入所有表的数据(如果试图插入的数据将会违反第3步创建的约束条件，则可跳过该行数据的输入)。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);当录制完department以后，再将null设置为相应的depno值。update employeeset depno = 1where depno is null;最后的结果：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'