MySQL 中数据的检索（三：数据分组）

刺骨的言语ヽ痛彻心扉 2022-05-29 08:50 268阅读 0赞

一、首先创建数据库 zhrq95，新建表格并添加数据进去：  
create database zhrq95;  
use zhrq95;

CREATE TABLE T\_Employee (FNumber VARCHAR(20),FName VARCHAR(20),FAge  
INT,FSalary DECIMAL(10,2),PRIMARY KEY (FNumber));

INSERT INTO T\_Employee(FNumber,FName,FAge,FSalary)  
VALUES(‘DEV001′,’Tom’,25,8300);  
INSERT INTO T\_Employee(FNumber,FName,FAge,FSalary)  
VALUES(‘DEV002′,’Jerry’,28,2300.80);  
INSERT INTO T\_Employee(FNumber,FName,FAge,FSalary)  
VALUES(‘SALES001′,’John’,23,5000);  
INSERT INTO T\_Employee(FNumber,FName,FAge,FSalary)  
VALUES(‘SALES002′,’Kerry’,28,6200);  
INSERT INTO T\_Employee(FNumber,FName,FAge,FSalary)  
VALUES(‘SALES003′,’Stone’,22,1200);  
INSERT INTO T\_Employee(FNumber,FName,FAge,FSalary)  
VALUES(‘HR001′,’Jane’,23,2200.88);  
INSERT INTO T\_Employee(FNumber,FName,FAge,FSalary)  
VALUES(‘HR002′,’Tina’,25,5200.36);  
INSERT INTO T\_Employee(FNumber,FName,FAge,FSalary)  
VALUES(‘IT001′,’Smith’,28,3900);

二、为 T\_Employee 表增加两列,分别为表示其所属分公司的 FSubCompany 字段和表示其所属部门的 FDepartment：  
ALTER TABLE T\_Employee ADD FSubCompany VARCHAR(20);  
ALTER TABLE T\_Employee ADD FDepartment VARCHAR(20);  
UPDATE T\_Employee SET FSubCompany=’Beijing’,FDepartment=’Development’ WHERE FNumber=’DEV001′;  
UPDATE T\_Employee SET FSubCompany=’ShenZhen’,FDepartment=’Development’ WHERE FNumber=’DEV002′;  
UPDATE T\_Employee SET FSubCompany=’Beijing’,FDepartment=’HumanResource’ WHERE FNumber=’HR001′;  
UPDATE T\_Employee SET FSubCompany=’Beijing’,FDepartment=’HumanResource’ WHERE FNumber=’HR002′;  
UPDATE T\_Employee SET FSubCompany=’Beijing’,FDepartment=’InfoTech’ WHERE FNumber=’IT001′;  
UPDATE T\_Employee SET FSubCompany=’ShenZhen’,FDepartment=’InfoTech’ WHERE FNumber=’IT002′;  
UPDATE T\_Employee SET FSubCompany=’Beijing’,FDepartment=’Sales’ WHERE FNumber=’SALES001′;  
UPDATE T\_Employee SET FSubCompany=’Beijing’,FDepartment=’Sales’ WHERE FNumber=’SALES002′;  
UPDATE T\_Employee SET FSubCompany=’ShenZhen’,FDepartment=’Sales’ WHERE FNumber=’SALES003′;

三、查看公司员工有哪些年龄段：  
SELECT FAge FROM T\_Employee  
GROUP BY FAge

GROUP BY 子句必须放到 SELECT 语句的之后,如果 SELECT 语句有 WHERE 子句,则 GROUP BY 子句必须放到 WHERE 语句的之后：  
SELECT FAge FROM T\_Employee  
WHERE FSubCompany = ‘Beijing’  
GROUP BY FAge

需要分组的所有列都必须位于 GROUP BY 子句的列名列表中，没有出现在 GROUP BY 子句中的列(聚合函数除外)是不能放到SELECT 语句后的列名列表中的。因为采用分组以后的查询结果集是以分组形式提供的,由于每组中人员的员工工资都不一样,所以就不存在能够统一代表本组工资水平的 FSalary 字段，不过每组中员工的平均工资却是能够代表本组统一工资水平的,所以可以对 FSalary 使用聚合函数：  
SELECT FAge,AVG(FSalary) FROM T\_Employee  
GROUP BY FAge

GROUP BY 子句中可以指定多个列,只需要将多个列的列名用逗号隔开即可。指定多个分组规则以后, 数据库系统将按照定义的分组顺序来对数据进行逐层分组,首先按照第一个分组列进行分组,然后在每个小组内按照第二个分组列进行再次分组、逐层分组,从而实现“组中组”的效果,而查询的结果集是以最末一级分组来进行输出的。比如下面的SQL语句将会列出所有分公司的所有部门情况:  
SELECT FSubCompany,FDepartment FROM T\_Employee  
GROUP BY FSubCompany,FDepartment

四、数据分组与聚合函数:  
SELECT FAge,COUNT(\*) AS CountOfThisAge FROM T\_Employee  
GROUP BY FAge \#查看每个年龄段的员工的人数

SELECT FSubCompany,FAge,COUNT(\*) AS CountOfThisSubCompAge FROM T\_Employee  
GROUP BY FSubCompany,FAge  
ORDER BY FSubCompany \#统计每个分公司的年龄段的人数，并按照FSubCompany字段进行排序

SELECT FSubCompany,SUM(FSalary) AS FSalarySUM FROM T\_Employee  
GROUP BY FSubCompany \#统计每个公司中的工资的总值

SELECT FDepartment,AVG(FSalary) AS FSalarySUM FROM T\_Employee  
GROUP BY FDepartment \#统计每个垂直部门中的工资的平均值

SELECT FDepartment,MIN(FAge) AS FAgeMIN,MAX(FAge) AS FAgeMAX FROM T\_Employee  
GROUP BY FDepartment \#统计每个垂直部门中员工年龄的最大值和最小值

五、having 语句  
聚合函数不能在 WHERE 语句中使用，必须使用 HAVING 子句来代替

SELECT FAge,COUNT(\*) AS CountOfThisAge FROM T\_Employee  
GROUP BY FAge  
HAVING COUNT(\*)>1 \#对部分分组进行过滤，比如只检索人数多余1个的年龄段

HAVING 语句中使用的语法和 WHERE 几乎是一样的，也可以像 WHERE 语句一样使用复杂的过滤条件  
SELECT FAge,COUNT(\*) AS CountOfThisAge FROM T\_Employee  
GROUP BY FAge  
HAVING COUNT(\*) =1 OR COUNT(\*) =3 \#检索人数为1个或者3个的年龄段  
等同于：  
SELECT FAge,COUNT(\*) AS CountOfThisAge FROM T\_Employee  
GROUP BY FAge  
HAVING COUNT(\*) IN (1,3) \#检索人数为1个或者3个的年龄段

注：使用 WHERE 的时候 GROUP BY 子句要位于 WHERE 子句之后，而使用 HAVING 子句的时候 GROUP BY 子句要位于 HAVING 子句之后  
SELECT FAge,COUNT(\*) AS CountOfThisAge FROM T\_Employee  
GROUP BY FAge  
HAVING COUNT(\*) IN (1,3)

注：在 HAVING 语句中不能包含未分组的列名，比如下面的SQL语句是错误的：  
SELECT FAge,COUNT(\*) AS CountOfThisAge FROM T\_Employee  
GROUP BY FAge  
HAVING FName IS NOT NULL  
执行的时候数据库系统会提示类似如下的错误信息：  
HAVING 子句中的列’T\_Employee.FName’ 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。  
需要用WHERE语句来代替HAVING，修改后的SQL语句如下：  
SELECT FAge,COUNT(\*) AS CountOfThisAge FROM T\_Employee  
WHERE FName IS NOT NULL  
GROUP BY FAge