mysql练习题二练

查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数 
select s.*,s1.cid,s1.score,s2.cid,s2.score from student s join (select sid,cid,score from sc where cid = 01) s1 join (select sid,cid,score from sc where cid = 02) s2
on s1.sid=s2.sid and s1.score > s2.score and s.sid=s1.sid;
查询同时存在" 01 "课程和" 02 "课程的情况 
select * from (select * from sc where cid=01) s1 join (select * from sc where cid=02) s2 on s1.sid=s2.sid;
查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
select * from (select * from sc where cid=01) s1 left join (select * from sc where cid=02) s2 on s1.sid=s2.sid;
查询不存在" 01 "课程但存在" 02 "课程的情况
select * from sc where sid not in (select sid from sc where cid=01) and cid = 02;
查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select s.sid,s.sname,avg(sc.score) from student s join sc where s.sid=sc.sid group by sc.sid having avg(sc.score)>60;
查询在 SC 表存在成绩的学生信息 4.1 查有成绩的学生信息
select distinct s.* from student s join sc where s.sid=sc.sid;
查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null) 
select s.sid,s.sname,c.cidSum as '选课总数',c.scoreSum as '总成绩' from student s left join 
(select sid,count(cid) as cidSum,sum(score) as scoreSum from sc group by sid) c on s.SId=c.sid;
查询「李」姓老师的数量
select count(*) from teacher where tname like "李%";
查询学过「张三」老师授课的同学的信息
select s.* from student s join sc on sc.cid = (select c.cid kcid from teacher t join course c on t.tname="张三" and t.tid=c.tid) 
and s.sid=sc.sid;
查询没有学全所有课程的同学的信息
select s.* from student s left join sc on s.sid=sc.sid group by s.sid having count(sc.cid)<(select count(cid) from course);
查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
select * from student where sid in (select sid from sc where cid in (select cid from sc where sid=01)) group by sid;
查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息	group_concat函数的使用
select * from student
where sid in 
(select sid from sc
where sid != 1
group by sid
having group_concat(cid ORDER BY cid ) =
(select group_concat(cid ORDER BY cid )from sc where sid =01))
查询没学过"张三"老师讲授的任一门课程的学生姓名
select distinct sid,sname from student where sid not in
(select sc.sid from course c join teacher t join sc on tname="张三" and t.tid=c.tid and sc.cid=c.cid);
查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select s1.sid,s1.sname,s2.a from student s1 join (select sid,count(cid) c,avg(score) a from sc where score<60 group by sid) s2
on s1.sid=s2.sid and s2.c>=2;
检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select DISTINCT s.* from student s join sc on sc.cid=01 and sc.score<60 and s.sid=sc.sid order by sc.score desc
按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select sid,cid,sum(score) 总成绩,avg(score) 平均成绩 from sc group by sid order by avg(score) desc;
查询各科成绩最高分、最低分和平均分： 以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select c.cid,c.cname,count(sc.sid) '选修人数',max(sc.score) '最高分',min(sc.score) '最低分',
CONCAT(count(case when score>=70 and score<80 then 'zd' END)/count(sc.sid)*100,'%') '中等率',
CONCAT(count(case when score>=80 and score<90 then 'yl' END)/count(sc.sid)*100,'%') '优良率',
CONCAT(count(case when score>=90 then 'yx' END)/count(sc.sid)*100,'%') '优秀率',
CONCAT(count(case when score>=60 then 'jg' END)/count(sc.sid)*100,'%') '及格率'
from course c join sc on c.cid=sc.cid group by sc.cid
按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺 
-- 这里不知道咋在cid发生改变的时候将rank也重新计算了。。
select cid,score,@rank:=@rank+1 rank from sc join (select @rank:=0) rank order by cid asc,score desc；
-- 搞不出来
select * from (select cid,score,@rank1:=@rank1+1 rank1 from sc join (select @rank1:=0) rank1 on cid=01 order by score desc) a
join (select cid,score,@rank2:=@rank2+1 rank2 from sc join (select @rank2:=0) rank2 on cid=02 order by score desc) b
join (select cid,score,@rank3:=@rank3+1 rank3 from sc join (select @rank3:=0) rank3 on cid=03 order by score desc) c
15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次
查询学生的总成绩，并进行排名，总分重复时保留名次空缺 
select a.*,@rank:=@rank+1 '排名' from
(select sc.sid,sum(sc.score) '总成绩' from sc group by sc.sid ORDER BY sum(sc.score) desc) a 
join (select @rank:=0) rank
16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
case when score<=100 and score>85 then '' end
select cid,CONCAT(count(case when score<=100 and score>85 then '' end)/count(sc.sid)*100,'%') '[100-85]占比',
查询各科成绩前三名的记录
sele
查询每门课程被选修的学生数
select cid,count(sid) from sc group by cid;
查询出只选修两门课程的学生学号和姓名
select s.sid,s.sname from student s join sc on sc.sid=s.sid group by sc.sid having count(sc.cid)=2
查询男生、女生人数
select ssex,count(sid) from student group by ssex;
查询名字中含有「风」字的学生信息
select * from student where sname like '%风%'
查询同名同性学生名单，并统计同名人数
select s1.*,(@num:=2) '人数' from student s1 join student s2 join (select @num:=0) a on s1.sname=s2.sname and s1.ssex=s2.ssex and s1.sid!=s2.sid;
查询 1990 年出生的学生名单
select * from student where YEAR(Sage)=1990;
查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select cid,avg(score) '平均成绩' from sc group by cid order by avg(score) desc,cid asc;
查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select s.sid,s.sname,avg(sc.score) from student s join sc group by sc.sid having avg(sc.score)>85;
查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
select s1.sid,s1.sname,s2.score from student s1 join sc s2 on s2.cid=(select cid from course where cname='数学') and s1.sid=s2.sid;
查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
select s.sid,sc.cid,sc.score from student s left join sc on sc.sid=s.sid;
查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select s.sname '学生姓名',c.mz '课程名字',c.fs '分数' from student s join 
(select sc.sid xh,c1.cname mz,sc.score fs from course c1 join sc on c1.cid=sc.cid and sc.score>70) c on c.xh=s.sid;
查询不及格的课程
select cid,score from sc where score<60;
查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
select s.sid,s.sname from student s join sc on sc.cid=01 and sc.score>80 and sc.sid=s.sid;
求每门课程的学生人数
select cid,count(sid) from sc group by cid;
成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select s1.*,s2.score from student s1 join 
(select cid,sid,score from sc where cid=(select cid from course c join teacher t on t.tname='张三' and c.tid=t.tid) having max(score)) s2 on s2.sid=s1.sid;
成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select s.sid,sc.cid,sc.score from student s join (select sid,cid,score from sc where)
查询每门功成绩最好的前两名
(select sid,cid,score from sc where cid=01 order by score  desc limit 2)
union
(select sid,cid,score from sc where cid=02 order by score  desc limit 2)
union
(select sid,cid,score from sc where cid=03 order by score  desc limit 2)
统计每门课程的学生选修人数（超过 5 人的课程才统计）。
select cid,count(sid) from sc group by cid having count(sid)>5;
检索至少选修两门课程的学生学号
select sid from sc group by sid having count(cid)>=2;
查询选修了全部课程的学生信息
SELECT s.* from student s join sc on s.sid=sc.sid group by sc.sid having count(sc.cid)=(select count(*) from course);
查询各学生的年龄，只按年份来算
select sname '姓名',(YEAR(CURRENT_DATE)-year(sage)) 年龄 from student;
按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select sname '姓名',(YEAR(CURRENT_DATE)-year(sage)) 年龄 from student;
查询本周过生日的学生
select * from student where week(CONCAT(YEAR(CURRENT_DATE),mid(sage,5,6)))=week(CURRENT_DATE);
查询下周过生日的学生
select * from student where week(CONCAT(YEAR(CURRENT_DATE),mid(sage,5,6)))=week(CURRENT_DATE)+1;
查询本月过生日的学生
select * from student where MONTH(sage)=MONTH(CURRENT_DATE);
查询下月过生日的学生 
select * from student where MONTH(sage)=MONTH(CURRENT_DATE)+1;

结合项目中 做报表的时候查询指定时间内的数据

switch(dateRange) {
			case "今日":
				sql += " DATE_FORMAT(?,'%m-%d') = DATE_FORMAT(NOW(), '%m-%d')";
				break;
			case "本周":
				sql += " week(CONCAT(YEAR(CURRENT_DATE),mid(?,5,6)))=week(CURRENT_DATE)";
				break;
			case "本月":
				sql += " MONTH(?)=MONTH(CURRENT_DATE)";
				break;
			case "全年":
				sql += " YEAR(?)=YEAR(CURRENT_DATE)";
				break;
//			default:
//				System.out.println("default");
		}