6.权限&&高级子查询

权限

创建用户

create user [用户名]
identified by [密码]

创建用户之后，DBA会赋予用户一些权限

以应用程序开发者为例, 一般具有下列系统权限:
- CREATE SESSION（创建会话）
- CREATE TABLE（创建表）
- CREATE SEQUENCE（创建序列）
- CREATE VIEW（创建视图）
- CREATE PROCEDURE（创建过程）

给权限

首先进入system用户登录
grant [权限] to [用户]

创建表空间

用户拥有create table权限之外，还需要分配相应的表空间才可开辟存储空间用于创建的表

alter user [用户] quota unlimited on users

创建角色并附权限

• 创建角色
  create role [角色名]
• 为角色赋权限
  grant create table ,create view to [角色名]
• 将角色赋给用户
  grant [角色名] to [用户]

修改密码

• DBA 可以创建用户和修改密码
• 用户本人可以使用alter user修改密码

alter user [用户名]`
identified by [密码]

对象全权限

• 不同的对象具有不同的对象权限
• 对象的拥有者拥有所有权限
• 对象的拥有者可以向外分配权限

例如，想把scott select employees的权限赋给新创建的bart用户。

grant select on scott.employees to bart;

这样bart用户就可以使用select语句查看scott用户的employees的表内容了

with grant option和public关键字

• with grant option：使用户同样具有分配权限的权利
  例如：将scott用户的emlpoyees表的select、insert 权限赋给bart用户，并允许bart用户把这些权限也给其他用户

grant select,insert 
on scott.employees 
to bart 
with grant option;

• public:使得用户的表成为公开的，每个用户都可以访问
  例如：使得scott用户的employees表的权限设置为public，那么任何用户都可以对该表进行操作

grant select 
on scott.employees 
to public 

查询权限分配情况

数据字典视图	描述
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	用户拥有的系统权限

收回权限

将scott用户的表employees的select和insert权限从bart用户收回

revoke select,insert 
on scott.employees 
from bart;

高级子查询

成对比较

1. 问题：查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id, manager_id, department_id

SELECT  employee_id, manager_id, department_id
FROM    employees
WHERE  (manager_id, department_id) IN
                      (SELECT manager_id, department_id
                       FROM   employees
                       WHERE  employee_id IN (141,174))
AND employee_id NOT IN (141,174);

不成对比较

SELECT  employee_id, manager_id, department_id
FROM    employees
WHERE   manager_id IN 
                    (SELECT  manager_id
                   FROM    employees
                   WHERE   employee_id IN (174,141))
AND     department_id IN 
                    (SELECT  department_id
                   FROM    employees
                   WHERE   employee_id IN (174,141))
AND employee_id NOT IN(174,141);

和上面那个例子效果一样

在from子句中查询

问题：返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资

方法1：

select last_name,department_id,salary,
(select avr(salary) 
        from employees e2 
        where e1.department_id=e2.departments_id 
        group by department_id) avg_salary 
from employees e1
where salary>(select avg(salary) 
                from employees e3 
                where e1.department_id=e3.department_id 
                group by department_id )

方法2

select last_name,department_id,salary,b.avg_salary 
from employee e1 (select department_id,avg(salary) from employees group by department_id) b 
where a.department_id = b.department_id
and a.salary > b.salavg;

单列子查询

问题1：

显式员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800的department_id相同，则location为’Canada’,其余则为’USA’。

SELECT employee_id, last_name,
       (CASE
            WHEN department_id =
            (SELECT department_id FROM departments
            WHERE location_id = 1800)
            THEN 'Canada' ELSE 'USA' END) location
FROM   employees;

问题2

问题：查询员工的employee_id,last_name,要求按照员工的department_name排序

SELECT   employee_id, last_name
FROM     employees e
ORDER BY
        (SELECT department_name
        FROM departments d
        WHERE e.department_id = d.department_id);

相关子查询

问题：查询员工中工资大于本部门平均工资的员工的last_name,
salary和其department_id

SELECT last_name, salary, department_id
FROM   employees outer
WHERE  salary >
            (SELECT AVG(salary)
             FROM   employees
             WHERE  department_id =  
             outer.department_id);

EXISTS 操作符应用举例

问题：查询公司管理者的employee_id,last_name,job_id,department_id信息

其中的’A’是一个任意值，因为我要查询的就是manager，所以不关心exists之后返回的是什么，只要他返回的事manager的就行了

SELECT employee_id, last_name, job_id, department_id
FROM   employees outer
WHERE  EXISTS ( SELECT 'X'
                 FROM   employees
                 WHERE  manager_id = 
                        outer.employee_id);

NOT EXISTS 操作符应用举例

问题：查询departments表中，不存在于employees表中的部门的department_id和department_name

SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT 'X'
                  FROM   employees
                  WHERE  department_id 
                         = d.department_id);