Oracle基础(四)之多表查询

所有员工所在部门的部门名称
select last_name,name
from s_emp,s_dept;
S1表(id,name)
 
S1表
id name
1 s
2 a
3 b
 
 
 
 
 
 
 
 
 
s2表(id,age)

S2表
id name
1 s
2 a
3 b
 
 
 
 
 
 
 
 
拼接后的表:S表
 
S表
s1.id s1.name s2.id s2.age
1 s 1 20
1 a 2 21
1 b 3 22
2 s 1 20
2 a 2 21
2 b 3 22
3 s 1 20
3 a 2 21
3 b 3 22
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
查询所有人的名字以及年龄
select name,age
from s1,s2;
多表查询实质是查询一张表
多表查询会产生迪卡尔积
消除笛卡尔积: 使用连接条件消除笛卡尔积 连接条件放在where中
    连接条件常用于主键的值=外键的值
select s1.name,s2.age
from s1,s2
where s1.id=s2.id;
1.等连接:用等号连接的连接
1)所有员工所在部门的部门名称
 select e.id,e.last_name,d.name
 from s_emp e,s_dept d
 where e.dept_id=d.id;
2)查询员工的姓名和部门所在地区的名称(3张表)
     select e.last_name,r.name
  from s_emp e,s_dept d,s_region r
  where e.dept_id=d.id
  and d.region_id=r.id;
3)查询部门名称包含sa的员工姓名薪水
 select d.name,e.last_name||e.first_name,e.salary
 from s_emp e,s_dept d
 where e.dept_id=d.id
 and lower(d.name) like '%sa%';
 注:先写连接条件 再写限定条件(消除无用数据)
 
4)查询欧洲销售部门的薪水在1000到2000的员工信息 
 select r.name,d.name,e.salary
 from s_emp e,s_dept d,s_region r
 where e.dept_id=d.id and d.region_id=r.id
 and r.name='Europe'
 and e.salary between 1000 and 2000
 and d.name='Sales';
5)查询部门名称是5位,该部门员工的薪水不等于1500,并按员工的薪水降序排序
 select d.name,e.salary
 from s_emp e,s_dept d
 where e.dept_id=d.id
 and length(d.name)=5
 and e.salary!=1500
 order by e.salary DESC;

2.不等连接:连接条件使用的不是等号的连接
            >,<,...between..and
drop table s_gender;
create table s_gender(
 id number(5) primary key,
 minSal number(7),
 maxSal number(7),
 name varchar2(20));
insert into s_gender
values(1,0,1000,'蓝领');
insert into s_gender
values(2,1000,1500,'白领');
insert into s_gender
values(3,1500,2500,'金领');
commit;
查询所有员工的工资等级? 
  select e.last_name,g.name
  from s_emp e,s_gender g
  where e.salary between g.minSal and g.maxSal;
3.在where中使用
1)外连接:把外键为空的数据一并查询出来
     使用:(+)(仅限于Oracle数据库)
     规则:(+)放在查询数据少的一方(即表中有空值的一方)
(1)左外连接:(+)放在等号的右边(标准的sql:..left join ...on...左外连接)
 
    查询员工所在部门的名称,包括没有部门号的员工(所有员工)
   select e.last_name,d.name
   from s_emp e,s_dept d
   where e.dept_id=d.id(+);
   注意:等连接不能查询外键值为空的数据。
   -------------------------------------------  
  标准:
   select e.last_name,d.name
   from s_emp e left join s_dept d
   on e.dept_id=d.id;
(2)右外连接:(+)放在等号的左边(标准的sql:..right join...on....右外连接              
   查询所有员工所在部门的名称,但是需要把所有部门给查询出来insert into s_dept values(1000,'teaching',2);
commit;
 select e.last_name,d.name
 from s_emp e,s_dept d
      where e.dept_id(+)=d.id;
----------------------------------------
标准:
 select e.last_name,d.name
 from s_emp e right join s_dept d
 on e.dept_id=d.id;
2)全连接 (标准的sql语句:full join...on...)
 查询员工所在部门的名称,没有部门号的员工,所有的部门都查询出来
   select e.last_name,d.name
   from s_emp e full join s_dept d
   on e.dept_id=d.id;
3)自连接:连接的时候来自同一张表
 查询所有员工的经理的last_name,salary  包括没有经理的员工?
   select w.last_name,w.salary,m.last_name,m.salary
   from s_emp w,s_emp m
   where w.manager_id=m.id(+);
4)集合连接
 union:并集,重复的列只显示一行
 union all:并集,不会消除重复的行
 minus:差集
 intersect:交集
 rownum:伪列,可以分页技术,逻辑位置
      只能等于1
      不能大于或者大于等于任何正整数
      可以小于或者小于等于任何正整数
  select *
  from s_dept
  where rownum<=2;  //查询表中前俩条数据
 rowid:数据保存到文件中的物理位置
  select rowid
  from s_dept;
 (1)部门表中前5条记录?
   select *
   from s_dept
   where rownum<=5;
 (2)部门表中第3条到第5条数据?
   select *
   from s_dept
   where rownum<=5
   minus
   select *
   from s_dept
   where rownum<=2;
posted @ 2020-09-03 22:02  zitos  阅读(65)  评论(0编辑  收藏