-- 创建数据库
create database school;
use school;

-- 建表
-- 学生表：学生编号,学生姓名, 出生年月,学生性别
create table Student(s_id varchar(10),s_name nvarchar(10),s_birth datetime,s_sex nvarchar(10));
insert into Student values(‘01‘ , N‘赵雷‘ , ‘1990-01-01‘ , N‘男‘);
insert into Student values(‘02‘ , N‘钱电‘ , ‘1990-12-21‘ , N‘男‘);
insert into Student values(‘03‘ , N‘孙风‘ , ‘1990-05-20‘ , N‘男‘);
insert into Student values(‘04‘ , N‘李云‘ , ‘1990-08-06‘ , N‘男‘);
insert into Student values(‘05‘ , N‘周梅‘ , ‘1991-12-01‘ , N‘女‘);
insert into Student values(‘06‘ , N‘吴兰‘ , ‘1992-03-01‘ , N‘女‘);
insert into Student values(‘07‘ , N‘郑竹‘ , ‘1989-07-01‘ , N‘女‘);
insert into Student values(‘08‘ , N‘王菊‘ , ‘1990-01-20‘ , N‘女‘);
-- 课程表：课程编号, 课程名称, 教师编号
create table Course(c_id varchar(10),c_name nvarchar(10),t_id varchar(10));
insert into Course values(‘01‘ , N‘语文‘ , ‘02‘);
insert into Course values(‘02‘ , N‘数学‘ , ‘01‘);
insert into Course values(‘03‘ , N‘英语‘ , ‘03‘);
-- 教师表：教师编号,教师姓名
create table Teacher(t_id varchar(10),t_name nvarchar(10));
insert into Teacher values(‘01‘ , N‘张三‘);
insert into Teacher values(‘02‘ , N‘李四‘);
insert into Teacher values(‘03‘ , N‘王五‘);
-- 成绩表：学生编号,课程编号,分数
create table Score(s_id varchar(10),c_id varchar(10),s_score decimal(18,1));
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 st.*, sc1.s_score as "课程1", sc2.s_score as "课程2", sc3.s_score as "课程3"
From student st 
	JOIN score sc1 on st.s_id=sc1.S_id AND sc1.c_id = "01"
	JOIN score sc2 on st.s_id=sc2.S_id AND  sc2.c_id = "02"
	JOIN score sc3 on st.s_id=sc3.S_id AND sc3.c_id ="03"
WHERE sc1.s_score>sc2.s_score

-- 2.查询平均成绩大于60分的同学的学号和平均成绩
SELECT st.s_id,s_name,ROUND(AVG(s_score),2) as avg_score
FROM student st JOIN score on st.s_id = score.s_id
GROUP BY s_id HAVING avg(s_score)>=60
#HAVING子句给出了选择组的条件;where作用于基本表或视图，having作用于组;WHERE子句中不能用聚集函数做条件表达式

-- 3.查询所有同学的学号、姓名、选课数、总成绩
SELECT st.s_id,st.s_name,count(c_id) as "选课总数",sum(s_score) as total_score
from student st left join score on st.s_id=score.s_id
GROUP BY st.s_id
#有学生未出现在成绩表上，用左连接，保证出现在学生表的学生都被输出

-- 4.查询姓“李”的老师的个数
SELECT count(t_id) from teacher where t_name like ‘李%‘

-- 5.查询没学过“张三”老师课的同学的学号、姓名
SELECT st1.* 
from student st1 where s_id not in 
    (SELECT st.s_id
    from student st,score sc,course c,teacher te
    WHERE st.s_id = sc.s_id and sc.c_id = c.c_id and c.t_id = te.t_id and     te.t_name = 	"张三")

-- 6.查询学过“张三”老师所教的课的同学的学号、姓名；
SELECT st.* 
from student st 
	join score on st.s_id = score.s_id
	join course on score.c_id = course.c_id
	join teacher on course.t_id = teacher.t_id AND teacher.t_name = "张三"		

SELECT  st.* 
from student st,score sc,course c,teacher te
WHERE st.s_id = sc.s_id and sc.c_id = c.c_id and c.t_id = te.t_id and te.t_name = "张三"

-- 7.查询学过编号“01”并且也学过编号“02”课程的同学的学号、姓名；
SELECT st.* 
from student st 
	join score sc1 on st.s_id = sc1.s_id and sc1.c_id = 01
	join score sc2 on st.s_id = sc2.s_id and sc2.c_id = 02 

-- 8.查询学过01但是没有学过02的同学的信息
SELECT st.* 
from student st join score s1 on st.s_id = s1.s_id and s1.c_id = 01 
where st.s_id not in (SELECT  s_id from  score WHERE c_id = 02)
#字段前最好都跟上表名

-- 9.查询所有课程成绩小于60分的同学的学号、姓名；
SELECT st.s_id,s_name
from student st 
WHERE s_id in(
	SELECT s_id from score 
	GROUP BY s_id HAVING max(s_score)<60
	) 

-- 10.查询没有学全所有课的同学的学号、姓名
select * from student 
where s_id not in(
	SELECT s_id from score 
	GROUP BY s_id HAVING count(c_id) = (
		select count(c_id) from course
		)
	)

-- 11.查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名
SELECT st.* 
from student st 
where s_id  in (
	SELECT s_id from score where c_id in(
		SELECT c_id from score WHERE s_id = 01
		) and s_id not in ("01")
	)

#思路类似，但将所有表放在一起可以精简过程
SELECT DISTINCT st.* 
FROM student st JOIN score sc 
	ON st.s_id=sc.s_id AND sc.c_id IN (
		SELECT sc.c_id FROM score sc WHERE sc.s_id="01" 
	) AND sc.s_id NOT IN ("01");

-- 12.查询和"01"号的同学学习的课程完全相同的其他同学的学号和姓名
SELECT st.* 
from student st join score sc on st.s_id = sc.s_id
WHERE st.s_id not in (
	SELECT s_id from student WHERE c_id not in(
		SELECT c_id from score where s_id = "01"
	)
)
GROUP BY st.s_id
HAVING COUNT(c_id)=(SELECT count(c_id) from score WHERE s_id = "01")
#虚拟表的名称为局部变量

-- 14.查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT s_name from student  WHERE s_id not in(
	SELECT st.s_id  FROM student st 
            join score sc on st.s_id = sc.s_id
	    join course c on sc.c_id = c.c_id
            join teacher te on te.t_id = c.t_id and t_name = "张三"
	)

-- 15.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT st.s_id,s_name,ROUND(avg(sc.s_score),2) as avg_score
from student st join score sc on st.s_id = sc.s_id
WHERE st.s_id in (
	SELECT s_id from score WHERE s_score < "60"
	GROUP BY s_id 
	HAVING COUNT(*)> 1
)
GROUP BY st.s_id

#有分组汇总函数就要有group by，除非只有一个组
SELECT st.s_id,st.s_name,ROUND(avg(sc.s_score),2) as avg_score
from student st 
	join score sc on st.s_id = sc.s_id and sc.s_score<‘60‘
GROUP BY s_id HAVING count(s_score) > 1

-- 16.检索"01"课程分数小于60，按分数降序排列的学生信息
SELECT st.* from student st JOIN score sc on st.s_id = sc.s_id
WHERE sc.s_score < 60 and sc.c_id = "01"
ORDER BY sc.s_score DESC

-- 17.按平均成绩从高到低显示所有学生的平均成绩
SELECT st.s_id, st.s_name,sc1.s_score as "01",sc2.s_score as "02",sc3.s_score as "03",ROUND(avg(sc.s_score),2) as "avg_score"
from student st 
    left join score sc on st.s_id = sc.s_id 
    left join score sc1 on st.s_id = sc1.s_id and sc1.c_id = "01"
    left join score sc2 on st.s_id = sc2.s_id and sc2.c_id = "02"
    left join score sc3 on st.s_id = sc3.s_id and sc3.c_id = "03" 
GROUP BY st.s_id
ORDER BY avg(sc.s_score) DESC;

-- 18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率
SELECT a.c_id "课程ID",a.c_name "课程名",
    max(b.s_score) "最高分",
    min(b.s_score) "最低分",
    ROUND(avg(b.s_score),2)  "平均分",
    sum(case when b.s_score>60 then 1 else 0 end)/count(1) "及格率",
    sum(case when b.s_score>=70 and b.s_score < 80 then 1 else 0 end)/count(1) "中等率",
    sum(case when b.s_score>=80 and b.s_score < 90 then 1 else 0 end)/count(1) "优良率",
    sum(case when b.s_score>=90 then 1 else 0 end)/count(1) "优秀率"
from course a join score b on a.c_id = b.c_id
GROUP BY 1

-- 19.按各科平均成绩从低到高和及格率的百分数从高到低顺序
SELECT sc.c_id,c_name,round(avg(s_score),2) ‘avg_score‘,
    concat(round(sum(case when s_score >= 60 then 1 else 0 end)/count(1)*100,2),‘%‘) "及格率"
from score sc join course c on sc.c_id = c.c_id
GROUP BY sc.c_id
ORDER BY avg_score,"及格率" desc

-- 20.查询学生的总成绩并进行排名
--有rank函数时
SELECT sc.s_id,st.s_name,sum(s_score) as sum_score,
	rank() over(ORDER BY sum(sc.s_score) desc) as score_rank
FROM score sc join student st on sc.s_id = st.s_id 
GROUP BY sc.s_id;

--无rank函数时
SELECT a.s_id,a.s_name,
	@i := @i +1 as 序号,
	@k := (case when  @score = a.total_score then  @k else @i end) as 排名,
	@score := a.total_score as total_score
from(
	SELECT st.s_id ,s_name ,sum(sc.s_score) as total_score
	from student st join score sc on st.s_id = sc.s_id 
	GROUP BY st.s_id
	ORDER BY total_score desc
	)a,
	(SELECT @i:=0,@k:=0,@score:=0)b 
	
-- 21.查询不同老师所教不同课程平均分从高到低显示
SELECT t.t_id,t_name,c.c_id,c.c_name,
	round(avg(s_score),2) avg_score
from teacher t 
	join course c on t.t_id = c.t_id 
	join score sc on c.c_id=sc.c_id
GROUP BY t_id,c_id
ORDER BY avg_score desc

-- 22.查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
--row_number 分数相同的两个人的名次按顺序确定
SELECT st.*,c.c_id,c.c_name,s.myrank
from student st 
	join (SELECT s_id,c_id,ROW_NUMBER() over(partition BY score.c_id order by s_score desc) as myrank from score ) s on st.s_id=s.s_id
	join course c on s.c_id = c.c_id
WHERE myrank in (2,3)

-- 或者自己写，即如果存在并列排名的情况，单个课程代码如下，多个课程的连接有点问题
SELECT st.*,sc.c_id,c_name,s_score,myrank
FROM student st 
	join score sc on st.s_id=sc.s_id and c_id = ‘03‘
	join course c on sc.c_id=c.c_id
	join (SELECT s_id,c_id,
		@i:=@i+1,
		@k:=(case when @score=s_score then @k else @i end) myrank,
		@score:=s_score
		from score sc
			join (SELECT @i:=0,@k:=0,@score:=0)b on sc.c_id=‘03‘
		ORDER BY s_score desc
		)a on sc.s_id = a.s_id
WHERE myrank<4

-- 23.统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
SELECT c.c_id,c_name,
	sum(case when s_score<60 then 1 else 0 end)/count(s_score) as "[0-60]比例",
	sum(case when s_score<60 then 1 else 0 end) as "[0-60]人数",
	sum(case when s_score<70 and s_score>=60 then 1 else 0 end)/count(s_score) as ‘[60-70]比例‘,
	sum(case when s_score<70 and s_score>=60 then 1 else 0 end) as ‘[60-70]人数‘,
	sum(case when s_score<85 and s_score>=70 then 1 else 0 end)/count(s_score) as ‘[70-85]比例‘,
	sum(case when s_score<85 and s_score>=70 then 1 else 0 end) as ‘[70-85]人数‘,
	sum(case when s_score>=85 then 1 else 0 end)/count(s_score) as ‘[85-100]比例‘,
	sum(case when s_score>=85 then 1 else 0 end) as ‘[85-100]人数‘
from score sc join course c on sc.c_id = c.c_id
GROUP BY c_id

-- 24.查询学生平均成绩及其名次
SELECT st.s_id,s_name,round(avg(s_score),2) as avg_score,
	rank() over(ORDER BY avg(s_score) desc) as avg_rank
from student st join score sc on st.s_id=sc.s_id 
GROUP BY s_id

-- 25.查询各科成绩前三名的记录
SELECT * from(
    SELECT  st.s_id,s_name,sc.c_id,c_name,s_score,
        ROW_NUMBER() over(partition BY sc.c_id order by s_score desc) as myrank
    from student st 
        JOIN score sc on st.s_id = sc.s_id
         join course c on sc.c_id = c.c_id
    GROUP BY c_id,s_id) t
WHERE myrank < 4

-- 26.查询每门课程被选修的学生数
SELECT sc.c_id,c_name,count(sc.s_id)
FROM score sc join course c on sc.c_id=c.c_id
GROUP BY c_id 

-- 27.查询出只选修了一门课程的全部学生的学号和姓名
SELECT st.s_id,s_name c_name
from student st 
	right JOIN score sc on st.s_id=sc.s_id
	join course c on sc.c_id=c.c_id
GROUP BY sc.c_id HAVING COUNT(st.s_id)=1

-- 28.查询男生、女生人数
SELECT s_sex,count(s_id) from student GROUP BY s_sex 

-- 29.查询名字中含有"风"字的学生信息
SELECT * from student  WHERE s_name LIKE ‘%风%‘;

-- 30.查询同名同性学生名单，并统计同名人数
SELECT s_id,s_name,count(s_id) from student GROUP BY s_name,s_sex 

-- 31.查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)
SELECT s_id,s_name,s_birth from student WHERE YEAR(s_birth)=1990

-- 32.查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
SELECT sc.c_id, c_name, round(avg(s_score),2) as avg_score
from score sc join course c on sc.c_id = c.c_id 
GROUP BY c_id 
ORDER BY avg_score ,c_id desc

-- 33.查询不及格的课程，并按课程号从大到小排列
SELECT sc.c_id,c_name,sc.s_id,s_score
from score sc join course c on sc.c_id=c.c_id
WHERE s_score<60
ORDER BY sc.c_id desc

-- 34.查询课程编号为"01"且课程成绩在60分以上的学生的学号和姓名
SELECT st.s_id ,s_name,s_score 
from student st join score sc on st.s_id = sc.s_id 
WHERE s_score > 60 and sc.c_id = 01

-- 35.查询所有学生的课程及分数情况
SELECT * from(
	SELECT st.s_id ,s_name ,sc.c_id,c_name,s_score 
	from student st 
		left join score sc on st.s_id = sc.s_id
		JOIN course c on sc.c_id = c.c_id
		) tb PIVOT(sum(s_score) for c_name in ([语文],[数学],[英语])) t
-- mysql不支持pivot函数转换

-- 36.查询任何一门课程成绩在70分以上的姓名、课程名称和分数
SELECT s_name,c_name,s_score
from student st 
	join score sc on st.s_id = sc.s_id
	JOIN course c on sc.c_id = c.c_id
WHERE st.s_id not in(SELECT s_id from score WHERE s_score<=70)

-- 37.查询课程名称为"数学"，且分数低于60的学生姓名和分数
SELECT s_name,s_score
from student st 
	join score sc on st.s_id = sc.s_id and s_score<60
	JOIN course c on c.c_id = sc.c_id and c_name=‘数学‘ 

-- 38.查询课程编号为03且课程成绩在80分以上的学生的学号和姓名
SELECT st.s_id,s_name
FROM student st 
	JOIN score sc on st.s_id = sc.s_id and sc.c_id=03 and s_score>80

-- 39.求每门课程的学生人数
SELECT c_id,count(s_score) as ‘学生人数‘ from score GROUP BY c_id

-- 40.查询选修“张三”老师所授课程的学生中，成绩最高的学生姓名及其成绩
SELECT  s_name,s_score
from student st 
	JOIN score sc on st.s_id=sc.s_id
	JOIN course c on sc.c_id = c.c_id
	join teacher t on c.t_id = t.t_id and t_name=‘张三‘
ORDER BY s_score desc LIMIT 0,1
#mysql 没有top语句

-- 41.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT s_id,c_id,s_score
from score
WHERE s_score in(
	SELECT s_score from score GROUP BY s_score HAVING count(s_id)>1
	)

-- 42.查询每门功课成绩最好的前两名
SELECT * from(
	SELECT st.s_id,s_name,sc.c_id,c_name,s_score,
		 ROW_NUMBER() over(partition BY sc.c_id order by s_score desc) as myrank
	from student st 
		join score sc on st.s_id = sc.s_id
		join course c on sc.c_id=c.c_id
		) a
WHERE myrank<3

-- 43.统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT sc.c_id,count(s_id) as ‘选修人数‘
from score sc 
GROUP BY c_id HAVING count(s_id)>5
ORDER BY ‘选修人数‘ desc,c_id asc

-- 44.检索至少选修两门课程的学生学号
SELECT s_id,count(c_id) as ‘选修课程数‘ from score GROUP BY s_id HAVING count(c_id)>1

-- 45.查询选修了全部课程的学生信息
SELECT st.*
from student st JOIN score sc on st.s_id = sc.s_id
GROUP BY s_id HAVING count(c_id) =
	(SELECT count(DISTINCT c_id) from score)

-- 46.查询各学生的年龄
SELECT s_id,s_name,(YEAR(sysdate())-YEAR(s_birth)) as s_age from student

--47.查询本周过生日的学生
SELECT * from student WHERE WEEKOFYEAR(now())-WEEKOFYEAR(s_birth)=0
SELECT WEEKOFYEAR(now())

-- 48.查询下周过生日的学生
SELECT * from student WHERE WEEKOFYEAR(now())-WEEKOFYEAR(s_birth)=-1

-- 49.查询本月过生日的学生
SELECT * from student WHERE month(now())-MONTH(s_birth)=0

-- 50.查询下月过生日的学生
SELECT * from student WHERE month(now())-MONTH(s_birth)=-1