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

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)

相关文章

相关标签/搜索