查询练习2

查询练习2

![在这里插入图片描述](https://img-blog.csdnimg.cn/20210628183351124.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzU2OTQ1MTM4,size_16,color_FFFFFF,t_70#pic_center

1、查询学生表中男生，女生各有多少人

--查询男女各多少人,进行分组
select sex as '性别',COUNT(1) as '人数' from Student group by Sex;

2、查询 ‘1班’ 的学生信息

--先查询出来1班的id,再根据1班的id查学生信息
select * from Student s inner join Classes c on s.ClassId=c.Id 
and c.ClassName='1班';

select * from Student where ClassId=(select id from Classes 
where ClassName='1班');

3、查询‘1班’学生的人数

--两表连接查询,根据班级分组,筛选出一班的人数
select ClassName '班级',COUNT(1) '人数' from Student s 
inner join Classes c on s.ClassId=c.Id 
group by ClassName having ClassName='1班';

--先查询出来1班的id,查询1班的信息,再count()求人数
select count(1) from Student where 
ClassId=(select Id from Classes where ClassName='1班');

4、查询‘1班’中男生，女生各有多少人

select s.sex '性别',COUNT(1) as '人数' from Student s inner join 
Classes c on s.ClassId=c.Id and c.ClassName='1班' group by Sex;

select Sex,COUNT(1) '性别' from Student where ClassId=
(select Id from Classes where ClassName='1班') group by Sex;

5、查询每个班级各有多少人，展示出班级名称。

select c.ClassName as '班级',COUNT(1) as '人数' from Student s 
left join Classes c on s.ClassId=c.Id group by ClassName;

select (select ClassName from Classes where 
Classes.Id=Student.ClassId) '班级',COUNT(1) '人数' from Student 
group by ClassId

6、查询‘张华三’同学个人信息以及对应的班级名信息

select s.*,c.ClassName from Student s 
left join Classes c on s.ClassId=c.Id where s.RealName='张华三';

select *,(select ClassName from Classes 
where Classes.Id=Student.ClassId) from Student where RealName='张华三'

7、查询没有学生的班级信息

--外连接,没有学生的班级信息
select * from Student s right join Classes c
on s.ClassId=c.Id where s.Id is null;

select * from Classes c left join Student s
on c.Id=s.ClassId where s.Id is null;

8、查询学生中分数大于60分的学生信息以及对应的班级名称

select * from Student s inner join Classes c on s.ClassId=c.Id 
where s.Score>60;


select *,(select ClassName from Classes 
where Classes.Id=Student.ClassId) 班级名称 
from Student where Student.Score>60

9、查询学生人数大于2的班级名称，班级人数。

--第五题类似:查询每个班级各有多少人，展示出班级名称。
select ClassName,COUNT(ClassName)'班级人数' from Student s inner join 
Classes c on s.ClassId=c.Id group by ClassName 
having COUNT(ClassName)>2;

select count(Student.Id) 班级人数,
(select ClassName from Classes where classes.Id=Student.Classid) 班级名称 
from Student group by ClassId having count(Student.Id)>2;

10、查询每个班级的及格(>60)学生人数，展示出班级名称。

select Classes.ClassName,count(1) as '及格学生人数' from student inner join Classes 
on Classes.Id= Student.ClassId 
and Student.Score >=60 
group by Classes.ClassName;

select (select ClassName from Classes where Classes.Id = Student.ClassId) '班级名称',COUNT(1) '及格人数'
from Student where Score>60 group by ClassId  

查询练习1
查询练习3
查询练习4
查询练习5