mysql多表查询的几个小例子

首先,内连结查询。

mysql> SELECT student.student_id, student.name, score.score,absence.date FROM student INNER JOIN score INNER JOIN absence INNER JOIN grade_event ON student.student_id = absence.student_id AND student.student_id = score.student_id AND score.event_id = grade_event.event_id AND grade_event.date = absence.date;
+------------+------+-------+------------+
| student_id | name | score | date       |
+------------+------+-------+------------+
|          1 | mio  |    99 | 2012-10-01 |
|          2 | yum  |    54 | 2012-10-01 |
|          3 | hehe |    22 | 2012-10-01 |
|          4 | kaka |    77 | 2012-10-01 |
|          1 | mio  |    77 | 2012-10-02 |
|          1 | mio  |    88 | 2012-10-03 |
|          1 | mio  |    99 | 2012-10-04 |
|          2 | yum  |    88 | 2012-10-04 |
+------------+------+-------+------------+
8 rows in set (0.00 sec)

统计学生参加考试的次数：

mysql> SELECT student.student_id, student.name, COUNT(absence.date) AS absences FROM student INNER JOIN absence ON student.student_id = absence.student_id GROUP BY student.student_id;
+------------+------+----------+
| student_id | name | absences |
+------------+------+----------+
|          1 | mio  |        4 |
|          2 | yum  |        3 |
|          3 | hehe |        1 |
|          4 | kaka |        2 |
+------------+------+----------+
4 rows in set (0.00 sec)

他按照student_id列出来了，但是有个id为5的家伙一次也没参加，就不会显示出来。
所以可以用LEFT JOIN左关联来代替普通的关联操作，MYSQL将为第一个数据表里的每一个中选数据生成一个输出行：

mysql> SELECT student.student_id, student.name, COUNT(absence.date) AS absences FROM student LEFT JOIN absence ON student.student_id = absence.student_id GROUP BY student.student_id;
+------------+------+----------+
| student_id | name | absences |
+------------+------+----------+
|          1 | mio  |        4 |
|          2 | yum  |        3 |
|          3 | hehe |        1 |
|          4 | kaka |        2 |
|          5 | ye   |        0 |
+------------+------+----------+
5 rows in set (0.04 sec)

子查询的例子，把一条SQL语句嵌套在另一个SQL语句中：

mysql> SELECT * FROM student WHERE student_id NOT IN (SELECT student_id FROM absence);
+------+-----+------------+
| name | sex | student_id |
+------+-----+------------+
| ye   | M   |          5 |
+------+-----+------------+
1 row in set (0.00 sec)

您可能感兴趣的文章：
MySQL多表更新与多表删除
mysql多表关联update更新
mysql批量更新、多表更新、多表删除的例子
实例学习MySQL多表之间字段的匹配
mysql多表联合查询并返回一张表的内容
mysql跨表更新(多表update sql语句)
mysql跨表delete删除多表记录

您可能感兴趣的文章：
mysql 子查询与join性能对比
mysql子查询的五种方式
mysql多表查询的几个小例子
mysql随机查询大量数据的sql语句性能分析
分享：mysql随机查询若干条数据的方法
MySQL多表更新与多表删除的实现方法
mysql随机取数据的几种方法
mysql全文查询与随机查询的例子
MySQL导入导出命令的例子
mysql 随机生成某个范围内的整数