007 SG SQL注意03

来源：互联网发布：元首最广为人知编辑：程序博客网时间：2024/05/29 11:03

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 PrivilegeTableViewSequenceProcedureALTERok ok DELETEokok EXECUTE okINDEXok INSERTokok REFERENCESokok SELECTokokok UPDATEokok

( 以上 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