MySql数据库基本操作七（select子查询）

SELECT子查询

  仅仅支持标量子查询
  案例：查询每个部门的员工个数

SELECT d.*,(
		SELECT COUNT(*)
		FROM employees e
		WHERE e.department_id=d.department_id
) 个数
FROM departments d;

  案例：查询员工号=102的部门名

SELECT (
		SELECT department_name
		FROM departments d
		INNER JOIN employees e
		ON d.department_id=e.department_id
		WHERE e.employee_id=102
)部门名;

FROM子查询

    将子查询结果充当一张表，要求起别名
  案例：查询每个部门的品骏工资的工资等级
  查询每个部门的平均工资

SELECT AVG(salary), department_id
FROM employees
GROUP BY department_id;

SELECT * FROM job_grades;

  2连接1的结果集和job_grades表，筛选条件平均工资

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（相关子查询）

  语法：
    EXISTS(完整的查询语句)
    结果：
      1或者0

		SELECT EXISTS(
		SELECT employee_id 
		FROM employees 
		WHERE salary=300000
);

  案例：查询有员工的部门名

SELECT department_name
FROM departments d
WHERE EXISTS(
		SELECT *
		FROM employees e
		WHERE d.department_id=e.department_id
);

SELECT department_name
FROM departments d
WHERE d.department_id IN(
		SELECT department_id
		FROM employees 
);