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.字段号