数据库原理上机实验报告

《数据库原理》上机实验报告

学号：2012104

姓名：

班级：测控

昆明理工大学信息工程与自动化学院

2014年12月

一、实验目的与要求：

●熟练使用SQL定义子语言、操纵子语言命令语句

●掌握关系模型上的完整性约束机制

●掌握一定的数据库管理技术

●能完成简单的数据库应用开发

二、实验内容及学时安排（总学时：14）

（一）数据定义子语言实验（2学时）

实验1：利用SQL语句创建Employee数据库

CREATE DATABASE Employee;

结果：

实验2：利用SQL语句在Employee数据库中创建人员表person、月薪表salary 及部门表dept。

要求：按表1、表2、表3中的字段说明创建

表1 person表结构

表2 salary表结构

表3 dept表结构

程序为：

CREATE TABLE dept(

deptno CHAR(4) PRIMARY KEY NOT NULL, dname V ARCHAR(10) NOT NULL)

CREATE TABLE Person(

P_no CHAR(6) PRIMARY KEY Not Null,

P_name V ARCHAR(10) Not Null,

Sex CHAR(2) Not Null,

Birthdate Datetime Null,

Prof V ARCHAR(10) Null,

Deptno CHAR(4) Not Null,

FOREIGN KEY(Deptno) REFERENCES dept(Deptno)); CREATE TABLE salary

(P_no Char(6) PRIMARY KEY Not Null,

Base Dec(5),

Bonus Dec(5) CHECK (Bonus>50),

Fact as Base+Bonus,

Month Int Not Null,

FOREIGN KEY (P_no) REFERENCES person(P_no));

结果：

（二）数据操纵子语言实验（6学时）

实验3：利用SQL语句向表person、salary和dept中插入数据。

要求：按表4、表5、表6中的数据插入。

表4 表person中的数据

表5 表salary中的数据

表6 表dept中的数据

程序为：

INSERT INTO dept(Deptno,Dname)

values('0001','人事部');

INSERT INTO dept(Deptno,Dname)

values('0002','财务部');

INSERT INTO dept(Deptno,Dname)

values('0003','市场部');

程序为：

INSERT INTO person(P_no,P_name,Sex,Birthdate,Prof,Deptno) V ALUES('000001','王云','男','1979-4-7','中级','0001');

INSERT INTO person(P_no,P_name,Sex,Birthdate,Prof,Deptno) V ALUES('000002','谢志文','男','1975-2-14','中级','0001'); INSERT INTO person(P_no,P_name,Sex,Birthdate,Prof,Deptno) V ALUES('000003','李浩然','男','1970-8-25','高级','0002'); INSERT INTO person(P_no,P_name,Sex,Birthdate,Prof,Deptno)

V ALUES('000004','廖小玲','女','1979-8-6','初级','0002'); INSERT INTO person(P_no,P_name,Sex,Birthdate,Prof,Deptno) V ALUES('000005','梁玉琼','女','1970-8-25','中级','0003'); INSERT INTO person(P_no,P_name,Sex,Birthdate,Prof,Deptno) V ALUES('000006','罗向东','男','1979-5-11','初级','0003'); INSERT INTO person(P_no,P_name,Sex,Birthdate,Prof,Deptno) V ALUES('000007','肖家庆','男','1963-7-14','高级','0003');

程序为：

INSERT INTO salary(P_no,Base,Bonus,Month)

V ALUES('000001','2100','300','1');

INSERT INTO salary(P_no,Base,Bonus,Month)

V ALUES('000002','1800','300','1');

INSERT INTO salary(P_no,Base,Bonus,Month)

V ALUES('000003','2800','280','1');

INSERT INTO salary(P_no,Base,Bonus,Month)

V ALUES('000004','2500','250','1');

INSERT INTO salary(P_no,Base,Bonus,Month)

V ALUES('000005','2300','275','1');

INSERT INTO salary(P_no,Base,Bonus,Month)

V ALUES('000006','1750','130','1');

INSERT INTO salary(P_no,Base,Bonus,Month)

V ALUES('000007','2400','210','1');

实验4：（1）利用SQL语句修改表中的数据。

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

修改的代码为：

UPDATE salary

SET Base=1800,Bonus=160

WHERE P_no='000006';

修改后的结果为：

（2）利用SQL语句删除表中的数据。

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

删除的代码为：

DELETE

FROM person

WHERE P_no='000007';

修改后的结果为：

冲突出错。不能够删除，是因为前面各表间都建立了联系，定义了外键，所以在此不能够删除，如果要删除，前面各表之间必须解除相互间的关系，把定义的外键删除。

（3）利用SQL语句查询person表中的所有数据。

查询语句为：

select *

from person

查询结果为：

此处也可以看出“删除person表中工号为000007的员工数据”这条命令没有成功，000007的信息还在。

实验5：条件查询

要求：

（1）查询person表中所有不重复的职称。

查询代码为：

SELECT DISTINCT prof

FROM person;

查询结果为：

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

SELECT *

FROM person

WHERE prof='中级';

查询结果为：

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

SELECT *

FROM person

WHERE prof='高级' AND Sex='男';

查询结果为：

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

SELECT*

FROM person

WHERE P_name IN ('王云','谢志文','罗向东');

查询结果为：

实验6：使用ORDER BY排序

要求：利用SQL语句将工号在000003和000006之间的员工的月收入按实发工资升序排序。

代码：

SELECT *

FROM salary

WHERE P_no BETWEEN 000003 AND 000006

ORDER BY Fact ASC

查询的结果为：

实验7：利用SQL语句查询各部门的实发工资总数。

代码：

SELECT Dname, SUM (Fact)

FROM person,salary,dept

WHERE dept.Deptno=person.Deptno AND person.P_no=salary.P_no GROUP BY Dname

查询结果为：

实验8：利用SQL语句查询人事部所有员工信息。

查询代码：

SELECT *

FROM person,dept

WHERE dept.Deptno=person.Deptno AND Dname='人事部'

查询结果为：

实验9：表的内连接查询：

要求：利用SQL语句查询person表中职称为中级的员工信息。

查询代码：

SELECT *

FROM person,salary,dept

WHERE person.P_no=salary.P_no AND person.Deptno=dept. Deptno AND prof='中级'查询结果为：

实验10：表的外连接查询：

要求：利用SQL语句查询每个员工1 月份的工资和奖金

查询代码：

select salary.base,Bonus

from salary left outer join person on (salary.P_no = person.P_no);

查询结果为：

实验11：子查询：

要求：利用SQL语句查询比工号为000005的员工实发工资高的所有员工信息。

查询代码为：

SELECT DISTINCT person. * ,salary. *,dept.*

FROM person,salary,dept

WHERE Fact>

(SELECT DISTINCT Fact

FROM salary

WHERE P_no='000005')AND person.P_no=salary.p_no AND person.Deptno=Dept.Deptno

查询结果为：

（三）数据完整性实验（1学时）

实验12：定义外键约束

要求：

（1）创建表时将person表的deptno列定义为外键，并参考dept表的列deptno。

代码为：

FOREIGN KEY Deptno REFERENCES dept(Deptno) /*在表级定义参照完整性*/

（2）将salary表中的P_no设为外键，并使其参照person表中的列P_no。代码为：

FOREIGN KEY P_no REFERENCES person(P_no) /*在表级定义参照完整性*/

其依赖关系为

实验13：测试对主表进行插入、更新及删除操作时的影响。（请写明原因）要求：

（1）向表dept中插入一行数据（‘0004’，‘研发部’），测试是否影响从表。

代码为：

INSERT INTO dept (Deptno, Dname)

V ALUES('0004','研发部');

测试结果：影响从表

原因：由于插入的元祖的Deptno属性的值在表person中找不到一个元祖,其Deptno属性的值与之相等，破坏了被参照表person的参照完整性。

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

代码为：

UPDATE dept

SET Deptno='0003'

WHERE Deptno='0006';

测试结果：

影响从表

原因：修改了dept表中的一个属性，使得在person表中找不到一个元祖，其Deptno属性的值与之相等。破坏了参照完整性

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

FROM dept

WHERE Deptno='0001';

测试结果：

影响从表

原因：删除了dept表中的一个员工的数据，使得在person表中定义的外键无法参照dept表，破坏了参照完整性。

实验14：测试对从表进行插入、更新及删除操作时的影响。（请写明原因）要求：

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

INSERT INTO person(P_no,P_name,Sex,Birthdate,Prof,Deptno)

V ALUES('000012','宋全礼','男','1980-7-17','初级','0005');

测试结果：违背了参照完整性

原因：由于person表要参照dept表，而在person表中插入这一行数据，是的这

一行数据无法在dept表中有相应的参照，故违背了参照完整性。

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

DELETE

FROM person

WHERE P_no='000005';

测试结果：不违背参照完整性

原因：由于对该员工的所有数据都进行了删除，所以在person表中和dept表中都没有存在该员工的数据，所以不影响参照完整性

（四）用Microsoft Access设计Employee数据库系统（5学时）要求：

（1）创建表

Person表

Salary表

Dept表

（2）创建关联

（3）用查询设计工具创建查询Person表的查询

Salary表的查询

Dept表的查询

（4）创建简单的窗体表person的窗体

表salary的窗体

（5）创建基本报表表person的报表

表salary的报表