分组查询：
关键字：group by 分组
分组概念：就按照一定的规则，将一个数据集划分为若干个小的区域
然后针对每个小的区域做汇总处理
简单说就是根据某个字段进行分组（相同的放一组，不同的分到不同的分组）
注意：
使用group by的时候，select子句中只能有group by子句中的字段，或者被聚合函数包裹的字段
group by后面如果要加条件，必须用having进行连接
-- 查看每个部门的平均工资是多少？？？ round() 四舍五入
select round(avg(sal+ifnull(comm,0)),2),deptno from emp group by deptno;
select round(123.6); -- 124
select round(123.156,2); -- 123.16
-- 类似于去重效果
select distinct deptno from emp;
select deptno from emp group by deptno;
-- 查询除了salesman职业以外的其他各个职业的工资总和是多少？
-- 先找出所有职业的工资总和，然后再排除salesman
select job,sum(sal+ifnull(comm,0)) from emp group by job;
select job,sum(sal+ifnull(comm,0)) from emp group by job where job != 'salesman'; -- 报错
select job,sum(sal+ifnull(comm,0)) from emp group by job having job != 'salesman'; -- 正确
select job,sum(sal+ifnull(comm,0)) from emp where job != 'salesman' group by job; -- 不能这么写，某些场景下会报错
where 和 having的区别
where是在结果返回之前起作用，where的优先级要高于group by
having 是结果返回之后起作用
where是从数据表中字段直接进行筛选
having是从select子句中进行筛选
-- where 和 having 都可以使用的场景
select ename,deptno from emp where deptno = 20;
select ename,deptno from emp having deptno = 20;
-- where可用，having不可用
select ename from emp where deptno = 20; -- 表中有deptno字段 不报错
select ename from emp having deptno = 20; -- select子句中没有deptno这个字段，所以报错
-- where不可用，having可用
select job,sum(sal+ifnull(comm,0)) from emp group by job where job != 'salesman'; -- 报错
select job,sum(sal+ifnull(comm,0)) from emp group by job having job != 'salesman'; -- 正确
select job,sum(sal+ifnull(comm,0)) from emp where job != 'salesman' group by job; -- 不能这么写，某些场景下会报错
select job,sum(sal+ifnull(comm,0))as temp from emp where temp>200 group by job; -- 不能这么写，表中没有temp这个字段，所以报错
逐级分组：
group by 子句用可以有多个字段
-- 查询每个部门里面，每种职位的员工的数量和平均工资？
select deptno,job,count(1),avg(sal+ifnull(comm,0)) from emp group by deptno,job;
group_concat() 很重要，很多场景需要使用到这个库函数
作用：将group by查询出来的同一个分组中的值，拼接起来，返回一个字符串结果
举例场景：
博客系统
文章 和 标签
一篇文章可以对应N个标签
-- 查看每个职业下面有多少员工，需要看到员工的名字
select group_concat(ename),job from emp group by job;
-- concat() 用于字符串拼接
select concat(1,2); -- 12
select concat(null,1); -- null
select concat(-1,1); -- -11
并集查询
将N个查询语句的结果集 合并到一起
关键字：union :不会去重
union all:会去除重复数据
-- 查询底薪在 1200 以上的员工信息
select * from emp where sal > 1200;
-- 查询底薪在 2600 以上的员工的信息
select * from emp where sal > 2600;
-- 合并两个结果集在一起
select * from emp where sal > 1200
union
select * from emp where sal > 2600;
-- 合并两个结果集在一起 union all不会去除重复数据
select * from emp where sal > 1200
union all
select * from emp where sal > 2600;
-- 使用union的时候，select子句的字段数量和类型要相同
select comm,sal from emp where sal > 1200
union all
select sal,comm from emp where sal > 2600;
关联查询：
概念：就是从多张表中联合查询数据，结果集来源于多张不同的表。
在关联查询中，用于指定表和表之间的联系条件：关联条件
注意：
-
关联查询中，一定要有关联条件，否则会出现笛卡尔积（n*n 没有意义，开销巨大）
-
N张表关联，至少要有N-1个关联条件。
-
关联条件和过滤条件要同时成立
-- 查询所有员工所在部门的地址？
select ename,loc from emp,dept where emp.deptno = dept.deptno;
select ename,loc from emp e,dept d where e.deptno = d.deptno;
-- 查看在 DALLAS 工作的员工有哪些？
select ename,loc from emp e,dept d where e.deptno = d.deptno and d.loc = 'DALLAS';
-- 查看底薪高于2000的员工的名字，底薪，所在部门的名字？
-- 先把要查的字段写上，再看这些字段来源于几张表，然后把表名写上，然后再看表的关联条件，再写过滤条件
select ename,sal,dname from emp e,dept d where e.deptno = d.deptno and e.sal > 2000;
join关联
全连接 inner join 返回的两张表的交集(几乎不用，因为有简写)
左外连接 left outer join 左表数据全部返回，右表返回有关联的数据
右外连接 right outer join 右表的数据全部返回，左边返回右关联的数据
join优化：
-
小的结果集去驱动大的结果集，就是用数据量小的表去驱动大的表，可以减少循环的个数，也就是扫描的个数
-
用来Join的字段要加上索引，会自动触发INLJ的算法
-
如果无法使用索引，可以调整join buffer的大小
-
减少不必要的字段查询（就是不要用*）
-- 查看在dalllas工作的员工有哪些
select ename,loc from emp e,dept d where e.deptno = d.deptno and d.loc = 'DALLAS';
-- 把两个表之间的,替换成inner join 把where 替换成On
select ename,loc from emp e inner join dept d on e.deptno = d.deptno and d.loc = 'DALLAS';
-- inner可以省略
select ename,loc from emp e join dept d on e.deptno = d.deptno and d.loc = 'DALLAS';
-- 左外连接 left outer join 左表数据全部返回，右表返回有关联的数据
select e.*,d.* from emp e left outer join dept d on e.deptno = d.deptno;
-- outer一般都不写，可以省略
select e.*,d.* from emp e left join dept d on e.deptno = d.deptno;
子查询：（不到万不得已，少用）
子查询是一条sql语句，是嵌套在其他sql中的
作用是为外层sql语句提供结果集
-- 查询和FORD同部门的员工都有谁？
-- 拆开来查，然后再组合到一起
-- 1. 查看FORD的部门号是多少
select deptno from emp e where e.ename = 'FORD'; -- 20
-- 2. 查看20号部门的员工都有谁
select * from emp e where e.deptno = 20;
-- 3. 组合到一起
select * from emp e where e.deptno =(select deptno from emp e where e.ename = 'FORD');
-- 查看比公司平均工资高的员工都有谁？
select * from emp e where e.sal > (select avg(sal) from emp);
-- 查看职位和 SMITH 相同，但是薪水比 FORD 低的员工都有谁？
select job from emp e where e.ename = "smith";
select sal from emp e where e.ename = 'ford';
select * from emp e where e.job = (select job from emp e where e.ename = "smith")
and sal<(select sal from emp e where e.ename = 'ford');
-- 子查询用在ddl中 通常用于表的快速备份
create table t_emp as (select * from emp);
create table emp as (select * from t_emp);
-- 子查询用在dml中
insert into t_emp(empno,ename) select 1,"Andy";
insert into t_emp(empno,ename) select empno,ename from emp;
