分组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