oracle系列笔记(2)---多表查询

多表查询

上一篇文章主要讲Oracle的单表查询，详情请看链接：单表查询

    这篇文章主要讲四点：

 （1）oracle多表查询    （2）SQL99标准的连接查询   （3）子查询     （4）分级查询

  oracle多表查询有两种方式，一种是oracle所特有的查询方式，一种是SQL99标准的连接查询，是通用的一种多表查询。

   1. Oracle 连接

    等值连接 在where中加入连接条件。在表中有相同的列在列名之前可以加上前缀。

--查询 员工的id 员工的姓名  员工的部门名称 员工所在的部门的城市select e.employee_id,e.first_name,d.department_name,l.city  --这三个属性都不在一个表中，所以要建立关系from employees e, departments d,locations l where e.department_id=d.department_id  and d.location_id =l.location_id

外连接：  使用外连接可以查询不满足条件的数据。外连接的符号(+)

--查询员工的id 姓名 部门名称 要求显示 所有的员工信息 没有部门的员工也要显示出来select  e.employee_id,e.first_name, d.department_namefrom employees  e,departments dwhere  e.department_id=d.department_id(+)--因为有可能有员工是没有部门的，这个时候默认是不显示的，要显示所有员工，就在和员工对面加（+）--查询员工的id 姓名 部门名称 要求显示 所有的部门信息 没有员工的部门也要显示出来select  e.employee_id,e.first_name, d.department_namefrom employees  e,departments dwhere  e.department_id(+)=d.department_id--同样会有部门没有员工的，同上

自连接：就是都在统一表中

--显示所有的员工 姓名 编号 和上一级领导的名字select e.employee_id,e.first_name, ee.first_namefrom employees e, employees eewhere  e.manager_id=ee.employee_id

2.SQL99标准的连接查询

通用的一种多表查询，上面的是oreal特有的

Join  on ：

---查询员工的id 姓名 部门的名字select e.employee_id,e.first_name, d.department_namefrom  employees  e join departments d on e.department_id= d.department_id;

Natural join ：自然连接  子句会以两个表中具有相同名字的列作为等值连接条件，在表中查询满足条件的数据

-- 部门的id   部门的名称  部门所在城市的名字select d.department_id,d.department_name,l.cityfrom departments d  natural join  locations l  --会按顺序查找是否有相同

Join using：使用using子句可以在有多个列满足条件的情况下进行筛选，不要给选中的列加上表名或者前缀或者别名

select e.employee_id,e.last_name,d.location_idfrom employees e join departments d using(department_id)--我觉得比Natural join更实用吧

外联接:

Lift outer join on ：左外连接

right outer join on 右外连接

Full  outer join on 满外连接： 就是把两边都不满足的选出来

--查询员工的id  员工的姓名  员工的部门  要求 显示所有的员工信息没有部门的员工信息也要显示出来select  e.employee_id, e.first_name,d.department_namefrom employees e left outer join departments d on  e.department_id=d.department_id; --左满表示左边全部显示

3.子查询

--查找工资比OConnell工资高的所有人select  first_name,salary from employees ewhere e.salary>(select salary from employees where  last_name='OConnelll')--括号中最后返回的仅仅是OConnell一个人的工资 

oracle 中有两个隐藏列

rowid 是一个唯一的 不会重复  rownum 是隐藏的用来标识当数据行的

Rownum：

-----找出工资最高的前三个select a.first_name,a.salary from(select rowid,rownum, e.first_name,e.salaryfrom employees e order by e.salary desc) awhere rownum<=3--括号中已经对工资进行降序，但rownum在没有排序之前就已经从1,2...开始开好了，--你最后排序了排序好后rownum变成杂乱的--，外面又是一个新表，同样也有隐藏的rownum，这个时候同样是1,2....排好序，而且也对于的是降序，--所以rownum<=3就可以出现工资最高的前三。

分页查询：使用oracle语法 写出一个分页查询的sql

--总共有107条件数据  每页显示10条     查询第三页数据  31-40  要求按照工资排序--第一步完成排序   第二步 固化rownum   第三步根据条件进行筛选select rn r,t2.first_name,t2.salary from (select rownum rn,t1.* from(select  *from employees e order by e.salary desc) t1) t2where rn>=31 and rn<=40--第一个括号仅仅是降序了，第二个括号使rownum 和salary都有序排列，第三才是实例化rownum这个列是他变成真正存在的r列。--同时where是不能别名的，所以要用rn，而不是r，因为顺序where优先于select

在子查询中使用组函数

--查询工资最低的员工有哪些select first_name,salary  from employees  where  salary= (select min(salary) from employees )--括号中就是一个工资最小值---筛选出比  按照部门分组 得到部门的最低工资  然后找出比 50号部门最低工资高的部门有哪些--子查询中的 HAVING 子句select department_id,min(salary) --有min就表示不能有wherefrom employees egroup by department_id having min(salary)>( select min(salary) from  employees  where department_id=50)--括号中是50号部门的最低工资

多行子查询：

In （等于列表中任意一个）

select  first_name,salary  from employees  e where e.salary in( select salary from employees ee where ee.salary<10000) --就相当于只要满足里面一个就可以，感觉加个in一点意义都没有

ANY (和子查询返回的任一个值比较）

select  first_name,salary  from employees  e where e.salary > any( select salary from employees ee where ee.salary<10000) --就相当于和括号中最小的一个值比较

ALL(和子查询返回的所有值比较）

--就相当于和括号中最大的一个值比较

4.分级查询：

可以明确的看到上下级关系

分级查询可以从上往下查询也可以从下往上查询

-- 从底部查询select employee_id,last_name,job_id,manager_id from employeesstart with employee_id=104connect by prior manager_id=employee_id 

运行结果：

--从顶部到底部查询select last_name,employee_id, manager_idfrom employees e start with last_name='King'connect by prior  employee_id =manager_id

--使用level 和lpad  格式化分层查询select lpad(last_name,length(last_name)+(level*3)-2,'_')from employees e start with last_name='King'connect by prior  employee_id =manager_id

运行结果：

 

这篇文件就讲到这里，有不足之处欢迎大家留言指点！