Oracle教案.docx

Oracle教案.docx

《Oracle教案.docx》由会员分享，可在线阅读，更多相关《Oracle教案.docx（110页珍藏版）》请在冰豆网上搜索。

Oracle教案

Oracle教案

第1章Oracle基础知识

1.1Oracle简介

oracle是殷墟（YinXu）出土的甲骨文（oracleboneinscriptions）的英文翻译的第一个单词，在英语里是“神谕”的意思。

Oracle的四大创始人

Oracle数据库中有个默认用户SCOTT，就是BruceScott，而SCOTT用户的默认密码tiger，是当时Scott养的一只猫的名字。

2009年4月Oracle公司以74亿美元收购SUN公司。

1.2Oracle的版本

Oracle8i（internet）:

表示Oracle公司开始正式进入互联网。

Oracle9i:

与Oracle8i相关，性能更佳，管理更人性化。

Oracle10g（grid） :

网格技术

Oracle11g（grid） :

网格技术

网格是利用互联网把地理上广泛分布的各种资源（包括计算资源、存储资源、带宽资源、软件资源、数据资源、信息资源、知识资源等）连成一个逻辑整体，就像一台超级计算机一样，为用户提供一体化信息和应用服务（计算、存储、访问等），彻底消除资源“孤岛”，最充分的实现信息共享。

1.3Oracle的安装（演示）

重要概念：

1.全局数据库名与SID

1）全局数据库名

是数据库的名称，当数据库处于网络中时，为标识该数据库的网络位置，需要用数据库名和网络位置组成其全局数据库名，其命名格式是：

database_name.database_domain。

例如：

，其中sales为数据库名，为数据库域。

指定全局数据库名时，尽量为数据库选择能够反映其用途的名称，例如sales。

数据库域用于将数据库与分布式环境中的其他数据库区分开来。

例如在上海的数据库可以命名为，北京的数据库可以命名为。

即使数据库名都相同，但数据库域不同，所以也能区分开。

2）SID（数据库实例名）

用于对外相连时使用。

Oracle实例（Instance）是用来访问数据库文件集的存储结构与后台程序的集合。

Oracle数据库其实是磁盘上的一堆文件；为了启动数据库即访问这堆文件，需要在内存中创建它的一个实例，然后由实例加载并打开数据库。

用户连接数据库时，实际上是连接到实例，然后由实例负责与数据库通信，再将处理结果返回给用户。

Oracle中一个数据库至少有一个实例与之对应，但一个数据库也可以对应多个实例，被多个实例访问。

因此SID主要用于区分同一台计算机上不同的实例。

对于单实例数据库，其SID通常与数据库名相同。

一个运行着的ORACLE数据库可以看成是一个ORACLESERVER，该SERVER由数据库（Database）和实例（Instance）组成，

一般情况下一个ORACLESERVER包含一个实例和一个与之对应的数据库，但是在特殊情况下，如8i的OPS，9i的RAC，一个SERVER中一个数据库可以对应多个实例。

当某一实例出现故障时，其他实例自动服务。

2.Oracle的常用账户

用户名

角色

默认密码

说明

sys

超级管理员

change_on_install

所有oracle的数据字典的基表和视图都存放在sys用户中，这些基表和视图对于oracle的运行是至关重要的，由数据库自己维护，任何用户都不能手动更改。

sys用户拥有dba，sysdba，sysoper等角色或权限，是oracle权限最高的用户。

system

普通管理员

manager

用于存放次一级的内部数据，如oracle的一些特性或工具的管理信息。

system用户拥有普通dba角色权限。

可用来创建其他用户。

scott

普通用户（练习常用）

tiger

在默认情况下从Oracle10g开始，scott不能登陆。

被禁用了。

需要手工解锁。

3.安装后的注意事项：

Oracle安装完成后至少要启动两个服务：

OracleOraDb11g_home1TNSListener:

监听器，监听程序的服务进程。

OracleServiceORCL:

主服务，是Oracle数据库实例的服务进程。

建议将启动类型改为手动。

1.4Oracle的常用管理工具

1.使用SQL*Plus

在Oracle中，用户对数据库的操作主要是通过SQL*Plus工具来实现的。

应用举例：

（1）.查看当前连接用户

SQL>showuser

（2）.查看全局数据库名

SQL>SELECT*FROMglobal_name;

（4）.清空屏幕

SQL>clscr;

2.使用OracleEnterpriseManager（OEM）

OracleEnterpriseManager（OEM）提供了基于Web界面的、可管理单个数据库的工具。

使用步骤：

（1）.启动OracleDBConsoleorcl服务

（2）.启动浏览器，输入OEM的URL地址（https:

//主机名:

1158/em），或者直接在【开始】菜单的Oracle程序组中选择DatabaseControl–orcl命令即可。

（3）.注意事项：

只能用sys和system用户登陆，sys必须用SYSDBA身份登陆。

3.使用DBCA创建数据库

如果在安装Oracle时选择仅安装数据库服务器软件，而不创建数据库，就需要安装后手动创建数据库。

如果在系统中已经存在Oracle数据库，为了充分利用服务器的资源，建议不要再创建一个数据库。

DBCA（DatabaseConfigurationAssistant）是一个图形化用户界面的工具，DBA通过它可以快速、直观地创建数据库。

选择【开始】｜【程序】｜Oracle-OraDb11g_home1|【配置和移置工具】｜DatabaseConfigurationAssistant命令，打开DBCA界面。

用户只需要根据DBCA的提示逐步进行设置，就可以根据相应配置创建数据库。

第2章SQL*Plus命令

2.1用户管理命令

1.更改用户登录

命令格式：

conn用户名/密码[ASSYSDBA]

注意：

如果连接的是超级管理员（SYS），必须写上ASSYSDBA

2.用户加锁和解锁

加锁：

SQL>alteruser用户名accountlock;

解锁：

SQL>alteruser用户名accountunlock;

3.更改用户密码

命令格式：

SQL>alteruser用户名identifiedby密码;

注意，如果忘记所有用户的密码，可启动SQL*Plus，输入以下命令：

conn/assysdba

SQL>alteruser用户名identifiedby密码;

2.2其他常用命令

1.help命令

SQL*Plus有许多命令，而且每个命令都有大量的选项，要记住每一个命令的所有选项是很困难的。

SQL*Plus提供了内建的帮助系统，可以使用HELP命令查询相关的命令信息。

命令格式：

SQL>help命令名

示例：

查看conn命令的帮助信息

SQL>helpconn;

查看SQL*Plus的命令清单

SQL>helpindex;

查看SQL*Plus的关键字清单

SQL>helpreservewords;

2.describe命令

describe命令可以缩写为desc，用来列出表或视图各个列的名称以及属性。

命令格式：

SQL>descobject_name;

示例：

查看scott用户的emp表的结构

SQL>descscott.emp;

3.setlinesize命令

系统默认每行打印80个字符，当SQL*Plus输出linesize指定数量的字符后，随后的数据就会折叠到下一行显示。

命令格式：

SQL>setlinesizenumber

示例：

SQL>showlinesize;

SQL>setlinesize800;

4.setpagesize命令

当SQL*Plus执行查询语句时，setpagesize命令可以设置一页显示的行数。

命令格式：

SQL>setpagesizenumber

示例：

SQL>showpagesize;

SQL>setpagesize30;

5.pause命令

如果在SQL*Plus中运行的查询语句可以返回多行数据，以至于无法在窗口中一次显示完，输出窗口会快速滚动显示。

可以设置环境变量pause为on来控制显示完一页后暂停显示，直到按回车键才继续显示下一页数据。

pause选项还可以设置暂停后显示的字符串，以便提示用户。

命令格式：

SQL>setpauseon ;

SQL>setpause‘按回车键继续’;

6.@命令

用于执行脚本文件。

命令格式：

SQL>@文件名;

注意：

需写文件路径；sql文件的后缀可以不写。

7.继续使用上次命令

命令格式：

SQL>/

2.3常用数据字典视图

数据字典是Oracle数据库的核心组件，是数据库中的所有对象信息的知识库，提供了数据库结构、数据库对象空间分配和数据库用户等有关的信息。

任何数据库用户都无法对数据字典中的内容进行修改，但可以查看数据字典中的内容。

数据字典中的信息通过表和视图的形式组织。

数据字典中的信息实际上保存在基础表中，只有Oracle系统才有权读取和写入基础表。

基础表中存储的信息通常是经过加密处理的。

而视图是一种虚拟表，它本身并不包含数据，用户可以通过数据字典视图来获取信息，而不需访问数据字典表。

数据字典视图分类：

视图类型

说明

USER视图

USER视图的名称以user_为前缀，用来记录用户对象的信息。

例如user_tables视图，它记录用户的表信息

ALL视图

ALL视图的名称以all_为前缀，是USER视图的扩展。

用来记录用户对象的信息以及被授权访问的对象信息。

例如all_synonyms视图，它记录用户可以存取的所有同义词信息

DBA视图

DBA视图的名称以dba_为前缀，用来记录数据库实例的所有对象的信息。

例如dba_tables视图，通过它可以访问所有用户的表信息

V$视图

V$视图的名称以v$为前缀，用来记录与数据库活动相关的性能统计动态信息。

例如v$datafile视图，它记录有关数据文件的统计信息

GV$视图

GV$视图的名称以gv$为前缀，用来记录分布式环境下所有实例的动态信息。

例如gv$lock视图，它记录出现锁的数据库实例的信息

基本数据字典视图

字典名称

说明

dba_tables

所有用户的所有表的信息

dba_tab_columns

所有用户的表的字段信息

dba_views

所有用户的所有视图信息

dba_synonyms

所有用户的同义词信息

dba_sequences

所有用户的序列信息

dba_constraints

所有用户的表的约束信息

dba_indexes

所有用户的表的索引简要信息

dba_ind_columns

所有用户的索引的字段信息

dba_triggers

所有用户的触发器信息

dba_sources

所有用户的存储过程信息

dba_segments

所有用户的段的使用空间信息

dba_extents

所有用户的段的扩展信息

dba_objects

所有用户对象的基本信息

cat

当前用户可以访问的所有基表

tab

当前用户创建的所有基表、视图和同义词等

dict

构成数据字典的所有表的信息

与数据库组件相关的数据字典

数据库组件

数据字典中的表或视图

说明

数据库

v$datafile

记录系统的运行情况

表空间

dba_tablespaces

记录系统表空间的基本信息

dba_free_space

记录系统表空间的空闲空间的信息

控制文件

v$controlfile

记录系统控制文件的基本信息

v$controlfile_record_section

记录系统控制文件中记录文档段的信息

v$parameter

记录系统各参数的基本信息

数据文件

dba_data_files

记录系统数据文件以及表空间的基本信息

v$filestat

记录来自控制文件的数据文件信息

v$datafile_header

记录数据文件头部分的基本信息

段

dba_segments

记录段的基本信息

数据区

dba_extents

记录数据区的基本信息

日志

v$thread

记录日志线程的基本信息

v$log

记录日志文件的基本信息

v$logfile

记录日志文件的概要信息

归档

v$archived_log

记录归档日志文件的基本信息

v$archive_dest

记录归档日志文件的路径信息

数据库实例

v$instance

记录实例的基本信息

v$system_parameter

记录实例当前有效的参数信息

内存结构

v$sga

记录SGA区的大小信息

v$sgastat

记录SGA的使用统计信息

v$db_object_cache

记录对象缓存的大小信息

v$sql

记录SQL语句的详细信息

v$sqltext

记录SQL语句的语句信息

v$sqlarea

记录SQL区的SQL基本信息

后台进程

v$bgprocess

显示后台进程信息

v$session

显示当前会话信息

常用动态性能视图

视图名称

说明

v$fixed_table

显示当前发行的固定对象的说明

v$instance

显示当前实例的信息

v$latch

显示锁存器的统计数据

v$librarycache

显示有关库缓存性能的统计数据

v$rollstat

显示联机的回滚段的名字

v$rowcache

显示活动数据字典的统计

v$sga

显示有关系统全局区的总结信息

v$sgastat

显示有关系统全局区的详细信息

v$sort_usage

显示临时段的大小及会话

v$sqlarea

显示SQL区的SQL信息

v$sqltext

显示在SGA中属于共享游标的SQL语句内容

v$stsstat

显示基本的实例统计数据

v$system_event

显示一个事件的总计等待时间

v$waitstat

显示块竞争统计数据

应用举例：

1.查看所有用户

SQL>SELECTusername,account_statusFROMdba_users;

*OPEN表示账户为解锁状态；EXPIRED表示账户为过期状态（需要设置口令才能解除此状态）；LOCKED表示账户为锁定状态。

2.查看SID

SQL>SELECTinstance_nameFROMv$instance;

3.查看当前用户所有的表信息

SQL>SELECT*FROMtab;

*table（表）view（视图）synonym（别名，与视图类似）

4.查看所有用户的表信息

SQL>SELECTtable_name,ownerFROMdba_tables;

5.查看指定用户的表信息

SQL>SELECTtable_name,ownerFROMdba_tableswhereowner=’SCOTT’;

第3章SQL语句基础

scott用户有4个案例表，可在日常练习中使用。

3.1Oracle内置字段数据类型

1.字符型

CHAR型：

定长字符串，短则用空格填充，长则出错。

VARCHAR2型：

变长字符串。

字段长度根据实际字符串长度自动调整，不用空格填充。

2.数值型NUMBER（PRECISION,SCALE）

精度PRECISION指定所有数字位的个数，范围SCALE指定小数的位数，两个参数均是可选的。

如果插入的字段数据超过指定位数，将自动四舍五入。

3.日期时间数据类型DATE

可以存储日期和时间的组合数据。

ORACLE默认的日期格式是DD-MON-YY。

4.LOB数据类型

用于大型的、未被结构化的数据，如二进制文件、图片文件等。

LOB数据类型又分为BLOB、CLOB和BFILE三种。

BLOB类型：

用于存储二进制对象。

如图像、音频、视频。

CLOB类型：

用于存储字符格式的大型对象。

Oracle将数据转换成Unicode格式。

BFILE类型：

将二进制文件作为操作系统文件存储在数据库外部，BFILE类型的字段仅保存二进制文件的指针。

5.ROWID类型

亦称伪列类型，用于保存表中每条记录的物理地址。

每条记录都有唯一的rowid。

ORACLE自动为每个表建立名称为ROWID的字段。

可以对该字段进行查询。

rowid确定了每条记录属于哪一个数据对象、数据文件、块、行。

是基于64位编码的18个字符显示。

其格式如下：

示例：

ROWID是隐含的，检索表时不会看到，须显式指定名称。

SQL>SELECTrowid,enameFROMemp;

3.2SQL简介

SQL（StructuredQueryLanguage）结构化查询语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

SQL语言分为3类：

1．数据定义语言（DDL）

用来定义和管理数据库中的对象（如表、视图、存储过程、触发器）。

由CREATE、ALTER、DROP命令构成。

2．数据操纵语言（DML）

针对数据库中存储的数据进行相关操作，主要包括增加（insert）、删除（delete）、更新（update）和查询（select）四种操作。

在实际工作中增加、删除和更新所占使用DML比例为20%，而查询所占比例为80%。

3．数据控制语言（DCL）

用来管理用户对指定数据库对象的使用权限。

常用操作有分配（grant）和回收（revoke）。

3.3SELECT语句的用法

在众多SQL语句中，使用频率最高的是SELECT语句，该语句主用于检索数据。

虽然在前面已经使用了一些SELECT语句，但这些使用是零散的、不完整的。

本节将对SELECT语句进行系统地、完整地介绍。

SELECT语句的完整语法格式如下：

SELECT[ALL|DISTINCT

{*|expression|column1_name[,column2_name][,...]}

FROM{table1_name|（subquery）}[alias]

[,{table2_name|（subquery）}[alias],...]

[WHEREcondition]

[CONNECTBYcondition[STARTWITHcondition]]

[GROUPBYexpression[,...]]

[HAVINGcondition[,...]]

[{UNION|INTERSECT|MINUS}]

[ORDERBYexpression[ASC|DESC][,...]]

[FORUPDATE[OF[schema.]table_name|view]column][NOWAIT];

3.3.1检索单表数据

检索单表数据是指从单个表中检索数据，检索的结果都是来自于同一个表中。

在检索数据的过程中，既可以检索所有的列，也可以检索部分列。

数据将按照SELECT子句后面指定的列的顺序显示。

如果使用星号*，则表示检索所有的列，这时数据将按照定义表时指定的列的顺序显示。

示例：

SQL>SELECT*FROMemp;

SQL>SELECTempno,ename,job,sal,deptnoFROMemp;

SQL>SELECTenameAS"姓名",jobAS"职位",hiredateAS"工作日期",salAS"工资"

FROMemp;

SQL>SELECTename"姓名",job"职位",hiredate"工作日期",sal"工资"

FROMemp;

SQL>SELECT'编号是'||empno||'的雇员,姓名是'||ename||'的工作是'||jobFROMemp;

SQL>SELECTDISTINCTjobFROMemp;

3.3.2过滤数据

在SELECT语句中可以使用WHERE子句过滤数据，只检索那些满足过滤条件的数据。

通过过滤数据，可以从大量的数据中获取自己所需要的数据。

1.比较运算符

比较操作符

说明

=

等于

<>、!

=

不等于

>=

大于等于

<=

小于等于

>

大于

<

小于

ANY

与一个列表中的任何值进行比较

ALL

与一个列表中的所有值进行比较

示例：

SQL>SELECTename,job,hiredate,salFROMempWHEREempno=7521;

SQL>SELECTename,job,hiredate,salFROMemp

WHEREjob=any（'CLERK','ANALYST'）;

2.SQL运算符

SQL运算符

说明

BETWEEN

指定条件在两个值之间，包括边界值

LIKE

匹配的字符样式，一般用于模糊查询

IN

匹配的一个列表值

ISNULL

匹配空值

注意：

表中SQL运算符可以与NOT运算符取反处理，例如，NOTLIKE，NOTBETWEEN和ISNOTNULL等。

示例：

SQL>SELECTempno,ename,job,salFROMempWHEREenameLIKE'S%';

SQL>SELECTempno,ename,job,salFROMempWHERE

empnoIN（7369,7521,7789）;

SQL>SELECTempno,ename,job,salFROMempWHERE

empnoNOTIN（7369,7521,7789）;

SQL>SELECTempno,ename,job,salFROMempWHERE

salBETWEEN1500AND2000;

SQL>SELECT*FROMempWHEREcommISNULL;

3.逻辑运算符

使用逻辑运算符可以将简单的条件组合起来。

运算符

说明

NOT

取反，当条件为真时，结果为假；当条件为假时，结果为真。

AND