mysql统计每个学生的选课门数_面试经典数据库查询题目（学生、课程、选课）...

这是前几天一刚毕业的朋友的面试题，算是面试常遇到的sql经典题目，记录一下(如图)

在此我在电脑数据库上建立了对应的表结构及数据，以供检验sql语句正误。

建表语句：

学生表(student_info)

DROP TABLE IF EXISTS `student_info`;

CREATE TABLE `student_info` (

`no` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,

`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

`sex` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

`age` int(10) NULL DEFAULT NULL,

`dept` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

PRIMARY KEY (`no`) USING BTREE

) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `student_info` VALUES ('1612101', '李勇', '男', 21, '数学系');

INSERT INTO `student_info` VALUES ('1612102', '刘晨', '男', 23, '数学系');

INSERT INTO `student_info` VALUES ('1612103', '王敏', '女', 21, '信息工程系');

INSERT INTO `student_info` VALUES ('1612104', '张立', '男', 20, '信息工程系');

课程表(course_info)

DROP TABLE IF EXISTS `course_info`;

CREATE TABLE `course_info` (

`no` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,

`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

`hours` int(10) NULL DEFAULT NULL,

PRIMARY KEY (`no`) USING BTREE

) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `course_info` VALUES ('C01', '计算机导论', 70);

INSERT INTO `course_info` VALUES ('C02', 'VC++', 90);

INSERT INTO `course_info` VALUES ('C03', '计算机网络', 80);

INSERT INTO `course_info` VALUES ('C04', '数据库基础', 105);

INSERT INTO `course_info` VALUES ('C05', '高等数学', 180);

INSERT INTO `course_info` VALUES ('C06', '数据结构', 72);

选课表(choice_info)

DROP TABLE IF EXISTS `choice_info`;

CREATE TABLE `choice_info` (

`student_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,

`course_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,

`grade` int(10) NULL DEFAULT NULL

) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `choice_info` VALUES ('1612101', 'C01', 90);

INSERT INTO `choice_info` VALUES ('1612101', 'C02', 86);

INSERT INTO `choice_info` VALUES ('1612101', 'C05', 84);

INSERT INTO `choice_info` VALUES ('1612102', 'C01', 85);

INSERT INTO `choice_info` VALUES ('1612102', 'C03', 91);

INSERT INTO `choice_info` VALUES ('1612102', 'C06', 76);

INSERT INTO `choice_info` VALUES ('1612103', 'C04', 80);

INSERT INTO `choice_info` VALUES ('1612103', 'C03', 82);

对应各个表中的数据根据图中提供进行插入

1.查询学生都选修了哪些课程，要求列出课程名、课程号、选修人数以及最好成绩；

SELECT

course.`name`,

course.`no`,

COUNT(choice.course_no) `count`,

MAX(choice.grade) Maxgrade

FROM

course_info course,

choice_info choice

WHERE

choice.course_no = course.`no`

GROUP BY

choice.course_no;

2.统计每个学生的选课门数，并按选课门数的递减顺序显示(注：题为每个学生，故包含未选课学生)

SELECT

student.`name`,

COUNT(choice.course_no) `count`

FROM

choice_info choice

RIGHT JOIN student_info student ON choice.student_no = student. NO

GROUP BY

student.no

ORDER BY

COUNT(choice.course_no) DESC;

3.查询选课门数超过2门的学生的平均成绩和选课门数；

SELECT

a.`name`,

a.avg,

a.count

FROM

(

SELECT

student.`name` `name`,

IFNULL(AVG(choice.grade), 0) `avg`,

COUNT(choice.course_no) `count`

FROM

student_info student

LEFT JOIN choice_info choice ON choice.student_no = student.`no`

GROUP BY

student.`no`

) a

WHERE

a.count > 2;

4.查询有考试成绩的所有学生的姓名、修课名称及考试成绩，并将查询结果放到一张新的永久表(假设新表名为tb_rel_new)中

注：此题包含新建表和插入查询数据

查询

SELECT

a.`name` studentName,

course.`name` courseName,

a.grade

FROM

course_info course

LEFT JOIN (

SELECT

student.`name` `name`,

choice.grade grade,

choice.course_no courseNo

FROM

student_info student

RIGHT JOIN choice_info choice ON choice.student_no = student.`no`

) a ON a.courseNo = course.`no`;

创建tb_rel_new表

CREATE TABLE `tb_rel_new` (

`studentName` VARCHAR (255) DEFAULT NULL,

`courseName` VARCHAR (255) DEFAULT NULL,

`grade` INT (10) DEFAULT NULL

) ENGINE = INNODB DEFAULT CHARSET = utf8;

插入数据

INSERT INTO tb_rel_new (

studentName,

courseName,

grade

) SELECT

a.`name` studentName,

course.`name` courseName,

a.grade

FROM

course_info course

LEFT JOIN (

SELECT

student.`name` `name`,

choice.grade grade,

choice.course_no courseNo

FROM

student_info student

RIGHT JOIN choice_info choice ON choice.student_no = student.`no`

) a ON a.courseNo = course.`no`;

总结：此题主要考察数据库sql基础，面试中要快速理清数据关系，问题便迎刃而解。