《大型数据库系统》 课程实验指导书.doc

大型数据库系统课程实验指导书修订 刘忠民 刘晓瑢实验一 DDL语句及DML语句操作一、实验目的l 了解Oracle系统的组织结构和操作环境l 熟悉Oracle系统环境l 掌握创建、修改、删除表的不同方法二、实验环境一台计算机，安装了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 DATE DEFAULT SYSDATE, 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参照本表的cnoSc(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 - - - 张自立 d01 李春生 d02 查询的结果仍然是一个表的形式每一列的标题可以在SELECT子句中指定SELECT 列名1 标题1，列名2 标题2SELECT sno 学号，sname 姓名，dno 系 FROM student WHERE sex=男; 输出： 学号 姓名 系 - - - 张自立 d01 李春生 d14 在SELECT子句后的查询输出列表项可以是表达式。不但可以出现列名，还可以出现常量、函数以及四则运算等。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 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= 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,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 o=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 o=o) union (select sno from sc,course where cname=日语 and o=o) 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、AVG、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 o=o group by sno; 如果查询学分不到20的同学，如何查询？ Select sno,sum(credit) from sc,course where grade>60 and o=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 EXITSTS R：R非空时为真，否则为假 s IN R ó s=ANY R s NOT IN R ó s !=ALL R s >ALL R s >ANY R查询体育课不及格的学生名单Select snameFrom studentWhere sex=男 and sno in (select sno from sc where grade<60 and cno in (select cno from course where cname=体育); 相关子查询：子查询使用到父查询的当前记录值 例如：查询年龄在50岁以上、工资小于本部门平均工资的职工 Select ename,age,sal From emp e1 Where age>50 and sal< (select avg(sal) from emp where deptno=e1.deptno); 与统计结合的子查询查询本学期选课超过10门的学生人数Select count(*)From studentWhere sno in (select sno from sc where grade is null -成绩为空表示新选课程 group by sno -以sno值为分组对象 having count(cno)>10);查询各系年龄最小的同学名单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赵三16女ma2） course表 cnocnamecpnoccredit1数据库系统542数学分析null23信息系统导论134操作系统原理635数据结构746数据处理基础null47C语言633） sc表snocnograde98001187980012679800139098002295980023881 基本查询语句例：查询全体学生的学号与姓名2 带ORDER BY子句的查询例：查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。3 多表查询（连接操作） 例：查询选修2号课程且成绩在90分以上的所有学生。4 元组变量的使用查询例：查询与王林在同一个系学习的学生。5 查询语句中的集合操作例：求各个课程号及相应的选课人数。6 组函数与group by子句（having子句）例：查询选修了3门以上课程的学生学号7 嵌套查询例：查询选修了课程号为“数据库系统”的学生学号和姓名。实验三 权限及角色的设置操作一、实验目的：理解ORACLE中的各系统权限、对象权限及角色的含义，掌握用户的创建（包括给用户的空间分配、概况）和给用户授予适当的权限。二、实验环境一台计算机，安装了WINDOWS 2000 SERVER和Oracle 9i。Oracle帐户（system/123, sys/123, scott/tiger）三、预备知识 Oracle数据库系统中权限分为两类：系统级和对象级。1 统级权限：对某一特定类型实体上执行特定操作的权限l 创建对象Create any table| index|sequence|synonym|view|type|trigger：没有any表示在自己的模式中建立对象的权限，加上any表示在所有模式中建立对象的权限l 修改对象Alter any table|index|view|type|sequence 删除对象Drop any table|index|view|type|sequence 数据库管理Create session -连接到Oracle的权限Create tablespace -创建表空间的权限Create user -创建用户的权限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,update,delete,alter,index等3 创建用户 create user testuser -用户名为testuser identified by testpwd -口令为testpwd default tablespace users -缺省表空间使用users表空间 temporary tablespace temp; -临时表空间使用temp表空间 quota unlimited on users -用户使用表空间users的配额限制 quota 10m on temp -用户使用临时表空间temp的配额限制 profile manager； -指定用户使用的概况名为manager注意：创建新用户是必须为用户建立表空间配额，否则用户没有使用表空间的空间的权利；当用CREATE USER命令建立一个用户时，该用户的权利范围是空的。必须为用户指定一定的权限，用户才能操作数据库。如：为登录Oracle,用户必须有CREATE SESSION系统特权。4用户概况（环境文件） 用户概况决定对数据库资源的使用的限制Create profile manager limit sessions_per_user unlimited -可建立的会话数目 cpu_per_session unlimited -单位百分之一秒 cpu_per_call 3000 -执行一条SQL语句可用时间 connect_time 45 -会话建立后允许的持续时间(分) logical_reads_per_session default -会话期间可读数据库块数 logical_reads_per_call 1000 -执行一条SQL语句可读数据库块数 private_sga 15k composite_limit -总的资源限制 failed_login_attempts 3 -最多允许用户3次登录失败 password_life_time unlimited -可用天数 password_reuse_time unlimited -间隔天数 password_reuse_max unlimited -口令重新使用前必须改变的次数 password_lock_time unlimited； -锁定账户天数5与用户有关的视图 ALL_USERS视图：数据库所有用户的信息 USER_TS_QUOTAS：当前用户的表空间使用限额信息 DBA_TS_QUOTAS：所有用户表空间限额 DBA_SYS_PRIVS：所有用户权限信息5修改用户alter user * default role resource,connect 可以授予用户缺省角色6删除用户 drop user 用户名 cascade; *加cascade参数不经提示删除模式下实体7创建角色 create role rolename;8授权 授系统权限 grant system_priv | role, to user | role | public with admin option；加with admin option选项允许用户将得到的系统权限或角色授予其它用户或角色，给角色授权时不能加with admin option 授对象权限 grant objec_priv (column,), on object to user |role | public with grant option; object_priv包括：alter、delete、index、insert、select、update、 execute（对存储过程）、 references(在表上定义外键约束的权限） 或 grant all on object to user | role | public with grant option; 注意：为了给其他用户授予实体权限，此实体必须是你自己拥有的或你已被授予了对此实体的WITH GRANT OPTION权限。9收回权限revoke 语句10系统默认角色 DBA：所有系统权限 RESOURCE：给开发人员使用，具有有系统权限：create cluster, create index, create procedure, create sequence, create table, create trigger, create type。CONNECT：给最终用户使用，具有系统权限：create session,alter session,create cluster,create database link,create sequence,create sysnonym,create table,create view四、实验内容1 验证create session的作用2 在创建用户时，给用户赋予表空间的配额，测试系统权限create table与create any table的差异3 在创建用户时，不给用户赋予表空间的配额，给用户授予create table权限，测试用户能否建表4 测试在没有被授予select any table或select对象权限的情况下，用户能否查询数据5 测试在没有被授予insert any table或insert对象权限的情况下，用户能否插入新记录6 测试用户在没有被授予drop any table权限的情况下，能否删除自己模式下的表，能否删除自己在scott用户下建立的表。7 创建一个角色，给角色赋予create session、create any table、select any table、update any table、drop any table权限。再将角色授予一个有表空间配额的新用户，测试用户的权限。8 利用system/123用户登录数据库，参照下面的过程来验证用户在没有某个表的数据插入权限的情况下，利用存储过程（用户被授予了执行权限）来插入数据。a.创建存储过程 create or replace procedure insertstudent(sno number,sname varchar2,sex varchar2,birthday date,dno varchar2) as begin insert into system.student values(sno,sname,sex,birthday,dno); end;b.创建用户create user testuseridentified by testuserdefault tablespace userstemporary tablespace tempquota unlimited on usersquota 10M on temp;c. 给用户赋权grant create session to testuser; grant select on student to testuser;grant execute on insertstudent to testuser;d.利用testuser/testuser帐户登录SQL*Plus，做插入数据操作 insert into system.student values(23400,'lishi','男','10-12月-1979','d01'); 结果失败。 但执行下面的PL/SQL程序：begin system.insertstudent(23400,'lishi','男','10-12月-1979','d01');end; 结果插入数据成功。实验四 PL/SQL程序设计一、 实验目的1 掌握PL/SQL程序设计方法。2 能利用PL/SQL程序来解决具体的操作问题一、 实验环境一台计算机，安装了WINDOWS 2000 SERVER和Oracle 9i。Oracle帐户（system/123, sys/123, scott/tiger）二、 预备知识1.PL/SQLPL/SQL(Procedure Language/SQL)语言是Oracle对SQL语言的过化扩充，是一种完整的编程语言；将过程化语言的数据处理能力与SQL语言的访问数据库的能力有机地结合在一起；PL/SQL是一种“后台技术”，它不是用来编写面向用户界面的程序，主要用在编写服务器端程序，如编