《SQL上机实验报告》word版.doc

上 机 实 验 报 告课程名称： 数据库原理及技术上机实验 设计题目： 学 院： 应 用 技 术 学 院 专 业： 电 子 信 息 工 程 年 级： 信 息 081 学生姓名： 指导教师： 日 期： 2011年11月 教 务 处 制昆明理工大学应用技术学院实验报告（ 2011 2012 学年 第 一 学期 ）课程名称：数据库原理及技术 实验室：4#机房 2011年 月 日年级、专业、班08级电子信息工程（2）班学号200811513274 姓 名杨明翠成绩实验项目名 称指导教师游进国教师评语该同学是否了解实验原理： A.了解B.基本了解C.不了解该同学的实验能力： A.强 B.中等 C.差 该同学的实验是否达到要求 ： A.达到 B.基本达到C.未达到实验报告是否规范： A.规范 B.基本规范C.不规范实验过程是否详细记录： A.详细 B.一般 C.没有 注：5个A为优，5个B为中，介于二者间为良，5个C为不及格，3个以上为及格。教师签名： 年 月 日目录一、实验目的与要求2二、实验相关概念与技术概述3三、实验环境安装6四、实验结果8第一次实验：8第二次实验10第三次实验14第四次实验18五、实验结论24一、实验目的与要求1、1) 安装Oracle数据库 2）创建表空间shebao 3）创建表AB012、 1)创建以上关系表,关系表的属性的数据类型自己设计; 2)修改学生关系表S,增加一个属性:系别(Department); 3)为以上四个关系表的属性设计和增加主键; 4)为选课关系表增加主外键约束,引用学生关系表和课程关系表; 5)在学生表的S#上和SName上分别创建两个索引.3、1）在学生关系表中查询学号为081001的学生；2）在学生关系表中查询学号以081开头的学生；3）在学生关系表中查询姓为李并且性别为男的学生；4）在学生关系表中查询年龄在2025之间的学生；5）对学生关系表按学号及姓名进行排序；6）在课程关系表中统计现共有多少门课程；7）在课程关系表中查询最大课序号的课程；8）对选课关系表统计每个人参加了多少门课程；9）对选课关系表统计总共有多少学生；10）对选课关系表按学号进行分组统计总成绩（score），平均成绩；11）对选课关系表按学号、课序号进行分组查询参加课程数大于2门课程的学生和其参加的课程；4、掌握使用Insert、Update、Delete命令维护数据表的数据的方法学习和掌握嵌套查询二、实验相关概念与技术概述1、oracle数据库架构概述Oracle数据库服务器有两个主要的组成部分：数据库和实例(instance)。Oracle数据库用于存储和检索信息，是数据的集合。Oracle实例是指数据库服务器的内存及相关处理程序。1、物理结构（由控制文件、数据文件、重做日志文件、参数文件、归档文件、口令文件组成） 一个数据库中的数据存储在磁盘上物理文件，被使用时，调入内存。其中控制文件、数据文件、重做日志文件、跟踪文件及警告日志（trace files,alert files）属于数据库文件；参数文件（parameter file）口令文件（password file）是非数据库文件1.1 数据文件：存储数据的文件.数据文件典型地代表了根据他们使用的磁盘空间和数量所决定的一个Oracle数据库的容积。由于性能原因，每一种类型的数据放在相应的一个或一系列文件中，将这些文件放在不同的磁盘中。 types:28 .data dictionary .data .redo data .index .temporary data 1.2 控制文件：包含维护和验证数据库完整性的必要信息、例如，控制文件用于识别数据文件和重做日志文件，一个数据库至少需要一个控制文件. 控制文件内容： 数据库名 表空间信息 所有数据文件的名字和位置 所有redo日志文件的名字和位置 当前的日志序列号 检查点信息 关于redo日志和归档的当前状态信息 控制文件的使用过程： 控制文件把Oracle引导到数据库文件的其它部分。启动一个实例时，Oracle 从参数文件中读取控制文件的名字和位置。安装数据库时，Oracle 打开控制文件。最终打开数据库时，Oracle 从控制文件中读取数据文件的列表并打开其中的每个文件。1.3 重做日志文件：含对数据库所做的更改记录，这样万一出现故障可以启用数据恢复。一个数据库至少需要两个重做日志文件.1.4 跟踪文件及警告日志(Trace Files and Alert Files)在instance 中运行的每一个后台进程都有一个跟踪文件（trace file）与之相连。Trace file 记载后台进程所遇到的重大事件的信息。警告日志（ Alert Log）是一种特殊的跟踪文件，每个数据库都有一个跟踪文件,同步记载数据库的消息和错误.1.5 参数文件：包括大量影响Oracle数据库实例功能的设定，如以下设定： 数据库控制文件的定位 Oracle用来缓存从磁盘上读取的数据的内存数量 默认的优化程序的选择. 和数据库文件相关，执行两个重要的功能： 1为数据库指出控制文件 2为数据库指出归档日志的目标1.6 归档文件：是重做日志文件的脱机副本，这些副本可能对于从介质失败中进行恢复很必要。 1.7 口令文件：认证哪些用户有权限启动和关闭Oracle例程.2、逻辑结构（表空间、段、区、块） 表空间：是数据库中的基本逻辑结构，一系列数据文件的集合。 段：是对象在数据库中占用的空间. 区：是为数据一次性预留的一个较大的存储空间. 块：ORACLE最基本的存储单位，在建立数据库的时候指定.3、内存分配（SGA和PGA）SGA：是用于存储数据库信息的内存区，该信息为数据库进程所共享。它包含Oracle 服务器的数据和控制信息,它是在Oracle服务器所驻留的计算机的实际内存中得以分配，如果实际内存不够再往虚拟内存中写。 PGA：包含单个服务器进程或单个后台进程的数据和控制信息，与几个进程共享的SGA 正相反,PGA 是只被一个进程使用的区域，PGA 在创建进程时分配,在终止进程时回收.4、后台进程包括数据写进程(Database Writer,DBWR)、日志写进程(Log Writer,LGWR)、系统监控(System Monitor,SMON)、进程监控(Process Monitor,PMON)、检查点进程(Checkpoint Process,CKPT)、归档进程、服务进程、用户进程） 数据写进程：负责将更改的数据从数据库缓冲区高速缓存写入数据文件 日志写进程：将重做日志缓冲区中的更改写入在线重做日志文件 系统监控：检查数据库的一致性如有必要还会在数据库打开时启动数据库的恢复 进程监控：负责在一个Oracle 进程失败时清理资源 检查点进程：负责在每当缓冲区高速缓存中的更改永久地记录在数据库中时,更新控制文件和数据文件中的数据库状态信息。该进程在检查点出现时，对全部数据文件的标题进行修改，指示该检查点。在通常的情况下，该任务由LGWR执行。然而，如果检查点明显地降低系统性能时，可使CKPT进程运行，将原来由LGWR进程执行的检查点的工作分离出来，由CKPT进程实现。对于许多应用情况，CKPT进程是不必要的。只有当数据库有许多数据文件，LGWR在检查点时明显地降低性能才使CKPT运行。CKPT进程不将块写入磁盘，该工作是由DBWR完成的。 init.ora文件中 CHECKPOINT_PROCESS 参数控制CKPT进程的使能或使不能。缺省时为FALSE，即为使不能。 归档进程：在每次日志切换时把已满的日志组进行备份或归档 服务进程：用户进程服务。 用户进程：在客户端，负责将用户的SQL语句传递给服务进程，并从服务器段拿回查询数据。 3、 SCN(System ChangeNumber)：系统改变号，一个由系统内部维护的序列号。当系统需要更新的时候自动增加，他是系统中维持数据的一致性和顺序恢复的重要标志。2、关系数据库语言SQL(1)、基本表的创建 基本表SS（S#，SNAME，AGE，SEX）可用下列语句创建：CREATE TABLE S(S#CHAR(4) NOT NULL， SNAME CHAR(8) NOT NULL， AGECHAR(1)， SEX CHAR(1), PRIMARY KEY(S#)（2）、索引的创建创建索引可用“CREATE INDEX”语句实现。其句法如下：CREATE UNIQUE INDEX ON （列名表） 索引的撤消当索引不需要时，可以用“DROP INDEX”语句撤消，其句法如下： DROP INDEX （3）、SELECT查询语句 SELECTFROMWHERE句型在关系代数中最常用的式子是下列表达式：A1,An(F(R1Rm) 这里R1、Rm为关系，F是公式，A1、An为属性。针对上述表达式，SQL为此设计了SELECTFROMWHERE句型：SELECT A1，AnFROM R1，RmWHERE F 这个句型是从关系代数表达式演变来的，但WHERE子句中的条件表达式F要比关系代数中公式更灵活。SELECTFROMWHERE句型在关系代数中最常用的式子是下列表达式：A1,An(F(R1Rm) 这里R1、Rm为关系，F是公式，A1、An为属性。针对上述表达式，SQL为此设计了SELECTFROMWHERE句型：SELECT A1，AnFROM R1，RmWHERE F 这个句型是从关系代数表达式演变来的，但WHERE子句中的条件表达式F要比关系代数中公式更灵活。字符串的匹配操作条件表达式中字符串匹配操作符是“LIKE”。在表达式中可使用两个通配符：百分号（%）：与零个或多个字符组成的字符串匹配。下划线（_）：与单个字符匹配。集合的并、交、差操作 当两个子查询结果的结构完全一致时，可以让这两个子查询执行并、交、差操作。并、交、差的运算符为UNION、INTERSECT和EXCEPT。 空值的比较操作SQL中允许列值为空，空值用保留字NULL表示。 （4） 、数据插入 （1）单元组的插入 INSERT INTO 基本表名 （列名表）VALUES（元组值） （2）多元组的插入 INSERT INTO 基本表名 （列名表） VALUES （元组值），（元组值），（元组值）(3) 查询结果的插入 INSERT INTO基本表名 (列名表)SELECT查询语句 这个语句可把一个SELECT语句的查询结果插到某个基本表中。 （4）表的插入 INSERT INTO基本表名1 （列名表）TABLE 基本表名2 这个语句可把基本表2的值插入到基本表1中。（5） 、数据删除 SQL的删除操作是指从基本表中删除元组，其句法如下：DELETE FROM基本表名WHERE 条件表达式其语义是从基本表中删除满足条件表达式的元组。删除语句实际上是“SELECT * FROM基本表名WHERE条件表达式”和DELETE操作的结合，每找到一个元组，就把它删去。应该注意，DELETE语句只能从一个基本表中删除元组。WHERE子句中条件可以嵌套，也可以是来自几个基本表的复合条件。(6) 、数据更新 当需要修改基本表中元组的某些列值时，可以用UPDATE语句实现，其句法如下： UPDATE 基本表名SET 列名=值表达式，列名=值表达式ROW =（元组）WHERE 条件表达式 其语义是：修改基本表中满足条件表达式的那些元组中的列值，需修改的列值在SET子句中指出。三、实验环境安装安装过程中要注意数据库口令的填写，需要记住，在进入Oracle数据库时需要输入用户名和口令，此处的口令即为要输入的口令。如下图：Oracle安装需要检测电脑是否符合安装该数据库，有时需要用户手动验证，见下图：检测完之后，符合先决条件，就可以继续下一步，出现如下图，点击安装。安装过程如下截图已经成功安装oracle，点击退出。四、实验结果1、第一次实验：进入Oracle数据库，出现如下界面，要求输入用户名，口令以及连接身份，在用户名处输入sys，表示是超级用户登录；口令即为安装时用户输入的口令；连接身份选择SYSDBA，然后点击登录。1、创建表空间在上述界面中找到管理，然后再找到存储，然后点击表空间，出现如下界面。在上述界面中点击创建,然后输入表空间名，进入如下界面在上述界面中输入文件名，在最大文件大小处选择“值”，然后输入100MB。然后点击继续，出现如下界面，可以看到已经添加了数据库文件，然后根据需要选择相应的表空间的区管理形式，表空间的类型以及其状态，可以创建永久型表空间，临时表空间以及撤销表空间等类型的表空间。选择完之后，点击确定。则表空间创建完成。2、创建表进入Oracle主界面之后，找到方案，然后再找到数据库对象，然后点击表，见下图：点击表之后，再点击创建表。如下图：然后点击继续，然后再点击创建，填写表名称，然后选择表空间。如下图：然后在上述表中填入要求创建的表结构。若输入的列数很多，则可点击“添加5个表列”，这样就又有5个新的列出现。输入完之后点击确定。则表AB01已创建完成。第二次实验现有如下关系表:1)教师关系: T(T#, TName, Title) 2)课程关系: C(C#, CName, T#)3)学生关系: S(S#, SName, Age, Sex)4)选课关系: SC(S#, C#, Score)1)创建以上关系表,关系表的属性的数据类型自己设计;2)修改学生关系表S,增加一个属性:系别(Department);3)为以上四个关系表的属性设计和增加主键;4)为选课关系表增加主外键约束,引用学生关系表和课程关系表;5)在学生表的S#上和SName上分别创建两个索引.1)创建以上关系表,关系表的属性的数据类型自己设计;2)修改学生关系表S,增加一个属性:系别(Department);3)为以上四个关系表的属性设计和增加主键4)为选课关系表增加主外键约束,引用学生关系表和课程关系表;5)在学生表的S#上和SName上分别创建两个索引.第三次实验1)教师关系: T(T#, TName, Title) 2)课程关系: C(C#, CName, T#)3)学生关系: S(S#, SName, Age, Sex)4)选课关系: SC(S#, C#, Score)学习、掌握基本查询、排序以及分组的方法1）在学生关系表中查询学号为081001的学生；2）在学生关系表中查询学号以081开头的学生；3） 在学生关系表中查询姓为李并且性别为男的学生；4）在学生关系表中查询年龄在2025之间的学生；5）对学生关系表按学号及姓名进行排序；6）在课程关系表中统计现共有多少门课程；7）在课程关系表中查询最大课序号的课程；8）对选课关系表统计每个人参加了多少门课程；9）对选课关系表统计总共有多少学生；10）对选课关系表按学号进行分组统计总成绩（score），平均成绩；11）对选课关系表按学号、课序号进行分组查询参加课程数大于2门课程的学生和其参加的课程；第四次实验1）为T、C、S、SC分别插入相关记录，其中S表至少要有姓名为张三、李四、王二的记录，C表和SC表中要有课序号为001，名称为数据库的课程；2)、为学生关系表S创建一个备份表S_bak，将S表的所有记录插入3）、在S表中将姓名为王二的学生姓名改为王小二，年龄改为18；4）、在S表中删除姓名为李四的学生；5）查询选修数据库课程的选课人数，要求按选各门课程人数的多少降序排序；6）、查询至少选修了2门课程且所有选修课成绩不低于80分的学生的平均成绩；7）查询选课至少二门的同学的名字、选课的门数及平均成绩，如果某门课成绩是空的，则该门课程不参加统计。8） 查询年龄大于女同学平均年龄的男生的姓名和年龄；9）查询年龄大于所有女同学年龄的男生的姓名和年龄；10）查询学号比张三同学大（注：张三同学可能会不止一个），而年龄比他小的学生姓名；五、实验结论 为期差不多一个学期的数据库系统教程接近了尾声，关于SQL语言我们采用的是将课堂理论知识与上机实践相结合的方式进行学习，我觉得这样的学习方法对我们来说是很好的。第一次实验我们主要是进行上机实验安装了Oracle数据库，