mysql 链接查询

链接查询：当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回

mysql支持三种类型的连接查询，分别为：

内连接查询：查询结果为两个表匹配到的数据，就是两个表中都有的数据的交集，显示有交集的数据。

 

右连接查询：查询结果为两个表匹配到的数据，以右表为基准匹配左表，对于匹配不到左表的数据，用null填充（对于左表中有，但是右表没有的，不显示，所以说以右为基准）实际中右连接较少用，因为使用左连接调换一下两个表的位置就可以了。

 

左连接查询：与右连接相反

 

内连接：inner join ... on

 

select ... from table1 inner join table2;

取两个表中都有的，没有的不显示，下例中，students表中的cls_id列与class中的id对应时，此条记录会显示

 

以下面两个表作为演练：

students中的class_id对应class中的id

class：

+----+--------------+

| id | name         |

+----+--------------+

|  1 | python_01期  |

|  2 | python_02期  |

|  3 | python_04期  |

+----+--------------+

 

students：

+----+-----------+------+--------+--------+--------+-----------+------+

| id | name      | age  | height | gender | cls_id | is_delete | dept |

+----+-----------+------+--------+--------+--------+-----------+------+

|  1 | 小明      |   18 | 180.00 | 女     |      1 |           | HR   |

|  2 | 小月月    |   18 | 190.00 | 女     |      2 |          | NULL |

|  3 | 彭于晏    |   29 | 185.00 | 男     |      1 |           | HR   |

|  4 | 刘德华    |   59 | 175.00 | 男     |      2 |          | NULL |

|  5 | 黄蓉      |   38 | 160.00 | 女     |      1 |           | NULL |

|  6 | 凤姐      |   28 | 150.00 | 保密   |      2 |          | HR   |

|  7 | 王祖贤    |   18 | 172.00 | 女     |      1 |          | NULL |

|  8 | 周杰伦    |   36 |   NULL | 男     |      1 |           | NULL |

|  9 | 成奎      |   27 | 181.00 | 男     |      2 |           | HR   |

| 10 | 刘亦菲    |   25 | 166.00 | 女     |      2 |           | NULL |

| 11 | 金星      |   33 | 162.00 | 中性   |      3 |          | NULL |

| 12 | 静香      |   12 | 180.00 | 女     |      4 |           | NULL |

| 13 | 郭靖      |   12 | 170.00 | 男     |      4 |           | NULL |

| 14 | 周杰      |   35 | 176.00 | 女     |      5 |           | NULL |

+----+-----------+------+--------+--------+--------+-----------+------+

 

如果只是：select * from students inner join class;  会出现以下效果：

+----+-----------+------+--------+--------+--------+-----------+------+----+--------------+

| id | name      | age  | height | gender | cls_id | is_delete | dept | id | name         |

+----+-----------+------+--------+--------+--------+-----------+------+----+--------------+

|  1 | 小明      |   18 | 180.00 | 女     |      1 |           | HR   |  1 | python_01期  |

|  1 | 小明      |   18 | 180.00 | 女     |      1 |           | HR   |  2 | python_02期  |

|  1 | 小明      |   18 | 180.00 | 女     |      1 |           | HR   |  3 | python_04期  |

|  2 | 小月月    |   18 | 190.00 | 女     |      2 |          | NULL |  1 | python_01期  |

|  2 | 小月月    |   18 | 190.00 | 女     |      2 |          | NULL |  2 | python_02期  |

|  2 | 小月月    |   18 | 190.00 | 女     |      2 |          | NULL |  3 | python_04期  |

|  3 | 彭于晏    |   29 | 185.00 | 男     |      1 |           | HR   |  1 | python_01期  |

|  3 | 彭于晏    |   29 | 185.00 | 男     |      1 |           | HR   |  2 | python_02期  |

|  3 | 彭于晏    |   29 | 185.00 | 男     |      1 |           | HR   |  3 | python_04期  |

 

select * from table1 inner join table2 on ...      #on 后面加条件：

显示cls_id和class id相对应的项（交集）：

select * from students inner join class on students.cls_id=class.id;

 

 

+----+-----------+------+--------+--------+--------+-----------+----+--------------+

 

| id | name      | age  | height | gender | cls_id | is_delete | id | name         |

 

+----+-----------+------+--------+--------+--------+-----------+----+--------------+

 

|  1 | 小明      |   18 | 180.00 | 女     |      1 |           |  1 | python_01期  |

 

|  2 | 小月月    |   18 | 190.00 | 女     |      2 |          |  2 | python_02期  |

 

|  3 | 彭于晏    |   29 | 185.00 | 男     |      1 |           |  1 | python_01期  |

 

|  4 | 刘德华    |   59 | 175.00 | 男     |      2 |          |  2 | python_02期  |

 

|  5 | 黄蓉      |   38 | 160.00 | 女     |      1 |           |  1 | python_01期  |

 

左边显示列students表中的列，右面显示了class表中的列，cls_id和class表中的id是交集，所以是重复的。

可以选择显示列：

 

 

mysql> select students.*, class.name from students inner join class on students.cls_id=class.id;

 

+----+-----------+------+--------+--------+--------+-----------+--------------+

 

| id | name      | age  | height | gender | cls_id | is_delete | name         |

 

+----+-----------+------+--------+--------+--------+-----------+--------------+

 

|  1 | 小明      |   18 | 180.00 | 女     |      1 |           | python_01期  |

 

|  2 | 小月月    |   18 | 190.00 | 女     |      2 |          | python_02期  |

 

|  3 | 彭于晏    |   29 | 185.00 | 男     |      1 |           | python_01期  |

 

|  4 | 刘德华    |   59 | 175.00 | 男     |      2 |          | python_02期  |

 

|  5 | 黄蓉      |   38 | 160.00 | 女     |      1 |           | python_01期  |

 

|  6 | 凤姐      |   28 | 150.00 | 保密   |      2 |          | python_02期  |

 

 

 

 

可以使用别名：

 

 

select s.name,c.name from students as s inner join class as c on s.cls_id=c.id;

 

查询结果如下：

 

 

+-----------+--------------+

 

| name      | name         |

 

+-----------+--------------+

 

| 小明      | python_01期  |

 

| 小月月    | python_02期  |

 

| 彭于晏    | python_01期  |

 

| 刘德华    | python_02期  |

 

| 黄蓉      | python_01期  |

 

| 凤姐      | python_02期  |

 

| 王祖贤    | python_01期  |

 

 

左连接：left join

inner join 是对于on后条件不匹配的数据不显示，只显示匹配到的数据

left join  是对于匹配不到的数据，以左表students中的cls_id去匹配右表class中的id，匹配到显示，匹配不到填充null：

select s.*,c.name from students as s left join class as c on s.cls_id=c.id;

结果如下：

+----+-----------+------+--------+--------+--------+-----------+--------------+

| id | name      | age  | height | gender | cls_id | is_delete | name         |

+----+-----------+------+--------+--------+--------+-----------+--------------+

|  1 | 小明      |   18 | 180.00 | 女     |      1 |           | python_01期  |

|  3 | 彭于晏    |   29 | 185.00 | 男     |      1 |           | python_01期  |

|  5 | 黄蓉      |   38 | 160.00 | 女     |      1 |           | python_01期  |

|  7 | 王祖贤    |   18 | 172.00 | 女     |      1 |          | python_01期  |

|  8 | 周杰伦    |   36 |   NULL | 男     |      1 |           | python_01期  |

|  2 | 小月月    |   18 | 190.00 | 女     |      2 |          | python_02期  |

|  4 | 刘德华    |   59 | 175.00 | 男     |      2 |          | python_02期  |

|  6 | 凤姐      |   28 | 150.00 | 保密   |      2 |          | python_02期  |

|  9 | 成奎      |   27 | 181.00 | 男     |      2 |           | python_02期  |

| 10 | 刘亦菲    |   25 | 166.00 | 女     |      2 |           | python_02期  |

| 11 | 金星      |   33 | 162.00 | 中性   |      3 |          | python_04期  |

| 12 | 静香      |   12 | 180.00 | 女     |      4 |           | NULL         |

| 13 | 郭靖      |   12 | 170.00 | 男     |      4 |           | NULL         |

| 14 | 周杰      |   35 | 176.00 | 女     |      5 |           | NULL         |

+----+-----------+------+--------+--------+--------+-----------+--------------+

 

显示班级、姓名，并按班级名排序，班级名相同时，按id排序：

 

 

select c.name,s.* from class as c right join students as s on c.id=s.cls_id order by c.name,id;

 

 

查询没有对应的数据，就是class.id为null的数据：

select c.name,s.* from students as s left join class as c on s.cls_id=c.id having c.name is null;

结果如下：

 

 

+------+----+--------+------+--------+--------+--------+-----------+

 

| name | id | name   | age  | height | gender | cls_id | is_delete |

 

+------+----+--------+------+--------+--------+--------+-----------+

 

| NULL | 12 | 静香   |   12 | 180.00 | 女     |      4 |           |

 

| NULL | 13 | 郭靖   |   12 | 170.00 | 男     |      4 |           |