Oracle数据库 —— SQL之select语句（4） 多表查询

6 select语句

6.5 多表查询

1 等值连接

　　连接条件都是用等号做的，这种连接称之为等值连接。
　　
　　这里涉及到三张表：
　　　　desc s_emp;//员工表
　　　　desc s_dept;//部门表
　　　　desc salgrade;//工资级别

　　列出每个员工的id first_name dept_id
　　　　select id,first_name,dept_id from s_emp;
　　/* 笛卡尔积 */
　　　　select s_emp.id,first_name,dept_id,name from s_emp,s_dept where dept_id=s_dept.id;

　　把每个部门的名字和部门对应的地区名列出来
　　　　select s_dept.name,s_region.name from s_dept,s_region where region_id=s_region.id;

　　/* 可以给表取别名，但取别名后，原表名就不能用了*/
　　　　select d.name,r.name from s_dept d,s_region r where region_id=r.id;

　　列出每个员工的first_name和所在的部门名以及部门对应的地区名
　　　　select e.first_name,d.name,r.name from s_emp e,s_dept d,s_region r where dept_id = d.id and region_id=r.id;

　　col name for a20(设置name占20列)

---

2 非等值连接　

　　如果连接条件不是用等号连接，则称之为非等值连接。

SQL> select * from salgrade;
     GRADE  LOSAL      HISAL
---------- ---------- ----------
     1    700       1200
     2   1201       1400
     3   1401       2000
     4   2001       3000
     5   3001       9999

列出每个员工的工资和工资对应的级别
　　salary　　　　grade
　　1800　　　　　3
　　1200　　　　　1
　　1300　　　　　2
select first_name,salary,grade from s_emp e,salgrade g where g.losal<=e.salary and e.salary<=g.hisal;

select first_name,salary,grade from s_emp,salgrade where salary between losal and hisal;

---

3 自连接

自连接：一个表中放入了逻辑上两种不同的数据。
　　s_emp 领导和非领导
　　挑出所有的领导？
　　有人的领导id是这个人的id
select id,manager_id from s_emp where id=manager_id;//不行
select distinct m.first_name,m.id from s_emp e,s_emp m where
　　　e.manager_id=m.id;

/* 找出所有的普通员工？ 25-8=17*/
select distinct m.first_name,m.id from s_emp e,s_emp m where e.manager_id!=m.id;(25个)

---

4 内连接

　　符合where条件数据就被选中，不符合where条件的数据就被过滤掉—内连接。

---

5 外连接

　　外连接：外连接的结果集等于内连接的结果集加匹配不上的记录。
　　一个也不能少
　　（+）——把（+）对面表的数据全部匹配出来
select distinct m.first_name,m.id from s_emp e,s_emp m where m.id=e.manager_id(+) and e.manager_id is null;

select distinct m.first_name,m.id from s_emp e,s_emp m where m.id=e.manager_id(+) and e.manager_id is not null;

列出每个员工的first_name和员工对应的部门名。
　　select first_name,name from s_emp e,s_dept d where e.dept_id=d.id;
　　把id=25员工的dept_id设置成NULL
　　update s_emp set dept_id=NULL where id=25;
　　commit;
列出所有的员工的first_name和员工对应的部门名，没有对应部门的员工first_name也要显示部门名，显示成no department
　　select first_name,nvl(name,’no department’) from s_emp e,s_dept d where e.dept_id=d.id(+);

列出所有部门名和部门对应的地区名
　　/* 增加一个新部门*/
　　insert into s_dept values(100,’Test’,NULL);
　　commit;
　　select d.name,r.name from s_dept d,s_region r where d.region_id=r.id(+) and r.id is not null;

　　SQL>/按回车执行上一条命令

列出每个员工的first_name salary 和工资对应的级别
　　select first_name,salary,grade from s_emp,salgrade where salary between losal and hisal;
　　/给老板加薪/
　　update s_emp set salary=12500 where id=22;
　　commit;
　　select first_name,salary,grade from s_emp,salgrade where salary between loscal(+) and hiscal(+);

　　select first_name,salary,grade from s_emp,salgrade where salary>=losal(+) and salary<=hisal(+);

---

6 sql99中内连接的标准

　　from a表 join b表 on 连接条件；
　　from a表 inner join b表 on 连接条件；

/*sql92写的多表连接*/
　　select s_emp.id,first_name,name from s_emp,s_dept where dept_id=s_dept.id;
/*sql99*/
　　select s_emp.id,first_name,name from s_emp join s_dept on dept_id=s_dept.id;
　　select s_emp.id,first_name,name from s_emp inner join s_dept on dept_id=s_dept.id;
/*把每个部门的名字和对应地区名列出来*/
　　select d.name,r.name from s_dept d,s_region r where d.region_id=r.id;/sql92/
/*sql99*/
　　select d.name,r.name from s_dept d inner join s_region on d.region_id=r.id;

---

7 sql99中外连接的标准

　　 a表 left outer join b表 on 连接条件；
　　a表 right outer join b表 on 连接条件；
　　a表 full outer join b表 on 连接条件；
　　
/sql92 列出每个部门的名字和地区的名字/
　　select d.name,r.name from s_dept d,s_region r where d.region_id=r.id;
/列出所有的部门名和对应的地区名/
　　select d.name,r.name from s_dept d,s_region r where d.region_id=r.id(+);
（+）对面的s_dept中的数据全部被匹配
a表 left outer join b表 on 连接条件；
a表中的数据全部被匹配出来。
　　select d.name,r.name from s_dept d left outer join s_region r on d.region_id=r.id;
　　
右外连接：b表中的数据全部被匹配出来
把左外连接改成右外连接
　　select d.name,r.name from s_region r right outer join s_dept d on d.region_id=r.id;

全外连接的结果集等于左外连接的结果集加右外连接的结果集，然后减去重复的记录。
　　select first_name,d.name from s_emp,s_dept d where dept_id=d.id(+);
/显示所有员工/
　　select first_name,d.name from s_emp left outer join s_dept d on dept_id=d.id;
/显示所有的部门/
　　select first_name,d.name from s_emp right outer join s_dept d on dept_id=d.id;
/合并集合的关键字union，合并两个结果集并排重/
　　select first_name,d.name from s_emp left outer join s_dept d on dept_id=d.id union
　　select first_name,d.name from s_emp right outer join s_dept d on dept_id=d.id;
　　select first_name,d.name from s_emp full outer join s_dept d on dept_id=d.id;

　　oracle不直接支持两端都加（+），要实现全外连接是通过union实现的。union all 合并两个结果集，不排重。

---

8 总结

连接
　　等值连接
　　　　把员工的first_name和对应的部门名显示出来
　　　　select first_name,d.name from s_emp,s_dept d where dept_id=d.id;
　　　　
　　等值的外连接
　　　　select first_name,d.name from s_emp,s_dept d where dept_id=d.id(+);
　　　　
　　非等值
　　　　列出每个员工的id salary和对应的工资级别
　　　　select id,salary,grade from s_emp,salgrade where salary between losal and hisal;
　　　　
　　非等值的外连接
　　　　select id,salary,grade from s_emp,salgrade where salary between losal(+) and hisal(+);
　　　　
　　自连接的内连接
　　　　找出所有的领导？
　　　　select m.id,m.first_name from s_emp e,s_emp m where e.manager_id=m.id;
　　　　
　　自连接的外连接
　　　　找出所有的普通员工?
　　　　select distinct m.id,m.first_name from s_emp e,s_emp m where e.manager_id(+)=m.id and e.manager_id is null;