《SQL面试50题》刷题笔记 day12（ 知识点：各种小知识点）

问题19 查询出只选修两门课程的学生学号和姓名

#联结：
select sc.sid,s.sname
from student s, score sc
where s.sid = sc.sid 
group by sc.sid 
having count(sc.cid)=2;
#子查询：
select sid, sname 
from student 
where sid in
(select sid from score group by sid having count(cid)=2);

问题20 查询所有课程的成绩第2名到第3名的学生信息及总成绩

Select s.sid,s.sname,s.sbirth,s.ssex,sum(sc.sscore) as tot 
from student s, score sc
Where s.sid=sc.sid 
group by s.sid,s.sname,s.sbirth,s.ssex
order by tot desc 
limit 1,2;

问题21 查询男生、女生人数

#方法1 case when
select 
sum(case when ssex ='男' then 1 else 0 end) as nansh,
sum(case when ssex ='女' then 1 else 0 end) as nvshu 
from student;
#方法2 union或union all
Select ssex, count(ssex) from student where ssex='男'
union all
Select ssex, count(ssex) from student where ssex='女';方法3
方法3 分组函数
Select ssex, count(ssex) from student group by ssex;

问题22 查询名字中含有"风"字的学生信息

Select * from student where sname like '%风%';

select * 
from student
where sname REGEXP('风');

问题23 查询同名学生名单，并统计同名人数

#方法1 分组筛选
Select sname, count(sname) 
from student 
group by sname having count(sname)>1;
#运行结果
#Empty set (0.00 sec)
方法2 联结
Select s1.sname, count(s1.sname) 
from student s1, student s2
Where s1.sname=s2.sname and s1.sid<> s2.sid;
#运行结果
#+-------+-----------------+
#| sname | count(s1.sname) |
#+-------+-----------------+
#| NULL  |               0 |
#+-------+-----------------+

问题24 查询1990年出生的学生名单

#时间函数year()
Select * from student where year(sbirth) ='1990';
#like+通配符%
select * from student where sbirth like '1990%';