新手小白，一周跟练MYSQL-- 学生信息查询项目

#建库
create database exercise50;
use exercise50;

# 制作学生表
create table Student(
s_id varchar(20) primary key,
s_name varchar(20) not null default '',   #默认‘’，非空
s_birth varchar(20) not null default '',
s_sex varchar(10) not null default ''
);
drop table student_test;
# 课程表
create table course(
c_id varchar(20),
c_name varchar(20) not null default '',
t_id varchar(20) not null ,
primary key (c_id)
);
select * from course;

# 教师表
create table teacher(
t_id varchar(20) not null default '',
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)
);
desc score;

# 插入数据(student表)
drop table student;

insert into student values('01','赵雷','1990-01-01','男');
insert into student values('02','刘明','1995-01-01','女');
insert into student values('03','大玲','1995-01-01','女');
insert into student values('04','牛牛','1997-01-01','女');
insert into student values('05','李云','2000-3-8','男');

# 课程表测试数据a
desc course;
insert into course values('01','语文','02');
insert into course values('02','数学','01');
insert into course values('03','语文','02');
insert into course values('04','数学','01');
insert into course values('05','英语','03');
insert into course values('06','英语','03');
select * from course;

#教师表测试数据
desc teacher;
insert into teacher values('01','张三');
insert into teacher values('02','李四');
insert into teacher values('03','王五');
insert into teacher values('04','李');
insert into teacher values('05','李健康');
insert into teacher values('06','李健康');
select * from teacher;

# 成绩表测试
desc score;  
truncate score;
insert into score values('01','01','80');
insert into score values('02','01','99');
insert into score values('03','01','69');
insert into score values('01','02','89');
insert into score values('02','02','99');
insert into score values('03','02','59');
select * from score;

####实战查询######
#  1. 查询01课程比02 课程成绩高的学生信息及课程分数
select st.*,sc1.s_score as 数学,sc2.s_score as 语文
from student st 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'
having sc1.s_score > sc2.s_score;

select st.*,sc1.s_score,sc2.s_score
from student st 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'
having sc1.s_score> sc2.s_score;

# 2.查询 01 成绩比03 成绩高的学生信息和分别的成绩alter
select st.*,sc1.s_score as 数学,sc3.s_score as 英语
from student st left join score sc1
on st.s_id = sc1.s_id and sc1.c_id='01'
left join score sc3 on sc3.c_id ='03' and st.s_id=sc3.s_id
where sc1.s_score > ifnull(sc3.s_score,60) ;

select st.*,sc1.s_score 数学,sc2.s_score 英语
from  student st 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'
where sc1.s_score >ifnull(sc2.s_score,0);

# 3. 查询平均成绩大于60的同学的学生编号和姓名和平均成绩
select st.* ,st.s_id 编号,st.s_name,round(avg(score.s_score),2) as a
from student st left  join score
on  st.s_id=score.s_id
group by st.s_id
having a >60;
 
select st.s_id,st.s_name,avg(score.s_score)
from student st   left join score
on st.s_id=score.s_id
group by st.s_id
having avg(score.s_score) >60;  # 当已经给st赋值后不能再select student!

# 4.查询平均成绩小于60分 的学生编号和姓名和平均成绩
select st.s_id,st.s_name,round(avg(score.s_score),2)
from student st left join score on st.s_id=score.s_id
group by st.s_id
having avg(score.s_score)<60;

select st.s_id ,st.s_name ,round(avg(score.s_score),1)
from student st left join score
on st.s_id=score.s_id
group by st.s_id
having avg(score.s_score)>60;

# 5.查询所有同学编号，姓名，选课总数，所有课程总成绩
select st.s_id 编号, st.s_name 姓名,count(score.c_id),sum(score.s_score)
from student st
left join score on st.s_id = score.s_id
group by st.s_id ;

#6.查询’李‘姓老师数量
select teacher.t_name,count(teacher.t_id) from teacher
where teacher.t_name like '李%'
group by teacher.t_id;

select count(tc.t_id),tc.t_name
from teacher tc
where tc.t_name like '李_%'
group by tc.t_id;

select count(teacher.t_id) ,teacher.t_name
from teacher
where teacher.t_name like '李%'
group by teacher.t_name;

# 7.查询张三老师授课的同学的所有信息
select st.*,course.t_id,teacher.t_name
from student st  join score
on st.s_id=score.s_id
left join  teacher on teacher.t_id=course.t_id
where  teacher.t_name='张三';

select st.*
from student st left join score on st.s_id=score.s_id
left join course on score.c_id=course.c_id
left join teacher on course.t_id=teacher.t_id
where teacher.t_name='张三';    

# 8.查询没学过"张三"老师授课的同学的信息,并给出每个人的平均成绩
select st.*,round(avg(score.s_score),2)
from student st left join score on st.s_id=score.s_id
left join course on score.c_id=course.c_id
left join teacher on course.t_id=teacher.t_id
where  '张三' not in (teacher.t_name)   #not in 后面加括号！
group by st.s_id;  

# 9.查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select student.*
from student left join score
on '01' in (score.s_id) and '02' in (score.s_id);

#10 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select student.*,count(score.c_id)  from student
left join score on student.s_id=score.s_id
where '01' in(score.c_id) and '02'not in (score.c_id)
group by student.s_id
;

desc score;
#11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select student.s_id,student.s_name,round(avg(score.s_score))
from student left join score
on student.s_id=score.s_id
where score.s_score>60
group by student.s_id
having count(*)>1;
desc score;

#12  检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select st.*, score.s_score  as 01分数
from  student st left join score
on score.s_id=st.s_id
where score.c_id='01' and score.s_score>60
group by st.s_id
order by score.s_score desc;

# 13 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select *
from score left join ( select score.s_id,avg(score.s_score) avg
                       from  score
                       group by score.s_id) t
on  score.s_id=t.s_id
order by avg desc;

#14 查询各科成绩最高分、最低分和平均分：
select * from course right join
                    (select score.c_id id,max(score.s_score),min(score.s_score),avg(score.s_score)
                    from score
                    group by score.c_id) m
on m.id = course.c_id
order by course.c_id    ;
#左连接： 左表全部保留，如果不匹配的行数据，依旧保留，右侧写成null值
#15 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺(不会，做了将总分排序的)
select m.*,st.s_name from student st left  join
                        (select score.s_id,sum(ifnull(score.s_score,0)) sum
                        from score
                        group by score.s_id
                        order by sum) m
on m.s_id=st.s_id
order by m.sum desc;
desc score;
# 17 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
select score.c_id,course.c_name,sum(case when ifnull(score.s_score,0) between 98  and 100 then 1 else 0 end) '85-100'
from score
left  join course
on score.c_id=course.c_id
group by score.c_id;

#    case when ifnull(score.s_score,0) between 98  and 100 then 1 else 0 end

#18查询各科综合成绩前三名的记录
set @i:=0;  #设置变量
select t.c_id,t.avg,@i:= @i+1 as 排名
from (select score.c_id,avg(score.s_score) avg
      from score
      group by score.c_id
      order by avg desc) t;
      
# 19.查询各科综合成绩排名的记录，相同时排名一致

set @i :=0;
set @p :=0;
set @q:=0;
select t.c_id ,t.avg,@p:=avg,if(@P=@q,@i,@i:=@i+1)as 'rank' ,@q:=@p
from
(select sc.c_id,avg(sc.s_score) avg
from score sc
group by sc.c_id
order by avg desc) t;

#20查询学生的总成绩，并进行排名，总分重复时保留名次空缺
set @i :=0;
set @p:=0;
set @q:=0;
select  t.s_id, t.sum,@p:=t.sum,if(@p=@q,@i,@i:=@i+1),@q:=@p
from
(select score.s_id,sum(score.s_score) sum
from score
group by score.s_id
order by sum desc )t;

#21.查询学生的总成绩，并进行排名，总分重复时bu保留名次空缺
set @i:=0;
select t.s_id,t.sum,@i:=@i+1
from (select sc.s_id,sum(sc.s_score) sum
from score sc
group by sc.s_id
order by sum desc) t ;

#22.统计各科成绩各分数段人数：课程编号，课程名称，[100-85），[85-70），[70-60），[60-0）及所占百分
select course.c_name,score.c_id,sum(if(score.s_score between 85 and 100,1,0)) as '[85-100]',sum(if(score.s_score between 70 and 85,1,0)) as '[85-70]',sum(if(score.s_score between 60 and 70,1,0)) as '[70-60]',
sum(if(score.s_score <60,1,0)) as '[60-0]'
from course right join score
on course.c_id=score.c_id
group by score.c_id;

# 23.查询各科成绩前三名的记录
set @i:=0;
set @p:=0;
set @q:=0;

select tt.c_id,tt.编号,tt.s_score,st.s_id,st.s_name
from (select t.*,@p:=t.c_id ,if(@p=@q,@i:=@i+1,@i:=1) 编号,@q:=@p
from(select score.s_score,score.c_id,score.s_id
     from score
     order by score.c_id,score.s_score desc) t)tt
join student st
on st.s_id=tt.s_id
having tt.编号<4
 ;   #1.排序 按照c_id 与s_score
     #2. 编号，将课程相同的进行编号，不同的 从1开始编号alter
     #3. 与st表连接，筛选编号数《4的

#24 .查询每门课程被选修的学生数
select score.c_id,count(score.s_id)
from score
group by score.c_id;

# 25 查询出只选修两门课程的学生学号和姓名
select tt.s_id,student.s_name
from (select score.s_id
      from score
      group by score.s_id
      having count(score.c_id)=2) as tt  
left join student
on student.s_id=tt.s_id
order by tt.s_id desc;

#26 .查询男生、女生人数
select student.s_sex 性别,count(*) 人数
from student
group by student.s_sex;

select * from teacher;
insert into student values('06','聚聚','2000-09-03','女');
select student.s_sex 性别 ,count(*)  人数
from student
group by student.s_sex ;

#27查询名字中含有「聚」字的学生和老师信息
select *
from student
where student.s_name like'%聚_';

#28查询同名同性学生名单，并统计同名人数
select student.s_name,count(student.s_name) a
from student
group by student.s_name
having a>1
order by count(student.s_name) desc;

insert into student values('07','聚聚','2000-09-03','女');

#29 查询 2000 年出生的学生名单
desc student;
select *
from student
where student.s_birth like '2000%' ;   #通配符 % 是指任意字符，可以是0个     _ 表示 一个字符，只能是一个，不能为空

# 30 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

select score.c_id,avg(score.s_score) avg
from score
group by score.c_id
order by avg,score.c_id asc;

# 31. 查询平均成绩大于等于 50 的所有学生的学号、姓名和平均成绩
select st.s_id,st.s_name,round(avg(score.s_score),2) avg
from student st  join score
on st.s_id=score.s_id
group by st.s_id
having avg>'50' ;

# 32 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

select course.c_name,score.s_score,st.s_name
from student st  join score
on st.s_id=score.s_id  and score.s_score<60 join course on course.c_id=score.c_id and course.c_name='数学';

#33.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
select score.c_id,course.c_name,score.s_score,st.s_name,st.s_id
from student st left join score
on st.s_id=score.s_id join course
on score.c_id=course.c_id    #on 后加两个表的连接条件，也可加筛选条件
group by st.s_id,score.c_id;

# 34 查询每门功课成绩最好的前两名
select sc1.*
from score sc1
where (  select count(*)
          from score sc
          where sc.c_id=sc1.c_id and sc1.s_score<sc.s_score)<2
; ## 不需要set @i:=0 赋变量，而是复制一遍表，然后选取 1表中分数小于2表分数次数小于2的数据，即为成绩的第一和第二，但是需要将两个表按照课程id进行匹配

#35 统计每门课程的学生选修人数（超过 5 人的课程才统计）
select if(count(*)>5,count(*),NUll)
from course cs
group by cs.c_id;

#36 检索至少选修两门课程的学生学号
select score.s_id,count(score.s_score) num
from score
group by score.s_id
having num=2 or num>2;

# 37 查询选修了全部课程的学生信
select  distinct st.*
        from student st  
        left join score sc
        on st.s_id=sc.s_id
        where sc.s_score like '%_%' ;

#38 查询各学生的年龄，只按年份来算
select st.s_birth, year(now())-year(st.s_birth) as 年龄
from student st
group by st.s_id;

#39 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select st.s_name, floor(datediff(current_date,st.s_birth)/365) as 年龄
from student st;

#40 查询本周过生日的学生
select st.s_name, week(now()),week(st.s_birth) as n
from student st
where week(now())=week(st.s_birth);

#41 查询下周过生日的学生
select st.s_name,st.s_id,week(now()),week(st.s_birth)
from student st
where week(st.s_birth)+1=week(now());

#42 查询本月过生日的学生
select st.s_name,
if(month(now())=month(st.s_birth),month(now()),'not his/her birth_month')
from student st;

# 43查询下月过生日的学生
select st.*
,if(month(now())+1=month(st.s_birth),concat(st.s_birth,'下月过生日'),'not') as '是否下个月生日'
from student st;