学习MySql第五天

复习

• distinct: 去掉重复的列值

  - 查询emp表中所有的职位,去重
  
      select distinct job from emp;
  
  - 查询emp表部门的编号,去重
  
      select distinct deptno from emp;
  

• GROUP BY 语句

• HAVING 子句

  -- 查询所有分类商品所对应的库存总量中,高于1000的总量
  
      select category_id,sum(num)
      from t_item
      group by category_id
      having sum(num)>1000;
  
  -- 查询所有分类商品所对应的平均单价中,低于100的均价
  
      select category_id,avg(price)
      from t_item
      group by category_id
      having avg(price)<100;
  
  -- 查询编号238和编号917分类商品的平均单价
  
      select category_id ,avg(price)
      from t_item
      group by category_id
      having category_id in(238,917);
  

having子句中经常跟聚合函数经常使用，如果没有使用到聚合函数，就要注意是否可以写在where条件中。

        select category_id ,avg(price)
        from t_item
        where category_id in(238,917)
        group by category_id;

• 子查询

  1.案例：查询emp表中姓名是‘KING’所属的部门的编号，名称
  
      select deptno from emp
      where ename='KING';
  
      select deptno,dname
      from dept
      where deptno=(select deptno from emp
                    where ename='KING');
  
  2.案例：查询部门名称是SALES的部门下所有员工的编号，姓名，职位，以及所属部门的编号
  
      select deptno
      from dept
      where dname='SALES'
  
      select empno,ename,job,deptno
      from emp
      where deptno=(select deptno
                    from dept
                    where dname='SALES');
  
  3.案例：查询部门地址是DALLAS的部门下所有员工的所有信息
  
      select deptno from dept
      where loc='DALLAS'
  
      select * from emp
      where deptno in (select deptno from                  dept
                       where loc='DALLAS');
  
  4.案例：查询跟JONES同样工作的员工的所有信息（包含JONES）
  
          select job from emp
          where ename='JONES'
  
          select * from emp
          where job=(select job from emp
                     where ename='JONES');
  
  不包含JONES
  
          select * from emp
          where job=(select job from emp
                     where ename='JONES')                       and ename<>'JONES';
  

• 关联查询数据

  • 使用where关联查询