【oracle学习】1.子查询
发布时间:2020-12-12 16:41:36 所属栏目:百科 来源:网络整理
导读:子查询所要解决的问题:问题不能一步求解 实际上就是sql语句的嵌套,就是在一个select语句中嵌套另外一个select语句 首先我们使用控制台登录连接我们的oracle: 我们先创建员工信息表EMP: create table EMP( EMPNO NUMBER,ENAME VARCHAR2(10),JOB VARCHAR2(9),
子查询所要解决的问题:问题不能一步求解
实际上就是sql语句的嵌套,就是在一个select语句中嵌套另外一个select语句 首先我们使用控制台登录连接我们的oracle: 我们先创建员工信息表EMP: create table EMP( EMPNO NUMBER,ENAME VARCHAR2(10),JOB VARCHAR2(9),MGR NUMBER,HIREDATE DATE,SAL BINARY_DOUBLE,COMM BINARY_DOUBLE,DEPTNO NUMBER ); 其中job是职位,mgr是该员工的上司的id,sal是工资,comm是提成,deptno是所属部门。 然后加入一些数据: insert into EMP ( EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO ) values( 1110,'张三','主管',1110,'12-3月 -14',5200,20 ); 以后数据的添加如上,这里不再赘述。 添加完成后结果 SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --------------- -------------------- ------------------ --------------- -------------- ----- ----- --------------- 1110 张三 主管 1110 12-3月 -14 5200 0 20 1111 李四 销售 1116 03-11月-15 3400 500 30 1112 王五 销售 1116 25-4月 -12 4400 800 30 1113 赵二 后勤 1110 30-5月 -11 3450 0 40 1114 李磊磊 会计 1110 22-12月-15 2500 0 50 1115 张少丽 销售 1110 11-3月 -16 2400 1400 30 1116 林建国 主管 1116 22-1月 -16 5700 0 20 1117 马富邦 后勤 1116 22-7月 -13 2800 0 40 1118 沈倩 会计 1116 06-5月 -10 2100 0 50 然后创建一张部门表 create table dept( DEPTNO NUMBER,DNAME VARCHAR2(50) );添加数据之后结果 SQL> select * from dept t; DEPTNO DNAME -------- -------- 20 管理部门 30 销售部门 40 后勤部门 50 金融部门 我们来查询工资比“李磊磊”高的员工信息 (为了防止科学记数法的出现,我们先对工资和提成进行排版: col sal for 9999 col comm for 9999) (1)李磊磊的工资 select sal from emp where ename='李磊磊'; SAL ---------- 2500 (2)查询比3000高的员工 select * from emp where sal >2500; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --------------- -------------------- ------------------ --------------- -------------- ----- ----- --------------- 1110 张三 主管 1110 12-3月 -14 5200 0 20 1111 李四 销售 1116 03-11月-15 3400 500 30 1112 王五 销售 1116 25-4月 -12 4400 800 30 1113 赵二 后勤 1110 30-5月 -11 3450 0 40 1116 林建国 主管 1116 22-1月 -16 5700 0 20 1117 马富邦 后勤 1116 22-7月 -13 2800 0 40 使用子查询 select * from emp where sal>(select sal from emp where ename='李磊磊'); 结果如上 子查询要注意的问题: 1. 将子查询放入括号中 2. 采用合理的书写风格 3. 可以在主查询的where select from having后面,放置子查询 select deptno,min(sal) from emp group by deptno having min(sal) > (select min(sal) from emp where deptno=30); DEPTNO MIN(SAL) --------------- -------- 20 5200 40 2800 拆分一下,其中 select deptno,min(sal) from emp group by deptno DEPTNO MIN(SAL) --------------- -------- 30 2400 20 5200 40 2800 50 2100 select min(sal) from emp where deptno=30; MIN(SAL) -------- 2400 所以找到的是min(sal)大于2400的数据 4. 不可以在group by后面放置子查询 5. 强调from后面放置子查询 select * from (select ename,sal from emp); ENAME SAL -------------------- ----- 张三 5200 李四 3400 王五 4400 赵二 3450 李磊磊 2500 张少丽 2400 林建国 5700 马富邦 2800 沈倩 2100 6. 主查询和子查询可以不是同一张表,只要子查询返回的结果,主查询可以使用,即可 我们查询部门名称为“销售”的员工信息 select * from emp where deptno=(select deptno from dept where dname='销售部门'); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --------------- -------------------- ------------------ --------------- -------------- ----- ----- --------------- 1111 李四 销售 1116 03-11月-15 3400 500 30 1112 王五 销售 1116 25-4月 -12 4400 800 30 1115 张少丽 销售 1110 11-3月 -16 2400 1400 30 效果如同下面的多表查询: select e.* from emp e,dept d where e.deptno=d.deptno and d.dname='销售部门'; 这里注意: SQL优化: 如果子查询和多表查询都可以,理论上尽量使用多表查询 7. 一般不在子查询中使用order by;但在Top-N分析问题中,必须使用order by 8. 一般先执行子查询,再执行主查询;但相关子查询除外 9. 单行子查询只能使用单行操作符;多行子查询只能使用多行操作符 多行操作符 --in :在集合中 --查询部门名称为“销售部门”和“金融部门”的员工信息 select * from emp where deptno in (select deptno from dept where dname='销售部门' or dname='金融部门') EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --------------- -------------------- ------------------ --------------- -------------- ----- ----- --------------- 1115 张少丽 销售 1110 11-3月 -16 2400 1400 30 1112 王五 销售 1116 25-4月 -12 4400 800 30 1111 李四 销售 1116 03-11月-15 3400 500 30 1118 沈倩 会计 1116 06-5月 -10 2100 0 50 1114 李磊磊 会计 1110 22-12月-15 2500 0 50 效果如同下面的多表查询: select e.* from emp e,dept d where e.deptno=d.deptno and (d.name='销售部门' or d.name='金融部门'); 像下面这种查询就是错误的,非法使用子查询 原因是“多行查询使用单行比较符” 10. 注意子查询中null 查询不是老板的员工信息(老板的上司mgr的id是他自己的id) select * from emp where empno not in (select mgr from emp where empno!=mgr); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --------------- -------------------- ------------------ --------------- -------------- ----- ----- --------------- 1111 李四 销售 1116 03-11月-15 3400 500 30 1112 王五 销售 1116 25-4月 -12 4400 800 30 1113 赵二 后勤 1110 30-5月 -11 3450 0 40 1114 李磊磊 会计 1110 22-12月-15 2500 0 50 1115 张少丽 销售 1110 11-3月 -16 2400 1400 30 1117 马富邦 后勤 1116 22-7月 -13 2800 0 40 1118 沈倩 会计 1116 06-5月 -10 2100 0 50 查询是老板的员工信息 select * from emp where empno in (select mgr from emp where empno=mgr); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --------------- -------------------- ------------------ --------------- -------------- ----- ----- --------------- 1110 张三 主管 1110 12-3月 -14 5200 0 20 1116 林建国 主管 1116 22-1月 -16 5700 0 20 但是如果not in集合中含有空值的话,就会取得所有的数据。 所以我们在not in语句的子集合中,要进行空值得判断,不然就会取所有数据。 最后我们再看一些有用的查询,any、all (1)any 和集合的任意一个值比较 查询工资比30号部门任意一个员工高的员工信息 select * from emp where sal>any(select sal from emp where deptno=30); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --------------- -------------------- ------------------ --------------- -------------- ----- ----- --------------- 1116 林建国 主管 1116 22-1月 -16 5700 0 20 1110 张三 主管 1110 12-3月 -14 5200 0 20 1112 王五 销售 1116 25-4月 -12 4400 800 30 1113 赵二 后勤 1110 30-5月 -11 3450 0 40 1111 李四 销售 1116 03-11月-15 3400 500 30 1117 马富邦 后勤 1116 22-7月 -13 2800 0 40 1114 李磊磊 会计 1110 22-12月-15 2500 0 50 相当于 select * from emp where sal > (select min(sal) from emp where deptno=30) (2)all 和集合的所有值比较 查询工资比30号部门所有员工高的员工信息 select * from emp where sal>all(select sal from emp where deptno=30); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --------------- -------------------- ------------------ --------------- -------------- ----- ----- --------------- 1110 张三 主管 1110 12-3月 -14 5200 0 20 1116 林建国 主管 1116 22-1月 -16 5700 0 20 相当于 select * from emp where sal > (select max(sal) from emp where deptno=30) 转载请注明出处:http://www.52php.cn/article/p-xvnogdko-bbb.html (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |