《数据库原理》上机实验指导书.doc
《《数据库原理》上机实验指导书.doc》由会员分享，可在线阅读，更多相关《《数据库原理》上机实验指导书.doc（34页珍藏版）》请在冰点文库上搜索。
《数据库原理》上机实验指导书
专业：
自动化、测控、信控
指导老师：
杨彪
昆明理工大学信息工程与自动化学院
2016年9月
一、实验目的与要求：
l熟练使用SQL定义子语言、操纵子语言命令语句
l掌握关系模型上的完整性约束机制
l掌握一定的数据库管理技术
l能完成简单的数据库应用开发
二、实验内容及学时安排（总学时：
8）
（一）数据定义子语言实验（2学时）
实验1：
利用SQL语句创建Employee数据库
CREATEDATABASEEmployee
实验2：
利用SQL语句在Employee数据库中创建人员表person、月薪表salary及部门表dept。
要求：
按表1、表2、表3中的字段说明创建
表1person表结构
字段名
数据类型
字段长度
允许空否
字段说明及备注
备注
P_no
Char
6
NotNull
工号，主键
P_name
Varchar
10
NotNull
姓名
Sex
Char
2
NotNull
性别
Birthdate
Datetime
8
Null
出生日期
Prof
Varchar
10
Null
职称
Deptno
Char
4
NotNull
部门代码
实验12以后作为外键（参照dept表）
表2salary表结构
字段名
数据类型
字段长度
允许空否
字段说明及备注
P_no
Char
6
NotNull
工号，主键
实验12以后作为外键（参照person表）
Base
Dec
5
Null
基本工资
Bonus
Dec
5
Null
奖金，要求>50
Fact
Dec
5
Null
实发工资=基本工资+奖金
Month
Int
2
NotNull
月份
表3dept表结构
字段名
数据类型
字段长度
允许空否
字段说明
Deptno
Char
4
NotNull
部门代码，主键，
Dname
Varchar
10
NotNull
部门名称
CREATETABLEdept
（ DeptnoCHAR（4）NOTNULLPRIMARYKEY,
DnameVARCHAR（10）NOTNULL
）;
CREATETABLEperson
（P_noCHAR（6）NOTNULLPRIMARYKEY,
P_nameVARCHAR（10）NOTNULL,
SexCHAR
（2）NOTNULL,
BirthdayDatetime,
ProfVARCHAR（10）,
DeptnoCHAR（4）NOTNULL
）;
CREATETABLEsalary
（P_noCHAR（6）NOTNULLPRIMARYKEY,
BaseDec（5）,
BonusDec（5）CHECK（Bonus>50）,
FactASBase+Bonus,
Monint,
）;
（二）数据操纵子语言实验（4学时）
实验3：
利用SQL语句向表person、salary和dept中插入数据。
要求：
按表4、表5、表6中的数据插入。
表4表person中的数据
P_no
P_name
Sex
BirthDate
Prof
Deptno
000001
王云
男
1973-4-7
中级
0001
000002
谢志文
男
1975-2-14
中级
0001
000003
李浩然
男
1970-8-25
高级
0002
000004
廖小玲
女
1979-8-6
初级
0002
000005
梁玉琼
女
1970-8-25
中级
0003
000006
罗向东
男
1979-5-11
初级
0003
000007
肖家庆
男
1963-7-14
高级
0003
答案：
INSERTINTOperson（P_no,P_name,Sex,BirthDay,Prof,Deptno）
VALUES（'000001','王云','男','1973-4-7','中级','0001'）;
INSERTINTOperson
VALUES（'000002','谢志文','男','1975-2-17','中级','0001'）;
INSERTINTOperson（P_no,P_name,Sex,BirthDay,Prof,Deptno）
VALUES（'000003','李浩然','男','1970-8-25','高级','0002'）;
INSERTINTOperson（P_no,P_name,Sex,BirthDay,Prof,Deptno）
VALUES（'000004','廖小玲','女','1970-8-25','初级','0002'）;
INSERTINTOperson（P_no,P_name,Sex,BirthDay,Prof,Deptno）
VALUES（'000005','梁玉琼','女','1970-5-11','中级','0003'）;
INSERTINTOperson（P_no,P_name,Sex,BirthDay,Prof,Deptno）
VALUES（'000006','罗想动','男','1979-5-11','初级','0003'）;
INSERTINTOperson（P_no,P_name,Sex,BirthDay,Prof,Deptno）
VALUES（'000007','肖家庆','男','1963-7-14','高级','0003'）;
表5表salary中的数据
P_no
Base
Bonus
Fact
S_month
000001
2100
300
1
000002
1800
300
1
000003
2800
280
1
000004
2500
250
1
000005
2300
275
1
000006
1750
130
1
000007
2400
210
1
答案：
INSERTINTOsalary（P_no,Base,Bonus,Mon）
VALUES（'000001',2100,300,1）;
INSERTINTOsalary（P_no,Base,Bonus,Mon）
VALUES（'000002',1800,300,1）;
INSERTINTOsalary（P_no,Base,Bonus,Mon）
VALUES（'000003',2800,280,1）;
INSERTINTOsalary（P_no,Base,Bonus,Mon）
VALUES（'000004',2500,250,1）;
INSERTINTOsalary（P_no,Base,Bonus,Mon）
VALUES（'000005',2300,275,1）;
INSERTINTOsalary（P_no,Base,Bonus,Mon）
VALUES（'000006',1750,130,1）;
INSERTINTOsalary（P_no,Base,Bonus,Mon）
VALUES（'000007',2400,210,1）;
表6表dept中的数据
Deptno
Dname
0001
人事部
0002
财务部
0003
市场部
答案：
INSERTINTOdept（Deptno,Dname）
VALUES（'001','人事部'）;
INSERTINTOdept
VALUES（'002','财务部'）;
INSERTINTOdept
VALUES（'003','市场部'）;
实验4：
（1）利用SQL语句修改表中的数据。
要求：
将salary表中工号为000006的员工工资增加为1800元，奖金增加为160元。
UPDATEsalary
SETBase=1800,Bonus=160
WHEREP_no='000006';
（2）利用SQL语句删除表中的数据。
要求：
删除 person表中工号为000007的员工数据。
DELETEFROMperson
WHEREP_no='000007';
（3）利用SQL语句查询person表中的所有数据。
SELECT*
FROMperson
实验5：
（1）更新视图
要求：
将员工视图PersonView中姓名为王云的员工职称改为高职
CREATEVIEWPView
ASSELECT*FROMperson
UPDATEPView
SETprof='高职'
WHEREP_name='王云'
（2）向视图中插入数据
要求：
向视图PersonView中插入一行数据（‘000008’，‘刘美萍’，‘中级’，‘0002’）
INSERTINTOPView（P_no,P_name,prof,Deptno）
VALUES（'000008','刘美萍','中级','0002'）;
（3）删除视图
要求：
将视图PersonView删除
DROPVIEWPView;
实验6：
条件查询
要求：
（1）查询person表中所有不重复的职称。
SELECTDISTINCTprof
FROMPerson;
（2）查询person表中职称为中级的所有员工数据。
SELECT*
FROMperson
WHEREProf='中级';
（3）查询person表中具有高级职称的男员工信息。
SELECT*
FROMperson
WHEREProf='高级'ANDSex='男';
（4）查询person表中姓名为王云、谢志文、罗向东的员工数据。
SELECT*
FROMperson
WHEREP_name='王云'ORP_name='谢志文'ORP_name='罗想动';
或者
SELECT*
FROMperson
WHEREP_nameIN（'王云','谢志文','罗想动'）
实验7：
使用ORDERBY排序
要求：
利用SQL语句将工号在000003和000006之间的员工的月收入按实发工资升序排序。
SELECT*
FROMSalary
WHEREP_noBETWEEN'000003'AND'000006'
ORDERBYFactASC;
实验8：
利用SQL语句查询各部门的实发工资总数。
SELECTdept.Dname,sum（Fact）AS"部门实发工资总数"
FROMdept,salary,person
WHEREperson.P_no=salary.P_noANDperson.Deptno=dept.Deptno
GROUPBYdept.Dname;
实验9：
利用SQL语句查询人事部所有员工信息。
SELECT*
FROMPerson
WHEREDeptno='0001'
实验10：
表的内连接查询：
要求：
利用SQL语句查询person表中职称为中级的员工信息。
SELECT*
FROMPerson
WHEREprof='中级';
实验11：
表的外连接查询：
要求：
利用SQL语句查询每个员工1月份的工资和奖金
SELECTSalary.P_no,Salary.Base,Salary.Bonus
FROMPersonJOINSalary
ONPerson.P_no=Salary.P_no;
实验12：
子查询：
要求：
利用SQL语句查询比工号为000005的员工实发工资高的所有员工信息。
SELECT*
FROMPerson,Salary
WHEREFact>（
SELECTFact
FROMSalary
WHEREP_no='000005'）;
（三）数据完整性实验（1学时）
实验12：
定义外键约束
要求：
（1）创建表时将person表的deptno列定义为外键，并参考dept表的列deptno。
CREATETABLEperson
（
P_noCHAR（6） NOTNULLPRIMARYKEY,
P_nameVARCHAR（10） NOTNULL,
SexCHAR
（2） NOTNULL,
BirthdayDatetime,
ProfVARCHAR（10）,
DeptnoCHAR（4） NOTNULL,
FOREIGNKEY（Deptno）REFERENCESdept（Deptno）
）;
ALTERTABLEperson
ADDFOREIGNKEY（Deptno）REFERENCESdept（Deptno）
（2）将salary表中的P_no设为外键，并使其参照person表中的列P_no。
CREATETABLEsalary
（P_noCHAR（6）NOTNULLPRIMARYKEY,
BaseDec（5）,
BonusDec（5）CHECK（Bonus>50）,
FactASBase+Bonus,
Monint,
FOREIGNKEY（P_no）REFERENCESperson（P_no）
）;
ALTERTABLEsalary
ADDFOREIGNKEY（P_no）REFERENCESperson（P_no）
实验13：
测试对主表进行插入、更新及删除操作时的影响。
（请写明原因）
要求：
（1）向表dept中插入一行数据（‘0004’，‘研发部’），测试是否影响从表。
INSERTINTOdept（Deptno,Dname）VALUES（'0004','研发部'）;
不影响
（2）将表dept中的部门号0003改为0006，测试是否影响从表。
UPDATEdeptSETDeptno='0006'WHEREDeptno='0003';
--影响:
UPDATE语句与REFERENCE约束"FK__person__Deptno__239E4DCF"冲突。
该冲突发生于数据库"Employee"，表"dbo.person",column'Deptno'。
--语句已终止。
（3）删除表dept中部门号为0001的员工的数据，测试是否影响从表。
DELETEFROMdeptWHEREDeptno='0001';
--影响:
DELETE语句与REFERENCE约束"FK__person__Deptno__239E4DCF"冲突。
该冲突发生于数据库"Employee"，表"dbo.person",column'Deptno'。
--语句已终止。
实验14：
测试对从表进行插入、更新及删除操作时的影响。
（请写明原因）
要求：
（1）向表person中插入一行数据（‘000012’，‘宋全礼’，‘男’、‘1980-7-17’，‘初级’，‘0005’），测试是否违背参照完整性。
INSERTINTOPersonVALUES（'000012','宋全礼','男','1980-7-17','初级','0005'）;
--影响:
INSERT语句与FOREIGNKEY约束"FK__person__Deptno__239E4DCF"冲突。
该冲突发生于数据库"Employee"，表"dbo.dept",column'Deptno'。
（2）删除表中工号为000005的员工数据，测试是否违背参照完整性。
DELETEFROMpersonWHEREP_no='000005';
--不影响
（四）SQLServer安全设置实验（1学时）
实验15：
创建登录账号
（1）创建Windows登录账号
要求：
基于Windows组成员或用户账号创建登录账号Market\000005
（2）建SQLServer登录账号
要求：
基于SQLServer创建新的登录账号000005
（3）删除登录账号
要求：
删除000005及Market\000005登录账号
实验16：
数据库用户设置
（1）基于Windows登录的设置
要求：
授权Market域的000005用户访问数据库Employee
（2）基于SQLServer登录的设置
要求：
授权000005登录账号以000005用户身份访问数据库Employee
（4）特殊的数据库用户账号guest的使用
要求：
将guest用户账号添加到数据库Employee中
（5）删除数据库用户账号
要求：
从数据库Employee中删除000005用户
三、实验报告撰写要求：
1、制作实验报告封面（见附录）。
2、内容顺序包括：
一、实验目的与要求：
二、实验内容：
（1）实验1至实验14：
要标明是第几个实验、实验内容及实验要求，然后把程序代码及运行结果写出，应写成：
实验1：
（内容、要求）
代码如下：
createtableAAA（……）
运行结果为：
（运行结果）.
……….
（2）设计的“Employee数据库系统”要求把设计思路及相应的界面打印出来，并作说明。
三、总结与体会
附录：
《数据库原理》上机实验报告封面
《数据库原理》上机实验报告
学号：
姓名：
班级：
指导教师：
昆明理工大学信息工程与自动化学院
2006年11月
33
升级会员 