《大型数据库系统》课程实验指导书02修订new.docx

1、大型数据库系统课程实验指导书02修订new大型数据库系统课程实验指导书修订 刘忠民 刘晓瑢实验一 DDL语句及DML语句操作一、实验目的 了解Oracle系统的组织结构和操作环境 熟悉Oracle系统环境 掌握创建、修改、删除表的不同方法二、实验环境一台计算机，安装了WINDOWS 2000 SERVER和Oracle 9i。Oracle帐户（system/manager, sys/change_on_install, scott/tiger）三、预备知识表是组织数据的基本数据结构，又叫基本表或基表。每张表都有一个名字，称为表名或关系名。一张表可以由若干列组成，列名唯一，又称为属性名。表中的一

2、行称为一个元组或一条记录。同一列的数据必须具有相同的数据类型。表中的每一列值必须是不可分割的基本数据项。SQL语言是一种综合的、通用的、功能极强的关系数据库语言，SQL语言包括三种类型的语句：DDL（数据定义语言）、DML（数据操纵语言）和DCL（数据控制语言）。其中DDL用来定义数据库中的对象（表、视图等），DML用来对数据库中的数据进行增删改及查询操作。与表有关的DDL语句有建表、修改表、删除表。建表语句CREATE TABLE 基本语法如下 CREATE TABLE 表名（列名1 数据类型 列完整性约束， 列名1 数据类型 列完整性约束， 表完整性约束) 存储子句；如：CREATE TA

3、BLE student (sno NUMBER(6), sname CHAR(6) DEFAULT 无名氏, sex CHAR(2), birthday DATE DEFAULT SYSDATE, dno CHAR(3);可以用DEFAULT方式给出列的默认值。定义表的完整性约束是一项非常重要的工作，在定义表时一般都需要为表定义适当的约束，在Oracle中包括主码完整性约束（PRIMARY KEY）、唯一完整性约束（UNIQUE）、非空完整性约束（NOT NULL）、基于属性值的完整性约束（CHECK）、引用完整性约束或外键约束（REFERENCES）五种约束。 主码完整性约束（PRIMARY

4、 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,

5、 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） 引用完整性约束（REFERENC

6、ES）完整性约束并非都针对一个表，考察这两个表 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), birthd

7、ay 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是主码，ss

8、ex要有CHECK约束Course(cno,cname,cpno,credit)：课程表 其中，cno是主码, cpno参照本表的cnoSc(sno,cno,grade)：选课表主码为（sno,cno）, sno和cno都要有外键约束,4 修改表定义向student表中增加sentrance“入学时间”列4插入记录 给每个表中插入正确的两三条记录，另外插入一些错误的记录验证各表的完整性约束 5修改数据 针对某个表做不带条件的修改、带条件的修改语句各一条 6删除记录 针对某个表做条件删除和删除所有记录实验二 查询语句运用一、 实验目的熟练掌握查询语句的用法。二、实验环境 一台计算机，安装了WIN

9、DOWS 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子句实现表的投影操作 SE

10、LECT sno,sname,dno FROM student WHERE sex=男； sno sname dno - - - 980001 张自立 d01 980033 李春生 d02 查询的结果仍然是一个表的形式每一列的标题可以在SELECT子句中指定SELECT 列名1 标题1，列名2 标题2SELECT sno 学号，sname 姓名，dno 系 FROM student WHERE sex=男; 输出： 学号 姓名 系 - - - 980001 张自立 d01 980033 李春生 d14 在SELECT子句后的查询输出列表项可以是表达式。不但可以出现列名，还可以出现常量、函数以及

11、四则运算等。SELECT ename 员工名,sal*12 年薪 FROM emp;SELECT ename 员工名,sal*12 年薪,元 RMB FROM emp; 员工名 年薪 RMB - - - 许再兴 96000 元SELECT sname 姓名,CEIL(SYSDATE-birthday)/365) 年龄 FROM student;3 WHERE子句查询语句中的选择操作 WHERE子句后跟条件表达式，参与运算的值可以是常量、系统函数及FROM子句中所声明的表中的列名 SELECT sname,birthday FROM student WHERE sex=女 AND birthda

12、y BETWEEN 01-1月-79 AND 31-12月-80; SELECT * FROM student WHERE birthday01-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 fro

13、m 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、多表查询与连接操作 有的时候一个查询内

14、容会涉及到多个基表，这时可以通过FROM子句将所有的表都罗列进去Select sno,sname,sexfrom student,dep where student.dno=dep.dno and dname=计算机系； 通常涉及到多个表的查询操作需要表的连接运算。上例中，连接的方式有两种：先按student.dno=dep.dno条件做两个表的连接运算，得到一张大的结果表，再在这张大表中按第二个条件dname=计算机系查询获得最后的结果：查询所有计算机系的学生。先按条件dname=计算机系查询系表，再进行连接。Oracle在执行SQL语句时，会进行优化处理，确定执行策略与步骤。 查询物理课不

15、及格的学生名单，输出学生的学号，姓名和成绩 Select student.sno,sname,grade from student,sc,course where cname=物理 and grade60 and o=o group by sno; 如果查询学分不到20的同学，如何查询？ Select sno,sum(credit) from sc,course where grade60 and o=o group by sno having sum(credit)ALL R s ANY R查询体育课不及格的学生名单Select snameFrom studentWhere sex=男 an

16、d sno in (select sno from sc where grade50 and sal10);查询各系年龄最小的同学名单Select sname,birthday,dnoFrom studentWhere (dno,birthday) in (select dno,max(birthday) from student group by dno);*上例中子查询的结果是元组的集合，查询用到了有关元组的集合运算四、实验内容对以下三张表进行查询1） stud表 snosnamesagessexsdept98001钱横18男cs98002王林19女cs98003李民20男is98004赵

17、三16女ma2） course表 cnocnamecpnoccredit1数据库系统542数学分析null23信息系统导论134操作系统原理635数据结构746数据处理基础null47C语言633） sc表snocnograde98001187980012679800139098002295980023881 基本查询语句例：查询全体学生的学号与姓名2 带ORDER BY子句的查询例：查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。3 多表查询（连接操作） 例：查询选修2号课程且成绩在90分以上的所有学生。4 元组变量的使用查询例：查询与王林在同一个系学习的学生。5 查询语句

18、中的集合操作例：求各个课程号及相应的选课人数。6 组函数与group by子句（having子句）例：查询选修了3门以上课程的学生学号7 嵌套查询例：查询选修了课程号为“数据库系统”的学生学号和姓名。实验三 权限及角色的设置操作一、实验目的：理解ORACLE中的各系统权限、对象权限及角色的含义，掌握用户的创建（包括给用户的空间分配、概况）和给用户授予适当的权限。二、实验环境一台计算机，安装了WINDOWS 2000 SERVER和Oracle 9i。Oracle帐户（system/123, sys/123, scott/tiger）三、预备知识 Oracle数据库系统中权限分为两类：系统级和对

19、象级。1 统级权限：对某一特定类型实体上执行特定操作的权限 创建对象Create any table| index|sequence|synonym|view|type|trigger：没有any表示在自己的模式中建立对象的权限，加上any表示在所有模式中建立对象的权限 修改对象Alter any table|index|view|type|sequence 删除对象Drop any table|index|view|type|sequence 数据库管理Create session -连接到Oracle的权限Create tablespace -创建表空间的权限Create user -创建

20、用户的权限Create profile -创建概况Create role -创建角色的权限Manage tablespace -管理表空间状态Unlimited tablespace -允许在任何表空间中使用无限量存储空间Alter user|profile|tablespace|any roleDrop user|profile|tablespace|any roleDrop tablespaceGrant any privilege -授予任何系统级权限Grant any role -授予任何角色2 对象级权限：针对某个数据库对象（表、视图等）的操作权限： select,insert,upda