EXISITS操作符,相关子查询,with子句
来源:互联网 发布:邵阳学院教务网络系统 编辑:程序博客网 时间:2024/06/01 08:18
--exisits操作符
--查询公司管理者的employee_id,job_id ,last_name,department_id信息
--方法1
select employee_id,job_id,last_name,department_id from employees
where employee_id in(select distinct manager_id from employees where manager_id is not null);
--方法2
select employee_id,job_id,last_name,department_id from employees e1
where employee_id in(select distinct manager_id from employees e2 e1.employee_id=e2.manager_id);
--方法3:自连接
select distinct e1.employee_id,e1.job_id,e1.last_name,e1.department_id from employees e1,employees e2
where e1.employee_id= e2.manager_id;
--方法4 exists操作符 只要()中的where条件成立,不需要返回值,就会输出结果
select employee_id,job_id,last_name,department_id from employees e1
where EXISTS (select 'a' from employees e2 where e1.employee_id=e2.manager_id);
--not exisits 查询departments表当中,不存在于employees表中的部门的department_id和department_name
方法1:
select department_id,department_name from departments d
where not EXISTS (select 'a' from employees e where d.department_id= e.department_id);
方法2:
select department_id,department_name from departments where department_id in(
select department_id from departments
minus
select department_id from employees);
--相关更新
--给表employees1添加了deparment_name项
alter table employees1
add department_name varchar2(30);
--给department_name项从departments表中获得相应得数据 并添加
update employees1 e
set department_name=(select department_name from departments d where d.department_id= e.department_id);
select department_name,employee_id from employees1;
--删除表emp7中,其与emp8表皆有的数据
delete FROM emp7 where department_id in(select department_id from emp8 where emp7.department_id=emp8.department_id);
- EXISITS操作符,相关子查询,with子句
- WITH引出的子查询扇出子句
- Oracle 使用IN子句的非相关子查询
- 数据库查询select with子句
- Start with ... Connect By 子句递归查询
- Start with ... Connect By 子句递归查询
- Oracle Having子句中使用子查询
- having子句中包含子查询(重点)
- into子句中使用子查询
- 关于子查询中的order by子句
- 在where子句中使用子查询
- Oracle用Start with...Connect By子句递归查询(还查出子句的子句)
- sql with 子查询
- WITH AS 子查询
- with as (子查询)
- 相关子查询 非相关子查询
- WITH子句
- WITH 子句
- 浅谈AJAX
- [Bash Shell] Shell学习笔记
- 进程和线程的区别
- Jquery和Dom元素的比较案例
- 代码整洁之道读书笔记(九)
- EXISITS操作符,相关子查询,with子句
- Single Number III_260
- Linux常用命令-文件处理命令
- Netty系列之Netty高性能之道
- Grunt 新手一日入门
- 安卓开发 - 微信支付
- Maven 2 pom.xml
- Jquery过滤器的基础入门案例
- 《第一行代码》FragmentBestPractice项目(简单新闻应用)中的不可忽视问题