sql:
SET FOREIGN_KEY_CHECKS=0;
-- Table structure for course
DROP TABLE IF EXISTS course;
CREATE TABLE course (
c_id varchar(11) COLLATE utf8_unicode_ci DEFAULT NULL,
c_name varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
t_id varchar(11) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- Records of course
INSERT INTO course VALUES ('01', '语文', '02');
INSERT INTO course VALUES ('02', '数学', '01');
INSERT INTO course VALUES ('03', '英语', '03');
-- Table structure for score
DROP TABLE IF EXISTS score;
CREATE TABLE score (
s_id varchar(11) COLLATE utf8_unicode_ci DEFAULT NULL,
c_id varchar(11) COLLATE utf8_unicode_ci DEFAULT NULL,
s_score int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- Records of score
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');
-- Table structure for student
DROP TABLE IF EXISTS student;
CREATE TABLE student (
s_id varchar(11) COLLATE utf8_unicode_ci DEFAULT NULL,
s_name varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
s_birth date DEFAULT NULL,
s_sex varchar(3) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- Records of student
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', '郑竹', '2023-02-09', '女');
INSERT INTO student VALUES ('08', '王菊', '2023-01-09', '女');
-- Table structure for teacher
DROP TABLE IF EXISTS teacher;
CREATE TABLE teacher (
t_id varchar(11) COLLATE utf8_unicode_ci DEFAULT NULL,
t_name varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- Records of teacher
INSERT INTO teacher VALUES ('01', '张三');
INSERT INTO teacher VALUES ('02', '李四');
INSERT INTO teacher VALUES ('03', '王五');
1. 01的课程比02 课程高的学生 信息 和 课程分数
-- 1.1 自连接
SELECT c.s_id , c.s_name,a.s_score as c1_score, b.s_score as c2_score from score a , score b, Student c WHERE a.s_id = b.s_id and a.c_id = 01 and b.c_id = 02 and a.s_score > b.s_score
and c.s_id = a.s_id
-- 1.1 长型变宽型
执行失败：
SELECT t1.s_id, t2.s_name,
MAX(case WHEN t1.c_id = 01 then s_score end) as score01,
MAX(case WHEN t1.c_id = 02 then s_score end) as score02
from score t1 , student t2
WHERE t1.s_id = t2.s_id
group by t1.s_id
HAVING score01 > score02
变成宽型：
SELECT * from
(SELECT s_id
MAX(case WHEN c_id = 01 then s_score end) as score01,
MAX(case WHEN c_id = 02 then s_score end) as score02
from score
group by s_id ) t2
最终sql:
SELECT t2.s_id,t2.score01,t2.score02,t3.s_name from
(
SELECT s_id,
MAX(case WHEN c_id = 01 then s_score end) as score01,
MAX(case WHEN c_id = 02 then s_score end) as score02
from score
group by s_id
) t2 , student t3 WHERE t2.s_id = t3.s_id and t2. score01 > t2.score02
2. 01的课程比02 课程 低 的学生 信息 和 课程分数
SELECT t1.s_id,t1.c_id, t2.c_id, t1.s_score score1 , t2.s_score score2, t3.s_name from score t1, score t2,student t3
WHERE t1.s_id = t2.s_id and t1.s_id = t3.s_id
and t1.c_id = 01 and t2.c_id = 02
and t1.s_score < t2.s_score
SELECT t1.s_id,t1.score1,t1.score2 , t2.s_name from
( SELECT
s_id,
MAX(case when c_id = 01 then s_score end) as score1,
MAX(case when c_id = 02 then s_score end) as score2
from score group by s_id) t1, student t2 WHERE t1. score1 < t1.score2 and t1.s_id =t2.s_id
3. 查询平均成绩大于等于60分的同学的学生编号 和学生姓名 和平均成绩
查询失败：
SELECT t1.s_id,t2.s_name,AVG(s_score) scores from score t1, student t2
WHERE t1.s_id = t2.s_id group by t1.s_id having scores > 60
但是示例中的可以执行成功：
SELECT a.s_id, s.s_name, avg(a.s_score) avg_s from score a, student s
WHERE a.s_id = s.s_id group by a.s_id having avg(a.s_score) > 60
成功：
SELECT t1.s_id,t2.s_name,t1.scores from (
SELECT s_id,AVG(s_score) scores from score group by s_id having scores > 60
) t1, student t2 WHERE t1.s_id = t2.s_id
子查询放在字段上：
SELECT s_id,
(SELECT t2.s_name from student t2 where t2.s_id = t1.s_id) as s_name,
AVG(s_score) scores from score t1 group by s_id having scores > 60
4. 查询平均成绩大于等于60分的同学的学生编号 和学生姓名 和平均成绩
SELECT t1.s_id,t2.s_name,t1.scores from (
SELECT s_id,AVG(s_score) scores from score group by s_id having scores < 60
) t1, student t2 WHERE t1.s_id = t2.s_id
5. 查询所有同学的学生编号 学生姓名 选课总数 所有课程的总成绩
SELECT t1.s_id,t1.s_name,ct, scores from student t1,
(SELECT s_id,count(*) ct,sum(s_score) scores from score group by s_id) t2
where t1.s_id = t2.s_id
不能有 s_name ,有了就报错
报错原因： https://blog.csdn.net/qq_39954916/article/details/120123550 数据库设置的模式
SELECT t1.s_id, t1.s_name from student t1 group by t1.s_id 这样也不行，就是说不能在select中有没有在group by指定的列
SELECT t1.s_id,t1.s_name,count(t2.c_id) ct, sum(t2.s_score) scores from student t1, score t2
where t1.s_id= t2.s_id group by t1.s_id
这样就可以：
SELECT t1.s_id,count(t2.c_id) ct, sum(t2.s_score) scores from student t1, score t2
where t1.s_id= t2.s_id group by t1.s_id
修改方案：
https://blog.csdn.net/qq_39408664/article/details/120715211
show variables like '%sql_mode%'
set global sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
set session sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
show variables like '%sql_mode%'
SELECT IFNULL(s_score,0) ifnull 可以指定为0
6. 查询李姓 老师的数量
SELECT count(*) from teacher WHERE t_name like '李%'
7. 查询学过 张三老师授课的同学的信息
SELECT t3.s_id,t4.s_name from teacher t1 , course t2, score t3, student t4
WHERE t1.t_id = t2.t_id and t2.c_id = t3.c_id and t3.s_id = t4.s_id and t_name = '张三'
8. 查询没有 学过 张三老师授课的同学的信息
SELECT * from student t5 WHERE s_id not in ( SELECT t3.s_id from teacher t1 , course t2, score t3, student t4
WHERE t1.t_id = t2.t_id and t2.c_id = t3.c_id and t3.s_id = t4.s_id and t_name = '张三' )
容易出错： 就是把第七题直接改成 不等于 张三
第二种： not EXISTS
SELECT * from student WHERE not EXISTS (
SELECT 1 from (SELECT t3.s_id from teacher t1 , course t2, score t3, student t4
WHERE t1.t_id = t2.t_id and t2.c_id = t3.c_id and t3.s_id = t4.s_id and t_name = '张三' ) t WHERE t.s_id = student.s_id
)
not exists 比 not in 效率高
9. 查询学过编号01 并且也学过编号为02的 同学的信息
SELECT t1.*,t2.score1,t2.score2 from student t1, (
SELECT s_id, MAX(CASE WHEN c_id = 01 then s_score end) as score1,
MAX(CASE WHEN c_id = 02 then s_score end) as score2 from score group by s_id
HAVING score1 > 0 and score2 > 0
) t2 where t1.s_id = t2.s_id
也可以用自连接：
SELECT * from score a , score b
where a.c_id = 01 and b.c_id = 02 and a.s_id = b.s_id
10. 查询学过编号01 但是没有学过 编号 02的课程 同学信息
缺少了 null的学生
SELECT t1.s_id,t1.s_score score1, t2.s_score score2 from
score t1 , score t2
WHERE t1.s_id = t2.s_id
and t1.c_id= 01 and t2.c_id = 02
即便是左连接也是不行
SELECT t1.s_id ,t2.s_score score1, t2.s_score score2 from
score t1
LEFT JOIN score t2 on t1.s_id = t2.s_id
WHERE t1.c_id= 01 and t2.c_id = 02
最终：
SELECT t2.*,t3.score1,t3.score2 from student t2,
( SELECT t1.s_id,
MAX(case WHEN t1.c_id = 01 then s_score end) as score1,
MAX(case WHEN t1.c_id = 02 then s_score end) as score2
from score t1 group by t1.s_id HAVING score1 > 0 and score2 is null) t3
where t2.s_id = t3.s_id
null是不能用来和0比较的
11. 查询没有学全所有课程的同学的信息
开始：
SELECT t1.s_id,
MAX(case WHEN t1.c_id = 01 then s_score end) as score1,
MAX(case WHEN t1.c_id = 02 then s_score end) as score2,
MAX(case WHEN t1.c_id = 03 then s_score end) as score3
from score t1 group by t1.s_id HAVING score1 is null or score2 is null or score3 is null
不完美的是 指定了 01 02 03
条件是 课程数量：
SELECT
t1.c_id, t1.c_name, t2.s_id ,t2.s_score , count(*)
from
course t1
join score t2 on t1.c_id = t2.c_id group by s_id
最终：
SELECT
t1.s_id ,t1.c_id ,count(*) ct
from
score t1 group by t1.s_id HAVING ct < (SELECT count(1) from course)
SELECT
t1.*, count(t2.s_id) ct
from student t1 left join score t2 on t1.s_id = t2.s_id group by t2.s_id having ct < (SELECT count(1) from course)
count里面的条件不同 也是不同的
一开始写的是 count(*) group by 之后 获取到的是1
count(s_id) 获取到的是 0
12. 查询至少有一门课程与学号为01 的同学所学的相同的同学的信心
SELECT t4.* from score t3, student t4 WHERE t3.s_id = t4.s_id and c_id in (
SELECT t2.c_id from student t1, score t2
WHERE t1.s_id = t2.s_id and t1.s_id = 01
) group by s_id
13. 查询和01 同学学习课程 完全相同的其他同学的信息
SELECT * from (
SELECT t3.,t4.c_id cid2 from
(SELECT t1.,t2.c_id from student t1, (
SELECT c_id from score WHERE s_id = 01
) t2 ) t3
LEFT JOIN score t4
on t3.s_id = t4.s_id and t3.c_id = t4.c_id
UNION
SELECT t3.,t4.c_id cid2 from
(SELECT t1.,t2.c_id from student t1, (
SELECT c_id from score WHERE s_id = 01
) t2 ) t3
RIGHT JOIN score t4
on t3.s_id = t4.s_id and t3.c_id = t4.c_id
) t5 where t5.c_id is null or t5.cid2 is null
14. 查询没学过张三老师 讲授的任一门课程的学生姓名
SELECT * from student WHERE s_id not in (
SELECT s_id from score WHERE c_id in (
SELECT c_id from course WHERE c_id in (SELECT t_id from teacher WHERE t_name = "张三")
)
)
15. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT t.s_id,scores,ct,t3.s_name from (
SELECT s_id,AVG(s_score) scores from score GROUP BY s_id
) t
join (SELECT s_id,count(*) ct from score WHERE s_score < 60 group by s_id HAVING ct > 1) t2 on t.s_id = t2.s_id
join student t3 on t.s_id = t3.s_id
视频中的写法：
SELECT t1. s_id,t2.s_name,avg(s_score) from score t1, student t2
WHERE t1.s_id = t2.s_id
group by s_id HAVING sum(case WHEN s_score < 60 then 1 else 0 end) >= 2
在 having中 使用 sum求和 以及 case when
16. 检索01课程分数小于60 按分数降序排列的学生信息
SELECT t1.s_id,t2.s_name, s_score, t1.c_id from score t1, student t2
WHERE t1.c_id = 01 and t1.s_id = t2.s_id and t1.s_score < 60 ORDER BY s_score desc
17. 按照平均成绩从高到底显示所有学生的课程成绩及平均成绩
SELECT * from score t1,
(
SELECT s_id,ROUND(avg(s_score),2) from score group by s_id
) t2
WHERE t1.s_id = t2.s_id
round(平均成绩，2) 取两位小数
mysql8.0 的开窗函数简化：
SELECT a.*, avg(s_score) over(PARTITION by a.s_id) avgs
from score a
18. 查询各科成绩最高分，最低分和平均分：
课程ID，课程name, 最高分，最低分，平均分，及格率，中等率，优良率，优秀率，
及格>=60 中等 ：70-80 优良： 80-90 优秀 >90
SELECT t1.c_id,t1.c_name,MAX(s_score),MIN(s_score),avg(s_score),
SUM(CASE when t2.s_score >= 60 then 1 else 0 end)/count() "及格率",
SUM(CASE when t2.s_score >= 70 and t2.s_score < 80 then 1 else 0 end)/count() "中等率",
SUM(CASE when t2.s_score >= 80 and t2.s_score < 90 then 1 else 0 end)/count() "优良率",
SUM(CASE when t2.s_score >= 90 then 1 else 0 end)/count() "优秀率"
from course t1, score t2
WHERE t1.c_id =t2.c_id
GROUP BY c_id
19. 按照各科成绩进行排序，并显示排名
SELECT
t1.*,
(SELECT count(s_score) from score t2 where t2.c_id = t1.c_id and t2.s_score > t1.s_score) + 1 as rk
from score t1 order by c_id,s_score desc
20. 查询学生的总成绩 并进行排名
这样不行：因为有group by
SELECT t1.s_id,
SUM(s_score) scores
,(SELECT count(*) from score t2 WHERE t2.c_id = t1.c_id and t2.s_score > t1.s_score) + 1 as rank
from score t1 group by t1.s_id ORDER BY scores desc
set @rank = 0;
select t2.s_id, scores, @rank := @rank + 1 t_rank
from (
SELECT t1.s_id,
SUM(s_score) scores
from score t1 group by t1.s_id ORDER BY scores desc
) t2
order by t2.scores desc
或者：
select t2.s_id, scores, @rank := @rank + 1 t_rank
from (
SELECT t1.s_id,
SUM(s_score) scores
from score t1 group by t1.s_id ORDER BY scores desc
) t2, (select @rank := 0) t3
order by t2.scores desc
21. 查询不同老师所教不同课程平均分 从高到低显示
SELECT t1.t_id,t_name,t2.c_id,c_name, scores from teacher t1 ,course t2,
(SELECT c_id, avg(s_score) scores from score group by c_id ) t3
WHERE t1.t_id = t2.t_id and t2.c_id = t3.c_id order by scores desc
或者：
SELECT t1.t_id,t_name,t2.c_id,c_name,avg(s_score) scores from teacher t1 ,course t2, score t3
WHERE t1.t_id = t2.t_id and t2.c_id = t3.c_id group by c_id order by scores desc
22. 查询所有课程成绩第2名到第三名的学生信息及该课程成绩
greatest(字段1，字段2，字段3，…，字段n) 取最大值
least(字段1，字段2，字段3，…，字段n) 取最小值
SELECT s_id, c_id, s_score
, GREATEST(IFNULL(t_rank1,0), IFNULL(t_rank2,0), IFNULL(t_rank3,0)) RESULT
from
(
SELECT
t1.*,
CASE WHEN c_id = 01 then @rank1 := @rank1 + 1 end t_rank1,
CASE WHEN c_id = 02 then @rank2 := @rank2 + 1 end t_rank2,
CASE WHEN c_id = 03 then @rank3 := @rank3 + 1 end t_rank3
from score t1, (select @rank1 := 0) t3 , (select @rank2 := 0) t4 , (select @rank3 := 0) t5 order by c_id,s_score desc
) t6
SELECT s_id, c_id, s_score,result from
(
SELECT s_id, c_id, s_score
, GREATEST(IFNULL(t_rank1,0), IFNULL(t_rank2,0), IFNULL(t_rank3,0)) as result
from
(
SELECT
t1.*,
CASE WHEN c_id = 01 then @rank1 := @rank1 + 1 end t_rank1,
CASE WHEN c_id = 02 then @rank2 := @rank2 + 1 end t_rank2,
CASE WHEN c_id = 03 then @rank3 := @rank3 + 1 end t_rank3
from score t1, (select @rank1 := 0) t3 , (select @rank2 := 0) t4 , (select @rank3 := 0) t5 order by c_id,s_score desc
) t6
) t7 where result in (2,3)
23. 统计各科成绩各分数段人数：课程编号，课程名称， 【100-85】 【85-70】 【70-60】【0-60】 及所占百分比
SELECT c_id,
SUM(case when s_score >= 85 and s_score < 100 then 1 else 0 end ) / count(1) "优秀率",
SUM(case when s_score >= 70 and s_score < 85 then 1 else 0 end ) / count(1) "优良率",
SUM(case when s_score >= 60 and s_score < 70 then 1 else 0 end ) / count(1) "中等率",
SUM(case when s_score >= 0 and s_score < 60 then 1 else 0 end ) / count(1) "不及格率"
from score
group by c_id
24. 查询学生平均成绩及其名次
SELECT s_id, scores ,@rank := @rank + 1 t_rank
from
(SELECT s_id, avg(s_score) scores
from score t1 group by s_id ORDER BY scores desc) t2, (select @rank := 0) t3
25. 查询各科成绩前三名
26. 查询每门课被选修的学生数
SELECT c_id,count(1) from score group by c_id
27. 查询只有两门课程的全部学生的学号和姓名
SELECT * from
(SELECT s_id,count(1) ct from score group by s_id ) t1
WHERE ct = 2
或者 用having ct = 2
28. 查询男生 女生人数
SELECT s_sex,count(1) from student GROUP BY s_sex
29. 查询 名字中含有 风 字的学生信息
SELECT * from student WHERE s_name like '%风%'
30.查询同名同性别的学生名单 并统计同名人数
SELECT s_name,s_sex,count(1) from student GROUP BY s_name,s_sex
31.查询1990年出生的学生名单
SELECT * from student WHERE year(s_birth) = 1990
32.查询每门课的平均成绩 结果按照平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT c_id,avg(s_score) scores from score group by c_id order by scores desc,c_id asc
33. 平均成绩>=85的所有学生的学号 姓名 平均成绩
SELECT c_id,avg(s_score) scores from score group by c_id HAVING scores >= 85
34.查询课程名称 为 数学 且分数低于60的学生姓名和分数
SELECT s_id,t1.c_id,t2.c_name,s_score scores from score t1, course t2 WHERE t1.c_id = t2.c_id
and t2.c_name = "数学" and s_score < 60
35.查询所有学生的课程及分数情况
SELECT t1.s_id,s_name,c_id,IFNULL(s_score,0) from student t1
LEFT JOIN score t2 on t1.s_id = t2.s_id
36.查询每一门课成绩都在70分以上的姓名 课程名称 和分数
SELECT t1.s_id,t1.s_name,t2.c_id,t2.s_score,t3.c_name from student t1
LEFT JOIN score t2 on t1.s_id = t2.s_id
LEFT JOIN course t3 on t2.c_id = t2.c_id
WHERE t1.s_id in (SELECT s_id from score GROUP BY s_id HAVING MIN(s_score) > 70)
37.查询不及格的课程
38.查询课程编号为01 且课程成绩在80分以上的学生学号和姓名
SELECT t1.s_id,t2.s_name from score t1
join student t2 on t1.s_id = t2.s_id
where c_id = 01 and s_score > 80
39.每门课的学生人数
SELECT c_id,count(1) from score GROUP BY c_id
40. 查询选修 张三老师所授课程的学生中 成绩最高的学生信息及其成绩
SELECT MAX(s_score),MIN(s_score) from score WHERE c_id in (
SELECT t2.c_id from teacher t1, course t2
WHERE t1.t_id =t2.t_id and t_name = '张三'
)
41. 查询不同课程成绩相同的学生的学生编号 课程编号 学生成绩
SELECT * from
(
SELECT s_id,
MAX(case WHEN c_id = 01 then s_score end) cid1 ,
MAX(case WHEN c_id = 02 then s_score end) cid2 ,
MAX(case WHEN c_id = 03 then s_score end) cid3
from score group by s_id
) t
where cid1 = cid2 or cid1 = cid3 or cid2 = cid3
42. 查询每门课成绩最好的前两门
SELECT * from score t1
WHERE
(SELECT count(1) from score t2 where t1.c_id =t2.c_id and t2.s_score > t1.s_score) + 1 <=2
43.统计每门课的学生选修人数（超过5人的才统计） 要求输出课程号 和 选修人数，查询结果按人数降序排列 若人数相同，按照课程号升序排列
44. 检索至少选修两门的学生
45.查询选修了全部课程的学生信息
46.查询各学生的年龄
SELECT year(now());
SELECT month(now());
SELECT day(now());
SELECT DAYOFYEAR(now());
SELECT weekofyear(now());
SELECT DAYOFMONTH(NOW());
SELECT DATE_FORMAT(NOW(),"%m%d");
SELECT STR_TO_DATE("190306", "%y%m%d");
SELECT *,year(NOW()) - year(s_birth) from student
47.查询本周过生日的学生
SELECT * from student
WHERE
WEEKOFYEAR(STR_TO_DATE(concat(year(NOW()), DATE_FORMAT(s_birth, "%m%d")), "%Y%m%d")) = WEEKOFYEAR(NOW())
48.查询下周过生日的学生
SELECT now(),now()+INTERVAL '7' day
SELECT * from student
WHERE
WEEKOFYEAR(STR_TO_DATE(concat(year(NOW()), DATE_FORMAT(s_birth, "%m%d")), "%Y%m%d")) = WEEKOFYEAR(now()+INTERVAL '7' day)
49.查询本月过生日的学生
SELECT * from student
WHERE
month(s_birth) = month(now())
50. 查询下月过生日的学生
SELECT * from student
WHERE
month(s_birth) = month(now()+INTERVAL 1 month)
