MySQL基础之自连接用法简介

待我称王封你为后i 2021-08-14 02:22 459阅读 0赞

MySQL系列之自连接简介，MySQL自连接操作，没有特定的关键字，所谓自连接指的是同一个表不同实例之间的join操作

引用https://www.w3resource.com的图示：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70]

特征：

*  自连接是同一个表不同实例的连接操作
 *  自连接必须指定别名(aliasName)区分不同实例

自连接操作一般应用与有层级关系的表，最经典的就是员工/经理例子

ok，下面举例说明

创建部门dept表

/*Table structure for table `dept` */
    
    DROP TABLE IF EXISTS `dept`;
    
    CREATE TABLE `dept` (
      `DEPTNO` int(2) NOT NULL,
      `DNAME` varchar(14) DEFAULT NULL,
      `LOC` varchar(13) DEFAULT NULL,
      PRIMARY KEY (`DEPTNO`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    /*Data for the table `dept` */
    # 往dept表写数据
    insert  into `dept`(`DEPTNO`,`DNAME`,`LOC`) values (10,'ACCOUNTING','NEW YORK'),(20,'RESEARCH','DALLAS'),(30,'SALES','CHICAGO'),(40,'OPERATIONS','BOSTON');

创建员工emp表

DROP TABLE IF EXISTS `emp`;
    
    CREATE TABLE `emp` (
      `EMPNO` int(4) NOT NULL,
      `ENAME` varchar(10) DEFAULT NULL,
      `JOB` varchar(9) DEFAULT NULL,
      `MGR` int(4) DEFAULT NULL,
      `HIREDATE` date DEFAULT NULL,
      `SAL` int(7) DEFAULT NULL,
      `COMM` int(7) DEFAULT NULL,
      `DEPTNO` int(2) DEFAULT NULL,
      PRIMARY KEY (`EMPNO`),
      KEY `FK_DEPTNO` (`DEPTNO`),
      CONSTRAINT `FK_DEPTNO` FOREIGN KEY (`DEPTNO`) REFERENCES `dept` (`DEPTNO`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    /*Data for the table `emp` */
    # 往员工emp表写数据
    insert  into `emp`(`EMPNO`,`ENAME`,`JOB`,`MGR`,`HIREDATE`,`SAL`,`COMM`,`DEPTNO`) values (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20),(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30),(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30),(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20),(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30),(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30),(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10),(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20),(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10),(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30),(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20),(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30),(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20),(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

这里使用左连接比较合适，因为顶级员工，也就是公司的总裁，是没有最上级的，用inner join的话，是不能获取数据的

SELECT 
      a.ename AS empName,
      b.ename AS managerName 
    FROM
      emp a 
      LEFT JOIN emp b 
        ON a.mgr = b.empno 
    ORDER BY managerName ;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 1]  
ok，根据sql，可以画出员工关系的图例，king是公司总裁  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 2]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70]: /images/20210813/daed81df3a924134bf941aa73675b386.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 1]: /images/20210813/85f2b4ea0775410285e88df8b2172098.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 2]: /images/20210813/8544b247115645d09dc5843d0e2f900c.png