资源描述：
《mysql-多表关联实例》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。
1、Mysql学习中，单表查询每个人都很容易掌握；但是多表之间的关联查询对于初学者是很难掌握的，以下是我学习中，结合老师所讲例子作的总结，主要就是三张表的关联查询。还包括groupby操作。Joinon；leftjoinon；groupby。。。。。三张表的具体信息：学生表student课程表course学生课程关系表s_to_cgroupby的简单使用：SELECT age, COUNT(1) FROM student GROUP BY ageSELECTname, COUNT(1) FROM student GROUP BYname-- 1、班内学生的男女个数SELECT gend
2、er,COUNT(1) FROM student GROUP BY gender-- 2、班内的年龄类型个数SELECT COUNT(DISTINCT age) FROM student-- 3、班内的性别类型个数SELECT COUNT(DISTINCT gender) FROM student-- 4、每个学生所参与的课程的个数SELECT a.stu_id, a.name, a.gender, a.age, COUNT(b.c_id) FROM student a LEFT JOIN s_to_c b ON a.stu_id=b.s_id
3、 GROUP BY a.stu_id, a.name, a.gender, a.ageSELECT a.stu_id, a.name, a.gender, a.age, COUNT(b.c_id) FROM student a INNER JOIN s_to_c b ON a.stu_id=b.s_id GROUP BY a.stu_id, a.name, a.gender, a.age-- 5、每个课程参与的学生个数SELECT a.c
4、_id, a.c_name, COUNT(b.s_id)FROM course a LEFT JOIN s_to_c b ON a.c_id=b.c_id GROUP BY a.c_id, a.c_name-- 6、每个学生的信息以及其参与的课程名称SELECT a.stu_id, a.name, a.gender, a.age, c.c_nameFROM student aLEFT JOIN s_to_c b ON a.stu_id=b.s_idLEFT JOIN course c ON b.c_id
5、=c.c_id-- 7、每个课程的信息以及其参与该课程的学生名字SELECT a.c_id, a.c_name, c.nameFROM course aLEFT JOIN s_to_c b ON a.c_id=b.c_idLEFT JOIN student c ON b.s_id=c.stu_id-- 8、每门课程的信息以及参与学生的个数，最大年龄，最小年龄，平均年龄SELECT a.c_id, a.c_name, COUNT(b.s_id), MAX(c.age), MIN(c.age), AVG(c.age)FROM course aL
6、EFT JOIN s_to_c b ON a.c_id=b.c_idLEFT JOIN student c ON b.s_id=c.stu_idGROUP BY a.c_id, a.c_name-- 9、关系添加表中添加一个字段"课程分数" score int，分别填入值ALTER TABLE s_to_c ADD score INT-- 10、每个学生的总成绩，平均成绩，最高分成绩，最低分成绩SELECT a.stu_id, a.name, a.age, a.gender, SUM(b.score), AVG(b.score),
7、 MAX(b.score), MIN(b.score)FROM student aLEFT JOIN s_to_c b ON a.stu_id=b.s_idGROUP BY a.stu_id, a.name, a.age, a.gender-- 11、每个学生分数最高的课程名称SELECT a.*, b.maxscore, d.c_nameFROM student aLEFT JOIN (SELECT
