多表关联查询

本文主要是介绍多表关联查询，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

  1 连接种类
  2 INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
  3 LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
  4 RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录
  5 OUT JOIN 全连接 将左边表数据和右边表数据全部匹配出来
  6 ---------------------------------------------------------------------------------------
  7 ---------------------------------------------------------------------------------------
  8 
  9 -- 示例
 10  SELECT * FROM tcount_tbl;
 11 +---------------+--------------+
 12 | runoob_author | runoob_count |
 13 +---------------+--------------+
 14 | 菜鸟教程  | 10           |
 15 | RUNOOB.COM    | 20           |
 16 | Google        | 22           |
 17 +---------------+--------------+
 18 SELECT * from runoob_tbl;
 19 +-----------+---------------+---------------+-----------------+
 20 | runoob_id | runoob_title  | runoob_author | submission_date |
 21 +-----------+---------------+---------------+-----------------+
 22 | 1         | 学习 PHP    | 菜鸟教程  | 2017-04-12      |
 23 | 2         | 学习 MySQL  | 菜鸟教程  | 2017-04-12      |
 24 | 3         | 学习 Java   | RUNOOB.COM    | 2015-05-01      |
 25 | 4         | 学习 Python | RUNOOB.COM    | 2016-03-06      |
 26 | 5         | 学习 C      | FK            | 2017-04-05      |
 27 +-----------+---------------+---------------+-----------------+
 28 
 29 ---------------------------------------------------------------------------------------
 30 ---------------------------------------------------------------------------------------
 31 
 32 INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录
 33 SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a 
 34 INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
 35 +-------------+-----------------+----------------+
 36 | a.runoob_id | a.runoob_author | b.runoob_count |
 37 +-------------+-----------------+----------------+
 38 | 1           | 菜鸟教程    | 10             |
 39 | 2           | 菜鸟教程    | 10             |
 40 | 3           | RUNOOB.COM      | 20             |
 41 | 4           | RUNOOB.COM      | 20             |
 42 +-------------+-----------------+----------------+
 43 -- 也可以这样使用
 44 SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a, tcount_tbl b WHERE a.runoob_author = b.runoob_author;
 45 +-------------+-----------------+----------------+
 46 | a.runoob_id | a.runoob_author | b.runoob_count |
 47 +-------------+-----------------+----------------+
 48 | 1           | 菜鸟教程    | 10             |
 49 | 2           | 菜鸟教程    | 10             |
 50 | 3           | RUNOOB.COM      | 20             |
 51 | 4           | RUNOOB.COM      | 20             |
 52 +-------------+-----------------+----------------+
 53 ---------------------------------------------------------------------------------------
 54 ---------------------------------------------------------------------------------------
 55 
 56 LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
 57  SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
 58 +-------------+-----------------+----------------+
 59 | a.runoob_id | a.runoob_author | b.runoob_count |
 60 +-------------+-----------------+----------------+
 61 | 1           | 菜鸟教程    | 10             |
 62 | 2           | 菜鸟教程    | 10             |
 63 | 3           | RUNOOB.COM      | 20             |
 64 | 4           | RUNOOB.COM      | 20             |
 65 | 5           | FK              | NULL           |
 66 +-------------+-----------------+----------------+
 67 
 68 -- 也可以这样 
 69 SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a, tcount_tbl b WHERE a.runoob_author = b.runoob_author(+);  （+在那边就显示那边的null）
 70 +-------------+-----------------+----------------+
 71 | a.runoob_id | a.runoob_author | b.runoob_count |
 72 +-------------+-----------------+----------------+
 73 | 1           | 菜鸟教程    | 10             |
 74 | 2           | 菜鸟教程    | 10             |
 75 | 3           | RUNOOB.COM      | 20             |
 76 | 4           | RUNOOB.COM      | 20             |
 77 | 5           | FK              | NULL           |
 78 +-------------+-----------------+----------------+
 79 ---------------------------------------------------------------------------------------
 80 ---------------------------------------------------------------------------------------
 81 
 82 
 83 RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录
 84 
 85 RIGHT JOIN
 86  SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a RIGHT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
 87 +-------------+-----------------+----------------+
 88 | a.runoob_id | a.runoob_author | b.runoob_count |
 89 +-------------+-----------------+----------------+
 90 | 1           | 菜鸟教程    | 10             |
 91 | 2           | 菜鸟教程    | 10             |
 92 | 3           | RUNOOB.COM      | 20             |
 93 | 4           | RUNOOB.COM      | 20             |
 94 | NULL        | NULL            | 22             |
 95 +-------------+-----------------+----------------+
 96  -- 也可以这样 
 97  SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a , tcount_tbl b ON a.runoob_author(+) = b.runoob_author;
 98 +-------------+-----------------+----------------+
 99 | a.runoob_id | a.runoob_author | b.runoob_count |
100 +-------------+-----------------+----------------+
101 | 1           | 菜鸟教程    | 10             |
102 | 2           | 菜鸟教程    | 10             |
103 | 3           | RUNOOB.COM      | 20             |
104 | 4           | RUNOOB.COM      | 20             |
105 | NULL        | NULL            | 22             |
106 +-------------+-----------------+----------------+
107 ---------------------------------------------------------------------------------------
108 ---------------------------------------------------------------------------------------
109 
110 OUT JOIN 全连接
111  SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a OUT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
112 +-------------+-----------------+----------------+
113 | a.runoob_id | a.runoob_author | b.runoob_count |
114 +-------------+-----------------+----------------+
115 | 1           | 菜鸟教程    | 10             |
116 | 2           | 菜鸟教程    | 10             |
117 | 3           | RUNOOB.COM      | 20             |
118 | 4           | RUNOOB.COM      | 20             |
119 | 5           | FK              | NULL           |
120 | NULL        | NULL            | 22             |
121 +-------------+-----------------+----------------+
122 ---------------------------------------------------------------------------------------
123 ---------------------------------------------------------------------------------------

这篇关于多表关联查询的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！