#--学生表
create table student(
s_id vaechar(20),
s_name vaechar(20) not NULL,
s_birth vaechar(20) not NULL,
s_sex vaechar(10) not NULL,
constraint pk_student PRIMARY KEY (s_id))
#--课程表
create table course(
c_id vaechar(20),
c_name vaechar(20) not NULL DEFAULT '',
t_id vaechar(20) not NULL,
PRIMARY KEY(c_id))
#--教师表
create table teacher(
t_id vaechar(20),
t_name vaechar(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))
alter table teacher convert to character set utf8
#--插入学生表测试数据
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);
#-- 26、查询每门课程被选修的学生数
select s.c_id, c.c_name, count(*)
from
(score s
join
course c
on s.c_id = c.c_id)
group by s.c_id
#--- 27、查询出只有两门课程的全部学生的学号和姓名
select * from student where s_id in
(select s_id from score
group by s_id
having count(*) = 2)
#--- 28、查询男生、女生人数
select s_sex, count(*)
from student
group by s_sex
#--- 29、查询名字中含有"风"字的学生信息
select * from student
where s_name like '%风%'
#--- 30、查询同名同性学生名单，并统计同名人数
select a.s_name,a.s_sex,count(*) from
student a
join
student b on a.s_id !=b.s_id and a.s_name = b.s_name and a.s_sex = b.s_sex
group by a.s_name,a.s_sex
#--- 31、查询1990年出生的学生名单
select * from student
where s_birth like '1990%'
#--- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select c_id, avg(s_score) from score
group by c_id
order by avg(s_score) desc, c_id asc
#--- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select st.s_id, st.s_name, avg(s.s_score) from
(score s join student st
on s.s_id = st.s_id)
group by s.s_id
having avg(s.s_score) >= 85
#--- 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
select c.c_name, t.s_name, s.s_score
from
(score s join course c on s.c_id = c.c_id
join student t on s.s_id = t.s_id)
where c.c_name = '数学' and s.s_score < 60
#--- 35、查询所有学生的课程及分数情况；
select st.s_id, st.s_name,
sum(case c.c_name when '语文' then s.s_score else 0 end) as '语文',
sum(case c.c_name when '数学' then s.s_score else 0 end) as '数学',
sum(case c.c_name when '英语' then s.s_score else 0 end) as '英语',
sum(s.s_score) '总分'
from
(student st left outer join score s on st.s_id = s.s_id
left outer join course c on s.c_id = c.c_id)
group by st.s_id
#--- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
select st.s_name, c.c_name, s.s_score
from
(score s join student st on s.s_id = st.s_id
join course c on s.c_id = c.c_id)
where s.s_score >= 70
#--- 37、查询不及格的课程
select s.s_id, c.c_name, s_score from
(score s left join course c on s.c_id=c.c_id)
where s_score<60
#---38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；
select s.s_id, s.c_id, st.s_name, s.s_score
from
(score s left join student st on s.s_id=st.s_id)
where s.c_id='01' and s.s_score>=80
#--- 39、求每门课程的学生人数
select c_id, count(c_id) from score group by c_id
#--- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
select t.t_name, st.s_id, st.s_name, s.s_score
from
(score s left join course c on s.c_id=c.c_id
left join teacher t on c.t_id=t.t_id
left join student st on s.s_id=st.s_id)
where t.t_name='张三'
group by s.s_score desc
limit 1
#--- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select distinct s.s_id, s.c_id, s.s_score from
score s cross join score sc
where s.s_score=sc.s_score and s.c_id!=sc.c_id
#--- 42、查询每门功成绩最好的前两名
select s.s_id, s.c_id, s.s_score from
score s left join score sc
on s.c_id=sc.c_id and s.s_score<sc.s_score
group by s.s_id, s.c_id having count(sc.s_id) < 2
order by s.c_id,s.s_score desc
#--- 43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，
--查询结果按人数降序排列，若人数相同，按课程号升序排列
select c_id, count(c_id) from score
group by c_id having count(c_id)>5
order by count(c_id)desc ,c_id asc
#--- 44、检索至少选修两门课程的学生学号
select s_id, count(c_id) from score
group by s_id
having count(c_id)>=2
#--- 45、查询选修了全部课程的学生信息
select * from student where s_id in
(select s_id from score group by s_id
having count(s_id)=(select count(*) from course))
#---46、查询各学生的年龄，按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select s_name,
(DATE_FORMAT(now(),'%Y')-DATE_FORMAT(cast(s_birth as date),'%Y'))-
(case when DATE_FORMAT(now(),'%m%d')>DATE_FORMAT(cast(s_birth as date),'%m%d') then 0 else 1 end) as age
from student
#--- 47、查询本周过生日的学生
select * from student where WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))=WEEK(s_birth)
#--- 48、查询下周过生日的学生
select * from student where week(date_format(now(),'%Y%m%d'))+1=week(s_birth)
#--- 49、查询本月过生日的学生
select * from student where MONTH(DATE_FORMAT(NOW(),'%Y%m%d')) = MONTH(s_birth)
#--- 50、查询下月过生日的学生
select * from student where MONTH(DATE_FORMAT(NOW(),'%Y%m%d'))+1 = MONTH(s_birth)
