数据库实验三Word下载.docx

数据库实验三Word下载.docx

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

（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（

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）;

创建关系：

ADDCONSTRAINTFK_JOBHISTORY_EMPLOYEE

FOREIGNKEY（EMPNO）

REFERENCESEMPLOYEE（EMPNO）;

ADDCONSTRAINTFK_EMPCOURSE_EMPLOYEE

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）

SELECT*

FROMEMPLOYEE

WHEREEMPNO<

5;

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

CREATEVIEWthirdview（EMPNO,COURSENUM）

SELECTEL.EMPNO,COUNT（*）

FROMEMPLOYEEELJOIN

EMPCOURSEECONEL.EMPNO=EC.EMPNO

GROUPBYEL.EMPNO;

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

CREATEVIEWfourthview（EMPNO,JOBNUM）

SELECTEL.EMPNO,COUNT（*）FROM

EMPLOYEEEL

JOINJOBHISTORYJOONEL.EMPNO=JO.EMPNO

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'

VALUES（2,'

FurtherAccounting'

1989-01-25'

VALUES（3,'

IssuesInAdministration'

1988-09-27'

VALUES（4,'

MoreAdministration'

1988-10-16'

select*fromcourse;

ALTERTABLEEMPLOYEEALTERCOLUMNDEPNOSMALLINTNULL;

INSERTINTOEMPLOYEE（EMPNO,SUPNAME,

Jones'

ElizabethBarbara'

'

1944-01-05'

26AgnewsTerrace,ShamrockBay'

2123372288'

NULL）;

Smith'

Robert'

1947-02-07'

18MarshStreet,Tollcross,Edinburgh'

0317328972'

White'

Allan'

1961-05-05'

6RemotePlace,NorthBerwick'

1215556622'

Reid'

Gordon'

1963-08-10'

9NobleRoad,Penicuik'

6294246713'

VALUES（5,'

MacCallan'

Claire'

1958-09-18'

25CrisisAvenue,Leith,Edinburgh'

0313374166'

VALUES（6,'

Murphy'

BrianCharles'

1954-06-30'

9RobertsStreet,Biggar'

3312294147'

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

UPDATEEMPLOYEE

SETDEPNO=1

WHEREDEPNOISNULL;

最后的结果：

INSERTINTODEPARTMENT（DEPNO,DNAME,LOCATION,HEAD）

accounts'

floor3'

1）;

administration'

floor2'

softwaredesign'

floor1'

2）;

communications'

floor4'

3）;

select*fromDEPARTMENT;

INSERTINTOEMPCOURSE（EMPNO,COURSENO）

VALUES（1,1）;

VALUES（1,2）;

VALUES（2,1）;

VALUES（2,2）;

select*fromEMPCOURSE;

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

AccountsManager'

1976-01-12'

NULL,30000）;

AssistantAccountsManager'

1972-02-11'

22000）;

Accountant'

1968-03-10'

15000）;

JuniorAccountant'

1964-04-09'

6000）;

1976-05-08'

NULL,25000）;

1971-06-07'

16000）;

1967-07-06'

8000）;

1981-08-05'

NULL,16000）;

1981-09-04'

1984-08-05'

1989-10-05'

1980-11-02'

1978-12-01'

1980-01-12'

select*fromJOBHISTORY;

（所有JOBS中表的数据图。

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

（2）修改数据

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

SETDEPNO=3

WHEREDOB>

'

1960-01-01'

;

SELECT*FROMEMPLOYEE;

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

（3）删除数据

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

修改以前：

修改以后：

DELETEFROMJOBHISTORY

WHEREENDDATEISNOTNULL;

SELECT*FROMJOBHISTORY;

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

六、收获，体会及问题

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

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

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

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

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

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

他实际是处在用户层。

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