【备战春招】第7天 Java数据库开发与实战（MySQL的高级查询2）

课程名称：Java工程师2022版

课程章节：Java数据库开发与实战

课程讲师：IT老齐 悟空 八戒猪 神思者 Leo

课程内容：MySQL的高级查询2

一、分组查询
1.WITH ROLLUP对汇总函数再次进行汇总计算。
GROUP_CONCAT函数可以把分组查询中的某个字段拼接成一个字符串，也就是合并成一条记录。
各种字句的执行顺序
FROM -> WHERE ->GROUP BY -> SELECT -> ORDER BY -> LIMIT
2.HAVING字句的用途
问题：查询每个部门中，1982年以后入职的员工超过2个人的部门编号

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

按照数字1分组，MySQL会依据SELECT字句中的列进行分组，HAVING字句也可以正常使用SELECT deptno,COUNT(*) FROM t_emp GROUP BY 1;
二、连接查询
1.从多场表中提取数据
从多张表中提取数据，必须指定关联的条件。如果不定义关联条件就会出现无条件连接，两张表的数据就会交叉连接，产生笛卡尔积。
规定了连接条件的表连接语句，就不会出现笛卡尔积。最关键的也是ON字句，只要存在ON字句就不会产生笛卡尔积。

SELECT e.empno,e.ename,d.dname
FROM t_emp e JOIN t_dept d
ON e.deptno=d.deptno;

代码中ON之后的属于条件，e和d都是别名，e是t_emp表的别名，d是t_dept表的别名。
2.表连接的分类
表连接分为两种：内连接和外连接。
内连接市结果集中只保留符合连接条件的记录。
外连接市不管符不符合连接条件，记录都要保留在结果集中。
内连接是最常见的一种表连接，用于查询多张关系表符合连接条件的记录。

SELECT ... FROM 表1
[INNER] JOIN 表2 ON 条件
[INNER] JOIN 表3 ON 条件
...

除了上述的格式以外，还可以衍生出其他几种内连接的多种语法形式，SELECT...FROM 表1 JOIN 表2 ON 连接条件； 还可以写为SELECT...FROM 表1 JOIN 表2 WHERE 连接条件；使用WHERE字句去替换ON字句也是正确的。最后还可以使用SELECT...FROM 表1, 表2 WHERE 连接条件； 直接省略JOIN字句，然后ON字句依然使用WHERE字句替换，这样的格式也是正确的。三种写法都是内连接，而且在运行的效率上没有任何的区别，查询的结果也是一模一样。
内连接的数据表不一定必要有同名的字段，只要字段之间符合逻辑关系就可以。
相同的数据表也是可以做表连接的，在语法和逻辑关系上都是没有问题的。
3.表连接查询（内连接）
可以把结果集也当作一张表进行表连接操作。
FLOOR不是四舍五入，而是向下取整，不管小数位多大。
CEIL也不是四舍五入，二十向上取整，不管小数位多小。
再次强调，查询得到的表是可以当作一张表和其他的表做表连接的。
4.表连接查询（外连接）
外连接与内连接的区别在于，除了符合条件的记录之外，结果集中还会保留不符合条件的记录。
左外连接就是保留左表所有的记录，与右表做连接。如果右表有符合条件的记录就与左表连接。如果右表没有符合条件的记录，就用NULL与左表连接。右外连接也是如此。
UNION关键字是排除重复部分的内容，然后将多个查询语句的结果集进行合并。（查询语句）UNION （查询语句）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);

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

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

这里直接用AND把条件写入ON条件字句中是不正确的，因为会导致直接保留不是10部门的人。正确的写法如下所示。

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

三、子查询
子查询是一种查询中嵌套查询的语句。
子查询可以卸载三个地方：WHERE字句，FROM字句，SELECT字句，但是只有FROM字句查询是最可取的。
FROM子查询只会查询一次，所以查询效率很高。
SELECT子查询每输出一条记录的时候都要执行一次，查询效率很低。
单行子查询的结果集只有一条记录，多行子查询结果集有多行记录。
多行子查询只有出现再WHERE字句和FROM字句中。
WHERE字句中，可以使用IN、ALL、ANY、EXISTS关键字来处理多行表达式结果集的条件判断。
EXISTS关键字是把原来在子查询之外的条件判断，写到了子查询的里面。

课程收获：

完成了今天的学习目标：
1.分组查询。
2.连接查询。
3.子查询。