MySQL学习笔记——子查询

mysql子查询
    where
        场景：对商品库存表查询
            查询最新的商品信息
              

 select  good_id, name, price from goods order by  goods_id desc limit 1;
 select goods_id, name, price from goods where goods_id=5;
 select goods_id, name, price from goods where goods_id=(select max(goods_id) from goods); 

  
            查询每个分类下最新的商品
              

select max (goods_id) from goods group by category;
select goods_id, name,  price, category from goods       
where goods_id in (                                                       
   select max (goods_id) from goods group by category 
);

            查询商品价格小于商品ID在2到5内的商品价格的商品（<  >  <=  >=   ）#all,same,any
                

select price from goods where goods_id > 2 and goods > 5;
select name, price, from goods 
where price < all (
    select price from goods where goods_id > 2 and goods_id < 5 
);
select name, price, from goods 
where price < same (
    select price from goods where goods_id > 2 and goods_id < 5 
);
select name, price, from goods 
where price < any(
select price from goods where goods_id > 2 and goods_id < 5 
);

    from
        非相关子查询
            查询不同分类下最大的商品价格，商品名称，商品分类
              

select goods_id, name, category from goods;
select tmp.name, tmp.category, from (
    select name , price, category from goods
)as tmp;

        相关子查询
            查询每个部门的编号，名称，位置，部门人数，平均工资
                

select d.dept_id , d.name, d.loc from dept as d,(                     
   select dept_id, count(emp_id) as emp_count, round(avg(money) , 2 ) as  avg_money        from emp 
   group by depr_id ) as tmp     
where d.dept_id = tmp.dept_id;

            查询所有销售一部的员工工号，姓名，工资，和该部门的最高最低工资
                查询所有销售一部的员工工号，姓名，工资
                    select emp_id， name， money from emp  where dept_id = (select dept_id from dept     where name = '销售一部');
                查询该部门的最高最低工资
                    select e.emp_id， e.name， e.money , tmp.max_money tmp.min_money  
from emp as e, ( select max(money) as max_money, min(money) as min_money    from emp   group by dept_id   ) as tmp                                               where e.dept_id = (select dept_id from dept   where name ='销售一部'   and   e.dept_id  =  tmp.dept_id);
                查询工资高于公司平均工资且高于部门平均工资的员工编号，姓名，工资，部门名称，位置，部门人数，该部门的平均工资
                    select e.emp_id, e.name, e.money, d.name, d.loc , tmp.emp_count,tmp.avg_money  from emp e, dept d, (                                                                                                            
     select  dept_id count(emp_id) emp_count,  round(avg(money), 2) avg_money                           
     form emp                                                                                                                          
      group by dept_id) tmp                                                                                                             
where e.money > (select avg(money) from emp)             and e.dept_id = d.dept_id               and e.dept.id = tmp.dept_id                   and e.money > tmp.avg_money;
    exist
        若班级存在，返回该班级学生姓名
            select name from student                              
where class_id = ( select id from class     where  class_name ='1班')                                              and  exists(select id from class where class_name = '1班');
子查询：一个查询句嵌套在另一个查询语句内部的查询，也叫嵌套查询
总结：1，=保证子查询（内部查询）返回值为单行单列单值；2，若返回多行单列in < > <= >= 若使用这些需要all same any