Oracle Day2

p14&p15 多表联合查询

先看一个简单的多表查询的例子

 注意起别名，这里为了防止笛卡尔积，增加了筛选条件deptno。

做两个练习：

1.查出雇员的编号，姓名，部门的编号，名称，地址。

 2.查询出每个员工的上级领导

3.在2的基础上，查询员工的部门名称

关联三张表，写明约束条件即可

 4.查询每名员工的编号，姓名，部门名称，工资等级和他的上级领导的姓名，工资等级

select e.empno,
       e.ename,
       d.dname,
       decode(s.grade,
              1,
              '一级',
              2,
              '二级',
              3,
              '三级',
              4,
              '四级',
              5,
              '五级',
              '无级') egrade,
       e1.ename,
       decode(s1.grade,
              1,
              '一级',
              2,
              '二级',
              3,
              '三级',
              4,
              '四级',
              5,
              '五级',
              '无级') e1grade
  from emp e, dept d, salgrade s, emp e1, salgrade s1
 where e.deptno = d.deptno
   and e.sal between s.losal and s.hisal
   and e.mgr = e1.empno
   and e1.sal between s1.losal and s1.hisal

 p16&p17 外连接查询

先看一个普通查询的例子：

 其实，还有一个deptno为40的部门，但是因为emp中没有一个人员在这个部门，所以被省略了。

如果我们不希望emp中有数据被省略，就需要用到外连接，请看如下例子：

 在非全量表右边加（+）即可！

左外连接：全量表在左边select * from dept d,emp e where d.deptno=e.deptno(+);

右外连接：全量表在右边select * from emp e,dept d where e.deptno(+)=d.deptno;

查询结果是一样的，只是字段显示顺序会不同而已。

我们尝试用外连接解决之前多表查询的问题：

 这样所有的员工都能被查出来，即使他没有上级。

p18 sql1999语法

1.交叉连接cross join

用于多表查询，用cross join代替逗号，效果一样，不推荐用（含笛卡尔积）

 2.自然连接natural join

会自动找关联字段，去掉笛卡尔积

 相当于普通多表查询加了个条件：where emp.deptno=dept.deptno

3.using子句

有时建表不规范，无法直接用natural join找到关联字段，就可以用using自己指定关联字段：

 4.on（重要，常用）

语法：用join on代替，where

 左连接（左边的是全量表）

 右连接（右边的是全量表）

p19 Oracle分组函数

1.统计记录数 count()

 一般不建议填*，填这张表主键的名称比较好！如下所示：

 2.找最小值min()

这句话的含义是：在emp中sal列查询最小的值

3.找最大值max()

4.平均值avg()

5.求和sum()

P20 Oracle分组统计

我们先看emp表：

 

如果我们想统计不同部门（deptno）的人数，就需要用到分组统计：

 

统计每个部门的平均工资：

 

可以按照多个条件进行group by，但是要注意，没有放在group by后面的字段，不能直接select出来，例如：

 

这里dname和deptno虽然是一一对应的，但是Oracle可不知道。如果想显示dname，久要把dname也加在分组字段中。

P21 Oracle分组统计条件限定查询

限定查询语法为having +条件，必须作用在分组函数中

练习1：查询出部门平均工资大于2000的部门

 

练习2：显示非销售人员工作名称以及从事同一工作的员工的月工资的总和，并且要满足从事同一工作月工资总和大于5000，结果按月工资总和的升序排列

 

  P22 子查询

子查询就是查询里面嵌套查询

直接看例1：查询出工资比工号为7654高的人的所有信息

思路：先查出工号为7654的人的工资，然后查询工资大于这个数的所有人信息即可。

 

 子查询在操作中有三类：

单列子查询：返回的结果是一列的一个内容

单行子查询：返回多个列，有可能是一个完整的记录

多行子查询：返回多条记录

例2：查询出比雇员7654的工资高，同时从事和7788的工作一样的员工

 

 例3：要求查询每个部门的最低工资和最低工资的雇员和部门名称

思路：先分组查询出每个部门的最低工资以及对应的部门编号，将其作为一个子表a。

然后将emp，a，dept三表多表联合查询出最低工资对应的雇员和部门名称。

 例4：查询出所有和每个部门最低工资员工工资相等的人

 

P23 Oracle 的exists使用

先看一个子查询的简单例子：

查询出有员工的部门（思路：先查出所有员工的部门的去重，然后用in关键字查询这些deptno的部门信息）

 

 但是，我们不推荐使用in关键字，因为它的性能很低。

我们来学习exists和not exists的用法：

通俗地说，exists后面括号里面的语句如果能查到东西，那么exists()这一整句就为真，则整个语句生效。一般需要与外侧查询关联。

我们用exists来取代上面例子中的in：