sql查询语句之平均分、最高最低分及排序语句 avg max min

DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`u_id` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘编号’,
`object_no` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘课程编号’,
`students_no` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘学号’,
`score` int(11) NULL DEFAULT NULL COMMENT ‘分数’
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

INSERT INTO `score` VALUES (‘1’, ‘100’, ‘s100’, 80);
INSERT INTO `score` VALUES (‘2’, ‘100’, ‘s200’, 59);
INSERT INTO `score` VALUES (‘3’, ‘100’, ‘s300’, 79);
INSERT INTO `score` VALUES (‘4’, ‘200’, ‘s100’, 54);
INSERT INTO `score` VALUES (‘5’, ‘200’, ‘s200’, 96);
INSERT INTO `score` VALUES (‘6’, ‘200’, ‘s300’, 74);
INSERT INTO `score` VALUES (‘7’, ‘300’, ‘s100’, 65);
INSERT INTO `score` VALUES (‘8’, ‘300’, ‘s200’, 80);
INSERT INTO `score` VALUES (‘9’, ‘200’, ‘s400′, 62);
INSERT INTO `score` VALUES (’10’, ‘300’, ‘s400′, 56);
INSERT INTO `score` VALUES (’11’, ‘100’, ‘s400’, 70);
SET FOREIGN_KEY_CHECKS = 1;

DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
`students_no` varchar(12) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘编号’,
`students_name` varchar(12) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘姓名’,
`sex` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘性别’,
PRIMARY KEY (`students_no`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

INSERT INTO `students` VALUES (‘s100’, ‘张三’, ‘1’);
INSERT INTO `students` VALUES (‘s200’, ‘李四’, ‘2’);
INSERT INTO `students` VALUES (‘s300’, ‘张三’, ‘1’);
INSERT INTO `students` VALUES (‘s400’, ‘王五’, ‘1’);
SET FOREIGN_KEY_CHECKS = 1;

select object_no as ‘课程编号’, count(DISTINCT students_no) ‘考试人数’ from score group by object_no

select object_no as ‘课程编号’,max(score) as ‘最高分’,min(score) as ‘最低分’ from score group by object_no

select object_no as ‘课程编号’, count(DISTINCT students_no) as ‘学生编号’ from score group by object_no

select sex ‘性别’,count(DISTINCT students_no) ‘数量’ from students group by sex

select students_no as ‘学生编号’,avg(score) as ‘平均成绩’ from score group by students_no

select students_no as ‘学生编号’,avg(score) as ‘平均成绩’ from score group by students_no HAVING avg(score)>70

select students_no as ‘学生编号’,count(object_no) as ‘课程编号’ from score group by students_no

select students_no as ‘学生编号’,count(object_no) as ‘课程编号’ from score group by students_no HAVING count( object_no)>2

select students_name as ‘学生名称’,count(students_name) as ‘同名数量’ from students group by students_name HAVING count(students_name)>1

select object_no as ‘课程编号’,avg(score) as ‘平均成绩’ from score group by object_no

select object_no as ‘课程编号’,avg(score) as ‘平均成绩’ from score group by object_no HAVING avg(score)>70

select object_no as ‘课程编号’,students_no ‘学生编号’,score ‘分数’ from score where score<60 order by object_no desc

select object_no as ‘课程编号’,avg(score) as ‘平均成绩’ from score group by object_no order by avg(score) desc,object_no asc