多表连接查询
一、等值连接
连接条件使用=
例:查询所有员工的ID,名字,和部门名称
select s_emp.id,first_name,name
from s_emp,s_dept
where s_emp.dept_id = s_dept.id;
例:查询员工姓名和部门名称所在地区的名称
select se.first_name,sd.name,sr.name
from s_emp se,s_dept sd,s_region sr
where se.dept_id = sd.id and sd.region_id = sr.id;
例:查询部门名称包含sa的员工姓名薪水
select se.first_name,se.salary,sd.name
from s_emp se,s_dept sd
where se.dept_id = sd.id and lower(sd.name) like '%sa%';
例:查询部门名称是5位,该部门员工薪水不等于1500,并按员工的薪水降序排列
select sd.name,se.salary,
from s_emp se,s_dept sd
where se.dept_id = sd.id and length(sd.name)=5 and se.salary ^=1500
order by se.salary desc;
二、不等值连接
连接条件使用的不是= 而是>=...<=....between...and
例:查询所有员工的等级
select se.first_name,se.salary,sr.name
from s_emp se, s_rank sr
where se.salary between sr.minSal and sr.maxSal;
三、外连接
(1)左外连接和右外连接
把外键为空的数据一并查询出来,使用“+”, “+”放在数据少的那一方
例:查询所有员工以及对应的部门的名字,没有部门的员工也要显示出来
select last_name,name
from s_emp,s_dept
where s_emp.dept_id = s_dept.id(+);
等价于
select last_name,name
from s_emp left outer join s_dept on s_emp.id = s_dept.id;
等价于
select last_name,name
from s_dept right outer join s_emp on s_emp.dept_id = s_dept.id;
例:查询所有员工以及对应的部门的名字,没有任何员工的部门也要显示出来
select last_name,name
from s_emp right outer join s_dept on s_emp.dept_id = s_dept.id;
等价于
select last_name,name
from s_dept left outer join s_emp on s_emp.dept_id = s_dept.id;
总结:向哪边连接,哪边的数据就多;如例中向左连接,左边的表s_dept的数据就多
(2)全连接
例:查询所有员工以及对应的部门的名字,没有任何员工的部门也要显示,没有部门的员工也要显示
select last_name,name
from s_emp full outer join s_dept on s_emp.dept_id = s_dept.id;
(3)自连接
例:查询每个员工的名字以及员工对应的经理的名字
select s1.last_name,s2.last_name
from s_emp s1,s_emp s2
where s1.manager_id = s2.id;
四、对两个结果集的操作:union,minus,union all,intersect
注意:前提条件为 两个结果集中查询的列要完全一致
(1)union:求并集,重复的行只显示一次
select last_name,dept_id,name
from s_emp,s_dept
where s_emp.dept_id=s_dept.id(+)
union
select last_name,dept_id,name
from s_emp,s_dept
where s_emp.dept_id(+)=s_dept.id;
(2)union all:并集,不会消除重复的行
select last_name,dept_id,name
from s_emp,s_dept
where s_emp.dept_id=s_dept.id(+)
union all
select last_name,dept_id,name
from s_emp,s_dept
where s_emp.dept_id(+)=s_dept.id;
(3)minus:第一个结果集减去第二个结果集和它相同的记录(差集)(会消除重复行)
select last_name,dept_id,name
from s_emp,s_dept
where s_emp.dept_id=s_dept.id(+)
minus
select last_name,dept_id,name
from s_emp,s_dept
where s_emp.dept_id(+)=s_dept.id;
(4)intersect:交集
select last_name,dept_id,name
from s_emp,s_dept
where s_emp.dept_id=s_dept.id(+)
intersect
select last_name,dept_id,name
from s_emp,s_dept
where s_emp.dept_id(+)=s_dept.id;
五、rownum伪列(oracle独有的)
rownum 所能做的操作:
rownum只能等于1 如果让其等于其他数 则查不到数据
rownum只能大于0 如果让其大于其他数 则查不到数据
rownum 可以小于等于任何整数
例: 查询员工表中的第一个人的名字
select last_name
from s_emp
where rownum=1;
例:查询员工表中前两个人的名字
select last_name
from s_emp
where rownum<3;
例:查询员工表中第三到第五条员工信息
select last_name,salary
from s_emp
where rownum<=5
minus
select last_name,salary
from s_emp
where rownum<3