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;