数据库原理与应用实验指导.docx
- 文档编号:5371820
- 上传时间:2022-12-15
- 格式:DOCX
- 页数:95
- 大小:66.16KB
数据库原理与应用实验指导.docx
《数据库原理与应用实验指导.docx》由会员分享,可在线阅读,更多相关《数据库原理与应用实验指导.docx(95页珍藏版)》请在冰豆网上搜索。
数据库原理与应用实验指导
数据库理论与应用实验指导书
李爱武编
2005年1月
目录
第1章SQL语言3
1.1SQL语言概述3
1.1.1SQL语言与传统程序语言的差别4
1.1.2SQL语言的历史4
1.1.3SQL语言的分类4
1.2SQL*Plus的使用方法5
1.2.1Oracle中的预置用户5
1.2.2连接数据库的方式5
1.2.3使用SQL*Plus6
1.3简单查询9
1.3.1select-from-where结构9
1.3.2设置查询结果字段的别名及排序10
1.3.3WHERE关键字的使用11
1.3.4汇总函数14
1.3.5GROUPBY及HAVING子句16
1.3.6理解NULL16
1.4UPDATE、DELETE、INSERT语句17
1.5复杂查询18
1.5.1表连接18
1.3.7集合运算22
1.3.8子查询23
1.3.9EXISTS及NOTEXISTS的用法(略)25
第2章表及完整性约束25
2.1数据类型25
2.2创建简单的表26
2.3完整性约束27
2.4创建带有约束的表27
2.6修改表30
第3章Oracle中建立数据库及表空间管理32
3.1手工建库32
3.2数据库的启动和关闭36
3.2.1数据库的启动36
3.2.2数据库的关闭36
3.3管理表空间(tablespace)37
3.3.1表空间的基本概念37
3.3.2表空间的种类37
3.3.3在Oracle中创建表空间38
3.3.4在表空间中创建表39
3.3.5autoallocate及uniformsize选项使用探秘39
3.3.6由多个数据文件构成的表空间43
第4章事务处理、并发控制及锁48
4.1事务的概念及ACID属性48
4.2Oracle中的事务操作49
4.3Oracle中的并发控制49
4.4Oracle中的事务隔离级别49
4.5锁51
第5章索引原理52
5.1索引结构(略)52
5.2Oracle中如何创建索引52
5.3在SQL*Plus中查看执行计划53
5.4使用索引的场合55
5.5索引未被使用的原因55
5.6索引与空值58
5.7实验:
应用索引提高查询速度的检验60
5.8DML语句对索引的影响60
第6章存储过程以及触发器64
6.1PL/SQL的基本结构64
6.2简单的PL/SQL程序设计66
6.2.1声明66
6.2.2使用%TYPE和%ROWTYPE67
6.3游标68
6.3.1显式游标68
6.3.2隐式游标70
6.4触发器71
6.4.1语句触发器71
6.4.2行触发器72
6.4.3insteadof触发器73
6.5存储过程74
第7章备份与恢复77
7.1Oracle数据库日志及归档日志的作用77
7.1归档日志的设置77
7.2备份与恢复实验79
7.3逻辑备份80
第8章Oracle的用户和权限管理83
8.1用户管理83
8.2权限管理84
第9章Oracle数据库的网络连接85
第10章Oracle数据库的体系结构87
第1章SQL语言
1.1SQL语言概述
SQL是在各种关系型数据库中对数据进行操作的语言(也可称为工具),可以利用数据库管理系统本身提供的客户端管理工具通过输入SQL对数据库进行操作,也可以嵌入到第三方程序开发语言中,对数据库进行操作。
由于SQL语言在查询上的灵活性、在语法上的简捷以及在执行上的高效率,使它成为数据库管理者及设计者的不可替代的工具。
SQL语言已经成为数据库系统的标准语言。
1.1.1SQL语言与传统程序语言的差别
SQL语言或用SQL语言写成的程序必须应用在数据库管理系统中,其本身不能独立执行,而且为“非过程性”语言,与平时所熟悉的C、PASCAL等程序设计语言有很大不同。
利用SQL语句我们只要指出自己所要的数据、存放位置和查询条件,根本不需要知道数据库管理系统是如何找到或处理数据的。
为了加强SQL语言的能力,各数据库厂商都在SQL语言中加入了过程性语言的特征,增加了诸如流程控制、条件转向、变量定义等指令,使SQL语言有了很大程度上的扩展。
Oracle对SQL语言的扩展称为PL/SQL,而SQLServer、Sybase对SQL语言的扩展称为T-SQL。
SQL是一种介于关系代数和关系演算之间的非过程化的集合操作语言,具有关系代数和关系演算的双重特点:
1)综合统一;
2)高度非过程化;
3)面向集合的操作方式;
4)以一种语法结构提供两种使用方式,即,既是自包含式语言,又是嵌入式语言;
5)语言简捷,易学易用。
1.1.2SQL语言的历史
1)1970年,IBM研究中心的E.FCodd提出关系模型的概念
2)1972年,IBM公司开始研制实验型关系数据库系统SYSTEMR,配置的查询语言SQUARE语言,在语言中使用了较多的数学符号。
3)1974年,Boyce和Chamberlin把SQUARE语言修改为SEQUEL语言,这两个语言在本质上是相同的,但后者去掉了一些实数学符号,并采用英语单词和结构化的语法规则,看起来很象英语句子,受到很大欢迎。
4)SEQUEL简称为SQL(StructuredQueryLangauge)。
5)1986年,ANSI发布SQL(86)标准,后来被ISO采纳为国际标准,称为“SQL-86”。
6)1989年,ANSI发布SQL-89,后来也被ISO采纳为国际标准。
SQL-86和SQL-89现在当然已经成为过时的标准。
7)1992年,ANSI/ISO发布SQL(1992),习惯称为SQL2,它分为EntrySQL,IntermediateSQL,FullSQL。
EntrySQL基本上就是SQL89,现在的数据库产品多数都完全支持Entry级别,同时部分支持Intermediate和Full级别的某些特性。
8)1999年,ANSI/ISO发布SQL-99(或SQL-1999),习惯称为SQL3。
1.1.3SQL语言的分类
在SQL-92标准中,SQL语法中的语句,依据其功能的差别,可以分为DDL(DataDefinitionLanguage,数据定义语言)、DML(DataManipulationLanguage,数据处理语言)及DCL(DataControlLanguage,数据控制语言)三类。
SQL-99的分类方式有所变化。
1)DDL语句
数据表、视图、索引、存储过程、触发器等都视为数据库对象。
凡用来定义(或建立)数据库对象,以及修改数据库对象结构的SQL语句,都属于DDL语句。
2)DML语句
DML类型语句主要包括由SELECT、UPDATE、INSERT、DELETE构成的SQL语句。
用于在数据表中查询、更新、增加和更改记录。
3)DCL语句
DCL语句一般指专门用来设置数据库对象使用权限的语句,包括GRANT、DENY和REVOKE三种。
从广义的角度看,还包括控制执行流程的IF……ELSE、WHILE语句,以及控制事务进行的BEGINTRAN、COMMITTRAN和ROLLBACKTRAN等语句。
1.2SQL*Plus的使用方法
为了方便读者进行实验,我们首先讲解Oracle的客户端工具的使用方法。
多数数据库产品都提供与数据库进行交互操作的两类客户端管理工具――图形界面和字符界面(或称为命令行),字符界面客户端管理工具用于输入和执行SQL语句,对SQL语句不熟悉的用户可以利用图形界面管理工具以更直观的方式操作数据库。
数据库专业人员一般主要使用字符界面工具。
Oracle提供的这两种工具分别是SQL*Plus和EnterpriseManager,前者为字符界面,后者为图形界面。
SQLServer提供的字符管理工具为osql(7.0之前的版本是isql),图形界面工具也称为EnterpriseManager。
在Oracle中,主要的管理工具为字符界面的SQL*Plus,这就要求用户要熟悉各种SQL命令。
1.2.1Oracle中的预置用户
要操作Oracle数据库,首先要以数据库中的合法用户连接到数据库,Oracle中常用的预置用户有以下三个:
sys:
Oracle中权限最大的用户,位于数据库之外,可以启动、关闭数据库,创建数据库,在数据库内拥有操作数据库的所有权限,以sys连接数据库时要加上assysdba子句。
system:
是数据库内权限最大的用户。
scott用户:
Oracle提供的用于学习和测试的用户,其口令为tiger,可以在SQL*Plus中执行scott.sql脚本文件建立这个用户以及此用户下的四张表,这几张表及其数据模拟了一个小型公司的员工管理系统,非常适合作为学习之用。
Oracle公司成立之初,软件开发任务主要由BruceScott(也就是Oracle数据库中的scott用户)和BobMiner担任,他们各自编写了Oracle的一半代码,tiger是Scott的女儿的猫的名字,BruceScott在Oracle公司工作了5年半左右,于1982年离开Oracle公司,现在是Pointbase公司的创办者和CEO,BobMiner一直是Oracle的技术总裁,1996年去世。
1.2.2连接数据库的方式
打开windows命令行工具,输入sqlplus/nolog,nolog关键字指明只启动SQL*Plus,而不连接到数据库。
出现“SQL>”提示符后,说明SQL*Plus正常启动。
C:
\>sqlplus/nolog
SQL*Plus:
Release9.2.0.1.0-Productionon星期一12月1308:
48:
232004
Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.
SQL>
SQL*Plus中以关键字connect连接数据库,后面跟用户名及对应的口令。
语法格式如下:
SQL>connectscott/tiger
已连接。
SQL>
上面的命令连接的数据库为本地机器上的数据库。
也可以在启动SQL*Plus的同时,连接到数据库,命令如下:
C:
\>sqlplusscott/tiger
SQL*Plus:
Release9.2.0.1.0-Productionon星期二12月1420:
46:
472004
Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.
连接到:
Oracle9iEnterpriseEditionRelease9.2.0.1.0-Production
WiththePartitioning,OLAPandOracleDataMiningoptions
JServerRelease9.2.0.1.0-Production
SQL>
1.2.3使用SQL*Plus
1)查询当前用户拥有的表
使用SQL语句:
select*fromtab;查看当前用户的表。
SQL>connectscott/tiger
已连接。
SQL>select*fromtab;
TNAMETABTYPECLUSTERID
------------------------------------------------
BONUSTABLE
DEPTTABLE
EMPTABLE
2)查看表的结构
使用命令“describe”或其简写“desc”查看表的结构,即查看表有哪些字段以及每个字段属于什么数据类型
SQL>descdept
名称是否为空?
类型
-----------------------------------------------------------
DEPTNONOTNULLNUMBER
(2)
DNAMEVARCHAR2(14)
LOCVARCHAR2(13)
3)输入和执行SQL语句
直接在“SQL>”提示符下输入SQL语句,一条命令可以多行输入,输入“;”执行。
也可以另起一新行,输入“/”来执行,另外,除了字符串外,SQL语句是不区分字母大小写的,“select*fromdept”与“selEct*FROmdepT”在语法上都是正确的,功能也是相同的。
SQL>select*fromdept;
DEPTNODNAMELOC
---------------------------------------------------
10ACCOUNTINGNEWYORK
20RESEARCHDALLAS
30SALESCHICAGO
40OPERATIONSBOSTON
已选择6行。
SQL>select*fromdept
SQL>/
DEPTNODNAMELOC
---------------------------------------------------
10ACCOUNTINGNEWYORK
20RESEARCHDALLAS
30SALESCHICAGO
40OPERATIONSBOSTON
已选择6行。
4)修改SQL语句
如果SQL语句中某个词汇输入错误,Oracle会以“*”标识错误的词汇。
可以通过change命令修改错误:
change/deptt/dept,前面命令把SQL语句中的错误单词“deptt”改为“dept”。
要注意上面的命令只能修改当前行中的错误词汇,如果错误词汇不在当前行中,可以通过在“SQL>”提示符下输入错误词汇所在的行号,从而把那一行变为当前行。
另外,“change”可以简写为“c”。
SQL>select*fromdeptt
2wheredeptno=20
3;
select*fromdeptt
*
ERROR位于第1行:
ORA-00942:
表或视图不存在
SQL>change/deptt/dept
1*select*fromdept
SQL>/
DEPTNODNAMELOC
----------------------------------------------------
20RESEARCHDALLAS
5)使用edit命令调用编辑工具修改缓冲区中的SQL语句
在“SQL>”提示符下输入“edit”命令(可简写为“ed”),可以调用操作系统默认的编辑器(Windows操作系统中为记事本)来编辑或修改当前缓冲区中的SQL语句。
这种方法便于用户对SQL语句做较大的变动。
6)执行SQL脚本
SQL脚本是指内容为SQL语句或PL/SQL程序段的文本文件,一般扩展名为.sql,可以用常用的操作系统的文本编辑工具(比如Windows的记事本)中编辑完成,然后在SQL*Plus中执行。
例如,我们把下面内容在Windows记事本中编辑后,存储为扩展名为.sql的脚本文件:
select*fromdept;,然后在SQL*Plus中以“@”或“start”命令执行,可以省略扩展名。
SQL>@c:
\test
DEPTNODNAMELOC
------------------------------------------------
10ACCOUNTINGNEWYORK
20RESEARCHDALLAS
30SALESCHICAGO
40OPERATIONSBOSTON
7)show命令的使用
show命令可以显示当前的用户名、内存配置、初始化参数值、当前的数据库版本、当前SQL*Plus的配置环境等。
这些命令分别为:
showuser:
显示当前用户。
showsga:
显示数据库的内存配置。
showparameter:
显示初始化参数的值。
showrelease:
显示Oracle数据库的版本。
showlinesize:
显示SQL*Plus的环境配置值。
showpagesize:
显示SQL*Plus的环境配置值。
……
读者可以在SQL*Plus中输入这些命令自行测试,有些命令的含义以后都会涉及到。
8)列的格式化
columncol_nameformata30
上面命令可以把字段“col_name”用30个字符来显示,如果某些字段定义过长,而实际内容很短,可以用上述命令格式化,使得查询内容更加清楚地显示。
其中的字符a表示alphabet,即字母的意思。
9)自定义SQL*Plus环境
SQL*Plus环境主要是指SQL*Plus中的显示内容的外观,下面是一些常用的定义SQL*Plus环境的命令。
setlinesizen:
定义在SQL*Plus中一行显示的字符个数,n是一指定整数。
linesize可简写为line。
setpagesizen:
定义在SQL*Plus中每个页面显示的行数。
setpauseon/off:
当查询内容一个画面不能容纳时,是否在按回车键之前暂停显示。
settimingon/off:
是否显示当前操作花费的时间。
用show命令,可以查看SQL*Plus当前的相应环境参数的设置值。
10)保存SQL*Plus环境
SQL*Plus启动时都会读取%oracle_home%\sqlplus\admin目录下的glogin.sql文件,可以在此文件中添加set语句从而让环境设置永久生效。
Oracle_home是安装Oracle数据库后产生的一个环境变量,指Oracle数据库的程序文件和配置文件所在的目录。
1.3简单查询
1.3.1select-from-where结构
一般简单的SQL查询语句的结构为:
select-from-where,其中select后面是要查询的字段名列表,“*”表示要查询所有字段,from后面是要查询的表名,where后面是附加的查询条件。
下面的语句查询dept表中的所有记录的所有字段。
SQL>select*fromdept;
DEPTNODNAMELOC
------------------------------------------------
10ACCOUNTINGNEWYORK
20RESEARCHDALLAS
30SALESCHICAGO
40OPERATIONSBOSTON
下面的语句查询dept表中满足部门号为10的所有记录的值。
SQL>select*fromdept
2wheredeptno=10
3;
DEPTNODNAMELOC
--------------------------------------------------------
10ACCOUNTINGNEWYORK
下面的语句查询dept表中满足部门号为10的所有记录的地址(loc)字段的值。
SQL>selectlocfromdept
2wheredeptno=10
3;
LOC
-------------
NEWYORK
1.3.2设置查询结果字段的别名及排序
查询结果字段的别名是指在查询结果中,字段名称用另外一个自定义名称表示。
一般来说,自定义的字段名称可以在ORDERBY参数中使用,但是不能在WHERE、GROUPBY或HAVING参数中使用。
以下两种方式是等价的。
SQL>selectloclocationfromdept
2wheredeptno=10;
LOCATION
-------------
NEWYORK
SQL>selectlocaslocationfromdept
2wheredeptno=10;
LOCATION
-------------
NEWYORK
可以用“orderby”关键字设置查询结果按一个或几个字段排序。
其中“desc”可以指定结果按降序排列,asc按升序排列,默认为升序,所以asc关键字一般不用。
下面的语句对emp表的查询结果分别按sal字段值的升序和降序来排列。
SQL>selectename,salfromemp
2wheredeptno>20orderbysal;
ENAMESAL
--------------------
JAMES950
WARD1250
MARTIN1250
TURNER1500
ALLEN1600
BLAKE2850
已选择6行。
SQL>selectename,salfromemp
2wheredeptno>20orderbysaldesc
3;
ENAMESAL
--------------------
BLAKE2850
ALLEN1600
TURNER1500
WARD1250
MARTIN1250
JAMES950
已选择6行。
1.3.3WHERE关键字的使用
WHERE关键字的功能就是设置查询结果应该符合的条件。
当不需要过滤数据记录时,就不需要使用WHERE关键字。
上面已经用到过一些带有where关键字的例子。
在WHERE关键字的过滤条件中,可以利用以下表所示的运算符进行对比处理。
表4-1WHERE参数中的运算符
运算符
说明
=
等于
<
小于
>
大于
<>或!
=
不等于(!
=为SQL-92标准)
<=
小于或等于
>=
大于或等于
!
<
不小于
!
>
不大于
IN
判断数据记录的某个字段值是否出现在所赋值的各个数据中
BETWEEN
判断数据记录是否出现在所赋值范围的数据中,也可以用于判断日期值范围
LIKE
主要用于判断字符串字段值是否符合指定的格式
除了上面这些运算符,还可以用AND、OR以及NOT来连接多个过滤条件。
下面我们通过例子来讲解IN、BETWEEN及LIKE的用法,其他几个比较简单,请读者自行实验。
1)IN的用法
下面语句查询部门号为10或20的部门的信息。
SQL>select*fromdept
2wheredeptnoin(10,20);
DEPTNODNAMELOC
------------------------------------------------
10ACCOUNTINGNEWYORK
20RESEARCHDALLAS
下面两个语句都是查询部门地址在NEWYOR
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 原理 应用 实验 指导
![提示](https://static.bdocx.com/images/bang_tan.gif)