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;
想多了都是问题,做多了才是答案