数据库实验三.docx
《数据库实验三.docx》由会员分享，可在线阅读，更多相关《数据库实验三.docx（23页珍藏版）》请在冰豆网上搜索。
数据库实验三
西南石油大学实验报告
课程名称：
数据库原理
插入你的照片
实验项目名称：
实验3SQL数据定义语言
专业年级班级、姓名、学号：
电子邮件地址：
实验所用机器名：
实验时间地点：
明理楼
实验指导教师：
孙瑜
成绩
批改人
批改日期
注意：
在粘贴截图时请保留窗口完整标题，但只需保留关键界面，多余的空白界面请删除。
一、实验课时：
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
SELECT,COUNT（*）
FROMEMPLOYEEELJOIN
EMPCOURSEECON=
GROUPBY;
（插入查询该视图的SQL窗口及结果）
（4）创建一个名为“fourthview”的视图，列出每个empno及其已经或正在从事的工作数
（插入定义该视图的SQL窗口）
CREATEVIEWfourthview（EMPNO,JOBNUM）
AS
SELECT,COUNT（*）FROM
EMPLOYEEEL
JOINJOBHISTORYJOON=
GROUPBY;
（插入查询该视图的SQL窗口及结果）
SELECT*FROMFOURTHVIEW;
（5）创建一个合并第3和第4小题视图的SELECT语句，以查询每个empno对应的工作数和课程数。
你不需要重复创建视图，只需要利用前面两个小题中已经创建好的视图。
如果某员工号对应的课程数为0，则在查询结果中应显示为NULL。
提示：
用外连接
（插入该查询的SQL窗口及结果）
SELECT,,FROMEMPLOYEEEL
LEFTJOINTHIRDVIEWTON
=
JOINFOURTHVIEWFON
=;
6．使用Transact-SQL语句对表添加、修改、删除数据
（1）插入数据
按照文件里的数据库状态图插入所有表的数据（如果试图插入的数据将会违反第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',
'88',NULL）;
INSERTINTOEMPLOYEE（EMPNO,SUPNAME,
FORENAMES,DOB,ADDRESS,TELNO,DEPNO）
VALUES（2,'Smith','Robert',
'1947-02-07','18MarshStreet,Tollcross,Edinburgh',
'02',NULL）;
INSERTINTOEMPLOYEE（EMPNO,SUPNAME,
FORENAMES,DOB,ADDRESS,TELNO,DEPNO）
VALUES（3,'White','Allan',
'1961-05-05','6RemotePlace,NorthBerwick',
'22',NULL）;
INSERTINTOEMPLOYEE（EMPNO,SUPNAME,
FORENAMES,DOB,ADDRESS,TELNO,DEPNO）
VALUES（4,'Reid','Gordon',
'1963-08-10','9NobleRoad,Penicuik',
'13',NULL）;
INSERTINTOEMPLOYEE（EMPNO,SUPNAME,
FORENAMES,DOB,ADDRESS,TELNO,DEPNO）
VALUES（5,'MacCallan','Claire',
'1958-09-18','25CrisisAvenue,Leith,Edinburgh',
'06',NULL）;
INSERTINTOEMPLOYEE（EMPNO,SUPNAME,
FORENAMES,DOB,ADDRESS,TELNO,DEPNO）
VALUES（6,'Murphy','BrianCharles',
'1954-06-30','9RobertsStreet,Biggar',
'47',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，在期末以前自己还要加深理解，加强记忆。
精心搜集整理，只为你的需要
升级会员 