Oracle笔记（3）

分组：

delect deptno avg(sal) from emp group by deptno;

//deptno 因为是分组的。不能单独用。

SQL> select deptno,avg(sal) from emp;

select deptno,avg(sal) from emp

       *

第 1 行出现错误:

ORA-00937: 不是单组分组函数

 

//遇到其他不需要分组字段需要放在最后

SQL> select deptno,job,avg(sal) from emp groupby deptno,job;

 

 

如果需要判断可以使用having

SQL> select deptno,avg(sal) from emp group bydeptno having avg(sal)>=2000;

这里不能使用where ,在一些情况下where和having可以互换。

--where后不能跟组函数

--如果既可以用where又可以用having，就用where，先筛选再分组效率高

select deptno,avg(sal) from emp where deptnoin(10,20) group by deptno;

 

 

 

 

下午：

 

多表：

select e.ename,e.job,d.dname from emp e,dept d; //但是这样会产生笛卡尔积

 

所有要给定条件：

select e.ename,e.deptno,d.dname from emp e,dept dwhere e.deptno=d.deptno;

 

查询emp表中工资大于salgrade表中losal且小于hisal的结果：

SQL> select e.ename,e.job,s.grade from empe,salgrade s where e.sal>s.losal and e.sal<s.hisal;

 

ENAME     JOB            GRADE                                                                                                                      

---------- --------- ----------                                                                                                                      

SMITH     CLERK              1                                                                                                                      

JAMES     CLERK              1                                                                                                                      

ADAMS     CLERK              1                                                                                                                      

WARD      SALESMAN           2                                                                                                                      

MARTIN    SALESMAN           2                                                                                                                       

MILLER    CLERK              2                                                                                                                      

TURNER    SALESMAN           3                                                                                                                      

ALLEN     SALESMAN           3                                                                                                                      

CLARK     MANAGER            4                                                                                                                      

BLAKE     MANAGER            4                                                                                                                       

JONES     MANAGER            4                                                                                                                      

KING       PRESIDENT          5       

并且部门是三号：

 

SQL> select e.ename,e.job,s.grade from empe,salgrade s where e.sal>s.losal and e.sal<s.hisal and s.grade =3;

 

 

左外连接：（oracle写法）

select d.deptno,d.dname,count(e.ename) from empe,dept d where e.deptno(+)=d.deptno group by d.deptno,d.dname

通用写法：

selece d.edeptno,d.dname,count(e.ename) from emp eright join dept d on e.deptno = d.deptno group by d.deptno,d.dname;

//两个表用jion连接。判断用on连接。//右连接同理。

 

 

自连接：

select e.ename,p.ename from emp e,emp p wheree.mgr = p.ename;

//把一个表虚拟出两张表，再进行操作。判断第一个表的mgr 等于第二个的ename所有得到e.ename的上司。

 

子查询：

//相当于把一条语句的结果拿来执行第二条语句。子查询通常跟在select、from、where、having后面。

select ename,deptno from emp where deptno= (selectdeptno from emp where ename='SCOTT')