【MySQL】基本查询之插入查询结果、聚合函数、分组查询_查询插入

在上篇文章中我总结了基本的增删查改语法，本篇文章我来叙述一下基本查询的后半部分

一、插入查询结果

单个查询的语法上篇文章已经了解了，下面开始看看两个查询语句复合使用的场景
语法：

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

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

---- 创建带有重复数据的数据表
mysql> CREATE TABLE duplicate_table (id int, name varchar(20));
-- 插入测试数据
mysql> INSERT INTO duplicate_table VALUES
    -> (100, 'aaa'),
    -> (100, 'aaa'),
    -> (200, 'bbb'),
    -> (200, 'bbb'),
    -> (200, 'bbb'),
    -> (300, 'ccc');
--查询表中信息
mysql> select * from duplicate_table;
+------+------+
| id   | name |
+------+------+
|  100 | aaa  |
|  100 | aaa  |
|  200 | bbb  |
|  200 | bbb  |
|  200 | bbb  |
|  300 | ccc  |
+------+------+

--创建另一张表
mysql> create table no_duplicate_table like duplicate_table;
--原表去重后的数据插入到新表中
mysql> insert into no_duplicate_table select distinct *from duplicate_table;
--查询新表信息
mysql> select * from no_duplicate_table;
+------+------+
| id   | name |
+------+------+
|  100 | aaa  |
|  200 | bbb  |
|  300 | ccc  |
+------+------+
--修改表名，实现原子的去重
mysql> rename table duplicate_table to old_duplicate_table,no_duplicate_table TO duplicate_table;

mysql> select * from duplicate_table;
+------+------+
| id   | name |
+------+------+
|  100 | aaa  |
|  200 | bbb  |
|  300 | ccc  |
+------+------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

这里通过rename修改表名是为了等表的操作结束后，统一放入，更新，生效，节省时间

二、聚合函数

MySQL中的聚合函数常用于对数据进行计算和统计，以下是几种常见的聚合函数

统计班级共有多少同学

mysql> select * from stu;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |     134 |   98 |      56 |
|  3 | 猪悟能    |     176 |   98 |      90 |
|  4 | 曹孟德    |     140 |   90 |      67 |
|  5 | 刘玄德    |     110 |  115 |      45 |
|  6 | 孙权      |     140 |   73 |      78 |
|  7 | 宋公明    |     150 |   95 |      30 |
+----+-----------+---------+------+---------+

--统计行数
mysql> select count(1) from stu;
mysql> select count(*) from stu;
+----------+
| count(1) |
+----------+
|        6 |
+----------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

统计班级的数学成绩有多少个（去重）

mysql> select math from stu;
+------+
| math |
+------+
|   98 |
|   98 |
|   90 |
|  115 |
|   73 |
|   95 |
+------+

--正确的去重：先去重后聚合
mysql> select count(distinct math) from stu;
+----------------------+
| count(distinct math) |
+----------------------+
|                    5 |
+----------------------+

--错误的去重：先聚合后去重
mysql> select distinct  count( math) from stu;
+--------------+
| count( math) |
+--------------+
|            6 |
+--------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

mysql> select sum(math) from stu;
+-----------+
| sum(math) |
+-----------+
|       569 |
+-----------+
1
2
3
4
5
6

1.统计数学成绩总分
2.统计数学成绩的平均分
3.统计英语成绩不及格的人数
4.返回英语最高分
5.返回 > 70 分以上的数学最低分

--1
mysql> select sum(math) from stu;
+-----------+
| sum(math) |
+-----------+
|       569 |
+-----------+
1 row in set (0.00 sec)
-----2
mysql> select avg(math) from stu;
+-------------------+
| avg(math)         |
+-------------------+
| 94.83333333333333 |
+-------------------+

mysql> select sum(math)/count(*) from stu;
+--------------------+
| sum(math)/count(*) |
+--------------------+
|  94.83333333333333 |
+--------------------+
-----3
mysql> select count(*) from stu where english<60;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
--4
mysql> select max(english) from stu;
+--------------+
| max(english) |
+--------------+
|           90 |
+--------------+
--5
mysql> select min(math) from stu where math>70;
+-----------+
| min(math) |
+-----------+
|        73 |
+-----------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

三、分组查询----group by&having

分组的目的是为了进行分组之后，方便进行聚合统计

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

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

结合案列探讨
EMP员工表
DEPT部门表
SALGRADE工资等级表

---

---

---

显示每个部门的平均工资和最高工资
group by ‘列名’：分组是以同一列不同行数据来进行分组的；分组过后，每组内的【分组列名如deptno】，一定是一样的，可以被聚合压缩

mysql> select deptno,max(sal) 最高,avg(sal)平均 from emp group by deptno;
+--------+---------+-------------+
| deptno | 最高    | 平均        |
+--------+---------+-------------+
|     10 | 5000.00 | 2916.666667 |
|     20 | 3000.00 | 2175.000000 |
|     30 | 2850.00 | 1566.666667 |
+--------+---------+-------------+
1
2
3
4
5
6
7
8

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

mysql> select deptno,job,avg(sal)平均,min(sal) 最低  from emp group by deptno,job;
+--------+-----------+-------------+---------+
| deptno | job       | 平均        | 最低    |
+--------+-----------+-------------+---------+
|     10 | CLERK     | 1300.000000 | 1300.00 |
|     10 | MANAGER   | 2450.000000 | 2450.00 |
|     10 | PRESIDENT | 5000.000000 | 5000.00 |
|     20 | ANALYST   | 3000.000000 | 3000.00 |
|     20 | CLERK     |  950.000000 |  800.00 |
|     20 | MANAGER   | 2975.000000 | 2975.00 |
|     30 | CLERK     |  950.000000 |  950.00 |
|     30 | MANAGER   | 2850.000000 | 2850.00 |
|     30 | SALESMAN  | 1400.000000 | 1250.00 |
+--------+-----------+-------------+---------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14

注意事项：在group by之后出现的字段是可以在select 之后出现的，还有聚合函数，其余的不一定【select ename,deptno,job,avg(sal)平均,min(sal) 最低 from emp group by deptno,job;】就会报错，因为ename不是聚合条件

3.1 having与where区别

having一般搭配group by使用
显示平均工资低于2000的部门和它的平均工资

--这里先进行聚合压缩统计，having再开始条件筛选
mysql> select deptno,avg(sal) deptavg from emp group by deptno having deptavg<2000;
+--------+-------------+
| deptno | deptavg     |
+--------+-------------+
|     30 | 1566.666667 |
+--------+-------------+
1
2
3
4
5
6
7

除SMITH外，显示平均工资低于2000的每个部门的每种岗位的和它的平均工资

四、SQL查询的执行顺序

SQL查询中各个关键字的执行先后顺序 ：from > on> join > where > group by > with > having > select>distinct > order by > limit

五、OJ练习

1. 批量插入数据

insert into actor values(1,"PENELOPE","GUINESS","2006-02-15 12:34:33"),(2,"NICK","WAHLBERG","2006-02-15 12:34:33");
1

2. 找出所有员工当前薪水salary情况

select distinct salary from salaries order by salary desc;
1

3. 查找最晚入职员工的所有信息

select * from employees order by hire_date desc limit 1;
1

4. 查找入职员工时间排名倒数第三的员工所有信息

select * from employees
 where hire_date=(
    select distinct hire_date from employees order by  hire_date desc limit 2,1
    );
1
2
3
4

5. 查找薪水记录超过15条的员工号emp_no以及其对应的记录次数t
   分组+聚合函数

select emp_no,count(*) t from salaries group by emp_no having t>15;
1

6. 从titles表获取按照title进行分组
   

select title,count(title) t from titles group by title having t>=2;
1

7. 查找重复数据

select email from Person group by email having count(email)>1;
1

8. 查找大国

select name,population,area from World where area>=3000000 or population>=25000000;
1

9. 给定一个Employee表，要找出其中第N高的薪资（Salary）
   

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  SET N = N - 1;
  RETURN (
     
      select distinct(Salary) as getNthHighestSalary
      from Employee
      GROUP BY Salary 
      ORDER BY Salary DESC 
      limit 1 offset N
  );
END
1
2
3
4
5
6
7
8
9
10
11
12