大师网-带你快速走向大师之路 解决你在学习过程中的疑惑,带你快速进入大师之门。节省时间,提升效率

Java工程师之Oracle技术-SQL入门(5)

控制用户权限

Oracle的用户权限概念

  • 每个登陆Oracle数据库都是以特定的数据库用户登录的,用户拥有哪些权限,都可以利用Oracle的权限控制机制进行控制

创建数据库用户的语法

角色

  • 如果要给多个用户赋予相同的权限,可以通过角色来简化管理
    SQL

对象权限

  • 区别于系统权限,细化到某个具体的数据库对象上的访问权限控制,各种数据库对象适合赋予的权限如下表:

    <table>
    <tr><th>对象权限</th><th>Table</th><th>View</th><th>Sequence</th><th>Procedure</th></tr>
    <tr><td>ALTER</td> <td>√</td> <td></td> <td>√</td> <td></td></tr>
    <tr><td>DELETE</td> <td>√</td> <td>√</td> <td></td> <td></td></tr>
    <tr><td>EXECUTE</td> <td></td> <td></td> <td></td> <td>√</td></tr>
    <tr><td>INDEX</td> <td>√</td> <td></td> <td></td> <td></td></tr>
    <tr><td>INSERT</td> <td>√</td> <td>√</td> <td></td> <td></td></tr>
    <tr><td>REFERENCES</td><td>√</td> <td>√</td> <td></td> <td></td></tr>
    <tr><td>SELECT</td> <td>√</td> <td>√</td> <td>√</td> <td></td></tr>
    </table>

  • 语法
    GRANT object_priv [(columns)] 
     ON object 
     TO {user|role|PUBLIC} 
     [WITH GRANT|ADMIN OPTION];
  • 可以通过数据字典查询系统中的赋予权限情况
  • ROLE_SYS_PRIVS 角色对应的系统权限
  • ROLE_TAB_PRIVS 角色对应的表权限
  • USER_ROLE_PRIVS 用户的角色分配表
  • USER_TAB_PRIVS_MADE 用户对象上赋权者与被赋权者的历史复权情况
  • USER_TAB_PRIVS_RECD 用户对象上拥有者与被赋权者的历史复权情况
  • USER_COL_PRIVS_MADE 用户对象列上赋权者与被赋权者的历史复权情况
  • USER_COL_PRIVS_RECD 用户对象列上拥有者与被赋权者的历史复权情况
  • USER_SYS_PRIVS 用户的系统权限
  • 收回权限
    REVOKE {privilege [, privilege...]|ALL} 
    ON object 
    FROM   {user[, user...]|role|PUBLIC}

数据库连接

  • Database Link 如果需要在数据库中访问另一个数据库中的表,最简单的方法是在当前数据库中创建一个数据库连接指向另一个数据库,然后通过@数据库连接的后缀就可以访问另一个数据库中的表了。
    CREATE PUBLIC DATABASE LINK - USING -

使用集合操作

  • UNION/UNION ALL 并集(返回两个结果集中的所有部分,重复的部分默认清除,使用ALL不清除,Union不建议使用,Union除了去重之外,还会进行排序,效率低)
  • INTERSECT 交集(返回连个结果集重复的部分)
  • MINUS 差集(返回前者结果集中在后者结果中没有出现的部分)
    SELECT ....
    UNION|UNION ALL|INTERSECT|MINUS
    SELECT ....

    Group By字句的增强

使用Rollup产生常规分组汇总行以及分组小计

  • Rollup 后面跟了n个字段,就将进行n+1次分组,从右到左每次减少一个字段进行分组,然后进行union

使用Cube产生常规分组汇总行以及分组小计 + 多维度的交叉表数据源

  • Cube 后面跟了n个字段,就将进行2的N次方的分组运算(每个字段都有参与和不参与分组两种方式,N个字段共有2^N中分组方式),然后进行union

GROUPING函数

  • Rollup和Cube有点抽象,他分别相当于n+1和2^n常规Group By预算,那么在Rollup和Cube的结果集中如何很明确的看出哪些行是针对那些列或者列的组合进行分组运算的?使用Grouping函数,没有被Grouping到返回1,否则返回0。

GROUPING SETS(set1, set2)

  • 相当于两次结果集的UNION

子查询进阶

非相关子查询

  • 将查询结果作为一张表与另一张表做连接

相关子查询

  • 子查询中参考外部主查询中的表

Exists/Not Exists操作

  • 判断相关子查询是否存在作为查询条件

Exists和In的效率问题

  • In可以看作是两次查询结果集做hash连接,而Exists可以看作是Loop循环查询,当子查询结果集比较小时,Exists效率比In高,而当查询结果集比较大时,In效率比Exists高。Not In和Not Exists相比,Not Exists效率永远比Not In高,因为Not Exists时通过索引查询,而Not In不会走索引。(网上查的,对不对需要验证)

Update/Delete语句中使用相关子查询

UPDATE table SET column = (SELECT ...);
DELETE from table WHERE column = (SELECT ...);

With字句

  • 使用With字句可以简化SQL,还能适当提高性能
    with name1 as (SELECT ...), name2 as (SELECT ...)
    SELECT ... FROM name1 .... name2