（5）Oracle——————控制用户权限，SET 操作符和高级子查询

Love The Way You Lie 2023-07-16 03:51 15阅读 0赞

1，控制用户权限

*  1.1， 系统权限
 *  1.2， 创建用户
 *  1.3， 角色
 *  1.4， 修改密码
 *  1.5， 对象权限
 *  1.6， WITH GRANT OPTION和PUBLIC关键字
 *  1.7，收回对象权限

2，SET 操作符

*  2.1， UNION 操作符
 *  2.2， UNION ALL 操作符
 *  2.3， INTERSECT 操作符
 *  2.4， MINUS 操作符
 *  2.5， 使用 SET 操作符注意事项

3，高级子查询

*  3.1， 子查询
 *  3.2， 多列子查询
 *  3.3， 在 FROM 子句中使用子查询
 *  3.4， 单列子查询表达式
 *  3.5， 使用 SET 操作符注意事项
 *  3.6， 相关子查询
 *  3.7， 相关更新，相关删除
 *  3.8， WITH 子句

#### 控制用户权限 ####

*  数据库安全性：  
    系统安全性  
    数据安全性
 *  系统权限：对于数据库的权限
 *  对象权限： 操作数据库对象的权限

**1，系统权限**

*  超过一百多种有效的权限，数据库管理员具有高级权限以完成管理任务，例如：  
    创建新用户  
    删除用户  
    删除表  
    备份表

**2，创建用户**

*  DBA 使用 CREATE USER 语句创建用户：

CREATE USER user              			   
    IDENTIFIED BY password;

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

GRANT  create session, create table, 
           create sequence, create view
    TO     scott;

*  用户拥有create table权限之外，还需要分配相应的表空间才可开辟存储空间用于创建的表  
    ALTER USER Tommey QUOTA UNLIMITED ON users

**3，角色**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70]

*  创建角色

create role manager;

*  为角色赋予权限

grant create table, create view to manager;

*  将角色赋予用户

GRANT manager TO TOMMEY, KOCHHAR;

**4，修改密码**

*  DBA 可以创建用户和修改密码
 *  用户本人可以使用 ALTER USER 语句修改密码

ALTER USER scott             			  
    IDENTIFIED BY root;

**5，对象权限**

*  不同的对象具有不同的对象权限
 *  对象的拥有者拥有所有权限
 *  对象的拥有者可以向外分配权限
 *  分配表 EMP 的查询权限

GRANT  select ON  emp TO  sue, rich;

*  分配表中各个列的更新权限

GRANT  update  ON  scott.departments TO  Tommey

**6，WITH GRANT OPTION和PUBLIC关键字**

*  WITH GRANT OPTION 使用户同样具有分配权限的权利

GRANT  select, insert
    ON     departments
    TO     scott
    WITH   GRANT OPTION;

*  向数据库中所有用户分配权限

GRANT  select
    ON	  zzz.emp
    TO	  PUBLIC;

*  查询权限分配情况  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 1]

**7，收回对象权限**

*  使用 REVOKE 语句收回权限
 *  使用 WITH GRANT OPTION 子句所分配的权限同样被收回

REVOKE  select, insert
    ON      departments
    FROM    scott;

#### SET运算符 ####

**1，UNION 操作符**

*  UNION 操作符返回两个查询的结果集的并集  
    ![\-][-]

SELECT employee_id, job_id
    FROM   employees
    UNION
    SELECT employee_id, job_id
    FROM   job_history;

**2，UNION ALL 操作符**

*  UNION ALL 操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 2]

**3，INTERSECT 操作符**

*  INTERSECT 操作符返回两个结果集的交集  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 3]

**4，MINUS 操作符**

*  MINUS操作符：返回两个结果集的差集  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 4]

**5，使用 SET 操作符注意事项**

*  在SELECT 列表中的列名和表达式在数量和数据类型上要相对应
 *  括号可以改变执行的顺序  
    ORDER BY 子句:
 *  使用 SET 操作符注意事项  
    只能在语句的最后出现  
    可以使用第一个查询中的列名, 别名或相对位置
 *  除 UNION ALL之外，系统会自动将重复的记录删除
 *  系统将第一个查询的列名显示在输出中
 *  除 UNION ALL之外，系统自动按照第一个查询中的第一个列的升序排列

SELECT 'sing' AS "My dream", 3 a_dummy
    FROM dual
    UNION
    SELECT 'I`d like to teach', 4
    FROM dual
    UNION 
    SELECT 'the world to', 2
    FROM dual
    ORDER BY 2;

#### 高级子查询 ####

**1，子查询**

*  子查询是嵌套在 SQL 语句中的另一个SELECT 语句  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 5]
 *  子查询 (内查询) 在主查询执行之前执行
 *  主查询(外查询)使用子查询的结果

**2，多列子查询**

*  主查询与子查询返回的多个列进行比较  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 6]
 *  多列子查询中的比较分为两种:  
    成对比较  
    不成对比较

**3，在 FROM 子句中使用子查询**

\---- 返回比本部门平均工资高的员工的last\_name, department\_id, salary及平均工资

方法一：

select a.ename,
           a.deptno,
           a.sal,
           (select avg(c.sal)
              from emp c
             where c.deptno = a.deptno
             group by c.deptno) avg_sal
      from emp a
     where a.sal > (select avg(b.sal)
                      from emp b
                     where a.deptno = b.deptno
                     group by b.deptno)

方法二：

select a.ename, a.deptno, a.sal, c.avg_sal
      from emp a,
           (select b.deptno, avg(b.sal) avg_sal from emp b group by b.deptno) c
     where a.sal > c.avg_sal
       and a.deptno = c.deptno

**4，单列子查询表达式**

*  单列子查询表达式是在一行中只返回一列的子查询
 *  Oracle8i 只在下列情况下可以使用, 例如:  
    SELECT 语句 (FROM 和 WHERE 子句)  
    INSERT 语句中的VALUES列表中
 *  Oracle9i中单列子查询表达式可在下列情况下使用:  
    DECODE 和 CASE  
    SELECT 中除 GROUP BY 子句以外的所有子句中
 *  在 CASE 表达式中使用单列子查询

\----- 显式员工的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;

*  在 ORDER BY 子句中使用单列子查询

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

**5，相关子查询**

*  相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 7]  
    \---- 若employees表中employee\_id与job\_history表中employee\_id相同的数目不小于2，输出这些相同id的员工的employee\_id,last\_name和其job\_id

SELECT e.employee_id, last_name, e.job_id
      FROM employees e
     WHERE 2 <=
           (SELECT COUNT(*) FROM job_history WHERE employee_id = e.employee_id);

**6，EXISTS 操作符**

*  EXISTS 操作符检查在子查询中是否存在满足条件的行
 *  如果在子查询中存在满足条件的行:  
    不在子查询中继续查找  
    条件返回 TRUE
 *  如果在子查询中不存在满足条件的行:  
    条件返回 FALSE  
    继续在子查询中查找

\---- 查询公司管理者的employee\_id,last\_name,job\_id,department\_id信息

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

\---- 查询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);

**7，相关更新，相关删除**

*  使用相关子查询依据一个表中的数据更新另一个表的数据

UPDATE employees e
       SET department_name =
           (SELECT department_name
              FROM departments d
             WHERE e.department_id = d.department_id);

*  使用相关子查询依据一个表中的数据删除另一个表的数据
 *  删除表employees中，其与emp\_history表皆有的数据

DELETE FROM employees E
     WHERE employee_id = (SELECT employee_id
                            FROM emp_history
                           WHERE employee_id = E.employee_id);

**8，WITH 子句**

*  使用 WITH 子句, 可以避免在 SELECT 语句中重复书写相同的语句块
 *  WITH 子句将该子句中的语句块执行一次并存储到用户的临时表空间中
 *  使用 WITH 子句可以提高查询效率

\---- 查询公司中各部门的总工资大于公司中各部门的平均总工资的部门信息

WITH dept_costs AS
     (SELECT d.department_name, SUM(e.salary) AS dept_total
        FROM employees e, departments d
       WHERE e.department_id = d.department_id
       GROUP BY d.department_name),
    avg_cost AS
     (SELECT SUM(dept_total) / COUNT(*) AS dept_avg FROM dept_costs)
    SELECT *
      FROM dept_costs
     WHERE dept_total > (SELECT dept_avg FROM avg_cost)
     ORDER BY department_name;

--------------------

[下一章，（6）Oracle——————PL/SQL的基本语法，记录类型和流程控制语句][6_Oracle_PL_SQL]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70]: /images/20230528/c9ca0b5c90dc47fa8febe3f63ad477a7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 1]: /images/20230528/b08d8cffcce24059ad44c5d57c84b6e0.png
[-]: /images/20230528/8614fdc05c5d4456aa2848869193db1d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 2]: /images/20230528/01eceaee6a0d41debef2f03fda5cdf4a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 3]: /images/20230528/d5294694c1f64ec2a875412c0cd24a43.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 4]: /images/20230528/5c17371fa6f44669bc805cabe2d47d15.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 5]: /images/20230528/7a68626ee2fd4bef9e903e8d612f8041.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 6]: /images/20230528/f72013890b064982bd1584dd9aa8a167.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 7]: /images/20230528/ac4aabf2c4284b40857f27725b252959.png
[6_Oracle_PL_SQL]: https://blog.csdn.net/qq_41530004/article/details/104978810