MySQL——分组函数、distinct、分组查询、连接查询、子查询

博客目录

1、分组函数

2、distinct

3、分组查询(group by、having)

4、一个完整的DQL语句的总结

5、连接查询(跨表查询)（关联多个表查询）

6、子查询

一、分组函数

1、会自动忽略空值

2、分组函数不能直接使用在where关键字后面

取平均值：avg

mysql> select avg(sal) from emp;
+-------------+
| avg(sal)    |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.60 sec)

最大值：max
最小值：min

取记录数：count

 mysql> select count(sal) from emp;
+------------+
| count(sal) |
+------------+
|         14 |
+------------+
1 row in set (0.04 sec)

求和：sum

二、distinct(去除重复记录)(显示)

使用distinct需要注意

1、该关键字前面不能出现字段

2、该关键字只能出现在所有字段的最前面

mysql> select distinct job from emp;

+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
+-----------+
5 rows in set (0.09 sec)

例

统计公司一共有几个工作岗位

mysql> select count(distinct job) from emp;
+---------------------+
| count(distinct job) |
+---------------------+
|                   5 |
+---------------------+
1 row in set (0.00 sec)

三、分组查询(group by、having)

1、group by

通过哪个或者哪些字段进行分组

重点

若一条DQL语句中有group by子句，那么select关键字后面只能跟参与分组的字段和分组函数(比如ename这些是不可以的，不是分组字段)

例

1、找出每个工作岗位的最高薪水

分析：先按照工作岗位分组，再使用max求每组最高记录

    mysql> select job,max(sal) from emp group by job;
    +-----------+----------+
    | job       | max(sal) |
    +-----------+----------+
    | ANALYST   |  3000.00 |
    | CLERK     |  1300.00 |
    | MANAGER   |  2975.00 |
    | PRESIDENT |  5000.00 |
    | SALESMAN  |  1600.00 |
    +-----------+----------+
    5 rows in set (0.00 sec)

2、计算每个部门的平均薪水

mysql> select
    -> deptno,avg(sal) as avgsal
    -> from
    -> emp
    -> group by
    -> deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)

3、计算不同工作岗位的最高薪水

mysql> select
    -> deptno,job,avg(sal) as avgsal
    -> from
    -> emp
    -> group by
    -> deptno,job;
+--------+-----------+-------------+
| deptno | job       | avgsal      |
+--------+-----------+-------------+
|     10 | CLERK     | 1300.000000 |
|     10 | MANAGER   | 2450.000000 |
|     10 | PRESIDENT | 5000.000000 |
|     20 | ANALYST   | 3000.000000 |
|     20 | CLERK     |  950.000000 |
|     20 | MANAGER   | 2975.000000 |
|     30 | CLERK     |  950.000000 |
|     30 | MANAGER   | 2850.000000 |
|     30 | SALESMAN  | 1400.000000 |
+--------+-----------+-------------+
9 rows in set (0.04 sec)

2、having

having和where功能都是为了完成数据的过滤
where和having后面都是添加条件
where和group by之前完成过滤
having在group by之后完成过滤

例

找出每个工作岗位的最高薪水，除manager之外

mysql> select
    -> job,avg(sal)
    -> from
    -> emp
    -> group by
    -> job
    -> having
    -> avg(sal)>1500;
+-----------+-------------+
| job       | avg(sal)    |
+-----------+-------------+
| ANALYST   | 3000.000000 |
| MANAGER   | 2758.333333 |
| PRESIDENT | 5000.000000 |
+-----------+-------------+
3 rows in set (0.09 sec)

注意上面为什么不能用where，因为where关键字后面不能用分组函数，分组函数必须在分组完成后执行，而分组要group by。而group by在where后执行

原则

一般情况尽量在where中过滤，实在无法过滤的数据，通常是需要先分组之后再过滤，此时可以选择having，这牵扯到效率问题

四、一个完整的DQL语句的总结

select   
    ...
from    从某张表中检索数据
    ...
where   经过某条件进行过滤
    ...
group by    分组
    ...
having      分组之后不满意再过滤
    ...
order by    排序输出
    ...

以上关键字顺序不能改变，严格遵守

以下是执行顺序

1、from
2、where
3、group by
4、having
5、select
6、order by

五、连接查询(跨表查询)(关联多个表查询)

1、连接查询分类(年代)

SQL92
SQL99

2、连接查询根据连接方式分类

内连接(两张表在进行表连接的时候，连接记录百分百匹配记录可查，内连接只能是两表匹配的)
- 等值连接
- 非等值连接
- 自连接
外链接(两表进行外连接的时候，除了可以百分百记录查询，另外需要将其中任意一张表数据无条件的全部展示出来，就要使用外连接)
- 左外连接(左连接)：将左边数据全部显示
- 右外连接(右连接)：将右边数据全部显示
全连接

外连接的查询结果都是一直大于等于内连接的查询结果

3、当多张表进行连接查询，若没有任何条件进行限制，会发生什么现象

会发生总记录是两张表记录乘积(此现象是笛卡尔积现象，为了避免该现象发生，必须在表连接时候加上限制)

例

1、内连接（等值连接）—————查询每个员工所在的部门名称，要求最终显示员工名和对应的部门名(两张表：员工表(部门号)、部门表(部门号))

tip：在连接查询的时候虽然使用了限制条件，但是匹配的次数没有减少，还是与无限制一样，只是这次的结果都是有效记录

SQL92语法：（属于内连接中的等值连接）（缺点：表连接与过滤同时了，结构不清晰）

mysql> select
    -> e.ename,d.dname
    -> from
    -> emp e,dept d
    -> where
    -> e.deptno=d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| CLARK  | ACCOUNTING |
| KING   | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH  | RESEARCH   |
| JONES  | RESEARCH   |
| SCOTT  | RESEARCH   |
| ADAMS  | RESEARCH   |
| FORD   | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| TURNER | SALES      |
| JAMES  | SALES      |
+--------+------------+
14 rows in set (0.15 sec)

SQL99语法(inner可以省略)（好处：表连接与过滤分离，在最后加where即可过滤，结构清晰）

mysql> select
    -> e.ename,d.dname
    -> from
    -> emp e
    -> inner join
    -> dept d
    -> on
    -> e.deptno=d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| CLARK  | ACCOUNTING |
| KING   | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH  | RESEARCH   |
| JONES  | RESEARCH   |
| SCOTT  | RESEARCH   |
| ADAMS  | RESEARCH   |
| FORD   | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| TURNER | SALES      |
| JAMES  | SALES      |
+--------+------------+
14 rows in set (0.00 sec)

2、内连接（非等值连接，SQL99）————找出每一个员工对应的工资等级，要求显示员工名，工资，工资等级（两张表：员工表，工资等级表(salgrade)）

mysql> select
    -> e.ename,e.sal,s.grade
    -> from
    -> emp e
    -> join
    -> salgrade s
    -> on
    -> e.sal
    -> between
    -> s.losal
    -> and
    -> s.hisal;
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+
14 rows in set (0.03 sec)

3、内连接(自连接,SQL99)——找出每个员工的上级领导，要求显示员工名以及对应的领导名(一张表看成两张表)

以下是员工表，表中也有员工序号和对应上级的序号，即可看出两张表

mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO
+-------+--------+-----------+------+------------+---------+---------+--------
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10
+-------+--------+-----------+------+------------+---------+---------+--------
14 rows in set (0.00 sec)



mysql> select
    -> a.ename as empname,b.ename as leadername
    -> from
    -> emp a
    -> join
    -> emp b
    -> on
    -> a.mgr=b.empno;
+---------+------------+
| empname | leadername |
+---------+------------+
| SMITH   | FORD       |
| ALLEN   | BLAKE      |
| WARD    | BLAKE      |
| JONES   | KING       |
| MARTIN  | BLAKE      |
| BLAKE   | KING       |
| CLARK   | KING       |
| SCOTT   | JONES      |
| TURNER  | BLAKE      |
| ADAMS   | SCOTT      |
| JAMES   | BLAKE      |
| FORD    | JONES      |
| MILLER  | CLARK      |
+---------+------------+
13 rows in set (0.00 sec)

SQL92语法就是将join改成’,’，on改成where

4、外连接(右连接)——找出每个员工对应的部门名称，要求部门名称全部显示（部分多出来的记录将用null与其匹配）(左连接的话将dept和emp换个位置就好)

mysql> select
    -> e.ename,d.dname
    -> from
    -> emp e
    -> right outer join
    -> dept d
    -> on
    -> e.deptno=d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| CLARK  | ACCOUNTING |
| KING   | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH  | RESEARCH   |
| JONES  | RESEARCH   |
| SCOTT  | RESEARCH   |
| ADAMS  | RESEARCH   |
| FORD   | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| TURNER | SALES      |
| JAMES  | SALES      |
| NULL   | OPERATIONS |
+--------+------------+
15 rows in set (0.05 sec)

5、找出每个员工对应的领导名，要求显示所有的员工

mysql> select a.ename empname,b.ename leadername from emp a join emp b on a.mgr=
b.empno;
+---------+------------+
| empname | leadername |
+---------+------------+
| SMITH   | FORD       |
| ALLEN   | BLAKE      |
| WARD    | BLAKE      |
| JONES   | KING       |
| MARTIN  | BLAKE      |
| BLAKE   | KING       |
| CLARK   | KING       |
| SCOTT   | JONES      |
| TURNER  | BLAKE      |
| ADAMS   | SCOTT      |
| JAMES   | BLAKE      |
| FORD    | JONES      |
| MILLER  | CLARK      |
+---------+------------+
13 rows in set (0.00 sec)

（多张表）6、找出每个员工对应的部门名称，以及该员工对应的部门名称，工资等级。要求显示员工名、部门名、工资等级

多张表进行表连接的语法格式(a,b,c表)
原理：a与b连接后，再a与c连接

select
    ...
from 
    a
join
    b
on
    条件
join
    c
on
    条件；

mysql> select e.ename,d.dname,s.grade from emp e join dept d on e.deptno=d.deptn
o join salgrade s on e.sal between s.losal and s.hisal;
+--------+------------+-------+
| ename  | dname      | grade |
+--------+------------+-------+
| SMITH  | RESEARCH   |     1 |
| ALLEN  | SALES      |     3 |
| WARD   | SALES      |     2 |
| JONES  | RESEARCH   |     4 |
| MARTIN | SALES      |     2 |
| BLAKE  | SALES      |     4 |
| CLARK  | ACCOUNTING |     4 |
| SCOTT  | RESEARCH   |     4 |
| KING   | ACCOUNTING |     5 |
| TURNER | SALES      |     3 |
| ADAMS  | RESEARCH   |     1 |
| JAMES  | SALES      |     1 |
| FORD   | RESEARCH   |     4 |
| MILLER | ACCOUNTING |     2 |
+--------+------------+-------+
14 rows in set (0.05 sec)

TIP

为什么inner、outer可以省略

因为区分内连接和外连接依靠的不是这些关键字，而是看SQL语句中是否存在left/right，若存在，标识一定是个外连接，其他都是内连接

inner、outer加上有什么好处

增强可读性

六、子查询

子查询就是嵌套的select语句(select语句嵌套select语句)

1、子查询出现的位置

select…(select)
from…(select)
where…(select)

例

where后使用子查询

找出薪水比公司平均薪水高的员工，要求显示员工名和薪水

第一步找出公司的平均水平
```
select avg(sal) from emp;
```

第二步找出薪水大于平均薪水的员工信息

mysql> select ename,sal from emp where sal>(select avg(sal) from emp);
+-------+---------+
| ename | sal     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+
6 rows in set (0.00 sec)

from后面使用子查询（可将查询结果当做临时表）

找出每个部门的平均薪水，并找出平均薪水的薪水等级

第一步找出每个部门的平均薪水

mysql> select avg(sal) as avgsal from emp group by deptno;

第二步将上面的查询结果当做临时表t，t表和salgrade s表进行表连接，条件：t.avgsal between s.losal and s.hisal

mysql> select
    -> t.deptno,t.avgsal,s.grade
    -> from
    -> (select deptno,avg(sal) as avgsal from emp group by deptno) t
    -> join
    -> salgrade s
    -> on
    -> t.avgsal
    -> between
    -> s.losal
    -> and
    -> s.hisal;
+--------+-------------+-------+
| deptno | avgsal      | grade |
+--------+-------------+-------+
|     30 | 1566.666667 |     3 |
|     10 | 2916.666667 |     4 |
|     20 | 2175.000000 |     4 |
+--------+-------------+-------+
3 rows in set (0.12 sec)

select后使用子查询(了解)

显示员工名与其对应的部门名

mysql> select e.ename,(select d.dname from dept d where e.deptno=d.deptno) as dn
ame from emp e;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)