《大型数据库系统》课程实验指导书修订(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 OR

sex=‘女’ 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 student

WHERE sex=‘男’ AND dno=‘d01’；

2 、SELECT子句——实现表的投影操作

?SELECT sno,sname,dno FROM student WHERE sex=‘男’；

sno sname dno

------- ---------- ------

980001 张自立d01

980033 李春生d02

……

?查询的结果仍然是一个表的形式

每一列的标题可以在SELECT子句中指定

SELECT 列名1 标题1，列名2 标题2……

SELECT sno 学号，sname 姓名，dno 系

FROM student

WHERE sex=‘男’;

输出：

学号姓名系

------- ---------- ------

980001 张自立d01

980033 李春生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,sex

from student,dep

where student.dno=dep.dno

and dname=‘计算机系’；

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

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

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

?查询物理课不及格的学生名单，输出学生的学号，姓名和成绩

Select student.sno,sname,grade

from student,sc,course

where cname=‘物理’ and

grade<60 and

https://www.360docs.net/doc/0e10313739.html,o=https://www.360docs.net/doc/0e10313739.html,o and

sc.sno=student.sno;

6、元组变量

?考察这样一个查询：在emp表中，查询职工王倩的直接领导信息，输出该领导的职工号和姓名。

Select eno,ename

from emp

where ename=‘王倩’ and mgr=eno;

?存在问题：这个查询涉及到一个表的两个元组，eno,ename到底表示王倩所在元组信息，还是领导所在元组信息，显然无法确定

?可以通过引入元组变量来解决这个问题

Select e2.eno,e2.ename from emp e1,emp e2 where e1.ename=‘王倩’ and e1.mgr=e2.eno;

?所谓的元组变量实质上是：为同一个表起不同的别名，再利用别名来实现表的自连接运算

Select e1.eno 职工号,e1.ename 职工名,

e2.eno 领导号,e2.ename 领导名

from emp e1,emp e2

where e1.ename=‘王倩’ and e1.mgr=e2.eno;

7、查询语句中的集合操作

?UNION（并运算）、INTERSECT（交运算）、MINUS（差运算）

?例如：查询同时选修了c01和c02两门课的学生，输出他们的学号

(select sno from sc where cno=‘c01’)

Intersect

(select sno from sc where cno=‘c02’);

?例如：查询选修了c01,但没有选修c02的学生

(select sno from sc where cno=‘c01’)

minus

(select sno from sc where cno=‘c02’);

?例如：查询选修了英语或日语的学生

(select sno

from sc,course

where cname=‘英语’ and https://www.360docs.net/doc/0e10313739.html,o=course.c no)

union

(select sno

from sc,course

where cname=‘日语’ and https://www.360docs.net/doc/0e10313739.html,o=course.c no)

order by sno;

8、查询中的重值处理

?查询的结果中有的时候会有重复的值，可以通过在select后加关键字distinct来去掉

Select cname from course;

Select all cname from course;

Select distinct cname from course;

(select sno from sc where cno=‘c01’)

Union all

(select sno from sc where cno=‘c02’);

9、组函数与聚合操作

?组函数，也称为统计函数：SUM、A VG、MIN、MAX、COUNT

select sum(sal) from emp;

select max(sal),min(sal),avg(sal) from emp

where deptno=‘d01’;

select count(*)

from student,dep

where dname=‘计算机系’

and dep.dno=student.dno;

select count(deptno) from emp;

select count(distinct deptno) from emp;

select count(distinct cno) from sc where grade is null;

?为了完成这样一个查询：查询每个系的学生总数，而不是学生总数或某个系的学生总数。在查询语句中要使用GROUP BY子句，可以根据指定的方式分组，然后与组函数配合为每个组返回一个结果。

select dno,count(*) from student group by dno;

dno count(*)

------- ---------------

d01 1200

d02 800

…………

*只有出现在GROUP BY子句中的列名才能与组函数一起出现在SELECT子句的输出项中

?分组子句可以指定多个分组项，例如：查询各系c01课程最高成绩和最低成绩，但要求每个系男女

生分开统计

Select dno,sex,max(grade),min(grade)

From student,sc

Where cno=‘c01’ and sc.sno=student.sno

Group by dno,sex;

dno sex max(grade) min(grade) ----- ----- -------------- -------------

d01 男100 45 d01 女98 61 d02 男94 60 d02 女90 47 …………

?涉及到分组统计结果选择条件的查询

如：查询每个学生获得的学分

Select sno,sum(credit)

from sc,course

where grade>60 and https://www.360docs.net/doc/0e10313739.html,o=https://www.360docs.net/doc/0e10313739.html,o

group by sno;

如果查询学分不到20的同学，如何查询？Select sno,sum(credit)

from sc,course

where grade>60 and https://www.360docs.net/doc/0e10313739.html,o=https://www.360docs.net/doc/0e10313739.html,o

group by sno having sum(credit)<20;

10、子查询

?出现在查询语句或其它语句中的查询称为子查询?子查询引入可以使查询的解决更容易更清晰

例如：查询物理系学生名单

select sname

from student,dep

where dname=‘物理系’

and dep.dno=student.dno;

也可以使用子查询完成

select sname

from student

where dno=(select dno

from dep

where dname=‘物理系’);集合运算符与子查询的配合使用

?EXISTS、IN、ALL、ANY