《学习》4集合函数与分组

-- 聚集函数 配合分组语句 group by
-- 显示最高分
SELECT MAX(sscore) FROM db.`student`;
-- 显示最高分学生的信息min max
SELECT * FROM db.`student` WHERE sscore IN(SELECT MAX(sscore) FROM db.`student`);

-- 统计人数 （不是信息）
SELECT COUNT(*) FROM db.`student`;
SELECT COUNT(*) FROM db.`student` WHERE sscore<60;;
SELECT AVG(sscore) 平均分,MAX(sscore) 最高分,MIN(sscore) 最低分 FROM student;
SELECT COUNT(*) FROM student WHERE sscore IS NULL;
SELECT COUNT(*) FROM student WHERE saddress IS NULL;

UPDATE student SET sscore=NULL WHERE sscore=0;

SELECT SUM(sage)FROM student;
SELECT AVG(sage)FROM student;

SELECT * FROM student;

 

-- 统计各地区的人数
SELECT COUNT(*) FROM student WHERE saddress='郑州';

SELECT sscore 分数,COUNT(*) 人数
FROM student
WHERE saddress IS NOT NULL -- 查询条件
GROUP BY sscore -- 分组字段
HAVING COUNT(*)>4 -- 分组条件（符合条件才会分组）
ORDER BY COUNT(*) DESC;-- desc降序排列

SELECT saddress,COUNT(*),MAX(sscore) FROM student GROUP BY saddress;

-- 并集
-- 求出郑州最高分学生信息
SELECT * FROM student
WHERE saddress='郑州'
AND sscore IN(SELECT MAX(sscore) FROM student WHERE saddress='郑州');

-- 上海最高分学生信息
SELECT * FROM student
WHERE saddress='上海'
AND sscore IN(SELECT MAX(sscore) FROM student WHERE saddress='上海');
------------------------------------

SELECT * FROM student
WHERE saddress='郑州'
AND sscore IN(SELECT MAX(sscore) FROM student WHERE saddress='郑州')
UNION
SELECT * FROM student
WHERE saddress='武汉'
AND sscore IN(SELECT MAX(sscore) FROM student WHERE saddress='武汉');