select后面的子查询

#二、放在select后面
#案例：查询每个部门的员工个数
SELECT 
  d.*,
  (SELECT 
    COUNT(*) 
  FROM
    `employees` e
    WHERE e.`department_id`=d.`department_id`)  个数
FROM
  `departments` d;
  
  #案例2:查询员工号=102的部门名
  #（1）查询员工号=102的部门号
  SELECT `department_id`
  FROM `employees`
  WHERE `employee_id`=102;
  #查询部门号为(1)的部门名
  SELECT `department_name`
  FROM `departments`
  WHERE `department_id`=(
 SELECT `department_id`
  FROM `employees`
  WHERE `employee_id`=102  
  );
  
  #正解
  SELECT (
          SELECT `department_name`
          FROM `departments` d
          INNER JOIN `employees` e
          ON d.`department_id`=e.`department_id`
          WHERE  e.`employee_id`=102
  
  ) 部门名;
#注意：select语句后面只能是一行一列，不能多行多列，会报语法错误。  
    

#二、放在select后面
#案例：查询每个部门的员工个数
SELECT 
  d.*,
  (SELECT 
    COUNT(*) 
  FROM
    `employees` e
    WHERE e.`department_id`=d.`department_id`)  个数
FROM
  `departments` d;
  
  #案例2:查询员工号=102的部门名
  #（1）查询员工号=102的部门号
  SELECT `department_id`
  FROM `employees`
  WHERE `employee_id`=102;
  #查询部门号为(1)的部门名
  SELECT `department_name`
  FROM `departments`
  WHERE `department_id`=(
 SELECT `department_id`
  FROM `employees`
  WHERE `employee_id`=102  
  );
  
  #正解
  SELECT (
          SELECT `department_name`
          FROM `departments` d
          INNER JOIN `employees` e
          ON d.`department_id`=e.`department_id`
          WHERE  e.`employee_id`=102
  
  ) 部门名;
#注意：select语句后面只能是一行一列，不能多行多列，会报语法错误。  
  
#三、from后面的子查询
#将子查询结果充当一张表，要求必须起别名。
#案例：查询每个部门的平均工资的工资等级
#(1)查询每个部门的平均工资
SELECT AVG(`salary`),`department_id`
FROM `employees`
GROUP BY `department_id`;
#(2)连接（1）的结果集和job_grades表，筛选条件平均工资between lowest_sal and highest_sal

SELECT ag_dep.*,g.`grade_level`
FROM (
      SELECT AVG(`salary`) ag,`department_id`
      FROM `employees`
      GROUP BY `department_id`
) ag_dep
 INNER JOIN job_grades g
ON  ag_dep.ag BETWEEN lowest_sal AND highest_sal ;

#exists后面的子查询(相关子查询),只关心有没有值
/*
语法：
select(完整的查询语句);
结果：
1或0
*/
SELECT EXISTS(SELECT `employee_id` FROM `employees`);
SELECT EXISTS(SELECT `employee_id` FROM `employees` WHERE `salary`=30000);
#案例1：查询有员工的部门名
SELECT  `department_name`
FROM `departments` d
WHERE EXISTS(
SELECT *
FROM `employees` e
WHERE  d.`department_id`=e.`department_id`
);
#使用in的方式
SELECT `department_name`
FROM `departments` d
WHERE `department_id`
IN(
  SELECT `department_id`
  FROM `employees` e

);

#案例2：查询没有女朋友的男神信息
#in
SELECT bo.*
FROM `boys` bo
WHERE bo.id NOT IN(
     SELECT b.`boyfriend_id`
     FROM `beauty` b
);

#exists
SELECT bo.*
FROM `boys` bo
WHERE NOT EXISTS(
  SELECT b.`boyfriend_id`
     FROM `beauty` b
     WHERE bo.`id`=b.`boyfriend_id`
);