Mysql连接查询福
连接查询：将多张表(>=2)进行记录的连接(按照某个指定的条件进行数据拼接)。
连接查询的意义: 在用户查看数据的时候,需要显示的数据来自多张表.
连接查询的分类: 交叉连接、内连接、外连接
1、交叉连接
1.1、交叉连接：
交叉连接: cross join, 从一张表中循环取出每一条记录, 每条记录都去另外一张表进行匹配: 匹配一定保留(没有条件匹配), 而连接本身字段就会增加(保留),最终形成的结果叫做: 笛卡尔积。基本用到的很少。
mysql> select * from tb_goods;
+
| id | name | parent_id |
+
| 1 | 家用电器 | 0 |
| 2 | 大家电 | 1 |
| 3 | 电脑 | 2 |
| 4 | 铅笔 | 0 |
| 5 | 耳机 | 4 |
| 6 | 可乐 | 3 |
+
6 rows in set (0.00 sec)
mysql> select * from ying;
+
| id | age |
+
| 0000000032 | 0 |
| 0000000444 | 0 |
| 0000333333 | 0 |
| 0000000033 | 3 |
+
4 rows in set (0.00 sec)
mysql> select * from tb_goods cross join ying;（在MySQL中cross join和inner join其实是一样的，所以select * from tb_goods inner join ying也是同样的效果）
+
| id | name | parent_id | id | age |
+
| 1 | 家用电器 | 0 | 0000000032 | 0 |
| 1 | 家用电器 | 0 | 0000000444 | 0 |
| 1 | 家用电器 | 0 | 0000333333 | 0 |
| 1 | 家用电器 | 0 | 0000000033 | 3 |
| 2 | 大家电 | 1 | 0000000032 | 0 |
| 2 | 大家电 | 1 | 0000000444 | 0 |
| 2 | 大家电 | 1 | 0000333333 | 0 |
| 2 | 大家电 | 1 | 0000000033 | 3 |
| 3 | 电脑 | 2 | 0000000032 | 0 |
| 3 | 电脑 | 2 | 0000000444 | 0 |
| 3 | 电脑 | 2 | 0000333333 | 0 |
| 3 | 电脑 | 2 | 0000000033 | 3 |
| 4 | 铅笔 | 0 | 0000000032 | 0 |
| 4 | 铅笔 | 0 | 0000000444 | 0 |
| 4 | 铅笔 | 0 | 0000333333 | 0 |
| 4 | 铅笔 | 0 | 0000000033 | 3 |
| 5 | 耳机 | 4 | 0000000032 | 0 |
| 5 | 耳机 | 4 | 0000000444 | 0 |
| 5 | 耳机 | 4 | 0000333333 | 0 |
| 5 | 耳机 | 4 | 0000000033 | 3 |
| 6 | 可乐 | 3 | 0000000032 | 0 |
| 6 | 可乐 | 3 | 0000000444 | 0 |
| 6 | 可乐 | 3 | 0000333333 | 0 |
| 6 | 可乐 | 3 | 0000000033 | 3 |
+
24 rows in set (0.00 sec)
2、内连接
内连接: [inner] join, 从左表中取出每一条记录,去右表中与所有的记录进行匹配: 匹配必须是某个条件在左表中与右表中相同最终才会保留结果,否则不保留.
基本语法：select * from tb_name1 inner join tb_name2 on tb_name.id = tb_name2.id； on表示连接条件: 条件字段就是代表相同的业务含义,如果没有on条件就是笛卡尔乘积，on可以使用where来代替，但是这样效率会比较低
mysql> select * from tt1;
+
| id | name | gender |
+
| 1 | xiong | 0 |
| 2 | ying | 0 |
| 3 | cai | 0 |
| 4 | zhang | 0 |
| 5 | li | 1 |
| 6 | wang | 1 |
+
6 rows in set (0.00 sec)
mysql> select * from tt2;
+
| id | age | name | gender |
+
| 1 | 23 | a | 1 |
| 2 | 24 | b | 1 |
| 3 | 33 | c | 2 |
| 4 | 43 | d | 2 |
| 5 | 11 | d | 2 |
+
5 rows in set (0.00 sec)
mysql> select * from tt2 inner join tt1 on tt1.gender = tt2.gender;
+
| id | age | name | gender | id | name | gender |
+
| 1 | 23 | a | 1 | 5 | li | 1 |
| 2 | 24 | b | 1 | 5 | li | 1 |
| 1 | 23 | a | 1 | 6 | wang | 1 |
| 2 | 24 | b | 1 | 6 | wang | 1 |
+
4 rows in set (0.00 sec)
3、外连接:
以某张表为主,取出里面的所有记录, 然后每条与另外一张表进行连接: 不管能不能匹配上条件,最终都会保留: 能匹配,正确保留; 不能匹配,其他表的字段都置空NULL
1.3.1、左外连接 left join:以左表为主
mysql> select * from tt1;
+----+-------+--------+
| id | name | gender |
+----+-------+--------+
| 1 | xiong | 0 |
| 2 | ying | 0 |
| 3 | cai | 0 |
| 4 | zhang | 0 |
| 5 | li | 1 |
| 6 | wang | 1 |
+----+-------+--------+
6 rows in set (0.00 sec)
mysql> select * from tt2;
+----+------+------+--------+
| id | age | name | gender |
+----+------+------+--------+
| 1 | 23 | a | 1 |
| 2 | 24 | b | 1 |
| 3 | 33 | c | 2 |
| 4 | 43 | d | 2 |
| 5 | 11 | d | 2 |
+----+------+------+--------+
5 rows in set (0.00 sec)
mysql> select * from tt2 left join tt1 on tt1.gender = tt2.gender;
+----+------+------+--------+------+------+--------+
| id | age | name | gender | id | name | gender |
+----+------+------+--------+------+------+--------+
| 1 | 23 | a | 1 | 5 | li | 1 |
| 2 | 24 | b | 1 | 5 | li | 1 |
| 1 | 23 | a | 1 | 6 | wang | 1 |
| 2 | 24 | b | 1 | 6 | wang | 1 |
| 3 | 33 | c | 2 | NULL | NULL | NULL |
| 4 | 43 | d | 2 | NULL | NULL | NULL |
| 5 | 11 | d | 2 | NULL | NULL | NULL |
+----+------+------+--------+------+------+--------+
7 rows in set (0.00 sec)
3、右外连接 left join:以右表为主
mysql> select * from tt1;
+----+-------+--------+
| id | name | gender |
+----+-------+--------+
| 1 | xiong | 0 |
| 2 | ying | 0 |
| 3 | cai | 0 |
| 4 | zhang | 0 |
| 5 | li | 1 |
| 6 | wang | 1 |
+----+-------+--------+
6 rows in set (0.00 sec)
mysql> select * from tt2;
+----+------+------+--------+
| id | age | name | gender |
+----+------+------+--------+
| 1 | 23 | a | 1 |
| 2 | 24 | b | 1 |
| 3 | 33 | c | 2 |
| 4 | 43 | d | 2 |
| 5 | 11 | d | 2 |
+----+------+------+--------+
5 rows in set (0.00 sec)
mysql> select * from tt2 right join tt1 on tt1.gender = tt2.gender;
+------+------+------+--------+----+-------+--------+
| id | age | name | gender | id | name | gender |
+------+------+------+--------+----+-------+--------+
| 1 | 23 | a | 1 | 5 | li | 1 |
| 1 | 23 | a | 1 | 6 | wang | 1 |
| 2 | 24 | b | 1 | 5 | li | 1 |
| 2 | 24 | b | 1 | 6 | wang | 1 |
| NULL | NULL | NULL | NULL | 1 | xiong | 0 |
| NULL | NULL | NULL | NULL | 2 | ying | 0 |
| NULL | NULL | NULL | NULL | 3 | cai | 0 |
| NULL | NULL | NULL | NULL | 4 | zhang | 0 |
+------+------+------+--------+----+-------+--------+
8 rows in set (0.00 sec)
|