Java38:数据库二(Oracle)-创新互联

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)<(select max(avg(sal)) from emp group by deptno) select max(avg(sal)) from emp2 group by deptno;select deptno,round(avg(sal)) from emp2 where deptno is not null group by deptno HAVING avg(sal)<(select max(avg(sal)) from emp2 group by deptno); select dname,deptno from emp2 join dept2 using(deptno) where deptno is not null group by deptno,dname having avg(sal)=(select max(avg(sal)) from emp2 group by deptno);

分页

    伪列

select empno,ename,rownum from emp2 order by ename; select * from emp2 where rownum<=10;select * from (select empno,ename, rownum n from emp2 where rownum<=10) where n>5 and n<=10select * from (select empno,ename, rownum n from emp2 ) where n>5 and n<=10select * from (select empno,ename, rownum n from emp2 where rownum<=10) where n>5 select * from (select empno,ename,sal, rownum n from (select * from emp2 order by sal) where rownum <=10) where n>5;

内联视图(Inner View)

就是把select后的结果当作一张表

select ename 名字,job 职位,emp_num 手下人数 from emp2 join(select deptno d,count(empno) emp_num from emp2 inner join dept2 using(deptno) group by deptno) on deptno=d where job='MANAGER';select ename 名字，job 职位,dname 部门,deptno 部门编号,emp_num 手下人数 from emp2 inner join(select deptno d,count(empno) emp_num,dname from emp2 inner join dept2 using(deptno) group by deptno, dname) on deptno=d where job='MANAGER'

group by 对多个列分组

而这两个列都是对部门的唯一标识，不会影响分组

另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。

网站栏目：Java38:数据库二(Oracle)-创新互联
网页地址：http://cdkjz.cn/article/iipge.html