《大型数据库系统》课程实验指导书02修订new.docx
《《大型数据库系统》课程实验指导书02修订new.docx》由会员分享，可在线阅读，更多相关《《大型数据库系统》课程实验指导书02修订new.docx（35页珍藏版）》请在冰豆网上搜索。
《大型数据库系统》课程实验指导书02修订new
《大型数据库系统》课程实验指导书
修订刘忠民刘晓瑢
实验一DDL语句及DML语句操作
一、实验目的
●了解Oracle系统的组织结构和操作环境
●熟悉Oracle系统环境
●掌握创建、修改、删除表的不同方法
二、实验环境
一台计算机，安装了WINDOWS2000SERVER和Oracle9i。
Oracle帐户（system/manager,sys/change_on_install,scott/tiger）
三、预备知识
表是组织数据的基本数据结构，又叫基本表或基表。
每张表都有一个名字，称为表名或关系名。
一张表可以由若干列组成，列名唯一，又称为属性名。
表中的一行称为一个元组或一条记录。
同一列的数据必须具有相同的数据类型。
表中的每一列值必须是不可分割的基本数据项。
SQL语言是一种综合的、通用的、功能极强的关系数据库语言，SQL语言包括三种类型的语句：
DDL（数据定义语言）、DML（数据操纵语言）和DCL（数据控制语言）。
其中DDL用来定义数据库中的对象（表、视图等），DML用来对数据库中的数据进行增删改及查询操作。
与表有关的DDL语句有建表、修改表、删除表。
建表语句CREATETABLE
•基本语法如下
CREATETABLE表名
（列名1数据类型[列完整性约束]，
列名1数据类型[列完整性约束]，
．．．．．．
[表完整性约束]）
[存储子句]
；
如：
CREATETABLEstudent
（snoNUMBER（6）,
snameCHAR（6）DEFAULT‘无名氏’,
sexCHAR
（2）,
birthdayDATEDEFAULTSYSDATE,
dnoCHAR（3））;
可以用DEFAULT方式给出列的默认值。
定义表的完整性约束是一项非常重要的工作，在定义表时一般都需要为表定义适当的约束，在Oracle中包括主码完整性约束（PRIMARYKEY）、唯一完整性约束（UNIQUE）、非空完整性约束（NOTNULL）、基于属性值的完整性约束（CHECK）、引用完整性约束或外键约束（REFERENCES）五种约束。
•主码完整性约束（PRIMARYKEY）
–一个表有且只能有一个主码
–约束可以保证主码的值在表中唯一且非空
–有些关系的主码由一个以上的属性组成
如：
表sc（sno,cno,grade）
定义这种主码必须采用表约束定义形式
CREATETABLEsc
（snoNUMBER（6）,
cnoCHAR（3）,
gradeNUMBER（3）,
CONSTRAINTsc1PRIMARYKEY（sno,cno）
）;
•唯一完整性约束（UNIQUE）
CREATETABLEdep
（dnoCHAR（3）CONSTRAINTd1PRIMARYKEY,
dnameCHAR（10）CONSTRAINTd2UNIQUE,
telCHAR（4）CONSTRAINTd3UNIQUE
）;
•在某些时候，需要定义属性组唯一性约束
UNIQUE（属性1，属性2）
•非空完整性约束（NOTNULL）
•基于属性值的完整性约束（CHECK）
AgeNUMBER
（2）CONSTRAINTe1CHECK（ageBETWEEN18AND60）,
利用表约束定义形式，可以定义涉及几个属性值的CHECK约束
CONSTRAINTE1CHECK（
sex=‘男’ANDageBETWEEN18AND60OR
sex=‘女’ANDageBETWEEN18AND55）
引用完整性约束（REFERENCES）
完整性约束并非都针对一个表，考察这两个表
Student（sno,sname,sex,birthday,dno）
Dep（dno,dname,tel）
•两个表中的dno属性存在联系，学生表中的dno字段的取值应该是系表中dno字段已经有的值，这中约束关系就是引用完整性约束或外键约束，约束定义中所引用的另一关系表的属性必须是主键或定义了唯一性约束的次键。
CREATETABLEstudent
（snoNUMBER（6）CONSTRAINTs1PRIMARYKEY,
snameCHAR（6）CONSTRAINTs2NOTNULL,
sexCHAR
（2）,
birthdayDATE,
dnoCHAR（3）CONSTRAINTs4REFERENCESdep（dno））;
当删除一个被引用值的纪录时，系统会报错。
但在引用完整性约束定义子句中，有一个“ONDELETECASCADE”选项，使用的结果会导致连带删除。
如可以这样定义：
dnoCHAR（3）CONSTRAINTs4REFERENCESdep（dno）ONDELETECASCADE
四、实验内容
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．删除记录
针对某个表做条件删除和删除所有记录
实验二查询语句运用
一、实验目的
熟练掌握查询语句的用法。
二、实验环境
一台计算机，安装了WINDOWS2000SERVER和Oracle9i。
Oracle帐户（system/123,sys/123,scott/tiger）
三、预备知识
查询语句是数据库操作中使用最多的语句，SQL语言的查询语句能完成对数据库的复杂查询操作，查询语句的语法结构比较复杂，如下图：
1、基本查询
•它由三个基本子句构成：
–SELECT子句指定查询哪些属性
–FROM子句指定查询涉及到的所有表或视图
–WHERE子句列出所有的条件
•查询的结果集可以看作是一个关系（结果关系）
•SELECT*
FROMstudent
WHEREsex=‘男’ANDdno=‘d01’；
2、SELECT子句——实现表的投影操作
•SELECTsno,sname,dnoFROMstudentWHEREsex=‘男’；
snosnamedno
-----------------------
980001张自立d01
980033李春生d02
……
•查询的结果仍然是一个表的形式
每一列的标题可以在SELECT子句中指定
SELECT列名1标题1，列名2标题2……
SELECTsno学号，sname姓名，dno系
FROMstudent
WHEREsex=‘男’;
输出：
学号姓名系
-----------------------
980001张自立d01
980033李春生d14
……
•在SELECT子句后的查询输出列表项可以是表达式。
不但可以出现列名，还可以出现常量、函数以及四则运算等。
SELECTename员工名,sal*12年薪FROMemp;
SELECTename员工名,sal*12年薪,‘元’RMBFROMemp;
员工名年薪RMB
-----------------------------
许再兴96000元
…………
SELECTsname姓名,CEIL（（SYSDATE-birthday）/365）年龄FROMstudent;
3WHERE子句——查询语句中的选择操作
•WHERE子句后跟条件表达式，参与运算的值可以是常量、系统函数及FROM子句中所声明的表中的列名
•SELECTsname,birthdayFROMstudentWHEREsex=‘女’ANDbirthdayBETWEEN’01-1月-79‘AND’31-12月-80‘;
•SELECT*FROMstudentWHEREbirthday<’01-1月-1980‘;
•SELECTdname,telFROMdepWHEREdnameIN（‘计算机系’,‘外语系’,‘中文系’）；
•SELECTcnoFROMscWHEREsno=980001ANDgradeISNULL;
•SELECT*FROMstudentWHEREsnameLIKE‘王%’
4、ORDERBY子句
•SELECT语句通过ORDERBY子句实现查询结果的排序输出
–Selectename,agefromempwheresex=‘男’orderbyage;
•可以指定排序是按升序（ASC）还是降序（DESC），还可以指定多个排序项（可以是表达式）
–Selectdeptno,ename,age,salfromemporderbydeptno,agedesc,sal;
•还可以将列标题名或输出项序号作为排序项
–Selectename,sal*12年薪fromemporderby2;
–Selectename,sal*12annual_incomefromemporderbyannual_income;
5、多表查询与连接操作
•有的时候一个查询内容会涉及到多个基表，这时可以通过FROM子句将所有的表都罗列进去
Selectsno,sname,sex
fromstudent,dep
wherestudent.dno=dep.dno
anddname=‘计算机系’；
•通常涉及到多个表的查询操作需要表的连接运算。
上例中，连接的方式有两种：
先按student.dno=dep.dno条件做两个表的连接运算，得到一张大的结果表，再在这张大表中按第二个条件dname=‘计算机系’查询获得最后的结果：
查询所有计算机系的学生。
先按条件dname=‘计算机系’查询系表，再进行连接。
Oracle在执行SQL语句时，会进行优化处理，确定执行策略与步骤。
•查询物理课不及格的学生名单，输出学生的学号，姓名和成绩
Selectstudent.sno,sname,grade
fromstudent,sc,course
wherecname=‘物理’and
grade<60and
o=oand
sc.sno=student.sno;
6、元组变量
•考察这样一个查询：
在emp表中，查询职工王倩的直接领导信息，输出该领导的职工号和姓名。
Selecteno,ename
fromemp
whereename=‘王倩’andmgr=eno;
•存在问题：
这个查询涉及到一个表的两个元组，eno,ename到底表示王倩所在元组信息，还是领导所在元组信息，显然无法确定
•可以通过引入元组变量来解决这个问题
Selecte2.eno,e2.enamefromempe1,empe2wheree1.ename=‘王倩’ande1.mgr=e2.eno;
•所谓的元组变量实质上是：
为同一个表起不同的别名，再利用别名来实现表的自连接运算
Selecte1.eno职工号,e1.ename职工名,
e2.eno领导号,e2.ename领导名
fromempe1,empe2
wheree1.ename=‘王倩’ande1.mgr=e2.eno;
7、查询语句中的集合操作
•UNION（并运算）、INTERSECT（交运算）、MINUS（差运算）
•例如：
查询同时选修了c01和c02两门课的学生，输出他们的学号
（selectsnofromscwherecno=‘c01’）
Intersect
（selectsnofromscwherecno=‘c02’）;
•例如：
查询选修了c01,但没有选修c02的学生
（selectsnofromscwherecno=‘c01’）
minus
（selectsnofromscwherecno=‘c02’）;
•例如：
查询选修了英语或日语的学生
（selectsno
fromsc,course
wherecname=‘英语’ando=o）
union
（selectsno
fromsc,course
wherecname=‘日语’ando=o）
orderbysno;
8、查询中的重值处理
•查询的结果中有的时候会有重复的值，可以通过在select后加关键字distinct来去掉
Selectcnamefromcourse;
Selectallcnamefromcourse;
Selectdistinctcnamefromcourse;
（selectsnofromscwherecno=‘c01’）
Unionall
（selectsnofromscwherecno=‘c02’）;
9、组函数与聚合操作
•组函数，也称为统计函数：
SUM、AVG、MIN、MAX、COUNT
selectsum（sal）fromemp;
selectmax（sal）,min（sal）,avg（sal）fromemp
wheredeptno=‘d01’;
selectcount（*）
fromstudent,dep
wheredname=‘计算机系’
anddep.dno=student.dno;
selectcount（deptno）fromemp;
selectcount（distinctdeptno）fromemp;
selectcount（distinctcno）fromscwheregradeisnull;
•为了完成这样一个查询：
查询每个系的学生总数，而不是学生总数或某个系的学生总数。
在查询语句中要使用GROUPBY子句，可以根据指定的方式分组，然后与组函数配合为每个组返回一个结果。
selectdno,count（*）fromstudentgroupbydno;
dnocount（*）
----------------------
d011200
d02800
…………
*只有出现在GROUPBY子句中的列名才能与组函数一起出现在SELECT子句的输出项中
•分组子句可以指定多个分组项，例如：
查询各系c01课程最高成绩和最低成绩，但要求每个系男女生分开统计
Selectdno,sex,max（grade）,min（grade）
Fromstudent,sc
Wherecno=‘c01’andsc.sno=student.sno
Groupbydno,sex;
dnosexmax（grade）min（grade）
-------------------------------------
d01男10045
d01女9861
d02男9460
d02女9047
…………
•涉及到分组统计结果选择条件的查询
如：
查询每个学生获得的学分
Selectsno,sum（credit）
fromsc,course
wheregrade>60ando=o
groupbysno;
如果查询学分不到20的同学，如何查询？
Selectsno,sum（credit）
fromsc,course
wheregrade>60ando=o
groupbysnohavingsum（credit）<20;
10、子查询
•出现在查询语句或其它语句中的查询称为子查询
•子查询引入可以使查询的解决更容易更清晰
例如：
查询物理系学生名单
selectsname
fromstudent,dep
wheredname=‘物理系’
anddep.dno=student.dno;
也可以使用子查询完成
selectsname
fromstudent
wheredno=（selectdno
fromdep
wheredname=‘物理系’）;
集合运算符与子查询的配合使用
•EXISTS、IN、ALL、ANY
•EXITSTSR：
R非空时为真，否则为假
•sINRs=ANYR
•sNOTINRs!
=ALLR
•s>ALLR
•s>ANYR
查询体育课不及格的学生名单
Selectsname
Fromstudent
Wheresex=‘男’andsnoin
（selectsno
fromsc
wheregrade<60andcnoin
（selectcno
fromcourse
wherecname=‘体育’））;
•相关子查询：
子查询使用到父查询的当前记录值
例如：
查询年龄在50岁以上、工资小于本部门平均工资的职工
Selectename,age,sal
Fromempe1
Whereage>50andsal<
（selectavg（sal）
fromemp
wheredeptno=e1.deptno）;
•与统计结合的子查询
查询本学期选课超过10门的学生人数
Selectcount（*）
Fromstudent
Wheresnoin
（selectsno
fromsc
wheregradeisnull--成绩为空表示新选课程
groupbysno--以sno值为分组对象
havingcount（cno）>10）;
查询各系年龄最小的同学名单
Selectsname,birthday,dno
Fromstudent
Where（dno,birthday）in
（selectdno,max（birthday）
fromstudent
groupbydno）;
**上例中子查询的结果是元组的集合，查询用到了有关元组的集合运算
四、实验内容
对以下三张表进行查询
1）stud表
sno
sname
sage
ssex
sdept
98001
钱横
18
男
cs
98002
王林
19
女
cs
98003
李民
20
男
is
98004
赵三
16
女
ma
2）course表
cno
cname
cpno
ccredit
1
数据库系统
5
4
2
数学分析
null
2
3
信息系统导论
1
3
4
操作系统原理
6
3
5
数据结构
7
4
6
数据处理基础
null
4
7
C语言
6
3
3）sc表
sno
cno
grade
98001
1
87
98001
2
67
98001
3
90
98002
2
95
98002
3
88
1．基本查询语句
例：
查询全体学生的学号与姓名
2．带ORDERBY子句的查询
例：
查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。
3．多表查询（连接操作）
例：
查询选修2号课程且成绩在90分以上的所有学生。
4．元组变量的使用查询
例：
查询与‘王林’在同一个系学习的学生。
5．查询语句中的集合操作
例：
求各个课程号及相应的选课人数。
6．组函数与groupby子句（having子句）
例：
查询选修了3门以上课程的学生学号
7．嵌套查询
例：
查询选修了课程号为“数据库系统”的学生学号和姓名。
实验三权限及角色的设置操作
一、实验目的：
理解ORACLE中的各系统权限、对象权限及角色的含义，掌握用户的创建（包括给用户的空间分配、概况）和给用户授予适当的权限。
二、实验环境
一台计算机，安装了WINDOWS2000SERVER和Oracle9i。
Oracle帐户（system/123,sys/123,scott/tiger）
三、预备知识
Oracle数据库系统中权限分为两类：
系统级和对象级。
1．统级权限：
对某一特定类型实体上执行特定操作的权限
●创建对象
Create[any]{table|index|sequence|synonym|view|type|trigger}：
没有any表示在自己的模式中建立对象的权限，加上any表示在所有模式中建立对象的权限
●修改对象
Alterany{table|index|view|type|sequence}
•删除对象
Dropany{table|index|view|type|sequence}
•数据库管理
Createsession--连接到Oracle的权限
Createtablespace--创建表空间的权限
Createuser--创建用户的权限
Createprofile--创建概况
Createrole--创建角色的权限
Managetablespace--管理表空间状态
Unlimitedtablespace--允许在任何表空间中使用无限量存储空间
Alter{user|profile|tablespace|anyrole}
Drop{user|profile|tablespace|anyrole}Droptablespace
Grantanyprivilege--授予任何系统级权限
Grantanyrole--授予任何角色
2．对象级权限：
针对某个数据库对象（表、视图等）的操作权限：
select,insert,upda
升级会员 