第二章 Oracle数据库应用
第二章 Oracle数据库应用
2.1 表空间和用户权限下管理
2.1.1 表空间
2.1.1.1 分类：
永久性表空间
临时性表空间
撤销表空间
2.1.1.2 表空间的目的
对不同的用户分配不同的空间
将不同的数据文件创建到不同的磁盘
2.1.1.3 创建表空间
2.1.1.3.1 语法：
CREATE TABLESPACE tablespacename
DATAFILE 'filename' [SIZE integet [K|H]]
[AUTOEXTEND [OFF | ON]];
2.1.1.4 删除表空间
2.1.1.4.1 语法：
DROP TABLESPACE tablespacename;
2.1.2 自定义用户权限
2.1.2.1 Sys:超级用户
2.1.2.2 System:系统管理员
2.1.2.3 Scott:示范用户
2.1.3 数据库权限管理
2.1.3.1 系统权限：
2.1.3.1.1 定义：在数据库中执行某种系统级别的权限操作
2.1.3.1.2 常用系统权限：
CREATE SESSION:连接到数据库
CREATE TABLE:创建表
CREATE VIEW:创建实体
CREATE SEQUENCE:创建序列
2.1.3.2 对象权限
2.1.3.2.1 定义：用户对数据库中具体对象所拥有的权限
2.1.3.2.2 获取权限途径：
CONNECT:连接到数据库的用户，特别是不需要创建表的用户，通常授予该权限；
RESOURCE：更为可靠的和正式的数据库用户可授予该角色；
DBA:数据库管理员，用于管理数据库的最高权限；
2.1.3.2.3 授予权限的语法：
GRANT 权限 | 角色 TO 用户；
2.1.3.2.4 撤销权限的语法：
REVOKE 权限 | 角色 FROM 用户；
2.1.3.2.5 数据库用户安全设计原则
1、按最小分配原则
2、用户分为管理，应用，维护，备份四类
3、不允许使用Sys和system用户建立数据库应用对象
4、禁止GRANT dba TO user;
2.2 序列
2.2.1 创建序列
语法：
CREATE SEQUENCE sequence_name
[START WITH integer] ---->指定要生成的第一个序列号
[INCREMENT BY integer] ---->指定序列号之间的间隔
[MAXVALUE integer | NOMAXVALUE] ---->指定序列可以生成的最大值，如果指定了NOMAXVALUE ，最大为10*27
[MINVALUE integer | NOMINVALUE] ---->指定序列可以生成的最小值，如果指定了NOMINVALUE ，最小为10*26
[CYCLE | NOCYCLE] ---->预先分配一组序列号
[CACHE integer | NOCACHE]; ---->不会分配一组序列号，如果两个都忽略，系统默认缓存20个序列号
2.2.2 访问序列
NEXTVAL:第一次使用时，返回该序列的初始值
CURRVAL:返回序列的当前值
2.2.3 更改序列
语法：
ALTER SEQUENCE [schema.]sequence_name
[INCREMENT BY integer]
[MAXVALUE integer | NOMAXVALUE]
[MINVALUE integer | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE integer | NOCACHE];
注：不能修改序列的START WITH参数，修改序列时，应注意升序序列的最小值应小于最大值；
2.2.3 删除序列
语法：
DROP SEQUENCE [schema.]sequence_name
例：DROP SEQUENCE seql;--->从数据库中删除seql序列
2.2.3 使用序列
2.3 同义词
2.3.1 同义词的用途
1.简化ＳＱＬ语句
2.隐藏对象的名称和所有者
3.位分布式数据库的远程对象提供了位置透明性
4.提供对对象的公共访问
2.3.2 同义词分类
2.3.2.1 私有同义词
1、创建语法：
CREATE [OR REPLACE] SYNONYM [schema.]synonym_name--->同义词名称
FOR [schema.]object_name --->在同义词存在的情况下替换该同义词
2.3.2.2 公有同义词
1、创建语法：
CREATE [OR REPLACE] PUBLIC SYNONYM [schema.]synonym_name
FOR [schema.]object_name
2.3.2.3 私有同义词和公有同义词的区别
1、私有同义词只能在当前模式西访问，不能和当前模式的对象同名
2、公有同义词可被所有的数据库用户访问
3、创建同义词需要拥有一定的权限
2.3.2 删除同义词
语法：
DROP [PUBLIC] SYNONYM [schema.]synonym_name
2.4 序列
2.4.1 什么是序列？
定义：序列是与表关联的可选结构，是一种快速访问数据库的途径，可提高数据库性能
2.4.2 序列的分类
2.4.2.1、分区或非分区索引 单列或组合索引
2.4.2.2、B树索引（标识索引） 唯一或非唯一索引
2.4.2.3、正常或反向键索引 基于函数索引
2.4.2.4、位图索引
2.4.2.5 B树索引
语法：
CREATE [UNIQUE] INDEX index_name ON table_name(column_list)
[TABLESPACE tablespace_name]
UNIQUE:指定唯一索引
index_name：创建索引的名称
column_list：在上创建索引的列名列表，可基于多列
2.4.2.6 唯一或非唯一索引
唯一索引定义:定义索引的列中任何两行都没有重复值
非唯一索引定义: 单个关键字可以有多个与其关联的行
2.4.2.7 反向键索引
定义：保存序列顺序的同时反转索引列的字节
2.4.2.8 位图索引
优点：1、对于大批即时查询
2、相比其他索引技术占用空间减少
3、配置低的终端硬件上，性能也很好
2.4.2.9 其他索引
组合索引：在表内多列上创建，索引列不必和表中的列顺序一样
基于函数索引：使用的函数或表达式时间正在建立的索引的标识的一列或多列，则创建基于函数索引
2.4.3 创建序列原则
1~8 教材58~59页
2.4.4 删除索引
2.4.4.1 DROP INDEX语句
例：DROP INDEX 索引名;
2.4.4.2 何时删除索引
1、应用程序不再需要索引
2、执行批量加载钱删除索引
3、索引已经损坏
2.4.5 重建索引
2.4.5.1 ALTER INDEX...REBUILD语句
例：ALTER INDEX 索引名 REBUILD NOREVERSE;
2.4.5.2 何时重建索引
1、用户表被移动到新的表空间后
2、索引中已包含很多已删除的项
3、需将现有的正常索引转换成反向键索引
2.5 分区表
2.5.1 什么分区表
2.5.1.1 定义：把一个表中的所有行分为几个部分，并储存在不同的位置，被分区的表称为分区表
2.5.1.2 优点：
1、改善表的查询
2、表更容易管理
3、便于备份和恢复
4、提高数据安全性
2.5.2 分区表的分类
2.5.2.1、范围分区
2.5.2.2、列表分区
2.5.2.3、散列分区
2.5.2.4、复合分区
2.5.2.5、间隔分区
2.5.2.6、虚拟分区
--样例
--创建表空间
CREATE TABLESPACE worktbs
DATAFILE 'D:\Berlin\Y2T198\WORKTBS01.DBF'
SIZE 10M
AUTOEXTEND ON;
--删除表空间
DROP TABLESPACE worktbs;
--创建用户
CREATE USER martin
IDENTIFIED BY martinpwd
DEFAULT TABLESPACE worktbs;
--TEMPORARY TABLESPACE temp;
--修改口令
ALTER USER martin
IDENTIFIED BY mpwd;
--删除用户
DROP USER martin CASCADE;
--授予connect和resource两个角色
GRANT CONNECT,RESOURCE TO martin;
--撤销connect和resource两个角色
REVOKE CONNECT,RESOURCE FROM martin;
--允许查看emp表中的记录
GRANT SELECT ON scott.emp TO martin;
--允许更新emp表中的记录
GRANT UPDATE ON scott.emp TO martin;
--创建序列
CREATE SEQUENCE seq1
START WITH 10 --指定要生成的第一个序列号
INCREMENT BY 1 --指定序列号之间的间隔
MAXVALUE 2000 --指定序列可以生成的最大值，如果指定了NOMAXVALUE ，最大为10*27
NOCYCLE --预先分配一组序列号
CACHE 30; --不会分配一组序列号，如果两个都忽略，系统默认缓存20个序列号
--插入数据
CREATE TABLE toys
(
toyid NUMBER(5),
toyname VARCHAR2(20),
toyprice NUMBER(4,2)
);
INSERT INTO toys(toyid,toyname,toyprice)
VALUES(seq1.NEXTVAL,'TWENTY',25);
INSERT INTO toys(toyid,toyname,toyprice)
VALUES(seq1.NEXTVAL,'MAGIC PENCIL',75);
--查询数据
SELECT * FROM toys;
--查询当前序列的值
SELECT seq1.CURRVAL FROM dual;
--更改序列
ALTER SEQUENCE seq1
INCREMENT BY 2
MAXVALUE 3000
NOCYCLE
CACHE 35;
--删除序列
DROP SEQUENCE seq1;
--创建私有同义词SY_EMP
CREATE SYNONYM SY_EMP FOR A_hr.employee
--访问同义词
SELECT * FROM SY_EMP;
--创建公有同义词public_sy_emp
CREATE PUBLIC SYNONYM public_sy_emp FOR employee
--访问同义词
SELECT * FROM public_sy_emp;
--删除同义词
DROP SYNONYM A_oe.SY_EMP;
DROP PUBLIC SYNONYM A_hr.public_sy_emp;
--创建普通索引
CREATE UNIQUE INDEX index_unique_grade ON salgrade(grade);
--创建反向索引
CREATE INDEX index_reverse_empno ON employee(empno) REVERSE;
--创建位图索引
CREATE BITMAP INDEX index_bit_job ON employee(job);
--创建大写函数索引
CREATE INDEX index_ename ON employee(UPPER(ename));
--删除索引
DROP INDEX index_reverse_empno;
--重建索引
--将反向键索引更改为正常的B树索引
ALTER INDEX index_reverse_empno REBUILD NOREVERSE;
--分区
CREATE TABLE sales2
(
sales_id NUMBER,--销售流水号
product_id VARCHAR2(5),--产品id
sales_date DATE NOT NULL,--销售日期
sales_cost NUMBER(10),--销售金额
areacode VARCHAR2(5)--销售区域
)
PARTITION BY RANGE(sales_date)
(
PARTITION p1 VALUES LESS THAN (to_date('2012-01-01','yyyy-mm-dd')),
PARTITION p2 VALUES LESS THAN (to_date('2012-02-01','yyyy-mm-dd')),
PARTITION p3 VALUES LESS THAN (to_date('2012-03-01','yyyy-mm-dd')),
PARTITION p4 VALUES LESS THAN (to_date('2012-04-01','yyyy-mm-dd')),
PARTITION p5 VALUES LESS THAN (MAXVALUE)
);
--查看第三季度的数据
SELECT * FROM sales2 PARTITION(p3);
--删除第三季度的数据
DELETE FROM sales2 PARTITION(p3);
--创建间隔分区
CREATE TABLE sales3
(
sales_id NUMBER,--销售流水号
product_id VARCHAR2(5),--产品id
sales_date DATE NOT NULL,--销售日期
sales_cost NUMBER(10),--销售金额
areacode VARCHAR2(5)--销售区域
)
PARTITION BY RANGE(sales_date)
INTERVAL(NUMTOYMINTERVAL(3,'month'))
(PARTITION p1 VALUES LESS THAN (to_date('2012-01-01','yyyy/mm/dd')))
--插入数据
INSERT INTO sales2 VALUES(1,'a',to_date('2013-08-1'),10,'1');
--查询分区情况
SELECT table_name,partition_name
FROM User_Tab_Partitions
WHERE table_name=UPPER('sales3');
--查询分区数据
SELECT * FROM sales3 PARTITION(sys_p2);
