RDBMS的威力在于他们能把一种东西与另一种东西关联起来,即能把来自多个数据表的信息结合在一起解决单个数据表解决不了的问题

我们可以用连结(join) 也 可以用 子查询

这是照着教科书建的4张比较三范式的表,但是用起来很纠结哈哈,看来我支持百度的反三范式在很多情况下是没错的

首先就是连结了,内连结

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)