oracle数据库学习②

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

创建用户

create user userName identified by password;

赋予用户权限

grant privilegeName to user;

只有具有create session权限，用户才能登录数据库

grant create session to userName;

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

alter user userName quota unlimited | 5m | 60k on tableSpaceName;

-- 举例
alter user designer01 quota 5m on users; -- 为用户designer01在表空间users中分配了5M的空间

创建角色

create role roleName;

为角色赋予权限

grant create session,create table ,create view to roleName;

将角色赋予用户

grant roleName to userName;

使用角色为用户赋权，可以简化赋权的操作，一次性完成相应权限的授予，而不用向之前那样一个个权限授予权限

修改用户密码(用户本身就可以操作)

alter user userName identified by password;

删除用户

drop user userName cascade;

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

grant object_priv [(columns)]
on object 
to [user | role | public]  -- to public 就是将权限给所有用户
[with grant option]  -- 使用户同样具有分配权限的权力

收回对象权限
①使用revoke语句收回权限
②使用with grant option 子句所分配的权限同样被收回

revoke [privilege...  | all]
on object
from [user | role | public]
[cascade constraints]

查询权限分配情况

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

DCL控制数据库权限

语句	功能
create user	创建用户(通常由DBA完成)
grant	分配权限
create role	创建角色(通常由DBA完成)
alter user	修改用户密码
revoke	收回权限

set操作符
①union / union all
②intersect
③minus

使用set操作符注意事项
①在select列表中的列名和表达式在数量和数据类型上要相对应
②括号可以改变执行的顺序
③order by 子句：只能在语句的最后出现，可以使用第一个查询中的列名，别名或相对位置 1，2，3…
④除union all之外，系统会自动将重复的记录删除
⑤系统将第一个查询的列名显示在输出中，下面的是不生效的
⑥除union all之外，系统会自动按照第一个查询中的第一个列的升序排列

对结果集进行排序，可以在每一个union语句后写上数字，然后最后order by 那个数值的字段，这样结果集多了一个数值的字段，可以使用

column 不想显示的字段名称 noprint;  

不打印那个字段即可

高级子查询
多列子查询

--查询与141号与144号员工的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,144)
                                    )
and employee_id not in(141,144);            

在order by 子句总使用单列子查询

-- 查询员工的employee_id,last_name,要求按照员工的department_name排序
select employee_id,last_name
from employees e
order by (
          select department_name
          from employees b
          where e.department_id=b.department_id
			);

单列子查询表达式范围
①单列子查询表达式是一行中只返回一列的子查询
②oracle8i只在下列情况可以使用，例如：
1 select语句(from 和 where 子句)
2 insert语句中的values列表中
③oracle9i中单列子查询表达式可在下列情况使用：
1 decode和case when
2 select中除了group by子句以外的所有子句中

相关子查询
按照一行接一行的顺序执行，著查询的每一行都执行一次子查询
①get 从主查询中获取候选列
②execute 子查询使用主查询的数据
③use 如果满足内查询的条件则返回该行

select column1,column2...
from table1 outer
where column1 operator 
                      (
                       select column1
                       from table2
                       where expr1=oter.expr2
						);
-- 举例
-- 查询员工中工资大于本部门平均工资的员工的last_name,salary,和其department_id
select last_name,salary,department_id
from employees e
where salary > (
				select avg(salary)
				from employees a
				where e.department_id=a.department_id
				)

--若employees表中employee_id与job_history表中与employee_id相同的数目不小于2，输出这些相同id的员工的employee_id,last_name,job_id
select employee_id,last_name,job_id
from employees e
where 2 <= (
			select count(*)
			from job_history a 
			where a.employee_id=e.employee_id
			);

EXISTS操作符
①exists操作符检查在子查询中是否存在满足条件的行
②如果在子查询中存在满足条件的行：
1 不在子查询中继续查找
2 条件返回true
③如果在子查询中不存在满足条件的行：
1 条件返回false
2 继续在子查询中查找

-- 查询公司管理者的employee_id,last_name,job_id,department_id
select employee_id,last_name,job_id,department_id
from employees e1
where exists (
				select 1
				from employees 
				where e1.employee_id=manager_id
             );

-- 原始的写法
select distinct e1.employee_id,e1.last_name,e1.job_id,e1.department_id
from employees e1
join employees e2 on e1.employee_id=e2.manager_id

select  distinct e1.employee_id,e1.last_name,e1.job_id,e1.department_id
from employees e1,employees e2
where  e1.employee_id=e2.manager_id

NOT EXISTS操作符

-- 查询departments表中，不存在于employees表中的部门的department_id和department_name
select department_id,department_name
from departments d
where not exists (
					select 1
					from employees
					where d.department_id=department_id
				)

相关更新

update tableName alias1
set columnName =  (
                  select expression 
                  from tableName2  alias2
                  where alias1.columnName = alias2.columnName
                  );
-- 举例
-- 首先创建一个表
create table emp01
as
select * 
from employees; -- 这个表没有department_name字段
-- 在上面的表中增加department_name字段，并且需要将department_name赋上相应的值
alter table emp01 add (department_name varchar2(20));
-- 通过相关子查询更新department_name字段的值
update emp01 
set department_name = (
                       select department_name
                       from departments
                       where emp01.department_id=department_id
                       )

相关删除

delete from tableName alias1
where columnName in (
                    select columnName
                    from tableName2 alias2
                    where alias1.columnName=alias2.columnName
                    )

-- 删除两张表中employee_id都存在的数据
delete from emp01 e1
where exists (
				select 1 
				from emp02 e2
				where e1.employee_id=e2.employee_id
				);
				
delete from emp01 e1
where employee_id in (
				select employee_id
				from emp02 e2
				where e1.employee_id=e2.employee_id
				);

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

练习：
查询员工的last_name,department_id,salary,其中员工的salary,department_id与有奖金的任何一个员工的salary,department_id相同即可

select last_name,department_id,salary
from employees
where (salary,department_id) in (
								select salary,department_id
								from employees
								where commission_pct is not null
								);