MySql 连接查询

墨蓝 2022-03-25 08:10 295阅读 0赞

连接包括内连接和外连接。

通过连接运算符可以实现多个表查询。

1 内连接查询

mysql> select * from students;
    +----+--------+-------+------+------+
    | id | number | name  | sex  | age  |
    +----+--------+-------+------+------+
    |  1 |   1111 | LiLy  | W    |   12 |
    |  2 |   1112 | Lucy  | W    |   11 |
    |  3 |   1113 | LiLei | M    |   13 |
    |  4 |   1114 | Bird  | NULL |   33 |
    +----+--------+-------+------+------+
    4 rows in set (0.00 sec)
    
    mysql> select * from scores;
    +----+--------+---------+---------+
    | id | number | Chinese | English |
    +----+--------+---------+---------+
    |  1 |   1111 |      99 |      87 |
    |  2 |   1112 |      66 |      87 |
    |  3 |   1113 |      55 |     100 |
    |  4 |   1116 |      55 |      77 |
    +----+--------+---------+---------+
    4 rows in set (0.00 sec)
    
    mysql> select name,age,scores.Chinese,scores.English from students inner join scores on students.number=scores.number;
    +-------+------+---------+---------+
    | name  | age  | Chinese | English |
    +-------+------+---------+---------+
    | LiLy  |   12 |      99 |      87 |
    | Lucy  |   11 |      66 |      87 |
    | LiLei |   13 |      55 |     100 |
    +-------+------+---------+---------+
    3 rows in set (0.00 sec)

2 外连接查询

外连接又分为左外连接和右外连接。

Left Join： 会返还左边表中所有数据和 与右表中与连接字段匹配的数据。  Right Join与之相似。

2.1 左连接示例

mysql> select name,age,scores.Chinese,scores.English from students left outer join scores on students.number=scores.number;
    +-------+------+---------+---------+
    | name  | age  | Chinese | English |
    +-------+------+---------+---------+
    | LiLy  |   12 |      99 |      87 |
    | Lucy  |   11 |      66 |      87 |
    | LiLei |   13 |      55 |     100 |
    | Bird  |   33 |    NULL |    NULL |
    +-------+------+---------+---------+
    4 rows in set (0.00 sec)

2.2 右连接示例

mysql> select name,age,scores.Chinese from students right outer join scores on students.number=scores.number;
    +-------+------+---------+
    | name  | age  | Chinese |
    +-------+------+---------+
    | LiLy  |   12 |      99 |
    | Lucy  |   11 |      66 |
    | LiLei |   13 |      55 |
    | NULL  | NULL |      55 |
    +-------+------+---------+
    4 rows in set (0.00 sec)