1. Constraints
NOT NULL , UNIQUE , PRIMARY KEY , FOREIGN KEY , CHECK ( 单行约束的 NOT NULL , 一顶要放到单行的最后位置 )
CREATE TABLE employees ( --> 只有创建script的最后一行, 不需要 , ( 逗号 )
employee_id NUMBER(6),
job_id VARCHAR2(10) NOT NULL , --> column 级 constraint , 系统给这个constraint命名 ,修改为如下情况,可以命名 , 最后一行也要加 , 逗号
job_id VARCHAR2(10) CONSTRAINTemp_job_id_c NOT NULL
CONSTRAINT emp_id_pk --> table 级 constraint , 用户给这个 constraint 命名
PRIMARY KEY (EMPLOYEE_ID))
Constraint 可以在创建 table时指定 , 也可以在创建完 table 之后指定 . ( 一般是在创建table时指定的 )
被设定为 unique约束的列 , 可以包含 NULL 值 , 因为 NULL <> anything . ( 如果是多列组成的的 composite UNIQUE , 则要么全部为NULL , 要么全部column都不为NULL.
CHECK 约束
CREATE TABLE employees (
salary NUMBER(8,2) CONSTRAINT emp_salary_min CHECK ( salary > 0 ),
Table已经创建 , 再添加约束语法
You can add , drop enable , or disable a constraint , but you can not modify its structure .
You can add a NOT NULL constraint to an existing column by using the MODIFY clause of the ALTER TABLE statement .
You can define a NOT NULL column only if the table is empty or if the column has a value for every row .
Add a FOREIGN KEY constraint to the EMPLOYEES table to indicate that a manager must already exist as a valid employee in the EMPLOYEES table .
这个是前面提到的 , employees table 自有关系 . ( 前面的实体联系矩阵 )
ALTER TABLE employees
ADD CONSTRAINT emp_manager_fk
FOREIGN KEY (manager_id)
REFERENCES employees(employee_id)
ALTER TABLE emplyees
DROP CONSTRAINT emp_manager_fk [ CASCADE ] --> 其中 CASCADE为可选项 , option of the DROP clause any dependent constraints also to be dropped .
ALTER TABLE emplyees
DISABLE CONSTRAINT emp_manager_fk [ CASCADE ] --> 其中 CASCADE 含义同上
Disabling a unique or primary key constraint removes the unique index .
ALTER TABLE emplyees
ENABLE CONSTRAINT emp_manager_fk
A unique or primary key index is automatically created .
刚刚 CASCADE 跟它相关的被 disable的约束 , 不会被激活 .
当你想删除某列时, 由于一些相关的约束 , 导致删除不成功 , 此时用到
ALTER TABLE test1
DROP (column1,column2) CASCADE CONSTRAINTS
如果想查看 constraint , 需要用到数据字典中的 USER_CONSTRAINTS ( table ) , USER_CONS_COLUMNS ( columns )
2. VIEW
VIEW的好处 : 限制访问 , 可以将复杂的query保存成一个简单的VIEW , 提高数据独立性 ( 不用管base table 的内容 ) , 呈现不同的结果对同一数据
创建VIEW :
CREATE [ OR REPLACE ] [ FORCE | NOFORCE ] VIEW view [ ( alias1, alias2 .......)] AS Subquery
[ WITH CHECK OPTION [ CONTRAINT contraint name ] ]
[ WITH READ ONLY [ CONTRAINT contraint name ] ]
其中: FORCE 指不管 base table 是否存在都创建 view , 而 NOFORCE 只必须base table 存在的时候才能创建 , NOFORCE是默认的 .
如果想起别名 alias1 ... 别名的个数,顺序必须和Subquery中的个数相同 .
WITH CHECK OPTION 指 specifies that only rows accessible to the view can be insterted or updated
一旦创建了view , 就可以在数据字典中确认到相关信息 ( USER_VIEWS )
Data Access Using Views ( 通过view进行存取操作 )
a.It retrieves the view definition from the data dictionary table USER_VIEWS.
b.It checks access privileges for the view base table .
c.It converts the view query into an equivalent operation on the underlying base tables .
DROP VIEW view name
Inline Views
An inline view is created by placing a subquery in the FROM clause and giving thata subquery an alias .
其实就是平时用的 select 子句替换 table . 例如 FROM employees a, ( select .. from departments ) b where a.** = b.**
返回前几个或者后几个 Top-n ( sql server 中直接有对应函数 , oracle 中没有 )
SELECT ROWNUM as Rank , last name , salary
FROM ( SELECT last_name, salary FROM employees
ORDER BY salary DESC )
WHERE ROWNUM <= 3
3. Sequence , Index , Synonym
Sequence : Generates primary key values . ( A sequence is a user created object that can be shared by multiple users to generate unique integers .
Sequence numbers are stored and generated independently of tables , Therefore , the same sequence can be used for multiple tables .
sequence 和其他的对象一样,属于某个用户, owner, 在这个 owner的各个对象可以共享这个 sequence.
CREATE SEQUENCE sequence_name
[ INCREMENT BY n ] --> 增长的间隔 , 如果省略 , 默认为 1
[ START WITH n ] --> 第一个数 , 默认为 1
[ MAXVALUE n | NOMAXVALUE ]
[ MINVALUE n | NOMINVALUE ]
[ CYCLE | NOCYCLE ] --> 达到MAX或者MIN时循环 , 默认为 NOCYCLE
[ CACHE n | NOCACHE ] --> specifies how many values the Oracle Server preallocates and keep in memory ( 默认 20 )
你可以在 data dictionary 中查看 sequence 相关情况 ( USER_OBJECT , USER_SEQUENCE )
使用 Sequence
NEXTVAL : returns the next available sequence value , it returns a unique value everytime it is referenced , even for different users .
CURRVAL : obtains the current sequence value .
可以使用 NEXTVAL , CURRVAL
> The SELECT list of a SELECT statement that is not part of a subquery .
> The SELECT list of a subquery in an INSERT statement .
> The VALUES clause of an INSERT statement .
> The SET clause of an UPDATE statement .
不可以使用 NEXTVAL , CURRVAL
< The SELECT list of a view .
< A SELECT statement with the DISTINCT keyword .
< A SELECT statement with GROUP BY , HAVING , or ORDER BY clauses
< A subquery in a SELECT , DELETE , or UPDATE statement .
< The DEFAULT expression in a CREATE TABLE or ALTER TABLE statement .
( INSERT INTO departments ( department_id , department_name, location_id )
VALUES( dept_seq.NEXTVAL , 'Support' , 2500 ))
( SELECT dept_seq.CURRVAL FROM dual )
ALTER SEQUENCE sequence_name ( 仿照 CREATE SEQUENCE 写 , 但是不能修改 start with )
DROP SEQUENCE sequence_name
Index : Improves the performance of some queries .
CREATE INDEX index_name ON table ( column,column,,, )
应该创建索引 :
> 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 .
不应该创建索引 :
< The table is small .
< The columns are not often used as a condition in the query .
< Most queries are expected to retrieve more than 2 to 4% of rows in the table .
< The table is updated frequently .
< The indexed columns are referenced as part of an expression
数据字典 : USER_INDEXES , USER_IND_COLUMNS
Function-Based Indexes
CREATE INDEX index_name ON departments(UPPER( department_name) )
不能修改索引 : 如果要修改 , 就必须先 drop 再 create
DROP INDEX index_name
If you drop a table , indexes and constraints are automatically dropped , but views and sequences remain .
Synonym : Alternative name for an object .
synonym好处 : Ease referring to a table owned by another user . Shorten lengthy object names
CREATE [ PUBLIC ] SYNONYM synonym_name FOR object
CREATE PUBLIC SYNONYM dept FOR alice.departments .
The database administrator can create a public synonym accessible to all users .
DROP PUBLIC SYNONYM dept ;
有两种方法: 第一种: 被授予权限用户,比如, hr 用户有个 table是 t_department, 那么 hr 将 select 权限给了 scott 用户, 此时
scott 用户查询: select * from hr.t_department, 注意前面加了一个hr, 如果想去掉 hr, 那么就需要同义词, 在 scott 用户下,使用
create synonym t_department for hr.t_department; 这样,你如果再次查询就可以 select * from t_department.
第二种是在,授予用户即 hr 用户下修改,语句是这样的 create synonym scott.t_department for t_department, 为什么在 hr 用户下建立此同义词
在 scott 用户下就不用在输入 hr 了呢? 因为实际上市 hr 用户替 scott 用户创建了一个synonym, 注意这个 synonym 是属于 scott的,而不是属于创建者 hr.
create scott.t_department for t_department, 第一个是表示在 scott 这个 schema 里创建一个 synonym, 它等同于 hr 这个用户里的 t_department 这个table.
4.Controling users access
CREATE USER user_name IDENTIFIED BY password
GRANT privilege [ , privilege ... ] TO user [ , user | role , PUBLIC ]
GRANT create session , create table , create sequence TO scott --> 这些都是 system privilege
Once the user has been granted the priveleges , the user immediately use those privileges .
ROLE
CREATE ROLE role_name ;
GRANT create table , create view to role_name
GRANT role_name TO scoatt .
Change Your Password ALTER USER scott IDENTIFIED BY password
可以分配的 Object privilege
Object Privilege | Table | View | Sequence | Procedure |
---|---|---|---|---|
ALTER | ok | ok | ||
DELETE | ok | ok | ||
EXECUTE | ok | |||
INDEX | ok | |||
INSERT | ok | ok | ||
REFERENCES | ok | ok | ||
SELECT | ok | ok | ok | |
UPDATE | ok | ok |
( 以上 INDEX 权限是 给 table 添加 index 的权限 , 即被授予的用户可以给这个table 添加 index
REFERENCES 权限 是给用户可以引用此 table的权限 , 详细看如下例子 )
GRANT objective privilege [ columns ]
ON object
TO [ user | role | PUBLIC ]
[ WICH GRANT OPTION ] --> 可以传递授权给他人
例如 : GRANT update ( department_name , location_id )
ON departments
TO scott , role_name
一般情况 : DBA 分配 system privileges , 其他用户分配 object privilege .
Confirming Privileges Granted ( 查看权限分配情况 , 通过数据字典)
REVOKE { privilege [ , privilege ...] | ALL } --> ( 连带着那些是 WITH GRANT OPTION 全部删除 )
ON object
FROM { user [ , user ...] role | PUBLIC }
[ CASCADE CONSTRAINTS ] ( is required to remove any referential integrity constraints made to the object by means of the REFERENCES privilege ? )
( This clause is relevant only if you revoke the REFERENCES privilege or ALL [PRIVILEGES] )
Revoking an Object Privilege with CASCADE CONSTRAINTS: Example
You can grant oe the privileges REFERENCES and UPDATE on the employees table in the schema hr with the following statement:
GRANT REFERENCES, UPDATE
ON hr.employees TO oe;
oe can exercise the REFERENCES privilege to define a constraint in his own dependent table that refers to the employees table in the schema hr:
CREATE TABLE dependent
(dependno NUMBER,
dependname VARCHAR2(10),
employee NUMBER
CONSTRAINT in_emp REFERENCES hr.employees(employee_id) );
You can revoke the REFERENCES privilege on hr.employees from oe by issuing the following statement that contains the CASCADE CONSTRAINTS clause:
REVOKE REFERENCES
ON hr.employees
FROM oe
CASCADE CONSTRAINTS;
Revoking oe's REFERENCES privilege on hr.employees causes Oracle to drop the in_emp constraint, because oe required the privilege to define the constraint.
However, if oe has also been granted the REFERENCES privilege on hr.employees by a user other than you, then Oracle does not drop the constraint.
oe still has the privilege necessary for the constraint by virtue of the other user's grant.
5. Database Link
A database link connection allows local users to access data on a remote database .
The link pointer is actually defined as an entry in a data dictionary table ( 不仅仅是外部文件 ) , To access the link , you must be connected to the local database
that contains the data dictionary entry . ( A -> local ( dictionary ) DB Link nameddlk can connect remote database B , But B can't use this DB LINK ( dlk ) to
connect database A . B -> local ( dlk ) connect A database ( they must define a link tbat is stored in the data dictionary of database B )
一般 DBA 有责任创建 database link , 另外在 USER_DB_LINKS 中包含相关信息 .
CREATE PUBLIC DATABASE LINK hwms.db.com
connect to <被连接库的用户名> identified by <被连接库的密码>
USING ' Oracle 客户端(连接端)工具建立的指向被连接库服务名' --> 例如 USING 'ASDF'
例如 :
ASDF =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 109.104.2.xx)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = ABC)
)
)
SELECT * FROM fred.emp@hwms.db.com
6. SET Operate ( 集合操作 )
UNION / UNION ALL , INTERSECT , MINUS ( 其中 UNION 是过滤掉了 duplicate , UNION ALL 包括 duplicate )
The expressions in the SELECT lists must match in number and data type .
Parentheses ( ) can be used to alter the sequence of execution .
The ORDER BY clause : can appear only at the very end of the statement , will accept the column name , aliases from the first SELECT statement ,
or the positional notation ( 必须在第一个 SELECT statement中 )
例如 :
SELECT 'SING' ,3
FROM DUAL
UNION
SELECT 'I d like to teach',1
FROM DUAL
UNION
SELECT 'the world to',2
FROM DUAL
ORDER BY 2 ( 此处的2 表示是第 2 列的意思 , column , 这个就是上面说的 positional notation ( 位置符号 ) )
结果 :
'i d like to teach' 1
'the world to' 2
'SING' 3