##权限控制
1.创建用户(通过命令行的方式默认是没有connect的权限的)
create user user_test
identified by user_test
####赋予用户系统权限 - grant(授予) …. to
create sessin 创建会话
create table 创建表
create sequence 创建序列
create view 创建视图
create procedure 创建存储过程
给user_test用户赋予连接权限
grant create session
to user_test
给user_test用户赋予创建表的权限
grant create table
to user_test
给user_test用户分配5M表空间
alter user user_test quota 5m
on users
修改自己的数据库密码
alter user user_test
identified by 231
####可以先创建角色，然后给角色赋予权限，将那个角色的权限赋予给新的角色
创建角色
create role my_role
赋予权限
grant create session,create table,create view to my_role
然后创建一个角色
create user user_test2
identified by 123
然后将赋予权限的my_role用户的权限给新创建的用户
grant my_role to user_test2
将user_test用户下的emp1表的select ,update权限分配给user_test2
grant select,update
on user_test.emp1
to user_test2
将系统用户下的employees表的select权限分配给user_test2
grant select
on employees
to user_test2
将departments表的select 权限分配给user_test2 并且user_test2能将这个权限分配给其他用户
grant select
on departments
to user_test2
with grant option
使用to public 就能将权限分配给所有人
grant select
on locations
to public
查询用户关于表的权限
select * from user_tab_privs_recd;
##收回对象权限
收回 user_test2的employees表的select权限
revoke select on
employees
from user_test2
##SET运算符
- union/union all = union并集 union all 就是相加的并集（不会删除重复的部分）
- intersect = 交集
- minus = 差集
排序：order by
注意：使用列的项数需一样，数据类型也需要一样,取别名的话只会按照上面查询的来命名，并且默认会按照第一列的顺序从小到大来排序
使用union取并集（去重）
select employee_id id,department_id dept_id
from employees02
union
select employee_id,department_id
from employees01
使用union all取并集（不去重）
select employee_id,department_id
from employees02
union all
select employee_id,department_id
from employees01
按照倒序来排序
select employee_id id,department_id
from employees02
union all
select employee_id,department_id
from employees01
order by id desc
可以使用相对位置来排序
select employee_id id,department_id
from employees02
union all
select employee_id,department_id
from employees01
order by 2 desc
使用intersect 取交集
select employee_id id,department_id
from employees02
intersect
select employee_id,department_id
from employees01
求两个表的并集
select employee_id id,department_id,to_char(null)
from employees02
union
select to_number(null),department_id,department_name
from departments
对语句进行并集操作使用了 column a_dummy noprint 命令来使不输出行号
select 'i want study' as "my_dream",1 a_dummy
from dual
union
select 'in',2
from dual
union
select 'hometown',3
from dual
order by 2 asc
####set 练习
1.查询部门的部门号，其中不包括job_id = “ST_CLERK”的部门号
-- 1.查询部门的部门号，其中不包括job_id = "ST_CLERK"的部门号
select department_id
from departments
minus
select department_id
from employees
where job_id = 'ST_CLERK'
2.查询10，20,50号部门的job_id,department_id并且department_id按 10,50,20 排序
使用了union 和column a_dummy noprint pl语法来使1,2,3不显示
-- 2.查询10，20,50号部门的job_id,department_id
-- 并且department_id按 10,50,20 排序
select job_id,department_id,1 a_dummy
from employees
where department_id = 10
union
select job_id,department_id,2
from employees
where department_id = 50
union
select job_id,department_id,3
from employees
where department_id = 20
order by 3 asc
3.查询所有员工的last_name,deparment_id和department_name 可以使用to_char(null)来占位
-- 3.查询所有员工的last_name,deparment_id和department_name
/*
select last_name,e.department_id,department_name
from employees e,departments d
where e.department_id = d.department_id
*/
select last_name,department_id,to_char(null)
from employees
union
select to_char(null),department_id,department_name
from departments
##高级子查询
###多列子查询
1.查询141号或174号员工的manager_id和department_id相同的其他员工的employee_id,manager_id，department_id
-- 查询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)
###使用from子句进行子查询
-- 在from子句中使用子查询
-- 返回比本部门平均工资高的员工的last_name,department_id,salary,以及平均工资
select last_name,e1.department_id,salary,avg_sal
from employees e1,(select department_id,avg(salary) avg_sal from employees group by department_id) e2
where e1.department_id = e2.department_id
and e1.salary>avg_sal
###单列子查询
1.显示员工的employee_id,last_name,location其中，若员工的department_id 与 location_id为1800的department_id相同，则location为’Canada’余为’USA’
-- 显示员工的employee_id,last_name,location
-- 其中，若员工的department_id 与 location_id为1800的department_id相同，则location为'Canada'
-- 其余为'USA'
select employee_id,last_name,
(
case department_id
when (select department_id from departments where location_id = 1800)then 'Canada'
else 'USA'
end
)location
from employees
2.查询员工的employee_id,last_name,要求按照员工的department_name排序
-- 查询员工的employee_id,last_name,要求按照员工的department_name排序
select employee_id,last_name
from employees e1
order by ( select department_name
from departments d
where e1.department_id = d.department_id
)
###相关子查询
相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询
1.若employees表中的employee_id与job_history表中的employee_id相同的数目不小于2输出这些相同id的员工的employee_id,last_name以及job_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 e1
where 2 <=(
select count(*)
from job_history
where employee_id = e1.employee_id
)
##exists操作符
exists 操作符检查在子查询中是否存在满足条件的行相当于if
如果在子查询中存在满足条件的行：
- 不继续查询 返回false
- 返回true
如果在子查询中不存在满足条件的行：
- 条件返回fasle
- 继续在子查询中查找
1.查询公司管理者的employee_id,last_name,job_id,department_id信息
-- 查询公司管理者的employee_id,last_name,job_id,department_id信息
/*
select employee_id,last_name,job_id,department_id
from employees e1
where e1.employee_id in (
select manager_id
from employees e2
where 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
*/
--这是使用了exists来查询
select employee_id,last_name,job_id,department_id
from employees e1
where exists (
select 'A'
from employees e2
where e1.employee_id = e2.manager_id
)
2.查询departments表中，不存在employees表中部门的department_id和department_name
-- 查询departments表中，不存在employees表中部门的department_id和department_name
select department_id,department_name
from departments d
where not exists (
select 'x'
from employees e
where d.department_id = e.department_id
)
####相关更新
使用相关子查询来更新表数据
/*
--1) 复制employees 表中数据
create table emp011
as
select * from employees
*/
/*
--2) 添加department_name 字段
alter table emp011
add (department_name varchar2(20))
*/
--3) 更新department_name
update emp011 e
set department_name = (
select department_name
from departments d
where e.department_id = d.department_id
)
####相关删除
删除emp022中和emp033中department_id相同部门的员工
delete from emp022
where department_id in (
select department_id
from emp033
where department_id = emp022.department_id
)
####with子句
1.查询公司中工资比Able高的员工的信息
--查询公司中工资比Able高的员工的信息
with Abel_sal as(
select salary
from employees
where last_name = 'Abel'
)
select employee_id,salary
from employees
where salary > (
select salary
from Abel_sal
)
2.查询公司中各部门的总工资大于公司中各部门的平均总工资的部门信息
--查询公司中各部门的总工资大于公司中各部门的平均总工资的部门信息
with dept_sum_sal as(
select sum(salary) sum_sal1,department_name
from employees e,departments d
where d.department_id = e.department_id
group by department_name
),
dept_avgsal as(
select sum(sum_sal1)/count(*) avg_sum_sal2
from dept_sum_sal
)
select * from
dept_sum_sal
where sum_sal1 > (
select avg_sum_sal2
from dept_avgsal
)
order by department_name