多表查询

 

多表查询就是从多张表中查找所需数据，并且整合在一起显示出来。

 

多表查询有多种实现方式。例如：

内连接

外连接

子查询

 

内连接查询

 

简单写法

内连接查询使用比较运算符进行表间列数据的比较操作。并输出符合条件的结果。

 

mysql> desc City;

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

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

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

| ID          | int(11)  | NO  | PRI | NULL    | auto_increment |

| Name        | char(35) |NO   |    |         |                |

| CountryCode | char(3)  |NO   |    |         |                |

| District    | char(20) |NO   |    |         |                |

| Population  | int(11)  | NO  |     | 0       |                |

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

5 rows in set (0.00 sec)

 

spacer.gif

 

 

mysql> select id,City.name,City.Population,LifeExpectancy fromCity,Country where id<10 and City.CountryCode=Country.Code;

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

| id | name           |Population | LifeExpectancy |

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

|  1 | Kabul          |   1780000 |           45.9 |

|  2 | Qandahar       |    237500 |           45.9 |

|  3 | Herat          |    186800 |           45.9 |

|  4 | Mazar-e-Sharif |     127800 |          45.9 |

|  5 | Amsterdam      |    731200 |           78.3 |

|  6 | Rotterdam      |    593321 |           78.3 |

|  7 | Haag           |    440900 |           78.3 |

|  8 | Utrecht        |    234323 |           78.3 |

|  9 | Eindhoven      |    201843 |           78.3 |

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

9 rows in set (0.00 sec)

 

 

 

标准写法 （迁移数据的时候比较好用，例如从MySQL迁移到oracle，如果用上面MySQL风格的语法，迁移到oracle还需要做语法调整）

 

mysql> select id,City.name,City.Population,LifeExpectancy fromCity INNER JOIN Country on id<10 and City.CountryCode=Country.Code;

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

| id | name           |Population | LifeExpectancy |

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

|  1 | Kabul          |   1780000 |           45.9 |

|  2 | Qandahar       |    237500 |           45.9 |

|  3 | Herat          |    186800 |           45.9 |

|  4 | Mazar-e-Sharif |     127800 |           45.9 |

|  5 | Amsterdam      |    731200 |           78.3 |

|  6 | Rotterdam      |    593321 |           78.3 |

|  7 | Haag           |    440900 |           78.3 |

|  8 | Utrecht        |    234323 |           78.3 |

|  9 | Eindhoven      |    201843 |           78.3 |

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

9 rows in set (0.00 sec)

 

 

 

笛卡尔积（没有where导致，导致左边的表每一条数据都会匹配右边表的每一条数据。）

 

Inner join 左边的表称之为基准表，右边的表称之为连接过来的表。

 

 

mysql> select join1.id,name,city ,unit from join1,join2;

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

| id   | name  | city    | unit  |

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

|    1 | Tube  | Shanghai | IT    |

|    2 | Kevin | Shanghai |IT    |

|    3 | Mark  | Shanghai | IT    |

|    1 | Tube  | Shanghai | IT    |

|    2 | Kevin | Shanghai |IT    |

|    3 | Mark  | Shanghai | IT    |

|    1 | Tube  | Shanghai | IT    |

|    2 | Kevin | Shanghai |IT    |

|    3 | Mark  | Shanghai | IT    |

|    1 | Tube  | Beijing | Sales |

|    2 | Kevin | Beijing  | Sales |

|    3 | Mark  | Beijing | Sales |

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

12 rows in set (0.06 sec)

 

外连接查询

 

左连接查询

 

mysql> select id,City.name,City.Population,LifeExpectancy fromCity LEFT OUTER  JOIN Country on id<10and City.CountryCode=Country.Code LIMIT 10;

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

| id | name           |Population | LifeExpectancy |

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

|  1 | Kabul          |   1780000 |           45.9 |

|  2 | Qandahar       |    237500 |           45.9 |

|  3 | Herat          |    186800 |           45.9 |

|  4 | Mazar-e-Sharif |     127800 |           45.9 |

|  5 | Amsterdam      |    731200 |           78.3 |

|  6 | Rotterdam      |    593321 |           78.3 |

|  7 | Haag           |    440900 |           78.3 |

|  8 | Utrecht        |    234323 |           78.3 |

|  9 | Eindhoven      |    201843 |           78.3 |

| 10 | Tilburg        |     193238 |           NULL |

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

10 rows in set (0.04 sec)

 

左右表连接的区别是：以谁为准。

 

mysql> select join1.id,name,city ,unit from join1 LEFT OUTER JOINjoin2 ON join1.id=join2.id;

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

| id   | name  | city    | unit |

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

|    1 | Tube  | Shanghai | IT   |

|    2 | Kevin | Shanghai |IT   |

|    3 | Mark  | Shanghai | IT   |

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

3 rows in set (0.00 sec)

 

右连接查询

mysql> select join1.id,name,city ,unit from join1 RIGHT OUTERJOIN join2 ON join1.id=join2.id;

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

| id   | name  | city    | unit  |

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

|    1 | Tube  | Shanghai | IT    |

|    2 | Kevin | Shanghai |IT    |

|    3 | Mark  | Shanghai | IT    |

| NULL | NULL  | Beijing  | Sales |

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

4 rows in set (0.00 sec)