Oracle笔记(二)

数据的更新操作

1、数据增加

如果现在要想实现数据的增加操作，则可以使用如下的语法完成：

INSERT INTO 表名称 [(字段1,字段2,…)] VALUES(值1,值2,…);

如果要想进行增加数据的话，则以下的几种数据类型要分别处理：

增加数字：直接编写数字，例如：123；
增加字符串：字符串应该使用“’“声明；
增加DATE数据：
- 第一种：可以按照已有的字符串的格式编写字符串，例如“’17-12月-80’”；
- 第二种：利用TO_DATE()函数将字符串变为DATE型数据；
- 第三种：如果设置的时间为当前系统时间，则使用SYSDATE；

可是对于数据的增加也有两种操作格式：完整型、简便型；

范例：增加数据 —— 完整语法编写

INSERT INTO myemp(empno,ename,hiredate,sal,mgr,job,comm)
VALUES(8888,'张三',TO_DATE('1960-08-17','yyyy-mm-dd'),8000,7369,'清洁工',1000);

INSERT INTO myemp(empno,ename,hiredate,sal,mgr,job,deptno)
VALUES(8889,'李四',SYSDATE,3000,7369,'清洁工',30);

范例：增加数据 —— 简便语法，不写增加的列名称

INSERT INTO myemp VALUES(8889,'李四','清洁工',7369,SYSDATE,3000,null,30);

很明显虽然简便语法代码少了，可是这种操作并不可取，所以在实际的开发之中，没有任何一个人会写简便语法，这样根本就不利于程序的维护，都编写完整的操作。

2、数据修改

如果要想修改表中已有的数据，则可以按照如下的语法进行：

UPDATE 表名称 SET 更新字段1=更新值1,更新字段2=更新值2,… [WHERE 更新条件(s)];

范例：更新雇员编号是7369的基本工资为5000，奖金为2000，职位修改为MANAGER，雇佣日期修改为今天

UPDATE myemp SET sal=5000,comm=2000,job='MANAGER',hiredate=SYSDATE WHERE empno=7369;

范例：所有人的工资改为7500

UPDATE myemp SET sal=7500;

如果更新时候不加上更新操作，则意味着更新全部的数据，但是这种做法绝对不可取，如果现在表中的数据量较大的话，这种更新所耗费的时间是相当长的，而且性能也会明显降低。

3、数据删除

当数据不再需要之后，就可以使用以下的语法删除数据：

DELETE FROM 表名称 [WHERE 删除条件(s)];

与更新一样，如果没有写删除条件的话，则表示删除全部数据；

范例：删除所有在1987年雇佣的雇员

DELETE FROM myemp WHERE TO_CHAR(hiredate,'yyyy')=1987;

一定要记住，如果删除的时候没有匹配条件的数据存在，则更新的记录为“0”，包括更新操作也是一样的。

范例：删除表中的全部记录

DELETE FROM myemp;

一般对于删除的操作而言，尽可能少去使用，包括以后在进行系统开发的时候，对于所有的删除操作之前实际上都建议大家先给出一个确认的提示框，以防止用户误删除。

Delete和Truncate

¨ 都是删除表中的数据

¨ Delete操作可以rollback，可以闪回

¨ Delete操作可能产生碎片，并且不释放空间

¨ Truncate是清空表

二、事务处理

事务基础

数据库事务由以下的部分组成:

l 一个或多个DML 语句

l 一个 DDL(Data Definition Language – 数据定义语言) 语句

l 一个 DCL(Data Control Language – 数据控制语言) 语句

数据库事务：

l 以第一个 DML 语句的执行作为开始

l 以下面的其中之一作为结束:

• 显示结束: commit rollback

• 隐式结束(自动提交): DDL语言,DCL语言, exit(事务正常退出)

• 隐式回滚(系统异常终了): 关闭窗口，死机，掉电

使用COMMIT 和 ROLLBACK语句,我们可以：

l 确保数据完整性。

l 数据改变被提交之前预览。

l 将逻辑上相关的操作分组。

所有的事务处理操作都是针对于每一个session进行的，在Oracle数据库之中，把每一个连接到数据库上的用户都称为一个session，每一个session之间彼此独立，不会有任何的通讯，而每一个session独享自己的事务控制，而事务控制之中主要使用两个命令：

事务的回滚：ROLLBACK，更新操作回到原点；
事务的提交：COMMIT，真正的发出更新的操作，一旦提交之后无法回滚；

l 回滚到保留点

¨ 使用 SAVEPOINT 语句在当前事务中创建保存点。

¨ 使用 ROLLBACK TO SAVEPOINT 语句回滚到创建的保存点。

1.1.1. 事务进程

¨ 自动提交在以下情况中执行:

DDL 语句。

DCL 语句。

不使用 COMMIT 或 ROLLBACK 语句提交或回滚，正常结束会话exit。

¨ 会话异常结束或系统异常会导致自动回滚。

提交或回滚前的数据状态
- 改变前的数据状态是可以恢复的
- 执行 DML 操作的用户可以通过 SELECT 语句查询之前的修正
- 其他用户不能看到当前用户所做的改变，直到当前用户结束事务。
- DML语句所涉及到的行被锁定，其他用户不能操作。
提交后的数据状态
- 数据的改变已经被保存到数据库中。
- 改变前的数据已经丢失。
- 所有用户可以看到结果。
- 锁被释放，其他用户可以操作涉及到的数据。
- 所有保存点被释放。

l 提交数据示例：

l 数据回滚后的状态

使用 ROLLBACK 语句可使数据变化失效:

l 数据改变被取消。

l 修改前的数据状态被恢复。

l 锁被释放。

1.1.2. 数据库的事务隔离级别

¨ 对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:

脏读: 对于两个事物 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段. 之后, 若 T2 回滚, T1读取的内容就是临时且无效的.
不可重复读: 对于两个事物 T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段. 之后, T1再次读取同一个字段, 值就不同了.
幻读: 对于两个事物 T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行. 之后, 如果 T1 再次读取同一个表, 就会多出几行.

¨ 数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题.

¨ 一个事务与其他事务隔离的程度称为隔离级别. 数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱

l 数据库提供的 4 种事务隔离级别:

l Oracle 支持的 2 种事务隔离级别：READ COMMITED, SERIALIZABLE. Oracle 默认的事务隔离级别为: READ COMMITED

l Mysql 支持 4 中事务隔离级别. Mysql 默认的事务隔离级别为: REPEATABLE READ

三、数据伪列

范例：查询前5条记录

SELECT ROWNUM,empno,ename,job,hiredate,sal FROM emp
WHERE ROWNUM<=5;

范例：查询6-10条记录

按照正常的思维肯定是直接进行BETWEEN…AND的判断；

SELECT ROWNUM,empno,ename,job,hiredate,sal FROM emp
WHERE ROWNUM BETWEEN 6 AND 10;

这个时候并没有返回任何的数据，因为ROWNUM不是真实列，而要想真正的实现这种查询，思路是：先查询前10条记录，之后再显示后5条记录，要依靠子查询完成。

SELECT * FROM(
      SELECT ROWNUM m,empno,ename,job,hiredate,sal FROM emp
      WHERE ROWNUM<=10) temp
WHERE temp.m>5;

如果现在按照这个思路，下面就可以给出日后程序中所需要分页的功能实现。

范例：显示前5条记录

当前所在页（currentPage）为1；

每页显示的记录长度（lineSize）为5；

第一页：

SELECT * FROM(
      SELECT ROWNUM m,empno,ename,job,hiredate,sal FROM emp
      WHERE ROWNUM<=5) temp
WHERE temp.m>0;

范例：显示中间的5条记录

当前所在页（currentPage）为2；

每页显示的记录长度（lineSize）为5；

第二页：

SELECT * FROM(
      SELECT ROWNUM m,empno,ename,job,hiredate,sal FROM emp
      WHERE ROWNUM<=10) temp
WHERE temp.m>5;

范例：显示第三页的内容，currentPage=3，lineSize=10；

SELECT * FROM(
      SELECT ROWNUM m,empno,ename,job,hiredate,sal FROM emp
      WHERE ROWNUM<=15) temp
WHERE temp.m>10;

以上的程序就是分页显示操作的核心代码。

表的创建及管理

一、常用的数据字段

№	数据类型	关键字	描述
1	字符串	VARCHAR2(n)	其中n表示的是字符串所能保存的最大长度，基本上保存200个左右的内容
2	整数	NUMBER(n)	表示最多为n位的整数，有时候也可以使用INT代替
3	小数	NUMBER(n,m)	其中m为小数位，n-m为整数位，有时候也可以使用FLOAT代替
4	日期	DATE	存放日期-时间
5	大文本	CLOB	可以存储海量文字（4G），例如存储《三国演艺》、《红楼梦》
6	大对象	BLOB	存放二进制数据，例如：电影、MP3、图片、文字

一般在开发之中使用最多的：VARCHAR2()、NUMBER、DATE、CLOB，而对于BLOB字段一般使用较少，首先BLOB可以存放4G的二进制数据，但是存放进去之后，一是数据库过于庞大，二是读取不方便；

二、表的创建

如果现在要想进行表的创建，可以使用如下的操作语法：

CREATE TABLE 表名称 (
    字段1 数据类型 [DEFAULT默认值],
    字段2 数据类型 [DEFAULT默认值],
    … …,
    字段n 数据类型 [DEFAULT默认值]
);

建表规则

表名和列名:

l 必须以字母开头

l 必须在 1–30 个字符之间

l 必须只能包含 A–Z, a–z, 0–9, _, $, 和 #

l 必须不能和用户定义的其他对象重名

l 必须不能是Oracle 的保留字

l Oracle默认存储是都存为大写

l 数据库名只能是1~8位，datalink可以是128位，和其他一些特殊字符

下面创建一张成员表（member），有如下保存的信息：姓名、年龄、生日、个人简介。

CREATE TABLE member (
     name VARCHAR2(50) DEFAULT '无名氏',
     age NUMBER(3),
     birthday DATE DEFAULT SYSDATE,
     content CLOB
);

三、表的复制

在之前学习过表的复制操作，下面给出其完整的操作语法：

CREATE TABLE 复制表名称 AS 子查询;

四、为表重命名

RENAME 旧的表名称 TO 新的表名称;

l 执行RENAME语句改变表, 视图, 序列, 或同义词的名称

l 必须是对象的拥有者

五、修改表结构

ALTER TABLE 表名称 ADD(列名称 数据类型 [DEFAULT 默认值],
　　列名称 数据类型 [DEFAULT 默认值],…);

如果增加的数据列没有默认值，则所有已有的数据的列的内容都是null，而如果增加的列指定了DEFAULT默认值的话，则所有已有的数据列都是设置的默认值。

现在也可以修改已有的表结构，此时的语法如下：

ALTER TABLE 表名称 MODIFY(列名称 数据类型 [DEFAULT 默认值],
　　列名称 数据类型 [DEFAULT 默认值],…);

六、清空表（Truncate）

l TRUNCATE TABLE 语句:

• 删除表中所有的数据

• 释放表的存储空间

l TRUNCATE语句不能回滚

l 可以使用 DELETE 语句删除数据

七、删除表（Drop Table）

l 数据和结构都被删除

l 所有正在运行的相关事物被提交

l 所有相关索引被删除

l DROP TABLE 语句不能回滚，但是可以闪回

约束

一、非空约束（NOT NULL）：NK

当数据表中的某个字段上的内容不希望设置为null的话，则可以使用NOT NULL进行指定。

范例：定义一张数据表

DROP TABLE member PURGE;
CREATE TABLE member(
    mid NUMBER,
    name VARCHAR2(50) NOT NULL
);

二、唯一约束（UNIQUE）：UK

唯一约束指的是每一列上的数据是不允许重复的，例如：email地址每个用户肯定是不重复的，那么就使用唯一约束完成。

DROP TABLE member PURGE;
CREATE TABLE member(
    mid NUMBER,
    name VARCHAR2(50) NOT NULL,
    email VARCHAR2(50) UNIQUE
);

约束的名字建议的格式“约束类型_字段”，例如：“UK_email”，指定约束名称使用CONSTRAINT完成。

DROP TABLE member PURGE;
CREATE TABLE member(
    mid NUMBER,
    name VARCHAR2(50) NOT NULL,
    email VARCHAR2(50),
    CONSTRAINT UK_email UNIQUE(email)
);

三、主键约束（Primary Key）：PK

主键约束 = 非空约束 + 唯一约束，在之前设置唯一的约束的时候发现可以设置为null，而如果现在使用了主键约束之后则不能为空，而且主键一般作为数据的唯一的一个标记出现，例如：人员的ID。

为了约束的使用方便，下面为主键约束起一个名字。

DROP TABLE member PURGE;
CREATE TABLE member(
    mid NUMBER,
    name VARCHAR2(50) NOT NULL,
    CONSTRAINT pk_mid PRIMARY KEY(mid)
);

四、检查约束（Check）：CK

检查约束指的是为表中的数据增加一些过滤条件，例如：

设置年龄的时候范围是：0~200；
设置性别的时候应该是：男、女；

范例：设置检查约束

DROP TABLE member PURGE;
CREATE TABLE member(
    mid NUMBER,
    name VARCHAR2(50) NOT NULL,
    sex VARCHAR2(10) NOT NULL,
    age NUMBER(3),
    CONSTRAINT pk_mid PRIMARY KEY(mid),
    CONSTRAINT ck_sex CHECK(sex IN('男','女')),
    CONSTRAINT ck_age CHECK(age BETWEEN 0 AND 200)
);

五、主-外键约束

之前的四种约束都是在单张表中进行的，而主-外键约束是在两张表中进行的，这两张表是存在父子关系的，即：子表中某个字段的取值范围由父表所决定。

例如，现在要求表示出一种关系，每一个人有多本书，应该定义两张数据表：member（主）、book（子）；

DROP TABLE member PURGE;
DROP TABLE book PURGE;
CREATE TABLE member(
    mid NUMBER,
    name VARCHAR2(50) NOT NULL,
    CONSTRAINT pk_mid PRIMARY KEY(mid)
);
CREATE TABLE book(
    bid NUMBER,
    title VARCHAR2(50) NOT NULL,
    mid NUMBER,
    CONSTRAINT pk_bid PRIMARY KEY(bid),
    CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid)
);

1、删除数据的时候，如果主表中的数据有对应的子表数据，则无法删除；

范例：删除member表中mid为1的数据

DELETE FROM member WHERE mid=1;

错误提示信息：“ORA-02292: 违反完整约束条件 (SCOTT.FK_MID) - 已找到子记录”。

此时，只能先删除子表记录，之后再删除父表记录：

DELETE FROM book WHERE mid=1;
DELETE FROM member WHERE mid=1;

但是这种操作明显不方便，如果说现在希望主表数据删除之后，子表中对应的数据也可以删除的话，则可以在建立外键约束的时候指定一个级联删除的功能，修改数据库创建脚本：

DROP TABLE member PURGE;
DROP TABLE book PURGE;
CREATE TABLE member(
    mid NUMBER,
    name VARCHAR2(50) NOT NULL,
    CONSTRAINT pk_mid PRIMARY KEY(mid)
);
CREATE TABLE book(
    bid NUMBER,
    title VARCHAR2(50) NOT NULL,
    mid NUMBER,
    CONSTRAINT pk_bid PRIMARY KEY(bid),
    CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid)

ON DELETE CASCADE

);

此时由于存在级联删除的操作，所以主表中的数据删除之后，对应的子表中的数据也都会被同时删除。

2、删除数据的时候，让子表中对应的数据设置为null

当主表中的数据删除之后，对应的子表中的数据相关项也希望将其设置为null，而不是删除，此时，可以继续修改数据表的创建脚本：

DROP TABLE member PURGE;
DROP TABLE book PURGE;
CREATE TABLE member(
    mid NUMBER,
    name VARCHAR2(50) NOT NULL,
    CONSTRAINT pk_mid PRIMARY KEY(mid)
);
CREATE TABLE book(
    bid NUMBER,
    title VARCHAR2(50) NOT NULL,
    mid NUMBER,
    CONSTRAINT pk_bid PRIMARY KEY(bid),
    CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid)

ON DELETE SET NULL

);
INSERT INTO member(mid,name) VALUES(1,'张三');
INSERT INTO member(mid,name) VALUES(2,'李四');
INSERT INTO book(bid,title,mid) VALUES(101,'Java开发',1);
INSERT INTO book(bid,title,mid) VALUES(102,'Java Web开发',2);
INSERT INTO book(bid,title,mid) VALUES(103,'EJB开发',2);
INSERT INTO book(bid,title,mid) VALUES(105,'Android开发',1);
INSERT INTO book(bid,title,mid) VALUES(107,'AJAX开发',1);

3、删除父表之前必须首先先删除对应的子表，否则无法删除

DROP TABLE book PURGE;
DROP TABLE member PURGE;

但是这样做明显很麻烦，因为对于一个未知的数据库，如果要按照此类方式进行，则必须首先知道其父子关系，所以在Oracle之中专门提供了一个强制性删除表的操作，即：不再关心约束，在删除的时候写上一句“CASCADE CONSTRAINT”。

DROP TABLE member CASCADE CONSTRAINT PURGE;
DROP TABLE book CASCADE CONSTRAINT PURGE;

此时，不关心子表是否存在，直接强制性的删除父表。

合理做法：在以后进行数据表删除的时候，最好是先删除子表，之后再删除父表。

六、修改约束

约束本身也属于数据库对象，那么也肯定可以进行修改操作，而且只要是修改都使用ALTER指令，约束的修改主要指的是以下两种操作：

为表增加约束：

ALTER TABLE 表名称 ADD CONSTRAINT 约束名称 约束类型(字段);

删除表中的约束：

ALTER TABLE 表名称 DROP CONSTRAINT 约束名称;

可以发现，如果要维护约束，肯定需要一个正确的名字才可以，可是在这五种约束之中，非空约束作为一个特殊的约束无法操作，现在有如下一张数据表：

DROP TABLE member CASCADE CONSTRAINT PURGE;
CREATE TABLE member(
    mid NUMBER,
    name VARCHAR2(50) NOT NULL,
    age NUMBER(3)
);

范例：为表中增加主键约束

ALTER TABLE member ADD CONSTRAINT pk_mid PRIMARY KEY(mid);

增加数据：

INSERT INTO member(mid,name,age) VALUES(1,'张三',30);
INSERT INTO member(mid,name,age) VALUES(2,'李四',300);

现在在member表中已经存在了年龄上的非法数据，所以下面为member表增加检查约束：

ALTER TABLE member ADD CONSTRAINT ck_age CHECK(age BETWEEN 0 AND 250);

这个时候在表中已经存在了违反约束的数据，所以肯定无法增加。

范例：删除member表中的mid上的主键约束

ALTER TABLE member DROP CONSTRAINT pk_mid;

可是，跟表结构一样，约束最好也不要修改，而且记住，表建立的同时一定要将约束定义好，以后的使用之中建议就不要去改变了。

七、查询约束

在Oracle之中所有的对象都会在数据字典之中保存，而约束也是一样的，所以如果要想知道有哪些约束，可以直接查询“user_constraints”数据字典：

SELECT owner,constraint_name,table_name FROM

user_constraints

但是这个查询出来的约束只是告诉了你名字，而并没有告诉在哪个字段上有此约束，所以此时可以查看另外一张数据字典表“user_cons_columns”；

COL owner FOR A15;
COL constraint_name FOR A15;
COL table_name FOR A15;
COL column_name FOR A15;
SELECT owner,constraint_name,table_name,column_name FROM

user_cons_columns

集合

在数学的操作之中存在交、差、并、补的概念，而在数据的查询中也存在此概念，有如下几个连接符号：

UNION：连接两个查询，相同的部分不显示；
UNION ALL：连接两个查询，相同的部分显示；
INTERSECT：返回两个查询中的相同部分；
MINUS：返回两个查询中的不同部分；

视图，序列，索引，同义词

一、视图

l 视图是一种虚表.

l 视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。

l 向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句.

l 视图向用户提供基表数据的另一种表现形式

l 视图的优点

¨ 简化复杂查询

¨ 限制数据访问

¨ 同样的数据，可以有不同的显示方式

l 注意：不建议通过视图对表进行修改

视图不能提高性能

l 使用下面的语法格式创建视图

• FORCE：子查询不一定存在

• NOFORCE：子查询存在（默认）

• WITH READ ONLY:只能做查询操作

l 子查询可以是复杂的 SELECT 语句

视图中使用DML的规定

l 可以在简单视图中执行 DML 操作

l 当视图定义中包含以下元素之一时不能使用delete:

l 组函数
l GROUP BY 子句
l DISTINCT 关键字
l ROWNUM 伪列

当视图定义中包含以下元素之一时不能使用update :

l 组函数
l GROUP BY子句
l DISTINCT 关键字
l ROWNUM 伪列
l 列的定义为表达式

当视图定义中包含以下元素之一时不能使用insert :

l 组函数
l GROUP BY 子句
l DISTINCT 关键字
l ROWNUM 伪列
l 列的定义为表达式
l 表中非空的列在视图定义中未包括

选项一：WITH CHECK OPTION

上面所创建的视图，是存在一个创建条件的“WHERE deptno=20”，那么如果现在更新视图中的这个条件呢？

UPDATE myview SET deptno=30 WHERE empno=7369;

此时更新的是一张视图，但是视图本身并不是一个具体的数据表，而且现在更新的操作又是视图的创建条件，很明显这样的做法不可取，所以此时为了解决这个问题，可以加入WITH CHECK OPTION;

CREATE OR REPLACE VIEW myview AS
　　SELECT * FROM emp WHERE deptno=20
　　WITH CHECK OPTION;

此时再次执行视图的更新操作，出现以下错误提示：

ORA-01402: 视图 WITH CHECK OPTIDN where 子句违规

意味着现在根本就不能去更新视图的创建条件。

选项二：WITH READ ONLY

虽然使用WITH CHECK OPTION可以保证视图的创建条件不被更新，但是其他的字段却允许更新。

UPDATE myview SET sal=9000 WHERE empno=7369;

与之前的问题一样，视图本身不是具体的真实数据，而是一些查询语句，所以这样的更新并不合理，那么在创建视图的时候建议将其设置为只读视图：

CREATE OR REPLACE VIEW myview AS
　　SELECT * FROM emp WHERE deptno=20
　　WITH READ ONLY;

此时再次发出更新的操作，则直接提示如下错误：

ORA-01733: 此处不允许虚拟列

而且一定要注意的是，以上给出的是一个简单的操作语句视图，如果现在视图中的查询语句是统计操作，则根本就不可能更新。

CREATE OR REPLACE VIEW myview AS
　　SELECT d.deptno,d.dname,d.loc,COUNT(e.empno) count,AVG(e.sal) avg
　　FROM emp e,dept d
　　WHERE e.deptno(+)=d.deptno
　　GROUP BY d.deptno,d.dname,d.loc;

二、序列

序列: 可供多个用户用来产生唯一数值的数据库对象

l 自动提供唯一的数值

l 共享对象

l 主要用于提供主键值

l 将序列值装入内存可以提高访问效率

创建序列

CREATE SEQUENCE myseq;

当一个序列创建完成之后，可以通过以下两种方式访问序列：

序列名称.nextval：让序列增长到下一个内容；
序列名称.currval：取得当前序列的内容；

在Oracle之中如果要想操作currval，则首先必须使用nextval；

序列一般都作为主键使用，例如，下面定义一张表：

DROP TABLE mytab PURGE;
CREATE TABLE mytab (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(20) NOT NULL
);

现在向mytab表中增加数据：

INSERT INTO mytab(id,name) VALUES(myseq.nextval,'姓名');

在默认情况下，序列从0开始，每次增长1，那么现在也可以修改；

范例：创建序列，从10开始，每次增长2

DROP SEQUENCE myseq;
CREATE SEQUENCE myseq INCREMENT BY 2 START WITH 10;

范例：希望定义一个序列，这个序列可以在1、3、5、7、9之间循环出现；

DROP SEQUENCE myseq;
CREATE SEQUENCE myseq INCREMENT BY 2 START WITH 1 MAXVALUE 10 MINVALUE 1 CYCLE NOCACHE;

关于序列中的CACHE解释：

在Oracle数据库之中，由于序列被经常使用到，所以Oracle为了揽性能，将序列的操作形式做了如下的处理。

准备了一块空间，这个空间之中，为用户准备好了若干个已经生成好的序列，每次操作的时候都是从这块空间之中取出序列的内容，但是这样有一个问题，如果现在数据库的实例关闭了，那么保存在这块空间中的内容就有可能消失了，但是虽然消失了，可是数据库已经增长好了，这样就会出现跳号的事情，而如果要想取消掉这种问题，则最好的方式是将序列设置为不缓存，使用NOCACHE声明。

三、同义词

同义词就是意思相近的一组词语，对于同义词的操作之前一直在使用，例如，现在有如下一个查询语句：

SELECT SYSDATE FROM dual;

在之前说过“dual”是一张虚拟表，但是虚拟表也肯定应该有它的用户，经过查询可以发现，这张表是属于SYS用户的，但是这个时候就出现一个问题，在之前讲解过，不同的用户要想访问其他用户的表，则需要写上“用户.表名称”，那么为什么此时scott用户访问的时候直接使用dual即可，而不是使用“sys.dual”呢，这个实际上就是同义词的应用，dual表示的是sys.dual的同义词，而同义词在Oracle之中称为SYNONYM，同义词的创建语法如下：

CREATE [PUBLIC] SYSNONYM 同义词的名称 FOR 用户名.表名称;

四、索引

l 一种独立于表的模式对象, 可以存储在与表不同的磁盘或表空间中

l 索引被删除或损坏, 不会对表产生影响, 其影响的只是查询的速度

l 索引一旦建立, Oracle 管理系统会对其进行自动维护, 而且由 Oracle 管理系统决定何时使用索引. 用户不用在查询语句中指定使用哪个索引

l 在删除一个表时, 所有基于该表的索引会自动被删除

l 通过指针加速 Oracle 服务器的查询速度

l 通过快速定位数据的方法，减少磁盘 I/O

在Oracle之中创建索引有以下两种方式：

主键约束：如果一张表中的列上存在了主键约束的话，自动创建索引；
手工创建：在某一个操作列上指定一个索引；

但是这种索引有一个最大的问题，即：如果要想实现性能的提高，则必须始终维持以上的一棵树，那么如果说现在这棵树上的数据需要频繁修改的话，则代码的性能肯定会有所降低。

所以一般索引只使用在不会频繁修改的表中，而如果一张表上频繁修改数据且又使用了索引的话，性能肯定会严重降低，所以性能的揽永远都是相对的。

以下情况可以创建索引:

l 列中数据值分布范围很广
l 列经常在 WHERE 子句或连接条件中出现
l 表经常被访问而且数据量很大，访问的数据大概占数据总量的2%到4%
l 什么时候不要创建索引

下列情况不要创建索引:

l 表很小
l 列不经常作为连接条件或出现在WHERE子句中
l 查询的数据大于2%到4%
l 表经常更新

用户管理

创建用户

范例：创建一个user用户，密码为password

CONN sys/change_on_install AS SYSDBA;
CREATE USER user IDENTIFIED BY password;

注意：“ORA-00988：口令缺失或无效” 错误，这种情况通常发生于创建 Oracle 例程时指定了非正常的全局数据库名称或系统用户密码。请注意全局数据库名称不能以数字开头，口令密码也不能由数字开头。

Oracle对密码的要求如下：

系统用户（SYS、SYSTEM）口令长度不能小于7个字符；
密码由1 到 30 个字符 (characters)组成；
必须以字母开头，不能是符号或者数字；
只接受字母，数字，以及三个符号 "#", "_" and "$"；
密码不能包含像"SELECT"，"DELETE"，"CREATE"这类的 Oracle/SQL 关键字；

提示用户现在没有创建SESSION的权限，在之前曾经解释过，对于sqlplusw而言，每一个用户都表示一个SESSION，如果没有创建SESSION的权限就意味着不能登录，所以下面要授权。

范例：将CREATE SESSION的权限给user用户

GRANT CREATE SESSION TO user;

将创建表的权限给user用户

GRANT CREATE TABLE TO user;

将CONNECT和RESOURCE TO user;

GRANT CONNECT,RESOURCE TO user;

但是现在一旦存在了用户的操作，那么就需要有用户的管理操作，最简单的一个功能，用户有可能丢掉自己的密码。

范例：修改密码

ALTER USER user IDENTIFIED BY password2;

但是当管理员为一个用户重置一个密码之后，往往会希望用户在第一次登录的时候可以修改密码，所以此时可以通过如下的命令让密码失效：

ALTER USER user PASSWORD EXPIRE;

也可以控制一个用户的锁定操作：

ALTER USER user ACCOUNT LOCK;

ALTER USER user ACCOUNT UNLOCK;

范例：回收user用户的权限

REVOKE SELECT,INSERT ON scott.emp FROM user;
REVOKE CONNECT,RESOURCE,CREATE TABLE,CREATE SESSION FROM user;

数据库备份

一、数据的导出和导入

数据的导出和导入是针对于一个用户的备份操作，可以按照如下的方式完成：

1、数据的导出

在硬盘上建立一个文件夹：c:\backup；
输入exp指令；
输入用户名和密码；
设置导出文件的名称：导出文件: EXPDAT.DMP；

2、数据的导入

先将表删除干净；
进入到导出文件所在的文件夹之中：c:\backup；
输入imp指令；
输入用户名和密码；
导入整个导出文件 (yes/no): no > yes

但是以上的操作只是做为一个演示，因为这种备份操作本身只适合数据量小的情况，如果数据量较大的话，这种操作是会损耗性能的，而且时间也会很长，要想解决大数据量的问题只能对数据分区操作。

二、数据表的冷备份

在数据操作之中，有可能有些用户不会进行事务的提交，那么在这种情况下很可能无法进行完整的备份操作，而所谓的冷备份指的就是在闭数据库实例的情况下进行数据库备份操作的实现。

如果要进行冷备份，则需要备份出数据库中的一些几个核心内容：

控制文件，指的是控制整个Oracle数据库的实例服务的核心文件，直接通过”v$controlfile”找到；
重做日志文件，可以进行数据的灾难恢复，直接通过”v$logfile”找到；
数据文件，表空间文件，通过”v$datafile”和”v$tablespace”找到；
核心操作的配置文件（pfile），通过”SHOW PARAMETER pfile”找到；

从实际的Oracle的部署来讲，所有的文件为了达到IO的平衡操作，要分别保存在不同的硬盘上。

确定了要备份的文件之后，下面按照如下的步骤查找：

1、使用超级管理员登录

CONN sys/change_on_install AS SYSDBA;

2、查找所有的控制文件目录

SELECT * FROM v$controlfile;

3、备份重做日志文件

SELECT * FROM v$logfile;

4、查找表空间文件

SELECT * FROM v$tablespace;
SELECT * FROM v$datafile;

5、找到pfile文件

SHOW PARAMETER pfile;

6、关闭数据库实例

SHUTDOWN IMMEDIATE;

7、将所有查找到的数据备份到磁盘上；

8、启动数据库实例

STARTUP;

发表于 2014-04-25 11:29 未响应中阅读(512) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

未响应中我是即将到来的日子
博客园首页新随笔联系订阅管理