group by 语句

 1 -- 一、group by 分组统计
 2 -- select语句中的 group by 子句对列进行分组
 3 --     格式: select  列名1,列名2,列名3....from   表名   group  by 列名
 4 
 5 -- Having 子句用于限制分组显示结果
 6 
 7 -- 创建部门表数据
 8     CREATE  TABLE dept(number INT UNSIGNED NOT NULL DEFAULT 0,
 9                         dept_name VARCHAR(20) NOT NULL DEFAULT  " ",
10                         address VARCHAR(25) NOT NULL DEFAULT  " ",
11                         salary FLOAT  NOT NULL DEFAULT 0 );
12                         
13 --     drop table dept;
14 -- 添加数据
15 INSERT INTO dept (number,dept_name,address,salary)VALUES(01,'总部', '云南',15000),(02,'研发部' ,'文山',5600),
16                                                     (03,'市场部','红河',3000),(04,'财务部','昆明',2500);
17 SELECT *FROM dept;
18 
19 
20 
21 
22 -- 创建员工表
23 CREATE TABLE emp(empo_number INT,                                                         /*员工编号*/
24                     enname VARCHAR(10)  NOT NULL DEFAULT ' ',   /*员工姓名*/
25                     job VARCHAR(10) NOT NULL DEFAULT ' ' ,            /*工作*/
26                     mgr  MEDIUMINT UNSIGNED ,            /*上级编号*/
27                     job_time DATE NOT NULL ,                    /*入职时间*/
28                     salary DECIMAL(7,2) NOT NULL,        /*工资*/
29                     bonus DECIMAL(5,2) NOT NULL,            /*奖金*/
30                     dept_number INT UNSIGNED NOT NULL DEFAULT 0);        /*部门编号*/
31                     
32 ALTER  TABLE emp  MODIFY bonus DECIMAL(7,2);
33 
34  INSERT INTO emp VALUES(100,'tom','程序开发员',20,'2000-1-1',10960.00,6900.00,3);
35  INSERT INTO emp VALUES(101,'jack','科员',30,'2001-11-1',4960.00,3200.00,1);
36  INSERT INTO emp VALUES(102,'mark','专职教师',40,'2002-11-1',5960.00,3100.00,2);
37  INSERT INTO emp VALUES(103,'smith','经理',50,'1996-10-1',7960.00,3200.00,3);
38  INSERT INTO emp VALUES(104,'june','销售员',60,'2020-11-6',4960.00,3000.00,4);
39  INSERT INTO emp VALUES(105,'xiaoming','裁判',70,'2021-11-1',3560.00,3200.00,1);
40  INSERT INTO emp VALUES(106,'xioahuang','运动员',80,'2010-9-1',2960.00,3200.00,2);
41  INSERT INTO emp VALUES(107,'xiaonong','保安',90,'2019-11-1',3960.00,300.00,4);
42  INSERT INTO emp VALUES(108,'xiaolu','秘书',00,'2020-11-1',5960.00,3000.00,2);                    
43                     
44 SELECT  DISTINCT*FROM emp;
45 
46 DELETE  FROM emp WHERE  job_time ='2020-11-1';
47 
48 
49 -- 工资级别表
50 CREATE TABLE  sal_grade( grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
51                         lowsal  DECIMAL(17,2) NOT NULL,
52                         heisal DECIMAL(17,2) NOT NULL);
53                         
54 INSERT INTO sal_grade VALUES(1,2000,3500);
55 INSERT INTO sal_grade VALUES(2,3500,4500);
56 INSERT INTO sal_grade VALUES(3,4500,6000);
57 INSERT INTO sal_grade VALUES(4,6000,7500);
58 INSERT INTO sal_grade VALUES(5,7500,9000);
59 
60 SELECT *FROM  sal_grade;
61    
62 --    查询每个部门的每种岗位的平均工资和最高工资、最低工资
63 
64 SELECT AVG(salary), MIN(salary),dept_number,job 
65                             FROM emp  GROUP BY dept_number,job;
66                             
67                             
68 -- 显示平均工资低于5000的部门号和它的平均工资
69 
70 分析：1.显示每个部门的平均工资和部门号
71          2.在1的基础上进行过滤，平均工资<2500
72      SELECT AVG(salary),dept_number FROM emp GROUP BY dept_number;
73 
74 -- Having 子句用于限制分组显示结果
75         
76      SELECT AVG(salary),dept_number FROM emp
77                                     GROUP BY dept_number
78                                             HAVING AVG(salary)<2500;
79 -- 使用别名
80  SELECT AVG(salary) AS ' 平均工资',dept_number FROM emp
81                                     GROUP BY dept_number
82                                             HAVING ' 平均工资' <6000;