温习 SQL 03

1. Controling users access

1) 创建用户

    CREATE USER user_name IDENTIFIED BY password ;

    例如 : CREATE USER leon IDENTIFIED BY leon ;

2) 分配权限

    GRANT privilege [, privilige… ] TO user [, user | role, PUBLIC ]

    例如 : GRANT create session, create table, create sequence TO leon

3) 创建角色 & 分配权限

    CREATE ROLE role_name;

    CREATE create_table, create view to role_name;

4) 赋予某个用于角色

     GRANT role_name to leon ;

5) 修改用户密码

     ALTER USER leon IDENTIFIED BY password

6) 权限介绍（ object privilege )

Object privilege	table	view	sequence	procedure
ALTER	y		y
DELETE	y	y
EXECUTE				y
INDEX	y
INSERT	y	y
REFERENCES	y	y
SELECT	y	y	y
UPDATE	y	y

以上的 INDEX 权限，是指是否可以给对象添加索引。

   1:  GRANT OBJECTIVE PRIVILEGE [COLUMNS]

   2:  ON OBJECT

   3:  TO [USER | ROLE | PUBLC]

   4:  [WITH GRANT OPTION]    -- 可以传递

一般情况下，DBA分配给 system privilege , user 分配 object privilege权限。

确认权限的相关数据字典 :

Data Dictionary View	Description
ROLE_SYS_PRIVS	System privileges granted to roles
ROLE_TAB_PRIVS	Table privileges granted to roles
ROLE_TAB_PRIVS	Roles accessible by the user
USER_TAB_PRIVS_MADE	Object privileges granted on the user’s object
USER_TAB_PRIVIS_RECD	Object privileges granted to the user
USER_COL_PRIVIS_MADE	Object privileges granted on the columns of the user’s objects
USER_COL_PRIVS_RECD	Object privileges granted to the user on specific columns
USER_SYS_PRIVS	Lists system privileges granted to the user

7) 收回权限

REVOKE { privilege [, privilege] | ALL }
ON object
FROM { user [, user ..] role | public}
[CASCADE CONSTRAINTS]

-- Example

-- 1. assign privilege
GRANT REFERENCES , UPDATE ON hr.employees TO leon ;

-- 2. 因为有了 references 权限，所以，可以在 leon用户下创建一个 table ，并且引用hr.employees
CREATE TABLE dependent (
  dependno  NUMBER,
  dependname VARCHAR2(10),
  employee NUMBER CONSTRAINT for_emp REFERENCES hr.employees( employee_id) 
)

-- 3. You can revoke the REFERENCES privilege on hr.employees from leon by issuing the following statement
-- And contains the CASCADE CONSTRAINTS clause
REVOKE REFERENCES
ON hr.employees
FROM leon
CASCADE CONSTRAINTS ;
-- 4. result , drop the for_emp constraint, because leon required the privilege to define the constraint.

2. Database Link

一个 database link 准许本地用户访问远程数据库。

The link pointer is actually defined as an entry in a data dictionary table.

A : local user, ( data dictionary include db link ) db link named dlk can connect remote database B

B : local user, ( this db named dlk ) 不可以连接 A, 只有在该用户下定义一个dblink 到 A的才可以。

数据字典 USER_DB_LINKS

CREATE PUBLIC DATABASE LINK hwms.db.com
connect to <被连接库的用户名> identified by <该被连接用户名得密码>
USING 'Oracle 客户端(连接端)工具建立的志向被连接服务名'  -- 例如 ASDF 如下


-- tnsnames
ASDF =
( DESCRIPTION =
  ( ADDRESS_LIST =
    ( ADDRESS= ( PROTOCOL = TCP ) ( HOST=109.104.2.XX ) (PORT=1521) )

    )
    ( CONNECT_DATA = ( SERVICE_NAME = ABC ) )
    -- service name 不是 sid

  )

SELECT * FROM fred.emp@hwms.db.com
)

3. 集合操作

UNION / UNION ALL , INTERSECT , MINUS

其中的 order by 语句必须放到最后

例如 :

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列的意思

-- 结果： ' I‘d like to teach ' 1
--       ' the world to '      2
--       ' sing ' 3

4. 高级 group by 子句

1) ROLLUP : GROUP BY ROLLUP(A, B, C) 结果：( A,B,C) (A,B) A

2) CUBE : 全排列 GROUP BY CUBE(A, B, C) 结果 : (ABC, AB, AC, BC, A, B, C, 空 )

3) GROUPING SETS : GROUP BY GROUPING SETS ( (A,B,C), (B), (B,C))按照给定的要求分组。

5. Hierarchical retrieval ( 树形 )

SELECT [LEVEL], column, expr...
FROM TABLE
[WHERE conditions]
[START WITH conditions]
[CONNECT BY PRIOR conditions]

解析 : 如果想从父-> 子，那么首先根节点要是父亲节点，然后 connect by prior parent column = child column. 如果要想是 子 –>父, 那么首先根节点要是子节点，然后 connect by prior child column = parent column. 所以，综上，主要是根据语意确定根节点。

结合如下例子，分析：首先确认根节点为董事长，语意是自上而下，而小懂是下边的MGR，所以,connect by prior emp_id = mgr_id, 即，先找到了小懂，然后再看谁的mgr_id=小懂的emp_id,找到他们，依此类推，而如果先确认了根节点为小张，那么肯定是自下而上，即 小张的上边是他的经理， connect by prior mgr_id = emp_id，即，先找到了小张，然后，小张这行的mgr_id所对应的emp_id就是要找的内容，依此类推，所以。。。connect by prior A = B, 其中 A 是当前列的内容, B是接下来列的内容。然后是等号的关系。

某一个公司的人员结构如下；

董事长（小董）

------------------------|-----------------------

技术经理（小技） 销售经理(小售)

------|-------- ------|-------

张三 李四 刘五 周六

create table EMP
(
EMPNO NUMBER(10),//员工号
ENAME VARCHAR2(16),//员工姓名
MGR NUMBER(10)//该员工上司的员工号

)

该公司的员工信息在此表中的记录如下：

EMPNO ENAME MGR

1 小董

2 小技 1

3 小售 1

4 张三 2

5 李四 2

6 刘五 3

7 周六 3

然后就可以对递归查询实验了

SELECT substr(sys_connect_by_path(ENAME,'->'),3) EMPLOYEE FROM EMP
CONNECT BY PRIOR EMPNO= MGR

START WITH ENAME='小董'

查询结果如下：

小董

小董->小技

小董->小技->张三

小董->小技->李四

小董->小售

小董->小售->刘五

小董->小售->周六

直观现象：以【小董】为根节点向下遍历所有分支

SELECT substr(sys_connect_by_path(ENAME,'->'),3) EMPLOYEE FROM EMP
CONNECT BY EMPNO= PRIOR MGR

START WITH ENAME='张三"

查询结果如下：

张三

张三->小技

张三->小技->小董

直观现象：以【张三】为根节点向上遍历，且最后只得到一条分支

总结如下：

在此例中EMPNO为子节点端，MGR为父节点端

6. INSERT 多行插入

INSERT ALL | FIRST

[ WHEN condition then]

[ ELSE ]

1) 没有限制条件

INSERT ALL

INTO sal_history VALUES(EMPID, HIREDATE, SAL) // 列名

INTO mgr_history VALUES ( EMPID, MGR, SAL )   // 列名

SELECT employee_id EMPID, hir_date HIRDATE, SALARY SAL, manager_id MGR

FROM employees

WHERE employee_id > 200

2) 有条件的 insert all

INSERT ALL

WHEN SAL > 10000 THEN

INTO sal_history VALUES(EMPID, HIREDATE, SAL)

WHEN MGR > 200 THEN

INTO mgr_history VALUES(EMPID, MGR, SAL)

SELECT employee_id EMPID, hir_date HIRDATE, SALARY SAL, manager_id MGR

FROM employees

WHERE employee_id > 200

注意：此时，只要满足条件的都插入，例如一条数据，即满足 salary > 10000 又满足 MGR > 200，那么这两条插入语句将都执行

3) 有条件的 insert first

INSERT FIRST

WHEN SAL > 10000 THEN

INTO sal_history VALUES(EMPID, HIREDATE, SAL)

WHEN MGR > 200 THEN

INTO mgr_history VALUES(EMPID, MGR, SAL)

ELSE

INTO hirdate_history VALUES( DEPTID, HIREDATE)

SELECT employee_id EMPID, hir_date HIRDATE, SALARY SAL, manager_id MGR

FROM employees

WHERE employee_id > 200

注意：以上，只要有一个满足条件，就会跳到下一条，也就是说只插入第一个匹配的就OK了。

4）行列转换

table: sales_source_date ( EMP_ID, WEEK_ID, SALES_MON, SALES_TUE, SALES_WED, SALES_THUR, SALES_FRI )

table: sales_info ( EMP_ID, WEEK_ID, SALES)

INSERT ALL

INTO sales_info VALUES ( emp_id, week_id, sales_mon )

INTO sales_info VALUES ( emp_id, week_id, sales_tue )

INTO sales_info VALUES ( emp_id, week_id, sales_wed )

INTO sales_info VALUES ( emp_id, week_id, sales_thur )

INTO sales_info VALUES ( emp_id, week_id, sales_fri )

SELECT emp_id, week_id, sales_mon, sales_tue, sales_wed, sales_thur, sales_fri

FROM sales_source_date;

运行结果， 1行 变 5行

7. 外部表

外部表是 read-only table. 不能使用索引和DML

外部表指不在数据库中的表，如操作系统上的一个 按一定格式分割的文本文件或者其他类型的表。这个外部表对于Oracle数据库来说，就好像是一张视图。

You are not in fact creating a table, Rather, you are creating metadata in the data dictionary that you can use to access external data.

1）操作系统文件限制

其实外部表简单的说，就是跟操作系统上固定格式的文件或者表格的一个连接。为了Oracle数据库系统能够正确链接外部表，对于外部表的格式就提出 了比较严格的要求。如果不符合这些要求的话，数据库系统就无法正确读取外部表中的数据。如对于分隔符有比较严格的要求。虽然在外部文件或者表格中，可以使 用多种分隔符，如英文状态下的逗号或者分号等等。但是有一个限制，即在同一个操作系统文件中只能够使用一个分割符号，要么逗号或者分号等等。因为在建立外 部表时，必须指定操作系统文件所使用的分隔符号。如果有多种分隔符号的话，数据库系统将无法识别。

另外在外部表格中，不能够带有标题信息。如现在有一张表格，以逗号分隔。而在其第一列数据中有各个列的标题信息。而数据库系统在连接这个表的时 候，会将这些标题信息当作普通的纪录来对待。即会将这些信息也显示在外部表中。为此如果这个标题信息与外部表的字段类型不一致（如字段内容是number 数据类型，而标题信息则是字符型数据，则在查询时就会出错）。如果数据类型恰巧一致的话，这个标题信息Oracle数据库也会当作普通记录来对待。如在建 立外部表的时候，最好确认一下操作系统文件中是否包含标题信息。如果有的话，需要删除。否则的话，可能会出错。

最后需要说明的是，当Oracle数据库系统访问这个操作系统文件的时候，会在这个文件所在的目录自动创建一个日志文件。无论最后是否访问成功，这个日志文件都会如期建立。查看这个日志文件，可以了解数据库访问外部表的频率、是否成功访问等等。

2 ) 建立临时表的限制 

在建立临时表时，也会有不少的限制。如表中字段的名称有一些特殊字符的话，那么这个表列的名称必须使用英文状态的下的双引号连接起来。如采用 “studentno#”。遇到列名字中有特殊符号时，如果不采用双引号括起来，虽然临时表可以正常创建，但是在采用的时候会出现错误，无法正常查询数 据。如数据库系统可能会提醒：“数据库插件错误”等信息。为此最好在创建临时表时不要在列名中使用一些特殊的字符。其实不光光是建立临时表有这种限制，建 立其他标或者试图都有类似的限制。

其次，这个外部表毕竟与内部表不同。在创建外部表的时候，其实在数据库中跟本没有创建表。也就是说，不会为外部表分配任何的存储空间。创建外部 表只是在数据字典中创建了外部表的元数据，以便对应访问外部表中的数据，而不在数据库中存储外部表的数据。简单地说，数据库存储的只是与外部文件的一种对 应关系，如字段与字段的对应关系。而没有存储实际的数据。为此在表的操作与管理上，就会受到很大的限制。如在外部表上，是不能够为表创建索引。因为创建索 引就意味着要存在对应的索引记录。而外部表其实在数据库不会有存储。故在外部中是无法建立索引的。如果硬要建立的话，则系统会提示“操作在外部组织表上不 受支持”的错误提示。同样的道理，在数据库中也不能够更新外部表中的数据，如插入记录、删除记录或者更新信息等等。简而言之，这个外部表对于数据库来说， 是只读的，不可更新。

3) 删除外部表或目录对象

当外部表不用时，需要及时删除外部表或者与之对应的目录对象。不过在删除这些内容时会有一些限制。这些限制主要是管理上的限制，而不是技术上的限 制。也就是说，Oracle数据库系统没有对其进行强制的限制。但是如果数据库管理员不遵守这些限制的话，可能会出现一些问题。如要先删除外部表，然后再 删除目录对象。有时候一个目录对象中可能会包含多个外部表。此时必须要确认所有的外部表都不用了，都已经删除干净了，然后才能够删除目录对象。在创建外部 表时，操作系统会判断一下，与之对应的目录对象是否已经创建。但是在删除对象时，系统不会去判断跟这个目录对象关联的外部表是否已经全部删除。如果目录对 象删除了，但是还有外部表存在。此时查询这个外部表的时候，系统就会提示“对象不存在”的错误信息。所以这个删除目录对象时，数据库系统缺乏一种检查，此 时只有数据库管理员在删除目录对象时，先手工确认一下这个目录对象是否存在其他的外部表。

要了解这个信息，则可以通过查询dba_external_locations。通过查询这张表，系统会反映当前所有的目录对象以及相关的外部 表，还会查询出这些外部表所对应的操作系统文件的名字。先查询这张表格，确定要删除的对象没有其他关联的外部表时，再进行删除。否则的话，需要先确认其他 外部表的可用性。免得因为误删除而导致外部表无法正常使用。

4）对操作系统平台的限制

虽然Oracle数据库是支持跨平台的数据库系统，即同时支持Windows或者Linux等多种操作系统。但是在使用外部表的时候需要注意一个问题，即 在两个操作系统上文本文件的存储方式是不同的。如在Windows操作系统上利用txt文件建立了一个以逗号作为分隔符的文件，其一行一条记录。但是在 Linux操作系统上打开的话，在其就可能使在同一行中显示了。故为了数据库系统能够正确识别操作系统文件，最好这个操作系统文件能够和Oracle数据 库系统部署在同一台服务器上或者同一种操作系统上。否则的话，很可能因为格式的冲突，而导致数据库系统无法正确读取外部文件中的数据。

5）外部表实例

   1:  --创建目录

   2:  create or replace directory dir_bdump as 'D:\oracle\product\10.2.0\admin\fgisdb\bdump';

   3:  --查询目录

   4:  select * from dba_directories;

   5:  --查询外部表的目录

   6:  select * from dba_external_locations;

   7:  --查询外部表

   8:  select * from dba_external_tables;

创建外部表

create table alert_fgisdb ( empno NUMBER, empname varchar2(400) )
  organization external (
  type oracle_loader
  default directory dir_external  --刚刚创建的
  access parameters (
  records delimited by newline
  nobadfile                       -- 指定bad file
  nodiscardfile                   -- 指定discard file
  nologfile                       -- 指定logfile
  fields terminated by '.'        -- 每一个字段以 . 分隔
  empno CHAR.
  empname CHAR.
  )
  location('emp1.txt')            -- 真正用来存放文件的外部表(格式为 10.limei)
  )
  reject limit unlimited

使用外部表，有时间再确认吧，目前用不到。。。