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

《《大型数据库系统》 课程实验指导书.doc》由会员分享，可在线阅读，更多相关《《大型数据库系统》 课程实验指导书.doc（24页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、大型数据库系统课程实验指导书修订 刘忠民 刘晓瑢实验一 DDL语句及DML语句操作一、实验目的l 了解Oracle系统的组织结构和操作环境l 熟悉Oracle系统环境l 掌握创建、修改、删除表的不同方法二、实验环境一台计算机，安装了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 TABLE student (sno N

3、UMBER(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） 一个表有且只能有一个主码

4、 约束可以保证主码的值在表中唯一且非空 有些关系的主码由一个以上的属性组成 如：表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) CONST

5、RAINT 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）完整性约束并非都针对一个表，考

6、察这两个表 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

7、(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(

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

9、racle 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

10、 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;SELE

11、CT 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 * FRO

12、M student WHERE birthday01-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 ） 还是降

13、序（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

14、from student,dep where student.dno=dep.dno and dname=计算机系； 通常涉及到多个表的查询操作需要表的连接运算。上例中，连接的方式有两种：先按student.dno=dep.dno条件做两个表的连接运算，得到一张大的结果表，再在这张大表中按第二个条件dname=计算机系查询获得最后的结果：查询所有计算机系的学生。先按条件dname=计算机系查询系表，再进行连接。Oracle在执行SQL语句时，会进行优化处理，确定执行策略与步骤。 查询物理课不及格的学生名单，输出学生的学号，姓名和成绩 Select student.sno,sname,grade

15、 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=男 and sno in (select sno from sc where grade50 and sal10

16、);查询各系年龄最小的同学名单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数学分析nul

17、l23信息系统导论134操作系统原理635数据结构746数据处理基础null47C语言633） sc表snocnograde98001187980012679800139098002295980023881 基本查询语句例：查询全体学生的学号与姓名2 带ORDER BY子句的查询例：查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。3 多表查询（连接操作） 例：查询选修2号课程且成绩在90分以上的所有学生。4 元组变量的使用查询例：查询与王林在同一个系学习的学生。5 查询语句中的集合操作例：求各个课程号及相应的选课人数。6 组函数与group by子句（having子句）例：查

18、询选修了3门以上课程的学生学号7 嵌套查询例：查询选修了课程号为“数据库系统”的学生学号和姓名。实验三 权限及角色的设置操作一、实验目的：理解ORACLE中的各系统权限、对象权限及角色的含义，掌握用户的创建（包括给用户的空间分配、概况）和给用户授予适当的权限。二、实验环境一台计算机，安装了WINDOWS 2000 SERVER和Oracle 9i。Oracle帐户（system/123, sys/123, scott/tiger）三、预备知识 Oracle数据库系统中权限分为两类：系统级和对象级。1 统级权限：对某一特定类型实体上执行特定操作的权限l 创建对象Create any table|

19、 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 -创建角色的权限Manag

20、e 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 testuse

21、r -用户名为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注意：创建新用户是必须为用户建立表空间配额，否则用户没有使用表空间的空间的权利；当用CREAT

22、E 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_p

23、er_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

24、； -锁定账户天数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 |

25、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

26、 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：给最终用户使用，具有系统权限

27、：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对象权限的情况下，

28、用户能否查询数据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用户登录数据库，参照下面的过程来验证用户在没有某个表的数据插入权限的情况下，

29、利用存储过程（用户被授予了执行权限）来插入数据。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

30、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

31、/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是一种“后台技术”，它不是用来编写面向用户界面的程序，主要用在编写服务器端程序，如编