分组统计group by

1. 使用group by子句对列进行分组
   SELECT column1,column2,column3… FROM table GROUP BY column
2. 使用having子句对分组后的结果进行过滤
   SELECT column1，column2，column3… FROM table GROUP BY column HAVING 过滤条件
3. GROUP BY 用于对查询的结果分组统计，HAVING 子句用于限制分组显示结果

USE db_02;
#创建部门表
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,'OPERATIONS','BOSTON')
SELECT * FROM dept;

#创建emp员工表
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,/*薪水*/