MySQL02-(进阶篇)

---

主要讲排序 条件查询 分组函数 group by having

---

排序

语法

select
   字段，字段
 from   
      表名
  where
     条件
  order by
    ...
      

默认是升序

案例：员工按工资排序

mysql> select ename,sal from emp order by sal;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| JAMES  |  950.00 |
| ADAMS  | 1100.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN  | 1600.00 |
| CLARK  | 2450.00 |
| BLAKE  | 2850.00 |
| JONES  | 2975.00 |
| FORD   | 3000.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
+--------+---------+
14 rows in set (0.00 sec)

order by asc //升序
order by desc //降序

案例工资按升序排列，名字按降序排列

mysql> select ename,sal from emp order by sal ,ename desc;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| JAMES  |  950.00 |
| ADAMS  | 1100.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN  | 1600.00 |
| CLARK  | 2450.00 |
| BLAKE  | 2850.00 |
| JONES  | 2975.00 |
| SCOTT  | 3000.00 |
| FORD   | 3000.00 |
| KING   | 5000.00 |
+--------+---------+
14 rows in set (0.00 sec)

越靠前的字段越能起到主导作用，只有当前面的字段无法完成排序的时候才会启用后面的字段

执行顺序

​ select 3

​ from 1

​ where 2

​ order by 4

分组函数

也叫多行处理函数，

特点：输入多行，最终输出的结果是1行，分组函数自动忽略NULL

函数	说明
count	计数
sum	求和
avg	平均值
max	最大值
min	最小值

所有的分组函数都是对“某一组”数据进行操作

count(*) 和count(具体的某个字段)，他们有什么区别？

count(*) 不是统计某个字段中数据的个数，而是统计总记录条数（和某个字段无关）

count(comm):表示统计comm字段中不为NULL的数据总数量。

count

mysql> select count(*) from emp;
+----------+
| count(*) |
+----------+
|       14 |
+----------+

sum

mysql> select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+
1 row in set (0.00 sec)

avg

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

max

mysql> select max(sal) from emp;
+----------+
| max(sal) |
+----------+
|  5000.00 |
+----------+
1 row in set (0.00 sec)

min

mysql> select min(sal) from emp;
+----------+
| min(sal) |
+----------+
|   800.00 |
+----------+
1 row in set (0.00 sec)

单行处理函数

输入一行，输出一行

所有数据库规定，只要有NULL参与运算结果一定是NULL

案例：计算每个员工赚多少

mysql> select ename, (sal+ifnull(comm,0))*12 from emp;
+--------+-------------------------+
| ename  | (sal+ifnull(comm,0))*12 |
+--------+-------------------------+
| SMITH  |                 9600.00 |
| ALLEN  |                22800.00 |
| WARD   |                21000.00 |
| JONES  |                35700.00 |
| MARTIN |                31800.00 |
| BLAKE  |                34200.00 |
| CLARK  |                29400.00 |
| SCOTT  |                36000.00 |
| KING   |                60000.00 |
| TURNER |                18000.00 |
| ADAMS  |                13200.00 |
| JAMES  |                11400.00 |
| FORD   |                36000.00 |
| MILLER |                15600.00 |
+--------+-------------------------+
14 rows in set (0.00 sec)

**ifnull()**空处理函数

ifnull(可能为NULL的数据，被当作什么处理)

group by

按照某个字段或者某些字段进行分组

注意：分组函数一般都会和group by联合使用，这也是为什么它被称为分组函数的原因

并且任何一个分组函数(count sum avg min)都是在group by语句执行之后才会执行的。当一条sql语句没有group by的话

整张表的数据会自成一组。

select ename ,sal from emp where sal >avg(sal), 报错

原因是：SQL语句当中有一个语法规则，分组函数不可直接使用在where子句当中。

group by是在where执行之后才会执行

案例：选出每个工作岗位的最高工资

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)

案例：选出每个部门不同工作岗位的最高工资

mysql> select DEPTNO,JOB,MAX(SAL) FROM emp group by DEPTNO,JOB;
+--------+-----------+----------+
| DEPTNO | JOB       | MAX(SAL) |
+--------+-----------+----------+
|     10 | CLERK     |  1300.00 |
|     10 | MANAGER   |  2450.00 |
|     10 | PRESIDENT |  5000.00 |
|     20 | ANALYST   |  3000.00 |
|     20 | CLERK     |  1100.00 |
|     20 | MANAGER   |  2975.00 |
|     30 | CLERK     |   950.00 |
|     30 | MANAGER   |  2850.00 |
|     30 | SALESMAN  |  1600.00 |
+--------+-----------+----------+
9 rows in set (0.00 sec)

having:

having是对分组之后的数据进行再次过滤

案例：找出平均工资大于1000的部门

mysql>  select DEPTNO,avg(sal) from emp group by DEPTNO HAVING AVG(SAL)>1000;
+--------+-------------+
| DEPTNO | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)

总结一个完整的DQL语句

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

2020/4/11