--1 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
-- 2 查询同时存在" 01 "课程和" 02 "课程的学生信息
-- 3 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
-- 4 查询不存在" 01 "课程但存在" 02 "课程的情况
-- 5. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
-- 6. 查询在 Score 表存在成绩的学生信息
-- 7 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
-- 8 查没有成绩的学生信息
-- 9. 查询「李」姓学生的数量
-- 10. 查询学过「张三」老师授课的同学的信息-- 空间复杂度:对内存空间的占用情况-- 时间复杂度:对CPU的占用情况(逻辑判断的次数)
-- 11. 查询没有学全所有课程的同学的信息(没学过的也算)
-- 12 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
-- 13 查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
-- 14 查询没学过"张三"老师讲授的任一门课程的学生姓名
-- 15 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
-- 16 检索" 01 "课程分数小于 60,按分数降序排列的学生信息
-- 17 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
-- 18.查询各科成绩最高分、最低分和平均分:
以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,
优秀率 及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
-- 19 按各科成绩进行排序,并各科成绩从高到底显示排名, Score 重复时保留名次空缺 RANK()
-- 20各科成绩进行排序,并显示排名, Score 重复时合并名次
-- 21查询学生的总成绩,总成绩并进行排名,总分重复时保留名次空缺
-- 22统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比
-- 23查询各科成绩前三名的记录
-- 24查询每门课程被选修的学生数
-- 25 查询出只选修两门课程的学生学号和姓名
-- 26 查询男生、女生人数
-- 27 查询名字中含有「小」字的学生信息
-- 28 查询同名同性学生名单,并统计同名人数
-- 29查询 1990 年出生的学生名单
-- 30查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
-- 31查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
-- 32查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
-- 33查询所有学生的课程名称及分数情况(存在学生没成绩,没选课的情况)
-- 34查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
-- 35 查询不及格的课程
-- 36 查询课程编号为 01 且课程成绩在 60 分以上的学生的学号和姓名
-- 37 求每门课程的学生人数
-- 38成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩 ROW_NUMBER()
-- 39成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
-- 40查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
-- 41查询每门功成绩最好的前两名 RANK
-- 42统计每门课程的学生选修人数(超过 5 人的课程才统计)。
-- 43检索至少选修两门课程的学生学号
-- 44查询选修了全部课程的学生信息
-- 45 查询各学生的年龄,只按年份来算
-- 46 按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一;
-- 47 查询本周过生日的学生
-- 48 查询下周过生日的学生 -
-- 49 查询本月过生日的学生
-- 50询下月过生日的学生
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 );
-- 1 查询" 01 "课程比" 02"课程成绩高的学生的信息及课程分数
SELECT
*
FROM
Score a
JOIN Student d ON a.s_id = d.s_id
JOIN Score b ON a.s_id = b.s_id
AND a.c_id = '01'
AND b.c_id = '02'
WHERE
a.s_score > b.s_score;
-- 2 查询同时存在" 01 "课程和" 02 "课程的学生信息
SELECT
c.*
FROM
Score a
JOIN Score b ON a.s_id = b.s_id
AND a.c_id = '01'
AND b.c_id = '02'
JOIN Student c ON a.s_id = c.s_id;
-- 3 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
SELECT
*
FROM
Score a
JOIN Score b ON a.s_id = b.s_id
AND b.c_id != '02'
WHERE
a.c_id = '01';
-- 4 查询不存在" 01 "课程但存在" 02 "课程的情况
SELECT
*
FROM
Score a
JOIN Score b ON a.s_id = b.s_id
AND b.c_id = '02'
WHERE
a.c_id != '01';
-- 5. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT
a.s_id,
s_name,
AVG( s_score ) c
FROM
Student a
JOIN Score b ON a.s_id = b.s_id
GROUP BY
a.s_id
HAVING
c > 60;
-- 6. 查询在 Score 表存在成绩的学生信息
SELECT DISTINCT
b.*
FROM
Score a
INNER JOIN Student b ON a.s_id = b.s_id;
-- 7 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
SELECT
a.s_id,
s_name,
COUNT( c_id ),
SUM( s_score )
FROM
Student a
JOIN Score b ON a.s_id = b.s_id
GROUP BY
a.s_id;
-- 8 查没有成绩的学生信息
SELECT
*
FROM
Student a
LEFT JOIN Score b ON a.s_id = b.s_id
WHERE
s_score IS NULL;
-- 9. 查询「李」姓学生的数量
SELECT
COUNT( s_name )
FROM
Student
WHERE
s_name LIKE '李%';
-- 10. 查询学过「张三」老师授课的同学的信息
SELECT
*
FROM
Student a
JOIN Score b ON a.s_id = b.s_id
JOIN Course c ON b.c_id = c.c_id
JOIN Teacher d ON c.t_id = d.t_id
WHERE
t_name = '张三';
-- 11. 查询没有学全所有课程的同学的信息(没学过的也算)
SELECT
a.*
FROM
Student a
LEFT JOIN Score b ON a.s_id = b.s_id
LEFT JOIN Course c ON b.c_id = c.c_id
GROUP BY
a.s_id
HAVING
COUNT(*)< 3;
-- 12 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
-- 查询01同学学的课程
SELECT
c_id
FROM
Score
WHERE
s_id = '01';
SELECT DISTINCT
a.*
FROM
Student a
JOIN Score b ON a.s_id = b.s_id
WHERE
c_id IN ( SELECT c_id FROM Score WHERE s_id = '01' );
-- 13 查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
-- 1.查询01同学学的课程
SELECT c_id from Score where s_id = '01';
-- 2.查询除了01同学外其他同学学的课程
SELECT s_id,c_id from Score where s_id != '01';
-- 3.查询01同学学的课程数
SELECT COUNT(c_id) from Score where s_id = '01';
-- 4.查询其他同学学的课程总数
SELECT s_id,COUNT(c_id) from Score where s_id != '01' GROUP BY s_id;
-- 5.总数进行对比,相等则为完全一样
SELECT
b.*
FROM
Score a
INNER JOIN Student b ON a.s_id = b.s_id
WHERE
a.s_id NOT IN (
SELECT
s_id
FROM
Score
WHERE
c_id NOT IN ( SELECT c_id FROM Score WHERE s_id = '01' ))
GROUP BY
a.s_id
HAVING
count( 1 ) = ( SELECT count(*) FROM Score WHERE s_id = '01' );
-- 14 查询没学过"张三"老师讲授的任一门课程的学生姓名
-- 1.查询哪些同学学习了张三老师的课程
-- select c_id FROM Teacher a join Course b on a.t_id = b.t_id where t_name = '张三'; -- 张三老师课程的编号
-- SELECT b.s_id from Score a join Student b on a.s_id = b.s_id where c_id =(select c_id FROM Teacher a join Course b on a.t_id = b.t_id where t_name = '张三');-- 学习张三老师课程的学生id
-- 2.除了这些学生
SELECT
*
FROM
Student
WHERE
s_id NOT IN (
SELECT
b.s_id
FROM
Score a
JOIN Student b ON a.s_id = b.s_id
WHERE
c_id =(
SELECT
c_id
FROM
Teacher a
JOIN Course b ON a.t_id = b.t_id
WHERE
t_name = '张三'
));
-- 15 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
-- SELECT a.s_id,s_name,AVG(s_score)
-- from Student a
-- join Score b on a.s_id = b.s_id
-- where s_score < 60 -- and SELECT s_id,COUNT(c_id) from Score GROUP BY s_id having COUNT(c_id)>=2
-- GROUP BY a.s_id;
SELECT
a.s_id,
b.s_name,
avg( s_score ) AS avg_score
FROM
Score a
LEFT JOIN Student b ON a.s_id = b.s_id
INNER JOIN (
SELECT
s_id,
s_name
FROM
Student
WHERE
s_id IN ( SELECT s_id FROM Score WHERE s_score < 60 GROUP BY s_id HAVING count( 1 )> 1 )) AS c ON a.s_id = c.s_id
GROUP BY
a.s_id;
-- 16 检索" 01 "课程分数小于 60,按分数降序排列的学生信息
SELECT
b.*,
s_score
FROM
Score a
RIGHT JOIN Student b ON a.s_id = b.s_id
WHERE
c_id = '01'
AND s_score < 60
ORDER BY
s_score DESC;
-- 17 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT
a.*,
GROUP_CONCAT( s_score ),
AVG( s_score )
FROM
Student a
JOIN Score b ON a.s_id = b.s_id
GROUP BY
a.s_id
ORDER BY
AVG( s_score ) DESC;
-- 18.查询各科成绩最高分、最低分和平均分:以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率 及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
SELECT
a.c_id,
c_name,
MAX(s_score) '最高分',
MIN(s_score) '最低分',
AVG(s_score) '平均分',
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 then 1 else 0 end )/COUNT(s_id) '优秀率'
FROM Score a
join Course b on a.c_id = b.c_id
group by c_id;
-- 19 按各科成绩进行排序,并各科成绩从高到底显示排名, Score 重复时保留名次空缺 RANK()
select
c_id,
s_score,
RANK() over (PARTITION by s_id ORDER BY s_score DESC)
from Score ;
-- 20各科成绩进行排序,并显示排名, Score 重复时合并名次
select
c_id,
s_score,
rank() over (partition by s_id ORDER BY s_score)
from Score;
-- 21查询学生的总成绩,总成绩并进行排名,总分重复时保留名次空缺
select
s_id,
sum_score ,
RANK() over (order by sum_score )
from (select s_id,
sum(s_score) as sum_score
from Score
group by s_id) a;
SELECT
s_id,
sum_score,
rank() over (ORDER BY sum_score DESC) AS '排名'
FROM (
SELECT
s_id,
SUM(s_score) AS sum_score
FROM Score
GROUP BY s_id) a;
-- 22统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比
-- 同14题
SELECT
a.c_id,
c_name,
count(case when s_score <= 100 and s_score >=85 then 1 end ) '[100-85]',
count(case when s_score < 85 and s_score >=70 then 1 end ) '[85-70]',
count(case when s_score < 70 and s_score >=60 then 1 end ) '[70-60]',
count(case when s_score < 60 and s_score >=0 then 1 end ) '[60-0]',
count(case when s_score <= 100 and s_score >=85 then 1 end )/count(s_id) '[100-85]百分比',
count(case when s_score < 85 and s_score >=70 then 1 end ) /count(s_id)'[85-70]百分比',
count(case when s_score < 70 and s_score >=60 then 1 end )/count(s_id) '[70-60]百分比',
count(case when s_score < 60 and s_score >=0 then 1 end )/count(s_id) '[60-0]百分比'
from Score a
join Course b on a.c_id = b.c_id
group by c_id;
-- 23查询各科成绩前三名的记录
select c_name,
GROUP_CONCAT(s_score ORDER BY s_score desc )
from Score a
join Course b on a.c_id = b.c_id
group by c_name;
select a.*
from Score a
where (select count(1) from Score b where b.c_id =a.c_id and b.s_score >a.s_score) < 3
order by c_id desc,s_score desc;
-- limit
-- 24查询每门课程被选修的学生数
SELECT c_id,COUNT(s_id)
FROM Score
GROUP BY c_id;
-- 25 查询出只选修两门课程的学生学号和姓名
select b.s_id,
s_name,
count(c_id)
from Student a
join Score b on a.s_id = b.s_id
group by s_id
HAVING count(c_id) = 2;
-- 26 查询男生、女生人数
-- select
-- COUNT(case when s_sex = '男' then 1 else 0 END) '男生人数',
-- count(case when s_sex = '女' then 1 else 0 END) '女生人数'
-- from Student;
select
s_sex,
count(s_sex)
from Student
group by s_sex;
-- 27 查询名字中含有「风」字的学生信息
select *
from Student
where s_name like '%风%';
-- 28 查询同名同性学生名单,并统计同名人数
select a.s_name,
a.s_sex,
count(*)
from Student a
join Student b on a.s_name = b.s_name and a.s_sex = b.s_sex and a.s_id != b.s_id
group by a.s_sex,a.s_name;
-- 29查询 1990 年出生的学生名单
select *
from Student
where year(s_birth) = '1990';
-- 30查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
select c_id ,AVG(s_score)
from Score
GROUP BY c_id
ORDER BY AVG(s_score) DESC,c_id ASC;
-- 31查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
SELECT b.s_id,
s_name,
AVG(s_score)
from Score a
join Student b ON a.s_id = b.s_id
GROUP BY s_id,s_name
having AVG(s_score) >=85;
-- 32查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
select a.s_id,s_name ,c.c_id,c_name, s_score
from Score a
join Student b on a.s_id = b.s_id
join Course c on a.c_id =c.c_id
where c_name = '数学' and s_score <60;
-- 33查询所有学生的课程名称及分数情况(存在学生没成绩,没选课的情况)
select s_name,c_name , s_score
from Student a
left join Score b on a.s_id = b.s_id
left join Course c on b.c_id = c.c_id;
-- 34查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select s_name , c_name ,s_score
from Student a
join (SELECT * from Score where s_score > 70) b on a.s_id = b.s_id
join Course c on b.c_id = c.c_id ;
-- join Score b on a.s_id = b.s_id
-- join Course c on b.c_id = c.c_id
-- where (select count(1) from Score where s_score >70 group by s_id ) = 3;
--
select sname,cname,score
from student
inner join (select * from sc where score > 70)as b on student.sid=b.sid
inner join course on b.cid = course.cid;
-- 35 查询不及格的课程
SELECT *
from Score
where s_score < 60;
-- 36 查询课程编号为 01 且课程成绩在 60 分以上的学生的学号和姓名
select a.s_id,s_name
from Student a
join Score b on a.s_id = b.s_id
where c_id = '01' and s_score > 60;
-- 37 求每门课程的学生人数
select c_id ,COUNT(s_id)
from Score
GROUP BY c_id;
-- 38成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩 ROW_NUMBER()
select * ,
ROW_NUMBER() over (partition by b.c_id ORDER BY s_score DESC)
from Student a
join Score b on a.s_id = b.s_id
join Course c on b.c_id = c.c_id
join Teacher d on c.t_id = d.t_id
where t_name = '张三'
limit 1;
-- 39成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
select *
from(
SELECT a.s_id ,s_name,s_score,
RANK() over (order by s_score desc) as ranking
FROM Student a
join Score b on a.s_id = b.s_id
join Course c on b.c_id = c.c_id
join Teacher d on d.t_id = d.t_id
where t_name = '张三'
)as st
where ranking = 1;
-- 40查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select
a.s_id,
a.c_id,
a.s_score
from Score a
join Score b on a.s_score = b.s_score and a.c_id != b.c_id and a.s_id != b.s_id;
-- 41查询每门课程成绩最好的前两名同学 RANK
SELECT *
FROM(SELECT c_id,s_name,
RANK() over (partition by c_id ORDER BY s_score DESC) as ranking
FROM Score a
join Student b on a.s_id = b.s_id
) as st
GROUP BY c_id,s_name
HAVING ranking in (1,2);
-- 42统计每门课程的学生选修人数(超过 5 人的课程才统计)。
SELECT
c_id,
COUNT(s_id)
from Score
group by c_id
having count(s_id) >5;
-- 43检索至少选修两门课程的学生学号
SELECT
s_id,
COUNT(1)
from Score
group by s_id
having COUNT(1) >= 2;
-- 44查询选修了全部课程的学生信息
SELECT a.*
from Student a
where (SELECT COUNT(1) from Score b where b.s_id = a.s_id) = (SELECT COUNT(1) from Course);
-- 45 查询各学生的年龄,只按年份来算
select *,
year(now())-YEAR(s_birth) age
from Student;
-- 46 按照出生日期来算,当前月日 < 出生年月的月日则年龄减一;
SELECT *,
TIMESTAMPDIFF(year,s_birth,NOW()) as age
from Student;
-- 47 查询本周过生日的学生
SELECT * ,
WEEK(s_birth),
WEEK(NOW())
from Student
where WEEK(s_birth) = WEEK(NOW());
-- 48 查询下周过生日的学生 -
SELECT * ,
WEEK(s_birth),
WEEK(NOW())
from Student
where WEEK(s_birth) =( WEEK(NOW()) + 1);
-- 49 查询本月过生日的学生
SELECT *,
MONTH(s_birth),
MONTH(NOW())
FROM Student
where MONTH(s_birth) = MONTH(NOW());
-- 50查询下月过生日的学生
SELECT *,
MONTH(s_birth),
MONTH(NOW())
FROM Student
where MONTH(s_birth) = (MONTH(NOW())+1);