1、数据库原理及实验指导书922数据库原理实验指导书大连民族学院数据库原理教学组2005年12月目 录实验一 数据库模式的设计与建立 3一上机目的 3二、实验类型 3三预备知识 3四上机内容 4五上机作业 6实验二 数据操纵 8一上机目的 8二、实验类型 8三预备知识 8四上机内容 10五上机作业 11实验三 数据查询 13一上机目的 13二、实验类型 13三预备知识 13四上机内容 14五上机作业 21实验四 存储过程与触发器建立与维护 22一实验目的 22二 实验类型 22三预备知识 22四上机内容 23实验五 数据库保护 25一上机目的 25二实验类型 25三预备知识 25三上机内容 26实
2、验六 数据库应用系统的初步开发 28一、上机目的 28二、实验类型 28三、上机内容 28参考文献: 29实验一 数据库模式的设计与建立一上机目的1 了解并掌握Oracle中表结构的定义。2 了解并掌握Oracle中的用Create命令定义表的方法,以及表的完整性定义。3 了解并掌握Oracle中的用Alter命令 和Drop命令对表的修改和删除。二、实验类型验证性三预备知识DDL是SQL命令的子集,用来创建、修改、删除Oracle数据库结构。这些命令能立即影响数据库和数据字典字段信息。1 表名命名规则所用的表名必须满足下面的条件:a) 名字必须以A-Z 或a-z的字母开始;b) 名字可以包括
3、字母、数字和特殊字母(_)。字符$和#也是合法的,但是这种用法不提倡;c) 名字大小写是一样的;例如EMP、emp和eMp是表示同一个表;d) 名字最长不超过30个字符;e) 表名不能和其它的对象重名;f) 表名不能是SQL保留字。表2-1 表名命名举例名字合法EMP85YES85EMPNO(开始不是字母)FIXED_ASSETSYESFIXED ASSETSNO(包含空格)UPDATENO(SQL保留字)2 字段类型表2-2 字段类型数据类型描述VARCHAR2(w)变长字符长度为w。最长为2000个字符。CHAR(w)定长字符长度为w。默认为1个字符;最长为255个字符NUMBER38位有
4、效数字的浮点数NUMBER(w)W位精确度的整数,范围从1至38NUMBER(w,s)W是精度,或总的数字书,范围从1至38。S是比例,或是小数点右边的数字位。比例的范围从-84至127DATE日期值,范围从公元前14712年1月到公元314712年12月LONG变长字符串,其最大长度为2G(或231-1个字节)RAW 和LONG RAW面向字节数据,可存储字符串、浮点数,二进制数据等3 Create命令1)、CREATE TABLE table_name( column_name type(size), column_name type(size), );例如:CREATE TABLE DE
5、PT(DEPTNO NUMBER(2),DNAME VARCHAR2(12),LOC VARCHAR2(12);2)、CREATE TABLE table_name (column_name,) AS SELECT statement;例如:CREATE TABLE DEPTNO10 (NAME,LOCATION)AS SELECT DNAME,LOC FROM DEPTWHERE DEPTNO = 10;4完整性约束Oracle允许用户为表和列定义完整性约束来增强一定的规则。可分为:表约束和字段约束四上机内容1 创建表EMPCREATE TABLE EMP(EMPNO NUMBER(4) N
6、OT NULL,ENAME VARCHAR2(10),JOB VARCHAR2(10),MGR NUMBER(4),HIREDATE DATE,SAL NUMBER(7,2),COMM NUMBER(7,2),DEPTNO NUMBER(2) NOT NULL);Table created.用SQL*PLUS命令DESCRIBE来看生成的EMP表的列明细清单:输入命令:DESCRIBE EMPEMP生成的数据表结构显示如下: NameNull?TypeEMPNONOT NULLNUMBER(4)ENAMEVARCHAR2(10)JOBVARCHAR2(10)MGRNUMBER(4)HIREDA
7、TEDATESALNUMBER(7,2)COMMNUMBER(7,2)DEPTNONOT NULLNUMBER(2)2 从其他表中抽取字段生成数据表CREATE TABLE EMP_PART ASSELECT EMPNO,ENAME,JOB,SAL,COMM FROM EMP;Table Created.输入命令:DESCRIBE EMP_PARTEMP_Part生成的数据表结构结果显示如下: NameNull?TypeEMPNONOT NULLNUMBER(4)ENAMEVARCHAR2(10)JOBVARCHAR2(10)SALNUMBER(7,2)COMMNUMBER(7,2)3 DRO
8、P命令删除数据表DROP TABLE EMP_PART;Table dropped.4 给数据表EMP增加一个字段SPOUSES_NAMEALTER TABLE EMPADD (SPOUSES_NAME CHAR(10));输入命令:DESCRIBE EMPEMP生成的数据表结构显示如下: NameNull?TypeEMPNONOT NULLNUMBER(4)ENAMEVARCHAR2(10)JOBVARCHAR2(10)MGRNUMBER(4)HIREDATEDATESALNUMBER(7,2)COMMNUMBER(7,2)DEPTNONOT NULLNUMBER(2)SPOUSES_NAM
9、ECHAR(10)5 用ALTER的MODIFY命令修改已存在的字段的定义ALTER TABLE EMPMODIFY (ENAME VARCHAR2(12);Table Altered.输入命令:DESCRIBE EMPEMP生成的数据表结构显示如下: NameNull?TypeEMPNONOT NULLNUMBER(4)ENAMEVARCHAR2(12)JOBVARCHAR2(10)MGRNUMBER(4)HIREDATEDATESALNUMBER(7,2)COMMNUMBER(7,2)DEPTNONOT NULLNUMBER(2)SPOUSES_NAMECHAR(10)6 用ALTER的D
10、ROP命令删除数据表中已存在的约束ALTER TABLE EMP DROP PRIMARY KEY;Table Altered. 7创建表CUSTOMERcreate table customer( last_name varchar2 (30) not null, state_cd varchar(2), sales number);Table created.8创建表STATEcreate table state( state_cd varchar(2) not null, sate_name varchar2(30);Table created.五上机作业1 创建如下三个基表:S (S#
11、,SNAME,AGE,SEX) 对应的中文为:学生 (学号,姓名,年龄,性别)SC (S#,C#,GRADE) 对应的中文为:学习(学号,课程号,成绩) C(C#,CNAME,TEACHER) 对应的中文为:课程(课程号,课程名,任课教师)注:本书以后要用到这三个基本表。2 生成一个数据表PROJECTS,其字段定义如下,其中PROJID是主键并且要求P_END_DATE不能比P_START_DATE早。 字段名称数据类型长度PROJIDNUMBER4P_DESCVARCHAR220P_START_DATEDATEP_END_DATEDATEBUDGET_AMOUNTNUMBER7,2MAX_
12、NO_STAFFNUMBER23 生成一个数据表ASSIGNMENTS,其字段定义如下,其中PROJID是外键引自PROJECTS数据表,EMPNO是数据表EMP的外键,并且要求PROJID和EMPNO不能为NULL。 字段名称数据类型长度PROJIDNUMBER4EMPNONUMBER4A_START_DATEDATEA_END_DATEDATEBILL_RATENUMBER4,2ASSIGN_TYPEVARCHAR224 用DESCRIBE命令查看1和2题定义的字段。5 给1题中的PROJECTS数据表增加一个COMMENTS字段,其类型为LONG。给2题中的ASSIGNMENTS数据表增
13、加一个HOURS字段,其类型为NUMBER。实验二 数据操纵一上机目的1 在数据表中用Insert增加记录。2 用Update修改数据表中的数据。3 用Delete删除表中的数据。4 掌握视图(VIEW)在Oracle的应用。二、实验类型验证性三预备知识1 Insert命令1)用来在数据表中增加记录,格式如下:INSERT INTO tablename (column, column, .)VALUES (value, value, .);命令中(column, column, .)是可选的。一般情况下,为了编程的方便,最好指定字段列表。该命令每次只能增加一条记录。注意,CHARACTER和D
14、ATE必须用单引号括起来。例如:INSERT INTO DEPT (DEPTNO,DNAME,LOC)VALUES (50,市场部,上海);在DEPT中增加一个新部门,忽略部门名称,这时字段列表必须指定否则出错,可用如下命令:INSERT INTO DEPT (DEPTNO, LOC)VALUES (50,上海);另外,如果部门名称不能确定,可用NULL代替,如下:INSERT INTO DEPT (DEPTNO,DNAME,LOC)VALUES (50,NULL,上海);增加DATE类型的数值,常用格式是DD-MON-YY。默认的世纪是20世纪,ORCALE已克服了Y2K(千年虫)问题。如下
15、:INSERT INTO EMP (EMPNO,NAM,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES (7568,MASON,ANALYST,7566,TO_DATE(24/06/2084 9:30,DD/MM/YYYY HH:MI),3000,NULL,20);2)增加从其他数据表查询出的数据命令格式:INSERT INTO table (column, column, .)SELECT select-listFROM table(s);2 Update命令在需要修改表中数据时,可使用update命令如下:UPDATE tablealiasSET colum
16、n,column. = expression, subqueryWHERE condition;命令由三部分组成: update后跟一个或多个要修改的表,这部分是必不可少的。 set后跟一个或多个要修改的表列,这也是必不可少的。 where后跟查询条件,这是选项;如果WHERE子句忽略,UPDATE命令将修改数据表中所有记录。例如:修改EMP表中SCOTT的记录数据,把他调到销售部,并且工资提高10%,具体命令如下:UPDATE EMPSET JOB =SALESMAN,HIREDATE = SYSDATE,SAL = SAL*1.1WHERE ENAME = SCOTT;1 record u
17、pdated.3 Delete命令Delete命令用来从表中删除一行或多行记录。命令格式如下:DELETE FROM table WHERE condition;该命令由两部分组成: 关键字delete from 后跟准备要从中删除数据的表名,这是必不可少的。 关键字where后跟删除条件,是可选项;如果不用WHERE子句,数据表中的所有记录将被删除。例如删除EMP表中部门号是10的记录:DELETE FROM EMP WHERE DEPTNO = 10;4 视图(VIEW)视图是一个“窗口”,通过它可以看或修改数据库表中的数据。视图来源于表或其他视图。视图只存为SELECT语句。它只是一个虚
18、表而不是在物理存储器上的真正存在的数据表。视图没有自己的数据,它的数据来自基表。视图的优点: 限制对数据库的访问。 允许用户对复杂的查询进行简单的查询。 对特别的用户和应用程序提供数据独立性。视图对从多个数据库表中检索数据是透明的。视图也允许不同的用户对同一数据表有不同的数据窗口。生成视图的命令格式:CREATE OR REPLACE FORCE VIEW view-name (column1, column2, ) AS SELECT statement WITH CHECK OPTION CONSTRAINT constraint_name;四上机内容1 用Insert在基本表custom
19、er中插入数据SQLinsert into customer values (Nicholson,CA,6989.99);1 row created.SQLinsert into customer values (Martin,CA,2345.45);1 row created.SQLinsert into customer values (Laursen,CA,34.34);1 row created.SQLinsert into customer values (Bambi,CA,1234.55);1 row created.SQLinsert into customer values (
20、McGraw,NJ,123.45);1 row created.2 在表STATE中插入指定的字段SQLinsert into state (state_name,state_cd)2 values (Massachusetttes,MA);1 row created.SQLinsert into state (state_name,state_cd)2 values (California, CA);1 row created.SQLinsert into state (state_name,state_cd)2 values (NewJersey,NJ);1 created.SQLinse
21、rt into state (state_name,state_cd)2 values (NewYork,NY);1 created.3修改数据把state表中NewYork改为Florida,NY改为FD:UPDATE state SET state_name = Florida, state_cd = FD where state_name = NewYork and state_cd = NY;4删除数据从STATE表删除state_name为Florida和state_cd为FD的记录:DELETE FROM STATE WHERE state_name = Florida AND s
22、tate_cd = FD;5 创建视图生成一个部门号是10的视图:CREATE VIEW D10EMPASSELECT EMPNO, ENAME, SALFROM EMPWHERE DEPTNO = 10;View created。6 视图应用从视图D10EMP中查询出全部信息:SELECT * FROM D10EMPORDER BY ENAME;7 删除视图DROP VIEW D10EMP;五上机作业1 用INSERT 命令输入数据 表3-1 基本表S的数据S1WANG20MS2LIU19MS3CHEN22MS4WU19MS5LOU21FS8DONG18F表3-2 基表C的数据C2MATHS
23、MAC4PHYSICSSHIC3CHEMISTRYZHOUC1DBLIC5OSWEN 表3-3 基本表SC的数据(空格为未选修) C# S#S1S2S3S4S5S8C1808590757090C270NULL8560NULLC38595NULL8090C490NULL70C57065NULL2 对S、C、SC表进行操作:1)、把C2课程的非空成绩提高10%。2)、在SC表中删除课程名为PHYSICS的成绩的元组。3)、在S和SC表中删除学号为S8的所有数据。3 在PROJECTS数据库表中增加下列记录:PROJID12P_DESCWRITE C030 COURSEPROOF READ NOTE
24、SP_START_DATE02-JAN-8801-JAN-89P_END_DATE07-JAN-8810-JAN-89BUDGET_AMOUNT500600MAX_NO_STAFF11COMMENTSBR CREATIVEYOUR CHOICE4 在ASSIGNMENTS数据库表中增加下列记录:PROJID112EMPNO736979027844A_START_DATE01-JAN-8804-JAN-8801-JAN-89A_END_DATE03-JAN-8807-JAN-8810-JAN-89BILL_RATE50.0055.0045.50ASSIGN_TYPEWRWRPFHOURS1520
25、305 把ASSIGMENTS表中ASSIGNMENT TYPE的WR改为WT,其他的值不变。6 在PROJECTS 和ASSIGNMENTS插入更多的记录。7 删除自己随意插入的记录。实验三 数据查询一上机目的1 掌握Select语句的运用。2 掌握一些函数的应用。3 掌握子查询的运用。4 连接和分组的应用。二、实验类型验证性三预备知识1Select语句Select命令用于从Oracle数据库中检索数据。读者利用select命令告诉数据库要检索什么样的信息。Select是读者看到的最常用的SQL语句,select命令(如下)有六个基本部分构成:SELECT DISTINCT *,COLUMN
26、 ALIAS,FROM tableWHERE condition(s)ORDER BY column,exper ASC|DESCGROUP BY column,experHAVING having_condtions;i Select后跟用户需要检索的信息(如下一部分将要提到的表中表列的名字)。这是select命令必不可少的部分。ii From后跟检索对象(如存放数据的一个或多个表的名称),from部分也是必不可少的。iii Where后跟检索条件(如限制检索内容的条件),where部分是可选的。iv Order by 后跟分类准则(如取自空值数据如何给出的第一部分的表列名称表),order
27、 by 部分是可选的。v Group by后跟分组的字段或准则。vi Having后跟分组的查询条件。2选择指定的列我们可以在select关键字后跟一个或多个表列。星号指示Oracle显示表中的所有字段。我们使用同样的select语句,但指定了需要查看的一个表的某些字段:SQLselect table_name from user_tablesTABLE_NAMECUSTOMERSTATE2 rows selected3条件查询到目前为止,我们看到了select命令可用来查看表中所有表列(select *)或部分表列(select column1,column3)。如果读者只想看特定的数据行,怎么办呢?这就需要用wherer子句来解决了。例如:我们想要查看state_cd值为MA的所有客户,可以用命令:select last_name,state_cd,sales from customer where state_cd = MA;结果如下:LAST_NAME ST SALESTeplow MA 23445.67四上机内容1 显示EMP表中所有的部门号、职工名称和管理者号码:SELECT DEPTNO,ENAME,MGR FROM EMP;DEPTNOENAMEMGR-20SMI