group by 分组

--每个工作的人数selectcount(job)fromempgroupbyjob;--有多少工作selectcount(coutn(job))fromempgroupbyjob;--每个工作的最高工资selectmax(sal)fromempgroupbyjob;--每个工作的最低工资selectmin(sal)fromempgroupbyjob;--每个工作的平均工资selectavg(sal)fromempgroupbyjob;--所有工作中最大的平均工资selectmax(avg(sal))fromempgroupbyjob;

使用having 根据条件过滤组

selectjob,count(job)fromempgroupbyjobhavingcount(job)>=3;

where 用在group by 之前

selectjob,count(job)fromempwheresal>1000groupbyjobhavingcount(job)>=3;

decode 函数

decode(value1if1then1if2then2...else)

decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)

如果 value1=if1 返回then1

否则 如果 value1=if2 返回 then2

默认的返回值是else

selectdecode(sal,1600,'一千六',5000,'五千','其他')fromemp;--按照job的内容分类统计其中部门ID是30的人数selectjob,count(decode(DEPTNO,30,1,null))deptnofromempgroupby(job);--按照部门分类统计每个部门中工作是CLERK的人数selectdeptno,count(decode(job,'CLERK',1,null))countsfromempgroupby(deptno);--按照部门分类计算每个每部中工作是CLERK的平均工资selectdeptno,avg(decode(job,'CLERK',sal,null))CLERK_AVG_SALfromempgroupby(deptno);

insertintoempvalues(8000,'lmdtx','CLERK',8080,to_date('1980/09/09','yyyy/mm/dd'),30000,null,null);insertintoempvalues(8880,'dsy','CLERK',8980,to_date('1982/02/02','yyyy/mm/dd'),3000,null,null);

表的关联

外键 在一个表中 存着对应另外一张表的主键

连接表

1·笛卡尔积emp 表中 deptno 为空的查询不到

--emp表中的所有的行*dept中的所有的行select*fromemp,dept;--需要写过滤条件过滤掉不需要的select*fromemp,deptwhereemp.deptno=dept.deptno;select*fromempe.deptdwheree.deptno=d.deptno;

2·内连接 这个结果和笛卡尔积 用条件后的结果一样

inner join 和 join 一样（省略 inner） 默认就是 内连接

join...on...fromempeinnerjoindeptdone.deptno=d.deptno

两个表的一个外键的名字 和 另一张表的主键的名字一样

fromempinnerjoindeptusing(deptno)

select*fromempeinnerjoindeptdone.deptno=d.deptno;select*fromempinnerjoindeptusing(deptno);

3·外连接

1·左外连(左边表的数据必须是全都有的，没有的地方补null)

fromempleftouterjoindeptusing(deptno)

select*fromempleftouterjoindeptonemp.deptno=dept.deptno;selectename,nvl(dname,'还没有部门')fromemp2leftouterjoindept2using(deptno);

2·右外连

select*fromemprightouterjoindeptusing(deptno);--查询所有部门的名称和部门人数按人数的降序排selectdname,count(empno)numfromemp2rightjoindept2Using(deptno)groupbydnameorderbynumdesc;

3·全外连（全都有）

empfullouterjoindept

select*fromempfullouterjoindeptusing(deptno);--查询所有部门的名称以及部门的人数没有部门的人化为未分配selectnvl(dname,'未分配'),count(empno)fromemp2fullouterjoindept2using(deptno)groupbydnameorderbydname;selectnvl(dname,'未分配部门')部门名字,decode(count(empno),0,'未分配人手',count(empno))人数fromemp2fullouterjoindept2using(deptno)groupbydnameorderbydname;

4·自连接

selecte1.ename,e2.enamefromemp2e1innerjoinemp2e2one1.mgr=e2.empno;

selecte1.ename,e2.enamefromemp2e1leftjoinemp2e2one1.mgr=e2.empno;selectnvl(e1.ename,'没有手下'),e2.enameMGRfromemp2e1rightjoinemp2e2one1.mgr=e2.empno;selecte2.ename,decode(count(e1.ename),0,'没有手下',count(e1.ename))fromemp2e1rightjoinemp2e2one1.mgr=e2.empnogroupbye2.ename

子查询

用在where子句中，单行单列selecte1.enamefromemp2e1innerjoinemp2e2one1.mgr=e2.empnowheree2.ename='KING';selectdistinctdnamefromemp2e1joindept2d2ONe1.deptno=d2.deptnowheree1.enamelike'%S%';selectdnamefromdept2wheredeptnoin(selectdeptnofromemp2whereenamelike'%S%');

ANY() 任意一个

ALL() 所有

selectename,job,salfromemp2wheresal>all(selectDISTINCTsalfromemp2wherejob='MANAGER')andjob<>'MANAGER';selectenamejob,salfromemp2wheresal>any(selectdistinctsalfromemp2wherejob='CLERK')andjob<>'CLERK';

exists 用于判断后面的子查询是否有行 有true 没有false

selectenamefromemp2e1whereEXISTS(select1fromemp2e2wheree2.mgr=e1.empno);selectenamefromemp2e1wherenotexists(select1fromemp2e2wheree2.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 中的多列 查询

selecte.ename,e.sal,d.deptno,d.dnamefromemp2einnerjoindept2done.deptno=d.deptnowhere(e.deptno,e.sal)in(selectdeptno,min(sal)fromemp2wheredeptnoisnotnullgroupbydeptno);select*fromemp2where(deptno,sal)in(selectdeptno,min(sal)fromemp2wheredeptnoisnotnullgroupbydeptno);selectdeptno,min(sal)fromemp2wheredeptnoisnotnullgroupbydeptno;

用在having

fromempwheredeptnoisnotnullgroupbydeptnohavingavg(sal)<(selectmax(avg(sal))fromempgroupbydeptno)

selectmax(avg(sal))fromemp2groupbydeptno;

selectdeptno,round(avg(sal))fromemp2wheredeptnoisnotnullgroupbydeptnoHAVINGavg(sal)<(selectmax(avg(sal))fromemp2groupbydeptno);

selectdname,deptnofromemp2joindept2using(deptno)wheredeptnoisnotnullgroupbydeptno,dnamehavingavg(sal)=(selectmax(avg(sal))fromemp2groupbydeptno);

分页

伪列

selectempno,ename,rownumfromemp2orderbyename;

select*fromemp2whererownum<=10;

select*from(selectempno,ename,rownumnfromemp2whererownum<=10)wheren>5andn<=10

select*from(selectempno,ename,rownumnfromemp2)wheren>5andn<=10

select*from(selectempno,ename,rownumnfromemp2whererownum<=10)wheren>5

select*from(selectempno,ename,sal,rownumnfrom(select*fromemp2orderbysal)whererownum<=10)wheren>5;

内联视图(Inner View)

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

selectename名字,job职位,emp_num手下人数fromemp2join(selectdeptnod,count(empno)emp_numfromemp2innerjoindept2using(deptno)groupbydeptno)ondeptno=dwherejob='MANAGER';

selectename名字，job职位,dname部门,deptno部门编号,emp_num手下人数fromemp2innerjoin(selectdeptnod,count(empno)emp_num,dnamefromemp2innerjoindept2using(deptno)groupbydeptno,dname)ondeptno=dwherejob='MANAGER'

group by 对多个列分组

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