数据库 mysql数据库 group by详解

川长思鸟来 2023-01-11 03:37 186阅读 0赞

### 数据库 mysql数据库 group by having详解 ###

*  数据库 mysql数据库 group by having详解
 *   *  一、概念
     *  二、语法结构
     *  三、验证案例

# 数据库 mysql数据库 group by having详解 #

## 一、概念 ##

group by 字段分组，以哪个字段分组就是以哪个字段作为唯一值进行分组，多个字段同样道理。
    having相当于是分组后的条件语句。

## 二、语法结构 ##

select   字段    from   表名   where    条件     group   by       字段
    select   字段    from   表名   group  by    字段    having    过滤条件
    注意：对于过滤条件，可以先用where，再用group  by或者是先用group  by，再用having

## 三、验证案例 ##

-- 创建员工表
    CREATE TABLE `tab_employee` (
      `id`     BIGINT(20) NOT NULL AUTO_INCREMENT,
      `num`    VARCHAR(2) DEFAULT NULL COMMENT '统计',
      `name`   VARCHAR(255) DEFAULT NULL COMMENT '姓名',
      `grade`  VARCHAR(30) DEFAULT NULL COMMENT '级别',
      `salary` VARCHAR(30) DEFAULT NULL COMMENT '薪资',
      PRIMARY KEY (`id`)
    ) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT '员工信息表';
    -- 查询表
    SELECT * FROM tab_employee;
    -- 删除表
    DROP TABLE tab_employee;
    -- 删除数据
    DELETE FROM tab_employee; 
    
    INSERT INTO tab_employee(num,NAME,grade,salary) VALUES('1','张三','A',1500);
    INSERT INTO tab_employee(num,NAME,grade,salary) VALUES('1','张三','A',2000);
    INSERT INTO tab_employee(num,NAME,grade,salary) VALUES('1','张三','B',1500);
    INSERT INTO tab_employee(num,NAME,grade,salary) VALUES('1','李四','A',1500);
    INSERT INTO tab_employee(num,NAME,grade,salary) VALUES('1','李四','B',2000);
    INSERT INTO tab_employee(num,NAME,grade,salary) VALUES('1','王五','A',1500);
    INSERT INTO tab_employee(num,NAME,grade,salary) VALUES('1','赵六','C',1500);
    INSERT INTO tab_employee(num,NAME,grade,salary) VALUES('1','田七','D',1500);
    
    -- 查询表
    SELECT * FROM tab_employee;
    
    -- AABA BACD
    SELECT grade FROM tab_employee;
    
    -- 按照姓名分组
    SELECT COUNT(num),NAME FROM tab_employee GROUP BY NAME;
    -- 按照级别分组
    SELECT COUNT(num),grade FROM tab_employee GROUP BY grade;
    -- 按照姓名与级别分组，把NAME,grade这列看做一个整体
    SELECT COUNT(num),NAME,grade FROM tab_employee GROUP BY NAME,grade; 
    -- 按照级别与姓名分组，把grade,NAME这列看做一个整体,与上边区别是分组字段顺序颠倒
    SELECT COUNT(num),NAME,grade FROM tab_employee GROUP BY grade,NAME; 
    
    -- 配合聚合函数一起使用
    -- 常用的聚合函数：count() , sum() , avg() , max() , min()
    
    -- count()：计数，查看表中相同人名的个数
    SELECT COUNT(num),NAME FROM tab_employee GROUP BY NAME;           
    
    -- sum()：求和, 查看表中人员的工资和（同姓的工资相加）
    SELECT SUM(salary),NAME FROM tab_employee GROUP BY NAME;  
    
    -- avg()：平均数, 查看表中人员的工资平均数（同姓工资平均数）
    SELECT NAME,AVG(salary) FROM tab_employee GROUP BY NAME,grade;
    -- 姓名与级别确定一个人，所以会有两个张三都是1500
    SELECT NAME,grade,AVG(salary) FROM tab_employee GROUP BY NAME,grade;
    
    -- max()：最大值, 查看按等级划分人员工资最大值
    SELECT grade, MAX(salary) FROM tab_employee GROUP BY grade;
    SELECT NAME,grade, MAX(salary) FROM tab_employee GROUP BY grade,NAME;
    
    -- min()：最小值, 查看按等级划分人员工资最小值
    SELECT NAME,grade, MIN(salary) FROM tab_employee GROUP BY grade, NAME;
    
    -- having相当于条件
    SELECT COUNT(num),NAME FROM tab_employee GROUP BY NAME;  
    SELECT COUNT(num),NAME FROM tab_employee GROUP BY NAME HAVING COUNT(num) > 1;