mysql join 三个表联接查询实例详解

首先创建三个表；分别为Books表、Authors表和AuthorBook表。 

mysql创建表代码如下：

mysql> CREATE TABLE Books(
    ->    BookID SMALLINT NOT NULL PRIMARY KEY,
    ->    Book  VARCHAR(60) NOT NULL,
    ->    Copyright YEAR NOT NULL
    -> )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE Authors(
    ->    AuthID SMALLINT NOT NULL PRIMARY KEY,
    ->    AuthFN VARCHAR(20),
    ->    AuthMN VARCHAR(20),
    ->    AuthLN VARCHAR(20)
    -> )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE AuthorBook(
    ->    AuthID SMALLINT NOT NULL,
    ->    BookID SMALLINT NOT NULL,
    ->    PRIMARY KEY (AuthID, BookID),
    ->    FOREIGN KEY (AuthID) REFERENCES Authors (AuthID),
    ->    FOREIGN KEY (BookID) REFERENCES Books (BookID)
    -> )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.03 sec)

然后分别向这三个表中插入一些数据：

mysql> INSERT INTO Books VALUES (12786, \'Java\',           1934),
    ->                          (13331, \'MySQL\',          1919),
    ->                          (14356, \'PHP\',            1966),
    ->                          (15729, \'PERL\',           1932),
    ->                          (16284, \'Oracle\',         1996),
    ->                          (17695, \'Pl/SQL\',         1980),
    ->                          (19264, \' \',     1992),
    ->                          (19354, \'www.java2s.com\', 1993);
Query OK, 8 rows affected (0.03 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> INSERT INTO Authors VALUES (1006, \'H\', \'S.\', \'T\'),
    ->                            (1007, \'J\', \'C\',  \'O\'),
    ->                            (1008, \'B\', NULL, \'E\'),
    ->                            (1009, \'R\', \'M\',  \'R\'),
    ->                            (1010, \'J\', \'K\',  \'T\'),
    ->                            (1011, \'J\', \'G.\', \'N\'),
    ->                            (1012, \'A\', NULL, \'P\'),
    ->                            (1013, \'A\', NULL, \'W\'),
    ->                            (1014, \'N\', NULL, \'A\');
Query OK, 9 rows affected (0.03 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> INSERT INTO AuthorBook VALUES (1006, 14356),
    ->                               (1008, 15729),
    ->                               (1009, 12786),
    ->                               (1010, 17695),
    ->                               (1011, 15729),
    ->                               (1012, 19264),
    ->                               (1012, 19354),
    ->                               (1014, 16284);
Query OK, 8 rows affected (0.05 sec)
Records: 8  Duplicates: 0  Warnings: 0

现在开始对这三个表进行联合查询操作。

需求：获取所有书籍的名称、作者和发布时间。

下面请看mysql使用join如何实现：

mysql> SELECT Book , Copyright, CONCAT_WS(\' \', AuthFN, AuthMN, AuthLN) AS Author
    -> FROM Books JOIN AuthorBook USING (BookID)
    ->    JOIN Authors USING (AuthID)
    -> WHERE Copyright<1980
    -> ORDER BY Book ;
+-----------+-----------+--------+
| Book  | Copyright | Author |
+-----------+-----------+--------+
| Java      |      1934 | R M R  |
| PERL      |      1932 | B E    |
| PERL      |      1932 | J G. N |
| PHP       |      1966 | H S. T |
+-----------+-----------+--------+
4 rows in set (0.00 sec)

大家可以自己把mysql代码粘贴过去在自己的电脑上运行一下，多动手多思考应该可以理解。