1、mysql DQL 操作2 函数综合运用 HVAING 、 GROUP BY + GROUP_CONCAT() 、SUM() 、ORDER BY 、 COUNT()
书写顺序:SELECT - FORM - WHERE - GROUP BY - HAVING - ORDER BY - LIMIT
[root@test ~]# mysql -u root -p000000 -e "use mysql_test;select * from test1;"
+----+--------+--------+--------+--------+--------+-----------+
| id | name | 性别 | 绩效 | 工资 | 奖金 | 部门 |
+----+--------+--------+--------+--------+--------+-----------+
| 1 | 张三 | 男 | 1000 | 4000 | 1000 | 技术部 |
| 2 | 李四 | 男 | 1500 | 4500 | 500 | 财务部 |
| 3 | 王五 | 女 | 500 | 2000 | 500 | 人事部 |
| 4 | 赵六 | 女 | 2000 | 8000 | 1000 | 技术部 |
| 5 | 钱七 | 女 | 1780 | 7500 | 700 | 技术部 |
| 6 | aa | 男 | 156 | 5656 | NULL | 技术部 |
| 7 | 周九 | 男 | 789 | 3500 | NULL | 财务部 |
| 8 | 吴十 | 女 | 5000 | 30000 | 1500 | 人事部 |
| 9 | 郑一 | 女 | 135 | 1500 | NULL | 人事部 |
+----+--------+--------+--------+--------+--------+-----------+
这是我表中所有的数据
1、将部门分类,使用别名 “a” 作为列,列出所有部门的工资;使用别名 “b” 作为列名 ,列出各部门工资的总和
[root@test ~]# mysql -u root -p000000 -e "use mysql_test;SELECT 部门,GROUP_CONCAT( 工资) AS a,SUM(工资) AS b FROM test1 GROUP BY 部门;"
+-----------+-----------------------------+-------+
| 部门 | a | b |
+-----------+------------------------------+-------+
| 人事部 | 2000,30000,1500 | 33500 |
| 技术部 | 4000,8000,7500,5656 | 25156 |
| 财务部 | 4500,3500 | 8000 |
+-----------+---------------------+------------------+
2、将部门分类,查询所有部门中工资数大于等于1500的数据,分别列出各部门的人员和工资,并count()函数计数工资;
[root@test ~]# mysql -u root -p000000 -e "use mysql_test;SELECT 部门,GROUP_CONCAT( 工资),GROUP_CONCAT(name),COUNT(工资) FROM test1 WHERE 工资 >= 1500 GROUP BY 部门;"
+-----------+-----------------------------------+----------------------------------------+--------------------------+
| 部门 | GROUP_CONCAT(工资) | GROUP_CONCAT(name) | COUNT(工资) |
+-----------+-----------------------------------+----------------------------------------+--------------------------+
| 人事部 | 2000,30000,1500 | 王五,吴十,郑一 | 3 |
| 技术部 | 4000,8000,7500,5656 | 张三,赵六,钱七,aa | 4 |
| 财务部 | 4500,3500 | 李四,周九 | 2 |
+-----------+-----------------------------------+---------------------------------------+----------------------------+
3、将部门分类,使用SUM()函数,计数各部门工资总和,并筛选出工资总和大于9000的数据;
[root@test ~]# mysql -u root -p000000 -e "use mysql_test;SELECT 部门,GROUP_CONCAT( 工资),SUM(工资) FROM test1 GROUP BY 部门 HAVING (SUM(工资) >= 9000);"
+-----------+-----------------------------------+-------------+
| 部门 | GROUP_CONCAT(工资) | SUM(工资) |
+-----------+-----------------------------------+-------------+
| 人事部 | 2000,30000,1500 | 33500 |
| 技术部 | 4000,8000,7500,5656 | 25156 |
+-----------+-----------------------------------+-------------+
4、将部门分类,筛选工资大等于2000 的数据,使用SUM()函数,计数各部门工资总和,并筛选出 工资总和 大于9000的数据;
[root@test ~]# mysql -u root -p000000 -e "use mysql_test;SELECT 部门,GROUP_CONCAT( 工资),SUM(工资) FROM test1 WHERE 工资 >= 2000 GROUP BY 部门 HAVING (SUM(工资) >= 8000);"
+-----------+-----------------------------------+-------------+
| 部门 | GROUP_CONCAT(工资) | SUM(工资) |
+-----------+-----------------------------------+-------------+
| 人事部 | 2000,30000 | 32000 |
| 技术部 | 4000,8000,7500,5656 | 25156 |
| 财务部 | 4500,3500 | 8000 |
+-----------+----------------------+----------------------------+
5、mysql -u root -p000000 -e "use mysql_test;SELECT 部门,GROUP_CONCAT( 工资),SUM(工资) FROM test1 where 工资 >= 2000 GROUP BY 部门 HAVING (SUM(工资) >= 8000) ORDER BY SUM(工资) ASC;"
+-----------+-----------------------------------+-------------+
| 部门 | GROUP_CONCAT( 工资) | SUM(工资) |
+-----------+------------------------------------+-------------+
| 财务部 | 4500,3500 | 8000 |
| 技术部 | 4000,8000,7500,5656 | 25156 |
| 人事部 | 2000,30000 | 32000 |
+-----------+-----------------------+---------------------------+
6、LIMIT
[root@xiandian ~]# mysql -u root -p000000 -e "use mysql_test;SELECT 部门,GROUP_CONCAT( 工资),SUM(工资) FROM test1 where 工资 >= 2000 GROUP BY 部门 HAVING (SUM(工资) >= 8000) ORDER BY SUM(工资) ASC;"
+-----------+-----------------------------------+-------------+
| 部门 | GROUP_CONCAT( 工资) | SUM(工资) |
+-----------+-----------------------------------+-------------+
| 财务部 | 4500,3500 | 8000 |
| 技术部 | 4000,8000,7500,5656 | 25156 |
| 人事部 | 2000,30000 | 32000 |
+-----------+-----------------------+---------------------------+
[root@xiandian ~]# mysql -u root -p000000 -e "use mysql_test;SELECT 部门,GROUP_CONCAT( 工资),SUM(工资) FROM test1 where 工资 >= 2000 GROUP BY 部门 HAVING (SUM(工资) >= 8000) ORDER BY SUM(工资) ASC LIMIT 0,1;"
+-----------+------------------------------------+-------------+
| 部门 | GROUP_CONCAT( 工资) | SUM(工资) |
+-----------+-----------------------------------+-------------+
| 财务部 | 4500,3500 | 8000 |
+-----------+-----------------------------------+-------------+
[root@xiandian ~]# mysql -u root -p000000 -e "use mysql_test;SELECT 部门,GROUP_CONCAT( 工资),SUM(工资) FROM test1 where 工资 >= 2000 GROUP BY 部门 HAVING (SUM(工资) >= 8000) ORDER BY SUM(工资) ASC LIMIT 2,1;"
+-----------+------------------------------------+-------------+
| 部门 | GROUP_CONCAT( 工资) | SUM(工资) |
+-----------+------------------------------------+-------------+
| 人事部 | 2000,30000 | 32000 |
+-----------+------------------------------------+-------------+