SQL INNER JOIN 用法解决

在表中存在至少一个匹配时，inner join 关键字返回行。

inner join 关键字语法
column_name(s)
from table_name1
inner join table_name2
on table_name1.column_name=table_name2.column_name
注释：inner join 与 join 是相同的，inner join 关键字在表中存在至少一个匹配时返回行。如果 "a" 中的行在 "b" 中没有匹配，就不会列出这些行。

多表联合查询

 

>
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.09 sec)

mysql>
mysql>
mysql> insert into books values (12786, 'java',           1934),
    ->                          (13331, 'mysql',          1919),
    ->                          (14356, '',            1966),
    ->                          (15729, 'perl',           1932),
    ->                          (16284, 'oracle',         1996),
    ->                          (17695, 'pl/sql',         1980),
    ->                          (19264, '',     1992),
    ->                          (19354, 'www.zhutiai.com', 1993);
query ok, 8 rows affected (0.05 sec)
records: 8  duplicates: 0  warnings: 0

mysql>
mysql>
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.05 sec)

mysql>
mysql>
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>
mysql>
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.06 sec)

mysql>
mysql>
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>
mysql>
mysql> select * from authors;
+--------+--------+--------+--------+
| authid | authfn | authmn | authln |
+--------+--------+--------+--------+
|   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      |
+--------+--------+--------+--------+
9 rows in set (0.00 sec)

mysql> select * from books;
+--------+----------------+-----------+
| bookid | booktitle      | copyright |
+--------+----------------+-----------+
|  12786 | java           |      1934 |
|  13331 | mysql          |      1919 |
|  14356 | php            |      1966 |
|  15729 | perl           |      1932 |
|  16284 | oracle         |      1996 |
|  17695 | pl/sql         |      1980 |
|  19264 | javascript     |      1992 |
|  19354 | |      1993 |
+--------+----------------+-----------+
8 rows in set (0.00 sec)

mysql> select * from authorbook;
+--------+--------+
| authid | bookid |
+--------+--------+
|   1009 |  12786 |
|   1006 |  14356 |
|   1008 |  15729 |
|   1011 |  15729 |
|   1014 |  16284 |
|   1010 |  17695 |
|   1012 |  19264 |
|   1012 |  19354 |
+--------+--------+
8 rows in set (0.00 sec)

mysql>
mysql>
mysql> select booktitle, authid from books inner join authorbook;
+----------------+--------+
| booktitle      | authid |
+----------------+--------+
| java           |   1006 |
| mysql          |   1006 |
| php            |   1006 |
| perl           |   1006 |
| oracle         |   1006 |
| pl/sql         |   1006 |
| javascript     |   1006 |
| |   1006 |
| java           |   1008 |
| mysql          |   1008 |
| php            |   1008 |
| perl           |   1008 |
| oracle         |   1008 |
| pl/sql         |   1008 |
| javascript     |   1008 |
| |   1008 |
| java           |   1009 |
| mysql          |   1009 |
| php            |   1009 |
| perl           |   1009 |
| oracle         |   1009 |
| pl/sql         |   1009 |
| javascript     |   1009 |
| |   1009 |
| java           |   1010 |
| mysql          |   1010 |
| php            |   1010 |
| perl           |   1010 |
| oracle         |   1010 |
| pl/sql         |   1010 |
| javascript     |   1010 |
| |   1010 |
| java           |   1011 |
| mysql          |   1011 |
| php            |   1011 |
| perl           |   1011 |
| oracle         |   1011 |
| pl/sql         |   1011 |
| javascript     |   1011 |
| |   1011 |
| java           |   1012 |
| mysql          |   1012 |
| php            |   1012 |
| perl           |   1012 |
| oracle         |   1012 |
| pl/sql         |   1012 |
| javascript     |   1012 |
| |   1012 |
| java           |   1012 |
| mysql          |   1012 |
| php            |   1012 |
| perl           |   1012 |
| oracle         |   1012 |
| pl/sql         |   1012 |
| javascript     |   1012 |
| |   1012 |
| java           |   1014 |
| mysql          |   1014 |
| php            |   1014 |
| perl           |   1014 |
| oracle         |   1014 |
| pl/sql         |   1014 |
| javascript     |   1014 |
| |   1014 |
+----------------+--------+
64 rows in set (0.00 sec)

mysql>
mysql> drop table authorbook;
query ok, 0 rows affected (0.02 sec)

mysql> drop table books;
query ok, 0 rows affected (0.06 sec)

mysql> drop table authors;
query ok, 0 rows affected (0.03 sec)

二个表连接

mysql> select employee.first_name, job.title, duty.task
    -> from employee, job, duty
    -> where (employee.id = job.id and employee.id = duty.id);
+------------+------------+-----------+
| first_name | title      | task      |
+------------+------------+-----------+
| jason      | tester     | test      |
| alison     | accountant | calculate |
| james      | developer  | program   |
| celia      | coder      | test      |
| robert     | director   | manage    |
| linda      | mediator   | talk      |
| david      | proffessor | speak     |
| james      | programmer | shout     |
+------------+------------+-----------+
8 rows in set (0.00 sec)

mysql>
mysql>
mysql>
mysql> drop table duty;
query ok, 0 rows affected (0.00 sec)

mysql> drop table job;
query ok, 0 rows affected (0.01 sec)

mysql> drop table employee;
query ok, 0 rows affected (0.00 sec)

总结

inner join 连接两个数据表的用法：
select * from 表1 inner join 表2 on 表1.字段号=表2.字段号

inner join 连接三个数据表的用法：
select * from (表1 inner join 表2 on 表1.字段号=表2.字段号) inner join 表3 on 表1.字段号=表3.字段号

inner join 连接四个数据表的用法：
select * from ((表1 inner join 表2 on 表1.字段号=表2.字段号) inner join 表3 on 表1.字段号=表3.字段号) inner join 表4 on member.字段号=表4.字段号

inner join 连接五个数据表的用法：
select * from (((表1 inner join 表2 on 表1.字段号=表2.字段号) inner join 表3 on 表1.字段号=表3.字段号) inner join 表4 on member.字段号=表4.字段号) inner join 表5 on member.字段号=表5.字段号