MySQL各类查询语句DQL------聚合函数

创建表格

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `createDate` datetime DEFAULT NULL,
  `userName` varchar(20) DEFAULT NULL,
  `pwd` varchar(11) DEFAULT NULL,
  `phone` varchar(36) DEFAULT NULL,
  `age` int(5) DEFAULT NULL,
  `sex` char(2) DEFAULT NULL,
  `introduce` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

插入数据

INSERT INTO student VALUES(0,'2023-8-26 10:45:7','纳西妲','521','19868866036','500','神','智慧之神');
INSERT INTO student VALUES(0,'2023-8-23 12:56:45','胡桃','521','19868866036','45','神','往生堂堂主');
INSERT INTO student VALUES(0,'2023-8-23 12:56:45','钟离','521','19868866036','1000','神','岩王帝君');
INSERT INTO student VALUES(0,'2023-8-23 12:56:45','芙宁娜','521','19868866036','500','神','神的替身');
INSERT INTO student VALUES(0,'2023-8-23 12:56:45','可莉','521','19868866036','120','萝','火花骑士');
INSERT INTO student VALUES(0,'2023-8-23 12:56:45','雷电将军','521','19868866036','800','神','永恒');
INSERT INTO student VALUES(0,'2023-8-23 12:56:45','班尼特','521','19868866036','54','少','六命火神');

聚合函数语句

#聚合函数5个。数量count（x），最大值max（x），最小值min（x），平均数avg（x），求和sum（x）
SELECT count(*)'所有神的数量' FROM student WHERE age>=500;
SELECT max(age) '最大年龄',min(age) '最小年龄' FROM student ;
SELECT avg(age) '平均年龄' FROM student ;
SELECT sum(age) '总年龄' FROM student ;
SELECT avg(age) 'avg出的总年龄',sum(age)/(SELECT count(*) FROM student) '计算出的' FROM student ;

实际情况