数据库总结（三）：分组，联结

数据库总结（一）：基本SQL

数据库总结（二）：基本查询

数据库总结（三）：分组，联结

数据库总结（四）：表设计之关联关系

数据库总结（五）：视图，约束，索引

数学相关的函数

1. 向下取整 floor(num)
   select floor(3.14);
2. 四舍五入 round(num)
   select round(23.8);
   round(num,m)
   select round(23.879,2);
3. 非四舍五入 truncate(num,m)
   select truncate(23.879,2);
4. 随机数 rand() 0-1
   select rand();
   -获取0-5的整数随机数
   select floor(rand()*6);
   -3-5的随机数

分组查询 group by

1. 查询每个部门的平均工资
   select deptno,avg(sal) from emp group by deptno;

2. 查询每个部门的工资总和
   select deptno,sum(sal) from emp group by deptno;

3. 查询每种职业的最高工资
   select job,max(sal) from emp group by job;

4. 查询每个领导下的人数
   select mgr,count(*) from emp
   where mgr is not null group by mgr;

5. 查询每个部门工资大于1000的员工数量
   select deptno,count(*) from emp
   where sal>1000 group by deptno;
   多字段分组查询 只需要在group by后面写多个字段名通过逗号分隔

6. 每个部门每个主管的手下人数
   select deptno,mgr,count(*) from emp
   where mgr is not null
   group by deptno,mgr;

   53.案例：查询emp表中每个部门的编号，人数，工资总和，最后根据人数进行升序排列，如果人数一致，根据工资总和降序排列。
   select deptno,count(),sum(sal) from emp
   group by deptno
   order by count(),sum(sal) desc;
   -别名写法
   select deptno,count(*) c,sum(sal) s from emp
   group by deptno
   order by c,s desc;

   54.案例：查询工资在1000~3000之间的员工信息，每个部门的编号，平均工资，最低工资，最高工资，根据平均工资进行升序排列。
   select deptno,avg(sal) a,min(sal),max(sal) from emp
   where sal between 1000 and 3000
   group by deptno
   order by a;
   55.案例：查询含有上级领导的员工，每个职业的人数，工资的总和，平均工资，最低工资，最后根据人数进行降序排列，如果人数一致，根据平均工资进行升序排列
   select job,count(*) c,sum(sal),avg(sal) a,min(sal) from emp where mgr is not null
   group by job order by c desc,a;

having

where后面只能写普通字段的条件 不能写聚合函数的条件
having和where类似都是用于添加条件的，having后面可以写普通字段的条件也可以写聚合函数的条件，但是建议写聚合函数的条件，而且要结合group by 使用

1. 查询每个部门的平均工资，要求平均工资大于2000
   -错误写法 where后面不能写聚合函数
   select deptno,avg(sal) from emp where avg(sal)>2000 group by deptno;
   -正确写法：
   select deptno,avg(sal) a from emp
   group by deptno having a>2000;
2. 查询商品表中每个分类的平均单价，要求平均单价小于100
   select category_id,avg(price) a from t_item group by category_id having a<100;
3. 查询emp表中工资在1000-3000之间的员工，每个部门的编号，工资总和，平均工资，过滤掉平均工资低于2000的部门，按照平均工资降序排序
   select deptno,sum(sal),avg(sal) a from emp where sal between 1000 and 3000 group by deptno having a>=2000 order by a desc;
4. 查询emp表中平均工资高于2000的部门编号，部门人数，平均工资，最后根据平均工资降序排序
   select deptno,count(*),avg(sal) a from emp group by deptno having a>2000 order by a desc;
5. 查询emp表中不是以s开头的职位，每个职位的名字，人数，工资总和，最高工资，过滤掉平均工资是3000的职位，根据人数升序，如果人数一致则根据工资总和降序
   select job,count(*) c,sum(sal) s,max(sal) from emp where job not like ‘s%’ group by job having avg(sal)!=3000 order by c, s desc;
6. 查询每年入职的人数(扩展)
   select extract(year from hiredate) year,count(*) from emp
   group by year;
7. 查询最高平均工资的部门编号（扩展） （并列第一的问题不能解决）
   select deptno from emp group by deptno
   order by avg(sal) desc limit 0,1;

子查询（嵌套查询）

可以在查询语句中嵌套另一条sql语句,可以嵌套n层

1. 查询emp表中工资最高的员工信息
   select max(sal) from emp;
   select * from emp where sal=(select max(sal) from emp);
2. 查询emp表中工资大于平均工资的员工信息
   select avg(sal) from emp;
   select * from emp where sal>(select avg(sal) from emp);
3. 查询工资高于20号部门最高工资的员工信息
   select max(sal) from emp where deptno=20;
   select * from emp where sal>(select max(sal) from emp where deptno=20);
4. 查询和jones相同工作的其它员工信息
   select job from emp where ename=‘jones’;
   select * from emp where job=(select job from emp where ename=‘jones’) and ename!=‘jones’;
5. 查询工资最低的员工的同事们的信息(同事=相同job)
   -得到最低工资
   select min(sal) from emp;
   -得到拿最低工资员工的职位
   select job from emp where sal=(select min(sal) from emp);
   -通过职位得到此职位的员工信息排除最低工资那个哥们儿
   select * from emp where job=(select job from emp where sal=(select min(sal) from emp)) and sal!=(select min(sal) from emp);
6. 查询最后入职的员工信息
   select max(hiredate) from emp;
   select * from emp where hiredate=(select max(hiredate) from emp);
7. 查询员工king所属的部门编号和部门名称（需要用到部门表dept）
   select deptno from emp where ename=‘king’;
   select deptno,dname from dept where deptno=(select deptno from emp where ename=‘king’);
8. 查询有员工的部门信息(查询在员工表中出现的部门的信息)
   -得到员工表中的部门编号
   select distinct deptno from emp;
   -查询上面结果对应的部门详情
   select * from dept where deptno in (select distinct deptno from emp);
9. 查询平均工资最高的部门信息(难度最高 需要考虑并列第一问题)
   • 查询最高的平均工资
     select avg(sal) a from emp group by deptno order by a desc limit 0,1;
   • 通过最高的平均工资找到对应的部门编号
     select deptno from emp group by deptno having avg(sal)=(select avg(sal) a from emp group by deptno order by a desc limit 0,1);
   • 通过部门编号查询部门信息
     select * from dept where deptno in(上面一坨);

子查询总结：

1. 嵌套在sql语句中的查询语句称为子查询
2. 子查询可以嵌套n层
3. 子查询可以写在什么位置？

• 写在where和having的后面 当做查询条件的值
• 写在创建表的时候
  • 格式： create table 表名 as (子查询)
    create table newemp as (select * from emp where deptno=10);
• 写在from后面当成一个虚拟表 必须有别名
  select ename from (select * from emp where deptno=10) newtable;

关联查询

同时查询多张表的查询方式称为关联查询

1. 查询每一个员工的姓名和其对应的部门名称
   select e.ename,d.dname
   from emp e,dept d
   where e.deptno=d.deptno;
2. 查询在new york工作的所有员工信息
   select e.*
   from emp e,dept d
   where e.deptno=d.deptno
   and d.loc=‘new york’;

笛卡尔积

• 关联查询必须写关联关系，如果不写则得到两张表的乘积，这个乘积称为笛卡尔积
• 工作中不允许出现因为 超级耗内存 有可能直接崩溃

等值连接和内连接

• 等值连接和内连接都是关联查询的查询方式
• 等值连接和内连接查询到的结果一样，都为两张表的交集数据
• 等值连接： select * from A,B where A.x=B.x and A.age=18;
• 内连接：select * from A join B on A.x=B.x where A.age=18;

1. 查询每个员工的姓名和对应的部门名称
   select e.ename,d.dname
   from emp e join dept d
   on e.deptno=d.deptno;
2. 查询在new york工作的所有员工信息
   select e.*
   from emp e join dept d
   on e.deptno=d.deptno
   where d.loc=‘new york’;

外链接

• 格式：select * from A left/right join B on A.x=B.x where A.age=18;

1. 查询部门表的全部名称和对应的员工姓名
   select d.dname,e.ename
   from emp e right join dept d
   on e.deptno=d.deptno;

关联查询总结：

1. 关联查询的查询方式包括三种： 等值连接、内连接和外链接
2. 如果查询两张表的交集数据使用等值连接和内连接，推荐使用内连接
3. 如果查询一张表的全部数据和另外一张表的交集数据使用外连接，外链接只需要掌握一种即可

总结

1. 数学相关函数

• 向下取整 floor()
• 四舍五入 round()
• 非四舍五入 truncate()
• 随机数 rand() 0-1

1. 分组查询 group by
2. having 后面写聚合函数的条件 需要结合group by使用 where后面写普通字段的条件
3. 子查询：嵌套在另外一条sql语句中的查询语句称为子查询，可以嵌套n层
   可写的位置： 可写的位置：
   1. 写在where和having后面 当做查询条件的值
   2. 写在创建表的时候，把查询出来的结果保存到新的表中
   3. 写在from后面当成一张虚拟表 必须有别名
4. 关联查询：同时查询多张表的查询方式称为关联查询

• 关联查询的查询方式三种：
• 等值连接：select * from A,B where A.x=B.x and A.age=18;
• 内连接： select * from A join B on A.x=B.x where A.age=18;
• 外链接： select * from A left/right join B on A.x=B.x where A.age=18;
• 如果需要查询两张表的交集数据使用等值连接和内连接(推荐)
• 如果查询一张表的全部数据和另外一张表的交集数据使用外链接(左外和右外都可以)
• 笛卡尔积：关联查询不写关联关系则会得到两张表的乘积，这个乘积称为笛卡尔积

作业：

1. 每个部门的人数，根据人数降序排序

   • select deptno,count(*) c from emp group by deptno order by c desc;
   • 正确写法
     select d.deptno,count(e.empno) c
     from emp e right join dept d
     on e.deptno=d.deptno group by d.deptno order by c desc;

2. 每个部门中，每个主管的手下人数
   select deptno,mgr,count(*) from emp where mgr is not null group by deptno,mgr;

3. 每种工作的平均工资
   select job,avg(sal) from emp group by job;

4. 每年的入职人数
   select extract(year from hiredate) year,count(*) from emp
   group by year;

5. 少于等于3个人的部门信息

   • 得到部门编号
     select deptno from emp group by deptno having count(*)<=3;
   • 通过部门编号查部门信息
     select * from dept where deptno in(select deptno from emp group by deptno having count(*)<=3);
   • 包含40号部门的写法：
     select d.*
     from emp e right join dept d
     on e.deptno=d.deptno group by d.deptno
     having count(e.empno)<=3;

6. 拿最低工资的员工信息
   select * from emp where sal=(select min(sal) from emp);

7. 只有一个下属的主管信息
   -得到主管编号
   select * from emp where mgr is not null
   group by mgr having count()=1;
   -通过主管编号查询详细信息
   select * from emp where empno in(select mgr from emp where mgr is not null
   group by mgr having count()=1);

8. 每月发工资最多的部门信息

   • 得到部门最高的工资总和
     select sum(sal) from emp group by deptno order by sum(sal) desc limit 0,1;
   • 通过最高的工资总和查询部门编号
     select deptno from emp group by deptno having sum(sal)=(select sum(sal) from emp group by deptno order by sum(sal) desc limit 0,1);
   • 通过部门编号得到部门详情
     select * from dept where deptno in(上面一坨);

9. 下属最多的人，查询其个人信息
   -得到最多的人数
   select count() from emp group by mgr order by count() desc limit 0,1;
   -通过最大人数得到领导的编号
   select mgr from emp group by mgr having count()=(select count() from emp group by mgr order by count(*) desc limit 0,1);

   • 通过领导编号得到个人详情
     select * from emp where empno in(上面一坨);

10. 拿最低工资员工的同事信息
    select min(sal) from emp;

    select job from emp where sal=(select min(sal) from emp);

    select * from emp where job=(select job from emp where sal=(select min(sal) from emp)) and sal!=(select min(sal) from emp);

11. 和最后入职的员工在同一部门的员工信息
    select max(hiredate) from emp;
    select deptno from emp where hiredate=(select max(hiredate) from emp);
    select * from emp where deptno=(第二坨) and hiredate!=(第一坨);

12. 查询平均工资高于20号平均工资的部门信息
    select avg(sal) from emp where deptno=20;

    select d.*
    from emp e join dept d
    on e.deptno=d.deptno
    group by d.deptno
    having avg(sal)>(select avg(sal) from emp where deptno=20);

13. 查询员工信息和员工对应的部门名称
    select e.*,d.dname
    from emp e join dept d
    on e.deptno=d.deptno;

14. 查询员工信息，部门名称，所在城市
    select e.*,d.dname,d.loc
    from emp e join dept d
    on e.deptno=d.deptno;

15. 查询Dallas市所有的员工信息
    select e.*
    from emp e join dept d
    on e.deptno=d.deptno where d.loc=‘dallas’;

16. 计算每个城市的员工数量
    select d.loc,count(e.ename)
    from emp e right join dept d
    on e.deptno=d.deptno
    group by d.loc;

17. 查询员工信息和他的主管姓名
    select e.ename,m.ename
    from emp e join emp m
    on e.mgr=m.empno;

18. 员工信息，员工主管名字，部门名
    select e.ename,m.ename,d.dname
    from emp e join emp m
    on e.mgr=m.empno
    join dept d
    on e.deptno=d.deptno;

19. 员工名和他所在部门名
    select e.ename,d.dname
    from emp e join dept d
    on e.deptno=d.deptno;

20. 案例：查询emp表中所有员工的姓名以及该员工上级领导的编号，姓名，职位，工资
    select e.ename,m.empno,m.ename
    from emp e left join emp m
    on e.mgr = m.empno;

21. 案例：查询emp表中名字中没有字母’K’的所有员工的编号，姓名，职位以及所在部门的编号，名称，地址
    insert into emp (empno,ename) values(10010,‘苍老师’);

    select e.empno,e.ename,e.job,d.*
    from emp e left join dept d
    on e.deptno=d.deptno
    where e.ename not like ‘%k%’;

22. 案例：查询dept表中所有的部门的所有的信息，以及与之关联的emp表中员工的编号，姓名，职位，工资
    select d.*,e.empno,e.ename,e.job,e.sal
    from emp e right join dept d
    on e.deptno=d.deptno;