【课堂笔记】【SQL】10.Creating Other Schema Objects

5dm

使用视图的目的：限制数据访问，简化查询。

简单视图可以被DML语句操作，复杂视图有些不能被DML语句操作。

修改视图和直接删除视图再重新创建的区别在于修改视图不用重新授权。

创建视图语法： 

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view

  [(alias[, alias]...)]

 AS subquery

[WITH CHECK OPTION [CONSTRAINT constraint]]

[WITH READ ONLY [CONSTRAINT constraint]];

 

 

?You cannot remove a row if the view contains the following:

–Group functions

–A GROUP BY clause

–The DISTINCT keyword

–The pseudocolumn ROWNUM keyword

 

 

5dm

You cannot modify data in a view if it contains:

?Group functions

?A GROUP BY clause

?The DISTINCT keyword

?The pseudocolumn ROWNUM keyword

?Columns defined by expressions

 

 

You cannot add data through a view if the view includes:

?Group functions

?A GROUP BY clause

?The DISTINCT keyword

?The pseudocolumn ROWNUM keyword

?Columns defined by expressions

?NOT NULL columns in the base tables that are not selected by the view

5dm

Sequeces 序列：（因为Oracle没有像SQL Server的种子标识这玩意，所以要借助序列.)

创建序列的语法： 

CREATE SEQUENCE sequence

       [INCREMENT BY n]

       [START WITH n]

       [{MAXVALUE n | NOMAXVALUE}]

       [{MINVALUE n | NOMINVALUE}]

       [{CYCLE | NOCYCLE}]

       [{CACHE n | NOCACHE}];

 

可以使用nextval,currval这两个属性。

5dm

INDEX 索引：

索引的好处：快速定位，减少I/O。

创建主键和唯一约束时系统自动创建唯一索引。

手工创建索引的语法： 

CREATE INDEX index

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

 

决定是否创建索引，参考一下情况：

 

Create an index when:
	A column contains a wide range of values
	A column contains a large number of null values
	One or more columns are frequently used together in a WHERE clause or a join condition
	The table is large and most queries are expected to retrieve less than 2% to 4% of the rows in the table
Do not create an index when:
	The columns are not often used as a condition in the query
	The table is small or most queries are expected to retrieve more than 2% to 4% of the rows in the table
	The table is updated frequently
	The indexed columns are referenced as part of an expression

 

5dm

SYNONYM 同义词：

语法： 

CREATE [PUBLIC] SYNONYM synonym

FOR    object;

 

public关键字使所有用户均可用。