Oracle数据库 —— SQL之select语句(4) 多表查询

前端之家收集整理的这篇文章主要介绍了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,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,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,s_emp m where e.manager_id!=m.id;(25个)


4 内连接

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


5 外连接

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

select distinct m.first_name,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,s_region r where d.region_id=r.id(+) and r.id is not null;

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

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

  select first_name,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,s_dept where dept_id=s_dept.id;
/*sql99*/
  select s_emp.id,name from s_emp join s_dept on dept_id=s_dept.id;
  select s_emp.id,name from s_emp inner join s_dept on dept_id=s_dept.id;
/*把每个部门的名字和对应地区名列出来*/
  select d.name,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,s_region r where d.region_id=r.id;
/列出所有的部门名和对应的地区名/
  select d.name,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,s_dept d where dept_id=d.id;        等值的外连接     select first_name,s_dept d where dept_id=d.id(+);        非等值     列出每个员工的id salary和对应的工资级别     select id,salgrade where salary between losal and hisal;        非等值的外连接     select id,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,s_emp m where e.manager_id(+)=m.id and e.manager_id is null;

猜你在找的Oracle相关文章