数据库原理上机实验-代码及截图

《数据库原理》上机实验报告
2017年11月
一、实验目的与要求：
●熟练使用SQL定义子语言、操纵子语言命令语句
●掌握关系模型上的完整性约束机制
●掌握一定的数据库管理技术
●能完成简单的数据库应用开发
二、实验内容
1、实验一到实验十七
（一）数据定义子语言实验（2学时）
实验1：利用SQL语句创建Employee数据库
代码如下：
create database Employee;
运行结果：
实验2：利用SQL语句在Employee数据库中创建人员表person、月薪表salary 及部门表dept, 暂不定义外键约束。

要求：按表1、表达、表3中的字段说明创建
表1 person表结构
字段名数据类型字段长度允许空否字段说明
表2 salary表结构
表3 dept表结构
代码如下：
create table person(
P_no char(6) not null primary key, P_name varchar(10) not null,
Sex char(2) not null,
Birthdate datetime null,
Prof varchar(10) null,
Deptno char(4) not null
);
create table salary(
P_no char(6) not null primary key, Base dec(5) null,
Bonus dec(5) null,
Fact dec(5) null,
Month int not null
);
create table dept(
Deptno char(4) not null primary key, Dname varchar(10) not null
);
运行结果：
（二）数据操纵子语言实验（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
代码如下：
insert into person (P_no,P_name,Sex,Birthdate,Prof,Deptno) values ('000001','王云','男','1973-4-7','中级','0001')
insert into person (P_no,P_name,Sex,Birthdate,Prof,Deptno) values ('000002','谢志文','男','1975-2-14','中级','0001')
insert into person (P_no,P_name,Sex,Birthdate,Prof,Deptno) values ('000003','李浩然','男','1970-8-25','高级','0002')
insert into person (P_no,P_name,Sex,Birthdate,Prof,Deptno) values ('000004','廖小玲','女','1979-8-6','初级','0002')
insert into person (P_no,P_name,Sex,Birthdate,Prof,Deptno) values ('000005','梁玉琼','女','1970-8-25','中级','0003')
insert into person (P_no,P_name,Sex,Birthdate,Prof,Deptno) values ('000006','罗向东','男','1979-5-11','初级','0003')
insert into person (P_no,P_name,Sex,Birthdate,Prof,Deptno) values ('000007','尚家庆','男','1963-7-14','高级','0003')
运行结果：
表5 表salary中的数据
P_no Base Bonus Fact S_month 00000121003001 00000218003001 00000328002801 00000425002501 00000523002751 00000617501301 00000724002101
代码如下：
insert into salary (P_no,Base,Bonus,Fact,Month) values
('000001',2100,300,2100+300,1)
insert into salary (P_no,Base,Bonus,Fact,Month) values
('000002',1800,300,1800+300,1)
insert into salary (P_no,Base,Bonus,Fact,Month) values
('000003',2800,280,2800+280,1)
insert into salary (P_no,Base,Bonus,Fact,Month) values
('000004',2500,250,2500+500,1)
insert into salary (P_no,Base,Bonus,Fact,Month) values
('000005',2300,275,2300+275,1)
insert into salary (P_no,Base,Bonus,Fact,Month) values
('000006',1750,130,1750+130,1)
insert into salary (P_no,Base,Bonus,Fact,Month) values
('000007',2400,210,2400+210,1)
运行结果：
表6 表dept中的数据
Deptno Dname
0001人事部
0002财务部
0003市场部代码如下：
insert into dept (Deptno,Dname) values ('0001','人事部')
insert into dept (Deptno,Dname) values ('0002','财务部')
insert into dept (Deptno,Dname) values ('0003','市场部')
运行结果：
实验4：（1）利用SQL语句修改表中的数据。

要求：将salary表中工号为000006的员工工资增加为1800元，奖金增加为160元。

代码如下：
update salary
set Base=1800,Bonus=160,Fact=1800+160
where P_no='000006'
运行结果：
（2）利用SQL语句删除表中的数据。

要求：删除salary表中工号为000007的员工数据。

代码如下：
delete
from salary
where P_no='000007'
（3）利用SQL语句查询person表中的所有数据。

代码如下：
select *
from person
运行结果：
实验5：（1）创建视图
要求：创建员工视图PersonView，包含员工的所有信息，并调
用视图
代码如下：
create view PersonView as
select
person.P_no,P_name,Sex,Birthdate,Prof,person.Deptno,Base,Bonus,Fact, Month,Dname
from person,salary,dept
where person.Deptno=dept.Deptno and salary.P_no=person.P_no
select * from PersonView 运行结果：
（2）删除视图
要求：将视图PersonView删除
代码如下：
drop view PersonView
运行结果：
实验6：条件查询
要求：
（1）查询person表中所有不重复的职称。

（2）查询person表中职称为中级的所有员工数据。

（3）查询person表中具有高级职称的男员工信息。

（4）查询person表中姓名为王云、谢志文、罗向东的员工数据。

代码及运行结果如下：
（1）select distinct Prof from person
（2）select * from person where Prof='中级'
（3）select * from person where Prof='高级' and Sex='男'
（4）select * from person where P_name in ('王云','谢志文','罗向东')
实验7：使用ORDER BY排序
要求：利用SQL语句将工号在000003和000006之间的员工的月收入按实发工资升序排序。

代码如下：
select * from salary where P_no between '000003' and '000006' order by Fact asc
运行结果：
实验8：利用SQL语句查询各部门的实发工资总数。

代码如下：
select dept.Dname,sum(Fact) as "部门实发工资总数"
from person,salary,dept
where person.Deptno=dept.Deptno and salary.P_no=person.P_no
group by dept.Dname
运行结果：
实验9：利用SQL语句查询人事部所有员工信息。

代码如下：
select *
from person,salary,dept
where person.Deptno=dept.Deptno and salary.P_no=person.P_no and dept.Dname=’人事部’
运行结果：
实验10：表的内连接查询：
要求：利用SQL语句查询person表中职称为中级的员工信息。

代码如下：
select * from person where prof='中级'
运行结果：
实验11：表的外连接查询：
要求：利用SQL语句查询每个员工1 月份的工资和奖金
代码如下：
select person.P_no,person.P_name,salary.Base,salary.Bonus
from person left outer join salary on (person.P_no=salary.P_no)运行结果：
实验12：子查询：
要求：利用SQL语句查询比工号为000005的员工实发工资高的所有员工信息。

代码如下：
select * from person,salary
where Fact>(select Fact
from salary
where P_no='000005') and person.P_no=salary.P_no
（三）数据完整性实验（1学时）
实验13：定义外键约束
要求：
（1）创建表时将person表的deptno列定义为外键，并参考dept表的列deptno。

代码如下：
alter table person
add constraint personkey foreign key (Deptno)
references dept(Deptno)
（2）将salary表中的P_no设为外键，并使其参照person表中的列
P_no。

代码如下：
alter table salary
add constraint salarykey foreign key (P_no)
references person(P_no)
运行结果：
实验14：测试对主表进行插入、更新及删除操作时的影响。

（请写明原因）要求：
（1）向表dept中插入一行数据（‘0004’，‘研发部’），测试是否影响从表。

代码如下：
insert into dept(Deptno,Dname) values ('0004','研发部')
运行结果：
不影响从表，插入的数据与外键约束的数据没有联系。

（2）将表dept中的部门号0003改为0006，测试是否影响从表。

代码及运行结果如下：
update dept set Deptno='0006' where Deptno='0003'
分析：影响从表，’0006’为原本不存在的数据，修改时受外键约束无法实现。

（3）删除表dept中部门号为0001的员工的数据，测试是否影响从表。

代码及运行结果如下：
delete from dept where Deptno='0001'
分析：影响从表，删除表dept中部门号为0001的员工的数据，修改时受外键约束无法实现。

实验15：测试对从表进行插入、更新及删除操作时的影响。

（请写明原因）要求：
（1）向表person中插入一行数据（‘000012’，‘宋全礼’，‘男’、‘1980-7-17’，‘初级’，‘0005’），测试是否违背参照完整性。

代码及运行结果如下：
insert into person(P_no,P_name,Sex,Birthdate,Prof,Deptno)
values ('000012','宋全礼','男','1980-7-17','初级','0005');
分析：违背参照完整性，更新数据的操作受到约束无法实现。

（2）删除表中工号为000005的员工数据，测试是否违背参照完整性。

代码及运行结果如下：delete from person where P_no='000005'
分析：违背参照完整性，删除数据的操作受到约束无法实现。

（四） SQL Server安全设置实验（1学时）
实验 16：创建登录账号
（1）创建SQL Server登录账号
要求：基于SQL Server创建登录账号kmust、student、
testdelete
sp_addlogin @loginame=kmust,
@passwd='123456',
@defdb=N'Employee',
@deflanguage=N'Simplified Chinese',
@sid=null,
@encryptopt=null
sp_addlogin @loginame=student,
@passwd='123456',
@defdb=N'Employee',
@deflanguage=N'Simplified Chinese',
@sid=null,
@encryptopt=null
sp_addlogin @loginame=testdelete,
@passwd='123456',
@defdb=N'Employee',
@deflanguage=N'Simplified Chinese',
@sid=null,
@encryptopt=null
（2）删除登录账号
要求：删除testdelete登录账号
实验 17：数据库用户设置
基于SQL Server登录的设置
要求：授权kmust、student登录账号以db_owner用户身份访问数
据库Employee
exec sp_adduser'kmust'
exec sp_addrolemember'db_owner','kmust'
exec sp_adduser'student'
exec sp_addrolemember'db_owner','student'
2、Employee数据库系统
1、创建数据库Employee。

2、在数据库Employee中创建表（可对表进行增删查改等操作）。

3、在数据库中创建视图。

4、创建登录账号，授权db_owner身份访问数据库Employee。

三、总结与体会
通过数据库上机实验，学会了使用SQL语句在SQL SERVE 中创建数据库；在数据库中建表、创建视图；对表进行增删查改、建立主键、外键约束；创建SQL SERVE账号并授权用户角色实现对数据库的访问。

数据库在生活中比较普遍，学会基本的语句操作会对以后的学习产生很大帮助，但数据库学问较深，还未接触到深层次的学习，以后有机会会认真努力学习相关知识。