第四章数据库的高级查询

4.1 聚合函数

聚合函数是用于简单的统计的

一般只用sum函数求数字类型的和

4.2 分组查询

只能出现deptno以及使用的聚合函数，sal是一个单独的字段，所以不可以

注意：这里where子句要在分组语句之前，找到符合条件的数据之后再进行分组

4.3 Having子句

where子句中不能出现聚合函数，要使用having子句

SELECT deptno 
FROM t_emp
GROUP BY deptno HAVING AVG(sal)>=2000;

注意：HAVING子句中和某个数值比可以，但是不能和某个字段进行比较，要进行表连接

4.4 表连接

4.4.1 内连接

连接条件写在where子句中
JOIN可以使用逗号替代

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

使用子查询实现：

SELECT 
FROM t_emp
WHERE deptno=(SELECT deptno FROM t_emp WHERE ename="SCOTT") AND ename != 'SCOTT';

数据库在执行子查询的时候是非常慢的，如果t_emp表中有1000条数据，那就会将子查询执行1000次

SELECT 
	e2.ename
FROM
	t_emp e1, t_emp e2
WHERE
	e1.ename = 'SCOTT' AND e2.ename != 'SCOTT' AND e1.deptno = e2.deptno;

其中：

SELECT 
	*
FROM
	t_emp e1, t_emp e2
WHERE
	e1.ename = 'SCOTT' AND e2.ename != 'SCOTT'

这部分是找到SCOTT的所有同事，然后再找到相同部门的

总体思路应该是，求出平均底薪，然后把它当成一张表，和原来的员工表进行连接，然后再筛选

SELECT e.ename, e.deptno, e.sal
FROM t_emp e, (SELECT AVG(sal) avg FROM t_emp) t
WHERE e.sal > avg;

SELECT COUNT(*), MAX(e.sal), MIN(e.sal), AVG(e.sal), AVG(DATEDIFF(NOW(),e.hiredate)/365)
FROM t_emp e, t_dept d
WHERE d.dname = 'RESEARCH' AND e.deptno = d.deptno;

SELECT 
	job, 
	MAX(e.sal +IFNULL(e.comm, 0)), 
	MIN(e.sal +IFNULL(e.comm, 0)), 
	AVG(e.sal +IFNULL(e.comm, 0)), 
	MAX(s.grade), MIN(s.grade)
FROM t_emp e, t_salgrade s
WHERE (e.sal +IFNULL(e.comm, 0)) BETWEEN s.losal and s.hisal
GROUP BY job;

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

4.4.2 外连接

内连接做条件比较的时候，陈浩就不会出现在结果集中。内连接是只会出现满足连接条件的记录

左连接：保留所有左表的信息去和右表进行连接
- 右表满足条件直接连接
- 右表不满足条件，用NULL跟坐标连接

40部门没有员工，不能使用内连接；要保留40部门，就要使用外连接

SELECT d.dname, COUNT(*)
FROM t_dept d LEFT JOIN t_emp e ON d.deptno = e.deptno
GROUP BY d.deptno;

40部门没有人，为什么结果是1呢？COUNT统计的是有效记录条数，带NULL值的也算

SELECT d.dname, COUNT(e.deptno)
FROM t_dept d LEFT JOIN t_emp e ON d.deptno = e.deptno
GROUP BY d.deptno;

不能简单的使用右外连接，要使用UNION

SELECT d.dname, COUNT(e.deptno)
    FROM t_dept d RIGHT JOIN t_emp e ON d.deptno = e.deptno
GROUP BY d.deptno;

使用UNION合并左右连接的结果

(
SELECT d.dname, COUNT(e.deptno)
    FROM t_dept d LEFT JOIN t_emp e ON d.deptno = e.deptno
GROUP BY d.deptno; 
)
UNION
(
SELECT d.dname, COUNT(*)
    FROM t_dept d RIGHT JOIN t_emp e ON d.deptno = e.deptno
GROUP BY d.deptno; 
)

4.5 子查询

where子句中不推荐使用子查询，应该改为表连接的写法

4.5.1 按照子查询的位置划分

4.5.1.1 where子查询

4.5.1.2 from子查询

4.5.1.3 select子查询

4.5.2 按照结果集的数量划分

4.5.2.1 单行子查询和多行子查询

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

ALL：比所有
ANY：比任意一个

# 查询工资等级是3级或者4级的员工信息
SELECT empno, ename, sal
FROM t_emp
WHERE EXISTS (
    SELECT * FROM t_salgrade WHERE sal BETWEEN losal AND hisal AND grade IN(3,4)
);

Java工程师-15-16周MySQL基础-第四章（数据库的高级查询）

第四章 数据库的高级查询