SQL之操作多个表

时间:2023-02-01 20:03:31

  1. select ename, deptno from emp
  2. union all
  3. select dname, deptno from dept; //每列的数据类型必须相同

  1. select deptno from emp
  2. union
  3. select deptno from dept;//无重复(union 与union all的区别)

  1. select e.ename, d.loc from emp e, dept d where e.deptno=d.deptno;
  2. select e.ename, d.loc from emp e inner join dept d on(e.deptno=d.deptno);

  1. create table v as (select ename, job from emp where job='clerk');
  2. select e.ename, e.job, e.sal from emp e, v where e.ename=v.ename and e.job=v.job;
  3. select e.ename, e.job, e.sal from emp e inner join v on(e.ename=v.ename and e.job=v.job);
  4. select ename, job, sal from emp where(ename,job) in (select ename, job from v);

  1. select deptno from dept where deptno not in (select deptno from emp);
  2. //当(select deptno from emp)中出现null值是会出现错误
  3. select d.deptno from dept d 
  4. where not exists (select null from emp e where d.deptno=e.deptno);
  5. select d.* from dept d left outer join emp e on(d.deptno=e.deptno) where e.deptno is null; 
  6. select d.* from dept d where d.deptno not in(select deptno from emp);

  1. select e.ename, d.loc, eb.received from 
  2. emp e join dept d on (e.deptno=d.deptno)
  3. left join emp_bonus eb on(e.empno=eb.empno)
  4. order by d.loc; //内联加外联

  1. select e.ename, d.loc, 
  2. (select eb.received from emp_bonus eb where eb.empno=e.empno) as r
  3. from emp e, dept d where e.deptno=d.deptno
  4. order by 2; //使用标量子查询解决外联的性能问题

  1. create view v as 
  2. select ename,job from emp where deptno!=10
  3. union all
  4. select ename,job from emp where ename='ward';

  1. select * from (select e.ename,e.job,count(*) as cnt from emp e group by ename,job) e where not exists(select null from (select v.ename,v.job,count(*) as cnt from v group by ename,job) v where e.ename=v.ename and e.job=v.job and e.cnt=v.cnt )
  2. union all
  3. select * from(select v.ename,v.job,count(*) as cnt from v group by ename,job) v where not exists( select null from(select e.ename,e.job,count(*) as cnt from emp e group by ename,job)e where v.ename=v.ename and v.job=e.job and v.cnt=e.cnt);


  1. select e.empno,e.ename,e.sal* 
    case 
    when eb.type=1 then 0.1 
    when eb.type=2 then 0.2
    when eb.type=3 then 0.3
    else 0
    end as bonus
    from emp e, emp_bonus eb 
    where e.empno=eb.empno;

  1. select empno, ename, sum(bonus) as total_bonus from 
    (select e.empno,e.ename,e.sal* 
    case 
    when eb.type=1 then 0.1 
    when eb.type=2 then 0.2
    when eb.type=3 then 0.3
    else 0
    end as bonus
    from emp e, emp_bonus eb 
    where e.empno=eb.empno)
    group by empno, ename;