MySQL的连接查询分为内连接查询（INNER JOIN）、外连接查询（OUTER JOIN）和复合连接查询，其中外连接查询又包含左连接查询（LEFT JOIN）和右连接查询（RIGHT JOIN）。
mysql> use learning;
Database changed
mysql> show tables;
+--------------------+
| Tables_in_learning |
+--------------------+
| houses |
| housesview |
| people |
| people2 |
+--------------------+
4 rows in set (0.01 sec)
mysql> select * from houses;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲 | 天河 | 1997 |
| 甲 | 天河 | 1998 |
| 乙 | 番禺 | 1998 |
| 丙 | 番禺 | 1998 |
| 丙 | 天河 | 1999 |
| 丁 | 天河 | 2000 |
+------+----------------+-----------------+
6 rows in set (0.00 sec)
mysql> select * from people;
+------+------+
| name | age |
+------+------+
| 甲 | 29 |
| 乙 | 35 |
+------+------+
2 rows in set (0.00 sec)
mysql> select * from people2;
+------+------+
| name | age |
+------+------+
| jia | 29 |
| yi | 35 |
+------+------+
2 rows in set (0.00 sec)
mysql> select a.name name1,a.house_location house_location3, a.purchasing_year
购买年份,b.name name45,b.age from houses a
-> right join people2 b
-> on a.name=b.name;
+-------+-----------------+--------------+--------+------+
| name1 | house_location3 | 购买年份 | name45 | age |
+-------+-----------------+--------------+--------+------+
| NULL | NULL | NULL | jia | 29 |
| NULL | NULL | NULL | yi | 35 |
+-------+-----------------+--------------+--------+------+
2 rows in set (0.00 sec)
mysql> create table housing_price (house_location varchar(3) not null, price int
(3) not null);
Query OK, 0 rows affected (0.07 sec)
mysql> desc housing_price;
+----------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------+------+-----+---------+-------+
| house_location | varchar(3) | NO | | NULL | |
| price | int(3) | NO | | NULL | |
+----------------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> insert into housing_price values('天河','500');
Query OK, 1 row affected (0.01 sec)
mysql> insert into housing_price values('番禺','400');
Query OK, 1 row affected (0.00 sec)
mysql> select * from housing_price;
+----------------+-------+
| house_location | price |
+----------------+-------+
| 天河 | 500 |
| 番禺 | 400 |
+----------------+-------+
2 rows in set (0.00 sec)
mysql> show tables;
+--------------------+
| Tables_in_learning |
+--------------------+
| houses |
| housesview |
| housing_price |
| people |
| people2 |
+--------------------+
5 rows in set (0.00 sec)
mysql> alter table housing_price rename to housingprice;
Query OK, 0 rows affected (0.04 sec)
mysql> show tables;
+--------------------+
| Tables_in_learning |
+--------------------+
| houses |
| housesview |
| housingprice |
| people |
| people2 |
+--------------------+
5 rows in set (0.00 sec)
mysql> select * from houses;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲 | 天河 | 1997 |
| 甲 | 天河 | 1998 |
| 乙 | 番禺 | 1998 |
| 丙 | 番禺 | 1998 |
| 丙 | 天河 | 1999 |
| 丁 | 天河 | 2000 |
+------+----------------+-----------------+
6 rows in set (0.00 sec)
mysql> select * from people;
+------+------+
| name | age |
+------+------+
| 甲 | 29 |
| 乙 | 35 |
+------+------+
2 rows in set (0.00 sec)
mysql> select * from people2;
+------+------+
| name | age |
+------+------+
| jia | 29 |
| yi | 35 |
+------+------+
2 rows in set (0.00 sec)
mysql> select * from housingprice;
+----------------+-------+
| house_location | price |
+----------------+-------+
| 天河 | 500 |
| 番禺 | 400 |
+----------------+-------+
2 rows in set (0.00 sec)
mysql> select a.name,a.house_location,a.purchasing_year,b.age from houses a
-> inner join people b
-> on a.name=b.name;
+------+----------------+-----------------+------+
| name | house_location | purchasing_year | age |
+------+----------------+-----------------+------+
| 甲 | 天河 | 1997 | 29 |
| 甲 | 天河 | 1998 | 29 |
| 乙 | 番禺 | 1998 | 35 |
+------+----------------+-----------------+------+
3 rows in set (0.00 sec)
mysql> select a.name,a.house_location,a.purchasing_year,b.age from houses a
-> join people2 b
-> on a.name=b.name;
Empty set (0.00 sec)
mysql> select a.name,a.house_location,a.purchasing_year,b.age from houses a
-> left join people b
-> on a.name=b.name;
+------+----------------+-----------------+------+
| name | house_location | purchasing_year | age |
+------+----------------+-----------------+------+
| 甲 | 天河 | 1997 | 29 |
| 甲 | 天河 | 1998 | 29 |
| 乙 | 番禺 | 1998 | 35 |
| 丙 | 番禺 | 1998 | NULL |
| 丙 | 天河 | 1999 | NULL |
| 丁 | 天河 | 2000 | NULL |
+------+----------------+-----------------+------+
6 rows in set (0.00 sec)
mysql> select a.name,a.house_location,a.purchasing_year,b.age from people b
-> left join houses a
-> on a.name=b.name;
+------+----------------+-----------------+------+
| name | house_location | purchasing_year | age |
+------+----------------+-----------------+------+
| 甲 | 天河 | 1997 | 29 |
| 甲 | 天河 | 1998 | 29 |
| 乙 | 番禺 | 1998 | 35 |
+------+----------------+-----------------+------+
3 rows in set (0.00 sec)
mysql> select a.name,a.house_location,a.purchasing_year,b.age from houses a
-> left join people b
-> on a.name=b.name and b.name='乙';
+------+----------------+-----------------+------+
| name | house_location | purchasing_year | age |
+------+----------------+-----------------+------+
| 乙 | 番禺 | 1998 | 35 |
| 甲 | 天河 | 1997 | NULL |
| 甲 | 天河 | 1998 | NULL |
| 丙 | 番禺 | 1998 | NULL |
| 丙 | 天河 | 1999 | NULL |
| 丁 | 天河 | 2000 | NULL |
+------+----------------+-----------------+------+
6 rows in set (0.00 sec)
mysql> select a.name,a.house_location,a.purchasing_year,b.age from houses a
-> left join people b
-> on a.name=b.name where b.name='乙';
+------+----------------+-----------------+------+
| name | house_location | purchasing_year | age |
+------+----------------+-----------------+------+
| 乙 | 番禺 | 1998 | 35 |
+------+----------------+-----------------+------+
1 row in set (0.00 sec)
mysql> insert into people values('戊','45');
Query OK, 1 row affected (0.00 sec)
mysql> select * from people;
+------+------+
| name | age |
+------+------+
| 甲 | 29 |
| 乙 | 35 |
| 戊 | 45 |
+------+------+
3 rows in set (0.00 sec)
mysql> select a.name,a.house_location,a.purchasing_year,b.age from houses a
-> right join people b
-> on a.name=b.name;
+------+----------------+-----------------+------+
| name | house_location | purchasing_year | age |
+------+----------------+-----------------+------+
| 甲 | 天河 | 1997 | 29 |
| 甲 | 天河 | 1998 | 29 |
| 乙 | 番禺 | 1998 | 35 |
| NULL | NULL | NULL | 45 |
+------+----------------+-----------------+------+
4 rows in set (0.00 sec)
mysql> select a.name 姓名,a.house_location,a.purchasing_year 购买年份,b.age 年龄
from houses a
-> right join people2 b
-> on a.name=b.name;
+--------+----------------+--------------+--------+
| 姓名 | house_location | 购买年份 | 年龄 |
+--------+----------------+--------------+--------+
| NULL | NULL | NULL | 29 |
| NULL | NULL | NULL | 35 |
+--------+----------------+--------------+--------+
2 rows in set (0.00 sec)
mysql> select a.name,a.house_location,a.purchasing_year,b.age,c.price from house
s a
-> inner join people b
-> right join housingprice c
-> on a.name=b.name
-> and a.house_location=c.house_location;
+------+----------------+-----------------+------+-------+
| name | house_location | purchasing_year | age | price |
+------+----------------+-----------------+------+-------+
| 甲 | 天河 | 1997 | 29 | 500 |
| 甲 | 天河 | 1998 | 29 | 500 |
| 乙 | 番禺 | 1998 | 35 | 400 |
+------+----------------+-----------------+------+-------+
3 rows in set (0.00 sec)
mysql>