• 多测师肖老师__多表讲解(7.9)


    多表讲解

    1、什么是多表关联查询?

    定义:查询数据来源于多张表

    在实际工作中我们数据需要去2个表或者更多的表中提取,需要使用多表关联查询

    2、多表查询类型?

    (1)内连接

      (2)左连接(左外连接)

    (3)右连接

    (4)全连接

    (5)左独有连接

    (6)右独有连接

    ==========================================

    建表语句:

    cREATE table dept(dept1 VARCHAR(6),dept_name VARCHAR(20))  default charset=utf8;
    
    INSERT into dept VALUES ('101','财务');
    INSERT into dept VALUES ('102','销售');
    
    INSERT into dept VALUES ('103','IT技术');
    
    INSERT into dept VALUES ('104','行政');
    
    CREATE table emp (sid VARCHAR(6),name VARCHAR(20),age TINYINT(2),woektime_start VARCHAR(10),incoming SMALLINT(10),dept2 VARCHAR(6))default charset=utf8;
    
    insert into emp VALUES ('1789','张三',35,'1980/1/1',4000,'101');
    
    insert into emp VALUES ('1674','李四',32,'1983/4/1',3500,'101');
    
    insert into emp VALUES ('1776','王五',24,'1990/7/1',2000,'101');
    
    insert into emp VALUES ('1568','赵六',57,'1970/10/11',7500,'102');
    insert into emp VALUES ('1564','荣七',64,'1963/10/11',8500,'102');
    
    insert into emp VALUES ('1879','牛八',55,'1971/10/20',7300,'103');
    insert into emp VALUES ('1880','刘十',55,'1971/10/21',7000,'105');
    
    drop  table  dept ;
    drop  table  emp ;
    select  *  from dept;
    select *  from  emp ;
    

     dept部门表:

    dept1 部门编号

    dept_name    部门名称

     

    emp 表  

    sid  员工编号

    name  员工姓名

    age  员工年龄

    woektime_start     开始工作时间

    incoming   工资

    dept2     部门编号

     

     ============================================================

    1、内连接

    定义:查询两个表共有的关联数据

    A、普通内连接

    B、隐藏内连接

    ============================

    A、普通内连接

    格式:select  *  from   表1 inner  join  表2  on  表1.关联字段1=表2.关联字段2 ;

    案例1:select  *  from   dept inner  join  emp  on  dept.dept1=emp.dept2 ;

    案例2:select * from dept s inner join emp c on s.dept1=c.dept2 ;  将表名取了别名

    案例3:select  *  from   dept s inner  join  emp c  on  dept1=dept2 ;

    B、隐藏内连接

    格式:select  *  from   表1,表2  where   表1.关联字段1=表2.关联字段2;

    案例:select  *  from   dept,emp  where  dept.dept1=emp.dept2 ;

     ============================

    左连接:

    定义:以左表为主,(显示整个左表),右表关联的数据就显示,不关联的数据就以null形式显示

    格式:select  * from   表1 left  join  表2    on  表1.关联字段=表2.关联字 ;

    案例:select  * from   dept left  join   emp  on   dept.dept1=emp.dept2 ;

      ============================

    右连接

    定义:以右表为主,(显示整个右表),左表关联的数据就显示,不关联的数据就以null形式显示

    格式:select  * from   表1   right  join  表2    on  表1.关联字段=表2.关联字 ;

    案例:select  * from   dept RIGHT   join   emp  on   dept.dept1=emp.dept2 ;

       ============================

    左独有数据:

    左连接的基础上,在判断右表为null  字段来判断

    格式:select  * from   表1 left  join  表2    on  表1.关联字段=表2.关联字  where     右表字段   is   null;

    案例:select * from dept left join emp on dept.dept1=emp.dept2 where name is null;

        ============================

    右独有数据:

    右连接的基础上,在判断左表为null  字段来判断

    格式:select  * from   表1 right  join  表2    on  表1.关联字段=表2.关联字  where     左表字段   is   null;

    案例:select * from dept right join emp on dept.dept1=emp.dept2 where   dept1   is null;

         ============================

    左独有+右独有   union

    格式:select  * from   表1 left  join  表2    on  表1.关联字段=表2.关联字  where     右表字段   is   null    union

    select  * from   表1 right  join  表2    on  表1.关联字段=表2.关联字  where     左表字段   is   null;

    案例:select * from dept left join emp on dept.dept1=emp.dept2 where name is null
    union
    select * from dept RIGHT join emp on dept.dept1=emp.dept2 where dept1 is null;

         ============================

    全 连接 :

    (1)内连接+左独有+右独有

    (2)左连接+右独有

    (3)右连接+左独有

    案例1:内连接+左独有+右独有

    select * from dept inner join emp on dept.dept1=emp.dept2
    UNION
    select * from dept left join emp on dept.dept1=emp.dept2 where name is null
    union
    select * from dept RIGHT join emp on dept.dept1=emp.dept2 where dept1 is null;

    案例2:左连接+右独有

    select * from dept left join emp on dept.dept1=emp.dept2
    UNION
    select * from dept RIGHT join emp on dept.dept1=emp.dept2 where dept1 is null;

    案例3:右连接+左独有

    select * from dept RIGHT join emp on dept.dept1=emp.dept2
    UNION
    select * from dept left join emp on dept.dept1=emp.dept2 where name is null;

    总结:
    多表:
    普通内连接:select * from 表1 inner join 表2 on 表 1.关联字段=表2.关联字段
    隐藏内连接:select * from 表1,表2 where 表 1.关联字段=表2.关联字段
    左连接:select * from 表1 left join 表2 on 表 1.关联字段=表2.关联字段
    右连接 :select * from 表1 right join 表2 on 表 1.关联字段=表2.关联字段
    左独有数据:select * from 表1 left join 表2 on 表 1.关联字段=表2.关联字段 where 表2中的字段 is null
    右独有数据:select * from 表1 right join 表2 on 表 1.关联字段=表2.关联字段 where 表1 中的字段 isnull
    全外连接:union
    (1)左独有+右独有+内连接
    (2)左连接+右独有
    (3)右连接+左独有

    ==========================================

    1.列出每个部门的平均收入及部门名称;

    条件:group  by     dept_name  

    结果:dept  表 :dept_name     emp 表:avg(incoming)

    方法一:SELECT dept_name,avg(incoming) from dept left JOIN emp on dept.dept1=emp.dept2 GROUP BY dept_name;

    方法二:

    SELECT dept_name,avg(incoming) from (
    SELECT * FROM dept RIGHT JOIN emp on dept1=dept2
    UNION
    SELECT * FROM dept LEFT JOIN emp on dept1=dept2 WHERE NAME IS NULL
    ) b1
    GROUP BY b1.dept_name;

     2.财务部门的收入总和;

    条件:  dept  表 :dept_name="财务"      emp表:sum(incoming)

    结果:收入总和

    方法1:select sum(incoming) from dept left JOIN emp on dept.dept1=emp.dept2 WHERE dept_name="财务"

    方法2:

    SELECT dept_name,sum(incoming) from (
    SELECT * FROM dept RIGHT JOIN emp on dept1=dept2
    UNION
    SELECT * FROM dept LEFT JOIN emp on dept1=dept2 WHERE NAME IS NULL
    ) b1
    WHERE b1.dept_name='财务';

    方法3:

    SELECT SUM(incoming) AS '收入总和' from emp where dept2=(SELECT dept1 from dept where dept_name='财务')

     3.It技术部入职员工的员工号  

    条件: dept_name=it技术部门

    结果: sid

    方法1:select sid from dept left JOIN emp on dept.dept1=emp.dept2 where dept_name="IT技术";

    方法2:SELECT name,sid from (
    SELECT * FROM dept RIGHT JOIN emp on dept1=dept2 
    UNION
    SELECT * FROM dept LEFT JOIN emp on dept1=dept2 WHERE NAME IS NULL
    ) b1
    WHERE b1.dept_name='IT技术';

    方法3:

    SELECT sid from emp where dept2=(SELECT dept1 from dept where dept_name='It技术')

  • 相关阅读:
    linux系统rewrite重定向及搭建discuz
    nginx客户端请求分类
    linux系统nginx动静分离
    RDB与AOF
    AOF持久化
    redis持久化(RDB)
    redis操作
    redis搭建
    Redis简介
    小技巧从此拒绝$?
  • 原文地址:https://www.cnblogs.com/xiaolehua/p/15705009.html
Copyright © 2020-2023  润新知