SQL面试50题 数据库准备（存储过程）

数据表关系图

数据表

CREATE TABLE `student` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(255) NOT NULL,`sex` enum('female','male') NOT NULL,`birth` date NOT NULL,`credit` float(5,2) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;CREATE TABLE `teacher` (`id` int(11) NOT NULL,`name` varchar(255) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `course` (`id` int(11) NOT NULL,`name` varchar(255) DEFAULT NULL,`tid` int(11) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `score` (`sid` int(11) NOT NULL,`cid` int(11) NOT NULL,`score` float(5,2) DEFAULT NULL,PRIMARY KEY (`sid`,`cid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

添加学生表数据

存储过程，添加 学生表（student）

DELETE FROM student;
ALTER TABLE student AUTO_INCREMENT = 1;DROP TABLE IF EXISTS temp_stu;
CREATE TEMPORARY TABLE temp_stu(name VARCHAR(255)
);INSERT INTO `temp_stu` (`name`) VALUES ('张三'),('李四'),('王五'),('赵六'),('牛金霞'),('闫景立'),('孙浩'),('周莉'),('吴鹏'),('郑洁'),('陈婷婷'),('刘洋'),('高敏'),('黄磊'),('林静'),('郭涛'),('何婉如'),('梁志远'),('罗芳芳'),('谢霆锋'),('唐嫣'),('韩雪'),('冯小刚'),('程思远');DROP PROCEDURE IF EXISTS insert_student_records;DELIMITER //CREATE PROCEDURE insert_student_records()
BEGINDECLARE done INT DEFAULT FALSE;DECLARE student_name VARCHAR(255);DECLARE student_sex ENUM('female', 'male');DECLARE random_year INT;DECLARE random_month INT;DECLARE random_day INT;DECLARE days_in_month INT;DECLARE credit FLOAT(5,2);-- 声明游标DECLARE nameset CURSOR FOR SELECT name FROM temp_stu;-- 声明继续处理程序DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;OPEN nameset;read_loop: LOOPFETCH nameset INTO student_name;IF done THENLEAVE read_loop;END IF;SET student_sex = IF(RAND() < 0.5, 'female', 'male');-- 随机选择一个年份（1994, 1995, 1997, 1998, 1999）SET random_year = FLOOR(1 + RAND() * 5) + 1993; IF random_year = 1996 THENSET random_year = CASE FLOOR(RAND() * 4) + 1994 WHEN 1996 THEN 1994 + FLOOR(RAND() * 4) ELSE random_year - 1 END; END IF;SET random_month = FLOOR(1 + RAND() * 12);-- 计算该月的天数（考虑闰年）SET days_in_month = CASEWHEN (random_month = 2 AND (random_year % 4 = 0 AND (random_year % 100 != 0 OR random_year % 400 = 0))) THEN 29WHEN random_month IN (4, 6, 9, 11) THEN 30ELSE 31END;SET random_day = FLOOR(1 + RAND() * days_in_month);SET @birth_date = CONCAT(random_year, '-', LPAD(random_month, 2, '0'), '-', LPAD(random_day, 2, '0'));SET credit = CASE WHEN RAND() < 0.1 THEN 50 - 30 * RAND() WHEN RAND() < 0.7 THEN 50 + 30 * RAND()ELSE 80 + 20 * RAND()	END;-- 插入到student表INSERT INTO student(name, sex, birth, credit) VALUES(student_name, student_sex, @birth_date, ROUND(credit, 2));END LOOP;CLOSE nameset;END //DELIMITER ;-- SHOW CREATE PROCEDURE insert_student_records;
CALL insert_student_records();DROP PROCEDURE IF EXISTS insert_student_records;
SELECT * FROM student;

其他表

-- 清理环境
DELETE FROM course;
DELETE FROM teacher;
DELETE FROM score;
DROP PROCEDURE IF EXISTS insert_course_records;
DROP PROCEDURE IF EXISTS insert_teacher_records;
DROP PROCEDURE IF EXISTS insert_score_records;DROP TABLE IF EXISTS temp_course;
CREATE TEMPORARY TABLE temp_course (value VARCHAR(255)
);-- 插入数据到临时表
INSERT INTO temp_course (value) VALUES ('语文'), ('数学'), ('英语'), ('政治'), ('地理'), ('历史'), ('物理'), ('化学'), ('生物'), ('C++'), ('Python'), ('机器学习'), ('强化学习'), ('自然语言处理'), ('关联规则挖掘');DROP TABLE IF EXISTS temp_teacher;
CREATE TEMPORARY TABLE temp_teacher (value VARCHAR(255)
);-- 插入数据到临时表
INSERT INTO temp_teacher (value) VALUES ('李明'), ('王芳'), ('张伟'), ('赵敏'), ('刘洋'), ('陈静'), ('周杰'), ('孙磊'),('徐丽'), ('朱强'), ('邓敏'), ('韩雪');DELIMITER //CREATE PROCEDURE insert_course_records()
BEGINDECLARE done INT DEFAULT FALSE;DECLARE course_id INT DEFAULT 1;DECLARE course_name VARCHAR(255);DECLARE course_num	INT DEFAULT 15;DECLARE teacher_num INT DEFAULT 12;-- 声明游标DECLARE courseset CURSOR FOR SELECT value FROM temp_course;-- 声明继续处理程序DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;OPEN courseset;read_loop: LOOPFETCH courseset INTO course_name;IF done THENLEAVE read_loop;END IF;SET @teacher_id = CEILING(teacher_num * RAND());INSERT INTO course(id, name, tid) VALUES(course_id, course_name, @teacher_id);SET course_id = course_id +1;END LOOP read_loop;CLOSE courseset;END //CREATE PROCEDURE insert_teacher_records()
BEGINDECLARE done INT DEFAULT FALSE;DECLARE teacher_id INT DEFAULT 1;DECLARE teacher_name VARCHAR(255);DECLARE teacherset CURSOR FOR SELECT value FROM temp_teacher;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;OPEN teacherset;read_loop: LOOPFETCH teacherset INTO teacher_name;IF done THENLEAVE read_loop;END IF;INSERT INTO teacher(id, name) VALUES(teacher_id, teacher_name);SET teacher_id = teacher_id + 1;END LOOP read_loop;CLOSE teacherset;END //CREATE PROCEDURE insert_score_records()
BEGINDECLARE student_num INT DEFAULT 24;DECLARE course_num INT DEFAULT 15;DECLARE student_id INT DEFAULT 1;DECLARE course_id INT DEFAULT 1;DECLARE score FLOAT(5,2);WHILE student_id <= student_num DOSET @temp_course_num = FLOOR(course_num / 3	* RAND());SET @course_idx  = 1;WHILE @course_idx <= @temp_course_num DOSET course_id = CASE WHEN RAND() < 0.4 THEN  @course_idxWHEN RAND() < 0.5 THEN  FLOOR(course_num / 3) + @course_idxELSE FLOOR(course_num	/ 3 * 2) + @course_idxEND ;SET score = CASEWHEN RAND() < 0.3 THEN ROUND(50 - 20 * RAND(), 2)WHEN RAND() < 0.8 THEN ROUND(50 + 30 * RAND(), 2)ELSE ROUND(80 + 20 * RAND(), 2)END ;INSERT INTO score(sid,cid, score) VALUES(student_id, course_id, score);SET @course_idx = @course_idx + 1; END WHILE;SET student_id = student_id + 1;END WHILE;
END//DELIMITER ;CALL insert_course_records();
CALL insert_teacher_records();
CALL insert_score_records();DROP PROCEDURE IF EXISTS insert_course_records;
DROP PROCEDURE IF EXISTS insert_teacher_records;
DROP PROCEDURE IF EXISTS insert_score_records;SELECT * from course;
SELECT * from teacher;
SELECT * FROM score;

查询示例

• 查询语文成绩比数学成绩高的学生

-- 第一题-查询课程编号为01的课程比02的课程成绩低的所有学生的学号（重要）SELECT id, name, a.score '语文' , b.score '数学' FROM student JOIN (SELECT sid, score FROM score WHERE cid = 1) a on id = a.sidJOIN (SELECT sid, score FROM score WHERE cid = 2) b on id = b.sidWHERE a.score < b.score;-- 第二题-查询平均成绩大于60分的学生的学号和平均成绩SELECT id, name, a.avg_score FROM studentRIGHT JOIN( SELECT sid , ROUND(AVG(score), 2) avg_score FROM scoreGROUP BY sidHAVING avg_score > 60) a on student.id = a.sid;-- 第三题-查询所有学生的学号、姓名、选课数、总成绩SELECT id, name, IF (ISNULL(a.selected_course),0,a.selected_course) '选课数', IF(ISNULL(a.sum_score),0,a.sum_score) '总成绩' FROM studentLEFT JOIN (SELECT sid, count(score)	selected_course, SUM(score) sum_score FROM score GROUP BY sid) a on student.id = a.sid;-- 第四题-查询姓猴的老师的个数SELECT COUNT(*) FROM teacher WHERE `name` LIKE '朱%';-- 第五题-查询没学过张三老师课的学生的学号和姓名（重要）-- SELECT  tid, temp.`name` ,GROUP_CONCAT(course.name) FROM course JOIN (SELECT * FROM teacher) temp  ON course.tid = temp.id GROUP BY tid,temp.`name`;
-- 邓敏老师SELECT id, `name` FROM student WHERE id NOT IN (SELECT sid FROM score WHERE cid IN (SELECT id FROM course WHERE tid = (SELECT id from teacher WHERE `name` = '邓敏') )
);-- 学过邓敏课程的学生		
SELECT id, name, score.cid  FROM student	JOIN score ON id = score.sidWHERE  score.cid IN (SELECT id FROM course	WHERE tid = (SELECT id FROM teacher WHERE `name` = '邓敏' ));SELECT id, name FROM student WHERE id NOT IN(-- 学过邓敏课程的学生		SELECT id  FROM student	JOIN score ON id = score.sidWHERE  score.cid IN (SELECT id FROM course	WHERE tid = (SELECT id FROM teacher WHERE `name` = '邓敏' ))
);-- 文心一言优化
SELECT s.id, s.name FROM student sWHERE s.id NOT IN (SELECT sc.sid	FROM score scJOIN course c ON sc.cid = c.idJOIN teacher t ON c.tid = t.idWHERE t.name = '邓敏'
);-- 第六题-查询学过张三老师所教的所有课的同学的学号和姓名（重要）-- 查询所教的课程数目大于1的老师
SELECT teacher.`name` , GROUP_CONCAT(course.`name`) FROM teacher JOIN course on course.tid = teacher.idGROUP BY teacher.id, teacher.`name`HAVING COUNT(*) > 1;-- 查询所选的课程数目大于1的学生
SELECT student.`name`, GROUP_CONCAT(course.`name`)  FROM studentJOIN score ON score.sid = student.idJOIN course ON course.id = score.cidGROUP BY student.id, student.`name`HAVING COUNT(*) > 1;-- 学过李明老师课程的学生		
SELECT student.`name`, GROUP_CONCAT(course.`name`) AS '科目', GROUP_CONCAT(score.score) '成绩' FROM studentJOIN score ON student.id = score.sidJOIN course ON course.id = score.cidJOIN (SELECT * FROM teacher WHERE teacher.`name` = '李明') t ON t.id = course.tidGROUP BY student.id, student.`name`;-- 所有科目：通过数目进行判断
SELECT s.id, s.name FROM student sJOIN score sc ON s.id = sc.sidJOIN course c ON sc.cid = c.idJOIN (SELECT id FROM teacher WHERE `name` = '李明' ) t ON t.id = c.tidGROUP BY s.id, s.`name`HAVING COUNT(*) = (SELECT COUNT(*) FROM course JOIN (SELECT id FROM teacher WHERE `name` = '李明') t ON t.id = course.tid);-- 文心一言WITH Teacher AS (SELECT id FROM teacher WHERE `name` = '李明'
),
CoursesByTeacher AS (SELECT c.id AS cidFROM course cJOIN Teacher t ON c.tid = t.id
),
StudentCourseCounts AS (SELECT s.id AS sid, COUNT(sc.cid) AS course_countFROM student sJOIN score sc ON s.id = sc.sidJOIN CoursesByTeacher ct ON sc.cid = ct.cidGROUP BY s.id
),
TeacherCourseCount AS (SELECT COUNT(*) AS total_course_countFROM CoursesByTeacher
)
SELECT sc.sid AS id, s.name
FROM StudentCourseCounts sc
JOIN student s ON sc.sid = s.id
WHERE sc.course_count = (SELECT total_course_count FROM TeacherCourseCount);-- 第七题-查询学过编号为01的课程并且也学过编号为02的课程的学生的学号和姓名（重要）SELECT student.id, student.`name` FROM studentJOIN (SELECT sid FROM score WHERE cid = '1') a ON a.sid = student.idJOIN (SELECT sid FROM score WHERE cid = '2') b ON b.sid = student.id;-- 第七题-查询学过编号为01的课程或者学过编号为02的课程的学生的学号和姓名（重要）SELECT student.id, student.`name`,course.`name` FROM studentJOIN (SELECT sid,cid FROM score) a ON a.sid = student.idJOIN course ON course.id = a.cid  WHERE cid = '1' OR cid = '2';SELECT student.id, student.`name`, a.`name` FROM studentJOIN (SELECT sid, course.`name` FROM score JOIN course ON course.id = score.cid  WHERE cid = '1' OR cid = '2' ) a ON a.sid = student.id;-- 对一个学生有多门选课时，只显示一次名称--  无法达成效果
-- DISTINCT关键字的使用方式有一点小瑕疵。在SQL中，DISTINCT是用来对整个结果集进行去重的，而不是单独对某一个字段进行去重。 
SELECT DISTINCT(student.id), student.`name`, a.`name` FROM studentJOIN (SELECT sid, course.`name` FROM score JOIN course ON course.id = score.cid  WHERE cid = '1' OR cid = '2' ) a ON a.sid = student.id;-- GROUP BY
SELECT DISTINCT(student.id), student.`name`, GROUP_CONCAT(a.`name`) '所选科目' FROM studentJOIN (SELECT sid, course.`name` FROM score JOIN course ON course.id = score.cid  WHERE cid = '1' OR cid = '2' ) a ON a.sid = student.idGROUP BY student.id, student.`name`;-- 第八题-查询课程编号为02的总成绩SELECT course.id, course.`name` , SUM(score) FROM scoreJOIN course ON score.cid = course.idGROUP BY  course.id,course.`name`;-- 第九题-查询所有课程成绩小于60分的学生的学号和姓名
SELECT student.id, student.`name`, GROUP_CONCAT(score.score SEPARATOR "    ") '所有成绩' FROM studentJOIN score ON score.sid = student.idGROUP BY student.id, student.`name`;SELECT DISTINCT student.id,student.name FROM studentRIGHT JOIN score ON score.sid = student.idWHERE student.id NOT IN (SELECT student.id FROM student JOIN(SELECT sid FROM score	WHERE score.score >= 60) a ON a.sid = student.id 
);-- 文心一言
-- MAX 函数的使用, ok ...-- 不考虑没选课
SELECT student.id, student.name
FROM student
LEFT JOIN score ON student.id = score.sid
GROUP BY student.id, student.name
HAVING MAX(score.score) < 60;-- 没选课的学生
SELECT student.id, student.name
FROM student
LEFT JOIN score ON student.id = score.sid
GROUP BY student.id, student.name
HAVING COUNT(score.score) = 0;-- 考虑没有选课的学生
SELECT student.id, student.name
FROM student
LEFT JOIN score ON student.id = score.sid
GROUP BY student.id, student.name
HAVING COALESCE(MAX(score.score), -9999) < 60; -- 使用一个远低于任何可能分数的值（如-9999）作为没有分数时的默认值-- 第十课-查询没有学全所有课的学生的学号和姓名（重点）
-- 略过，GROUP BY 通过数目比对-- 第十一题-查询至少有一门课与学号为01的学生所学课程相同的学生的学号和姓名（重点）SELECT DISTINCT student.id, student.`name` FROM studentJOIN score ON student.id = score.sidWHERE score.cid in ( SELECT score.cid FROM score WHERE score.sid = 1 ) AND student.id <> 1;-- 第十二题-查询和01号同学所学课程完全相同的其他同学的学号 (重点)SELECT * FROM studentWHERE student.id IN (--  选课数目与1号同学选课数目相同		SELECT score.sid FROM scoreGROUP BY score.sid HAVING COUNT(score.cid) = (SELECT COUNT(DISTINCT score.cid) FROM score WHERE score.sid = 12)) AND student.id NOT IN (-- 选择了1号同学不同的科目 		SELECT score.sid FROM scoreWHERE score.cid NOT IN (SELECT score.cid FROM score WHERE score.sid = 12)		)AND student.id != 12;-- 每位同学的选课情况
SELECT student.id, student.name, GROUP_CONCAT(course.`name` SEPARATOR "  ") FROM student LEFT JOIN score ON score.sid = student.idJOIN course ON course.id = score.cidGROUP BY student.id, student.`name`;-- 每门课程的选课学生
SELECT course.id, course.`name`, GROUP_CONCAT(student.`name` SEPARATOR " ")FROM courseLEFT JOIN score ON score.cid = course.idJOIN student ON student.id = score.sidGROUP BY course.id, course.`name`;-- 第十五题-（13题前面有重复，14题没有）查询两门及其以上不及格课程的同学的学号姓名及其平均成绩（重点）SELECT student.id, student.name, ROUND(AVG(score.score), 2) FROM student JOIN score ON student.id = score.sidWHERE student.id IN (SELECT score.sid FROM score WHERE score.score < 60GROUP BY score.sidHAVING COUNT(score.score) >= 2)GROUP BY student.id;-- 第十六题-检索01课程分数小于60，按分数降序排列的学生信息
SELECT student.*, sc.score FROM studentRIGHT JOIN (SELECT sid, score FROM score WHERE score.cid = 1) sc ON sc.sid = student.idWHERE sc.score < 60ORDER BY sc.score DESC;-- 第十七题-（case when）按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩（重点 ）SELECT student.id, student.`name`, ROUND(AVG(COALESCE(score.score, 0)), 2)  '平均成绩', GROUP_CONCAT(COALESCE(score.score, 0) SEPARATOR " 、 ") FROM studentLEFT JOIN score ON score.sid = student.idGROUP BY student.id, student.`name`;-- max 考虑补考情况，一门课两次成绩
SELECT student.id, student.`name`, ROUND(AVG(score.score), 2) '平均成绩', MAX(CASE WHEN score.cid = 1 THEN score.score ELSE NULL END) '语文',MAX(CASE WHEN score.cid = 2 THEN score.score ELSE NULL END) '数学', MAX(CASE WHEN score.cid = 3 THEN score.score ELSE NULL END) '英语'  FROM student		LEFT JOIN score ON score.sid = student.idGROUP BY student.id, student.`name`ORDER BY AVG(score.score) DESC;-- 第十八题-查询各科成绩最高分,最低分,平均分,及格率,中等率,优良率,优秀率（重点）SELECT course.`name`, MAX(score.score), MIN(score.score), AVG(score.score), COUNT(CASE WHEN score.score >= 60 THEN 1 ELSE NULL END) / COUNT(score.score)  FROM scoreLEFT JOIN course ON course.id = score.cidGROUP BY score.cid, course.`name`;-- 查询中位数
-- 注意：这个查询是为了演示目的而编写的，并且可能不适用于所有数据库系统。
-- 它使用了字符串聚合来模拟成绩列表，这可能会导致性能问题和精度损失。
-- 在实际生产环境中，建议使用数据库特定的功能或临时表来计算中位数。-- TODO-- 第十九题-按各科成绩进行排序，并显示排名（row_number,rank,dense_rank）SELECT course.`name`, GROUP_CONCAT(score.score ORDER BY score.score) FROM studentLEFT JOIN score ON score.sid = student.idJOIN course ON course.id = score.cidGROUP BY score.cid, course.`name`-- ROW_NUMBER() 为每个学生的成绩分配一个唯一的序号，这个序号在每个课程内部是唯一的，并且是根据成绩降序排列的。
-- RANK()  如果有两个学生的成绩相同，他们将获得相同的排名，但下一个学生的排名将跳过（例如，1, 2, 2, 4）。
-- DENSE_RANK() 在这个排名中，如果有两个学生的成绩相同，他们将获得相同的排名，并且下一个学生的排名不会跳过（例如，1, 2, 2, 3）。-- 按个人征信credit对学生进行排名
SELECT  ROW_NUMBER() over (ORDER BY credit DESC) , student.* FROM student; -- 分区函数  PARTITION BY
-- <窗口函数> over ( partition by<用于分组的列名> order by <用于排序的列名>)-- 窗口函数
-- 专用窗口函数: rank(), dense_rank(), row_number()
-- 聚合函数 : sum(), max(), min(), count(), avg() 等-- 专用窗口函数
-- over(partition by type order by price desc)
-- 先对 type 中相同的进行分区，在 type 中相同的情况下对 price 进行排序-- 每个学生的科目成绩排序
SELECT student.`name`, course.`name`  '科目', RANK() over(PARTITION BY score.sid ORDER BY score.score DESC) '排名', score.score FROM student JOIN score ON score.sid = student.idJOIN course ON course.id = score.cid;-- 聚合函数作为窗口函数-- 窗口内的累计总分，实际使用场景？？？
-- TODO
SELECT student.`name`, course.`name`  '科目', SUM(score.score) over(PARTITION BY score.sid ORDER BY score.score DESC) '排名', score.score FROM student JOIN score ON score.sid = student.idJOIN course ON course.id = score.cid;-- 科目名次
SELECT DENSE_RANK() OVER (PARTITION BY score.cid ORDER BY ROUND(score.score) DESC) '名次',course.name '科目', student.`name` '学生姓名', ROUND(score.score) '分数' FROM scoreJOIN course ON course.id = score.cidJOIN student ON student.id =  score.sid;-- 第二十题-查询学生的总成绩并进行排名-- 平均成绩，总成绩科目数相差太多，无意义
SELECT RANK() over (ORDER BY AVG(score.score)  DESC), student.`name`, AVG(score.score) AS avg_score FROM studentJOIN score ON score.sid = student.idGROUP BY score.sid, student.`name`ORDER BY avg_score DESC;-- 	未选课视为0分
SELECT RANK() over (ORDER BY AVG(COALESCE(score.score, 0))  DESC), student.`name`, AVG(COALESCE(score.score, 0)) AS avg_score FROM studentLEFT JOIN score ON score.sid = student.idGROUP BY student.id, student.`name`ORDER BY avg_score DESC;-- 第二十一题-查询不同老师所教不同课程平均分从高到低显示SELECT teacher.`name`, AVG(CASE WHEN score.cid = 1 THEN score.score ELSE 0 END) '语文平均成绩', GROUP_CONCAT(DISTINCT(course.`name`)) , AVG(score.score)   FROM teacherLEFT JOIN course ON course.tid = teacher.id JOIN score ON score.cid = course.idGROUP BY teacher.id, teacher.`name`, score.cid;-- 第二十二题-查询所有课程的成绩第2名到第3名的学生信息及该课程成绩（重点）
-- TODO
SELECT * FROM (SELECT RANK() over( PARTITION BY course.id ORDER BY score.score DESC) as rk, student.`name` as sname, course.`name` as cname, score.score FROM studentLEFT JOIN score ON score.sid = student.idJOIN course ON course.id = score.cid
) temp
WHERE temp.rk in (2,3);-- 第二十四题-查询学生平均成绩及其名次
SELECT ROW_NUMBER() over (ORDER BY rk.avg_sc DESC), rk.sname, rk.avg_sc		FROM(SELECT 	student.`name` as sname, AVG(score.score) avg_sc FROM student LEFT JOIN score ON score.sid = student.idGROUP BY student.id, student.`name`ORDER BY  avg_sc DESC) rk ;-- 第二十六题 -（第25题与第22题类似）查询每门课程被选修的学生数
SELECT course.`name`, COUNT(score.sid) FROM courseLEFT JOIN score ON score.cid = course.idGROUP BY course.id;-- 第二十七题 - 查询出只有两门课程的全部学生的学号和姓名SELECT student.`name`, GROUP_CONCAT(course.`name`) FROM studentJOIN score ON score.sid = student.idJOIN course ON course.id  = score.cidGROUP BY score.sid, student.`name`HAVING COUNT(DISTINCT score.cid) = 2;-- 第二十八题 - 查询男生、女生人数SELECT student.sex, COUNT(student.id) FROM studentGROUP BY student.sex;-- 第二十九题-查询名字中含有风字的学生信息SELECT * FROM studentWHERE student.`name` LIKE '%雪%';-- 第三十一题-（30题没有）查询1990年出生的学生名单（重点）SELECT * FROM studentWHERE YEAR(birth) = 1994;-- 第三十二题-查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩SELECT student.id, student.`name`, AVG(score.score) FROM studentLEFT JOIN score ON score.sid = student.idGROUP BY student.id, student.`name`HAVING AVG(score.score) > 70;-- 第三十三题-查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列SELECT course.`name`,AVG(score.score) AS avg_sc, score.cid FROM scoreLEFT JOIN course ON course.id = score.cidGROUP BY score.cid, course.`name`ORDER BY avg_sc ASC, score.cid DESC;-- 第三十四题-查询课程名称为数学，且分数低于60的学生姓名和分数SELECT * FROM student LEFT JOIN score ON score.sid = student.idWHERE score.cid = (SELECT course.id FROM course WHERE course.`name` = '数学') AND score.score < 60;-- 第三十五题-查询所有学生的课程及分数情况（重点）
-- 第三十六题-查询课程成绩在70分以上课程名称,分数和学生姓名
-- 第三十七题-查询不及格的课程并按课程号从大到小排列
-- 第三十八题-查询课程编号为03且课程成绩在80分以上的学生的学号和姓名
-- 第三十九题-求每门课程的学生人数-- 第四十题-查询选修张三老师所授课程的学生中成绩最高的学生姓名及其成绩SELECT * FROM studentJOIN score ON score.sid = student.idJOIN course ON course.id = score.cidWHERE course.tid = (SELECT id FROM teacher WHERE teacher.`name` = '赵敏')ORDER BY score.score;-- 好吧，一个老师一门课		
SELECT teacher.id, GROUP_CONCAT(course.`name`) FROM teacherLEFT JOIN course ON course.id = teacher.idGROUP BY teacher.id;-- 第四十一题-查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩SELECT ROUND(score.score),GROUP_CONCAT(course.`name`), GROUP_CONCAT(student.`name`) FROM studentLEFT JOIN score ON score.sid = student.idJOIN course ON course.id = score.cidGROUP BY ROUND(score.score)HAVING COUNT(DISTINCT score.cid) > 1;-- 第四十三题 -（42题有类似题）统计每门课程的学生选修人数
-- 第四十四题-检索至少选修两门课程的学生学号
-- 第四十五题-查询选修了全部课程的学生信息-- 第四十六题-查询各学生的年龄-- 日期操作
SELECT name, TIMESTAMPDIFF(YEAR, birth, CURDATE())  as age FROM student ORDER BY age; -- 第四十七题-查询没学过张三老师讲授的任一门课程的学生姓名
-- 第四十八题-查询下周过生日的同学SELECT * FROM student ORDER BY MONTH(birth);-- 查找下周过生日的学生
SELECT * FROM student WHERE WEEK(NOW())+2 = WEEK(birth);
SELECT * FROM student WHERE WEEK(NOW())+1 = WEEK(CONCAT(YEAR(NOW()),"-",SUBSTR(birth,6,5)));-- 第四十九题-查询本月过生日的人
-- 第五十题-查询下一个月过生日的同学
-- 跨年
-- 查到下个月过生日的学生
SELECT * FROM student WHERE MONTH(NOW()) = MONTH(birth);
SELECT * FROM student WHERE MONTH(birth) - MONTH(NOW()) = 1;
SELECT * FROM student WHERE MONTH(birth) = CASE WHEN MONTH(NOW()) = 12 THEN 1 ELSE MONTH(NOW()) + 1 END;-- 逻辑错误
-- 在MySQL中，使用TIMESTAMPDIFF函数计算两个日期之间的月份差异时，函数会返回两个日期之间完整的月份数。
SELECT TIMESTAMPDIFF(MONTH, '2024-01-31', '2024-02-29');
SELECT TIMESTAMPDIFF(MONTH, '2024-01-31', '2024-03-01');SELECT CONCAT(YEAR(CURDATE()), "-", SUBSTR("1994-12-06", 6,5 ));
SELECT * FROM student WHERE TIMESTAMPDIFF(MONTH, STR_TO_DATE(CONCAT(YEAR(CURDATE()), "-", SUBSTR(birth, 6, 5)),"%Y-%m-%d"), CURDATE())=10;