MySQL数据库之高级查询

分组查询

1.查询为什么要分组：
默认情况下汇总函数是对全表范围内的数据做统计；
GROUP BY子句的作用是通过一定的规则将一个数据集划分成若干 个小的区域，然后针对每个小区域分别进行数据汇总处理。
2.分组查询对SELECT子句的要求：
查询语句中如果含有GROUP BY子句， 那么SELECT子句中的内容就必须要遵守规定：SELECT子句中可以包括聚合函数，或者GROUP BY子句的分组列，其余内容均不可以出现在SELECT子句中。
例如，

SELECT deptno , COUNT(*), AVG (sal)
FROM t_emp
 GROUP BY deptno;

3.对分组结果集再次做汇总计算（WITH ROLLUP）
WITH ROLLUP：在group分组字段的基础上再进行统计数据。
例如，

SELECT
deptno , COUNT(* ) ,AVG (sal)，MAX ( sal) ,MIN ( sal)
FROM  t_emp 
GROUP BY deptno WITH ROLLUP;

4.GROUP_CONCAT函数
GROUP_CONCAT函数可以把分组查询中的某个字段拼接成一个字符串。
例如，
查询每个部门内底薪超过2000元的人数和员工姓名

SELECT
deptno ,GROUP_CONCAT (ename ) , COUNT(* )
FROM t_emp 
WHERE sal>=2000
GROUP BY deptno;

5.各种子句的执行顺序
查询语句中， GROUP BY子句应该第几个执行？
FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY -> LIMIT

6.分组查询遇到的困难？
因为WHERE子句先于GROUP BY执行， 一旦WHERE子句中出现了汇总函数，数据库根本不知道按照什么范围计算汇总值。

7.HAVING子句的用途
WHERE 在分组和聚集之前过滤掉我们不需要的输入行，而 HAVING 在 GROUP 之后那些不需要的组。因此，WHERE 无法使用一个聚集函数的结果。我们也没有理由写一个不涉及聚集函数的 HAVING。 如果条件不包含聚集，可以把它写在 WHERE 里面。

例， 查询每个部门中，1982年以后入职的员工超过2个人的部门编号

SELECT deptno FROM  t_emp
WHERE hiredate>="1982-01-01"
GROUP BY deptno 
HAVING COUNT(*)>=2
ORDERBY deptno ASC;

表连接查询

1、内连接的多种语法形式
SELECT …… FROM 表1 JOIN 表2 ON 连接条件 ;
SELECT …… FROM 表1 JOIN 表2 WHERE 连接条件 ;
SELECT …… FROM 表1 , 表2 WHERE 连接条件 ;
例1，查询每个员工的工号、姓名、部门名称、底薪、职位、工资等级？

SELECT
e.empno,e.ename,d.deptno,e.sal,e.job,s.grade
FROM t_emp e
JOIN t_dept d ON e.deptno=d.deptno
JOIN t_salgrade s ON e.sal BETWEEN s.losal AND s.hisal;

例2，查询与SCOTT相同部门的员工都有谁？

SELECT e2.ename
FROM t_emp e1 JOIN t_emp e2 ON e1.deptno=e2.deptno
WHERE e1.ename="SCOTT" AND e2.ename != "sCOTT";

例3，查询月薪超过公司平均月薪的员工信息？

SELECT e1.ename
FROM t_emp e1 JOIN
(SELECT AVG (sal) avg FROM t_emp) e2
ON el.sal > e2.avg;

例4，查询每个底薪超过部门平均底薪的员工信息

SELECT 
e.empno,e.ename,e.sal,t.avg
FROM t_emp e JOIN
(SELECT deptno,AVG(sal) as avg
 FROM t_emp GROUP BY deptno) t
 ON e.deptno=t.deptno AND e.sal>=t.avg;

2、为什么要使用外连接
外连接与内连接的区别在于，除了符合条件的记录之外，结果集中 还会保留不符合条件的记录。如果说陈浩是一名临时人员，没有固定的部门编制，那么我们想查询每名员工和他的部门名称，用内连接就会漏掉陈浩，所以要引入外连接的语法才能解决这个问题。

SELECT
e.empno, e.ename,d.dname
FROM t_emp e
LEFT JOIN t _dept d ON e.deptno = d.deptno;

3、UNION关键字可以将多个查询语句的结果集进行合并

( 查询语句 ) UNION ( 查询语句 ) UNION ( 查询语句 ) ……
4、外连接练习
查询每名员工的编号、姓名、部门、月薪、工资等级、工龄、上司 编号、上司姓名、上司部门？

SELECT
e.empno,e.ename,d.dname,
e.sal+IFNULL (e.comm,0),s.grade,
FLOOR(DATEDIFF(NOW(),e.hiredate)/365),
t.empno AS mgrno,t.ename As mname,t.dname AS mdname
FROM t_emp e LEFT JOIN t_dept d ON e.deptno = d.deptno 
LEFT JOIN  t_salgrade s ON e.sal BETWEEN s.losal AND s.hisal
LEFT JOIN
(SELECT e1.empno,e1.ename,d1.dname
FROM t_emp e1 JOIN t_dept d1
ON e1.deptno=d1.deptno
)t ON e.mgr=t.empno;

外连接注意事项
内连接只保留符合条件的记录， 所以查询条件写在ON子句和 WHERE子句中的效果是相同的。但是外连接里，条件写在WHERE 子句里，不合符条件的记录是会被过滤掉的，而不是保留下来。

SELECT
e.ename,d.dname,d.deptno
FROM
t_emp e
LEFT JOIN t_dept d ON e.deptno = d.deptno AND e.deptno = 10;

SELECT
e.ename,d.dname,d.deptno
FROM
t_emp e
LEFT JOIN t_dept d ON e.deptno = d.deptno 
WHERE e.deptno = 10;

子查询

子查询介绍
子查询可以写在三个地方：WHERE子句、FROM子句、SELECT子句，但是只有FROM子句子查询是最可取的。

1.WHERE子查询
这种子查询最简单，最容易理解，但是却是效率很低的子查询 。比较每条记录都要重新执行子查询。

SELECT
empno, ename, sal
FROM  t_emp
WHERE sal >= (SELECT AVG(sal) FROM t_emp);

2.用表连接替代WHERE子查询
表连接的优点是子查询只执行一次，查询效率特别高

SELECT e2.empno,e2.ename
FROM t_emp e1 JOIN t_emp e2 ON e1.deptno=e2.deptno
AND e2.ename !="FORD"
AND e1.ename="FORD";

3.FROM子查询
这种子查询只会执行一次，所以查询效率很高。

SELECT
e.empno,e.ename,e.sal,t.avg
FROM t_emp e JOIN
(SELECT deptno, AVG (sal) as avg FROM t_emp GROUP BY deptno) t
ON e.deptno=t.deptno AND e.sal>=t.avg;

4.SELECT子查询
这种子查询每输出一条记录的时候都要执行一次，查询效率很低。

SELECT
e.empno,e.ename,
(SELECT dname FROM t_dept WHERE deptno=e.deptno)
FROM t emp e;

相关子查询
查询语句执行的时候要多次地依赖于子查询的结果，这类子查询被称作相关子查询。
WHERE子查询和SELECT子查询都属于相关子查询。
因为相关子查询要反复多次执行，所以应该避免使用。

单行子查询和多行子查询
单行子查询的结果集只有一条记录，多行子查询结果集有多行记录 。
多行子查询只能出现在WHERE子句和FROM子句中。

例，如何用子查询查找FORD和MARTIN两个人的同事？

SELECT ename FROM t_emp
WHERE 
deptno IN
(SELECT deptno FROM t_emp WHERE ename IN ("FORD"，"MARTIN" ) )
AND ename NOT IN("FORD"，"MARTIN");

WHERE子句中的多行子查询
WHERE子句中，可以使用IN、ALL、ANY、EXISTS关键字来处理多行表达式结果集的条件判断。
例，查询比FORD和MARTIN底薪都高的员工信息？

SELECT ename FROM t_emp
WHERE sal >ALL
(SELECT sal FROM t_emp
WHERE ename IN ("FORD","MARTIN "));

EXISTS关键字
EXISTS关键字是把原来在子查询之外的条件判断，写到了子查询的里面。
SELECT …… FROM 表名 WHERE [NOT] EXISTS ( 子查询 );
例，查询工资等级3和4级的员工信息？

SELECT
empno, ename, sal,comm 
FROM t_emp
WHERE EXISTS
(SELECT * FROM t_salgrade WHERE grade IN(3,4)
AND sal BETWEEN losal AND hisal);