MySQL--子查询

1.含义

出现在其他语句中的SELECT语句，成为子查询或内查询

外部的查询语句，称为主查询或外查询

2.分类

1.按子查询出现的位置：

1.SELECT后面：

仅仅支持标量子查询

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

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

2.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_grades g on age_dep.ag BETWEEN lowest_sal AND highest_sal;

3.WHERE或HAVING后面：★

标量子查询(单行)√

SELECT * FROM employees WHERE salary>(SELECT salary FROM employees WHERE last_name = 'abel');

SELECT last_name,job_id,salary FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 141) AND salary > (SELECT salary FROM employees WHERE employee_id = 143);

SELECT last_name,job_id,salary FROM employees WHERE salary = (SELECT MIN(salary) FROM employees);

SELECT MIN(salary),department_id FROM employees GROUP BY department_id HAVING MIN(salary)>(SELECT MIN(salary) FROM employees WHERE department_id = 50);

SELECT MIN(salary),department_id FROM employees GROUP BY department_id HAVING MIN(salary)>(SELECT salary FROM employees WHERE department_id = 50)(这个是报错的，业务子查询表示一行一列的)

SELECT MIN(salary),department_id FROM employees GROUP BY department_id HAVING MIN(salary)>(SELECT salary FROM employees WHERE department_id = 250)(这个是部门是不存在的)

列子查询(多行)√

①返回多行

②使用多行比较操作符

SELECT last_name FROM employees WHERE department_id IN(SELECT DISTINCT department_id FROM departments WHERE location_id IN(1400,1700));（DISTINCT去重）

SELECT last_name,employee_id,job_id,salary FROM employees WHERE salary<ANY(SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG';或

SELECT last_name,employee_id,job_id,salary FROM employees WHERE salary < ANY(SELECT DISTINCT MAN(salary) FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG';

SELECT last_name,employee_id,job_id,salary FROM employees WHERE salary < ALL(SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id<>'IT_PROG';或

SELECT last_name,employee_id,job_id,salary FROM employees WHERE salary < (SELECT DISTINCT MIN(salary) FROM employees WHERE job_id = 'IT_PROG') AND job_id<>'IT_PROG';

行子查询(结果集一行多列或多行多列)

SELECT * FROM employees WHERE (employee_id,salary)=(SELECT MIN(employee_id),MAX(salary) FROM employees);

5.特点

①子查询放在小括号内

②子查询一般放在条件的右侧

③标量子查询，一般搭配着单行操作符使用

> < >= <= = <>

④列子查询，一般搭配着多行操作符使用

IN、ANY/SOME、ALL

⑤子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

4.EXISTS后面(相关子查询)

表子查询

语法：EXISTS(完整的查询语句)

结果：1或0

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 departmen_id FROM employees);

SELECT bo.* FROM boys bo WHERE bo.id NOT IN(SELECT boyfriend_id FROM beauty);或者

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

2.按结果集的行列数不同

1.标量子查询(结果集只有一行一列)

2.列子查询(结果集只有一列多行)

3.行子查询(结果集只有一行多列)

4.表子查询(结果集一般为多行多列)