Home >Database >Mysql Tutorial >mysql join 三个表联接查询实例详解

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

WBOY
WBOYOriginal
2016-06-01 09:56:472108browse

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

mysql创建表代码如下:

<code class="language-sql">mysql> CREATE TABLE Books(
    ->    BookID SMALLINT NOT NULL PRIMARY KEY,
    ->    BookTitle 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)</code>

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

<code class="language-sql">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, 'JavaScript',     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</code>

 

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

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

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

<code class="language-sql">mysql> SELECT BookTitle, Copyright, CONCAT_WS(' ', AuthFN, AuthMN, AuthLN) AS Author
    -> FROM Books JOIN AuthorBook USING (BookID)
    ->    JOIN Authors USING (AuthID)
    -> WHERE Copyright ORDER BY BookTitle;
+-----------+-----------+--------+
| BookTitle | 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)</code>

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

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn