Oracle学习笔记4-Oracle的基本操作

4.1  Oracle的启动与关闭

1.  启动Oracle数据库

每一个启动的数据库至少对应有一个例程，例程是Oracle用来管理数据库的一个实体。启动Oracle数据库需要执行三个操作步骤： 1.启动例程、2.装载数据库、3.打开数据库。

Oracle的各种启动方式

1、一般启动

（1）启动例程

当启动例程时，这些内存结构和服务进程得到分配，初始化和启动。此时例程还没有与一个确定的数据库相关联，或者说数据库是否存在对例程的启动并没有影响，即还没有装载数据库，在启动例程的过程中只会使用STRATUP语句中指定的（或使用默认的）初始化参数文件。如果初始化参数文件或参数设置有误，则无法启动例程。启动例程包括执行如下几个任务：

• 读取初始化参数文件，默认时读取SPFILE服务器参数文件，或读取PFILE选项指定的文本参数文件。
• 根据该初始化参数文件中有关SGA区、PGA区的参数以及其设置值，在内存中分配相应的空间。
• 根据该初始化参数文件中有关后台进程的参数及其设置值，启动相应的后台进程。
• 打开跟踪文件、预警文件

如果使用STARTUP NOMOUNT命令启动例程 （但不打开控制文件，也不装载数据库）通常使用数据库的这种状态来创建一个新的数据库，或创建一个新的控制文件。

（2）装载数据库

         装载数据库时，例程将打开数据库的控制文件，根据初始化参数control_files的设置，找到控制文件，并从中获取数据库物理文件(即数据文件，重做日志文件)的位置和名称等关于数据库物理结构的信息，为打开数据库做好准备。此时数据库仍然处于关闭状态，只有数据库管理员可以通过部分命名修改数据库，用户无法与数据库建立连接或会话。

         在执行下列任务时，需要数据库处于装载状态，但无须打开数据库：

重新命名、增加、删除数据文件和重做日志文件

执行数据库的完全恢复

改变数据库的归当模式

         使用STARTUP MOUNT命令启动例程并装载数据库。

（3）打开数据库

        打开数据库时，例程将打开所有处于联机状态的数据文件和重做日志文件。如果在控制文件中列出来的任何一个数据文件或重做日志文件无法正常打开，数据库都将返回错误信息。这时需要进行数据库恢复。可以使用STARTUP OPEN (或STARTUP)依次、透明地启动例程，装载数据库并打开数据库。

启动模式及说明：

NOMOUNT 模式 ：启动例程，但不装载数据库，提示：ORACLE例程已经启动

MOUNT模式：启动例程、装载数据库，但不打开数据库 提示：ORACLE例程已经启动，数据库装载完毕

OPEN模式：启动例程、装载数据库、打开数据库 提示：例程已经启动，数据库装载完毕，数据库已经打开

启动数据库的语法如下：

STARTUP [NOMOUNT | MOUNT | OPEN | FORCE] [RESTRICT] [RESTRICT] [PFILE=’pfile_name’];

2.  关闭数据库

当执行数据库的定期备份、数据库软件的升级时，常需要关闭数据库。关闭数据库的操作与启动数据库的操作对应，也是3个步骤（或模式）：

1、关闭数据库

关闭数据库时，Oracle将重做日志高速缓存中的内容写入重做日志文件，并且将数据库高速中被改动的数据写入数据文件。在数据文件中执行一个检查点，即记录下数据库关闭的时间，然后再关闭所有的数据文件和重做日志文件。这时数据库的控制文件仍然处于打开状态，但是由于数据库已经处于关闭状态，所有用户将无法访问数据库。

2、卸载数据库

关闭数据库后，例程才能够卸载数据库，并在控制文件中更改相关的项目，然后关闭控制文件，但是例程仍然存在

3、关闭例程

上述俩步完成后接下来的操作便是终止例程，例程所拥有的所有后台进程和服务进程被终止，分配给例程的内存SGA区和PGA区被回收。

SQL*Plus关闭数据库的语法如下：

SHUTDOWN [NORMAL | TRANSACTIONAL | IMMEDIATE | ABORT];

4.2  表的创建与改进

        表是Oracle数据库最基本的对象，其他许多数据库对象（如索引、视图）都是以表为基础的。表被用于实际存储数据。系统的数据，用户的数据都被分门别类地，按行和列保存在各个表中。在关系数据库中，不同表中的数据通过主键，外键关系，彼此可能是关联的，由此这些数据成为一个逻辑上的整体。约束（constraint）可以被看作是在数据库中定义的各种规则或者策略，用来保证数据的完整性和业务规则。

1.  表的基本概念

        表是数据库存储数据的基本单元，表一般指的是一个关系表，也可以生成对象表及临时表。对象表是通过用户定义的数据类型生成的，临时表用于存储专用于某个事务或者会话的临时数据。

2.  表结构设计

1、表与列的命名

当创建一个表时，必须给它赋予一个名称，还必须给各个列赋予一个名称。

• 长度必须在1～30个字节之间
• 必须以一个字母开头
• 能够包含字母、数值、下划线_、英镑符号＃和美元符号$
• 不能使用保留字
• 若名称被围在双引号””中，唯一的要是是名称的长度在1～30个字符之间，并且不能汗有嵌入的双引号。
• 每个列名称在单个表内必须是唯一的。

 2、列的类型

Oracle数据表中列的数据类型和PL/SQL中的数据类型基本相同，在一些细节上稍微有差异，Oracle数据库表中的列的数据类型如下：

（1）字符数据类型

CHAR [(<size>) [BYTE | CHAR]]

NCHAR[(<size>)]

VARCHAR2(size [BYTE | CHAR])

NVARCHAR2(<size>)

（2）大对象数据类型

CLOB

NCLOB

BLOB

BFILE

（3）数字数据类型

NUMBER[ (<precision >[ .  <scale>])]

（4）日期和时间数据类型

DATE

TIMESTAMP [(<precision>)]

TIMESTAMP [(<precision>)] WITH TIME ZONE

TIMESTAMP [(<precision>)] WITH LOCAL TIME ZONE

INTERVAL DAY [(<precision>)] TO second

（5）二进制数据类型

ROW (<size>)

LONG ROW

（6）行数据类型

ROWID

UROWID

3、列的约束

        在Oracle中可以建立的约束条件包括NOT NULL、UNIQUE、CHECK、PRIMARY KEY 、FOREIGN KEY。

（1）NOT NULL 约束

NOT NULL即非空约束，主要用于防止NULL值进入指定的列，这些数据类型的约束是在单列上定义的。在默认情况下，Oracle准许在任何列中有NULL值。NOT NULL约束具有如下特点：

定义了NOT NULL约束的列中不能包含NULL值或无值。

只能在单个列上定义NOT NULL 约束。

在同一个表中可以在多个列上定义分别定义NOT NULL约束。

（2）UNIQUE约束

UNIQUE即唯一约束，该约束用于保证在该表中指定的各个列的组合中没有重复的值。

• 定义了UNIQUE约束的列中不能包含重复值，但是如果在一个列上仅定义了UNIQUE约束，而没有定义NOT NULL约束，则该列可以包含多个NULL值或无值。
• 可以为单个列定义UNIQUE约束，也可以为多个列的组合定义UNIQUE约束。因此UNIQUE约束即可以在列级定义，也可以在表级定义
• Oracle会自动为具有UNIQUE约束的列建立一个唯一索引（NUQUE INDEX）。如果这个列已经具有唯一或非唯一索引，Oracle将使用已有的所有索引
• 对同一个列，可以同时定义UNIQUE约束和NOT NULL约束。
• 在定义UNIQUE约束时可以为它的索引指定存储位置和存储参数。

（3）CHECK约束

        CHECK约束即检查约束，用于检查在约束中指定的条件是否得到了满足，CHECK约束具有如下特点：

• 定义了CHECK约束的列必须满足约束表达式中指定的条件，但可以为NULL。
• 在约束表达式中必须引用表中的单个列或多个列，并且约束表达式的计算机结果必须是一个布尔值
• 在约束表达式中不能包含子查询
• 在约束表达式中不能包含SYSDATE、UID、USER、USERENV等内置的SQL函数，也不能包含ROWID、ROWNUM等为例
• CHECK约束即可以在列级定义，也可用在在表级定义。
• 对同一个列，可以定义多个CHECK约束，也可以同时定义CHECK和NOT NOT NULL约束。

（3）PRIMARY KEY约束

        PRIMARY KEY约束即主键约束，其用来唯一地标识出表的每一行，并且防止出现NULL值，一个表只能有一个主键约束，PRIMARY KEY 约束具体如下：

定义了PRIMARY KEY约束的列（或列组合） 不能包含重复值，并且不能包含NULL值。

Oracle会自动为具有PRIMARY KEY约束的列（或列组合）建立一个唯一索引（unique index）和一个NOT NULL约束。

同一个表中只能够定义一个PRIMARY KEY约束，也可以在多个列的组合上定义PRIMARY KEY约束。因此PRIMARY KEY约束既可以在列级定义，也可以在表级定义。

（4）FOREIGN KEY约束

        FOREIGN KEY 约束即外键约束，通过使用外键，可保证表与表之间的参照完完整性。在参照表上定义的外键需要参照主表的主键，该约束具有如下特点：

• 定义了FOREIGN KEY约束的列中只能包含相应的在其他表中引用的列的值或为NULL。
• 定义了FOREIGN KEY约束的外键列和相应的引用列可以存在于同一个表中，这种情况称为“自引用”。
• 对同一个列，可以同时定义FOREIGN KEY约束和NOT NULL约束。
• FOREIGN KEY约束必须参照一个PRIMARY KEY 约束或UNIQUE约束。
• 可以在单个列上定义FOREIGN KEY 约束，也可以在多个列上组合上定义FOREIGN KEY约束。

3.  表的创建

在Oracle数据库中，CREATE TABLE语句的基本语法格式是：

CREATE [[GLOBAL]  TEMPORORY | TABLE | schema.] table_name(

        column1 datatype1 [DEFAULT exp1] [column1 constraint],

        column2 datetype2 [DEFAULT exp2] [column2 constraint]

[table contraint])

[ON COMMIT (DELETE | PRESERVE | ROWS)]

[ORGANIZITION { HEAP | INDEX | EXTERNAL ….}]

[PARTITION BY…(…)]

[TABLESPACE tablespace_name]

[LOGGING | NOLOGGING]

[COMPRESS | NOCOMPRESS]

NOTE:

   column1 datatype 为列指定的数据类型

   DEFAULT exp1为列指定默认值

   column1 constraint 为列定义完整性约束（constraint）

   [table constraint]为表定义完整性约束（constraint）

   [ORGANIZTION {HEAP | INDEX | EXTERNAL ….}]为表的类型，如关系型（标准，按堆组织）、临时、索引、外部型或者对象型。

   [PARTITION BY …(…)]为分区及子分区信息

   [TABLESPACE tablespace_name]指示用于存储表或索引的表空间

   [LOGGIN | NOLOGGING] 指示是否保留重做日志

   [COMPRESS | NOCOMPRESS]z指示是否压缩

4.  修改表结构

普通用户只能对自己方案中的表进行更改，而具有ALTERANYTABLE系统权限的用户可以修改任何方案的表。需要对已经建立的表进行修改的情况包括以下几种：

• 添加或删除表中的列，或者修改表中列的定义（包括数据类型、长度、默认值以及NOT NULL约束等等）
• 对表进行重新命名
• 将表移动到其他数据段或表空间中，以便重新组织表。
• 添加修改或删除表中约束条件
• 启用或禁用表中的约束条件，触发器等等。

1. 用ALTER TABLE 命令修改表结构

（1）增加列

   如果需要在表中增加新的列，新添加的列总是位于表的末尾

ALTER TABLE [schema.] table_name ADD(column definitionl,column definition2)；

（2）更改列

        如果需要调整一个表中某些列的数据类型，长度和默认值，就需要更改这些列的属性，没有更改的列不受任何影响

ALTER TABLE[schema.] table_name MODIFY (column_name  new_attrbutes1,...)

（3）直接删除列

  当不再需要某些列时，可以将其删除。

ALTER TABLE[schema.] table_name DROP(column_name1, column_name2) [CASCADE CONSTRAINTS];

        可以在括号中使用多个列名，每个列用逗号分隔，相关列的索引和约束也会被删除，如果删除的列是一个多行约束的组成部分，那么就必须指定CASCADE CONSTRAINTS选项，这样才会删除相关的约束

（3）将列标记为UNUSED状态

        删除列时，将删除表中每条记录的相应列的值，同时释放所占用的存储空间，因此如果要删除一个大表的列，由于必须对每条记录进行处理，删除操可能会占用大量的系统资源，为了避免这种情况，可以暂时同ALTERTABLE SET UNUSED语句将要删除的列设置为UNUSED状态。

ALTER TABLE [schema.] table_name SET UNUSED (column_name, column_name….)

[CASCADE CONSTRAINTS];

        被标记为UNUSED状态的列与被删除的列之间是没有区别的，都无法通过数据字典或在查询中看到，在数据字典视图USER_UNUSED_COL_TABS,ALL_UNUSED_CLOTABS和DBA_UNUSED_COL_TABS中可以查看到数据库中有那些表哪几列被标记为UNUSED状态。

3.  索引

1.  索引的概念

        索引的功能是提高对数据表的检索效率，索引是建立在表上的，通过在表中的一个列或多个列上创建索引，却能够为数据的检索提供快捷的存取路径】减少查询时的硬盘I/O操作、加快数据的检索速度。

2.  创建索引

1、用SQL*Plus创建索引

CREATE [UNIQUE]|[BITMAP] INDEX [schema.]index_name

ON[schcma.]table_name([column1[ASC|DESC]])

CREATE [UNIQUE]|[BITMAP] INDEX [ schema.] index_ name
ON [schema.] table_ name([column1[ASC|DESC],column2[ASC|DESC],...]|[express])
[TABLESPACE tablespace_ name]
[PCTFREE n1]
[STORAGE(INITIAL n2)]
[COMPRESS n3]|[NOCOMPRESS]
[LOGGING]|[NOLOGGING]
[ONLINE]
[COMPUTE STATISTICS]
[REVERSE][NOSORT];

注意：

• PCTFREE选项用于指定为将来的INSERT操作所预留的空间百分比。
• TABLESPACE选项用于指定索引段所在的表空间。
• 如果不指定BITMAP选项，则默认创建的是B树索引。

3.  删除索引

• 索引的创建不合理或不必要，应删除该索引，以释放其占用的空间。
• 通过一段时间的监视，发现几乎没有查询，或者只有极少数查询会使用到该索引。
• 由于该索引中包含损坏的数据块，或者包含过多的存储碎片，需要首先删除该索引，然后再重建该索引。
• 如果移动了表的数据，导致索引无效，此时需要删除并重建该索引。

在SQL*Plus中删除索引

DROP INDEX index_name;

4.  视图

1.  视图的概念

        视图是由select子查询语句定义的一个逻辑表，只有定义而无数据，因此是一个“虚表”。视图是查看和操作表中数据的一种方法。

2.  创建视图

1、语法

CREATE [OR REPLACE][FORCE] VIEW [schema.]view_name

[(column1,column2,)]

AS select ... from ... where ...

[with check option][constraint constraint_name]

[with read only];

OR REPLACE：如果存在同名视图，则替换

FORCE：强制创建视图

schema：指出在哪个方案中创建视图

with check option：检查涉及的视图能否通过select子查询的where条件

constraint constraint_name：当使用with check option选项时，用于指定该视图的该约束的名称

2、创建视图的步骤

1. 编写SELECT子查询语句
2. 测试SELECT子查询语句
3. 检查查询结果的正确性
4. 使用该select子查询语句创建视图，并注意命名方面与选项 方面的规定

3.  视图更改

        由于视图只是一个虚表，其中没有数据，所以更改视图只是改变数据字典中对该视图的定义信息，而视图中的所有基础对象的定义和数据都不会受到任何影响。更改视图之后，依赖于该视图的所有视图和PL/SQL程序都将变为INVALID (失效状态)。

5.  数据操纵与数据查询

1.  复制原表插入记录

在Oracle中，可以使用CREATE TABLE table name AS语句来创建一个表并且向其中插入记录。这时AS后面需要跟一个Select子句，这条语句的功能是创建一个表，其表结构和Select子句的column列表相同，同时将该Select子句所选择的记录插入到新创建的表中。示例如下:

create table High_salary as select * from employees where salary>5000;

数据查询

1、一般查询条件

select employee_id,first_name,salary from employees where salary>5000;

2、组合查询条件

select employee_id, first_name,salary

from employees emp,departments dep

where department_name='it'

and emp.department_id=dep.department_id;

3、用group进行分组查询

select dept.department_name,count(emp.employee_id) dep_count,sum(emp.salary) total_salary,avg(salary) average_salary

from employees emp,department dep

where emp.department_id=dep.department_id

group by dep.department_name;