Mysql数据库学习Day4

mysql数据库高级查询——分组函数和子查询

分组函数

分组函数是对表中一组记录进行操作，每组只返回一个结果，即首先要对表记录进行分组，然后再进行操作汇总，每组返回一个结果，分组时可能是整个表分为一组，也可能根据条件分成多组。
分组函数常用到以下五个函数：

MIN
MAX
SUM
AVG
COUNT

比如没有使用group by 对整个表进行分组就是将整个表当成一个分组

count（）函数的一些用法

count（*）返回组中全部的记录
count（comm） 表示返回非空的值的个数，也就是comm（在表中指的是奖金）
couunt（comm）返回的值就是奖金不为空的行数
使用distinct消除重读

查询有员工的部门数量

     SELECT 	COUNT(DISTINCT deptno)
     FROM	emp;

除了COUNT（*）之外，其它所有分组函数都会忽略列中的空值，然后再进行计算。

IFNULL 函数可以使分组函数强制包含含有空值的记录

 SELECT AVG(IFNULL(comm,0))
   FROM   emp;

GROUP BY 和 HAVING 的用法
GROUP BY先进行分组，之后再用having子句限制或者说是筛选组查询的结果，因此 having子句的使用是存在一定条件的，
having子句的使用条件
（1）不能写在where子句中
（2）已经使用过group by进行了分组

SELECT语句执行过程：
1.通过FROM子句中找到需要查询的表；
2.通过WHERE子句进行非分组函数筛选判断；
3.通过GROUP BY子句完成分组操作；
4.通过HAVING子句完成组函数筛选判断；
5.通过SELECT子句选择显示的列或表达式及组函数；
6.通过ORDER BY子句进行排序操作。

子查询
首先清楚的是使用子查询的情况，一般是，一句话中有两个未知
比如说问题：查询工资比Jones工资高的员工信息？
这个问题中，首先我们要知道jones的工资，其次我们将Joins的工资作为已知条件再进行查询，就可以知道哪些员工的工资比他多

select ename,sal 
from emp 
where sal>(select sal from emp where ename='Jones')

这是最基本的子查询用法。

其次我们要清楚在，子查询不仅仅可以写在where条件中，
子查询可以嵌于以下SQL子句中：
WHERE子句
HAVING子句
FROM子句中

    -- 查询工资最低的员工姓名？
    -- select ename,sal from emp
    --  where sal<(select sal from emp)
    
    -- 显示和雇员7369从事相同工作并且工资大于雇员7876的雇员的姓名和工作。
    select ename,job ,sal from emp where job=(select job from emp where empno=7369) and sal >(select sal from emp where empno=7876);
    select job from emp where empno=7369;
    select sal from emp where empno=7876;
    
    -- 查询工资最低的员工姓名，岗位及工资
    select ename,job,sal 
    from emp
    where sal=(select min(sal) from emp)
     
    -- 查询部门最低工资比20部门最低工资高的部门编号及最低工资
    select  deptno ,sal"最低工资"  from emp GROUP BY deptno having min(sal)>(Select min(sal) from emp where deptno=20)
        
   -- 查询入职日期最早的员工姓名，入职日期

    select ename,HIREDATE from emp where hiredate =(select min(hiredate) from emp)
        
    -- 查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称
    select ename,sal,dname 
    from emp e,dept d 
    where e.deptno =d.deptno 
    and e.sal>(select sal from emp where ename = 'SMITH')
    and loc='CHICAGO'
    
    -- 查询是经理的员工姓名，工资
    select  ename,sal from emp where empno = ANY (select mgr from emp)

以上情况均属于单行子查询，
所谓单行子查询就是子查询所得到的结果是一条数据

多行子查询
顾名思义多行子查询中子查询得到的返回值不止一条数据
和多行子查询进行搭配的关键词有
in
any
all

in 的使用

    SELECT	ename, sal
    FROM	emp
    WHERE 	empno IN (SELECT mgr
                       FROM  emp);

ANY的使用

• ANY：表示和子查询的任意一行结果进行比较，有一个满足条件即可。 < ANY：表示小于子查询结果集中的任意一个，即小于最大值就可以。

  ANY：表示大于子查询结果集中的任意一个，即大于最小值就可以。

= ANY：表示等于子查询结果中的任意一个，即等于谁都可以，相当于IN。

-- 查询部门编号不为10，且工资比10部门任意一名工资低的员工编号，姓名，职位，工资。

   select empno,ename,job,sal  from emp where deptno <>10 and sal>any(select sal from emp where deptno=10);
    select sal from emp where deptno=10

ALL的使用
ALL：表示和子查询的所有行结果进行比较，每一行必须都满足条件。
< ALL:表示小于子查询结果集中的所有行，即小于最小值。

ALL:表示大于子查询结果集中的所有行，即大于最大值。

= ALL :表示等于子查询结果集中的所有行,即等于所有值，通常无意义。

– 查询部门编号不为20，且工资比20部门所有员工工资高的员工编号，姓名，职位，工资。

    select  empno ,ename,job,sal 
    from emp
    where deptno<>20 
    and sal > all(select SAL from emp where deptno = 20)
    ```

**在from中使用子查询的情况**
题目：
查询比自己部门平均工资高的员工姓名，工资，部门编号，部门平均工资
```sql
    select a.ename,a.sal,a.deptno,b.avgsal 
    from emp a, (select deptno ,avg(sal) as avgsal ,empno from emp GROUP BY deptno)b
    where a.deptno=b.deptno and a.sal>b.avgsal