oracle sql 基础知识2

上一篇: oracle sql 基础知识1
下一篇: oracle sql 基础知识3

多表查询

1. 关联查询

select * from emp e, dept d where e.deptno = d.deptno;

2. 外连接查询

// 用 (+)来实现， 这个+号可以这样来理解： + 表示补充，即哪个表有加号，这个表就是匹配表。如果加号写在右表，左表就是全部显示，所以是左连接
select * from dept d,  emp e where d.deptno = e.deptno(+) ;  -- 左外连接
select * from emp e, dept d where e.deptno(+) = d.deptno;  -- 右外连接
// left join .. on  和 right join on 实现
select * from emp e left join  dept d on e.deptno = d.deptno;  --left join .. on 左外链接

分组函数

1. count函数

//查表中有多少条数
select count(*) from emp; 

2. 最小值查询 min函数

select min(sal) from emp;   --员工最低工资

3. 最大值查询 max函数

select max(sal) from emp;  --员工最高工资

4. 平均值函数

selelct avg(sal) from emp; --员工平均工资

5. 求和函数

select sum(sal) from emp; --所有员工的工资之和

6. row_number() over() 函数
   row_number() over(partition by col1 order by col2) 表示根据col1分组，在分组内部根据col2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内是连续且唯一的）。

分组统计

1. GROUP BY 关键字 使用

语法: select * from 表名 {where 查询条件} {GROUP BY 分组字段} order by 列名1 ASC|DESC , 列名2 ASC|DESC
select count(*) ,deptno, job from emp group by deptno, job order by deptno;  --查询每个部门里每个工作有多少人

注意： 在分组统计的时候，除了分组函数意外，结果列必须是group by 后面分组的列（后面有的列前面才能有，后面没有的列前面绝对不能有）

2. HAVING 关键字使用

在分组查询之后加条件不能用where 需要用having

/*显示非销售人员工资名称以及从事同一工作的员工的月工资的总和,
并且要满足从事同一工作月工资总和大于5000, 结果按月工资总和升序排列*/
select sum(e.sal) e.job 
from emp e 
where e.job <> 'SALESMAN' 
group by e.job 
having sum(e.sal) > 5000
order by sum(e.sal) asc

子查询

在一个查询内部还包括另一个查询, 则称为子查询

• 例子

• 查询所有和每个部门最低工资相等的人

selelct * from emp t where t.sal in (select min(e.sal) from emo e group by e.deptno)

• 查询每个部门的最低工资和最低工资的员工和部门名称
  

1. exists和not exists 的使用
   exists sql返回结果集为真
   not exists sql 不返回结果集为真

//查询出有员工的部门有哪些
select * from dept t where  t.deptno in (select distinct deptno from emp);

in 关键字尽量少用, 因为性能比较低, 可以使用exists来代替,性能高
使用exists子查询一般要和外层查询关联

//exists() 子查询记录数为0则整个表达式是false, 如果大于0为true
select * from dept t where exists (select * from emp e where e.deptno = t.deptno);
select * from dept t where not exists (select * from emp e where e.deptno = t.deptno);

2. union和unin All的使用 做并集
   union 把两个集合做并集的时候会把重复的数据去掉
   union all 把两个集合做并集的时候不会把重复的数据去掉

select * from emp t where t.sal > 1000
union
select * from emp t where t.sal > 2000;