mysql数据库-分组查询

目的

用于将结果分组便于统计

SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name; 

having

用于分组后条件过滤

example

show databases;

# group bu 用于查询结果分组统计


############### 部门表 ################3
create table dept
(
    deptno mediumint unsigned not null default 0,
    dname  varchar(20)        not null default "",
    loc    varchar(13)        not null default ""
);

insert into dept
values (10, 'ACCOUNTING', 'NEW YORK'),
       (20, 'RESEARCH', 'DALLAS'),
       (30, 'SALES', 'CHICAGO'),
       (40, 'OPERATTONS', 'BOSTON');


select *
from dept;

################ 雇员表 #####################
create table emp
(
    empno    mediumint unsigned not null default 0,
    ename    varchar(20)        not null default "",
    job      varchar(9)         not null default "",
    mgr      mediumint unsigned,
    hiredate date               not null,
    sal      decimal(7, 2)      not null,
    comm     decimal(7, 2),
    deptno   mediumint unsigned not null default 0
);

desc emp;
select *
from emp;

INSERT INTO emp
VALUES (7369, 'SMITH', 'CLERK', 7902, '1990-12-17', 800.00, NULL, 20),
       (7499, 'ALLEN', 'SALESMAN', 7698, '1990-12-17', 1600, 300, 30),
       (7521, 'WARD', 'SALESMAN', 7698, '1991-2-22', 1250, 500, 30),
       (7566, 'JONES', 'MANAGER', 7839, '1991-4-2', 2975, NULL, 20),
       (7654, 'MARTIN', 'SALESMAN', 7698, '1991-9-28', 1250, 1400, 30),
       (7698, 'BLAKE', 'MANAGER', 7839, '1991-5-1', 2850, NULL, 30),
       (7782, 'CLARK', 'MANAGER', 7839, '1991-6-9', 2450, NULL, 10),
       (7788, 'SCOTT', 'ANALYST', 7566, '1997-4-9', 3000, NULL, 20),
       (7839, 'KING', 'PRESIDENT', NULL, '1991-11-17', 5000, NULL, 10),
       (7844, 'TURNER', 'SALESMAN', 7698, '1991-9-8', 1500, NULL, 30),
       (7900, 'JAMES', 'CLERK', 7698, '1991-12-3', 950, NULL, 30),
       (7902, 'FORD', 'ANALYST', 7566, '1992-1-23', 3000, NULL, 60),
       (7934, 'MILLER', 'CLERK', 7782, '1990-12-17', 1300, NULL, 10);


select *
from emp;


############# 工资级别表 ##############


create table salgrade
(
    grade mediumint unsigned not null default 0,
    losal decimal(17, 2)     not null, # 该级别最低工资
    hisal decimal(17, 2)     not null  # 该级别最高工资
);



insert into salgrade
values (1, 700, 1200),
       (2, 1201, 1400),
       (3, 1401, 2000),
       (4, 2001, 3000),
       (5, 30001, 9999);



select *
from salgrade;



############### QUESTION ###########
# 1。 如何显示每个部门的平均工资和最高工资
# - avg(sal) max(sal)
# - 按照部门分组查询

select avg(sal), max(sal), deptno
from emp
group by deptno;

# 2。 显示每个部门的每种岗位平均工资和最低工资
# - 每个部门
# - 每种岗位
# - avg(sal) min(sal)


select avg(sal), min(sal), deptno, job
from emp
group by deptno, job;



# 3。 显示平均工资低于 2000 的部门号 和他的平均工资
# - 化繁为简
# - 各个 部门的平均工资和部门号
# - 低于2000 的

select avg(sal), deptno
from emp
group by deptno
having avg(sal) < 2000;


select avg(sal) as avg_sal,deptno
from emp
group by deptno
having avg_sal <2000;

################### 如何理解分组 ############
/*
｜ 学号  ｜ 姓名 ｜ 性别 ｜
｜ ---- ｜ ---- ｜ ----｜
｜ z001 ｜ 张三 ｜ 男   ｜
｜ z002 ｜ 李四 ｜ 女   ｜
｜ z003 ｜ 王五 ｜ 男   ｜
｜ z004 ｜ 赵六 ｜ 女   ｜

#################################

按照类型进行分组

｜ ---- ｜ ---- ｜
｜ 男   ｜ 2    ｜
｜ 女   ｜ 2    ｜


####### 分组后如果要过滤 则需要使用  having ############

 */