一、表结构图
二、创建测试数据
-- 建表
-- 学生表
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('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
-- 课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
-- 教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
-- 成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);
三、练习题
以下为面试50题
其中重点为:1/2/5/6/7/10/11/12/13/15/17/18/19/22/23/25/31/35/36/40/41/42/45/46 共16题
超级重点 18和23、 22和25 、 41、46
-- 1.查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号(重点)
SELECT
student.*,
a.s_score,
b.s_score
FROM
( SELECT * FROM score WHERE c_id = '01' ) a
INNER JOIN ( SELECT * FROM score WHERE c_id = '02' ) b ON a.s_id = b.s_id
INNER JOIN student ON student.s_id = a.s_id
WHERE
a.s_score > b.s_score;
-- 2.查询平均成绩大于60分的学生的学号和平均成绩(简单,第二道重点)
SELECT
score.s_id,
AVG( IFNULL( score.s_score, 0 ) )
FROM
score
GROUP BY
score.s_id
HAVING
AVG( score.s_score ) > 60;
-- 3.查询所有学生的学号、姓名、选课数、总成绩(不重要)
SELECT
st.s_id,
st.s_name,
COUNT( sc.s_id ),
SUM( sc.s_score )
FROM
student st
INNER JOIN score sc ON st.s_id = sc.s_id
GROUP BY
st.s_id;
-- 4. 查询姓“张”的老师的个数(不重要)
SELECT
COUNT( 1 )
FROM
teacher
WHERE
teacher.t_name LIKE '张%';
-- 5、查询没学过“张三”老师课的学生的学号、姓名(重点)
SELECT
s_id,
s_name
FROM
student
WHERE
s_id NOT IN (
SELECT
s_id
FROM
score sc
INNER JOIN course co ON sc.c_id = co.c_id
INNER JOIN teacher te ON te.t_id = co.t_id
WHERE
te.t_name = '张三'
);
-- 6、查询学过“张三”老师所教的所有课的同学的学号、姓名(重点)
SELECT
st.s_id,
st.s_name
FROM
student st,
score s
WHERE
st.s_id = s.s_id
AND s.c_id IN ( SELECT c.c_id FROM teacher t, course c WHERE t.t_id = c.t_id AND t.t_name = '张三' )
GROUP BY
st.s_id
HAVING
COUNT( st.s_id ) = ( SELECT COUNT( 1 ) FROM teacher INNER JOIN course ON course.t_id = teacher.t_id WHERE teacher.t_name = '张三' );
-- 7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名(重点)
SELECT
s_id,
s_name
FROM
student
WHERE
s_id IN ( SELECT s_id FROM score WHERE c_id = '01' )
AND s_id IN ( SELECT s_id FROM score WHERE c_id = '02' );
-- 8、查询课程编号为“02”的总成绩(不重点)
SELECT
SUM( s_score )
FROM
score
WHERE
c_id = '02';
-- 9、查询所有课程成绩小于60分的学生的学号、姓名
SELECT
s_id,
s_name
FROM
student
WHERE
s_id IN ( SELECT s_id FROM score WHERE s_score < 60 );
-- 10、查询没有学全所有课的学生的学号、姓名(重点)
SELECT
s_id,
s_name
FROM
student
WHERE
s_id IN (
SELECT
s_id
FROM
score
GROUP BY
s_id
HAVING
COUNT( s_id ) = ( SELECT COUNT( 1 ) FROM course ));
-- 11、查询至少有一门课与学号为“02”的学生所学课程相同的学生的学号和姓名(重点)
SELECT
s_id,
s_name
FROM
student
WHERE
s_id IN (
SELECT DISTINCT
s_id
FROM
score
WHERE
c_id IN ( SELECT c_id FROM score WHERE s_id = '02' ));
-- 12、查询和“01”号同学所学课程完全相同的其他同学的学号(重点)
SELECT
s_id
FROM
score
WHERE
s_id <> '01'
AND c_id IN ( SELECT c_id FROM score WHERE s_id = '01' )
GROUP BY
s_id
HAVING
COUNT( s_id ) = ( SELECT COUNT( 1 ) FROM score WHERE s_id = '01' );
-- 13、查询没学过"张三"老师讲授的任一门课程的学生姓名 和5题一样(重点,能做出来)
SELECT
s_id,
s_name
FROM
student
WHERE
s_id NOT IN (
SELECT
score.s_id
FROM
score
INNER JOIN course ON score.c_id = course.c_id
INNER JOIN teacher ON course.t_id = teacher.t_id
WHERE
teacher.t_name = '张三'
);
-- 15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩(重点)
SELECT
student.s_id,
student.s_name,
AVG( score.s_score )
FROM
student
INNER JOIN score ON student.s_id = score.s_id
WHERE
score.s_score < 60 GROUP BY student.s_id HAVING COUNT( score.c_id ) >= 2;
-- 16、检索"01"课程分数小于60,按分数降序排列的学生信息(和34题重复,不重点)
SELECT
*
FROM
student
WHERE
s_id IN ( SELECT s_id FROM score WHERE c_id = '01' AND s_score < 60 ORDER BY s_score DESC );
-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(重重点与35一样)
SELECT
s_id '学号',
MAX( CASE WHEN c_id = '01' THEN s_score ELSE 0 END ) '语文',
MAX( CASE WHEN c_id = '02' THEN s_score ELSE 0 END ) '数学',
MAX( CASE WHEN c_id = '03' THEN s_score ELSE 0 END ) '英语',
MAX( CASE WHEN c_id = '04' THEN s_score ELSE 0 END ) '物理',
SUM( s_score ) / ( SELECT COUNT( 1 ) FROM course ) '平均成绩'
FROM
score
GROUP BY
s_id
ORDER BY
AVG( s_score ) DESC;
-- 18、查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率(>=60),中等率(70-80),优良率(80-90),优秀率(90-100)
SELECT
s.c_id '课程ID',
c.c_name '课程name',
MAX( s_score ) '最高分',
MIN( s_score ) '最低分',
SUM( s_score ) / ( COUNT( s.s_id ) ) '平均分',
SUM( CASE WHEN s_score >= 60 THEN 1 ELSE 0 END ) / COUNT( s_id ) '及格率',
SUM( CASE WHEN s_score >= 70 AND s_score < 80 THEN 1 ELSE 0 END ) / COUNT( s_id ) '中等率',
SUM( CASE WHEN s_score >= 80 AND s_score < 90 THEN 1 ELSE 0 END ) / COUNT( s_id ) '优良率',
SUM( CASE WHEN s_score >= 90 AND s_score < 100 THEN 1 ELSE 0 END ) / COUNT( s_id ) '优秀率'
FROM
score s
INNER JOIN course c ON s.c_id = c.c_id
GROUP BY
s.c_id;
-- 19、按各科成绩进行排序,并显示排名(重点row_number)
SELECT
s_id,
c_name,
rank() over ( PARTITION BY c_name ORDER BY s_score DESC )
FROM
score
INNER JOIN course ON score.c_id = course.c_id;
-- 20、查询学生的总成绩并进行排名(不重点)
SELECT
s_id,
SUM( s_score ) '总成绩'
FROM
score
GROUP BY
s_id
ORDER BY
SUM( s_score ) DESC;
-- 21、查询不同老师所教不同课程平均分从高到低显示(不重点)
SELECT
c.c_id,
c.c_name,
t.t_name,
AVG( s_score ) avg
FROM
score s
INNER JOIN course c ON c.c_id = s.c_id
INNER JOIN teacher t ON t.t_id = c.t_id
GROUP BY
t.t_id,
c.c_name
ORDER BY
avg DESC;
-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩(重要 25类似)
-- mysql 5.7 不支持窗口函数
SELECT
*
FROM
score
ORDER BY
s_score DESC
LIMIT 1,
2;
-- 23、使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩,分别统计各分数段人数:课程ID和课程名称(重点和18题类似)
SELECT
FROM
-- 24、查询学生平均成绩及其名次(同19题,重点)
-- 25、查询各科成绩前三名的记录(不考虑成绩并列情况)(重点 与22题类似)
-- 26、查询每门课程被选修的学生数(不重点)
-- 27、查询出只有两门课程的全部学生的学号和姓名(不重点)
-- 28、查询男生、女生人数(不重点)
-- 29、查询名字中含有"风"字的学生信息(不重点)
-- 31、查询1990年出生的学生名单(重点year)
-- 32、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩(不重要)
-- 33、查询每门课程的平均成绩,结果按平均成绩升序排序,平均成绩相同时,按课程号降序排列(不重要)
-- 34、查询课程名称为"数学",且分数低于60的学生姓名和分数(不重点)
-- 35、查询所有学生的课程及分数情况(重点)
-- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数(重点)
-- 37、查询不及格的课程并按课程号从大到小排列(不重点)
-- 38、查询课程编号为03且课程成绩在80分以上的学生的学号和姓名(不重要)
-- 39、求每门课程的学生人数(不重要)
-- 40、查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩(重要top)
-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 (重点)
-- 42、查询每门功成绩最好的前两名(同22和25题)
-- 43、统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列(不重要)
-- 44、检索至少选修两门课程的学生学号(不重要)
-- 45、查询选修了全部课程的学生信息(重点划红线地方)
-- 46、查询各学生的年龄(精确到月份)
-- 47、查询没学过“张三”老师讲授的任一门课程的学生姓名(还可以,自己写的,答案中没有)
-- 47、查询两门以上不及格课程的同学的学号及其平均成绩(还可以,自己写的,答案中没有)
-- 48、查询下周过生日的学生
-- 查询本月过生日的学生
-- 查询本周过生日的学生
-- 查询下周过生日的学生
-- 49、查询本月过生日的人
-- 50、查询下一个月过生日的同学