mysql 查询学生与课程关系表

-- 已经学生和课程之间是多对多关系
-- 学生表（学生编号（主键）、学生名称（唯一约束不为空）、性别（默认值男）、年龄）
drop table if EXISTS student;
create table student (
	stu_no int PRIMARY KEY,
	stu_name VARCHAR(20) not null UNIQUE,
	sex CHAR(1) DEFAULT '男' COMMENT '性别',
	age VARCHAR(3)
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT '学生表';
desc student;

-- 课程表（课程编号（主键）、课程名称（唯一约束不为空）、学分）
drop table if exists class;
create table class (
	class_no int PRIMARY KEY,
	class_name VARCHAR(20) not null UNIQUE,
	score int
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT '课程表';
desc class;

-- 学生和课程关系表（学生编号，课程编号，成绩）
drop table if exists student_course;
create table student_course (
 	stu_no INT,
 	class_no INT,
 	results VARCHAR(20) NOT NULL,
-- 1、课程编号和学生编号为联合主键。
 	PRIMARY KEY(stu_no,class_no),
-- 2、课程编号和学号编号为外键 数据来源于学生表和课程表的主键值。
  FOREIGN key(stu_no) REFERENCES student(stu_no),
  FOREIGN key(class_no) REFERENCES class(class_no) 
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT '关系表';
desc student_course;

INSERT INTO student(stu_no,stu_name,age) VALUES(8101,'张三',18);
INSERT INTO student(stu_no,stu_name,age) VALUES(8102,'钱四',16);
INSERT INTO student(stu_no,stu_name,age) VALUES(8105,'李飞',19);
INSERT INTO student(stu_no,stu_name,age) VALUES(8201,'张飞',18);
INSERT INTO student(stu_no,stu_name,age) VALUES(8302,'周瑜',16);
INSERT INTO student(stu_no,stu_name,age) VALUES(8203,'王亮',17);

INSERT INTO class VALUES (101,'软件',2),(102,'微电子',3),(103,'无机化学',3),(104,'高分子化学',3),(105,'统计数学',4);

DELETE FROM student_course;
INSERT into student_course VALUES (8101,101,93),(8101,102,68),(8101,103,99),(8101,104,93),(8101,105,45),(8102,101,66),(8102,102,77),(8102,103,88),(8102,104,66),(8102,105,88),(8105,101,99),(8105,102,99),(8105,103,99),(8105,104,99),(8105,105,99),(8201,101,33),(8201,102,93),(8201,103,33),(8201,104,63),(8201,105,73),(8302,101,33),(8302,102,93),(8302,103,33),(8302,104,63),(8302,105,73),(8203,101,33),(8203,102,93),(8203,103,33),(8203,104,63),(8203,105,73);

-- 1、查询学生与课程关系表（显示的字段有：学生姓名、课程姓名、课程成绩）
SELECT sc.*,s.stu_name,c.class_name 
FROM student s
join student_course sc
ON s.stu_no=sc.stu_no 
JOIN class c
ON c.class_no=sc.class_no
ORDER BY sc.stu_no

-- 2、统计每个学生选了几门课程 （学生编号、学生姓名、课程数量，具体课程名称）。
SELECT s.stu_no,s.stu_name,COUNT(*) c,GROUP_CONCAT(c.class_name) className
FROM student s
JOIN student_course sc
ON s.stu_no=sc.stu_no
JOIN class c
ON c.class_no=sc.class_no
GROUP BY s.stu_no;

-- 3、统计每个课程被几个学生选。 （课程编号、课程名称、学生数量、具体学生的名称）
SELECT c.class_no,c.class_name,COUNT(*) studentSum,GROUP_CONCAT(s.stu_name) stuName
FROM student s
JOIN student_course sc
ON s.stu_no=sc.stu_no
JOIN class c
ON c.class_no=sc.class_no
GROUP BY c.class_no