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

1.MySQL数据库创建（学生表）

CREATE TABLE `class` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `createdate` datetime DEFAULT NULL,
  `username` varchar(255) DEFAULT NULL,
  `pwd` varchar(255) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `sex` varchar(255) DEFAULT NULL,
  `introduce` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据

INSERT into student VALUES(0,'2024-02-25','香菱','123','15614333260',18,'女','万民堂大厨');
INSERT into student VALUES(0,'2024-02-25','凝光','123','15614333260',26,'女','璃月七星天权星');
INSERT into student VALUES(0,'2024-02-25','刻晴','123','15614333260',23,'女','璃月七星玉衡星');
INSERT into student VALUES(0,'2024-02-25','北斗','123','15614333260',26,'女','大船长');
INSERT into student VALUES(0,'2024-02-25','胡桃','123','15614333260',18,'女','往生堂第77代堂主');
SELECT * FROM student;

查询语句

# 聚合函数5个，数量count(x)，最大值max(x),最小值min(x),平均数avg(x),求和sum(x)
SELECT count(*)'所有成年人数量'from class WHERE age>=18;
SELECT MAX(age)'最大年龄'，min(age)'最小年龄'from class;
SELECT AVG(age)'平均年龄'FROM class;
SELECT sum(age)'总年龄'from class;
SELECT AVG(age)'avg出的总年龄'，SUM(age)/(SELECT count(*)from class)'计算出的'from class;