场景:假设一个学生有多门课程,一门课程有多个学生(虽然两者是多对多关系,但本次假设为一对多)。
学生表:
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`ID` int(22) NOT NULL,
`StuName` varchar(22) DEFAULT NULL,
`StuAge` int(22) DEFAULT NULL,
`Cid` int(22) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '张三', '66', '3');
INSERT INTO `student` VALUES ('2', '李四', '77', '3');
INSERT INTO `student` VALUES ('4', '王五', '25', '4');
课程表:
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`ID` int(22) NOT NULL,
`CouName` varchar(22) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', '语文');
INSERT INTO `course` VALUES ('2', '数学');
INSERT INTO `course` VALUES ('3', '英语');
INSERT INTO `course` VALUES ('4', '地理');
INSERT INTO `course` VALUES ('5', '政治');
INSERT INTO `course` VALUES ('6', '历史');
INSERT INTO `course` VALUES ('7', '化学');
INSERT INTO `course` VALUES ('8', '物理');
需求 1:查询学生名对应的课程名
select s.StuName,c.CouName from Student s INNER JOIN Course c ON s.Cid = c.ID;
需求2:查询每门课程的选修人数
SELECT c.CouName,COUNT(DISTINCT s.StuName) AS '选修人数' FROM Student s RIGHT JOIN Course c ON s.Cid = c.ID
GROUP BY c.CouName;