《大型数据库系统》课程实验指导书修订(new)

《大型数据库系统》课程实验指导书修订刘忠民刘晓瑢实验一DDL语句及DML语句操作一、实验目的●了解Oracle系统的组织结构和操作环境●熟悉Oracle系统环境●掌握创建、修改、删除表的不同方法二、实验环境一台计算机，安装了WINDOWS 2000 SERVER和Oracle 9i。

Oracle帐户（system/manager, sys/change_on_install, scott/tiger）三、预备知识表是组织数据的基本数据结构，又叫基本表或基表。

每张表都有一个名字，称为表名或关系名。

一张表可以由若干列组成，列名唯一，又称为属性名。

表中的一行称为一个元组或一条记录。

同一列的数据必须具有相同的数据类型。

表中的每一列值必须是不可分割的基本数据项。

SQL语言是一种综合的、通用的、功能极强的关系数据库语言，SQL语言包括三种类型的语句：DDL（数据定义语言）、DML（数据操纵语言）和DCL（数据控制语言）。

其中DDL用来定义数据库中的对象（表、视图等），DML用来对数据库中的数据进行增删改及查询操作。

与表有关的DDL语句有建表、修改表、删除表。

建表语句CREATE TABLE•基本语法如下CREATE TABLE 表名（列名1 数据类型[列完整性约束]，列名1 数据类型[列完整性约束]，．．．．．．[表完整性约束])[存储子句]；如：CREATE TABLE student(sno NUMBER(6),sname CHAR(6) DEFAULT ‘无名氏’,sex CHAR(2),birthday DA TE DEFAULT SYSDA TE,dno CHAR(3));可以用DEFAULT方式给出列的默认值。

定义表的完整性约束是一项非常重要的工作，在定义表时一般都需要为表定义适当的约束，在Oracle中包括主码完整性约束（PRIMARY KEY）、唯一完整性约束（UNIQUE）、非空完整性约束（NOT NULL）、基于属性值的完整性约束（CHECK）、引用完整性约束或外键约束（REFERENCES）五种约束。

•主码完整性约束（PRIMARY KEY）–一个表有且只能有一个主码–约束可以保证主码的值在表中唯一且非空–有些关系的主码由一个以上的属性组成如：表sc(sno,cno,grade)定义这种主码必须采用表约束定义形式CREATE TABLE sc(sno NUMBER(6),cno CHAR(3),grade NUMBER(3),CONSTRAINT sc1 PRIMARY KEY(sno,cno));•唯一完整性约束（UNIQUE）CREATE TABLE dep(dno CHAR(3) CONSTRAINT d1 PRIMARY KEY,dname CHAR(10) CONSTRAINT d2 UNIQUE,tel CHAR(4) CONSTRAINT d3 UNIQUE);•在某些时候，需要定义属性组唯一性约束UNIQUE（属性1，属性2）•非空完整性约束（NOT NULL）•基于属性值的完整性约束（CHECK）Age NUMBER(2) CONSTRAINT e1 CHECK(age BETWEEN 18 AND 60),利用表约束定义形式，可以定义涉及几个属性值的CHECK约束CONSTRAINT E1 CHECK(sex=‘男’ AND age BETWEEN 18 AND 60 ORsex=‘女’ AND age BETWEEN 18 AND 55）引用完整性约束（REFERENCES）完整性约束并非都针对一个表，考察这两个表Student(sno,sname,sex,birthday,dno)Dep(dno,dname,tel)•两个表中的dno属性存在联系，学生表中的dno字段的取值应该是系表中dno字段已经有的值，这中约束关系就是引用完整性约束或外键约束，约束定义中所引用的另一关系表的属性必须是主键或定义了唯一性约束的次键。

CREATE TABLE student(sno NUMBER(6) CONSTRAINT s1 PRIMARY KEY,sname CHAR(6) CONSTRAINT s2 NOT NULL,sex CHAR(2),birthday DATE ,dno CHAR(3) CONSTRAINT s4 REFERENCES dep(dno));当删除一个被引用值的纪录时，系统会报错。

但在引用完整性约束定义子句中，有一个“ON DELETE CASCADE”选项，使用的结果会导致连带删除。

如可以这样定义：dno CHAR(3) CONSTRAINT s4 REFERENCES dep(dno) ON DELETE CASCADE四、实验内容1．查看数据库的系统文件。

2．熟悉Oracle系统环境3．用OEM及SQL语句两种方式创建以下表◆Student(sno,sname,sex,sage,sdept)：学生表sno是主码，ssex要有CHECK约束◆Course(cno,cname,cpno,credit)：课程表其中，cno是主码, cpno参照本表的cno◆Sc(sno,cno,grade)：选课表主码为（sno,cno）, sno和cno都要有外键约束,4．修改表定义向student表中增加sentrance“入学时间”列4．插入记录给每个表中插入正确的两三条记录，另外插入一些错误的记录验证各表的完整性约束5．修改数据针对某个表做不带条件的修改、带条件的修改语句各一条6．删除记录针对某个表做条件删除和删除所有记录实验二查询语句运用一、实验目的熟练掌握查询语句的用法。

二、实验环境一台计算机，安装了WINDOWS 2000 SERVER和Oracle 9i。

Oracle帐户（system/123, sys/123, scott/tiger）三、预备知识查询语句是数据库操作中使用最多的语句，SQL语言的查询语句能完成对数据库的复杂查询操作，查询语句的语法结构比较复杂，如下图：1、基本查询•它由三个基本子句构成：–SELECT子句指定查询哪些属性–FROM子句指定查询涉及到的所有表或视图–WHERE子句列出所有的条件•查询的结果集可以看作是一个关系（结果关系）•SELECT *FROM studentWHERE sex=‘男’ AND dno=‘d01’；2 、SELECT子句——实现表的投影操作•SELECT sno,sname,dno FROM student WHERE sex=‘男’；sno sname dno------- ---------- ------980001 张自立d01980033 李春生d02……•查询的结果仍然是一个表的形式每一列的标题可以在SELECT子句中指定SELECT 列名1 标题1，列名2 标题2……SELECT sno 学号，sname 姓名，dno 系FROM studentWHERE sex=‘男’;输出：学号姓名系------- ---------- ------980001 张自立d01980033 李春生d14……•在SELECT子句后的查询输出列表项可以是表达式。

不但可以出现列名，还可以出现常量、函数以及四则运算等。

SELECT ename 员工名,sal*12 年薪FROM emp;SELECT ename 员工名,sal*12 年薪,‘元’ RMB FROM emp;员工名年薪RMB----------- ---------- --------许再兴96000 元…………SELECT sname 姓名,CEIL((SYSDA TE-birthday)/365) 年龄FROM student;3 WHERE子句——查询语句中的选择操作•WHERE子句后跟条件表达式，参与运算的值可以是常量、系统函数及FROM子句中所声明的表中的列名•SELECT sname,birthday FRO M student WHERE sex=‘女’ AND birthday BETWEEN ’01-1月-79‘ AND ’31-12月-80‘;•SELECT * FROM student WHERE birthday<’01-1月-1980‘;•SELECT dname,tel FROM dep WHERE dname IN (‘计算机系’,‘外语系’,‘中文系’）；•SELECT cno FROM sc WHERE sno=980001 AND grade IS NULL;•SELECT * FROM student WHERE sname LIKE ‘王%’4、ORDER BY子句•SELECT语句通过ORDER BY子句实现查询结果的排序输出–Select ename,age from emp where sex=‘男’ order by age;•可以指定排序是按升序（ASC ）还是降序（DESC ），还可以指定多个排序项（可以是表达式）–Select deptno,ename,age,sal from emp order by deptno,age desc,sal;•还可以将列标题名或输出项序号作为排序项–Select ename,sal*12 年薪from emp order by 2;–Select ename,sal*12 annual_income from emp order by annual_income;5、多表查询与连接操作•有的时候一个查询内容会涉及到多个基表，这时可以通过FROM子句将所有的表都罗列进去Select sno,sname,sexfrom student,depwhere student.dno=dep.dnoand dname=‘计算机系’；•通常涉及到多个表的查询操作需要表的连接运算。

上例中，连接的方式有两种：先按student.dno=dep.dno条件做两个表的连接运算，得到一张大的结果表，再在这张大表中按第二个条件dname=‘计算机系’查询获得最后的结果：查询所有计算机系的学生。

先按条件dname=‘计算机系’查询系表，再进行连接。

Oracle在执行SQL语句时，会进行优化处理，确定执行策略与步骤。

•查询物理课不及格的学生名单，输出学生的学号，姓名和成绩Select student.sno,sname,gradefrom student,sc,coursewhere cname=‘物理’ andgrade<60 ando=o andsc.sno=student.sno;6、元组变量•考察这样一个查询：在emp表中，查询职工王倩的直接领导信息，输出该领导的职工号和姓名。