数据库教程:MySql中子查询内查询示例详解

  #where或having后面  #1.标量子查询(单行子查询)  #2.列子查询(多行子查询)  #3.行子查询(多列多行)  #特点:  # ①子查询放在小括号内  # ②子查询一般放在条件的右侧  # ③标量子查询:一般搭配着单行操作符使用  #  单行操作符: > < >= <= <> !-  #  列子查询，一般搭配着多行操作符使用  #  in,any/some(任意),all  # ④子查询的执行优先与主查询执行,主查询的条件用到了子查询的结果。

  #1.标量子查询  #案例1:谁的工资比abel高？  #①查询abel的工资  select salary  from employees  where last_name = 'abel';

  #②查询员工的信息，满足salary>①结果  select *  from employees  where salary>(select salary from employees where last_name='abel');

  #案例2.返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id,工资。  #①查141员工的job_id  select job_id  from employees  where employee_id='141';

  #②查143员工的salary  select salary  from employees  where employee_id='143';

  #③最后合并结果  select concat(last_name,first_name) as 姓名,  job_id as 工种编号,  salary as 工资  from employees  where job_id=(   select job_id   from employees   where employee_id='141'  )  and salary>(   select salary   from employees   where employee_id='143'  );

  #案例3.返回公司工资最少的员工的last_name,job_id和salary。  select min(salary)  from employees;

  select  last_name as 姓,  salary as 工资,  job_id as 工种编号  from employees  where salary=(   select min(salary)   from employees       );

  #案例4.查询最低工资大于50号部门最低工资的部门id和其最低工资。  #①查50部门的最低工资  select min(salary)  from employees  where department_id=50;

  #分组后，筛选条件①.【不用排除没有部门的所以不筛选部门编号】  select department_id as 部门编号,  min(salary) as 月薪  from employees  #where department_id  group by department_id  having 月薪>(   select min(salary)   from employees      );

  #2.列子查询(多行子查询)  #返回多行  #使用多行比较操作符

  #案例1.返回location_id是1400或1700的部门中的所有员工姓名。  #①查询location_id是1400或1700的部门编号  select distinct department_id  from departments  where location_id in(1400,1700);

  #②查询员工姓名，要求部门号是①列表的某一个  select concat(last_name,first_name) as 姓名  from employees  where department_id in (   select distinct department_id   from departments   where location_id in(1400,1700)   );

  用any替代in与上面同样的结果  select concat(last_name,first_name) as 姓名  from employees  where department_id = any(   select distinct department_id   from departments   where location_id in(1400,1700)   );

  #案例.返回location_id不是1400或1700的部门中的所有员工姓名。  select concat(last_name,first_name) as 姓名  from employees  where department_id not in(   select distinct department_id   from departments   where location_id in(1400,1700)  );  ==============================  select concat(last_name,first_name) as 姓名  from employees  where department_id <> all(   select distinct department_id   from departments   where location_id in(1400,1700)  );

  #案例2.返回其他工种中比job_id为it_prog部门任意一工资低的员工工号,  # 姓名,job_id以及salary  #①把it_prog部门中的工资查出来  select distinct salary  from employees  where job_id='it_prog';

  #②把不是it_prog部门信息查出来  select *  from employees  where job_id != 'it_prog';

  #③合并①与②在员工表中查出来  select employee_id as 员工编号,  concat(last_name,first_name) as 姓名,  job_id as 工种编号,  salary as 工资  from employees  where job_id != 'it_prog'  and salary<any(   select salary   from employees   where job_id='it_prog'    );

  用max代替any与上面同样的效果  select employee_id as 员工编号,  concat(last_name,first_name) as 姓名,  job_id as 工种编号,  salary as 工资  from employees  where job_id <> 'it_prog'  and salary<(   select max(salary)   from employees   where job_id='it_prog'    );

  #案例3.返回其他部门中比job_id为‘it_prog'部门所有工资都低的员工  #的员工号，姓名，job_id以及salary。  #①先把it_prog部门的工资查出来。  select distinct salary  from employees  where job_id='it_prog';

  select employee_id as 员工号,  concat(last_name,first_name) as 姓名,  job_id as 工种编号,  salary as 工资  from employees  where salary<all(   select distinct salary   from employees   where job_id='it_prog'  )   and job_id <> 'it_prog';  =============================  min替代all  select employee_id as 员工号,  concat(last_name,first_name) as 姓名,  job_id as 工种编号,  salary as 工资  from employees  where salary<(  　　select min(salary)  　　from employees  　　where job_id='it_prog'  )   and job_id <> 'it_prog';

  #3.行子查询(结果集一行多列或者多行多列)  #案例1.查询员工编号最小并且工资最高的员工信息.引入  select min(employee_id)  from employees;  =================  select max(salary)  from employees;

  select *  from employees  where employee_id = (   select min(employee_id)   from employees  )  and salary = (   select max(salary)   from employees       );

  这种查询结果使用虚拟字段，单行操作符必须一致可以使用。查出来与上面同样的效果。  select *  from employees  where (employee_id,salary)=(   select min(employee_id),   max(salary)   from employees         );

  #二.select子查询  #仅仅支持标量子查询，结果是一行一列  #案例1.查询每个部门的员工个数  select d.*,(select count(*) from employees)  from departments d; 

  添加条件  select d.*,(select count(*)  from employees e  where e.department_id=d.department_id  ) as 个数  from departments d; 

  #案例2.查询员工号=102的部门名。  select department_name  from departments;  ==============  select employee_id  from employees  where employee_id = 102;

  select employee_id,  (   select department_name   from departments d   where e.department_id=d.department_id  )  from employees e  where employee_id=102;

  #三.from 后面  注意:将子查询结果充当一张表，要求必须起别名  #案例:查询每个部门的平均工资等级。  select round(avg(salary),2),department_id  from employees  group by department_id;

  select e.平均工资,j.grade_level  from job_grades as j  ,(  select round(avg(salary),2) as 平均工资,department_id   from employees   group by department_id  ) as e  where e.平均工资 between j.lowest_sal and j.highest_sal;

  #1999语法，老师答案  select e.*,j.grade_level  from (  　　select round(avg(salary),2) as 平均工资,department_id  　　from employees  　　group by department_id  ) as e  inner join job_grades j  on e.平均工资 between j.lowest_sal and j.highest_sal;

  #四.exists后面(相关子查询)  语法:exists(完整的查询语句)  备注:完整的查询语句可以是一行一列，可以使一行多列  注意:先走外查询，然后根据某个字段的值再去过滤  exists 判断(布尔类型)值存不存在,结果只有两种:1有，0没有  #引入  select exists(select employee_id from employees);

  查询工资3w的员工信息  select exists(select * from employees where salary=30000);

  #案例引入.查询员工名和部门名  #查员工名与部门编号  select first_name,department_id  from employees  where department_id;

  #查部门名  select department_name  from departments;

  #查员工名与部门名  select e.first_name,d.department_name  from employees e  inner join (  select department_name,department_id   from departments  ) as d  on e.department_id=d.department_id;

  #案例1..查有员工的部门名  select department_name  from departments d  where exists(   select *   from employees e   where d.department_id=e.department_id      );

  使用in代替exists，同样是上面的结果  select department_name  from departments d  where d.department_id in(   select department_id   from employees       );

  #案例2.查询没有女朋友的男神信息  #in方法  select *  from boys bo  where bo.id not in(   select boyfriend_id   from beauty be  );  ===============  #exists方法  select *  from boys bo  where not exists(   select boyfriend_id   from beauty be   where bo.id=be.boyfriend_id  );

  进阶9:联合查询  union 联合 合并:将多条查询语句的结果合并成一个结果。  语法:   查询语句1   union   查询语句2   union   ...    应用场景:  要查询的结果来自于多个表，且多个表没有直接的连接关系，  但查询信息一致时。  网页搜索内容，内容从不同的表中检索联合起来返回给用户。    特点:  1.要求多条查询语句的查询列数是一致的。  2.要求多条查询语句的查询的每一列的类型和顺序最好一致。  3.使用union关键字默认去重，如果使用union all全部展示，包含重复项