13.笔记 MySQL学习——多表查询
如果学过其他数据库,大家就会发现其实都很像的。
mysql> select student_id,date,score,category fromgrade_event inner join score on grade_event.event_id = score.event_id wheredate='2012-09-23';
+------------+------------+-------+----------+
| student_id | date | score | category |
+------------+------------+-------+----------+
| 1 | 2012-09-23 | 15 | Q |
| 2 | 2012-09-23 | 12 | Q |
| 3 | 2012-09-23 | 11 | Q |
| 5 | 2012-09-23 | 13 | Q |
| 6 | 2012-09-23 | 18 | Q |
| 7 | 2012-09-23 | 14 | Q |
| 8 | 2012-09-23 | 18 | Q |
| 9 | 2012-09-23 | 13 | Q |
| 10 | 2012-09-23 | 14 | Q |
| 11 | 2012-09-23 | 18 | Q |
| 12 | 2012-09-23 | 8 | Q |
| 13 | 2012-09-23 | 8 | Q |
| 14 | 2012-09-23 | 16 | Q |
| 15 | 2012-09-23 | 13 | Q |
| 16 | 2012-09-23 | 15 | Q |
| 17 | 2012-09-23 | 11 | Q |
| 18 | 2012-09-23 | 18 | Q |
| 19 | 2012-09-23 | 18 | Q |
| 20 | 2012-09-23 | 14 | Q |
| 21 | 2012-09-23 | 17 | Q |
| 22 | 2012-09-23 | 17 | Q |
| 23 | 2012-09-23 | 15 | Q |
| 25 | 2012-09-23 | 14 | Q |
| 26 | 2012-09-23 | 8 | Q |
| 28 | 2012-09-23 | 20 | Q |
| 29 | 2012-09-23 | 16 | Q |
| 31 | 2012-09-23 | 9 | Q |
+------------+------------+-------+----------+
27 rows in set (0.00 sec)
mysql> selectstudent.student_id,student.name,count(absence.date) as absences from studentinner join absence on student.student_id = absence.student_id group bystudent.student_id;
+------------+-------+----------+
| student_id | name | absences |
+------------+-------+----------+
| 3 | Kyle | 1 |
| 5 | Abby | 1 |
| 10 | Peter | 2 |
| 17 | Will | 1 |
| 20 | Avery | 1 |
+------------+-------+----------+
5 rows in set (0.00 sec)
以上是内连接(inner join)
左连接:
查询缺勤的同学,同时查询其他学生情况
mysql> selectstudent.student_id,student.name,count(absence.date) as absences from studentleft join absence on student.student_id = absence.student_id group bystudent.student_id;
+------------+-----------+----------+
| student_id | name | absences |
+------------+-----------+----------+
| 1 | Megan | 0 |
| 2 | Joseph | 0 |
| 3 | Kyle | 1 |
| 4 | Katie | 0 |
| 5 | Abby | 1 |
| 6 | Nathan | 0 |
| 7 | Liesl | 0 |
| 8 | Ian | 0 |
| 9 | Colin | 0 |
| 10 | Peter | 2 |
| 11 | Michael | 0 |
| 12 | Thomas | 0 |
| 13 | Devri | 0 |
| 14 | Ben | 0 |
| 15 | Aubrey | 0 |
| 16 | Rebecca | 0 |
| 17 | Will | 1 |
| 18 | Max | 0 |
| 19 | Rianne | 0 |
| 20 | Avery | 1 |
| 21 | Lauren | 0|
| 22 | Becca | 0 |
| 23 | Gregory | 0 |
| 24 | Sarah | 0 |
| 25 | Robbie | 0 |
| 26 | Keaton | 0 |
| 27 | Carter | 0 |
| 28 | Teddy | 0 |
| 29 | Gabrielle | 0 |
| 30 | Grace | 0 |
| 31 | Emily | 0 |
+------------+-----------+----------+
31 rows in set (0.00 sec)
选出出生在Andrew Jackson总统之前
mysql> select last_name,first_name,birth frompresident where birth < (select birth from president where last_name ='Jackson' and first_name = 'Andrew');
+------------+------------+------------+
| last_name | first_name | birth |
+------------+------------+------------+
| Washington | George | 1732-02-22 |
| Adams | John | 1735-10-30 |
| Jefferson | Thomas | 1743-04-13 |
| Madison | James | 1751-03-16 |
| Monroe | James | 1758-04-28 |
+------------+------------+------------+
5 rows in set (0.00 sec)