（十二）连接查询

---

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

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

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

以内连接为例子，如果有多张表需要连接，三张表的语法：(表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