（十二）连接查询

电玩女神 2022-05-08 06:52 199阅读 0赞

### 文章目录 ###

*  左右表
 *  交叉连接（cross join）
 *  内连接（\[inner\] join）
 *  外连接（outer join）
 *  自然连接（nature join）

--------------------

连接查询，将多张表进行记录的连接，按照某个指定的条件进行数据的拼接 ；

连接查询的意义，用户查看的数据，实际本质上来自多张表 ；

连接查询分类：**内连接、外连接、自然连接、交叉连接** ；

> 以内连接为例子，如果有多张表需要连接，三张表的语法：**`(表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号`** ，四张表、五张表、以此类推下去 ；

比如这样：

SELECT * FROM (( role_user as r_s JOIN `user` as u  ON r_s.user_id = u.id and u.id = '1')) JOIN role on role.id = u.id);

--------------------

# 左右表 #

连接查询 ： `join` ，使用方式：左表 `join` 右表 ；  
左表：在 `join` 关键字左边的表 ；  
右表：在 `join` 关键字右边的表 ；

--------------------

# 交叉连接（cross join） #

从左表中取出每一条记录，每条记录都去匹配右表所有的记录，字段直接保留，最终得到一个 `笛卡尔积` ；

# 基本语法
    左表 cross join 右表 ，一般简写为 左表，右表 ；
    
    # 交叉查询
    mysql> select * from my_copy_student cross join student ;  -- 简写为下面的形式
    mysql> select * from my_copy_student ,student ;
    +----+------+------+----+-------+-----+
    | id | name | sex  | id | name  | sex |
    +----+------+------+----+-------+-----+
    |  2 | dog  | male |  1 | andy  | 1   |
    |  2 | dog  | male |  2 | sas   | 1   |
    |  2 | dog  | male |  3 | white | 2   |
    | 12 | 哈哈 | 男   |  1 | andy  | 1   |
    | 12 | 哈哈 | 男   |  2 | sas   | 1   |
    | 12 | 哈哈 | 男   |  3 | white | 2   |
    | 22 | ww   | NULL |  1 | andy  | 1   |
    | 22 | ww   | NULL |  2 | sas   | 1   |
    | 22 | ww   | NULL |  3 | white | 2   |
    | 33 | haha | 男   |  1 | andy  | 1   |
    | 33 | haha | 男   |  2 | sas   | 1   |
    | 33 | haha | 男   |  3 | white | 2   |
    +----+------+------+----+-------+-----+
    12 rows in set

--------------------

# 内连接（\[inner\] join） #

从左表中取出每一条记录，去与右表中的所有记录进行匹配，必须是 ***连接条件*** 在左表和右表中相同，匹配结果才会得到保留 ；

# 语法
    # on 表示连接条件
    左表 [inner] join 右表 on 左表.字段 = 右表.字段 ； 
    
    mysql> -- 内连接
        -> select * from my_copy_student as cs inner join student as s on cs.id = s.id ;
    +----+------+------+----+------+-----+
    | id | name | sex  | id | name | sex |
    +----+------+------+----+------+-----+
    |  2 | dog  | male |  2 | sas  | 1   |
    +----+------+------+----+------+-----+
    1 row in set

**使用别名**

使用内连接，多表查询的时候，匹配出的结果，往往会出现字段名冲突的情况，我们可以使用别名，解决字段名冲突的问题，还可以使用别名代表长长的表名 ；

**连接条件中省略 表名.**

如果字段仅仅在某一张表中有，则可以不写 `表名.字段` ，直接写字段 ；系统会自动的识别出来；

但是一般都写上，不要自己给自己添堵，以后看知道那个字段是哪一张表的？

**不写连接条件**

那么查出来的结果和交叉查询一样，一个 ***笛卡尔积*** ；

**用 where 换掉 on**

可以替换掉，但是 `on` 效率高；

`where` 是先匹配再进行连接条件的判断，成功则保留匹配结果，失败则丢弃 ；

`on` 是先进行匹配条件的判断，成功了再匹配，失败则跳过 ；

--------------------

# 外连接（outer join） #

`outer join` ，以某张表为主表，取出其中所有记录，然后每条记录都有另外表的记录进行匹配，满足匹配条件，则保留该次匹配，不满足，则其他表的字段均赋值为 `null` ;

外连接分为两种：

*  `left join` 左外连接（左连接），以左表为主表 ；
 *  `right join` 右外连接（右连接），以右表为主表 ；

# 基本语法
    左表 left join/right join 右表 on 左表.字段 = 右表.字段 ； -- on 不可以省略
    
    
    mysql> select cs.*,s.id s_id,s.name s_name,s.sex s_sex from 
       my_copy_student cs right join student s 
         on cs.id = s.id ;
    +------+------+------+------+--------+-------+
    | id   | name | sex  | s_id | s_name | s_sex |
    +------+------+------+------+--------+-------+
    | NULL | NULL | NULL |    1 | andy   | 1     |
    |    2 | dog  | male |    2 | sas    | 1     |
    | NULL | NULL | NULL |    3 | white  | 2     |
    +------+------+------+------+--------+-------+
    3 rows in set
    
    
    
    mysql> select cs.*,s.id s_id,s.name s_name,s.sex s_sex from 
      student s  right join my_copy_student cs
         on cs.id = s.id ;
    +----+------+------+------+--------+-------+
    | id | name | sex  | s_id | s_name | s_sex |
    +----+------+------+------+--------+-------+
    |  2 | dog  | male |    2 | sas    | 1     |
    | 12 | 哈哈 | 男   | NULL | NULL   | NULL  |
    | 22 | ww   | NULL | NULL | NULL   | NULL  |
    | 33 | haha | 男   | NULL | NULL   | NULL  |
    +----+------+------+------+--------+-------+
    4 rows in set

左表的数据在左边，右表的数据在右边，跟左、右连接无关 ；

--------------------

# 自然连接（nature join） #

就是自动匹配，不需要我们写匹配条件，系统以 ***`同名字段`*** 名作为匹配条件，多个同名字段的时候，就多个都作为条件 ,；

自然连接分为两种

*  自然内连接 ：`左表 natural join 右表`
    
    其实跟内连接一样，就是，匹配条件是字段 ；
    
    **匹配成功的记录的相同字段会被合并 ；**
    
        mysql> select * from 
            -> my_copy_student natural join student ;
        +----+------+-----+
        | id | name | sex |
        +----+------+-----+
        |  2 | an   | 1   |
        +----+------+-----+
        1 row in set
 *  自然外连接：`左表 natural right/left join 右表`
    
    其实跟外连接一样，就是，匹配条件是字段 ；
    
        mysql> select * from 
            -> my_copy_student natural right join student ;
        +----+-------+-----+
        | id | name  | sex |
        +----+-------+-----+
        |  1 | andy  | 1   |
        |  2 | sas   | 1   |
        |  3 | white | 2   |
        |  2 | an    | 1   |
        +----+-------+-----+
    
    **模拟自然连接**
    
    我们发现自然连接和内连接、外连接很相似；
    
    其实用内连接、外连接都可以模拟自然连接，只要做到，使用同名字段、合并字段 ；
    
        左表 left/right join 右表 using(字段名)  ; -- 使用同名字段作为连接条件，自动合并字段
        
        	mysql> select * from 
        	    -> my_copy_student  right join student using(id,name,sex) ;
        	+----+-------+-----+
        	| id | name  | sex |
        	+----+-------+-----+
        	|  1 | andy  | 1   |
        	|  2 | sas   | 1   |
        	|  3 | white | 2   |
        	|  2 | an    | 1   |
        	+----+-------+-----+
        	4 rows in set

--------------------

… ℒℴѵℯ ℒℴѵℯ ℒℴѵℯ

天气阴冷，还保留着那个梦，南大mse!

– 2018年10月9日23:45:33