Oracle

VIEW:一个或者多个表的逻辑数据子集。

作用：1.限制数据访问。2.简化查询语句的书写。3.提供数据独立性。4.展示相同数据的不同角度。

创建视图：CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias]...)] AS subquery

[WITH CHECK OPTION [CONSTRAINT constraint]]

[WITH READ ONLY [CONSTRAINT constraint]];

创建视图:CREATE VIEW em

AS SELECT em_id, name, ...

FROM em

WHERE name="xxxx";

SELECT * FROM em

CREATE OR REPLACE VIEW em (xx,xx,xx,...) AS SELECT xx,xx,....

FROM em

WHERE xx="xxx";

CREATE OR REPLACE VIEW em

AS SELECT * FROM em

WHERE xxx="xxx"

WITH CHECK OPTION CONSTRAINT em; 

CREATE OR REPLACE VIEW em

AS SELECT * FROM em

WHERE xxx="xxx"

WITH READ ONLY; 

DROP VIEW em;

Inline 视图：是一个带有别名的查询语句，可以再SQL中使用。

主查询中的FROM子句中的带别名的子查询。

linline视图不是数据库对象。

SELECT t.*, rownum rn 

FROM(SELECT [colnum]

)t

WHERE rn <= num;

Top-N分析语句：

SELECT [column_list], ROWNUM 

FROM (SELECT [colnum_list]

FROM table

ORDER BY Top-N_colnum)

WHERE ROWNUM <= N;

序列：1.自动生成的不重复的数值。2.一个共享的数据库对象。3.典型应用于生成主键值。4.简化应用代码的编写。

5.可以预缓存在内存中的以加速快速访问速度。

例子：

CREATE SEQEUENCE sequence

[INCREMENT BY n]

[START WITH N]

[{MAXVALUE n | NOMAXVALUE}]

[{MINVALUE n | NOMINVALUE}]

[{CYCLE | NOCYCLE}]

[{CACHE n | NOCACHE}];

创建序列DEPT_DEPTID_SEQ用于生成DEPARTMENTS表的主键

CREATE SEQUENCE dept_deptid_seq

INCREMENT BY 10

START WITH 120

MAXVALUE 9999

NOCACHE

NOCYCLE;

SELECT sequence_name, min_value,max_value,increment_by,last_number FROM user_sequences;

NEXTVAL:返回下一个可用的序列值，总是生成一个不同的值，即便是不用的用户调用。

CURRVAL:返回序列的当前值

使用:必须先调用NEXTVAL,否则CURRVAL将不包括任何值。

使用序列：INSERT INTO depart(depart_id, depart_name)

VALUES(dept_deptid_seq.NEXTVAL, "Support", 2500);

SELECT dept_deptid_seq.CURRVAL FROM dual;

使用序列注意事项：1.在内存中缓存序列的值可以加快对这些值得访问速度

2.序列的值在一下情况下回出现断号：事物被退回，系统崩溃，多个表同时使用一个序列

3.若序列在创建时采用了NOCACHE选项，可以通过查询USER_SEQUENCES的到序列的下一个可用值

修改序列：

ALTER SEQUENCE dept_deptid_seq

INCREMENT BY 20  每次增加几个

START BY 1    从1开始

MAXVALUE 999999  最大

NOCACHE   不缓存序列号，缓存序列号，可以提高效率，可能造成跳号

NOCYCLE; 循环

修改序列：1.必须是序列的拥有者或者具有该序列上饿ALTER权限

2.对序列的修改只对修改以后的序列产生影响

3.Start with 选项不能修改，若要修改，只能删除然后重建。

4.需要做一些验证工作：如更改后的最大值不能小于当前值。

DROP SEQUENCE deqt

索引：是一种数据库对象；是数据库用索引加速查询；可以用快速定位数据的方法，因而可以大大减少对磁盘的

I/O操作；与表相对独立；索引的使用是由数据库服务器自动进行的。

索引的创建：

1.自动：系统对于PRIMARY KEY和UNIQUE字段会自动创建一个索引

2.用户可以在需要索引的列上手工创建索引，以加速对相关字段的查询。

CREATE INDEX index

ON table (colnum[, colnum]...);

加速对表EMPLOYEES的列LAST_NAME的查询

CREATE INDEX emp

ON table(last_name);

何时创建索引：1.列的取值范围很广。2.列包含大量的空值。3.一个列或者多个列的组合经常出现在WHERE子句中，

或者经常出现在链接条件中。4.表的数据量很大，而大多数的查询仅返回表中2%-4%的数据。

何时不用创建索引：1.表很小。2.列不常用于查询重要条件。3.大多数查询都会返回超过表中2%-4%的数据

4.表中的数据经常被更新。5.列被引用时常出现在表达式中。

查询索引信息：1.数据字典视图USER_INDEXES中包含索引的名称以及其唯一性等信息。

2.数据字典视图USER_IND_COLUMNS中包含索引的名称，表的名称以及索引的名称等信息。

SELECT c.index_name, c.column_name,c.column_position,x.uni

FROM user_index x, user_ind_columns c

WHERE c.index_name = x.index_name

AND c.table_name = 'EMPLOYEES';

函数索引：1.函数索引是基于列的函数值或者表达式的值所创建的索引。2.可以是列的有效表达式，常量，SQL函数或任何

用户定义的函数。

CREATE INDEX upper_dept_name_idx 

ON departments(UPPER(department_name));

SELECT *

FROM departments

WHERE UPPER(department_name) = "SALES";

DROP INDEX index;

同义词：是数据库对象的另一个等价的名称，通过同义词可以简化对数据库对象的访问。

1.简化对其它用户下的对象的访问。2.缩写对象名称

CREATE [PUBLIC] SYNONYM synonym FOR object;

为视图创建同义词：CREATE SYNONYM d_sum

FOR dept_sum_vu;

DROP SYNONYM d_sum;

权限:1.数据库安全，系统安全，数据安全。2.系统权限：获得对数据库的访问权限。

3.对象权限：获得对数据库对象的操纵权限。4.模式：一组数据库对象的集合，如表，视图和序列等。

系统权限：Oracle有超过100种权限。

创建用户：CREATE USER user IDENTIFIED BY tiger;

授权用户的系统权限：GRANT privilege[,privilege...]  TO user [, user | role,PUBLIC...]; 

GRANT create session,create table,create sequence, create view TO scott;

回收权限：1.用PEVOKE命令回收授予用户的系统权限。2.具有ADMIN OPTION 系统权限的用户也可能回收授予的系统权限

3.只有用GRANT命令授予的系统权限才能被回收。

CREATE ROLE manager;

GRANT create table,create view TO manager;

GRANT manager TO DEHAAN, KOCHHAR;

修改密码:ALTER USER scott

IDENTIFIED BY lico;

对象权限：1.对象权限各种对象不同。2.对象的创建者拥有对象上的所有权限。3.对象的拥有者可以讲该对象上的任意对象

权限授权予其它用户。

GRANT obj[(columns)]

ON obj

TO {user|role|public}

[WITH GRANT OPTION];

授予对象权限：GRANT SELECT ON em TO  role.

GRANT UPDATE(column)

ON table

TO user;

PUBLIC:GRANT SELECT ,INSERT ON table TO user WITH GRANT OPTION;

GRANT SELECT ON table TO PUBLIC

ROLE_SYS_PRIVS : 授权角色的系统权限

ROLE_TAB_PRIVS:授予角色的表权限

USER_ROLE_PRIVS:‘用户具有的角色信息

USER_TAB_PRIVS_MADE:当前用户对象授予其它用户的对象权限

USER_TAB_PRIVS_RECD:当前用户授予的对象权限

USER_SYS_PRIVS:当前用户被授权的系统权限

回收权限：REVOKE语句

REVOKE{privilege[,privilege...]|All}

ON pbj

FROM {user[, user...]|role|PUBLIC}

[CASCADE CONSTRAINTS];

REVOKE select, insert

ON table

FROM user;

数据库连接：CREATE PUBLIC DATABASE LINK xxxxx

USING "sales";

SELECT * FROM address (@xxxxxx);