Java38: 数据库二(Oracle)

seemebaby

　　group by 分组
--每个工作的人数　　
select count(job) from emp group by job;
　　
--有多少工作
　　
select count(coutn(job)) from emp group by job;
　　
--每个工作的最高工资
　　
select max(sal) from emp group by job;
　　
--每个工作的最低工资
　　
select min(sal) from emp group by job;
　　
--每个工作的平均工资
　　
select avg(sal) from emp group by job;
　　
--所有工作中最大的平均工资
　　
select max(avg(sal)) from emp group by job;
　　使用having  根据条件过滤组
select job,count(job) from emp group by job having count(job)>=3;　　where 用在group by 之前
select job,count(job) from emp where sal>1000 group by job having count(job)>=3;　　decode 函数
decode(value1 if1 then1 if2 then2 ... else)　　decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)
　　如果 value1=if1 返回then1
　　否则 如果 value1=if2 返回 then2
　　默认的返回值是else
select decode(sal,1600,'一千六',5000,'五千','其他') from emp;　　

　　
--按照job的内容分类统计其中部门ID是30的人数
　　
select job,count(decode(DEPTNO,30,1,null)) deptno from emp group by(job);
　　
--按照部门分类 统计每个部门中工作是CLERK的人数
　　
select deptno,count(decode(job,'CLERK',1,null)) counts from emp group by(deptno);
　　
--按照部门分类 计算每个每部中工作是CLERK的平均工资
　　
select deptno,avg(decode(job,'CLERK',sal,null)) CLERK_AVG_SAL from emp group by(deptno);
insert into emp values(8000,'lmdtx','CLERK',8080,to_date('1980/09/09','yyyy/mm/dd'),30000,null,null);　　
insert into emp values(8880,'dsy','CLERK',8980,to_date('1982/02/02','yyyy/mm/dd'),3000,null,null);
　　表的关联
　　外键 在一个表中 存着对应另外一张表的主键
　　连接表
　　1·笛卡尔积  emp 表中 deptno 为空的查询不到
--emp表中的所有的行* dept中的所有的行　　
select * from emp,dept;
　　
--需要写过滤条件 过滤掉不需要的
　　
select * from emp,dept where emp.deptno=dept.deptno;
　　

　　
select * from emp e.dept d where e.deptno=d.deptno;
　　2·内连接   这个结果和笛卡尔积 用条件后的结果一样
　　inner join 和 join 一样（省略 inner） 默认就是 内连接
join...on...　　
from emp e inner join dept d on e.deptno=d.deptno
　　两个表的一个外键的名字 和 另一张表的主键的名字一样
from emp inner join dept using(deptno)select * from emp e inner join dept d on e.deptno=d.deptno;　　

　　
select * from emp inner join dept using(deptno);
　　3·外连接
　　1·左外连(左边表的数据必须是全都有的，没有的地方补null)
from emp left outer join dept using(deptno)select * from emp left outer join dept on emp.deptno=dept.deptno;　　
select ename,nvl(dname,'还没有部门') from emp2 left outer join dept2 using(deptno);
　　2·右外连
select * from emp right outer join dept using(deptno);　　

　　
--查询所有部门的名称和部门人数按人数的降序排
　　
select dname,count(empno) num
　　
from emp2 right join dept2 Using(deptno)
　　
group by dname
　　
order by num desc;
　　3·全外连（全都有）
emp full outer join deptselect * from emp full outer join dept using(deptno);　　
--查询所有部门的名称以及部门的人数 没有部门的人 化为 未分配
　　
select nvl(dname,'未分配'),count(empno)
　　
from emp2 full outer join dept2 using(deptno)
　　
group by dname
　　
order by dname;
　　

　　

　　
select nvl(dname,'未分配部门') 部门名字,decode(count(empno),0,'未分配人手',count(empno)) 人数
　　
from emp2 full outer join dept2 using(deptno)
　　
group by dname
　　
order by dname;
　　4·自连接
select e1.ename,e2.ename from emp2 e1 inner join emp2 e2 on e1.mgr=e2.empno;select e1.ename, e2.ename from emp2 e1 left join emp2 e2 on e1.mgr = e2.empno;　　
select nvl(e1.ename,'没有手下')  , e2.ename MGR from emp2 e1 right join emp2 e2 on e1.mgr=e2.empno;
　　

　　
select e2.ename,decode(count(e1.ename),0,'没有手下',count(e1.ename))from emp2 e1 right join emp2 e2 on e1.mgr=e2.empno group by e2.ename
　　子查询
用在where 子句中，单行单列　　
select e1.ename from emp2 e1 inner join emp2 e2 on e1.mgr=e2.empno where e2.ename='KING';
　　

　　
select distinct dname from emp2 e1 join dept2 d2 ON e1.deptno= d2.deptno where e1.ename like '%S%';
　　

　　
select dname from dept2 where deptno in(select deptno from emp2 where ename like '%S%');
　　ANY() 任意一个
　　ALL() 所有
select ename,job,sal from emp2 where sal>all(select DISTINCT sal from emp2 where job='MANAGER') and job'MANAGER';　　

　　
select ename job,sal from emp2 where sal>any(select distinct sal from emp2 where job='CLERK') and job'CLERK';
　　exists 用于判断后面的子查询是否有行 有true 没有false
　　
select ename from emp2 e1 where EXISTS(select 1 from emp2 e2 where e2.mgr= e1.empno);
　　

　　
select ename from emp2 e1 where not exists(select 1 from emp2 e2 where e2.mgr=e1.empno);
　　3 in (1,2,3) true
　　3 in (1,2,null) false
　　5 not in (1,2,3) true
　　5 not in (1,2,null) false
　　5 in (1,2,null) false
　　5 not in (null) false
　　5in (null) false
　　not in 的子查询中不能出现null值
　　where 中的多列 查询
select e.ename,e.sal, d.deptno,d.dname from emp2 e inner join dept2 d on e.deptno= d.deptno where (e.deptno,e.sal) in (select deptno,min(sal) from emp2 where deptno is not null group by deptno);　　

　　
select * from emp2 where (deptno,sal)in (select deptno,min(sal) from emp2 where deptno is not null group by deptno);
　　

　　
select deptno,min(sal) from emp2 where deptno is not null group by deptno;
　　用在having
from emp where deptno is not null group by deptno having avg(sal)