第1章 Oracle关系数据库 Oracle 11g完全学习手册

一、安装oracle11g步骤及查看oracle系统

安装步骤：

1. 选择高级安装，下一步

2. 选择默认的企业版，下一步

3. 下一步，下一步。。。

4.到指定数据库方案的口令的页面，个人喜好，我将所有用户都设为同一口令。下一步

5.安装。。。在安装的过程将创建一个oracle实例以及一些用户信息。

安装过程中需要基础数据库名和sid，还有用户口令。

 查看oracle服务：

 桌面上“我的电脑-”图标，点右键->管理-->左边服务和应用程序-->服务

 

二、用户解锁

oracle数据库自带了许多用户，如system、sys和scott等，但是默认下只对其中五个用户进行了解锁：sys、system、dbsnmp、sysman和mgmt_view。

      用户解锁步骤：

1.select  username, account_status  from dba_users;在SQL Plus窗口中敲入这行，显示如下图：

account_status的值为open为解锁状态，explred为过期状态，locked为锁定状态。上图不只五个用户为open是因为我后来创建过用户了，(*^__^*) 嘻嘻……

2.用一个没有没有解锁的用户连接，如下图：

如果一个用户没有解锁，那么不能用这个用户连接到数据库，不能对这个用户的一些对象执行操作，比如它的表。

3.用系统用户登进去后，使用解锁语句：alter  user  outln  account  unlock;显示如下图

4.为outln用户指定口令：alter  user  outln  identified   by  wanghui;将用户口令设置为wanghui，如下图

解锁完成。

最后检查：用系统用户登陆进去，继续使用select  username, account_status  from dba_users;在SQL Plus查看，如下图：

额外小知识：oracle 怎样查看某用户下的所有表的表名？

答：如果是用该用户登录使用以下语句：
SELECT * FROM USER_TABLES;
如果是用其他用户：
SELECT * FROM ALL_TABLES WHERE OWNER='USER_NAME' ;

 

三、OEM

Oracle  Enterprise  Manager（OEM）提供了基于web界面的、可用于管理单个Oracle数据库的工具。由于OEM采用基于WEB的应用，它对数据库的访问也采用HTTP/HTTPS协议，即使用三层数据结构方位Oracle数据库服务。在成功安装玩oracle后，OEM也就被安装完毕。访问地址为：https://localhost:1158/em

访问前要开启OEM服务了才行额~~

就是：OracleOraDb11g_home1ClrAgent和OracleDBConsoleora11g

访问界面如下：

不过不建议使用它，我们应该使用语句以熟悉oracle底层。

 

四、体系结构

Oracle数据库从存储结构上可以分为物理存储结构和逻辑存储结构，从实例结构上可以分为内存结构与进程结构。

       Oracle的物理存储结构是由存储在磁盘中的操作系统文件所组成的，Oracle在运行时需要使用这些文件。一般Oracle数据库在物理上主要由三种类型的文件组成：数据文件(*.dbf)、控制文件(*.ctl)和重做日志文件(*.log)。

1.数据文件

     数据文件(Data  File)是指存储数据库数据的文件。数据库中所有数据最总都保存在数据文件中。比如表中的记录和索引等。如果数据文件中的某些数据被频繁访问，则这些数据会被存储在内存的缓冲区中。读取数据时，Oracle系统会首先从内存的数据缓冲区中查找相关数据信息，早不到再到数据库文件中把数据读取出来，存放到内存的数据缓冲区中，供查询使用；存储数据同理，执行提交操作时才由Oracle的后台进程DBWn将数据写入数据文件。

执行select  file_name  from dba_data_files;查看数据库文件，出现如下图：

上图是oracle11g这个数据库实例的数据文件，都是.dbf结尾的文件。

2.控制文件

       控制文件(Control  File)是一个很小的二进制单位，用于描述和维护数据库的物理结构。控制文件相当重要，它存放有数据库中数据文件和日志文件的信息。Oracle数据库在启动时需要访问控制文件，在数据库使用过程中，数据库需要不断更新控制文件，由此可见，一旦控制文件受损，那么数据库将无法正常工作。

执行select name from v$controlfile;查看控制文件，出现如下图：

3.重做日志文件(简称日志文件)

     重做日志文件(Redo  Log  File)是记录数据库中所有修改信息的文件，简称日志文件。其中，修改信息包括数据库中数据的修改信息和数据库结构的修改信息等，例如删除表中的一行数据或删除表中的一列。但是查询操作是不会被记录到日志文件中的。日志文件可以保证数据库安全，是进行数据库备份与恢复的重要手段。如果日志文件受损，数据库同样可能无法正常运行。

逻辑存储结构，如下图 ：

1.表空间

       表空间是Oracle中最大的逻辑存储结构，它与物理上的一个或多个数据文件相对应，每个Oracle数据库都至少拥有一个表空间，表空间的大小等于构成该空间的所有数据文件大小的综合。表空间永固存储用户在数据库中创建的所有内容，例如用户在创建表时，可以指定一个表空间存储该表，如果用户没有指定表空间，则Oracle系统会将用户创建的内容存储到默认的表空间中。

下图是oracle默认安装的表空间：

表空间	说明
system	系统表空间，用于存储系统的数据字典、系统的管理信息和用户数据表等
sysaux	辅助系统表空间。用于减少系统表空间的负荷，提高系统作业效率，该表空间由Oracle系统自动维护，一般不用于存储用户数据
temp	临时表空间。用于存储临时的数据，例如存储排序时产生的临时数据。一般情况下，数据库中的所有用户都 使用temp作为默认的临时表空间
undotbsl	撤销表空间。用于在自动撤销管理方式下存储撤销信息。在撤销表空间中，用户不可以在撤销表空间中创建任何数据库对象
users	用户表空间，用于存储永久性对象和私有信息

查看数据库系统表空间的语句：select  tablespace_name  from  dba_dablespaces;如下图：

2.数据字典

     Oracle数据字典(Data  Dictionary)是存储在数据库中的所有对象信息的知识库，Oracle数据库管理系统使用数据字典获取对象信息和安全信息，Oracle数据库管理系统使用数据字典获取对象信息和安全信息，而用户和数据库系统管理员则用数据字典来查询数据库信息。Oracle数据字典保存有数据库中对象和段的信息，例如表、视图、索引、包、存储过程以及用户、权限、角色、审计和约束等相关信息。

数据字典视图分五大类，如下表：

视图类型	说明
USER视图	USER视图的名称已user_为前缀，用来记录用户对象的信息。例如user_tables视图
ALL视图	ALL视图的名称以all_为前缀，用来记录用户对象的信息以及被授权访问的对象信息。例如all_synonyms视图
DBA视图	DBA视图的名称以dba_为前缀，用来记录数据库实例的所有对象信息。例如dba_tables视图
V$视图	V$视图的名称以v$为前缀，用来记录与数据库活动相关的性能动态信息。例如v$datafile视图
GV$视图	GV$视图的名称以gv$为前缀，用来记录分布式环境下所有实例的动态信息。例如gv$lock视图

部分基本数据字典，如下表：

字典名称	说明
dba_tables	所有用户的所有表的信息
dba_tab_colums	所有用户的表的字段信息
dba_views	所有用户的所有视图信息
dba_synonyms	所有用户的所有同义词信息
dba_sequences	所有用户的序列信息
dba_constraints	所有用户的表的约束信息
dba_indexes	所有用户的表的索引简要信息
dba_ind_colums	所有用户的索引的字段信息

与数据库组件相关的数据字典，如下图：

组件	数据字典中的表或视图	说明
数据库	v$datafile	记录系统的运行情况
表空间	dba_dablespaces	系统表空间的基本信息
	dba_free_space	系统表空间的空闲空间的信息
控制文件	v$controlfile	系统控制文件的基本信息
	v$controlfile_record_section	系统控制文件中记录文档段的信息
	v$parameter	系统各参数的基本信息
数据文件	dba_data_files	系统数据文件以及表空间的基本信息
	v$filestat	来自控制文件的数据文件信息
	v$datafile_header	记录数据文件头部分的基本信息

常用动态性能视图如下表：

视图名称	说明
V$fixed_tabel	显示当前发行的固定对象说明
V$instance	显示当前实例的信息
V$latch	显示锁存器性能的统计数据
V$librarycache	显示有关库缓存性能的统计数据
V$rollstat	显示联机的回滚段的名字
V$rowcache	显示活动数据字典的统计
V$sga	显示有关系统全局区的总结信息
V$sgastat	显示有关系统全局区的详细信息
V$sort_usage	显示临时段的大小及会话
V$sqlarea	显示SQL区的SQL信息

 

以下语句还没用过，不太了解。。。

SELECT  table_name,  tablespace_name,  owner  FROM  dba_tables  WHERE  owner = 'SCOTT';

SELECT username,  terminal  FROM  v$session  WHERE  username  IS  NOT  NULL;

SELECT  instance_name,  host_name,  status  from  v$instance;

DESCRIBE  V$INSTANCE;

 

五、SQLPlus工具

SQL*Plus工具主要用于数据查询和数据处理。利用SQL*Plus可以将SQL和Oracle专有的PL/SQL结合起来进行数据查询和处理。

1. SQL*Plus工具可以实现以下功能：

 a)连接数据库，完成数据库管理

 b)对数据库表可以执行插入、修改、删除、查询操作、以及执行SQL、PL/SQL块。

 c)查询结果的格式化、运算处理、保存、打印以及输出Web格式。

 d)显示任何一个表的字段定义，并与终端用户交互。

 e)运行存储在数据库中的子程序或包。

  f)启动/停止数据库实例。

2. SQL*Plus连接与断开数据库

   a)启动SQL*Plus，连接到默认数据库

   b)从命令行连接到数据库

   c)使用SQL*Plus命令连接与断开数据库

   a方式连接，如下图：
    

打开SQL Plus，输入用户名和密码，回车，如下图：

  b方式连接，开始菜单-->运行-->cmd，弹出dos窗口中输入sqlplus  ztuser/1qaz2wsx@ora11g ，即用户名/口令@数据库名，这种方式的好处是可以连接到不同的数据库，而a方式默认连接到本机的数据库，但是这用别人会看到用户口令，不安全。如下图：

   c连接方式：通过在SQL>这个命令输入CONNECT  用户名/口令 连接数据库，还通过DISCONNECT断开数据，如下图：

3.使用DESCRIBE命令

 常用的SQL*Plus命令，如下表：

命令	功能
HELP[topic]	查看命令的使用方法，topic表示需要查看的命令名称。例如：HELP DESC
HOST	使用该命令可以从SQL*Plus环境切换到操作系统环境，一边执行操作系统命令
HOST操作系统命令	执行操作系统命令，例如：HOST notepad.exe，打开一个记事本
CLEAR SCR[EEN]	清屏操作
SHOW[ALL|USER|SGA|ERRORS|REL[EASE]|PARAMETERS]	查看SQL*Plus的所有系统变量值信息、当前是哪个用户在使用SQL*Plus、显示SGA大小、查看错误信息、数据库版本信息、系统初始化参数信息
DESC[RIBE]	查看对象的结构，这里的对象可以是表、视图、存储过程、函数和包等。例如：DESC dual

 
SQL*Plus: Release 11.2.0.1.0 Production on 星期六 9月 10 23:21:13 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

请输入用户名:  ztuser
输入口令:

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> HELP

 HELP
 ----

 Accesses this command line help system. Enter HELP INDEX or ? INDEX
 for a list of topics.

 You can view SQL*Plus resources at
     http://www.oracle.com/technology/tech/sql_plus/
 and the Oracle Database Library at
     http://www.oracle.com/technology/documentation/

 HELP|? [topic]

SQL> HELP describe

 DESCRIBE
 --------

 Lists the column definitions for a table, view, or synonym,
 or the specifications for a function or procedure.

 DESC[RIBE] {[schema.]object[@connect_identifier]}

SQL> HOST
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.

D:\app\Administrator\product\11.2.0\dbhome_1\BIN>SQLPLUS ztuser/1qaz2wsx

SQL*Plus: Release 11.2.0.1.0 Production on 星期六 9月 10 23:22:19 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> HOST notepad.exe

SQL> SHOW user
USER 为 "ZTUSER"
SQL> DESCRIBE dual
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 DUMMY                                              VARCHAR2(1)

SQL> DESC ztuser.sysmenu
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 ID                                        NOT NULL NUMBER(9)
 MENUNAME                                           VARCHAR2(100)
 MODULEID                                  NOT NULL NUMBER(9)
 URL                                                VARCHAR2(200)
 MENUINDEX                                          NUMBER(9)
 PARENTMENUID                                       NUMBER(9)
 PROJSTATE                                          NUMBER(9)
 MENUTYPE                                           NUMBER(9)

SQL>

4. 编辑SQL语句

下表是常用的命令：

命令	功能
A[PPEND]text	将text附加到当前行之后
C[HANGE]/old/new	将当前行中的old替换为new
CL[EAR]BUFF[ER]	清除缓存区中的所有行
I[NPUT]text	插入指定的文本text
DEL m n	删除从第m行到第n行之间的命令行
L[IST]n	列出第n行，即查看缓冲区中所的命令
R[UN]或/	显示缓冲区中保存的语句，并运行这些语句
n	将第n行作为当前行
n text	使用text文本代替第n行信息
O text	在第一行之前插入text文本

 5.使用SAVE命令

使用SAVE命令可以将当前缓冲区的内容保存到文件中，这样，即使缓冲区中的内容被覆盖，也保留有前面的执行语句。SAVA命令的语法如下：SAV[E]  [FILE]  file_name  [CRE[ATE]] | REP[LACE]  |  APP[END]]

a)file_name  表示将SQL*Plus缓冲区的内容保存到由file_name指定的文件中。

b)CREATE   表示创建一个file_name文件，并将缓冲区中的内容保存到该文件。该选项为默认值。

c)APPEND   如果file_name文件已经存在，则将缓冲区中的内容，追加到file_name文件的内容之后；如果该文件不存在，则创建该文件。

d)REPLACE   如果file_name文件已存在，则覆盖file_name文件的内容，如果不存在，则创建该命令。

 注意，如果不指定文件地址时，比如>SAVE   ztuser.sql，则生成的文件默认路径为

       D:\app\Administrator\product\11.2.0\dbhome_1\BIN下。

 6. 使用GET命令(将一个文件的内容读取到缓冲区中)

       语法：GET  [FILE]  file_name  [LIST | NOLIST]

      a)FILE  文件路径

      b)file_name  表示一个指定的文件，将该文件的内容读入SQL*Plus缓冲区中。

      c)LIST  列出缓冲区中的语句。

      d) NOLIST  不列出缓冲区中的语句。

    如果不指定文件路径，默认读取D:\app\Administrator\product\11.2.0\dbhome_1\BIN下的文件。

 7. 使用START命令(读取文件的内容到缓冲区中并运行)

  语法：STA[RT]  {url | file_name}

   a)url   用来指定一个URL地址，例如 http://host.domain/script.sql

   b)file_name  指定一个文件。该命令将file_name文件的内容读入SQL*Plus缓冲区中，然后运行缓冲区中的内容。

  此外，还可以用@来代替START，例如：START 'E:\wanghui.sql' 等价于@ 'E:\wanghui.sql' ，两者可以不用单引号。

8. 使用EDIT命令(将SQL*Plus缓冲区的内容复制到一个名为afiedt.buf的文件中，然后启动操作系统默认的编辑器打开这个文件，

     并且文件内容能够进行编辑，Windows操作系统中默认的编辑器是Notepad(记事本))。

     语法：ED[IT] [file_name]

     其中，file_name默认为afield.buf，也可以指定一个其他的文件。

9. 使 用SPOOL命令(经SQL*Plus中的输出结果复制到一个指定的文件中，或者把查询结果发送到打印机中，直到

    使用SPOOL  OFF命令为止)

   语法：SPO

1.   [file_name  [CRE[ATE] | REP[LACE] | APP[END]]  |  OFF  |  OUT  ]

       a)file_name  指定一个操作系统文件

       b)CREATE  创建一个指定的file_name文件

       c)REPLACE  如果指定的文件已经存在，则替换该文件

       d)APPEND  将内容附加到一个已经存在的文件中

       e)OFF  停止将SQL*Plus中的输出结果复制到file_name文件中，并关闭该文件

        f)OUT  启动该功能，将SQL*Plus中的输出结果复制到file_name文件中

   10. 使用临时变量

          在Oracle数据库中，可以使用变量来编写通用的SQL语句，在运行SQL语句时，为变量输入值，就会在SQL语句中将变量替换成这些值。  临时变量只是在使用它的SQL中有效，变量值不能保留。临时变量也被称为替换变量。在SQL语句中，如果某个变量前面使用了&符号，那么就表示该变量是一个临时变量。执行SQL语句时，系统会提示用户为该变量提供一个具体的数据。

   如下例子：
   SQL> select * from sysmenu s where s.id=&temp;
   输入 temp 的值:  1
   原值    1: select * from sysmenu s where s.id=&temp
   新值    1: select * from sysmenu s where s.id=1

   如果是两个一样的临时变量，那么可以用&&符号来定义临时变量。例如：

   SQL>select  &&temp  ，id  from  emp  where   &&temp >1231

   输入 temp 的值:  empno

   原值    1: select  &&temp  ，id  from  emp  where   &&temp >1231

   新值    1: select  empno，id  from  emp  where   empno>1231

    11. 使用已定义变量

    已定义变量是指具有明确定义的变量，该变量的值会一直保留到被显式地删除、重定义或退出SQL*Plus为止。

   在SQL语句中，可以在使用变量之前对变量进行定义，然后同一个SQL语句中可以多次使用这个变量。定义变量可以使用DEFINE或ACCEPT命令；删除变量，可以使用UNDEFINE命令。

   使用define可以查看所有已定义的变量，如下：

   SQL> define
   DEFINE _DATE           = "11-9月 -11" (CHAR)
   DEFINE _CONNECT_IDENTIFIER = "ora11g" (CHAR)
   DEFINE _USER           = "ZTUSER" (CHAR)
   DEFINE _PRIVILEGE      = "" (CHAR)
   DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
   DEFINE _EDITOR         = "Notepad" (CHAR)
   DEFINE _O_VERSION      = "Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
   With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
   DEFINE _O_RELEASE      = "1102000100" (CHAR)
   SQL>

   命令	说明
   DEF[INE]	显示所有已定义变量
   DEF[INE] variable	显示指定变量的名称、值和其数据类型
   DEF[INE] variable=value	创建一个char类型的用户变量，并且为该变量赋初始值

    使用ACCEPT命令也可以定义变量，并且定制一个用户提示，用于提示用户输入指定变量的数据。在定义变量时，可以明确地指定该变量是NUMBER还是DATE等数据类型。为了安全性的原因，还可以将用户输入的信息隐藏起来。                                         ACCEPT语法：

   ACC[EPT]  variable  [data_type]  [FOR[MAT]  format]  [DEF[AULT]  default]  [PROMPT  text | NOPR[OMP]]  [HIDE];

   例如：ACCEPT  test  NUMBER  FORMAT  9999  PROMPT'你好，请输入EMPNO值:' HIDE  

    意思是定义一个NUMBER型的名为test的临时变量，为四个整数格式的，提示信息为“你好，请输入EMPNO值:”，隐藏输入。

   12. 使用COLUMN命令

   选项	说明
   FOR[MAT] format	将列或列名的显示格式设置为由format字符串指定的格式，format可以使用的格式如下表
   HEA[DING] text	设置由text字符串指定的列标题
   JUS[TIFY][{LEFT|CENTER|RIGHT}]	将列的输出信息设置为左对齐、居中对齐或右对齐
   WRA[PPED]	在输出结果中将一个字符串的末尾换行显示。该选项可能导致单个单词跨越多行
   WOR[D_WRAPPED]	与WRAPPED选项类似，但是单个单词不会跨越多行
   CLE[AR]	清除列的格式化
   TRUNCATED	删除第一行的字符串
   NULL text	指定列为空值时显示的内容
   PRINT	显示列标题
   NOPRINT	隐藏列标题

   format格式元素，如下表：

   元素	说明	举例
   An	为[VAR]CHAR类型的列内容设置宽度。如果内容超过指定的宽度，则内容自动换行	A5
   9	设置NUMBER列的显示格式	999 999
   $	浮动的货币符号	$9,999
   L	本地货币符号	L9999
   .	小数点位置	9999.99
   ,	千位分隔符	9,999

   13 使用PAGESIZE命令 

        设置每一页的大小，从而控制每一页显示的数据量。

        PAGESIZE命令的语法：SET  PAGESIZE  n

        n表示每一页的正整数，最大值可以为50,000，默认值为14；

        页不是仅仅由输出的数据行构成的，而是由SQL*Plus显示到屏幕上的所有输出结果构成，包括标题和空行等。

   14. 使用LISTSIZE命令

   15. 创建简单报表

   命令			说明
   TTI[TLE] [printspec[text|variable]...]|[OFF|ON]			指定出现在报表中每一个页面顶端的页眉。其中printspec的可选值有TEFT、CENTER、RIGHT、BOLD、FORMAT text、COL n、S[KIP][n]。ON选项表示启用设置；OFF选项表示取消设置。还可以带有用户变量(内容由系统来维护)，如SQL.PNO页号、SQL.LNO号等
   								BTI[TLE][printspec[text|variable]...]|[OFF|ON]			指定出现在报表中每一个页面底端的页脚

        有一个report.sql的文件，内容如下：

   TTITLE   LEFT  '日期：'_DATE  CENTER  '执行scott.emp(员工表)'  RIGHT  '页：'  FORMAT  999  SKIP 2  SQL.PNO

   BTITLE  CENTER '谢谢使用报表！'

   SET  ECHO  OFF

   SET  VERIFY  OFF

   SET  PAGESIZE  30

   SET  LINESIZE  120

   CLEAR  COLUMNS

   COLUMN  empno  HEADING  '员工编号'  FORMAT  9999、

   COLUMN  ename  HEADING  '员工姓名'  FORMAT  A10

   COLUMN  mgr  HEADING  '上级编号'  FORMAT 9999

   COLUMN  hiredate  HEADING '受雇日期'  JUSTIFY  CENTER

   COLUMN  sal  HEADING  '员工工资'  FORMAT  $99.999.99

   SELECT  empno,  ename, mgr,  hiredate, sal  from  scott.emp;

   CLEAR  COLUMNS

   TTITLE  OFF

   BTITLE  OFF

   16. 计算小计