avg最多用多少列 mysql_MYSQL 计数 最大值 最小值 平均数 求和

笔记：

一、聚合函数：计数 最大值  最小值  平均数  求和

1.计数 COUNT()  忽略NULL值

方式1：COUNT(*)  *代表所有字段 一般用于查询表中共有多少条记录(实体)

SELECT COUNT(*) zongshu FROM emp;

方式2：计算某个字段中非NULL值的个数

SELECT COUNT(ALL comm) FROM emp;-- all 可以省略

方式3：去重计数  计算某列中不重复非NULL值的个数

SELECT COUNT( DISTINCT job) FROM emp;

2.最大值  MAX() 求某一列中的最大值，不分组的情况下不可与其他字段一起使用

SELECT MAX(sal) FROM emp ;

3.最小值  MIN()  不分组的情况下不可与其他字段一起使用

SELECT  MIN(sal) FROM emp ;

4.平均值  AVG()  不分组的情况下不可与其他字段一起使用

SELECT AVG(sal) FROM emp;

5.求和  SUM() 不分组的情况下不可与其他字段一起使用

SELECT SUM(sal) FROM emp;

练习：

1.查询员工的总人数；

SELECT COUNT(*) FROM emp;

2.查询员工的平均工资；

SELECT AVG(sal) FROM emp;

3.查询文员的总人数；

SELECT COUNT(*) FROM emp WHERE job='文员';

4. 查询30号部门中文员的总工资；

SELECT SUM(sal+IFNULL(comm,0)) FROM emp WHERE deptno=30 AND job ='文员';

5.查询10号部门中员工的最低工资；

SELECT MIN(sal) FROM emp WHERE deptno=10;

6.查询入职时间在2001-2005的员工的最高工资

SELECT MAX(sal) FROM emp WHERE hiredate BETWEEN '2001' AND '2005';

7.查询2005年之前入职的员工的平均工资

SELECT AVG(sal) FROM emp WHERE hiredate

8.查询2002年之后入职的员工的最高工资和最低工资和平均工资。

SELECT MAX(sal) ,MIN(sal),AVG(sal) FROM emp  WHERE hiredate >'2002';

9.查询30号部门的最高工资、最低工资、平均工资；

SELECT MAX(sal),MIN(sal),AVG(sal) FROM emp WHERE deptno =30;

10.查询10号或20号部门的文员的最高工资、最低工资、平均工资；

SELECT MAX(sal),MIN(sal),AVG(sal) FROM emp WHERE (deptno =10 OR deptno =20 ) AND job ='文员';

二、分组  GROUP BY

分组计算时SELECT 语句中可以有分组字段和聚合函数，但不要放其他字段；

SELECT deptno, AVG(sal),MAX(sal) ,MIN(sal) ,SUM(sal)FROM  emp WHERE sal >20000 GROUP BY deptno;

HAVING 用户过滤聚合函数的值 只能用在分组后面

SELECT deptno ,MIN(sal),MAX(sal) FROM emp GROUP BY deptno HAVING MIN(sal)>8000 AND MAX()<50000;

SELECT deptno ,MIN(sal) minSal ,MAX(sal) b FROM emp GROUP BY deptno HAVING minSal>8000 AND b<50000;

使用多个字段分组：

计算每个部门中每个岗位的平均工资：

SELECT deptno,job,AVG(sal) FROM emp GROUP BY deptno ,job ;

分组练习：

-- 1.查询各个部门的最高工资、最低工资、平均工资。

SELECT deptno,MAX(sal),MIN(sal),AVG(sal) FROM emp GROUP BY deptno;

-- 2.查询各个职位的平均工资 降序排列

SELECT job,AVG(sal) avgSal FROM emp GROUP BY job ORDER BY avgSal DESC ;

-- 3.查询平均工资大于10000的岗位

SELECT job ,AVG(sal) avgSal FROM emp GROUP BY job HAVING avgSal >10000 ;

-- 4.查询每个部门的人数

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

-- 5.查询人数大于5的部门

SELECT deptno ,COUNT(ename) qty FROM emp GROUP BY deptno HAVING qty>5;

-- 6.查询部门人数小于3的部门的平均工资、最高工资、最低工资

SELECT deptno,AVG(sal),MAX(sal),MIN(sal) FROM emp  GROUP BY deptno HAVING COUNT(*)<=3;

-- 7.查询各个部门中工资大于10000的人数 降序显示

SELECT deptno ,COUNT(empno) qty FROM emp WHERE sal>10000 GROUP BY deptno ORDER BY qty DESC ;

-- 8.查询各个岗位中工资大于10000的人数 升序显示

-- 9.查询平均工资大于10000的前两个部门。

SELECT AVG(sal) ,deptno FROM emp  GROUP BY deptno HAVING AVG(sal)>10000 LIMIT 2;

-- 10.查询每个部门中没有津贴的人数。

SELECT deptno,COUNT(*) FROM emp WHERE comm IS NULL GROUP BY deptno

三、组合查询 - 子查询

1.单行单列  跟在WHERE子句后，用于判断的条件

-- 比李世民工资高的所有员工的信息

SELECT * FROM emp WHERE sal >(SELECT sal FROM emp WHERE ename='李世民')

2.单行多列  跟在WHERE子句后

-- 查询和李世民工资及职位都一样的人的所有信息

SELECT * FROM emp WHERE (job,sal) IN (SELECT job ,sal FROM emp WHERE ename='李世民')

AND ename !='李世民';

3.多行单列

-- 查询大于30号部门中任意一个员工工资的人的所有信息

-- any:任意的意思

SELECT * FROM emp WHERE sal >ANY(SELECT sal FROM emp WHERE deptno=30);

SELECT * FROM emp WHERE sal >  (SELECT MIN(sal) FROM emp WHERE deptno=30);

-- 查询大于30号部门中所有人工资的员工信息

SELECT * FROM emp WHERE sal>ALL(SELECT sal FROM emp WHERE deptno=30);

4.多行多列 用在FROM子句后面 作为虚表使用

SELECT e.ename FROM (SELECT * FROM emp WHERE deptno =30) e WHERE e.comm IS NULL;

组合查询-- 合并结果集  上下两条查询语句的字段个数要一样；

UNION --去除重复数据的合并；

SELECT ename,sal FROM emp WHERE deptno=10 UNION SELECT  ename,sal FROM emp WHERE deptno=20;

UNION ALL  -- 不去除重复记录

SELECT ename,sal  FROM emp WHERE deptno=10 UNION ALL SELECT  ename,sal FROM emp WHERE deptno=20;

四、链接

1. 内连接去除笛卡尔积：去除匹配错误的数据；

1.1 mysql方言方式：

SELECT * FROM emp ,dept  WHERE emp.deptno= dept.deptno AND dept.dname='学工部' ORDER BY emp.sal DESC ;

1.2 标准SQL方式：SELECT * FROM emp INNER JOIN dept  ON emp.deptno= dept.deptno;

1.3自然连接：SELECT * FROM emp NATURAL JOIN dept;

2.外连接

2.1 左外连接 LEFT OUTER JOIN  -- out可以省略,以左边表的行数为准，若右边表没有与之匹配的数据，那么用null值填充

SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno =d.deptno;

SELECT * FROM dept d LEFT OUTER JOIN emp e ON e.deptno =d.deptno;

2.2 右外连接  RIGHT OUTER JOIN  与左外连接意思相反

SELECT * FROM emp e RIGHT  JOIN dept d ON e.deptno =d.deptno;

二。1：查询雇员表中工资最高的雇员的员工号、员工姓名、工资和部门号。

2：薪水大于12000的雇员，按照部门编号进行分组，分组后的平均薪水必须大于15000，查询各分组的平均工资，按照工资的倒序进行排列

3:查询每个雇员和其所在的部门名

4.查询每个雇员姓名及其工资所在的等级

5：查询雇员名第2个字不是‘中‘的雇员的姓名、所在的部门名、工资所在的等级。

6：查询每个雇员和其经理的姓名

-- 7：查询每个雇员和其经理的姓名(包括公司老板本身(他上面没有经理))

SELECT e1.ename,IFNULL(e2.ename,'老板') FROM emp e1 LEFT JOIN emp e2 ON e1.mgr =e2.empno;

-- 8：查询每个雇员的姓名及其所在部门的部门名(包括没有雇员的部门)

SELECT e.ename,d.dname FROM dept d  LEFT JOIN emp e  ON  d.deptno=e.deptno;

-- 9：查询每个部门中工资最高的人的姓名、薪水和部门编号

SELECT * FROM emp WHERE (deptno,sal)IN (SELECT deptno,MAX(sal) sal FROM emp GROUP BY deptno);

SELECT * FROM emp e1,(SELECT deptno,MAX(sal) sal FROM emp GROUP BY deptno) e2 WHERE e1.deptno=e2.deptno AND e1.sal = e2.sal;

-- 10：查询每个部门平均工资所在的等级

S