数据库之多表查询的形式

小灰灰 2021-05-03 05:08 558阅读 0赞

select *from employees s;
    select *from departments t;
    select *from job_history;
    select *from tax;
    --多表查询形式：
    --1、等值连接
    --2、非等值连接
    --3、自连接 自己连接自己 列如：雇员表连接雇员表
    --4、自然连接
    --5、内连接
    --6、外连接
          --左外连接
          --右外连接
          --全连接
    --7、Oracle独有外连接
      --需求：查询一下雇员的信息，显示雇员的名称，职位编号，薪水，部门名称
      --等值连接 取交集
      select *
        from employees s, departments t
       where s.department_id = t.department_id;
      --等值连接查部分列
       select s.employees, s.first_name, t.departments_name, t.department_id
         from employees s, departments t
        where s.department_id = t.department_id;
      --需求：查询一下雇员表中每个雇员的税率
        --非等值连接
        select *
          from employees s, tax x
         where s.salary between x.min_salary and x.max_salary;
        --非等值连接查部分列
        select s.first_name, x.tax_percent, s.salary, x.min_salary, max_salary
          from employees s, tax x
         where s.salary between x.min_salary and x.max_salary;
      --作业：查询一下雇员的信息，显示雇员的名称，邮箱，职位名称，部门名称
    ----------------------------------------------------------------------------
      --数据库之自连接 
      --需求：查询一下雇员的姓名，和他的管理者的姓名？
       select p.first_name, l.first_name
         from employees p, employees l
        where p.manager_id =l.employee_id;
        
        select p.first_name, l.first_name
          from employees p, employees l
         where p.manager_id =l.employee_id
           and p.employee_id= 101;
           
      --自然连接 不需要条件 自然选取两个表中相同属性字段
        select *from employees s natural join departments;
     --内连接 取交集
     select *
       from employees s
      inner join departments t
         on s.department_id = t.department_id;
    ----------------------------------------------------------------------------------------
    ---外连接
    select count(*) from departments s;
    select  distinct s.department_id from employees s;
    ---统计一下那些部门不再雇员表中使用
    select *
      from departments t
     where t.department_id not in
           (select distinct s.department_id
              from employees s
             where department_id is not null)
    --查询一下每个雇员的名称，邮箱等和部门名称（要统计所有的员工）
    --左外连接：
    select *
      from employees s
      left outer join departments t
        on s.department_id = t.department_id;
    --右外连接：
    select *
      from employees s
      right outer join departments t
        on s.department_id = t.department_id;
    --全连接：
    select *
      from employees s
      full join departments t
        on s.department_id = t.department_id;
    
    --Oracle独有外连接
    --等值连接
    select s.employee_id, s.first_name, t.department_name, t.department_id
      from employees s, departments t
      where s.department_id=t.department_id
    --等值连接改造成Oracle独有外连接
    ---相当于左连接
    select s.employee_id, s.first_name, t.department_name, t.department_id
      from employees s, departments t
      where s.department_id=t.department_id(+)
    --相当于右连接
    select s.employee_id, s.first_name, t.department_name, t.department_id
      from employees s, departments t
      where s.department_id(+)=t.department_id;
    ---作业
    --统计每个部门的平均工资，显示平均工资和部门工资1
    select avg(s.salary), t.department_name
      from employees s
      left outer join departments t
        on s.employee_id = t.department_id
     group by t.department_name
     order by avg(s.salary)
    
    --统计每个职位的最高工资，显示最高工资和职位名称1
    select *from jobs
    
    select max(s.salary) 最高工资, s.job_id
      from employees s
      left outer join departments t
        on s.employee_id = t.department_id
     group by s.job_id
     order by max(s.salary)
     
     select max(s.salary) 最高工资, j.job_title
       from employees s
       left outer join jobs j
         on s.job_id = j.job_id
      group by j.job_title
      order by max(s.salary);
    -----------------------------------------------------------------------------------
    ----数据库之集合操作
    ---查询一下雇员表中，工资最高的前三名人员，和工资最低的后三名人员
    select *from (select * from  employees s order by s.salary desc )where rownum<=3
    select *from (select * from  employees s order by s.salary asc)where rownum<=3
    --合并 --unio去重复 union不去重复
    select *from (select * from  employees s order by s.salary desc )where rownum<=3
    union all
    select *from (select * from  employees s order by s.salary asc)where rownum<=3
    ---统计雇员的信息，删除工资最高的三个人
    --minus相减即可
    select *
      from employees s
    minus
    select *
      from (select * from employees s order by s.salary desc)
     where rownum <= 3
    --从雇员表中将换过工作的人员去掉
    select *
      from employees s
    minus
    select *
      from employees s
     where s.employee_id in (select distinct employee_id from job_history);
    ---相交 intersect
    select 1,2,3,4,7 from dual
    intersect
    select 1,2,3,4 ,6 from dual