mysql 查询一个班级有多少男生和女生 sql 查询一个班级有多少男生和女生
提示:学习本文前,首先要熟练的掌握数据库的单表查询,以及多表查询的基本语句,本文主要讲述软件测试需要具体掌握的SQL语句的高级操作。创建好三个表
①t_stuent(id,sname,age,cid,phone,address)
②t_class(id,cname,teacher)
③t_grade(id,sid,chinese,math,english)
文章目录
- 1.分组查询---group by
- 2.分组查询---having
- 3.分组查询---排序
- 4.分组查询---限制数据显示数量
- 5.分组查询---case when...then...end
- 6.分组查询---内连接、左连接、右连接
- 课后练习
1.分组查询—group by
数据库中的分组查询就是将数据库中的数据进行分组,更直观的显示某些数据,将同一类的归为一组,例如求某个班级中的平均数,就需要以班为单位进行分组,或者求某个班的男生女生数量就需要按照性别进行分组,然后统计人数
数据库中的分组查询命令是 group by
例如:有一张学生信息表,要统计这个班的男生女生的人数
就需要用到分组查询,以性别为条件进行分组
输如查询命令,就能够查询出男女生的人数,注意:分组要和聚合函数一起使用,不使用聚合函数时,只能显示分组条件的这个字段
SELECT
sex as '性别',
COUNT(*) as '个数'
FROM
t_student
GROUP BY
sex;
2.分组查询—having
查询某个表中重名的人,也就是说首先要按照名字进行分组,但是有些不重名的也会显示,此时就需要对分组后的数据再进行限制也就是 having命令
SELECT
sname as '姓名',
COUNT(*) as '重名个数'
FROM
t_student
GROUP BY
sname
HAVING
COUNT(*) > 2;
简而言之,having命令和where命令作用一样,但是where限制适用于分组之前的查询 而 having用于分组之后的查询
例题:查询某个班语文成绩大于100分的语文同学的成绩的语文平均分,并且只显示平均分高于100分的班级
注意group by 与having的使用
SELECT
c.cname as '班级名称',
AVG(g.chinese) as '平均分'
FROM
t_student s
JOIN t_class c ON s.cid = c.id
JOIN t_grade g ON s.id = g.sid
WHERE
g.chinese > 100
GROUP BY
c.cname
having 平均分 >100 ;
3.分组查询—排序
查询某个班的平均成绩后,要对平均成绩进行排序,用到的命令:asc 和desc
例如将成绩从小到大排序
SELECT
c.cname AS '班级名称',
AVG(g.chinese) AS '平均分'
FROM
t_student s
JOIN t_class c ON s.cid = c.id
JOIN t_grade g ON s.id = g.sid
WHERE
g.chinese > 60
GROUP BY
c.cname
ORDER BY
平均分 ASC;
最后代码中的ASC 可以省略不写
成绩倒叙排列 就将最后代码改为 ORDER BY DESC;
desc不可省略,不区分大小写
4.分组查询—限制数据显示数量
因为实际工作中数据库中的表中有大量的数据,有一些数据我们不使用,但是查询的时候全部显示需要浪费大量的时间与资源,我们可以使用limit命令来控制显示的数据的多少
例如只显示两组平均分数据,代码最后加上 limit 0,2
0代表从哪个开始,注意计算机中的下标从0开始
2代表几条数据
SELECT
c.cname AS '班级名称',
AVG(g.chinese) AS '平均分'
FROM
t_student s
JOIN t_class c ON s.cid = c.id
JOIN t_grade g ON s.id = g.sid
WHERE
g.chinese > 60
GROUP BY
c.cname
ORDER BY
平均分 ASC
LIMIT 0,2;
5.分组查询—case when…then…end
查询数据,使年龄18一下的输出为青少年,18-30岁称为青年,大于30为老年,可以不显示实际年龄
SELECT
s.sname '姓名',
s.age '年龄',
(
CASE
WHEN s.age < 18 THEN
'青少年'
WHEN s.age >= 18
AND s.age <= 30 THEN
'青年'
ELSE
'老年'
END
) AS '阶段'
FROM
t_student s
JOIN t_class c ON s.cid = c.id
JOIN t_grade g ON s.id = g.sid;
6.分组查询—内连接、左连接、右连接
内连接就是我么之前学的join 全称为 inner join inner可以忽略不写
内连接就是取表之间的交集 (inner) join
左连接就是显示左边表的全部加上两表的交集 left join
右连接就是显示右边表的全部加上两表的交集 right join
课后练习
1.计算所有学生的英语成绩平均分 :
SELECT
AVG(mark.english) '英语平均分'
FROM
mark;
2.现有学号为11、 22 、33、 44、 55 用一条SQL语句实现列出这五个学生的数学成绩及其姓名、学生地址和电话号码
SELECT
mark.math '数学成绩',
NAME '姓名',
Address '地址',
Telno '电话号码'
FROM
mark
JOIN cust ON mark.studentno = cust.Studentno;
3.查询所有学生的姓名,计算机成绩,按照计算机成绩从高到低排序
SELECT
cust.`Name` '姓名',
mark.computer '计算机成绩'
FROM
mark
JOIN cust ON mark.studentno = cust.Studentno
ORDER BY mark.computer DESC;
4.查询所有总成绩大于240分的学生学号,姓名、总成绩,按照总成绩从高到低排序
SELECT
cust.Studentno '学号',
cust.`Name` '姓名',
SUM(
mark.english + mark.math + mark.computer
) score
FROM
mark
JOIN cust ON mark.studentno = cust.Studentno
GROUP BY
cust.Studentno
HAVING score > 240
ORDER BY
score DESC;
下一篇: 深度学习网络只能有一个输入吗?