09-基础SQL-DQL(数据查询语言)-聚合函数(count、max、min、avg、sum)
DQL-介绍(常用)
DQL英文全称是Data Query Language(数据查询语言),数据查询语言用来查询数据库中表的记录
查询关键字:SELECT
DQL-语法
DQL-聚合函数
介绍:
将一列数据作为一个整体,进行纵向计算
常见的聚合函数:
语法:
SELECT 聚合函数(字段列表) FROM 表名;
案例:创建一个 emp 员工表,添加一些员工数据
CREATE TABLE emp( id int comment "编号", workno varchar(10) comment "工号", name varchar(10) comment "姓名", gender char(1) comment "性别", age tinyint unsigned comment "年龄", idcard char(18) comment "身份证号", workaddress varchar(50) comment "工作地址", entrydate date comment "入职时间" ) comment "员工表";
INSERT INTO emp (id,workno,name,gender,age,idcard,workaddress,entrydate) VALUES (1,"1","马铃薯","男","23","123456789000000000","江西","2023-12-31"), (2,"2","任先生","男","26","123456789000000001","河北","2023-12-31"), (3,"3","王校凯","男","23","123456789000000002","河北","2023-12-31"), (4,"4","张晓峰","男","24","123456789000000003","山西","2023-12-31")
1)统计该企业员工数量
SELECT count(*) FROM emp;
2)统计该企业员工的平均年龄
SELECT avg(age) FROM emp;
3)统计河北地区,企业员工的平均年龄
SELECT avg(age) FROM emp WHERE workaddress = "河北";
4)统计河北地区,企业员工的年龄之和
SELECT sum(age) FROM emp WHERE workaddress = "河北";