2Oracle高级数据选择.ppt

Oracle高级数据选择 提纲 显示多表数据 连接/外连接/自连接 组函数及其用法 子查询 连接 数据库中有很多的表，通常我们会按有意义的方式从多个表中取得数据。 连接两个表中数据的条件是：两个表中有相同的列（名称相同或者不同），通常这个列是其中一个表的主键（或主键的一部分） 连接表的键 连接 select e.ename,e.deptno,d.dname from emp e,dept d where e.deptno=d.deptno; 对应ANSI语法： select ename,emp.deptno,dname from emp join dept on emp.deptno=dept.deptno 注意：如果没有连接条件会生成迪卡尔积（cross join）。没有现实意义。为避免出现迪卡尔积情况，N个表至少要有N-1个相等性条件。 自然连接 Oracle根据两个表中的同名列自然建立连接。 对EMP和DEPT两个表，DEPTNO是两个表中唯一相同的列，因而两个表适合自然连接。 select ename,deptno,dname from emp natural join dept; 外连接 扩展了查询功能，即使在相同列中没有相应记录，也可以显示表中信息。 首先执行语句： update emp set deptno=NULL where ename=KING; 然后查询所有的职员所属的部门，没有所属部门的显示为空。 外连接 外连接除了返回符合查询中连接条件的行，也返回一个表中不符合查询中连接条件的行。 select e.ename,e.deptno,d.dname from dept d,emp e where d.deptno(+)=e.deptno; d为外表（有+标志），e为内表，内表返回所有行 ansi: select e.ename,e.deptno,d.dname from emp e left outer join dept d on d.deptno=e.deptno; 左外，即左边的表返回所有行 外连接 select e.ename,e.deptno,d.dname from dept d,emp e where d.deptno=e.deptno(+); ansi: select e.ename,e.deptno,d.dname from emp e right outer join dept d on d.deptno=e.deptno; 全外连接 select e.ename,e.deptno,d.dname from emp e full outer join dept d on d.deptno=e.deptno; 回滚数据 rollback; 表的自连接 有时候需要用一个表进行连接，这是用表的两个副本连接表中数据。 可用两个别名指定同一表。 职工(NO,name,lead_by) 使用组函数 select avg(sal) from emp; select count(*),count(1),count(rowid) from emp; select max(sal),min(sal) from emp; select sum(sal) from emp; *组函数忽略空值。 Select avg(comm) from emp; 如果想计算comm平均值，而不忽略空值，如何做？ group by子句 通常跟组函数联合使用 select job,count(job) from emp group by job select job,count(job) from emp; ? select job from emp group by job; 错误写法： Select ename,job from emp group by job; select deptno,avg(sal),job from emp group by deptno; 查询子句中的非组表达式都要放在列子句中组表达式之前，然后把非组表达式加入group by后面 select deptno,job,avg(sal) from emp group by deptno,job; 与order by一起使用 select deptno,job,avg(sal) from emp group by deptno,job order by 3 desc; Having筛选组数据 select deptno,job,avg(sal) from emp group by deptno,job having avg(sal)2000; Where用于分组前对记录筛选,having用于分组后筛选