等值连接、自连接、自然连接、左右外连接、内连接、全连接讲解

各连接的由来

       1、当业务系统演变复杂时，系统内部数据库的表结构也会随着复杂化，而不在是简单的单表操作。某个系统业务，往往伴随着是多个表之间的连接查询。

       2、为了消除多表查询中出现的笛卡尔积效应导致的多冗余数据。

各连接的讲解

    为了能帮助大家理解各连接的情况和区别，因此创建了两张表，具体表结构和数据如下：

 DROP TABLE IS EXISTS `student`; DROP TABLE IS EXISTS `course`; DROP TABLE IS EXISTS `sc`; -- 学生表 CREATE TABLE student ( `sid` INT NOT NULL AUTO_INCREMENT, `snum` INT NOT NULL PRIMARY KEY, `sname` VARCHAR (20) NOT NULL, `sage` TINYINT, `sclass` SMALLINT NOT NULL, UNIQUE KEY `student_num` (`sid`) # 最后不能加',',否则会报错 ) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8; # 使用'='的都放在student()之外。 INSERT INTO `student` ( `snum`, `sname`, `sage`, `sclass` ) VALUES (20201101, 'zhangsan', 20, 150), (20201102, 'lisi', 18, 151), (20201103, 'wangwu', 19, 151), (20201104, 'zhaoliu', 18, 150), (20201105, 'qianqi', 21, 151), (20201106, 'sunba', 20, 152); -- 课程表 CREATE TABLE course ( `cid` INT NOT NULL AUTO_INCREMENT, `cnum` INT NOT NULL PRIMARY KEY, `cname` VARCHAR (20) NOT NULL, UNIQUE KEY `course_num` (`cid`) # 最后不能加',',否则会报错 ) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8; # 使用'='的都放在student()之外。 INSERT INTO `course` ( `cnum`, `cname` ) VALUES (101, '数据结构'), (102, '编译原理'), (103, '计算机网络'), (105,'计算机组成原理'), (106, '数据库'); -- 学生课程中间表 CREATE TABLE `sc` ( `scid` INT NOT NULL UNIQUE AUTO_INCREMENT, `snum` INT NOT NULL, `cnum` INT NOT NULL, `grade` SMALLINT, PRIMARY KEY (`snum`, `cnum`) ) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8; INSERT INTO `sc` ( `snum`, `cnum`, `grade` ) VALUES (20201101, 101, 85), (20201102, 101, 55), (20201103, 101, 90), (20201101, 102, 88), (20201102, 102, 75), (20201103, 102, 58), (20201101, 103, 72), (20201102, 103, 80), (20201103, 103, 92); 

 

  

 

1. 等值连接

    等值连接也称为显示内连接，在进行多表联合查询时通过“=”等号来连接多张表之间相字段对应的值，其产生的结果会出现重复列。意思是，如果对多张表进行等值连接操作，那么前提要求是这多张表之间必须有相同的字段名。比如上述表中，课程表和学生课程中间表均有相同的“cnum”字段，学生表与学生课程中间表均有相同的“snum”字段。

    

2. 自连接

    自连接是一种特殊的连接，它的本质是将一个表与它自身做连接， 若要在一个表中查找具有相同列值的行，则可以使用自连接。使用自连接时需为表指定两个别名，且对所有列的引用均要用别名限定。

    

3. 自然连接

    自然连接是一种特殊的等值连接，在进行多表联合查询时通过“natural join”关键字来连接多张表之间相同字段对应的值，其产生的结果会将重复列只保留一个。如果对多张表进行自然连接操作，那么前提要求是这多张表之间必须有相同的字段名。比如上述表中，课程表和学生课程中间表均有相同的“cnum”字段，学生表与学生课程中间表均有相同的“snum”字段。

    

4. 左右外连接

    左外连接在进行多表联合查询时通过“left join.. on ”关键字来连接多张表之间符合条件的字段，其查询的结果会返回左表的所有数据，以及左右表交集的数据，若右表记录不足，则使用null来表示需要筛选的右表的值。

     

   

      

   右外连接在进行多表联合查询时通过“right join.. on ”关键字来连接多张表之间符合条件的字段，其查询的结果会返回右表的所有数据，以及左右表交集的数据，若左表记录不足，则使用null来表示需要筛选的右表的值。

 

 

5. 内连接

    内外接在进行多表联合查询时通过“inner join.. on ”关键字来连接多张表之间符合条件的字段，其查询的结果左右表匹配的数据。

    

6. 全连接

    全连接在进行多表联合查询时通过full join.. on ”关键字来连接多张表之间符合条件的字段，其查询的结果除了会返回左右表匹配的数据，还会返回两个表的所有行。但是mysql中没有全连接，也可以使用"UNION " 来实现full join 功能，union 连接左右外连接。

 

  

用心对待，相信美好东西会如期归来。ღ( ´･ᴗ･` )比