MySQL课程-数据表多表查询

数据表的多表查询

更新时间：浏览次数：

多表查询

SELECT查询不但可以从一张表查询数据，还可以从多张表同时查询数据。查询多张表的语法是：SELECT * FROM <表1> <表2>。

例如，同时从student表和class表的“乘积”，即查询数据，可以这么写：

                    SELECT * FROM student,class;

这种一次查询两个表的数据，查询的结果也是一个二维表，它是students表和classes表的“乘积”，这种多表查询又称笛卡尔查询，即student表的每一行与class表的每一行都两两拼在一起返回。结果集的列数是student表和class表的列数之和，行数是student表和class表的行数之积。

通过多表之间的投影查询，我们可以只保留我们关心的栏位，例如：

                    SELECT t1.id,t1.age,t1.name AS sname,t2.name FROM student t1,class t2;

其中student的别名是t1，class的别名是t2，我们只查询student的id、age、name（别名为sname）栏位和class的name栏位。

通过WHERE子句将两个数据表按一定约束条件组织到一起。

                    mysql> SELECT t1.id,t1.age,t1.name AS sname,t2.name FROM student t1,class t2 WHERE t1.classId = t2.id;
                    +----+------+----------+-------------------+
                    | id | age  | sname    | name              |
                    +----+------+----------+-------------------+
                    |  1 |    8 | xiaoming | 1st class,grade 3 |
                    |  2 |    8 | xiaogang | 1st class,grade 3 |
                    |  3 |    8 | 小华     | 2nd class,grade 3 |
                    |  4 |   10 | 小娟     | 2nd class,grade 3 |
                    |  6 |    8 | 晓东     | 2nd class,grade 3 |
                    |  8 |    9 | 李笑     | 2nd class,grade 3 |
                    |  5 |    9 | 小菊     | 3rd class,grade 3 |
                    |  7 |   10 | 王佳     | 3rd class,grade 3 |
                    +----+------+----------+-------------------+
                    8 rows in set (0.00 sec)

连接查询

内连接的语法 SELECT ... FROM <表1> INNER JOIN <表2> ON ...

                    mysql> SELECT t1.id,t1.age,t1.name AS sname,t2.name FROM student t1  INNER JOIN class t2 ON t1.classId = t2.id;
                    +----+------+----------+-------------------+
                    | id | age  | sname    | name              |
                    +----+------+----------+-------------------+
                    |  1 |    8 | xiaoming | 1st class,grade 3 |
                    |  2 |    8 | xiaogang | 1st class,grade 3 |
                    |  3 |    8 | 小华     | 2nd class,grade 3 |
                    |  4 |   10 | 小娟     | 2nd class,grade 3 |
                    |  6 |    8 | 晓东     | 2nd class,grade 3 |
                    |  8 |    9 | 李笑     | 2nd class,grade 3 |
                    |  5 |    9 | 小菊     | 3rd class,grade 3 |
                    |  7 |   10 | 王佳     | 3rd class,grade 3 |
                    +----+------+----------+-------------------+
                    8 rows in set (0.00 sec)

左连接的语法 SELECT ... FROM <表1> LEFT JOIN <表2> ON ...

                    mysql> SELECT t1.id,t1.age,t1.name AS sname,t2.name FROM student t1  LEFT JOIN class t2 ON t1.classId = t2.id;

右连接的语法 SELECT ... FROM <表1> RIGHT JOIN <表2> ON ...

                    mysql> SELECT t1.id,t1.age,t1.name AS sname,t2.name FROM student t1  RIGHT JOIN class t2 ON t1.classId = t2.id;