oracle数据库——联接查询（内部联结、外部联结、自联结） 高级查询（子查询、关联子查询）decode(表达式) case row_number()用法

联结查询

1.内部联结
语法1：select 列名,列名 from 表名,表名
where 条件
例如：

select ename,job,dname,loc from emp,dept where emp.deptno=dept.deptno;
--或者对表定义别名：
select ename,job,dname,loc,e.deptno from emp e,dept d where e.deptno=d.deptno; 
--对结果进一步筛选
select ename,job,dname,loc,e.deptno from emp e,dept d where e.deptno=d.deptno and job='CLERK';

语法2：
select 列名,列名 from 表1
inner join 表2 on 联结条件
例如：

select ename,job,dname,loc from emp
inner join dept on emp.deptno=dept.deptno;
--对结果进一步筛选
select ename,job,dname,loc from dept
inner join emp on emp.deptno=dept.deptno;
where job='CLERK';

三表查询：
例如查询员工姓名，职位，薪水，部门名称，工资等级

select ename,job,sal,dname,grade from emp
inner join dept on emp.deptno=dept.deptno
inner join salgrade on sal between losal and hisal;
--进一步筛选工资至少2级的员工 
select ename,job,sal,dname,grade from emp
inner join dept on emp.deptno=dept.deptno
inner join salgrade on sal between losal and hisal
where grade>=2  
order by dname;       
--查询每个工资等级的员工人数   
 select grade,count(*) as 人数 from emp    
 inner join salgrade on sal between losal and hisal
 group by grade
--查询每个部门的平均工资  
 select dname,avg(sal) from emp
 inner join dept on emp.deptno=dept.deptno
 group by dname   

分组之后只能查询
A 被分组的列
B 使用聚合函数统计的列

2. 外部联结
   首先建两个表

 --建学生表
 create table student
    (
        stuid number(10) not null,
        stuname varchar2(30) not null,
        gender varchar2(2) not  null,
        phone varchar2(30) null
    )
    --建成绩表
    create table exam
    (
       stuid number(10) not null,
       subject varchar2(30) not null,
       score number(3) not null
    )

    --插入数据
    insert into student values(100,'张三','男',null);
    insert into student values(101,'李四','男',null);
    insert into student values(102,'王五','男',null);   
    insert into student values(103,'熊二','男',null);
    insert into student values(104,'熊三','男',null); 
    
    
    insert into exam values(100,'JAVA',95);
    insert into exam values(100,'C++',98);
    insert into exam values(103,'C++',58);
    insert into exam values(108,'C#',66);
    insert into exam values(109,'C#',77);
    
    --两表联合，查询姓名，科目，成绩
    --内部联结
    select stuname,subject,score from student
      inner join exam on student.stuid=exam.stuid;
    --外部联结
    --左外部联结
    select stuname,subject,score from student
      left outer join exam on student.stuid=exam.stuid;

    --右外部联结
    select stuname,subject,score from student
      right outer join exam on student.stuid=exam.stuid;
 
    --两个表名替换位置，结果不同
    select stuname,subject,score from exam
      right outer join student on student.stuid=exam.stuid;
      
      --完整外部连接
     select stuname,subject,score from exam
      full join student on student.stuid=exam.stuid; 
  
  --查询姓名，科目，成绩，如果没有成绩就显示缺考
  select stuname,nvl(subject,'缺考'), nvl(score,0) from exam
     right outer join student on exam.stuid=student.stuid;
  
  --查询没参加过考试的学生    
  select stuname,subject, score from exam
     right outer join student on exam.stuid=student.stuid
     where subject is null;

3. 自联结

--联结的两个表是同一个表
--查询员工姓名，职位，上司姓名，上司职位
select e.ename,e.job,m.ename as 上司姓名 ,m.job as 上司职位 from emp e
  left outer join emp m on e.mgr=m.empno

高级查询

1.子查询
语法：
select * from 表 where
列=
(
select … from 表
);
select * from 表 where
列 in
(
select … from 表
);
select * from 表 where
(列1,列2) in
(
select 列1，列2 from 表
)

 --和SMITH在同一个部门的员工信息
 select * from emp where deptno=
 (
     select deptno from emp where ename='SMITH'
 )
 --比scott工资高的员工信息
 select * from emp where sal>
 (
     select sal from emp where ename='SCOTT'
 )
 
 --和10号部门工资一样的员工信息
 
 select * from emp where sal=
 (
     select sal from emp where deptno=10
 )
 --当子查询的结果不止一个时，要把=换成in
 select * from emp where sal in
 (
     select sal from emp where deptno=10
 )
 --大于10号部门工资的员工信息
 select * from emp where sal>
 any(
     select sal from emp where deptno=10
 )
 --比10号部门所有人工资都高的员工信息
  select * from emp where sal>
 all(
     select sal from emp where deptno=20
 )
 --查询和SMITH同一部门同一职位的员工信息
 select * from emp where deptno=
 (
 select deptno from emp where ename='SMITH'
 )
 and job=
 (
 select job from emp where ename='SMITH'
 )   
--查询和SMITH同一部门同一职位的员工信息
 select * from emp where (deptno,job)=
 (
     select deptno,job from emp where ename='SMITH'
 );

 --INSERT 子句中使用子查询
 --在成绩表中添加一条数据
 insert into exam values((select stuid from student where stuname='熊二'),'JAVA',59);
 --delete 子句中使用子查询
 --删除所有在纽约工作的员工
 delete from emp  where deptno in
 (
     select deptno from dept where loc='NEW YORK'
 )
 --update语句使用子查询
 --把在纽约工作的员工工资加10
 update emp set sal=sal+100 where deptno in
 (
     select deptno from dept where loc='NEW YORK'
 )
 --select 子句中使用子查询
 --查询员工的名字，职位，部门
 select ename,job,(select dname from dept where deptno=emp.deptno) as 部门 from emp;
 --查询员工的名字，职位，平均工资
 select ename,job,sal,(select avg(sal) from emp where deptno=e.deptno) as 平均工资  from emp e;

2.关联子查询
把一个查询结果作为临时表 ，从临时表中查询数据，将临时表和其他表做联结查询

 --查询比本部门平均工资高的员工信息
     select * from emp e where sal>
     (
         select avg(sal) from emp where deptno=e.deptno
     )
 -- 分页查询
 --每页5行，显示第一页
 select * from
 (
     select t.*,rownum as rn from 
     (
         select * from emp order by sal asc
     )t
 )t2 where t2.rn between 1 and 5
 --显示第二页
 select * from
 (
     select t.*,rownum as rn from 
     (
         select * from emp order by sal asc
     )t
 )t2 where t2.rn between 6 and 10
 --用函数的方法显示第二页 
select * from
( 
 select emp.*,row_number() over(order by sal asc) as rn from emp
)t where t.rn between 6 and 10;
 
 -- 查询每个部门中工资最高的员工的信息（方法1）
 select * from emp
 inner join 
 (
 select deptno,max(sal) as maxsal from emp group by deptno
 )t
 on emp.deptno=t.deptno and emp.sal=t.maxsal;
 --查询每个部门中工资最高的员工的信息（方法2）
 select * from emp
 where (deptno,sal) in
 (
     select deptno,max(sal) as maxsal from emp group by deptno
 )
 
 -- 集合查询
 --并集（查询的列数、类型要相同）
 --查询工资大于两千并且名字以S开头的员工信息
 select * from emp where sal>2000
 union 
 select * from emp where ename like 'S%'
 --合并两个结果
 select ename,job,sal from emp
  union 
 select dname,'', avg(sal) from dept inner join emp on emp.deptno=dept.deptno group by dname
 
 --交集
 --查询工作是职员并且工资大于两千的
 select * from emp where job='CLERK'
 intersect
 select * from emp where sal>2000
 
 --差集
 --查询部门为10的减去工资小于两千的
 select * from emp where deptno=10
 minus
 select * from emp where sal<2000

decode(表达式)

  select ename,decode(job,'CLERK','科员','MANAGER','经理','ANALYST','分析师','PRESIDENT','董事长','xxxx') from emp;

case

--例一
select ename,case
     job
     when 'CLERK' then '科员'
     when 'MANAGER' then '经理'
     when 'ANALYST' then '分析师'
     when 'PRESIDENT' then 'PRESIDENT'
       else '未知'
     end case from emp;
--例二
select case ,count(*) from
    ( 
    select ename, case
      when sal<=1000 then '蓝领'
      when sal<=2000 then '灰领'
      when sal<=3000 then '白领'    
      when sal<=5000 then '金领'
        else 'xxx领'
       end case from emp
    )t group by case;

row_number()

 select emp.* ,row_number() over(order by sal asc) from emp;
 select emp.* ,row_number() over(partition by deptno order by sal asc) from emp;