Oracle+11g+笔记(4)-Oracle的基本操作

Oracle+11g+笔记(4)-Oracle的基本操作

4、Oracle的基本操作

4.1 Oracle的启动和关闭

4.1.1 启动

启动数据库的语法如下：

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

1、startup nomount

非安装启动，这种方式下启动可执行：重建控制文件、重建数据库，读取init.ora文件，启动instance，即启

动SGA和后台进程，这种启动只需要init.ora文件。

SQL> conn sys/sysroot as sysdba
SQL> startup nomount;

2、startup mount

安装启动，这种方式启动下可执行：数据库日志归档、数据库介质恢复、使数据文件联机或脱机、重新定位数据文

件、重做日志文件。

执行nomount，然后打开控制文件，确认数据文件和联机日志文件的位置，但此时不对数据文件和日志文件进行

校验检查。

SQL> conn sys/sysroot as sysdba
SQL> startup mount;

3、startup open

先执行nomount，然后执行mount，再打开包括Redo log文件在内的所有数据库文件，这种方式下可访问数据库

中的数据。

SQL> conn sys/sysroot as sysdba
SQL> startup open;

数据库有3种启动模式，分别代表启动数据库的3个步骤，如表所示。当数据库管理员使用STARTUP命令时，可以

指定不同的选项来决定将数据库的启动推进到哪个启动模式。在进入某个模式后，可以使用ALTER DATABASE命令

来将数据库提升到更高的启动模式，但不能使数据库降低到前面的启动模式。

4、startup

等于以下三个命令：

startup nomount
alter database mount
alter database open

5、startup restrict

约束方式启动，这种方式能够启动数据库，但只允许具有一定特权的用户访问，非特权用户访问时，会出现以下提

示：ERROR：ORA-01035: ORACLE 只允许具有 RESTRICTED SESSION 权限的用户使用。

SQL> conn sys/sysroot as sysdba
SQL> startup restrict;

6、startup force

强制Oracle数据库启动方式，当不能关闭数据库时，可以用startup force来完成数据库的关闭，先关闭数据

库，再执行正常启动数据库命令。

SQL> conn sys/sysroot as sysdba
SQL> startup force;

7、startup pfile=参数文件名

带初始化参数文件的启动方式，先读取参数文件，再按参数文件中的设置启动数据库

例：startup pfile=E:\Oracle\admin\oradb\pfile\init.ora。

8、startup EXCLUSIVE

独占和共享启动。这种启动方式只允许一个例程使用该数据库；共享启动的参数是shared，表示允许多个例程并

行使用该数据库，即将数据库装入多个现场。

4.1.2 关闭

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

SHUTDOWN [NORMAL|TRANSACTIONAL|IMMEDIATE|ABORT];

1、shutdown normal

正常方式关闭数据库。

2、shutdown immediate

立即方式关闭数据库，在SVRMGRL中执行shutdown immediate，数据库并不立即关闭，而是在Oracle执行某些

清除工作后才关闭(终止会话、释放会话资源)，当使用shutdown不能关闭数据库时，shutdown immediate可以

完成数据库关闭的操作。

3、shutdown abort

直接关闭数据库，正在访问数据库的会话会被突然终止，如果数据库中有大量操作正在执行，这时执行

shutdown abort后，重新启动数据库需要很长时间。

4、shutdown transactional

TRANSACTIONAL选项比NORMAL 选项稍微主动些，它能在尽可能短的时间内关闭数据库。按TRANSACTIONAL 选项

关闭数据库时，Oracle 将等待所有当前未提交的事务完成后再关闭数据库。

4.2 表的创建与改进

4.2.1 表与列的命名

• 长度必须在1~30个字节之间。

• 必须以一个字母开头。

• 能够包含字母、数值、下划线符号_、英镑符号#和美元符号$。

• 不能使用保留字，如CHAR 或是 NUMBER。

• 若名称被围在双引号""中，唯一的要求是名称的长度在1~30个字符之间，并且不含有嵌入的双引号。

• 每个列名称在单个表内必须是唯一的。

4.2.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

4.2.3 列的约束

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

4.2.4 表的创建

用CREATE TABLE命令创建表。

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

CREATE [[GLOBAL] TEMPORORY|TABLE|schema.]table_name
(columnl datatype1[DEFAULT exp1][columnl constraint],
column2 datatype2[DEFAULT exp2][column2 constraint]
[table constraint])
[ON COMMIT(DELETE| PRESERVE}ROWS]
[ORGANIZITION{HEAP|INDEX|EXTERNAL...}]
[PARTITION BY...(...)]
[TABLESPACE tablespace_name]
[LOGGING|NOLOGGING]
[COMPRESSINOCOMPRESS];

其中：

• columnl datatype1 为列指定数据类型。

• DEFAULT exp1 为列指定默认值。

• column1 constraint为列定义完整性约束(constraint)。

• [table constraint]为表定义完整性约束(constraint)。

• [ORGANIZITION{HEAP|INDEX|EXTERNAL...}]为表的类型，如关系型(标准，按堆组织)、临时型、索引型、

  外部型或者对象型。

• [PARTITION BY...(...)]为分区及子分区信息。

• [TABLESPACE tablespace name]指示用于存储表或索引的表空间。

• [LOGGING|NOLOGGING]指示是否保留重做日志。

• [COMPRESS|NOCOMPRESS]指示是否压缩。

如果要在自己的方案中创建表，要求用户必须具有CREATE TABLE系统权限。如果要在其他方案中创建表，则要求

用户必须具有CREATE ANY TABLE系统权限。表的创建者必须在指定的表空间上具有空间配额或具有UNLIMITED

TABLESPACE 系统权限。

4.2.5 修改表结构

表在创建之后还允许对其进行更改，如添加或删除表中的列、修改表中的列，以及对表进行重新命名和重新组织

等。

普通用户只能对自己方案中的表进行更改，而具有ALTERANYTABLE系统权限的用可以修改任何方案中的表。需要

对已经建立的表进行修改的情况包括以下几种：

• 添加或删除表中的列，或者修改表中列的定义(包括数据类型、长度、默认值以及NOT NULL约束等)。

• 对表进行重新命名。

• 将表移动到其他数据段或表空间中，以便重新组织表。

• 添加、修改或删除表中的约束条件。

• 启用或禁用表中的约束条件、触发器等。

用ALTER TABLE命令修改表结构。

(1)、增加列

ALTER TABLE [schema.]table_name ADD(column definition1,column definition2);

新添加的列总是位于表的末尾。column definition部分包括列名、列的数据类型以及将具有的任何默认值。

(2)、更改列

ALTER TABLE [schema.]table_name MODIFY(column_namel new_attributes1,
column_name2 new_attributes2...)

(3)、直接删除列

ALTER TABLE [schema.]table_name DROP(colume_namel,colume_name2...)
[CASCADE CONSTRAINTS];

如果删除的列是一个多列约束的组成部分，那么就必须指定CASCADECONSTRAINTS选项，这样才会删除相关的约

束。

(4)、将列标记为UNUSED状态

删除列时，将删除表中每条记录的相应列的值，同时释放所占用的存储空间。因此，如果要删除一个大表中的列，

由于必须对每条记录进行处理，删除操作可能会执行很长的时间。为了避免在数据库使用高峰期间由于执行删除列

的操作而占用过多系统资源，可以暂时通过ALTERTABLE SET UNUSED语句将要删除的列设置为UNUSED状态。

ALTERTABLE [schema.]table_name SET UNUSED(column_namel,column_name2...)
[CASCADE CONSTRAINTS];

被标记为UNUSED状态的列与被删除的列之间是没有区别的，都无法通过数据字典或在查询中看到。另外，甚至可

以为表添加与UNUSED状态的列具有相同名称的新列。

在数据字典视图USER_UNUSED_COL_TABS，ALL_UNUSED_COLTABS和DBA_UNUSED_COL_TABS中可以查看到数据

库中有哪些表哪几列被标记为UNUSED状态。

4.3 索引

4.3.1 索引

创建索引的语法格式为：

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操作所预留的空间百分比。假定表已经包含了大量数据，那么在建立

  索引时应该仔细规划PCTFREE的值，以便为以后的INSERT操作预留空间。

• TABLESPACE选项用于指定索引段所在的表空间。

• 如果不指定BITMAP选项，则默认创建的是B树索引。

4.3.2 删除索引

删除索引时，如果要在自己的方案中删除索引，需要具有DROP INDEX系统权限；如果要在其他用户的方案中删除

索引，需要具有DROP ANY INDEX系统权限。

如果索引是使用CREATE INDEX 语句创建的，可以使用DROP INDEX语句删除索引；如果索引是在定义约束时由

Oracle 自动建立的，则可以通过禁用约束(DISABLE)或删除约束的方式来删除对应的索引。

注意：在删除一个表时，所有基于该表的索引也会被自动删除。

DROP INDEX index_name; 

4.4 视图

4.4.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：指出在哪个方案中创建视图。

• view_name：视图的名称。

• columnl,column2等：视图的列名。列名的个数必须与 SELECT子查询中的列个数相同。如果不提供视图的

  列名，Oracle 会自动使用子查询的列名或列别名，如果子查询包含函数或表达式，则必须为其定义列名。如

  果由 columnl,column2等指定的列名个数与SELECT子查询中列名个数不相同，则会有错误提示。

• AS SELECT：用于创建视图的SELECT子查询。子查询的类型，决定了视图的类型。创建视图的子查询不能包

  含FOR UPDATE子句，并且相关的列不能引用序列的CURRVAL 或NEXTVAL 伪列值。

• WITH CHECK OPTION：使用视图时，检查涉及的数据是否能通过SELECT子查询的 WHERE条件，否则不允许

  操作并返回错误提示。

• CONSTRAINT constraint name：当使用WITH CHECK OPTION选项时，用于指定该视图的该约束的名称。如

  果没有提供一个约束名称,Oracle 就会生成一个以SYSC开头的约束名称，后面是一个唯一的字符串。

• WITH READ ONLY：创建的视图只能用于查询数据，而不能用于更改数据。该子句不能与 ORDER BY 子句同

  时存在。

注意：同所有的子查询一样，定义视图的查询不能包含FOR UPDATE子句。

【以下创建视图Managers，该视图用于显示出所有部门经理的信息】

create view managers as select employee_id,first_name,last_name,email,phone_number,
job_id,salary,department_id from employees where employee_id in (select distinct manager_id from departments);

select * from managers;

【以下创建视图Managers，该视图用于显示出所有部门经理的信息，部门显示的是部门名称】

create view managers1 as select emp.employee_id,emp.first_name,emp.last_name,emp.email,
emp.phone_number,emp.job_id,emp.salary,dep.department_name from employees emp,departments dep where emp.employee_id in (select distinct manager_id from departments) and emp.department_id = dep.department_id;

select * from managers1;

【以下创建视图dep_empcount，该视图显示出部门员工数量和平均工资信息】

create view dep_empcount as select department_id,count(employee_id) emp_count,avg(salary) avg_sal from employees Group by department_id;

select * from dep_empcount;

4.4.2 视图更改

由于视图只是一个虚表，其中没有数据，所以更改视图只是改变数据字典中对该视图的定义信息，而视图中的所有

基础对象的定义和数据都不会受到任何影响。更改视图之后，依赖于该视图的所有视图和PL/SQL程序都将变为

INVALID(失效状态)。创建视图后，可能要改变视图的定义，如修改列名或修改所对应的子查询语句，但如果仍然

使用CREATE VIEW语句来修改视图，就会有错误提示告知视图创建的失败，这是由于原有视图名称的存在使得无

法创建同名视图，这时应该使用CREATE OR REPLACE VIEW方法。使用这种方法，会保留在该视图上授予的各种

权限，但与该视图相关的存储过程和视图会失效。

提示：若以前的视图中具有WITH CHECK OPTION选项，但在重定义时没有使用WITH CHECK OPTION选项，则以

前的WITH CHECK OPTION选项将被自动删除。

-- 重新编译视图
ALTER VIEW dep_empcount COMPILE;

4.4.3 删除视图

可以删除当前模式中的各种视图，无论是简单视图、连接视图，还是复杂视图。如果要删除其他模式中的视图，必

须拥有DROP ANY VIEW系统权限。

使用DROP VIEW语句删除视图，对创建该视图的基表或基础视图没有任何影响。

-- 删除视图
DROP VIEW 视图名 [CASCADE CONSTRAINTS];

4.5 数据操纵与数据查询

4.5.1 复制原表插入数据

在Oracle中，可以使用CREATE TABLE table name AS 语句来创建一个表并且向其中插入记录。这时AS后面需

要跟一个Select 子句，这条语句的功能是创建一个表，其表结构和 Select 子句的 column 列表相同，同时将该

Select 子句所选择的记录插入到新创建的表中。

create table High_Salary AS Select * from employees where salary > 5000.00;

4.5.2 使用视图

CREATE TABLE test_user (
	ID INTEGER PRIMARY KEY,
	NAME VARCHAR2(100) NOT NULL
);
insert into test_user values(1,'tom1');
insert into test_user values(2,'tom2');
insert into test_user values(3,'tom3');
insert into test_user values(4,'tom4');
insert into test_user values(5,'tom5');

create view v_test_user as select id,name from test_user;

1、用视图进行插入

使用视图进行插入时，插入的数据需要满足对应基表的相关约束。

insert into v_test_user values(5,'tom5')

ORA-00001: 违反唯一约束条件 (HR.SYS_C0011149)

insert into v_test_user values(6,null)

ORA-01400: 无法将 NULL 插入 ("HR"."TEST_USER"."NAME")

2、用视图进行修改

修改视图中的数据同样需要通过对应基表相关约束的检查。

3、用视图进行删除

删除视图中的数据同样需要通过对应基表相关约束的检查。

一般情况下设置外键关联。

4.5.3 使用PL/SQL语言

下面程序建立了游标从EMPLOYEES表中获得记录信息，其条件是DEPARTMENT_ID等于IT部门编号，将信息插入

IT_EMPLOYEES表(IT部门编号从DEPARTMENTS 表中取，即读DEPARTMENT_NAME字段为IT的

DEPARTMENT_ID信息)。

create table IT_EMPLOYEES
(
EMPLOYEE_ID NUMBER(6) not null,
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25) not null,
EMAIL VARCHAR2(25),
PHONE_NUMBER VARCHAR2(20),
JOB_ID VARCHAR2(10),
SALARY NUMBER(8,2),
MANAGER_ID NUMBER(6)
);

declare
	emp_id number(6);
	dep_id number(4);
	fname varchar2(20);
	lname varchar(25);
	c_email varchar(25);
	phone varchar2(20);
	job varchar2(10);
	n_salary number(8,2);
	m_id number(6);
	it_id number(4);
	cursor cur1 is select department_id,employee_id,first_name,last_name,email,
	phone_number,job_id,salary,manager_id from employees;
begin
	select department_id into it_id from departments where department_name='IT';
	open cur1;
	fetch cur1 into dep_id,emp_id,fname,lname,c_email,phone,job,n_salary,m_id;
	loop
		exit when cur1%NOTFOUND;
		if dep_id=it_id then
			insert into IT EMPLOYEES values(emp_id,fname,Iname,c_email,phone,job,n_salary,m_id);
		end if;
	fetch cur1 into dep_id,emp_id,fname,lname,c_email,phone,job,n_salary,m_id;
	end loop;
close cur1;
end;

SELECT * FROM IT_EMPLOYEES;

EMPLOYEE_ID|FIRST_NAME|LAST_NAME|EMAIL   |PHONE_NUMBER|JOB_ID |SALARY|MANAGER_ID|
-----------+----------+---------+--------+------------+-------+------+----------+
        103|Alexander |Hunold   |AHUNOLD |590.423.4567|IT_PROG|  9000|       102|
        104|Bruce     |Ernst    |BERNST  |590.423.4568|IT_PROG|  6000|       103|
        105|David     |Austin   |DAUSTIN |590.423.4569|IT_PROG|  4800|       103|
        106|Valli     |Pataballa|VPATABAL|590.423.4560|IT_PROG|  4800|       103|
        107|Diana     |Lorentz  |DLORENTZ|590.423.5567|IT_PROG|  4200|       103|

4.5.4 数据查询

1、一般条件查询

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

2、组合条件查询

select employee_id,first_name,salary from employees emp,departments dep
Where department_name='IT' and emp.department_id=dep.department_id;

select employee_id,first_name,salary from employees emp,departments dep
Where department_name='IT' And emp.department_id=dep.department_id And salary>5000.00;

3、用 group进行分组查询

select dep.department_name,count(emp.employee_id) dep_count,
Sum(emp.salary) total_salary,avg(salary) average_salary
From employees emp,departments dep
Where emp.department_id=dep.department_id
Group by dep.department_name;

create view dep_salary as select department_id,count(employee_id) dep_count,
Sum(salary) total_salary,avg(salary) average_salary
From employees emp Group by department_id;

select dep.department_name,average_salary
From dep_salary,departments dep Where dep_salary.department_id=dep.department_id
And average_salary>5000.00;