mysql左连和内连_mysql左连接,右连接,内连,全连

mysql> #首先建立两张表A,B，查看下表结构

mysql> desc A;

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

| Field | Type        | Null | Key | Default | Extra |

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

| a1    | int(11)     | NO   | PRI | NULL    |       |

| a2    | varchar(22) | YES  |     | NULL    |       |

| a3    | int(11)     | YES  |     | NULL    |       |

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

3 rows in set (0.00 sec)

mysql> desc B;

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

| Field | Type        | Null | Key | Default | Extra |

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

| b1    | int(11)     | NO   | PRI | NULL    |       |

| b2    | varchar(22) | YES  |     | NULL    |       |

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

2 rows in set (0.00 sec)

mysql> #查询下A表和B表的数据

mysql> select * from A;

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

| a1 | a2   | a3   |

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

|  1 | math |   95 |

|  2 | c    |   90 |

|  3 | java |   80 |

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

3 rows in set (0.00 sec)

mysql> select * from B;

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

| b1 | b2       |

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

|  1 | zhangsan |

|  2 | lisi     |

|  4 | wangwu   |

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

3 rows in set (0.00 sec)

mysql> #接下来 演示左连接查询

mysql> select A.* ,B.* from A left outer join B on(A.a1=B.b1);

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

| a1 | a2   | a3   | b1   | b2       |

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

|  1 | math |   95 |    1 | zhangsan |

|  2 | c    |   90 |    2 | lisi     |

|  3 | java |   80 | NULL | NULL     |

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

3 rows in set (0.00 sec)

mysql> #下面是右连接查询

mysql> select A.* ,B.* from A right outer join B on(A.a1=B.b1);

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

| a1   | a2   | a3   | b1 | b2       |

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

|    1 | math |   95 |  1 | zhangsan |

|    2 | c    |   90 |  2 | lisi     |

| NULL | NULL | NULL |  4 | wangwu   |

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

3 rows in set (0.00 sec)

mysql> #下面是内连查询

mysql> select A.*,B.* from A inner join B on(A.a1=B.b1);

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

| a1 | a2   | a3   | b1 | b2       |

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

|  1 | math |   95 |  1 | zhangsan |

|  2 | c    |   90 |  2 | lisi     |

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

2 rows in set (0.00 sec)

mysql> #因为mysql里没有full outer join,因此用union关键字实现全连接查询

mysql> select A.* ,B.* from A left outer join B on(A.a1=B.b1) union select A.* ,B.* from A right outer join B on(A.a1=B.b1);

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

| a1   | a2   | a3   | b1   | b2       |

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

|    1 | math |   95 |    1 | zhangsan |

|    2 | c    |   90 |    2 | lisi     |

|    3 | java |   80 | NULL | NULL     |

| NULL | NULL | NULL |    4 | wangwu   |

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

4 rows in set (0.00 sec)

mysql> #演示完毕

mysql> notee;