1. 简介

多表查询就是将多个表的数据横向联合起来。多表查询的分类有：
1）内连接
2）外链接: 左外链接，右外连接
3）交叉连接
4）自然连接

2.多表查询

2.1 内连接【inner join】

语法一：select 列名 from 表1 inner join 表2 on 表1.公共字段=表2.公共字段
语法二：selcet 列名 from 表1,表2 where 表1.公共字段=表2.公共字段

例题：

方法一：
mysql> select stuname,stusex,writtenexam,labexam from stuinfo inner join stumarks on stuinfo.stuno=stumarks.stuno;
+--------------+--------+-------------+---------+
| stuname      | stusex | writtenexam | labexam |
+--------------+--------+-------------+---------+
| 李斯文       | 女     |          80 |      58 |
| 李文才       | 男     |          50 |      90 |
| 欧阳俊雄     | 男     |          65 |      50 |
| 张秋丽       | 男     |          77 |      82 |
| 争青小子     | 男     |          56 |      48 |
+--------------+--------+-------------+---------+

方法二：
mysql> select stuname,stusex,writtenexam,labexam from stuinfo,stumarks where stuinfo.stuno=stumarks.stuno;
+--------------+--------+-------------+---------+
| stuname      | stusex | writtenexam | labexam |
+--------------+--------+-------------+---------+
| 李斯文       | 女     |          80 |      58 |
| 李文才       | 男     |          50 |      90 |
| 欧阳俊雄     | 男     |          65 |      50 |
| 张秋丽       | 男     |          77 |      82 |
| 争青小子     | 男     |          56 |      48 |
+--------------+--------+-------------+---------+

脚下留心：显示公共字段需要指定表名

不指定公共字段的表名会报错:
mysql> select stuno,stuname,stusex,writtenexam,labexam from stuinfo inner join stumarks on stuinfo.stuno=stumarks.stuno;
ERROR 1052 (23000): Column 'stuno' in field list is ambiguous

指定公共字段的表名：
mysql> select stuinfo.stuno,stuname,stusex,writtenexam,labexam from stuinfo inner join stumarks on stuinfo.stuno=stumarks.stuno;
+--------+--------------+--------+-------------+---------+
| stuno  | stuname      | stusex | writtenexam | labexam |
+--------+--------------+--------+-------------+---------+
| s25303 | 李斯文       | 女     |          80 |      58 |
| s25302 | 李文才       | 男     |          50 |      90 |
| s25304 | 欧阳俊雄     | 男     |          65 |      50 |
| s25301 | 张秋丽       | 男     |          77 |      82 |
| s25318 | 争青小子     | 男     |          56 |      48 |
+--------+--------------+--------+-------------+---------+

多学一招：三个表的内连接如何实现？

select * from 表1 inner join 表2 on 表1.公共字段=表2.公共字段
inner join 表3 on 表2.公共字段=表3.公共字段

2.2 左外连接【left join】

以左边的表为标准，如果右边的表没有对应的记录，用NULL填充。

语法：select 列名 from 表1 left join 表2 on 表1.公共字段=表2.公共字段

例题：

mysql> select stuname,writtenexam,labexam from stuinfo left join stumarks on stuinfo.stuno=stumarks.stuno;
+--------------+-------------+---------+
| stuname      | writtenexam | labexam |
+--------------+-------------+---------+
| 李斯文       |          80 |      58 |
| 李文才       |          50 |      90 |
| 欧阳俊雄     |          65 |      50 |
| 张秋丽       |          77 |      82 |
| 争青小子     |          56 |      48 |
| 诸葛丽丽     |        NULL |    NULL |
| 梅超风       |        NULL |    NULL |
+--------------+-------------+---------+

2.3 右外连接【right join】

以右边的表为标准，如果左边的表没有对应的记录，用NULL填充。

语法：select 列名 from 表1 right join 表2 on 表1.公共字段=表2.公共字段

例题：

mysql> select stuname,writtenexam,labexam from stuinfo right join stumarks on stuinfo.stuno=stumarks.stuno;
+--------------+-------------+---------+
| stuname      | writtenexam | labexam |
+--------------+-------------+---------+
| 李斯文       |          80 |      58 |
| 李文才       |          50 |      90 |
| 欧阳俊雄     |          65 |      50 |
| 张秋丽       |          77 |      82 |
| 争青小子     |          56 |      48 |
| NULL         |          66 |      77 |
+--------------+-------------+---------+

2.4 交叉连接【cross join】

插入测试数据：

mysql> create table t1(
    -> id int,
    -> name varchar(10)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t1 values (1,'tom'),(2,'berry');
Query OK, 2 rows affected (0.00 sec)

mysql> create table t2(
    -> id int,
    -> score int
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t2 values (1,99),(2,88);

1. 如果没有连接表达式，返回的是笛卡尔积：

mysql> select * from t1 cross join t2;
+------+-------+------+-------+
| id   | name  | id   | score |
+------+-------+------+-------+
|    1 | tom   |    1 |    99 |
|    2 | berry |    1 |    99 |
|    1 | tom   |    2 |    88 |
|    2 | berry |    2 |    88 |
+------+-------+------+-------+

2. 如果有连接表达式，等价于内连接：

mysql> select * from t1 cross join t2 where t1.id=t2.id;
+------+-------+------+-------+
| id   | name  | id   | score |
+------+-------+------+-------+
|    1 | tom   |    1 |    99 |
|    2 | berry |    2 |    88 |
+------+-------+------+-------+

2.5 自然连接【natural】

自然连接，自动地通过判断条件连接，它是通过同名字段来判断的。

自然连接又分为：

1. 自然内连接 natural join
2. 自然左外连接 natural left join
3. 自然右外连接 natural right join

例题：

# 自然内连接
mysql> select * from stuinfo natural join stumarks;
+--------+----------+--------+--------+---------+------------+---------+-------------+---------+
| stuNo  | stuName  | stuSex | stuAge | stuSeat | stuAddress | examNo  | writtenExam | labExam |
+--------+----------+--------+--------+---------+------------+---------+-------------+---------+
| s25303 | 李斯文        | 女      |     22 |       2 | 北京           | s271811 |          80 |
  58 |
| s25302 | 李文才        | 男       |     31 |       3 | 上海          | s271813 |          50 |
  90 |
| s25304 | 欧阳俊雄        | 男       |     28 |       4 | 天津           | s271815 |          65 |
     50 |
| s25301 | 张秋丽         | 男       |     18 |       1 | 北京           | s271816 |          77 |
    82 |
| s25318 | 争青小子        | 男       |     26 |       6 | 天津           | s271819 |          56 |
     48 |
+--------+----------+--------+--------+---------+------------+---------+-------------+---------+
5 rows in set (0.00 sec)

# 自然左外连接

mysql> select * from stuinfo natural left join stumarks;
+--------+----------+--------+--------+---------+------------+---------+-------------+---------+
| stuNo  | stuName  | stuSex | stuAge | stuSeat | stuAddress | examNo  | writtenExam | labExam |
+--------+----------+--------+--------+---------+------------+---------+-------------+---------+
| s25301 | 张秋丽         | 男       |     18 |       1 | 北京           | s271816 |          77
    82 |
| s25302 | 李文才        | 男       |     31 |       3 | 上海          | s271813 |          50 |
  90 |
| s25303 | 李斯文        | 女      |     22 |       2 | 北京           | s271811 |          80 |
  58 |
| s25304 | 欧阳俊雄        | 男       |     28 |       4 | 天津           | s271815 |          65
     50 |
| s25305 | 诸葛丽丽         | 女      |     23 |       7 | 河南           | NULL    |        NULL
   NULL |
| s25318 | 争青小子        | 男       |     26 |       6 | 天津           | s271819 |          56
     48 |
| s25319 | 梅超风        | 女      |     23 |       5 | 河北          | NULL    |        NULL |
ULL |
+--------+----------+--------+--------+---------+------------+---------+-------------+---------+
7 rows in set (0.00 sec)

# 自然右外连接
mysql> select * from stuinfo natural right join stumarks;
+--------+---------+-------------+---------+----------+--------+--------+---------+------------+
| stuNo  | examNo  | writtenExam | labExam | stuName  | stuSex | stuAge | stuSeat | stuAddress |
+--------+---------+-------------+---------+----------+--------+--------+---------+------------+
| s25303 | s271811 |          80 |      58 | 李斯文        | 女      |     22 |       2 | 北京
     |
| s25302 | s271813 |          50 |      90 | 李文才        | 男       |     31 |       3 | 上海
     |
| s25304 | s271815 |          65 |      50 | 欧阳俊雄        | 男       |     28 |       4 | 天津
        |
| s25301 | s271816 |          77 |      82 | 张秋丽         | 男       |     18 |       1 | 北京
       |
| s25318 | s271819 |          56 |      48 | 争青小子        | 男       |     26 |       6 | 天津
        |
| s25320 | s271820 |          66 |      77 | NULL     | NULL   |   NULL |    NULL | NULL       |
+--------+---------+-------------+---------+----------+--------+--------+---------+------------+
6 rows in set (0.00 sec)

自然连接结论：

1. 表连接通过同名的字段来连接的

2. 如果没有同名的字段返回笛卡尔积

3. 会对结果进行整理，整理的规则如下

   a)连接字段保留一个

   b)连接字段放在最前面

   c)左外连接左边在前，右外连接右表在前

2.6 using()

1. 用来指定连接字段
2. using()也会对连接字段进行整理，整理方式和自然连接一样。
   例题：

mysql> select * from stuinfo inner join stumarks using(stuno);
+--------+--------------+--------+--------+---------+------------+---------+-------------+---------+
| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress | examNo  | writtenExam | labExam |
+--------+--------------+--------+--------+---------+------------+---------+-------------+---------+
| s25303 | 李斯文       | 女     |     22 |       2 | 北京       | s271811 |          80 |      58 |
| s25302 | 李文才       | 男     |     31 |       3 | 上海       | s271813 |          50 |      90 |
| s25304 | 欧阳俊雄     | 男     |     28 |       4 | 天津       | s271815 |          65 |      50 |
| s25301 | 张秋丽       | 男     |     18 |       1 | 北京       | s271816 |          77 |      82 |
| s25318 | 争青小子     | 男     |     26 |       6 | 天津       | s271819 |          56 |      48 |
+--------+--------------+--------+--------+---------+------------+---------+-------------+---------+
5 rows in set (0.00 sec)