Oracle高级查询

深碍√TFBOYSˉ_ 2024-02-17 17:51 19阅读 0赞

**分组Group by 与Having**

\--统计每个部门的员工个数

select \* from emp;  
select deptno, count(1),job from emp group by deptno,job;  
  
  
\--统计每个部门的员工人数至少是5个以上的部门  
select deptno,count(1) from emp group by deptno having count(1)>5;  
  
  
\--根据不同的部门排名  
**--分析函数**  
\--（1）ROW\_NUMBER 返回连续的排位，不论值是否相等  
\--【语法】ROW\_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2)  
\--【功能】表示根据COL1分组，在分组内部根据 COL2排序，而这个值就表示每组内部排序后的顺序编号（组内连续的唯一的）  
select deptno,ename,sal,row\_number() OVER (partition by deptno order by sal desc) aa from emp;  
  
  
\--（2）RANK 具有相等值的行排位相同，序数随后跳跃  
\--【语法】RANK ( ) OVER ( \[query\_partition\_clause\] order\_by\_clause )  
 select deptno,ename,sal,rank() OVER (partition by deptno order by sal desc) aa from emp;  
  
  
\--（3）DENSE\_RANK 具有相等值的行排位相同，序号是连续的  
\--【语法】dense\_RANK ( ) OVER ( \[query\_partition\_clause\] order\_by\_clause )  
  
\--根据每个部门工资降序排序的名次

select deptno,ename,sal,dense\_rank() OVER (partition by deptno order by sal desc) aa from emp;

\--根据员工表中的工资降序排序的名次  
select deptno,ename,sal,dense\_rank() OVER (order by sal desc) aa from emp;  
  
  
**\-- 子查询**  
\--查询与“SCOTT”在同一个部门的雇员信息  
  
\--(1)查询“SCOTT”用户的部门编号  
select deptno from emp where ename='SCOTT'  
  
\--(2)查询20的部门的雇员信息  
select \* from emp where deptno=20;  
  
\-- = (只能一个值) /in(单个/多个) 发生错误?  
select \* from emp where deptno in (select deptno from emp where ename='SCOTT');  
  
select \* from emp;  
  
\--查询与“SCOTT”不在同一个部门的雇员信息 not in  
select \* from emp where deptno not in(select deptno from emp where ename='SCOTT');  
  
  
\--查询工资在1500-3000的员工信息部门是10与40  
select \* from emp where sal>=1500 and sal<=3000  
  
\--部门是10与40  
select \* from ( select \* from emp where sal>=1500 and sal<=3000) e1  
where e1.deptno in (10,40);  
  
  
\--EMP表中，查询雇员薪水大于3000的部门名称  
  
\--薪水大于3000的部门编号  
select \* from emp where sal>3000;  
  
\--薪水大于3000的部门名称  
select dname from dept where deptno in(select deptno from emp where sal>3000)  
select dname from dept where deptno not in(select deptno from emp where sal>3000)  
  
  
\--exists 存在 where /  
select dname from dept d where not exists (select \* from emp where sal>3000 and emp.deptno=d.deptno)  
  
select dname from dept d where exists (select \* from emp where sal>3000)  
  
  
  
 **连接查询**  
 **--内连接**  
\--(1) select \* from 表1 inner join 表2 on ...  
  
select \* from emp; --18  
select \* from dept; --4  
  
\--查询雇佣所在的部门名称  
select empno,ename,job,sal,dept.dname from emp inner join dept on emp.deptno =dept.deptno  
  
\--(2) select \* from 表1，表2 where.....  
select empno,ename,job,sal,dept.dname from emp, dept where emp.deptno =dept.deptno  
  
\--表名能否交换位置？？会影响查询结果 (没有影响，可以交换!)  
select empno,ename,job,sal,dept.dname from dept inner join emp on emp.deptno =dept.deptno  
  
\--不等值=18\*4-18  
select empno,ename,job,sal,dept.dname from dept inner join emp on emp.deptno !=dept.deptno  
  
select \* from emp;  
select \* from dept;  
  
insert into dept values(50,'IT','HZ');  
  
 **--左外连接**   
\--以左表为基准 ，如果没有匹配的，显示为null  
\--select \* from 表1 left join 表2 on ...  
select empno,ename,job,sal,dept.dname from emp left join dept on emp.deptno =dept.deptno --18  
\--两张能否位置 （不能）  
select empno,ename,job,sal,dept.dname from dept left join emp on emp.deptno =dept.deptno --4?5 19  
  
 **--右外连接**   
\--以右表为基准 ，如果没有匹配的，显示为null  
\--select \* from 表1 left join 表2 on ...  
select empno,ename,job,sal,dept.dname from emp right join dept on emp.deptno =dept.deptno --19  
\--两张能否位置 （不能）  
select empno,ename,job,sal,dept.dname from dept right join emp on emp.deptno =dept.deptno --18  
  
  
  
  
**联合查询：**  
select \* from emp; --18  
select \* from emp2; --3  
  
\--删除多余的列  
alter table emp2 drop column dept;  
  
\-- union (过滤重复的数据)  
select \* from emp  
union  
select \* from emp2  
  
  
\-- union all (不过滤)  
select \* from emp  
union all  
select \* from emp2  
  
  
\-- intersect (交集)  
select \* from emp  
intersect  
select \* from emp2  
  
  
  
\-- minus (补集) 18-2=16  
select \* from emp  
minus  
select \* from emp2