数据库实践报告

时间：2024.3.31

中南大学

数据库实验报告

课题：数据库原理及应用

姓名：林一新

班级：信息0803

学号：0909082112

指导老师：张祖平

实验时间：2011. 04

一、 实验目的与要求

1. 熟悉ORACLE的运行环境，在CLIENT端进行联接设置与服务器联通，进入ORACLE的SQL*PLUS的操作环境，进一步建立表格，并考虑主键,外部键,值约束。

2. 熟悉在SQL*PLUS环境下进行数据记录查询，其中查询考虑简单查询,复杂查询,嵌套查询。

3. 熟悉在SQL*PLUS环境下进行数据记录的修改、删除及表结构的修改。

4. 熟悉在SQL*PLUS环境下进行数据视图及权限控制的操作。

5. 熟悉在SQL*PLUS环境下数据库触发器的建立及作用，了解大型数据库编程。

二、 实验环境

硬件：586以上服务器、586以上微机，内存512M以上。

软件：WINDOWS 2000/2003 SERVER 可安装ORACLE/9i/10g/11g/13i FOR NT/WINDOWS(注意在32位与64位的区别) 如果windows非server如XP等,安装时请选择个人版(PERSONAL)

三、 实验内容及步骤

1. 熟悉ORALCE环境并练习SQL的定义

1) 打开控制面板，――>管理工具――>服务――>启动Oracle Serviceslab或者如图通过界面或资源管理器右键找到管理。再找服务：启动的服务如下或有OracleService****的服务。

2) 从桌面执行SQL*PLUS或在程序中

3) 输入：system/manager

4) 在SQL>状态输入建立用户命令:

create user username identified by password;

其中username与password不要数字开头，用户名username中按规则加班级学号后缀

如：U_301表示3班学号尾数为1号的用户。

5) 给用户授权：grant resource,connect to username;

6) 连接用户：connect username/password

在SQL>就可以建表等操作了

7) 根据要求建立如下表，插入数据

DROP TABLE DEPT_???; /* 系，???为班级学号如dept_512,5班12号

代码：

create user U_2112 identified by lyx330;

grant resource,connect to U_2112;

connect U_2112/lyx330;

DROP TABLE DEPT_2112;

CREATE TABLE DEPT_2112(DNO CHAR(3) PRIMARY KEY,DN VARCHAR2(32), DEAN CHAR(8),TEL CHAR(8) CHECK(SUBSTR(1,3)='8887'));

DROP TABLE STUD_2112;

CREATE TABLE STUD_2112(SNO CHAR(10) PRIMARY KEY,SNAME CHAR(8),BIRTHDAY DATE,NATIVE_PLACE VARCHAR2(20),DNO CHAR(3) REFERENCES DEPT_2112(DNO));

DELETE FROM STUD_2112;

DELETE FROM DEPT_2112;

INSERT INTO DEPT_2112 VALUES('D01','计算机系','王大明','88879626');

INSERT INTO DEPT_2112 VALUES('D02','物理系','张大奔','88879726');

INSERT INTO DEPT_2112 VALUES('D03','化学系','李水明','88879826');

INSERT INTO DEPT_2112 VALUES('D04','管理系','欧阳卫红','88879926');

INSERT INTO DEPT_2112 VALUES('D05','外语系','宽左右','88879526');

INSERT INTO STUD_2112 VALUES('0909080524','胡明天',SYSDATE-365*21,'湖南长沙','D01');

INSERT INTO STUD_2112 VALUES('0909080134','唐明海',SYSDATE-365*21,'湖南长沙','D01');

INSERT INTO STUD_2112 VALUES('0909080224','张丽梅',SYSDATE-365*20,'湖南长沙','D01');

INSERT INTO STUD_2112 VALUES('0909080324','李有能',SYSDATE-365*22,'湖南长沙','D02');

INSERT INTO STUD_2112 VALUES('0909080424','王汉中',SYSDATE-365*22,'湖南长沙','D02');

INSERT INTO STUD_2112 VALUES('0909080464','高强',SYSDATE-365*21,' ','D02');

INSERT INTO STUD_2112 VALUES('0909080474','JONE',SYSDATE-365*23,' ','D03');

INSERT INTO STUD_2112 VALUES('0909080484','SMITH',SYSDATE-365*21,' ','D03');

INSERT INTO STUD_2112 VALUES('0909080594','TOM HONE',SYSDATE-365*22,' ','D03');

INSERT INTO STUD_2112 VALUES('0909080264','张三', SYSDATE-365*22,' ','D04');

INSERT INTO STUD_2112 VALUES('0909080274','李四', SYSDATE-365*21,' ','D04');

INSERT INTO STUD_2112 VALUES('0909080284','王老五', SYSDATE-365*20,' ','D04');

思考题：

1．改变表的名称及数据类型再进行相应的操作。

答：改变表名的操作为：ALTER TABLE 当前表名 RENAME TO 新表名;可以通过以下属性改变属性的数据类型：ALTER TABLE 表名 MODIFY(列名数据类型)。

2．先删除主表DEPT_???，结果怎么样？

答：因为DEPT_???中属DNO作为STUD_???中的外键被参照，为了约束完整性不应该被首先删除。但是可以用cascade 关键字实现级联删除。

3．修改表中的字段类型与长度，考虑表中有数据或者没数据情况。

答：修改表中字段类型与长度：ALTER TABLE DEPT_??? MODIFY(DNO CHAR(2))。

表中主键值不允许为null；属性定义中声明为not null也不允许空值。

4．插入相同的学号与相同的系号会出现什么结果

答：不允许。主键值不可相同。

5．插入空的学号或空的系号会出现的结果

答：不允许。主键值不允许为空值。

2.记录查询

1．查询所有学生记录

select * from STUD_2112;

2．查询所有系的记录，并按系号升序排列

select DN,DNO from DEPT_2112

ORDER by DNO DESC;

3.查询有自己姓名的记录

Select * from STUD_2112

where EXISTS(SNAME);

4.查询’张三’的SNO，SNAME，BIRTHDAY

select SNO,SNAME,BIRTHDAY from STUD_2112

where SNAME=’张三’;

5.查询计算系所有的学生记录

select * from STUD_2112

where DNO=’D01’;

6.查询各计算系学生在各年龄值的人数，如19的为200，20的有500等

select COUNT(AGE)

from STUD_2112

group by (SYSDATE-BIRTHDAY)/365;

7.查询’张三’的SNO，BIRTHDAY，DNO，DN，DEAN

select SNO，BIRTHDAY,STUD_2112.DNO,DN,DEAN

from STUD_2112, DEPT_2112

where SNAME=’张三’ AND STUD_2112.DNO= DEPT_2112.DNO;

思考题：

1．查询学生的记录数。

答：select COUNT(*) from STUD_2112；

2．查询平均年龄小于22岁的系号及学生SNO，SNAME，AGE

答：运用子查询可实现。

3．找出平均年龄大于20的系名及平均年龄数

答：运用having（）聚合函数。

3.SQL的数据操纵

1．在STUD中增加AGE NUMBER（2）/*年龄*/字段，并求出每个学生入校时的年龄。

ALTER TABLE STUD_2112 ADD AGE NUMBER(2);

UPDATE STUD_2112

SET AGE =(SYSDATE-BIRTHDAY)/365; UPDATE STUD_2112

2．将STUD_???表中的08级学生的年龄都加3

SET AGE =AGE+3

WHERE SNO like ’090908____’;

3．将DEPT_???表中的’计算机系’改为’信息科学与工程学院’

UPDATE DEPT_2112

SET DN=’信息科学与工程学院’

WHERE DN=’计算机系’;

4．删除07级以前的所有的学生记录

DELETE FROM STUD_2112

WHERE SNO<0909070000;

思考题：

1．误删了STUD_???中的记录，怎么办？

答：可以运用事务回滚rollback to savepointlabel.此外，为了防止不必要的回滚操作。设置savepoint 前应该在适当位置用commit提交事务。

2．怎么样才能做到在一个语句中将学生的年龄加上相应的学习年数（如08级加3，09级加2，10级加1，07级加4等）。

答： UPDATE STUD_2112

SET AGE=AGE+11-TO_NUMBER(SUBSTR(SNO,5,2));

3：建立与STUD_???对应的表ST_???（SNO，SNAME，AGE/*年龄*/），在ST_???输入若干学号（在STUD_？？？中有的有几个），然后将STUD_???中的SNAME，BIRTHDAY转入到ST_???相应的SNO，AGE（多表间的更新）。

答：CREATE TABLE ST_2112(SNO CHAR(10) PRIMARY KEY,SNAME CHAR(8), AGE NUMBER(2));

INSERT INTO ST_2112(SNO) VALUES('0909080424');

INSERT INTO ST_2112(SNO) VALUES('0909080464');

INSERT INTO ST_2112(SNO) VALUES('0909080474');

INSERT INTO ST_2112(SNO) VALUES('0909080484');

UPDATE ST_2112

SET ST_2112.AGE = ST_2112.AGE, ST_2112.BIRTHDAY= ST_2112.BIRTHDAY

WHERE ST_2112. SNO = ST_2112. SNO;

4.视图及权限控制

1．视图的建立

connect system/manager;

grant create view to U_2112;

connect U_2112/lyx330;

CREATE VIEW S_D_2112 AS SELECT SNO,SNAME,STUD_2112.DNO,DN,TEL FROM STUD_2112,DEPT_2112

WHERE STUD_2112.DNO=DEPT_2112.DNO AND TO_CHAR(BIRTHDAY,'YYYY')>='1991';

2．对视图进行查询

Select * from S_D_2112;

3．分别将对象的各种权限授予相应的用户假若已有用户SCOTT（视系统实际情况），在SCOTT中看操作权限的变化。

GRANT INSERT ON STUD_2112 TO SCOTT;

GRANT UPDATE(BIRTHDAY) ON STUD_2112 TO SCOTT;

GRANT DELETE ON STUD_2112 TO SCOTT;

GRANT SELECT ON S_D_2112 TO SCOTT;

思考题：

1．在视图S_D_???中只考虑信息08级，视图将怎样建立？

答; CREATE VIEW S_D_2114 AS SELECT SNO,SNAME,STUD_2112.DNO,DN,TEL FROM STUD_2112,DEPT_2112

WHERE STUD_2112.DNO=DEPT_2112.DNO AND SNO like ’090908____’;

如果用户为DBA，还需要给授权吗？

答：不需要。

2．考虑基于一个表、简单条件的视图的更新操作。

答：类同。

4.考虑基于多表视图的更新操作。

答：一般不允许。

5.数据库触发器与过程

1．先建立表ST_???(SNO,SNAME,AGE)

DROP TABLE ST_2112;

CREATE TABLE ST_2112(SNO CHAR(10),SNAME CHAR(8),AGE NUMBER(2));

2．建立触发器

CREATE OR REPLACE TRIGGER STUD_TRG_2112

BEFORE INSERT OR UPDATE OR DELETE ON STUD_2112

FOR EACH ROW

BEGIN

IF INSERTING THEN

INSERT INTO ST_2112 VALUES(:NEW.SNO,:NEW.SNAME,(SYSDATE-:NEW.BIRTHDAY)/365);

END IF;

IF UPDATING THEN

DELETE FROM ST_2112 WHERE SNO=:OLD.SNO;

INSERT INTO ST_2112

VALUES(:NEW.SNO,:NEW.SNAME,(SYSDATE-:NEW.BIRTHDAY)/365);

END IF;

IF DELETING THEN

DELETE FROM ST_2112 WHERE SNO=:OLD.SNO;

END IF;

END

3．验证触发器的作用

在STUD_2112中输入、修改、删除数据时，查看ST_2112表中的变化。

ALTER TABLE ST_2112 DROP COLUMN AGE cascade constraints;

INSERT INTO ST_2112 VALUES('0909081111','一一一 ',date'1988-05-02','中国福建','D01');

INSERT INTO ST_2112 VALUES('0909082222','二二二 ',date'1988-04-02','中国广东','D01');

INSERT INTO ST_2112 VALUES('0909083333','一一一 ',date'1988-04-02','中国福建','D02');

INSERT INTO ST_2112 VALUES('0909084444','一一一 ',date'1988-02-02','中国福建','D03');

SELECT *

FROM ST_2112;

UPDATE ST_2112

SET BIRTHDAY=DATE'1988-09-02'

WHERE SNAME='二二二';

SELECT *

FROM ST_2112;

DELETE FROM ST_2112

WHERE SNAME='一一一';

SELECT *

FROM ST_2112;

4．设计并调试过程

（1）分系dno统计STUD_2112表中各年龄的人数

（2）根据Bank数据库，分街道(street)统计客户的存款余额（balance）,其中街道名为变量。

四、 出现的问题和解决方法

1. 建立视图时显示权限不足。

解决方法：进行授权。connect system/manager;

grant create view to U_2112;

connect U_2112/lyx330;

2. 插入数据时，出现错误。

解决方法：根据提示查找错误。

检查语法错误。

检查拼写错误。

检查标点符号，引号是否缺少，标点是否为英文标点。

五、 心得体会

数据库的上机实验结束了，通过对数据库的学习也初步掌握了其各方面的知识，数据库的的功能是强大的，面对目前的信息化社会，在整理、查询、分析数据方面是一款强有力的工具。

学习的目的在于将知识能合理顺利的运用，将书本知识化为己用，将理论知识用在显示当中的问题，是一个不知道到知道，了解完善应用的过程，尤其是计算机方面的知识更是如此，必要的上机练习是必不可少的。本次数据库上机主要用到的是SQL结构化查询语言，它是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。通过这几次上机实验，我们学会了数据查询、修改、视图建立和查询、权限的修改、触发器的建立等。上机过程中也遇到不少问题，通过老师的帮助和同学互相探讨，解决了很多问题。通过本次实验，我了解到数据库今后的发展方向以及其广泛应用，在今后应加强学习。

六、 参考文献

[1] Ramez Elmasri，Shamkant B.Navathe著，孙瑜注释.Fundamentals of Database Systems (Fourth Edition)(数据库系统基础初级篇英文注释版第四版)人民邮电出版社(影印,英文版). 20##-10

[2] 张祖平,孙星明等.数据库原理及应用,中南大学出版社,2010.11

七、 代码汇总

/*建表*/