数据库实验三.docx

数据库实验三.docx

《数据库实验三.docx》由会员分享，可在线阅读，更多相关《数据库实验三.docx（23页珍藏版）》请在冰豆网上搜索。

数据库实验三

西南石油大学实验报告

课程名称：

数据库原理

插入你的照片

实验项目名称：

实验3SQL数据定义语言

专业年级班级、姓名、学号：

电子邮件地址：

实验所用机器名：

实验时间地点：

2015.12.11明理楼

实验指导教师：

孙瑜

成绩

批改人

批改日期

注意：

在粘贴截图时请保留窗口完整标题，但只需保留关键界面，多余的空白界面请删除。

一、实验课时：

4

二、实验目的

（1）掌握使用T-SQL语句创建、删除数据库的方法。

（2）掌握使用T-SQL语句创建、修改、删除表的方法。

（3）掌握使用T-SQL语句创建、删除数据库完整性约束条件的方法。

（4）掌握使用T-SQL语句对表添加、修改、删除数据的方法。

（5）掌握使用T-SQL语句创建、修改、删除、查询视图的方法。

三、实验要求

（1）使用SQLServer2008查询分析器。

（2）严格依照操作步骤进行。

（3）在本地服务器中创建和管理数据库。

四、实验环境

（1）PC机。

（2）SQLServer2008。

五、实验内容及步骤（请特别注意实验步骤:

第6项的第1小项，即“插入数据”操作必须在第4项以前执行）

   1．使用Transact-SQL语句创建JOBS数据库，数据库名格式为JOBS_SunYu（即JOBS_你的中文名字拼音）

CREATEDatabaseJOBS_DengZhiPeng;

2．使用Transact-SQL语句创建JOBS数据库包含的所有表

EMPLOYEE

CREATETABLEEMPLOYEE（

EMPNOSMALLINTNOTNULL,

SUPNAMEVARCHAR（50）NOTNULL,

FORENAMESVARCHAR（50）NOTNULL,

DOBDATENOTNULL,

ADDRESSVARCHAR（50）NOTNULL,

TELNOCHAR（10）NOTNULL,

DEPNOSMALLINTNOTNULL

）;

JOBHISTORY

CREATETABLEJOBHISTORY（

EMPNOSMALLINTNOTNULL,

POSITIONVARCHAR（50）NOTNULL,

STARDATEDATENOTNULL,

ENDDATEDATENULL,

SALARYINTNOTNULL

）;

COURSE

CREATETABLECOURSE（

COURSENOSMALLINTNOTNULL,

CNAMEVARCHAR（50）NOTNULL,

CDATEDATENOTNULL

）;

DEPARTMENT

CREATETABLEDEPARTMENT（

DEPNOSMALLINTNOTNULL,

DNAMEVARCHAR（50）NOTNULL,

LOCATIONVARCHAR（10）NOTNULL,

HEADSMALLINTNOTNULL

）;

EMPCOURSE

CREATETABLEEMPCOURSE（

EMPNOSMALLINTNOTNULL,

COURSENOSMALLINTNOTNULL）;

3．使用Transact-SQL语句创建JOBS数据库包含表的主键、外键约束条件

创建每个表的主键：

ALTERTABLEEMPLOYEE

ADDCONSTRAINTPK_EMPLOYEE

PRIMARYKEY（EMPNO）;

ALTERTABLEJOBHISTORY

ADDCONSTRAINTPK_JOBHISTORY

PRIMARYKEY（EMPNO,POSITION,STARDATE）;

ALTERTABLECOURSE

ADDCONSTRAINTPK_COURSE

PRIMARYKEY（COURSENO）;

ALTERTABLEDEPARTMENT

ADDCONSTRAINTPK_DEPARTMENT

PRIMARYKEY（DEPNO）;

ALTERTABLEEMPCOURSE

ADDCONSTRAINTPK_EMCOURSE

PRIMARYKEY（EMPNO,COURSENO）;

创建关系：

ALTERTABLEJOBHISTORY

ADDCONSTRAINTFK_JOBHISTORY_EMPLOYEE

FOREIGNKEY（EMPNO）

REFERENCESEMPLOYEE（EMPNO）;

ALTERTABLEEMPCOURSE

ADDCONSTRAINTFK_EMPCOURSE_EMPLOYEE

FOREIGNKEY（EMPNO）

REFERENCESEMPLOYEE（EMPNO）;

ALTERTABLEEMPCOURSE

ADDCONSTRAINTFK_EMPCOURSE_COURSE

FOREIGNKEY（COURSENO）

REFERENCESCOURSE（COURSENO）;

ALTERTABLEEMPLOYEE

ADDCONSTRAINTFK_EMPLOYEE_DEPARTMENT

FOREIGNKEY（DEPNO）

REFERENCESDEPARTMENT（DEPNO）;

ALTERTABLEDEPARTMENT

ADDCONSTRAINTFK_DEPARTMENT_EMPLOYEE

FOREIGNKEY（HEAD）

REFERENCESEMPLOYEE（EMPNO）;

具体关系：

EXECUTEsp_helpconstraintDEPARTMENT;

EXECUTEsp_helpconstraintEMPCOURSE;

EXECUTEsp_helpconstraintJOBHISTORY;

EXECUTEsp_helpconstraintEMPLOYEE;

（所有JOBS中表的约束图。

注意：

必须如上图所示清楚完整显示约束的constraint_type、constraint_name、constraint_keys等信息。

）

4．备份JOBS数据库

5．使用Transact-SQL语句在JOBS数据库里创建视图（注意：

在以下各个小题中，后续题目可以利用前面题目创建的视图）

（1）创建一个名为“firstview”的视图，列出不重复的所有选修了课程的empno

（插入定义该视图的SQL窗口）

CREATEVIEWfirstview（EMPNO）

AS

SELECTDISTINCTEMPNO

FROMEMPCOURSE;

（插入查询该视图的SQL窗口及结果）

（2）创建一个名为“secondview”的视图，列出所有empno小于5的员工信息

（插入定义该视图的SQL窗口）

CREATEVIEWsecondview（EMPNO,SUPNAME,

FORENAMES,DOB,ADDRESS,TELNO,DEPNO）

AS

SELECT*

FROMEMPLOYEE

WHEREEMPNO<5;

（插入查询该视图的SQL窗口及结果）

（3）创建一个名为“thirdview”的视图，列出每个empno及其相应的选修课程数

（插入定义该视图的SQL窗口）

CREATEVIEWthirdview（EMPNO,COURSENUM）

AS

SELECTEL.EMPNO,COUNT（*）

FROMEMPLOYEEELJOIN

EMPCOURSEECONEL.EMPNO=EC.EMPNO

GROUPBYEL.EMPNO;

（插入查询该视图的SQL窗口及结果）

（4）创建一个名为“fourthview”的视图，列出每个empno及其已经或正在从事的工作数

（插入定义该视图的SQL窗口）

CREATEVIEWfourthview（EMPNO,JOBNUM）

AS

SELECTEL.EMPNO,COUNT（*）FROM

EMPLOYEEEL

JOINJOBHISTORYJOONEL.EMPNO=JO.EMPNO

GROUPBYEL.EMPNO;

（插入查询该视图的SQL窗口及结果）

SELECT*FROMFOURTHVIEW;

（5）创建一个合并第3和第4小题视图的SELECT语句，以查询每个empno对应的工作数和课程数。

你不需要重复创建视图，只需要利用前面两个小题中已经创建好的视图。

如果某员工号对应的课程数为0，则在查询结果中应显示为NULL。

提示：

用外连接

（插入该查询的SQL窗口及结果）

SELECTEL.EMPNO,T.COURSENUM,F.JOBNUMFROMEMPLOYEEEL

LEFTJOINTHIRDVIEWTON

T.EMPNO=EL.EMPNO

JOINFOURTHVIEWFON

EL.EMPNO=F.EMPNO;

 6．使用Transact-SQL语句对表添加、修改、删除数据

（1）插入数据

按照ActiveSQL_JobsDB.rar文件里的数据库状态图插入所有表的数据（如果试图插入的数据将会违反第3步创建的约束条件，则可跳过该行数据的输入）。

INSERTINTOCOURSE（COURSENO,CNAME,CDATE）

VALUES（1,'BasicAccounting','1989-01-11'）;

INSERTINTOCOURSE（COURSENO,CNAME,CDATE）

VALUES（2,'FurtherAccounting','1989-01-25'）;

INSERTINTOCOURSE（COURSENO,CNAME,CDATE）

VALUES（3,'IssuesInAdministration','1988-09-27'）;

INSERTINTOCOURSE（COURSENO,CNAME,CDATE）

VALUES（4,'MoreAdministration','1988-10-16'）;

select*fromcourse;

ALTERTABLEEMPLOYEEALTERCOLUMNDEPNOSMALLINTNULL;

INSERTINTOEMPLOYEE（EMPNO,SUPNAME,

FORENAMES,DOB,ADDRESS,TELNO,DEPNO）

VALUES（1,'Jones','ElizabethBarbara',

'1944-01-05','26AgnewsTerrace,ShamrockBay',

'2123372288',NULL）;

INSERTINTOEMPLOYEE（EMPNO,SUPNAME,

FORENAMES,DOB,ADDRESS,TELNO,DEPNO）

VALUES（2,'Smith','Robert',

'1947-02-07','18MarshStreet,Tollcross,Edinburgh',

'0317328972',NULL）;

INSERTINTOEMPLOYEE（EMPNO,SUPNAME,

FORENAMES,DOB,ADDRESS,TELNO,DEPNO）

VALUES（3,'White','Allan',

'1961-05-05','6RemotePlace,NorthBerwick',

'1215556622',NULL）;

INSERTINTOEMPLOYEE（EMPNO,SUPNAME,

FORENAMES,DOB,ADDRESS,TELNO,DEPNO）

VALUES（4,'Reid','Gordon',

'1963-08-10','9NobleRoad,Penicuik',

'6294246713',NULL）;

INSERTINTOEMPLOYEE（EMPNO,SUPNAME,

FORENAMES,DOB,ADDRESS,TELNO,DEPNO）

VALUES（5,'MacCallan','Claire',

'1958-09-18','25CrisisAvenue,Leith,Edinburgh',

'0313374166',NULL）;

INSERTINTOEMPLOYEE（EMPNO,SUPNAME,

FORENAMES,DOB,ADDRESS,TELNO,DEPNO）

VALUES（6,'Murphy','BrianCharles',

'1954-06-30','9RobertsStreet,Biggar',

'3312294147',NULL）;

当录制完DEPARTMENT以后，再将NULL设置为相应的DEPNO值。

UPDATEEMPLOYEE

SETDEPNO=1

WHEREDEPNOISNULL;

最后的结果：

INSERTINTODEPARTMENT（DEPNO,DNAME,LOCATION,HEAD）

VALUES（1,'accounts','floor3',1）;

INSERTINTODEPARTMENT（DEPNO,DNAME,LOCATION,HEAD）

VALUES（2,'administration','floor2',1）;

INSERTINTODEPARTMENT（DEPNO,DNAME,LOCATION,HEAD）

VALUES（3,'softwaredesign','floor1',2）;

INSERTINTODEPARTMENT（DEPNO,DNAME,LOCATION,HEAD）

VALUES（4,'communications','floor4',3）;

select*fromDEPARTMENT;

INSERTINTOEMPCOURSE（EMPNO,COURSENO）

VALUES（1,1）;

INSERTINTOEMPCOURSE（EMPNO,COURSENO）

VALUES（1,2）;

INSERTINTOEMPCOURSE（EMPNO,COURSENO）

VALUES（2,1）;

INSERTINTOEMPCOURSE（EMPNO,COURSENO）

VALUES（2,2）;

select*fromEMPCOURSE;

INSERTINTOJOBHISTORY（EMPNO,POSITION,STARDATE,ENDDATE,SALARY）

VALUES（1,'AccountsManager','1976-01-12',NULL,30000）;

INSERTINTOJOBHISTORY（EMPNO,POSITION,STARDATE,ENDDATE,SALARY）

VALUES（1,'AssistantAccountsManager','1972-02-11','1976-01-12',22000）;

INSERTINTOJOBHISTORY（EMPNO,POSITION,STARDATE,ENDDATE,SALARY）

VALUES（1,'Accountant','1968-03-10','1972-02-11',15000）;

INSERTINTOJOBHISTORY（EMPNO,POSITION,STARDATE,ENDDATE,SALARY）

VALUES（1,'JuniorAccountant','1964-04-09','1968-03-10',6000）;

INSERTINTOJOBHISTORY（EMPNO,POSITION,STARDATE,ENDDATE,SALARY）

VALUES（2,'AssistantAccountsManager','1976-05-08',NULL,25000）;

INSERTINTOJOBHISTORY（EMPNO,POSITION,STARDATE,ENDDATE,SALARY）

VALUES（2,'Accountant','1971-06-07','1976-05-08',16000）;

INSERTINTOJOBHISTORY（EMPNO,POSITION,STARDATE,ENDDATE,SALARY）

VALUES（2,'JuniorAccountant','1967-07-06','1971-06-07',8000）;

INSERTINTOJOBHISTORY（EMPNO,POSITION,STARDATE,ENDDATE,SALARY）

VALUES（3,'Accountant','1981-08-05',NULL,16000）;

INSERTINTOJOBHISTORY（EMPNO,POSITION,STARDATE,ENDDATE,SALARY）

VALUES（3,'JuniorAccountant','1981-09-04','1984-08-05',8000）;

INSERTINTOJOBHISTORY（EMPNO,POSITION,STARDATE,ENDDATE,SALARY）

VALUES（4,'Accountant','1989-10-05',NULL,16000）;

INSERTINTOJOBHISTORY（EMPNO,POSITION,STARDATE,ENDDATE,SALARY）

VALUES（5,'Accountant','1980-11-02',NULL,16000）;

INSERTINTOJOBHISTORY（EMPNO,POSITION,STARDATE,ENDDATE,SALARY）

VALUES（5,'JuniorAccountant','1978-12-01','1980-11-02',8000）;

INSERTINTOJOBHISTORY（EMPNO,POSITION,STARDATE,ENDDATE,SALARY）

VALUES（6,'Accountant','1980-01-12',NULL,16000）;

select*fromJOBHISTORY;

（所有JOBS中表的数据图。

注意：

在上图中，一次查询可以完成多个元组数据的插入。

）

（2）修改数据

将所有1960年以后出生员工的部门编号修改为3。

UPDATEEMPLOYEE

SETDEPNO=3

WHEREDOB>'1960-01-01';

SELECT*FROMEMPLOYEE;

（分别附上修改以前和修改之后的数据图）

（3）删除数据

删除所有员工以前的工作历史。

修改以前：

修改以后：

DELETEFROMJOBHISTORY

WHEREENDDATEISNOTNULL;

SELECT*FROMJOBHISTORY;

（分别附上删除以前和删除之后的数据图）

六、收获，体会及问题

（请详细书写，写得越详细、越个性化、越真实越好，否则我不知道你做这个实验的心路历程，也就无法充分地判断你是否是独立完成的这个实验、你是否在做这个实验时进行了认真仔细地思考、通过这个实验你是否在实践能力上得到了提高）

通过对sql的DDL的学习让我更加明白如何用sql语言来实现一个数据库的创建，给创建的数据库进行添加数据，更新数据，删除数据。

在添加数据以前要先添加关系，避免自己录入的是垃圾数据。

在录入数据时，可能因为刚才录入了关系而不准添加。

可先将其中一个参照关系的录入值为NULL，等到把主键录入完了，再把外键更新即可。

视图的创建实质是一张虚表，当关闭sqlserver时，会自动消除。

他实际是处在用户层。

不过自己虽然会做题，但对于一些语句还不是很熟悉，需要对照着PPT，在期末以前自己还要加深理解，加强记忆。