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()
BEGIN
    DECLARE 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: LOOP
        FETCH nameset INTO student_name;
        IF done THEN
            LEAVE 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 THEN
            SET 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 = CASE
            WHEN (random_month = 2 AND (random_year % 4 = 0 AND (random_year % 100 != 0 OR random_year % 400 = 0))) THEN 29
            WHEN random_month IN (4, 6, 9, 11) THEN 30
            ELSE 31
        END;
        
        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()
BEGIN
		DECLARE 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: LOOP
        FETCH courseset INTO course_name;
        IF done THEN
            LEAVE 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()
BEGIN
		DECLARE 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: LOOP
        FETCH teacherset INTO teacher_name;
        IF done THEN
            LEAVE 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()
BEGIN
		DECLARE 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 DO
				SET @temp_course_num = FLOOR(course_num / 3	* RAND());
				SET @course_idx  = 1;
				WHILE @course_idx <= @temp_course_num DO
						SET course_id = CASE 
								WHEN RAND() < 0.4 THEN  @course_idx
								WHEN RAND() < 0.5 THEN  FLOOR(course_num / 3) + @course_idx
								ELSE FLOOR(course_num	/ 3 * 2) + @course_idx
						END ;
						SET score = CASE
								WHEN 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.sid
	JOIN (SELECT sid, score FROM score WHERE cid = 2) b on id = b.sid
	WHERE a.score < b.score;
			

-- 第二题-查询平均成绩大于60分的学生的学号和平均成绩

SELECT id, name, a.avg_score FROM student
 RIGHT JOIN( SELECT sid , ROUND(AVG(score), 2) avg_score FROM score
								GROUP BY sid
								HAVING 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 student
		LEFT 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.sid
		WHERE  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.sid
				WHERE  score.cid IN (SELECT id FROM course	WHERE tid = (SELECT id FROM teacher WHERE `name` = '邓敏' ))
);


-- 文心一言优化
SELECT s.id, s.name FROM student s
	WHERE s.id NOT IN (
				SELECT sc.sid	FROM score sc
						JOIN course c ON sc.cid = c.id
						JOIN teacher t ON c.tid = t.id
					WHERE t.name = '邓敏'
);

-- 第六题-查询学过张三老师所教的所有课的同学的学号和姓名（重要）

-- 查询所教的课程数目大于1的老师
SELECT teacher.`name` , GROUP_CONCAT(course.`name`) FROM teacher 
		JOIN course on course.tid = teacher.id
		GROUP BY teacher.id, teacher.`name`
		HAVING COUNT(*) > 1;

-- 查询所选的课程数目大于1的学生
SELECT student.`name`, GROUP_CONCAT(course.`name`)  FROM student
		JOIN score ON score.sid = student.id
		JOIN course ON course.id = score.cid
		GROUP BY student.id, student.`name`
		HAVING COUNT(*) > 1;
		


-- 学过李明老师课程的学生		
SELECT student.`name`, GROUP_CONCAT(course.`name`) AS '科目', GROUP_CONCAT(score.score) '成绩' FROM student
		JOIN score ON student.id = score.sid
		JOIN course ON course.id = score.cid
		JOIN (SELECT * FROM teacher WHERE teacher.`name` = '李明') t ON t.id = course.tid
		GROUP BY student.id, student.`name`;
		
		

-- 所有科目：通过数目进行判断
SELECT s.id, s.name FROM student s
		JOIN score sc ON s.id = sc.sid
		JOIN course c ON sc.cid = c.id
		JOIN (SELECT id FROM teacher WHERE `name` = '李明' ) t ON t.id = c.tid
		GROUP 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 cid
    FROM course c
    JOIN Teacher t ON c.tid = t.id
),
StudentCourseCounts AS (
    SELECT s.id AS sid, COUNT(sc.cid) AS course_count
    FROM student s
    JOIN score sc ON s.id = sc.sid
    JOIN CoursesByTeacher ct ON sc.cid = ct.cid
    GROUP BY s.id
),
TeacherCourseCount AS (
    SELECT COUNT(*) AS total_course_count
    FROM 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 student
		JOIN (SELECT sid FROM score WHERE cid = '1') a ON a.sid = student.id
		JOIN (SELECT sid FROM score WHERE cid = '2') b ON b.sid = student.id;



-- 第七题-查询学过编号为01的课程或者学过编号为02的课程的学生的学号和姓名（重要）

SELECT student.id, student.`name`,course.`name` FROM student
		JOIN (SELECT sid,cid FROM score) a ON a.sid = student.id
		JOIN course ON course.id = a.cid  WHERE cid = '1' OR cid = '2';

SELECT student.id, student.`name`, a.`name` FROM student
		JOIN (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 student
		JOIN (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 student
		JOIN (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 student.id, student.`name`;


-- 第八题-查询课程编号为02的总成绩

SELECT course.id, course.`name` , SUM(score) FROM score
		JOIN course ON score.cid = course.id
		GROUP BY  course.id,course.`name`;
	

-- 第九题-查询所有课程成绩小于60分的学生的学号和姓名
SELECT student.id, student.`name`, GROUP_CONCAT(score.score SEPARATOR "    ") '所有成绩' FROM student
		JOIN score ON score.sid = student.id
		GROUP BY student.id, student.`name`;
		

SELECT DISTINCT student.id,student.name FROM student
		RIGHT JOIN score ON score.sid = student.id
		WHERE 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 student
		JOIN score ON student.id = score.sid
		WHERE score.cid in ( SELECT score.cid FROM score WHERE score.sid = 1 ) AND student.id <> 1;
		
		
-- 第十二题-查询和01号同学所学课程完全相同的其他同学的学号 (重点)

SELECT * FROM student
		WHERE student.id IN (
			--  选课数目与1号同学选课数目相同		
			SELECT score.sid FROM score
					GROUP 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 score
						WHERE 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.id
		JOIN course ON course.id = score.cid
		GROUP BY student.id, student.`name`;


-- 每门课程的选课学生
SELECT course.id, course.`name`, GROUP_CONCAT(student.`name` SEPARATOR " ")FROM course
		LEFT JOIN score ON score.cid = course.id
		JOIN student ON student.id = score.sid
		GROUP 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.sid
		WHERE student.id IN (
				SELECT score.sid FROM score 
					WHERE score.score < 60
					GROUP BY score.sid
					HAVING COUNT(score.score) >= 2
		)
		GROUP BY student.id;
	
	
-- 第十六题-检索01课程分数小于60，按分数降序排列的学生信息
SELECT student.*, sc.score FROM student
		RIGHT JOIN (SELECT sid, score FROM score WHERE score.cid = 1) sc ON sc.sid = student.id
		WHERE sc.score < 60
		ORDER 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 student
			LEFT JOIN score ON score.sid = student.id
			GROUP 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.id
			GROUP 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 score
		LEFT JOIN course ON course.id = score.cid
		GROUP BY score.cid, course.`name`;

 
-- 查询中位数
-- 注意：这个查询是为了演示目的而编写的，并且可能不适用于所有数据库系统。
-- 它使用了字符串聚合来模拟成绩列表，这可能会导致性能问题和精度损失。
-- 在实际生产环境中，建议使用数据库特定的功能或临时表来计算中位数。

-- TODO


-- 第十九题-按各科成绩进行排序，并显示排名（row_number,rank,dense_rank）

SELECT course.`name`, GROUP_CONCAT(score.score ORDER BY score.score) FROM student
		LEFT JOIN score ON score.sid = student.id
		JOIN course ON course.id = score.cid
		GROUP 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.id
		JOIN 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.id
		JOIN 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 score
		JOIN course ON course.id = score.cid
		JOIN student ON student.id =  score.sid;
		

-- 第二十题-查询学生的总成绩并进行排名

-- 平均成绩，总成绩科目数相差太多，无意义
SELECT RANK() over (ORDER BY AVG(score.score)  DESC), student.`name`, AVG(score.score) AS avg_score FROM student
		JOIN score ON score.sid = student.id
		GROUP 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 student
		LEFT JOIN score ON score.sid = student.id
		GROUP 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 teacher
		LEFT JOIN course ON course.tid = teacher.id 
		JOIN score ON score.cid = course.id
	GROUP 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 student
				LEFT JOIN score ON score.sid = student.id
				JOIN 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.id
						GROUP BY student.id, student.`name`
						ORDER BY  avg_sc DESC
		) rk ;


-- 第二十六题 -（第25题与第22题类似）查询每门课程被选修的学生数
SELECT course.`name`, COUNT(score.sid) FROM course
		LEFT JOIN score ON score.cid = course.id
		GROUP BY course.id;


-- 第二十七题 - 查询出只有两门课程的全部学生的学号和姓名

SELECT student.`name`, GROUP_CONCAT(course.`name`) FROM student
		JOIN score ON score.sid = student.id
		JOIN course ON course.id  = score.cid
		GROUP BY score.sid, student.`name`
		HAVING COUNT(DISTINCT score.cid) = 2;
		
		
		
-- 第二十八题 - 查询男生、女生人数

SELECT student.sex, COUNT(student.id) FROM student
		GROUP BY student.sex;

-- 第二十九题-查询名字中含有风字的学生信息

SELECT * FROM student
		WHERE student.`name` LIKE '%雪%';


-- 第三十一题-（30题没有）查询1990年出生的学生名单（重点）


SELECT * FROM student
		WHERE YEAR(birth) = 1994;



-- 第三十二题-查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩


SELECT student.id, student.`name`, AVG(score.score) FROM student
		LEFT JOIN score ON score.sid = student.id
		GROUP BY student.id, student.`name`
		HAVING AVG(score.score) > 70;


-- 第三十三题-查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列


SELECT course.`name`,AVG(score.score) AS avg_sc, score.cid FROM score
		LEFT JOIN course ON course.id = score.cid
		GROUP BY score.cid, course.`name`
		ORDER BY avg_sc ASC, score.cid DESC;
		
		
-- 第三十四题-查询课程名称为数学，且分数低于60的学生姓名和分数


SELECT * FROM student 
		LEFT JOIN score ON score.sid = student.id
		WHERE score.cid = (SELECT course.id FROM course WHERE course.`name` = '数学') AND score.score < 60;
		
		
-- 第三十五题-查询所有学生的课程及分数情况（重点）
-- 第三十六题-查询课程成绩在70分以上课程名称,分数和学生姓名
-- 第三十七题-查询不及格的课程并按课程号从大到小排列
-- 第三十八题-查询课程编号为03且课程成绩在80分以上的学生的学号和姓名
-- 第三十九题-求每门课程的学生人数

-- 第四十题-查询选修张三老师所授课程的学生中成绩最高的学生姓名及其成绩

SELECT * FROM student
		JOIN score ON score.sid = student.id
		JOIN course ON course.id = score.cid
		WHERE course.tid = (SELECT id FROM teacher WHERE teacher.`name` = '赵敏')
		ORDER BY score.score;
		
-- 好吧，一个老师一门课		
SELECT teacher.id, GROUP_CONCAT(course.`name`) FROM teacher
		LEFT JOIN course ON course.id = teacher.id
		GROUP BY teacher.id;
		
		
-- 第四十一题-查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

SELECT ROUND(score.score),GROUP_CONCAT(course.`name`), GROUP_CONCAT(student.`name`) FROM student
		LEFT JOIN score ON score.sid = student.id
		JOIN course ON course.id = score.cid
		GROUP 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;