05.DQL(数据查询语言)基本操作下篇

二、DQL基本操作

2.6、聚合函数 sum avg max min count

聚合函数是用来做纵向运算的函数：

• COUNT()：统计指定列不为NULL的记录行数；
• MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
• MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
• SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
• AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

COUNT
当需要纵向统计时可以使用COUNT()。
查询emp表中记录数：

SELECT COUNT(*) AS cnt FROM emp;

---

查询emp表中有佣金的人数：

SELECT COUNT(comm) cnt FROM emp;

注意，因为count()函数中给出的是comm列，那么只统计comm列非NULL的行数。

---

查询emp表中月薪大于2500的人数：

SELECT COUNT(*) FROM emp WHERE sal > 2500;

---

统计月薪与佣金之和大于2500元的人数：

SELECT COUNT(*) AS cnt FROM emp WHERE sal+IFNULL(comm,0) > 2500;

---

查询有佣金的人数，有领导的人数：

SELECT COUNT(comm), COUNT(mgr) FROM emp;

---

SUM和AVG
当需要纵向求和时使用sum()函数。
查询所有雇员月薪和：

SELECT SUM(sal) FROM emp;

---

查询所有雇员月薪和，以及所有雇员佣金和：

SELECT SUM(sal), SUM(comm) FROM emp;

---

查询所有雇员月薪+佣金和：

SELECT SUM(sal+IFNULL(comm,0)) FROM emp;

---

统计所有员工平均工资：

SELECT AVG(sal) FROM emp;

---

MAX和MIN
查询最高工资和最低工资：

SELECT MAX(sal), MIN(sal) FROM emp;

---

2.7、分组查询

当需要分组查询时需要使用GROUP BY子句，例如查询每个部门的工资和，这说明要使用部门来分组。

注：凡和聚合函数同时出现的列名，一定要写在group by 之后

查询每个部门的部门编号和每个部门的工资和：

SELECT deptno, SUM(sal) FROM emp GROUP BY deptno;

---

查询每个部门的部门编号以及每个部门的人数：

SELECT deptno,COUNT(*) FROM emp GROUP BY deptno;

---

查询每个部门的部门编号以及每个部门工资大于1500的人数：

SELECT deptno,COUNT(*) FROM emp WHERE sal>1500 GROUP BY deptno;

---

2.8、HAVING子句

查询工资总和大于9000的部门编号以及工资和：

SELECT deptno, SUM(sal)FROM emp GROUP BY deptno HAVING SUM(sal) > 9000;

　　
注：having与where的区别:
1.having是在分组后对数据进行过滤.where是在分组前对数据进行过滤
2.having后面可以使用聚合函数(统计函数)
where后面不可以使用聚合函数。
WHERE是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组；而HAVING是对分组后数据的约束。

2.9、LIMIT 方言

LIMIT用来限定查询结果的起始行，以及总行数。

查询5行记录，起始行从0开始

SELECT * FROM emp LIMIT 0, 5;

注意，起始行从0开始，即第一行开始！

查询10行记录，起始行从3开始

SELECT * FROM emp LIMIT 3, 10;

分页查询
如果一页记录为10条，希望查看第3页记录应该怎么查呢？
第一页记录起始行为0，一共查询10行；
第二页记录起始行为10，一共查询10行；
第三页记录起始行为20，一共查询10行；

INT currentPage=1; -- 当前页
INT pageSize=10; -- 每页显示的条数

SELECT * FROM emp LIMIT (currentPage-1)*pageSize,pagesize;
-- 当前页等于1时，limit 0,10;当前页等于2时，limit 10,10;

查询代码的书写顺序和执行顺序
查询语句书写顺序：select – from- where- group by- having- order by-limit
查询语句执行顺序：from - where -group by - having - select - order by-limit