MySQL学习--子查询

多行操作符

	in/not in 等于列表中的任意一个
	any/some 和子查询返回的某一个值比较
	all 和子查询返回的所有值比较

行子查询

（结果集一行多列或多行多列）
	SELECT *
	FROM employees 
	WHERE (employee_id,salary) = (
		SELECT MIN(employee_id),MAX(salary)
		FROM employees
	);

放在select后面的查询

查询每个部门的员工个数
	SELECT d.*,(
		SELECT COUNT(*)
		FROM employees e
		WHERE e.department_id =d.department_id
	) 个数
	FROM departments d;

放在from后面的查询

子查询放在from后面–要求子查询结果充当一张表，要求必须起别名

查询每个部门的平均工资的工资等级
	SELECT ag_dep.*,g.grade_level
	FROM(
		SELECT AVG(salary),ag.department_id
		FROM employees
		GROUP BY department_id
	) ag_dep
	INNER JOIN job_grade g
	ON ag_dep.ag BETWEEN lowest_sal AND hight_sal;

放在exists后面的查询

xists查询结果是否存在exists（完整的查询结果）结果为1或0
	查询有员工的部门名
	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 d.department_id IN(
	SELECT department_id
	FROM employees
	);