【MySQL】插入查询结果，聚合函数

6.5 插入查询结果

语法：

INSERT INTO table_name [(column [, column ...])] SELECT ... 

案例：删除表中的的重复记录，重复的数据只能有一份-- 创建原数据表

CREATE TABLE duplicate_table (id int, name varchar(20)); 
Query OK, 0 rows affected (0.01 sec) -- 插入测试数据 

INSERT INTO duplicate_table VALUES 
(100, 'aaa'), 
(100, 'aaa'), 
(200, 'bbb'), 
(200, 'bbb'), 
(200, 'bbb'), 
(300, 'ccc'); 
Query OK, 6 rows affected (0.00 sec) 
Records: 6  Duplicates: 0  Warnings: 0 

思路：-- 创建一张空表 no_duplicate_table，结构和 duplicate_table 一样 
CREATE TABLE no_duplicate_table LIKE duplicate_table; 
Query OK, 0 rows affected (0.00 sec) 

-- 将 duplicate_table 的去重数据插入到 no_duplicate_table 
INSERT INTO no_duplicate_table SELECT DISTINCT * FROM duplicate_table; 
Query OK, 3 rows affected (0.00 sec) 

Records: 3  Duplicates: 0  Warnings: 0 -- 通过重命名表，实现原子的去重操作 
RENAME TABLE duplicate_table TO old_duplicate_table,  
no_duplicate_table TO duplicate_table; 
Query OK, 0 rows affected (0.00 sec)

SELECT * FROM duplicate_table;  -- 查看最终结果 
+------+------+ 
| id   | name | 
+------+------+ 
|  100 | aaa  | 
|  200 | bbb  | 
|  300 | ccc  | 
+------+------+ 
3 rows in set (0.00 sec) 

6.6 聚合函数

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的 数量
SUM([DISTINCT] expr)	返回查询到的数据的 总和，不是数字没有意义
AVG([DISTINCT] expr)	返回查询到的数据的 平均值，不是数字没有意义
MAX([DISTINCT] expr)	返回查询到的数据的 最大值，不是数字没有意义
MIN([DISTINCT] expr)	返回查询到的数据的 最小值，不是数字没有意义

案例：

6.6.1 统计班级共有多少同学 – 使用 * 做统计，不受 NULL 影响

SELECT COUNT(*) FROM students; 
+----------+ 
| COUNT(*) | 
+----------+ 
|        4 | 
+----------+
1 row in set (0.00 sec) 

SELECT COUNT(1) FROM students; -- 使用表达式做统计 
+----------+
| COUNT(1) | 
+----------+ 
|        4 | 
+----------+ 
1 row in set (0.00 sec) 

6.6.2 统计班级收集的 qq 号有多少

-- NULL 不会计入结果 
SELECT COUNT(qq) FROM students; 
+-----------+ 
| COUNT(qq) | 
+-----------+ 
|         1 | 
+-----------+ 
1 row in set (0.00 sec)

6.6.3 统计本次考试的数学成绩分数个数

-- COUNT(math) 统计的是全部成绩 
SELECT COUNT(math) FROM exam_result; 
+---------------+ 
| COUNT(math) | 
+---------------+ 
|             6 | 
+---------------+ 
1 row in set (0.00 sec) -- COUNT(DISTINCT math) 统计的是去重成绩数量 

SELECT COUNT(DISTINCT math) FROM exam_result; 
+------------------------+ 
| COUNT(DISTINCT math) | 
+------------------------+ 
|                      5 | 
+------------------------+ 
1 row in set (0.00 sec) 

6.6.4 统计数学成绩总分

SELECT SUM(math) FROM exam_result; 
+-------------+ 
| SUM(math) | 
+-------------+ 
|         569 | 
+-------------+ 
1 row in set (0.00 sec) -- 不及格 < 60 的总分，没有结果，返回 NULL 

SELECT SUM(math) FROM exam_result WHERE math < 60; 
+-------------+ 
| SUM(math) | 
+-------------+ 
|        NULL | 
+-------------+ 
1 row in set (0.00 sec) 

6.6.4 统计平均总分

SELECT AVG(chinese + math + english) 平均总分 FROM exam_result; 
+--------------+ 
| 平均总分     | 
+--------------+ 
|        297.5 | 
+--------------+

6.6.5 返回英语最高分

SELECT MAX(english) FROM exam_result; 
+-------------+ 
| MAX(english) | 
+-------------+ 
|          90 | 
+-------------+ 
1 row in set (0.00 sec) 

6.6.6 返回 > 70 分以上的数学最低分

SELECT MIN(math) FROM exam_result WHERE math > 70; 
+-------------+ 
| MIN(math) | 
+-------------+ 
|          73 | 
+-------------+ 
1 row in set (0.00 sec)

6.7 group by子句的使用

在select中使用group by 子句可以对指定列进行分组查询

select column1, column2, .. from table group by column; 

案例：

• 准备工作，创建一个雇员信息表（来自oracle 9i的经典测试表）
  • EMP员工表
  • DEPT部门表
  • SALGRADE工资等级表
• 如何显示每个部门的平均工资和最高工资

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

• 显示每个部门的每种岗位的平均工资和最低工资

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

• 显示平均工资低于2000的部门和它的平均工资

统计各个部门的平均工资

select avg(sal) from EMP group by deptno

having和group by配合使用，对group by结果进行过滤

select avg(sal) as myavg from EMP group by deptno having myavg<2000; 
--having经常和group by搭配使用，作用是对分组进行筛选，作用有些像where