1、数据库原理上机实验报告2017 年 11 月一、实验目的与要求：l 熟练使用 SQL 定义子语言、操纵子语言命令语句l 掌握关系模型上的完整性约束机制l 掌握一定的数据库管理技术l 能完成简单的数据库应用开发二、实验内容 1、实验一到实验十七（一）数据定义子语言实验（2 学时）实验 1：利用 SQL 语句创建 Employee 数据库代码如下：create database Employee;运行结果：实验 2：利用 SQL 语句在 Employee 数据库中创建人员表 person、月薪表 salary及部门表 dept, 暂不定义外键约束。要求：按表 1、表达、表 3 中的字段说明创建表
2、1person 表结构字段名数据类型字段长度允许空否字段说明30P_noChar6Not Null工号，主键P_nameVarchar10Not Null姓名SexChar2Not Null性别BirthdateDatetime8Null出生日期ProfVarchar10Null职称DeptnoChar4Not Null部门代码，外键（参照 dept 表）表 2salary 表结构字段名数据类型字段长度允许空否字段说明P_noChar6Not Null工号，主键，外键（参照 person表）BaseDec5Null基本工资BonusDec5Null奖金，要求50FactDec5Null实发工资
3、=基本工资+奖金MonthInt2Not Null月份表 3dept 表结构字段名数据类型字段长度允许空否字段说明DeptnoChar4Not Null部门代码，主键，DnameVarchar10Not Null部门名称代码如下：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
4、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表
5、person 中的数据P_noP_nameSexBirthDateProfDeptno000001王云男1973-4-7中级0001000002谢志文男1975-2-14中级0001000003李浩然男1970-8-25高级0002000004廖小玲女1979-8-6初级0002000005梁玉琼女1970-8-25中级0003000006罗向东男1979-5-11初级0003000007肖家庆男1963-7-14高级0003000007肖家庆男1963-7-14高级0003代码如下：insert into person (P_no,P_name,Sex,Birthdate,Prof,Dept
6、no) 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) va
7、lues (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
8、 (000007,尚家庆,男,1963- 7-14,高级,0003)运行结果：表 5表 salary 中的数据P_noBaseBonusFactS_month00000121003001000002180030010000032800280100000425002501000005230027510000061750130100000724002101代码如下：insert into salary (P_no,Base,Bonus,Fact,Month) values (000001,2100,300,2100+300,1) insert into salary (P_no,Base,Bonu
9、s,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
10、) 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 中的数据DeptnoDname0001人事部0002财务部0003市场部代码如下：insert into dept (Deptno,Dname) values (0001,人事部) insert into dept (Deptno,
11、Dname) values (0002,财务部) insert into dept (Deptno,Dname) values (0003,市场部) 运行结果：实验 4：（1）利用 SQL 语句修改表中的数据。要求：将 salary 表中工号为 000006 的员工工资增加为 1800 元， 奖金增加为 160 元。代码如下：update salaryset Base=1800,Bonus=160,Fact=1800+160 where P_no=000006运行结果：（2） 利用 SQL 语句删除表中的数据。要求：删除 salary 表中工号为 000007 的员工数据。代码如下：delet
12、e from salarywhere P_no=000007（3） 利用 SQL 语句查询 person 表中的所有数据。代码如下：select * from person 运行结果：实验 5：（1）创建视图要求：创建员工视图 PersonView，包含员工的所有信息，并调用视图代码如下：create view PersonView as selectperson.P_no,P_name,Sex,Birthdate,Prof,person.Deptno,Base,Bonus,Fact,Month,Dna mefrom person,salary,deptwhere person.Deptno=
13、dept.Deptno and salary.P_no=person.P_noselect * from PersonView运行结果：（2） 删除视图代码如下：要求：将视图 PersonView 删除drop view PersonView运行结果：实验 6：条件查询要求：（1） 查询 person 表中所有不重复的职称。（2） 查询 person 表中职称为中级的所有员工数据。（3） 查询 person 表中具有高级职称的男员工信息。（4） 查询 person 表中姓名为王云、谢志文、罗向东的员工数据。代码及运行结果如下：（1） select distinct Prof from pers
14、on（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
15、：利用 SQL 语句查询各部门的实发工资总数。代码如下：select dept.Dname,sum(Fact) as 部门实发工资总数 from person,salary,deptwhere person.Deptno=dept.Deptno and salary.P_no=person.P_no group by dept.Dname运行结果：实验 9：利用 SQL 语句查询人事部所有员工信息。代码如下：select *from person,salary,deptwhere person.Deptno=dept.Deptno and salary.P_no=person.P_no and
16、 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：子查询：要求
17、：利用 SQL 语句查询比工号为 000005 的员工实发工资高的所有员工信息。代码如下：select * from person,salary where Fact(select Factfrom salarywhere P_no=000005) and person.P_no=salary.P_no（三） 数据完整性实验（1 学时）实验 13：定义外键约束要求：（1） 创建表时将 person 表的 deptno 列定义为外键，并参考 dept 表的列 deptno。代码如下：alter table personadd constraint personkey foreign key (D
18、eptno) references dept(Deptno)（2） 将 salary 表中的 P_no 设为外键，并使其参照 person 表中的列P_no。代码如下：alter table salaryadd constraint salarykey foreign key (P_no)references person(P_no)运行结果：实验 14：测试对主表进行插入、更新及删除操作时的影响。（请写明原因）要求：（1） 向表 dept 中插入一行数据（0004，研发部），测试是否影响从表。代码如下：insert into dept(Deptno,Dname) values (0004,研
19、发部)运行结果：不影响从表，插入的数据与外键约束的数据没有联系。（2） 将表 dept 中的部门号 0003 改为 0006，测试是否影响从表。代码及运行结果如下：update dept set Deptno=0006where Deptno=0003分析：影响从表，0006为原本不存在的数据，修改时受外键约束无法实现。（3） 删除表 dept 中部门号为 0001 的员工的数据，测试是否影响从表。代码及运行结果如下：delete from deptwhere Deptno=0001分析：影响从表，删除表 dept 中部门号为 0001 的员工的数据，修改时受外键约束无法实现。实验 15：测试
20、对从表进行插入、更新及删除操作时的影响。（请写明原因）要求：（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 whe
21、re P_no=000005分析：违背参照完整性，删除数据的操作受到约束无法实现。（四） SQL Server 安全设置实验（1 学时）实验 16：创建登录账号（1） 创建 SQL Server 登录账号要求：基于 SQL Server 创建登录账号 kmust、student、testdelete sp_addlogin loginame=kmust,passwd=123456, defdb=NEmployee, deflanguage=NSimplified Chinese, sid=null,encryptopt=nullsp_addlogin loginame=student, pas
22、swd=123456, defdb=NEmployee, deflanguage=NSimplified Chinese, sid=null,encryptopt=nullsp_addlogin loginame=testdelete, passwd=123456, defdb=NEmployee, deflanguage=NSimplified Chinese, sid=null,encryptopt=null（2） 删除登录账号要求：删除 testdelete 登录账号实验 17：数据库用户设置基于 SQL Server 登录的设置要求：授权 kmust、student 登录账号以 db_
23、owner 用户身份访问数据库 Employeeexec sp_adduserkmustexec sp_addrolememberdb_owner,kmustexec sp_adduserstudentexec sp_addrolememberdb_owner,student2、Employee 数据库系统1、创建数据库 Employee。2、在数据库 Employee 中创建表（可对表进行增删查改等操作）。3、在数据库中创建视图。4、创建登录账号，授权 db_owner 身份访问数据库 Employee。三、总结与体会通过数据库上机实验，学会了使用 SQL 语句在 SQLSERVE 中创建数据库；在数据库中建表、创建视图；对表进行增删查改、建立主键、外键约束；创建 SQLSERVE 账号并授权用户角色实现对数据库的访问。数据库在生活中比较普遍，学会基本的语句操作会对以后的学习产生很大帮助，但数据库学问较深，还未接触到深层次的学习， 以后有机会会认真努力学习相关知识。
