MySql多表查询分组查询,子查,分页查询

一.多表查询

 查询每个雇员的编号，姓名，职位，工资，部门名称，部门位置。

SELECT e.empno,e.ename,e.job,e.sal,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno;

1.全连接

 查询每个雇员的编号，姓名，职位，工资，部门名称，部门位置。

SELECT e.empno,e.ename,e.job,e.sal,d.dname
FROM emp e JOIN dept d
ON(e.deptno=d.deptno);

2.左连接

左外连接就是在全连接的基础上加上主表中的未匹配数据。
查询左边全部数据,添加右表中的数据,右边没有数据显示为null
查询出每一位雇员的编号、姓名、职位、部门编号、部门名称。

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

二.分组查询

1.常见的集合函数:

      sum()     求和
      count()   统计
      max()     最大值
      min()     最小值
      avg()     平均值

查看所有雇员中最高的薪资。

SELECT　MAX(sal) FROM emp;

统计 emp 中 有佣金的雇员的个数。

SELECT　MAX(sal) FROM emp;

2.分组查询,使用group by操作

  1.按照部门编号分组，求出每个部门的人数，平均工资

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

 2.按照职位分组，求出每个职位的最高和最低工资

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

三. 分组查询 HAVING 的使用

1. 显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于$5000，输出结果按月工资的合计升序排列：

   SELECT job,SUM(sal) sum
   FROM emp
   WHERE job<>'SALESMAN'
   GROUP BY job
   HAVING SUM(sal)>5000
   ORDER BY sum ASC;

2. 显示部门编号不是30的，的部门详细信息（部门编号、部门名称、部门人数、部门月薪资总和），并要求 部门月工资总和大于$8000，输出结果按部门月薪资的总和降序排列。

   SELECT d.deptno,d.dname,COUNT(*) 人数,SUM(e.sal) 月总收入
   FROM dept d,emp e
   WHERE d.deptno=e.deptno  AND d.deptno!=30
   GROUP BY d.deptno,d.dname
   HAVING SUM(e.sal) >8000
   ORDER BY SUM(e.sal) DESC;

   三.子查询

   子查询：指的就是在一个查询之中嵌套了其他的若干查询，嵌套子查询之后的查询SQL语句
   一般情况下：

   • WHERE：子查询一般只返回单行单列、多行单列、单行多列的数据；
   • FROM：子查询返回的一般是多行多列的数据，当作一张临时表出现。

   .1子查询在 WHERE 中出现
   查询出工资比SMITH还要高的全部雇员信息

    SELECT * FROM emp
    WHERE sal>(SELECT sal FROM emp WHERE ename='SMITH');

   2 子查询返回 单行多列 数据(少用)

    SELECT * FROM emp
    WHERE (job,sal) in (SELECT job,sal FROM emp WHERE ename='SCOTT');

   3.子查询返回 多行单列 数据
   1.>ANY：比子查询中返回记录最小的还要大的数据；

   SELECT * FROM emp
   WHERE sal >ANY( SELECT sal FROM emp WHERE job='MANAGER');

   2.>ALL：比子查询中返回的最大的记录还要大

   SELECT * FROM emp
   WHERE sal >ALL(SELECT sal FROM emp WHERE job='MANAGER');

   4.子查询返回 多行多列 数据（当临时表使用）
   查询出每个部门的编号、名称、位置、部门人数、平均工资

   SELECT d.deptno,d.dname,d.loc,temp.count,temp.avg
   FROM dept d,(
   SELECT deptno dno,COUNT(empno) count,AVG(sal) avg
   FROM emp
   GROUP BY deptno) temp
   WHERE d.deptno=temp.dno;

四.分页查询 limit

1.select * from emp limit  0(从哪儿开始),5(查询多条数据);

2.要查询 0 到 5 条数据
       select * from emp limit 0,5;

3. 比如 分页的操作
    15条数据  每一页 4条件  4页 
    第1页数据: 0 - 4;
        select * from emp limit 0,4; --  currentPage = 1
    第2页数据: 4 - 8;
        select * from emp  limit 4,4;--  currentPage = 2
    第3页数据: 8 - 12;
        select * from emp  limit 8,4; -- currentPage
    第4页数据: 12 - 15;
        select * from emp limit 12,4;

    当前的页数 currentPage = 1，2，3 
    每页的大小 pageSize = 4;
    分页的总结:
             select * from emp limit (currentPage - 1) * pageSize ,pageSize;