数据库专业实验三.doc
,.西南石油大学实验报告课程名称：数据库原理插入你的照片实验项目名称：实验3 SQL数据定义语言 专业年级班级、姓名、学号：电子邮件地址：实验所用机器名：实验时间地点：2015.12.11 明理楼实验指导教师：孙瑜成绩批改人批改日期注意：在粘贴截图时请保留窗口完整标题，但只需保留关键界面，多余的空白界面请删除。一、实验课时：4二、实验目的(1) 掌握使用T-SQL语句创建、删除数据库的方法。(2) 掌握使用T-SQL语句创建、修改、删除表的方法。(3) 掌握使用T-SQL语句创建、删除数据库完整性约束条件的方法。(4) 掌握使用T-SQL语句对表添加、修改、删除数据的方法。(5) 掌握使用T-SQL语句创建、修改、删除、查询视图的方法。三、实验要求(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语句创建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(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 SMALLINT 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,STARDATE);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)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_DEPARTMENTFOREIGN 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;(所有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窗口及结果)(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.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 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 FOURTHVIEW 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,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 ALTER 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,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 Place,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,TELNO,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 Roberts 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,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(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,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-10,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,1976-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-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 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,1978-12-01,1980-11-02,8000);INSERT INTO JOBHISTORY(EMPNO,POSITION,STARDATE,ENDDATE,SALARY)VALUES (6,Accountant,1980-01-12,NULL,16000);select * from JOBHISTORY;(所有JOBS中表的数据图。注意：在上图中，一次查询可以完成多个元组数据的插入。)(2) 修改数据将所有1960年以后出生员工的部门编号修改为3。UPDATE EMPLOYEESET DEPNO = 3WHERE DOB > 1960-01-01;SELECT * FROM EMPLOYEE;(分别附上修改以前和修改之后的数据图)(3) 删除数据删除所有员工以前的工作历史。修改以前：修改以后：DELETE FROM JOBHISTORYWHERE ENDDATE IS NOT NULL;SELECT * FROM JOBHISTORY;(分别附上删除以前和删除之后的数据图)六、收获，体会及问题(请详细书写，写得越详细、越个性化、越真实越好，否则我不知道你做这个实验的心路历程，也就无法充分地判断你是否是独立完成的这个实验、你是否在做这个实验时进行了认真仔细地思考、通过这个实验你是否在实践能力上得到了提高)通过对sql的DDL的学习让我更加明白如何用sql语言来实现一个数据库的创建，给创建的数据库进行添加数据，更新数据，删除数据。在添加数据以前要先添加关系，避免自己录入的是垃圾数据。在录入数据时，可能因为刚才录入了关系而不准添加。可先将其中一个参照关系的录入值为NULL，等到把主键录入完了，再把外键更新即可。视图的创建实质是一张虚表，当关闭sql server时，会自动消除。他实际是处在用户层。不过自己虽然会做题，但对于一些语句还不是很熟悉，需要对照着PPT，在期末以前自己还要加深理解，加强记忆。
