python_day16_DB_SQL

zhwz

// 查看老师对应的学生   内连接inner join　　
MariaDB [hellodb]> select * from students inner join teachers on students.TeacherID=teachers.TID;
　　
    +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
　　
    | StuID | Name        | Age | Gender | ClassID | TeacherID | TID | Name          | Age | Gender |
　　
    +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
　　
    |     5 | Yu Yutong   |  26 | M      |       3 |         1 |   1 | Song Jiang    |  45 | M      |
　　
    |     1 | Shi Zhongyu |  22 | M      |       2 |         3 |   3 | Miejue Shitai |  77 | F      |
　　
    |     4 | Ding Dian   |  32 | M      |       4 |         4 |   4 | Lin Chaoying  |  93 | F      |
　　
    +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
　　
    3 rows in set (0.00 sec)
　　

　　

　　
// 只显示老师学生姓名
　　
MariaDB [hellodb]> select students.Name as 学生姓名,teachers.Name as 老师姓名 from students inner join teachers on students.TeacherID=teachers.TID;
　　
    +-------------+---------------+
　　
    | 学生姓名    | 老师姓名      |
　　
    +-------------+---------------+
　　
    | Yu Yutong   | Song Jiang    |
　　
    | Shi Zhongyu | Miejue Shitai |
　　
    | Ding Dian   | Lin Chaoying  |
　　
    +-------------+---------------+
　　
    3 rows in set (0.00 sec)
　　

　　
// 显示班级对应的学生人数
　　
    group_concat()  -- 将重复的显示在一行,
　　
    group by classid将班级id进行分组查询
　　

　　
MariaDB [hellodb]> select classes.ClassID as 班级id, classes.Class as 班级,group_concat(students.Name) as 学生姓名 from students inner join classes on students.ClassID = classes.ClassID group by classes.ClassID;
　　
    +--------+----------------+------------------------------------------------------+
　　
    | 班级id | 班级           | 学生姓名                                             |
　　
    +--------+----------------+------------------------------------------------------+
　　
    |      1 | Shaolin Pai    | Xiao Qiao,Wen Qingqing,Xu Zhu,Shi Potian             |
　　
    |      2 | Emei Pai       | Shi Zhongyu,Tian Boguang,Xie Yanke                   |
　　
    |      3 | QingCheng Pai  | Yue Lingshan,Yu Yutong,Lu Wushuang,Xi Ren            |
　　
    |      4 | Wudang Pai     | Duan Yu,Ma Chao,Ding Dian,Lin Chong                  |
　　
    |      5 | Riyue Shenjiao | Shi Qing                                             |
　　
    |      6 | Lianshan Pai   | Xue Baochai,Ren Yingying,Huang Yueying,Yuan Chengzhi |
　　
    |      7 | Ming Jiao      | Diao Chan,Hua Rong,Lin Daiyu                         |
　　
    +--------+----------------+------------------------------------------------------+
　　

　　
// 查看每个课程对应的分数
　　
MariaDB [hellodb]> select courses.courseid as 课程编号, courses.course as 课程名称,group_concat(scores.score) as 课程分数 from courses inner join scores on courses.courseid = scores.courseid group by courses.courseid;
　　
    +----------+----------------+-------------+
　　
    | 课程编号 | 课程名称       | 课程分数    |
　　
    +----------+----------------+-------------+
　　
    |        1 | Hamo Gong      | 39,96,86    |
　　
    |        2 | Kuihua Baodian | 89,88,77,47 |
　　
    |        3 | Jinshe Jianfa  | 93          |
　　
    |        4 | Taiji Quan     | 57          |
　　
    |        5 | Daiyu Zanghua  | 97,71       |
　　
    |        6 | Weituo Zhang   | 75,93       |
　　
    |        7 | Dagou Bangfa   | 83,63       |
　　
    +----------+----------------+-------------+
　　
    7 rows in set (0.00 sec)
　　

　　
// 查看每个课程的总分数
　　
MariaDB [hellodb]> select courses.courseid as 课程编号, courses.course as 课程名称,sum(scores.score) as 课程分数 from courses inner join scores on courses.courseid = scores.courseid group by courses.courseid;
　　
    +----------+----------------+----------+
　　
    | 课程编号 | 课程名称       | 课程分数 |
　　
    +----------+----------------+----------+
　　
    |        1 | Hamo Gong      |      221 |
　　
    |        2 | Kuihua Baodian |      301 |
　　
    |        3 | Jinshe Jianfa  |       93 |
　　
    |        4 | Taiji Quan     |       57 |
　　
    |        5 | Daiyu Zanghua  |      168 |
　　
    |        6 | Weituo Zhang   |      168 |
　　
    |        7 | Dagou Bangfa   |      146 |
　　
    +----------+----------------+----------+
　　
    7 rows in set (0.00 sec)
　　

　　
// 接上面,找出分数大于100的班级
　　
    having 条件 可以直接使用as name
　　
MariaDB [hellodb]> select courses.courseid as 课程编号, courses.course as 课程名称,sum(scores.score) as 课程分数 from courses inner join scores on courses.courseid = scores.courseid  group by courses.courseid having 课程分数>100;
　　
    +----------+----------------+----------+
　　
    | 课程编号 | 课程名称       | 课程分数 |
　　
    +----------+----------------+----------+
　　
    |        1 | Hamo Gong      |      221 |
　　
    |        2 | Kuihua Baodian |      301 |
　　
    |        5 | Daiyu Zanghua  |      168 |
　　
    |        6 | Weituo Zhang   |      168 |
　　
    |        7 | Dagou Bangfa   |      146 |
　　
    +----------+----------------+----------+
　　
    5 rows in set (0.00 sec)
　　

　　
// 查找出学生对应的班级以及对应班级的分数
　　
    多表left join 连接查询
　　

　　
MariaDB [hellodb]> select courses.courseid,courses.course,students.Name,scores.score from scores left join students on scores.StuID = students.stuid left join courses on scores.courseid = courses.courseid;
　　
    +----------+----------------+-------------+-------+
　　
    | courseid | course         | Name        | score |
　　
    +----------+----------------+-------------+-------+
　　
    |        2 | Kuihua Baodian | Shi Zhongyu |    77 |
　　
    |        6 | Weituo Zhang   | Shi Zhongyu |    93 |
　　
    |        2 | Kuihua Baodian | Shi Potian  |    47 |
　　
    |        5 | Daiyu Zanghua  | Shi Potian  |    97 |
　　
    |        2 | Kuihua Baodian | Xie Yanke   |    88 |
　　
    |        6 | Weituo Zhang   | Xie Yanke   |    75 |
　　
    |        5 | Daiyu Zanghua  | Ding Dian   |    71 |
　　
    |        2 | Kuihua Baodian | Ding Dian   |    89 |
　　
    |        1 | Hamo Gong      | Yu Yutong   |    39 |
　　
    |        7 | Dagou Bangfa   | Yu Yutong   |    63 |
　　
    |        1 | Hamo Gong      | Shi Qing    |    96 |
　　
    |        1 | Hamo Gong      | Xi Ren      |    86 |
　　
    |        7 | Dagou Bangfa   | Xi Ren      |    83 |
　　
    |        4 | Taiji Quan     | Lin Daiyu   |    57 |
　　
    |        3 | Jinshe Jianfa  | Lin Daiyu   |    93 |
　　
    +----------+----------------+-------------+-------+
　　
    15 rows in set (0.00 sec)
　　

　　
// 查看学生对应班级,以及分数
　　
select group_concat(courses.course) as 班级名称,students.Name as 学生姓名,group_concat(scores.score) as 分数 from scores
　　
    left join students on scores.StuID = students.stuid
　　
    left join courses on scores.courseid= courses.courseid group by Name;
　　

　　
    +------------------------------+-------------+-------+
　　
    | 班级名称                     | 学生姓名    | 分数  |
　　
    +------------------------------+-------------+-------+
　　
    | Kuihua Baodian,Daiyu Zanghua | Ding Dian   | 89,71 |
　　
    | Jinshe Jianfa,Taiji Quan     | Lin Daiyu   | 93,57 |
　　
    | Daiyu Zanghua,Kuihua Baodian | Shi Potian  | 97,47 |
　　
    | Hamo Gong                    | Shi Qing    | 96    |
　　
    | Weituo Zhang,Kuihua Baodian  | Shi Zhongyu | 93,77 |
　　
    | Dagou Bangfa,Hamo Gong       | Xi Ren      | 83,86 |
　　
    | Weituo Zhang,Kuihua Baodian  | Xie Yanke   | 75,88 |
　　
    | Dagou Bangfa,Hamo Gong       | Yu Yutong   | 63,39 |
　　
    +------------------------------+-------------+-------+
　　
    8 rows in set (0.00 sec)
　　

　　
// 接上,查看总分
　　
select group_concat(courses.course) as 班级名称,students.Name as 学生姓名,sum(scores.score) as 分数 from scores
　　
    left join students on scores.StuID = students.stuid
　　
    left join courses on scores.courseid= courses.courseid group by Name;
　　

　　
    +------------------------------+-------------+------+
　　
    | 班级名称                     | 学生姓名    | 分数 |
　　
    +------------------------------+-------------+------+
　　
    | Kuihua Baodian,Daiyu Zanghua | Ding Dian   |  160 |
　　
    | Jinshe Jianfa,Taiji Quan     | Lin Daiyu   |  150 |
　　
    | Daiyu Zanghua,Kuihua Baodian | Shi Potian  |  144 |
　　
    | Hamo Gong                    | Shi Qing    |   96 |
　　
    | Weituo Zhang,Kuihua Baodian  | Shi Zhongyu |  170 |
　　
    | Dagou Bangfa,Hamo Gong       | Xi Ren      |  169 |
　　
    | Weituo Zhang,Kuihua Baodian  | Xie Yanke   |  163 |
　　
    | Dagou Bangfa,Hamo Gong       | Yu Yutong   |  102 |
　　
    +------------------------------+-------------+------+
　　

　　
// 接上,查看总分大于120分以上的
　　
select group_concat(courses.course) as 班级名称,students.Name as 学生姓名,sum(scores.score) as 分数 from scores
　　
    left join students on scores.StuID = students.stuid
　　
    left join courses on scores.courseid= courses.courseid group by Name
　　
    HAVING 分数>120;
　　

　　
    +------------------------------+-------------+------+
　　
    | 班级名称                     | 学生姓名    | 分数 |
　　
    +------------------------------+-------------+------+
　　
    | Kuihua Baodian,Daiyu Zanghua | Ding Dian   |  160 |
　　
    | Jinshe Jianfa,Taiji Quan     | Lin Daiyu   |  150 |
　　
    | Daiyu Zanghua,Kuihua Baodian | Shi Potian  |  144 |
　　
    | Weituo Zhang,Kuihua Baodian  | Shi Zhongyu |  170 |
　　
    | Dagou Bangfa,Hamo Gong       | Xi Ren      |  169 |
　　
    | Weituo Zhang,Kuihua Baodian  | Xie Yanke   |  163 |
　　
    +------------------------------+-------------+------+
　　
    6 rows in set (0.00 sec)
　　

　　
// 右连接,以右边为主
　　
select students.name as 学生姓名,teachers.name as 老师姓名 from teachers
　　
  RIGHT join students on  teachers.tid = students.teacherid;
　　
      +---------------+---------------+
　　
    | 学生姓名      | 老师姓名      |
　　
    +---------------+---------------+
　　
    | Shi Zhongyu   | Miejue Shitai |
　　
    | Shi Potian    | NULL          |
　　
    | Xie Yanke     | NULL          |
　　
    | Ding Dian     | Lin Chaoying  |
　　
    | Yu Yutong     | Song Jiang    |
　　
    | Shi Qing      | NULL          |
　　
    | Xi Ren        | NULL          |
　　
    | Lin Daiyu     | NULL          |
　　
    | Ren Yingying  | NULL          |
　　
    | Yue Lingshan  | NULL          |
　　
    | Yuan Chengzhi | NULL          |
　　
    | Wen Qingqing  | NULL          |
　　
    | Tian Boguang  | NULL          |
　　
    ......
　　

　　
    // 如有更多问题,请留言