mysql有意思的题目记录

 1.写sql，查找一张表中分数在0-60,60-80,80-100三个层次的学生人数

• 用到了if函数：if（条件,a,b）
  • 条件为true，返回a，否则返回b

select sum(if(score between 0 and 60,1,0)) as '0-60',sum(if(score between 60 and 80,1,0)) as '60-80',
sum(if(score BETWEEN 80 and 100,1,0)) as '80-100' from myscores
-- 可以替换下列的三条sql，节省系统的开销
select count(*) from myscores where score between 0 and 60;
select count(*) from myscores where score between 60 and 80;
select count(*) from myscores where score between 80 and 100;

---

2.四张表数据如下

• 学生表student

• 教师表teacher

• 科目表course

• 成绩表sc

  1.用一条SQL语句查询每门课程都是大于等于80分的学生的姓名

select s.sname,count(*) from student s 
inner join sc
on s.sid=sc.sid where sc.score>=80
group by s.sid having count(*)=(select count(*) from course)

2.查询“语文”课程比“数学”课程成绩高的学生的信息以及课程分数

select s.*,a.score 语文成绩,b.score 数学成绩 
from student s,(select * from sc where cid=1)a,(select * from sc where cid=2) b
where a.sid=b.sid and s.sid=a.sid and a.score>b.score

 3.查询在SC表中有成绩的学生信息

select distinct * from sc
left join student s
on sc.sid=s.sid where score is not null;
-- 或者
select * from Student 
WHERE Sid in
(  
   select sid FROM SC 
   WHERE score is not NULL
)