数据库原理上机实验代码及截图.docx
《数据库原理上机实验代码及截图.docx》由会员分享，可在线阅读，更多相关《数据库原理上机实验代码及截图.docx（35页珍藏版）》请在冰豆网上搜索。
数据库原理上机实验代码及截图
《数据库原理》上机实验报告
2017年11月
一、实验目的与要求：
●熟练使用SQL定义子语言、操纵子语言命令语句
●掌握关系模型上的完整性约束机制
●掌握一定的数据库管理技术
●能完成简单的数据库应用开发
二、实验容
1、实验一到实验十七
（一）数据定义子语言实验（2学时）
实验1：
利用SQL语句创建Employee数据库
代码如下：
createdatabaseEmployee;
运行结果：
实验2：
利用SQL语句在Employee数据库中创建人员表person、月薪表salary及部门表dept,暂不定义外键约束。
要求：
按表1、表达、表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
部门代码，外键（参照dept表）
表2salary表结构
字段名
数据类型
字段长度
允许空否
字段说明
P_no
Char
6
NotNull
工号，主键，外键（参照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
部门名称
代码如下：
createtableperson（
P_nochar（6）notnullprimarykey,
P_namevarchar（10）notnull,
Sexchar
（2）notnull,
Birthdatedatetimenull,
Profvarchar（10）null,
Deptnochar（4）notnull
）;
createtablesalary（
P_nochar（6）notnullprimarykey,
Basedec（5）null,
Bonusdec（5）null,
Factdec（5）null,
Monthintnotnull
）;
createtabledept（
Deptnochar（4）notnullprimarykey,
Dnamevarchar（10）notnull
）;
运行结果：
（二）数据操纵子语言实验（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
000007
肖家庆
男
1963-7-14
高级
0003
代码如下：
insertintoperson（P_no,P_name,Sex,Birthdate,Prof,Deptno）values（'000001','王云','男','1973-4-7','中级','0001'）
insertintoperson（P_no,P_name,Sex,Birthdate,Prof,Deptno）values（'000002','志文','男','1975-2-14','中级','0001'）
insertintoperson（P_no,P_name,Sex,Birthdate,Prof,Deptno）values（'000003','浩然','男','1970-8-25','高级','0002'）
insertintoperson（P_no,P_name,Sex,Birthdate,Prof,Deptno）values（'000004','廖小玲','女','1979-8-6','初级','0002'）
insertintoperson（P_no,P_name,Sex,Birthdate,Prof,Deptno）values（'000005','梁玉琼','女','1970-8-25','中级','0003'）
insertintoperson（P_no,P_name,Sex,Birthdate,Prof,Deptno）values（'000006','罗向东','男','1979-5-11','初级','0003'）
insertintoperson（P_no,P_name,Sex,Birthdate,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,Fact,Month）values（'000001',2100,300,2100+300,1）
insertintosalary（P_no,Base,Bonus,Fact,Month）values（'000002',1800,300,1800+300,1）
insertintosalary（P_no,Base,Bonus,Fact,Month）values（'000003',2800,280,2800+280,1）
insertintosalary（P_no,Base,Bonus,Fact,Month）values（'000004',2500,250,2500+500,1）
insertintosalary（P_no,Base,Bonus,Fact,Month）values（'000005',2300,275,2300+275,1）
insertintosalary（P_no,Base,Bonus,Fact,Month）values（'000006',1750,130,1750+130,1）
insertintosalary（P_no,Base,Bonus,Fact,Month）values（'000007',2400,210,2400+210,1）
运行结果：
表6表dept中的数据
Deptno
Dname
0001
人事部
0002
财务部
0003
市场部
代码如下：
insertintodept（Deptno,Dname）values（'0001','人事部'）
insertintodept（Deptno,Dname）values（'0002','财务部'）
insertintodept（Deptno,Dname）values（'0003','市场部'）
运行结果：
实验4：
（1）利用SQL语句修改表中的数据。
要求：
将salary表中工号为000006的员工工资增加为1800元，奖金增加为160元。
代码如下：
updatesalary
setBase=1800,Bonus=160,Fact=1800+160
whereP_no='000006'
运行结果：
（2）利用SQL语句删除表中的数据。
要求：
删除 salary表中工号为000007的员工数据。
代码如下：
delete
fromsalary
whereP_no='000007'
（3）利用SQL语句查询person表中的所有数据。
代码如下：
select*
fromperson
运行结果：
实验5：
（1）创建视图
要求：
创建员工视图PersonView，包含员工的所有信息，并调用视图
代码如下：
createviewPersonViewas
selectperson.P_no,P_name,Sex,Birthdate,Prof,person.Deptno,Base,Bonus,Fact,Month,Dname
fromperson,salary,dept
whereperson.Deptno=dept.Deptnoandsalary.P_no=person.P_no
select*fromPersonView
运行结果：
（2）删除视图
要求：
将视图PersonView删除
代码如下：
dropviewPersonView
运行结果：
实验6：
条件查询
要求：
（1）查询person表中所有不重复的职称。
（2）查询person表中职称为中级的所有员工数据。
（3）查询person表中具有高级职称的男员工信息。
（4）查询person表中为王云、志文、罗向东的员工数据。
代码及运行结果如下：
（1）selectdistinctProffromperson
（2）select*frompersonwhereProf='中级'
（3）select*frompersonwhereProf='高级'andSex='男'
（4）select*frompersonwhereP_namein（'王云','志文','罗向东'）
实验7：
使用ORDERBY排序
要求：
利用SQL语句将工号在000003和000006之间的员工的月收入按实发工资升序排序。
代码如下：
select*fromsalarywhereP_nobetween'000003'and'000006'orderbyFactasc
运行结果：
实验8：
利用SQL语句查询各部门的实发工资总数。
代码如下：
selectdept.Dname,sum（Fact）as"部门实发工资总数"
fromperson,salary,dept
whereperson.Deptno=dept.Deptnoandsalary.P_no=person.P_no
groupbydept.Dname
运行结果：
实验9：
利用SQL语句查询人事部所有员工信息。
代码如下：
select*
fromperson,salary,dept
whereperson.Deptno=dept.Deptnoandsalary.P_no=person.P_no
anddept.Dname=’人事部’
运行结果：
实验10：
表的连接查询：
要求：
利用SQL语句查询person表中职称为中级的员工信息。
代码如下：
select*frompersonwhereprof='中级'
运行结果：
实验11：
表的外连接查询：
要求：
利用SQL语句查询每个员工1月份的工资和奖金
代码如下：
selectperson.P_no,person.P_name,salary.Base,salary.Bonus
frompersonleftouterjoinsalaryon（person.P_no=salary.P_no）
运行结果：
实验12：
子查询：
要求：
利用SQL语句查询比工号为000005的员工实发工资高的所有员工信息。
代码如下：
select*fromperson,salary
whereFact>（selectFact
fromsalary
whereP_no='000005'）andperson.P_no=salary.P_no
（三）数据完整性实验（1学时）
实验13：
定义外键约束
要求：
（1）创建表时将person表的deptno列定义为外键，并参考dept表的列deptno。
代码如下：
altertableperson
addconstraintpersonkeyforeignkey（Deptno）
referencesdept（Deptno）
（2）将salary表中的P_no设为外键，并使其参照person表中的列P_no。
代码如下：
altertablesalary
addconstraintsalarykeyforeignkey（P_no）
referencesperson（P_no）
运行结果：
实验14：
测试对主表进行插入、更新及删除操作时的影响。
（请写明原因）
要求：
（1）向表dept中插入一行数据（‘0004’，‘研发部’），测试是否影响从表。
代码如下：
insertintodept（Deptno,Dname）values（'0004','研发部'）
运行结果：
不影响从表，插入的数据与外键约束的数据没有联系。
（2）将表dept中的部门号0003改为0006，测试是否影响从表。
代码及运行结果如下：
updatedeptsetDeptno='0006'whereDeptno='0003'
分析：
影响从表，’0006’为原本不存在的数据，修改时受外键约束无法实现。
（3）删除表dept中部门号为0001的员工的数据，测试是否影响从表。
代码及运行结果如下：
deletefromdeptwhereDeptno='0001'
分析：
影响从表，删除表dept中部门号为0001的员工的数据，修改时受外键约束无法实现。
实验15：
测试对从表进行插入、更新及删除操作时的影响。
（请写明原因）
要求：
（1）向表person中插入一行数据（‘000012’，‘宋全礼’，‘男’、‘1980-7-17’，‘初级’，‘0005’），测试是否违背参照完整性。
代码及运行结果如下：
insertintoperson（P_no,P_name,Sex,Birthdate,Prof,Deptno）
values（'000012','宋全礼','男','1980-7-17','初级','0005'）;
分析：
违背参照完整性，更新数据的操作受到约束无法实现。
（2）删除表中工号为000005的员工数据，测试是否违背参照完整性。
代码及运行结果如下：
deletefrompersonwhereP_no='000005'
分析：
违背参照完整性，删除数据的操作受到约束无法实现。
（四）SQLServer安全设置实验（1学时）
实验16：
创建登录账号
（1）创建SQLServer登录账号
要求：
基于SQLServer创建登录账号kmust、student、testdelete
sp_addloginloginame=kmust,
passwd='123456',
defdb=N'Employee',
deflanguage=N'SimplifiedChinese',
sid=null,
encryptopt=null
sp_addloginloginame=student,
passwd='123456',
defdb=N'Employee',
deflanguage=N'SimplifiedChinese',
sid=null,
encryptopt=null
sp_addloginloginame=testdelete,
passwd='123456',
defdb=N'Employee',
deflanguage=N'SimplifiedChinese',
sid=null,
encryptopt=null
（2）删除登录账号
要求：
删除testdelete登录账号
实验17：
数据库用户设置
基于SQLServer登录的设置
要求：
授权kmust、student登录账号以db_owner用户身份访问数据库Employee
execsp_adduser'kmust'
execsp_addrolemember'db_owner','kmust'
execsp_adduser'student'
execsp_addrolemember'db_owner','student'
2、Employee数据库系统
1、创建数据库Employee。
2、在数据库Employee中创建表（可对表进行增删查改等操作）。
3、在数据库中创建视图。
4、创建登录账号，授权db_owner身份访问数据库Employee。
三、总结与体会
通过数据库上机实验，学会了使用SQL语句在SQLSERVE中创建数据库；在数据库中建表、创建视图；对表进行增删查改、建立主键、外键约束；创建SQLSERVE账号并授权用户角色实现对数据库的访问。
数据库在生活中比较普遍，学会基本的语句操作会对以后的学习产生很大帮助，但数据库学问较深，还未接触到深层次的学习，以后有机会会认真努力学习相关知识。
升级会员 