MySQL最经典50道练习题

表名和字段

学生表

Student(s_id,s_name,s_birth,s_sex)：学生编号、学生姓名、出生年月、学生性别。

课程表

Course(c_id,c_name,t_id)：课程编号、课程名称、教师编号。

教师表

Teacher(t_id,t_name)：教师编号、教师姓名。

成绩表

Score(s_id,c_id,s_score)：学生编号、课程编号、分数。

建表语句

# 建表
# 学生表
CREATE TABLE `student`(
    `s_id` VARCHAR(20),
    `s_name` VARCHAR(20) NOT NULL DEFAULT '',
    `s_birth` VARCHAR(20) NOT NULL DEFAULT '',
    `s_sex` VARCHAR(10) NOT NULL DEFAULT '',
    PRIMARY KEY(`s_id`)
);
# 课程表
CREATE TABLE `course`(
    `c_id` VARCHAR(20),
    `c_name` VARCHAR(20) NOT NULL DEFAULT '',
    `t_id` VARCHAR(20) NOT NULL,
    PRIMARY KEY(`c_id`)
);
# 教师表
CREATE TABLE `teacher`(
    `t_id` VARCHAR(20),
    `t_name` VARCHAR(20) NOT NULL DEFAULT '',
    PRIMARY KEY(`t_id`)
);
# 成绩表
CREATE TABLE `score`(
    `s_id` VARCHAR(20),
    `c_id` VARCHAR(20),
    `s_score` INT(3),
    PRIMARY KEY(`s_id`,`c_id`)
);

# 插入数据
# 插入学生表测试数据
insert into Student values('1', '赵雷', '1990-01-01', '男');
insert into Student values('2', '钱电', '1990-12-21', '男');
insert into Student values('3', '孙风', '1990-05-20', '男');
insert into Student values('4', '李云', '1990-08-06', '男');
insert into Student values('5', '周梅', '1991-12-01', '女');
insert into Student values('6', '吴兰', '1992-03-01', '女');
insert into Student values('7', '郑竹', '1989-07-01', '女');
insert into Student values('8', '王菊', '1990-01-20', '女');

# 插入课程表测试数据
insert into Course values('1', '语文', '2');
insert into Course values('2', '数学', '1');
insert into Course values('3', '英语', '3');

# 插入教师表测试数据
insert into Teacher values('1', '张三');
insert into Teacher values('2', '李四');
insert into Teacher values('3', '王五');

# 插入成绩表测试数据
insert into Score values('1', '1', 80);
insert into Score values('1', '2', 90);
insert into Score values('1', '3', 99);
insert into Score values('2', '1', 70);
insert into Score values('2', '2', 60);
insert into Score values('2', '3', 80);
insert into Score values('3', '1', 80);
insert into Score values('3', '2', 80);
insert into Score values('3', '3', 80);
insert into Score values('4', '1', 50);
insert into Score values('4', '2', 30);
insert into Score values('4', '3', 20);
insert into Score values('5', '1', 76);
insert into Score values('5', '2', 87);
insert into Score values('6', '1', 31);
insert into Score values('6', '3', 34);
insert into Score values('7', '2', 89);
insert into Score values('7', '3', 98);

题目及答案

# 1.查询"1"课程比"2"课程成绩高的学生的信息及课程分数。
SELECT
	student.*,
	s1.s_score AS "语文成绩",
	s2.s_score AS "数学成绩"
FROM
	student
LEFT JOIN
	score AS s1
ON
	student.s_id = s1.s_id AND s1.c_id = 1
LEFT JOIN
	score AS s2
ON
	student.s_id = s2.s_id AND s2.c_id = 2
WHERE
	s1.s_score > s2.s_score;
	
# 2.查询"1"课程比"2"课程成绩低的学生的信息及课程分数。
SELECT
	student.*,
	s1.s_score AS "语文成绩",
	s2.s_score AS "数学成绩"
FROM
	student
LEFT JOIN
	score AS s1
ON
	s1.s_id = student.s_id AND s1.c_id = 1
LEFT JOIN
	score AS s2
ON
	s2.s_id = student.s_id AND s2.c_id = 2
WHERE
	s1.s_score < s2.s_score;
	
# 3.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩。
SELECT
	student.s_id AS "学生编号",
	student.s_name AS "学生姓名",
	ROUND(AVG(score.s_score),2) AS "平均成绩"
FROM
	student
LEFT JOIN
	score
ON
	score.s_id = student.s_id
GROUP BY
	student.s_id
HAVING
	AVG(score.s_score) >= 60;

# 4.查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩（包括有成绩的和无成绩的）。
SELECT
	student.s_id AS "学生编号",
	student.s_name AS "学生姓名",
	IFNULL(ROUND(AVG(score.s_score),2),0) AS "平均成绩"
FROM
	student
LEFT JOIN
	score
ON
	score.s_id = student.s_id
GROUP BY
	student.s_id
HAVING
	AVG(score.s_score) < 60 OR AVG(score.s_score) IS NULL;
	
# 5.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩。
SELECT
	student.s_id AS "学生编号",
	student.s_name AS "学生姓名",
	COUNT(course.c_id) AS "选课总数",
	IFNULL(SUM(score.s_score),0) AS "所有课程总成绩"
FROM
	student
LEFT JOIN
	score
ON
	student.s_id = score.s_id
LEFT JOIN
	course
ON
	course.c_id = score.c_id
GROUP BY
	student.s_id;

# 6.查询"李"姓老师的数量。
SELECT
	COUNT(t_id)
FROM
	teacher
WHERE
	t_name LIKE "李%";
	
# 7.查询学过"张三"老师授课的同学的信息。
SELECT
	student.*
FROM
	student
LEFT JOIN
	score
ON
	student.s_id = score.s_id
LEFT JOIN
	course
ON
	score.c_id = course.c_id
LEFT JOIN
	teacher
ON
	course.t_id = teacher.t_id
WHERE
	teacher.t_name = "张三";
	
# 8.查询没学过"张三"老师授课的同学的信息。
SELECT
	student.*
FROM
	student
WHERE
	student.s_id NOT IN
(
	SELECT
		student.s_id
	FROM
		student
	LEFT JOIN
		score
	ON
		score.s_id = student.s_id
	WHERE
		score.c_id IN
	(
		SELECT
			course.c_id
		FROM
			course
		LEFT JOIN
			teacher
		ON
			course.t_id = teacher.t_id
		WHERE
			teacher.t_name = "张三"
	)
)

# 9.查询学过编号为"1"并且也学过编号为"2"的课程的同学的信息。
SELECT
	student.*
FROM
	student
INNER JOIN
	score
ON
	score.s_id = student.s_id
INNER JOIN
	course
ON
	course.c_id = score.c_id
GROUP BY
	student.s_id
HAVING
	SUM(IF(course.c_id = 1 OR course.c_id = 2, 1, 0)) > 1;

# 10.查询学过编号为"1"但是没有学过编号为"2"的课程的同学的信息。
SELECT
	student.*
FROM
	student
INNER JOIN
	score
ON
	score.s_id = student.s_id AND score.c_id = 1
WHERE
	student.s_id NOT IN
(
	SELECT
		student.s_id
	FROM
		student
	INNER JOIN
		score
	ON
		score.s_id = student.s_id AND score.c_id = 2
);

# 11.查询没有学全所有课程的同学的信息。
SELECT
	student.*
FROM
	student
LEFT JOIN
	score
ON
	score.s_id = student.s_id
GROUP BY
	student.s_id
HAVING
	COUNT(c_id) < (
		SELECT
			COUNT(c_id)
		FROM
			course
	);
	
# 12.查询至少有一门课与学号为"1的同学所学相同的同学的信息。
SELECT
	student.*
FROM
	student
LEFT JOIN
	score
ON
	score.s_id = student.s_id
WHERE
	c_id IN (
		SELECT
			c_id
		FROM
			score
		LEFT JOIN
			student
		ON
			score.s_id = student.s_id
		WHERE
			student.s_id = 1
	)
GROUP BY
	student.s_id;
	
# 13.查询和"1"号的同学学习的课程完全相同的其他同学的信息。
SELECT
	student.*
FROM
	student
LEFT JOIN
	score
ON
	score.s_id = student.s_id
GROUP BY
	student.s_id
HAVING
	GROUP_CONCAT(c_id) = (
		SELECT
			GROUP_CONCAT(c_id)
		FROM
			student
		LEFT JOIN
			score
		ON
			score.s_id = student.s_id
		GROUP BY
			student.s_id
		HAVING
			student.s_id = 1
	);

# 14.查询没学过"张三"老师讲授的任一门课程的学生姓名。
SELECT
	s_name
FROM
	student
WHERE
	s_id NOT IN (
		SELECT
			s_id
		FROM
			score
		INNER JOIN
			course
		ON
			course.c_id = score.c_id
		INNER JOIN
			teacher
		ON
			teacher.t_id = course.t_id AND t_name = "张三"
	);
	
# 15.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩。
SELECT
	student.s_id AS "学号",
	s_name AS "姓名",
	IFNULL(ROUND(AVG(s_score),2),0) AS "平均成绩"
FROM
	student
LEFT JOIN
	score
ON
	score.s_id = student.s_id
GROUP BY
	student.s_id
HAVING
	SUM(IF(s_score < 60, 1, 0)) >= 2;
	
SELECT st.s_id,st.s_name, sc.* FROM score AS sc LEFT JOIN student AS st ON sc.s_id = st.s_id  WHERE sc.s_score < 60 GROUP BY sc.s_id;
	
# 16.检索"1"课程分数小于60，按分数降序排列的学生信息。
SELECT
	student.*,
	s_score AS "分数"
FROM
	student
INNER JOIN
	score
ON
	score.s_id = student.s_id AND c_id = 1
WHERE
	s_score < 60
ORDER BY
	s_score DESC;

# 17.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩。
SELECT
	s_name,
	IFNULL(s1.s_score, 0) AS "语文成绩",
	IFNULL(s2.s_score, 0) AS "数学成绩",
	IFNULL(s3.s_score, 0) AS "英语成绩",
	IFNULL(ROUND(AVG(s4.s_score), 2), 0) AS "平均成绩"
FROM
	student
LEFT JOIN
	score AS s1
ON
	s1.s_id = student.s_id AND s1.c_id = 1
LEFT JOIN
	score AS s2
ON
	s2.s_id = student.s_id AND s2.c_id = 2
LEFT JOIN
	score AS s3
ON
	s3.s_id = student.s_id AND s3.c_id = 3
LEFT JOIN
	score AS s4
ON
	s4.s_id = student.s_id
GROUP BY
	student.s_id
ORDER BY
	AVG(s4.s_score) DESC;
	
# 18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率。
# 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90。
SELECT
	course.c_id AS "课程ID",
	c_name AS "课程名称",
	MAX(s_score) AS "最高分",
	MIN(s_score) AS "最低分",
	AVG(s_score) AS "平均分",
	(SUM(IF(s_score >= 60, 1, 0)) / COUNT(1)) AS "及格率",
	(SUM(IF(s_score >= 70 AND s_score < 80, 1, 0)) / COUNT(1)) AS "中等率",
	(SUM(IF(s_score >= 80 AND s_score < 90, 1, 0)) / COUNT(1)) AS "优良率",
	(SUM(IF(s_score >= 90, 1, 0)) / COUNT(1)) AS "优秀率"
FROM
	course
INNER JOIN
	score
ON
	score.c_id = course.c_id
GROUP BY
	course.c_id;

# 19.按各科成绩进行排序，并显示排名。
SELECT
	c_name AS "课程名称",
	s_score AS "分数",
	(
		CASE
			WHEN @p = s_score THEN @rank
			WHEN @p := s_score THEN @rank := @rank + 1
		END
	) AS "排名"
FROM
	(
		SELECT
			c_name,
			s_score
		FROM
			course
		INNER JOIN
			score
		ON
			score.c_id = course.c_id
		WHERE
			course.c_id = 1
		ORDER BY
			s_score DESC
	) AS tab,
	(SELECT @rank := 0, @p := NULL) AS r
UNION ALL
SELECT
	c_name AS "课程名称",
	s_score AS "分数",
	(
		CASE
			WHEN @p2 = s_score THEN @rank2
			WHEN @p2 := s_score THEN @rank2 := @rank2 + 1
		END
	) AS "排名"
FROM
	(
		SELECT
			c_name,
			s_score
		FROM
			course
		INNER JOIN
			score
		ON
			score.c_id = course.c_id
		WHERE
			course.c_id = 2
		ORDER BY
			s_score DESC
	) AS tab,
	(SELECT @rank2 := 0, @p2 := NULL) AS r
UNION ALL
SELECT
	c_name AS "课程名称",
	s_score AS "分数",
	(
		CASE
			WHEN @p3 = s_score THEN @rank3
			WHEN @p3 := s_score THEN @rank3 := @rank3 + 1
		END
	) AS "排名"
FROM
	(
		SELECT
			c_name,
			s_score
		FROM
			course
		INNER JOIN
			score
		ON
			score.c_id = course.c_id
		WHERE
			course.c_id = 3
		ORDER BY
			s_score DESC
	) AS tab,
	(SELECT @rank3 := 0, @p3 := NULL) AS r;

# 20.查询学生的总成绩并进行排名。
SELECT
	s_name AS "学生姓名",
	IFNULL(SUM(s_score), 0) AS "总成绩"
FROM
	student
LEFT JOIN
	score
ON
	score.s_id = student.s_id
GROUP BY
	student.s_id
ORDER BY
	SUM(s_score) DESC;
	
# 21.查询不同老师所教不同课程平均分从高到低显示。
SELECT
	teacher.t_name AS "老师姓名",
	c_name AS "课程名称",
	AVG(s_score) AS "平均分"
FROM
	teacher
INNER JOIN
	course
ON
	course.t_id = teacher.t_id
INNER JOIN
	score
ON
	score.c_id = course.c_id
GROUP BY
	course.c_id
ORDER BY
	AVG(s_score) DESC;
	
# 22.查询所有课程的成绩第2名到第3名的学生信息及该课程成绩。
(SELECT
	c_name AS "课程名称",
	student.*,
	s_score AS "分数"
FROM
	student
INNER JOIN
	score
ON
	student.s_id = score.s_id
INNER JOIN
	course
ON
	course.c_id = score.c_id AND course.c_id = 1
ORDER BY
	s_score DESC
LIMIT 1, 2)
UNION ALL
(SELECT
	c_name AS "课程名称",
	student.*,
	s_score AS "分数"
FROM
	student
INNER JOIN
	score
ON
	student.s_id = score.s_id
INNER JOIN
	course
ON
	course.c_id = score.c_id AND course.c_id = 2
ORDER BY
	s_score DESC
LIMIT 1, 2)
UNION ALL
(SELECT
	c_name AS "课程名称",
	student.*,
	s_score AS "分数"
FROM
	student
INNER JOIN
	score
ON
	student.s_id = score.s_id
INNER JOIN
	course
ON
	course.c_id = score.c_id AND course.c_id = 3
ORDER BY
	s_score DESC
LIMIT 1, 2);

# 23.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[0-60]及所占百分比。
SELECT
	course.c_id AS "课程编号",
	c_name AS "课程名称",
	SUM(IF(s_score > 85 AND s_score <= 100, 1, 0)) AS "[85-100]人数",
	(SUM(IF(s_score > 85 AND s_score <= 100, 1, 0)) / COUNT(1)) AS "所百分比",
	SUM(IF(s_score > 70 AND s_score <= 85, 1, 0)) AS "[70-85]人数",
	(SUM(IF(s_score > 70 AND s_score <= 85, 1, 0)) / COUNT(1)) AS "所百分比",
	SUM(IF(s_score > 60 AND s_score <= 70, 1, 0)) AS "[60-70]人数",
	(SUM(IF(s_score > 60 AND s_score <= 70, 1, 0)) / COUNT(1)) AS "所百分比",
	SUM(IF(s_score <= 60, 1, 0)) AS "[0-60]人数",
	(SUM(IF(s_score <= 60, 1, 0)) / COUNT(1)) AS "所百分比"
FROM
	score
INNER JOIN
	course
ON
	score.c_id = course.c_id
GROUP BY
	course.c_id;
	
# 24.查询学生平均成绩及其名次。
SELECT
	s_name AS "学生姓名",
	avg AS "平均分",
	(
		CASE
		WHEN @p = avg THEN @rank
		WHEN @p := avg THEN @rank := @rank + 1
		END
	) AS "排名"
FROM
	(
		SELECT
			s_name,
			ROUND(IFNULL(AVG(s_score), 0), 2) AS avg
		FROM
			student
		LEFT JOIN
			score
		ON
			score.s_id = student.s_id
		GROUP BY
			student.s_id
		ORDER BY
			AVG(s_score) DESC
	) AS tab,
	(SELECT @rank := 0, @p := NULL) AS r;
	
# 25.查询各科成绩前三名的记录。
(SELECT
	c_name AS "课程名称",
	s_score AS "分数"
FROM
	score
INNER JOIN
	course
ON
	course.c_id = score.c_id AND course.c_id = 1
ORDER BY
	s_score DESC
LIMIT 3)
UNION ALL
(SELECT
	c_name AS "课程名称",
	s_score AS "分数"
FROM
	score
INNER JOIN
	course
ON
	course.c_id = score.c_id AND course.c_id = 2
ORDER BY
	s_score DESC
LIMIT 3)
UNION ALL
(SELECT
	c_name AS "课程名称",
	s_score AS "分数"
FROM
	score
INNER JOIN
	course
ON
	course.c_id = score.c_id AND course.c_id = 3
ORDER BY
	s_score DESC
LIMIT 3)

# 26.查询每门课程被选修的学生数。
SELECT
	c_name AS "课程名称",
	COUNT(s_id) AS "人数"
FROM
	course
INNER JOIN
	score
ON
	score.c_id = course.c_id
GROUP BY
	course.c_id;
	
# 27.查询出只有两门课程的全部学生的学号和姓名。
SELECT
	student.s_id AS "学号",
	s_name AS "姓名"
FROM
	student
LEFT JOIN
	score
ON
	score.s_id = student.s_id
GROUP BY
	student.s_id
HAVING
	COUNT(score.c_id) = 2;
	
# 28.查询男生、女生人数。
SELECT
	s_sex AS "性别",
	COUNT(s_sex) AS "人数"
FROM
	student
GROUP BY
	s_sex;
	
# 29.查询名字中含有"风"字的学生信息。
SELECT
	*
FROM	
	student
WHERE
	s_name LIKE "%风%";
	
# 30.查询同名同性学生名单，并统计同名人数。
SELECT
	s_name AS "姓名",
	s_sex AS "性别",
	COUNT(s_name) AS "人数"
FROM
	student
GROUP BY
	s_name,
	s_sex
HAVING
	COUNT(s_name) > 1;

# 31.查询1990年出生的学生名单。
SELECT
	s_name AS "姓名"
FROM
	student
WHERE
	s_birth LIKE "1990%";
	
# 32.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列。
SELECT
	course.c_id AS "课程编号",
	c_name AS "课程名称",
	ROUND(AVG(s_score), 2) AS "平均成绩"
FROM
	score
INNER JOIN
	course
ON
	course.c_id = score.c_id
GROUP BY
	course.c_id
ORDER BY
	AVG(s_score) DESC,
	course.c_id ASC;
	
# 33.查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩。
SELECT
	student.s_id AS "编号",
	s_name AS "姓名",
	ROUND(AVG(s_score), 2) AS "平均成绩"
FROM
	student
LEFT JOIN
	score
ON
	student.s_id = score.s_id
GROUP BY
	student.s_id
HAVING
	AVG(s_score) >= 85;
	
# 34.查询课程名称为"数学"，且分数低于60的学生姓名和分数。
SELECT
	s_name AS "学生姓名",
	s_score AS "分数"
FROM
	student
INNER JOIN
	score
ON
	student.s_id = score.s_id
INNER JOIN
	course
ON
	course.c_id = score.c_id AND c_name = "数学"
WHERE
	s_score < 60;
	
# 35.查询所有学生的课程及分数情况。
SELECT
	student.s_id AS "学号",
	s_name AS "姓名",
	c_name AS "课程名称",
	IFNULL(s_score, 0) AS "分数"
FROM
	student
LEFT JOIN
	score
ON
	score.s_id = student.s_id
LEFT JOIN
	course
ON
	course.c_id = score.c_id;
	
# 36.查询任何一门课程成绩在70分以上的姓名、课程名称和分数。
SELECT
	s_name AS "姓名",
	c_name AS "课程名称",
	s_score AS "分数"
FROM
	student
LEFT JOIN
	score
ON
	score.s_id = student.s_id
LEFT JOIN
	course
ON
	course.c_id = score.c_id
WHERE
	s_score > 70;
		
# 37.查询不及格的课程。
SELECT
	c_name AS "课程名称",
	s_score AS "分数"
FROM
	course
INNER JOIN
	score
ON
	score.c_id = course.c_id
WHERE
	s_score < 60;
	
# 38.查询课程编号为"1程成绩在80分以上的学生的学号和姓名。
SELECT
	student.s_id AS "学号",
	s_name AS "姓名"
FROM
	student
INNER JOIN
	score
ON
	score.s_id = student.s_id AND c_id = 1 AND s_score >= 80;

# 39.求每门课程的学生人数。
SELECT
	c_name AS "课程名称",
	COUNT(s_id) AS "学生人数"
FROM
	course
INNER JOIN
	score
ON
	score.c_id = course.c_id
GROUP BY
	course.c_id;
	
# 40.查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩。
SELECT
	student.*,
	s_score AS "分数"
FROM
	student
INNER JOIN
	score
ON
	score.s_id = student.s_id
INNER JOIN
	course
ON
	course.c_id = score.c_id
INNER JOIN
	teacher
ON
	teacher.t_id = course.t_id
WHERE
	t_name = "张三"
ORDER BY
	s_score DESC
LIMIT 1;

# 41.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩。
SELECT
	student.s_id AS "学生学号",
	s_name AS "学生姓名",
	s1.c_id AS "课程编号",
	s1.s_score AS "分数"
FROM
	student
LEFT JOIN
	score AS s1
ON
	s1.s_id = student.s_id
LEFT JOIN
	course AS c1
ON
	c1.c_id = s1.c_id
WHERE
	(
		SELECT
			COUNT(1)
		FROM
			student
		LEFT JOIN
			score AS s2
		ON
			s2.s_id = student.s_id
		LEFT JOIN
			course AS c2
		ON
			c2.c_id = s2.c_id
		WHERE
			s1.s_score = s2.s_score
			AND
			c1.c_id != c2.c_id
	) > 1;
	
# 42.查询每门功成绩最好的前两名。
(
	SELECT
		c_name AS "课程名称",
		s_name AS "学生姓名",
		s_score AS "分数"
	FROM
		student
	LEFT JOIN
		score
	ON
		score.s_id = student.s_id
	LEFT JOIN
		course
	ON
		course.c_id = score.c_id
	WHERE
		course.c_id = 1
	LIMIT 2
)
UNION ALL
(
	SELECT
		c_name AS "课程名称",
		s_name AS "学生姓名",
		s_score AS "分数"
	FROM
		student
	LEFT JOIN
		score
	ON
		score.s_id = student.s_id
	LEFT JOIN
		course
	ON
		course.c_id = score.c_id
	WHERE
		course.c_id = 2
	LIMIT 2
)
UNION ALL
(
	SELECT
		c_name AS "课程名称",
		s_name AS "学生姓名",
		s_score AS "分数"
	FROM
		student
	LEFT JOIN
		score
	ON
		score.s_id = student.s_id
	LEFT JOIN
		course
	ON
		course.c_id = score.c_id
	WHERE
		course.c_id = 3
	LIMIT 2
);

# 43.统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。
SELECT
	c_id AS "课程编号",
	COUNT(s_score) AS "学生人数"
FROM
	score
GROUP BY
	c_id
HAVING
	COUNT(s_score) > 5
ORDER BY
	COUNT(s_score) DESC,
	c_id ASC;
	
# 44.检索至少选修两门课程的学生学号。
SELECT
	s_id AS "学号"
FROM
	score
GROUP BY
	s_id
HAVING
	COUNT(c_id) >= 2;
	
# 45.查询选修了全部课程的学生信息。
SELECT
	student.*
FROM
	student
LEFT JOIN
	score
ON
	score.s_id = student.s_id
GROUP BY
	student.s_id
HAVING
	COUNT(c_id) = (
		SELECT
			COUNT(1)
		FROM
			course
	);
	
# 46.查询各学生的年龄。
SELECT
	s_name AS "姓名",
	TIMESTAMPDIFF(YEAR, s_birth, NOW())
FROM
	student;
	
# 47.查询本周过生日的学生。
SELECT
	student.*
FROM
 student
WHERE
	WEEK(NOW(), 1) = WEEK(CONCAT(YEAR(s_birth), '-', MONTH(s_birth), '-', DAY(s_birth)), 1);
	
# 48.查询下周过生日的学生。
SELECT
	student.*
FROM
 student
WHERE
	WEEK(NOW(), 1) = WEEK(CONCAT(YEAR(s_birth), '-', MONTH(s_birth), '-', DAY(s_birth)), 1) - 1;
	
# 49.查询本月过生日的学生。
SELECT
	student.*
FROM
 student
WHERE
	MONTH(NOW()) = MONTH(CONCAT(YEAR(s_birth), '-', MONTH(s_birth), '-', DAY(s_birth)));
	
# 50.查询下月过生日的学生。
SELECT
	student.*
FROM
 student
WHERE
	MONTH(NOW()) = MONTH(CONCAT(YEAR(s_birth), '-', MONTH(s_birth), '-', DAY(s_birth))) - 1;

如有问题或有更优写法，欢迎大家留言提建议！