《大型数据库系统》课程实验指导书200802修订(new)

1《大型数据库系统》课程实验指导书修订刘忠民刘晓瑢2实验一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）3五种约束。•主码完整性约束（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=‘男’ANDageBETWEEN18AND60ORsex=‘女’ANDageBETWEEN18AND55）引用完整性约束（REFERENCES）完整性约束并非都针对一个表，考察这两个表Student(sno,sname,sex,birthday,dno)Dep(dno,dname,tel)•两个表中的dno属性存在联系，学生表中的dno字段的取值应该是系表中dno字段已经有的值，4这中约束关系就是引用完整性约束或外键约束，约束定义中所引用的另一关系表的属性必须是主键或定义了唯一性约束的次键。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．删除记录针对某个表做条件删除和删除所有记录5实验二查询语句运用一、实验目的熟练掌握查询语句的用法。二、实验环境一台计算机，安装了WINDOWS2000SERVER和Oracle9i。Oracle帐户（system/123,sys/123,scott/tiger）三、预备知识查询语句是数据库操作中使用最多的语句，SQL语言的查询语句能完成对数据库的复杂查询操作，查询语句的语法结构比较复杂，如下图：1、基本查询•它由三个基本子句构成：–SELECT子句指定查询哪些属性–FROM子句指定查询涉及到的所有表或视图6–WHERE子句列出所有的条件•查询的结果集可以看作是一个关系（结果关系）•SELECT*FROMstudentWHEREsex=‘男’ANDdno=‘d01’；2、SELECT子句——实现表的投影操作•SELECTsno,sname,dnoFROMstudentWHEREsex=‘男’；snosnamedno-----------------------980001张自立d01980033李春生d02……•查询的结果仍然是一个表的形式每一列的标题可以在SELECT子句中指定SELECT列名1标题1，列名2标题2……SELECTsno学号，sname姓名，dno系FROMstudentWHEREsex=‘男’;输出：学号姓名系-----------------------980001张自立d01980033李春生d14……•在SELECT子句后的查询输出列表项可以是表达式。不但可以出现列名，还可以出现常量、函数以及四则运算等。SELECTename员工名,sal*12年薪FROMemp;SELECTename员工名,sal*12年薪,‘元’RMBFROMemp;员工名年薪RMB-----------------------------许再兴96000元…………SELECTsname姓名,CEIL((SYSDATE-birthday)/365)年龄FROMstudent;3WHERE子句——查询语句中的选择操作•WHERE子句后跟条件表达式，参与运算的值可以是常量、系统函数及FROM子句中所声明的表中的列名7•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,sexfromstudent,depwherestudent.dno=dep.dnoanddname=‘计算机系’；•通常涉及到多个表的查询操作需要表的连接运算。上例中，连接的方式有两种：先按student.dno=dep.dno条件做两个表的连接运算，得到一张大的结果表，再在这张大表中按第二个条件dname=‘计算机系’查询获得最后的结果：查询所有计算机系的学生。先按条件dname=‘计算机系’查询系表，再进行连接。Oracle在执行SQL语句时，会进行优化处理，确定执行策略与步骤。8•查询物理课不及格的学生名单，输出学生的学号，姓名和成绩Selectstudent.sno,sname,gradefromstudent,sc,coursewherecname=‘物理’andgrade60andcourse.cno=sc.cnoandsc.sno=student.sno;6、元组变量•考察这样一个查询：在emp表中，查询职工王倩的直接领导信息，输出该领导的职工号和姓名。Selecteno,enamefromempwhereename=‘王倩’andmgr=eno;•存在问题：这个查询涉及到一个表的两个元组，eno,ename到底表示王倩所在元组信息，还是领导所在元组信息，显然无法确定•可以通过引入元组变量来解决这个问题Selecte2.eno,e2.enamefromempe1,empe2wheree1.ename=‘王倩’ande1.mgr=e2.eno;•所谓的元组变量实质上是：为同一个表起不同的别名，再利用别名来实现表的自连接运算Selecte1.eno职工号,e1.ename职工名,e2.eno领导号,e2.ename领导名fromempe1,empe2wheree1.ename=‘王倩’ande1.mgr=e2.eno;7、查询语句中的集合操作•UNION（并运算）、INTERSECT（交运算）、MINUS（差运算）•例如：查询同时选修了c01和c02两门课的学生，输出他们的学号(selectsnofromscwherecno=‘c01’)Intersect(selectsnofromscwherecno=‘c02’);•例如：查询选修了c01,但没有选修c02的学生(selectsnofromscwherecno=‘c01’)minus(selectsnofromscwherecno=‘c02’);•例如：查询选修了英语或日语的学生(selectsno9fromsc,coursewherecname=‘英语’andsc.cno=course.cno)union(selectsnofromsc,coursewherecname=‘日语’andsc.cno=course.cno)orderbysno;8、查询中的重值处理•查询的结果中有的时候会有重复的值，可以通过在select后加关键字distinct