记录一下MySQL的一些查询语句使用案例

#简单查询并更据性别分组
select * from stu group by sex

#先对查询的学生信息按性别进行分组(分成男,女),然后分别统计每组学生的个数
select sex,count(sex)from stu group by sex;

#先对查询的学生信息按性别进行分组　然后计算平均分
select sex,avg(score) from stu group by sex 

# 先对学生按照班级分组　然后统计各组的学生数量，在按照人数多少进行降序排序
select classid,count(classid) from stu group by classid order by count(classid) desc 

# 先对学生按照班级分组　然后按照每个班的平均分多少进行降序排序
select avg(score) avgScore,classid FROM stu GROUP BY classid order by avgScore desc

# 查询所有学生,按年龄进行分组,然后分别统计每组>1的人数,再筛选年龄升序 (having　隐藏记录　有group by 才能有　having
SELECT age,count(*) from stu GROUP BY age HAVING count(*)>1 ORDER BY age ASC

#查找每个班的平均分
SELECT classid,AVG(score) avg FROM stu GROUP BY classid

#查找每个班大于平均分的人
SELECT a.* FROM stu a,
(SELECT classid,AVG(score) avg FROM stu GROUP BY classid) b
WHERE a.classid=b.classid AND a.score>= b.avg 

# 如果一张表中,pageNum表示查询的页码,pageSize表示查询每页的条数:
#select * from stu limit (pageNum-1)*pageSize,pageSize;
#对数据表中的学生信息进行分页显示,总共22条数据,我们每页显示5条
select * from stu LIMIT 5,5

#对数据表中的学生信息班级为YX2101进行分页显示,每页显示5条
select * from stu WHERE classid = 'YX2101' LIMIT 0,5

#内连接
select ... from tableName inner join tableName2;

#如果直接执行select … from tableNmae1 inner join tableName2; 会获取两张数据表中的数据集合的笛卡尔积
#(依次使用tableName1表中的每条数据 去 匹配tableName2的每条数据)
SELECT * FROM stu INNER JOIN lesson

#两张表同时用inner join连接查询之后产生笛卡尔积数据很多是无意义的,
#我们如何消除无意义的数据----添加两张进行连接的查询时的条件
#使用where设置过滤条件:先生成笛卡尔积再从笛卡尔积中过滤数据(效率很低)
select * from students inner join classes where students.cid=classes.class_id

#使用ON设置连接查询条件:先判断连接条件是否成立,如果成立两张表的数据进行组合在生成一条结果记录
select * from students inner join classes on students.cid=classes.class_id;

#筛选出学生中选修了 JavaSE课程的
select stu.stu_name,les.lesson_name from student01 stu INNER JOIN lesson les 
on stu.lesson_id=les.lesson_id WHERE les.lesson_name='JavaSE'

#如果不用内连接
SELECT stu.stu_name,les.lesson_name from lesson les,
(SELECT stu_name,lesson_id from student01 WHERE lesson_id=1) stu
WHERE les.lesson_id=stu.lesson_id

#左连接:显示左表中的所有数据,如果在右表中满足条件的数据,则进行匹配;如果右表中不存在匹配数据,则显示为null
SELECT * FROM student01 stu LEFT JOIN lesson les on stu.lesson_id=les.lesson_id 

#右连接:示右表中的所有数据,如果在右表中满足条件的数据,则进行匹配;如果左表表中不存在匹配数据,则显示为null
select * from student01 stu right join lesson les on stu.lesson_id = les.lesson_id

#查询所有Java班级中的学生信息
#a查询所有Java班的班级编号 结果是1，5
#子查询多行单列
select lesson_id from lesson where lesson_name like 'Java%';

#b查询这些班级编号中的学生信息(union 将多个查询语句的结果整合到一起)
select * from student01 where lesson_id = 1 union 
select * from student01 where lesson_id = 5 

#用子查询 如果查询返回的结果是多个值(单列多行),条件使用**in / not in**
select * from student01 where lesson_id in (select lesson_id from lesson where lesson_name like 'Java%');

#查询cid=1的班级中性别为男的学生信息
#传统的多条件查询
#子查询返回多个值,多行多列
select * from students where cid=1 and stu_gender='男';

#子查询(先查询cid=1班级中的所有信息,将这些信息作为一个整体虚拟表 在基于这个虚拟表查询性别为男的学生信息)
select * from (select * from students where cid=1) t where t.stu_gender='男';

#查找每个班大于平均分的人数，按照班级进行排序
#查了两张表，一张表是stu a一张表是虚拟表b，虚拟表b中有各个班级的平均分，
#然后拿stu a表中的classid取和虚拟表中的classid进行比对，再加上要分数大于平均分就好了
SELECT a.classid,count(a.id) FROM stu a,
(SELECT classid,AVG(score) avg FROM stu GROUP BY classid) b
WHERE a.classid=b.classid AND a.score>= b.avg group by classid ORDER BY classid