第四章 多表查询
笛卡儿积
A{a,b,c} B{1,2,3} A*B{a,1}{a,2}{a,3}{b,1}{b,2}{b,3}{c,1}{c,2}{c,3}
select s.id,s.last_name,s.dept_id,d.id,d.name from s_emp s,s_dept d;
等值连接:基于主外键找到有用的数据
select s.id,s.last_name,s.dept_id,d.id,d.name from s_emp s,s_dept d where s.dept_id=d.id;
不等值连接:把不具有关联关系的两张表通过某些列建立联系
create table rank(id number primary key,name varchar2(10),missal number,maxsal number);
insert into rank values(1,'白领',0,1000);
insert into rank values(2,'蓝领',1001,2000);
insert into rank values(3,'金领',2001,3000);
查询员工所处薪水等级
select s.last_name,s.salary,r.name from s_emp s,rank r where s.salary between r.missal and r.maxsal;
自连接(自己和自己连接)
查询员工及其经理的信息
select s.id,s.last_name,s.manager_id,m.id,s.salary from s_emp s,s_emp m where s.manager_id=m.id;
外连接
左外连接:在等值连接的基础之上,主表中没有匹配的数据也要显示出来
查询员工及所处部门的信息,即使员工没有部门也要显示出来
select s.id,s.last_name,s.dept_id,d.id,d.name from s_emp s,s_dept d where s.dept_id=d.id(+);
等价select s.id,s.last_name,s.dept_id,d.id,d.name from s_emp s left outer join s_dept d
on s.dept_id=d.id;(outer可省略)
查询员工及经理的信息,即使员工没有经理也要显示出来
select s.id,s.last_name,s.manager_id,m.id,s.salary from s_emp s,s_emp m where s.manager_id=m.id(+);
右外连接:在等值连接的基础之上,把从表没有匹配的数据也要显示出来
查询员工及其所属部门的信息,即使部门没有员工匹配也要显示出来
select s.id,s.last_name,s.dept_id,d.id,d.name from s_emp s,s_dept d where s.dept_id(+)=d.id;
等价select s.id,s.last_name,s.dept_id,d.id,d.name from s_emp s right outer join s_dept d
on s.dept_id=d.id;(outer可省略)
全连接:在等值连接的基础之上,把主表和从表没有匹配的数据全部显示出来
查询员工及所处部门的信息,员工没有部门以及部门没有员工匹配也要显示出来
select s.id,s.last_name,s.dept_id,d.id,d.name from s_emp s full outer join s_dept d
on s.dept_id=d.id;(outer可省略)
查询员工及经理的信息,即使员工没有经理或者经理没有员工也要显示出来
select s.id,s.last_name,s.manager_id,m.id,s.salary from s_emp s full join s_emp m on s.manager_id=m.id;
union 求并集A∪B
查询员工及所处部门的信息,员工没有部门以及部门没有员工匹配也要显示出来
select s.id,s.last_name,s.dept_id,d.id,d.name from s_emp s,s_dept d where s.dept_id=d.id(+) union
select s.id,s.last_name,s.dept_id,d.id,d.name from s_emp s,s_dept d where s.dept_id(+)=d.id;
union all 求并集(不去重)
minus 第一个查询结果去掉第二个结果相同部分A-B
intersect 求交集A∩B
rownum 伪列:数据库表中不存在,只要查询的时候,数据库会给查询的内容每一行都分配一个伪列(用于分页)
rownum等于1,查的是第一条,1除外的值都不能用于等于 where rownum=1;
rownum<n,n可以是任意值,但是不能为0,
rownum<=n,n可以是任意值,但是不能为0,
rownum>m,m只能为0,为0显示所有内容,
查询第6条到第10条的记录
select id from s_emp where rownum<=10 minus select id from s_emp where rownum<=5;