数据库上机

《数据库原理》上机实验指导书

专业：自动化、测控

指导老师：张光辉

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

2012年9月

一、实验目的与要求：

●熟练使用SQL定义子语言、操纵子语言命令语句●掌握关系模型上的完整性约束机制

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

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

二、实验内容及学时安排（总学时：8）（一）数据定义子语言实验（2学时）

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

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

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

表1 person表结构

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,

);

表2 salary表结构

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

);

表3 dept表结构

CREATE TABLE dept

(Deptno Char (4) Not Null,

Dname Varchar (10) Not Null

);

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

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

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

表4 表person中的数据

INSERT INTO Person (P_no,P_name,Sex,BirthDate,Prof,Deptno)

V ALUES('000001','王云','男','1973-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');

表5 表salary中的数据

INSERT INTO salary(P_no,Base,Bonus,Fact,month)

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

INSERT INTO salary (P_no,Base,Bonus,Fact,month)

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

INSERT INTO salary (P_no,Base,Bonus,Fact,month)

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

INSERT INTO salary (P_no,Base,Bonus,Fact,month)

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

INSERT INTO salary(P_no,Base,Bonus,Fact,month)

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

INSERT INTO salary (P_no,Base,Bonus,Fact,month)

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

INSERT INTOsalary (P_no,Base,Bonus,Fact,month)

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

表6 表dept中的数据

INSERT INTO dept(Deptno,Dname)

V ALUES('0001','人事部')

INSERT INTO dept(Deptno,Dname)

V ALUES('0002','财务部')

INSERT INTO dept(Deptno,Dname)

V ALUES('0003','市场部')

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

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

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

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

DELETE

FROM person

WHERE P_no='000007';

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

实验5：（1）将员工中姓名为王云的员工职称改为高级

update Person

set prof='高级'

where p_name='王云';

（2）向Person中插入一行数据（‘000008’，‘刘美萍’，‘中级’，‘00002’）

实验6：条件查询

要求：

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

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

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

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

实验7：使用ORDER BY排序

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

select * from salary

Where P_no BETWEEN 000003 AND 000006

Order by Fact ASC;

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

select Dname,sum(salary.Fact) as totalFact from person,dept,salary

Where person.Deptno=dept.Deptno AND salary.P_no=person.P_no

group by Dname

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

select * from person

Where person.Deptno='0001';

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

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

select * from person

Where Prof='中级';

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

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

select P_name,Base,Bonus from person,salary

Where person.P_no=salary.P_no and month='1';

实验12：子查询：

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

Select * from person ,salary as a

Where person.P_no=a.P_no

AND a.Fact>(select b.Fact from salary as b

where b.P_no='000005');

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

实验12：定义外键约束

要求：

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

ALTER TABLE person

ADD CONSTRAINT F1 FOREIGN KEY (DEPTNO) REFERENCES

DEPT(DEPTNO);

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

ALTER TABLE salary

ADD CONSTRAINT F2 FOREIGN KEY(P_no) REFERENCES

PERSON(P_no);

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

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

insert into dept

Values('0004','研发部');

select*from dept

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

Set Deptno='0006' where Deptno='0003';

分析：运行发生错误，Deptno作为表的主码，不能直接进行修改。

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

Delete from dept

Where Deptno='0001';

分析：运行发生错误，Deptno作为表的主码，不能直接进行修改。

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

要求：

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

Values('000012','宋全礼','男',1987-7-17,'初级','0005');

分析：运行发生错误，Deptno作为表的主码，不能直接进行修改，违背。

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

Where P_no='0005';

（四）SQL Server安全设置实验（1学时）

实验15：创建登录账号

建SQL Server登录账号

要求：基于SQL Server创建新的登录账号000005

实验16：数据库用户设置

基于SQL Server登录的设置

要求：授权000005登录账号以000005用户身份访问数据库

Employee

三、实验报告撰写要求：

1、制作实验报告封面（见附录）。

2、内容顺序包括：

一、实验目的与要求：

二、实验内容：

（1）实验1至实验14：要标明是第几个实验、实验内容及实验要求，然后把程序代码及运行结果写出，应写成：

实验1：（内容、要求）

代码如下：

create table AAA (……)

运行结果为：（运行结果）.

……….

（2）设计的“Employee数据库系统”要求把设计思路及相应的界面打印出

来，并作说明。

三、总结与体会

附录：《数据库原理》上机实验报告封面

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

学号：

姓名：

班级：

指导教师：

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

2012年12月