13.笔记 MySQL学习——多表查询

来源：互联网发布：js设置div内容颜色编辑：程序博客网时间：2024/05/16 23:58

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';

+------------+------------+-------+----------+

+------------+------------+-------+----------+

| 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)

阅读全文

0 0