数据库MySQL-子查询
含义:
出现在其他语句中的select语句,称为子查询或内查询
外部的查询语句,称为主查询或外查询
分类:
按子查询出现的位置:
select后边
仅仅支持标量子查询
from后面
表子查询
where或having后面(*)
标量子查询
列子查询
行子查询
exists后面(相关子查询)
表子查询
按结果集的行列数不同:
标量子查询(结果集只有一行一列)
列子查询(结果集中只有一列多行)
行子查询(结果集有一行多列)
表子查询(结果集一般为多行多列)
*/
一、where或having后面
*1、标量子查询(单行子查询)
2、列子查询(多行子查询)
3、行子查询(多列多行)
特点:1、子查询放在小括号内
2、子查询一般放在条件的右侧
3、标量子查询,一般搭配着单行操作符使用:> < >= <= = <>.
列子查询,一般搭配着多行操作符使用:in、any/some、all.
4、子查询的执行优先于主查询的执行,主查询的条件用到了子查询的结果。
*/
SELECT salary
FROM employees
WHERE last_name = 'Abel';
SELECT *
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);
SELECT job_id
FROM employees
WHERE `employee_id` = 141;
SELECT salary
FROM employees
WHERE `employee_id` = 143;
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 MIN(salary)
FROM employees;
SELECT `last_name`,`job_id`,`salary`
FROM `employees`
WHERE `salary` = (
SELECT MIN(salary)
FROM employees
);
SELECT MIN(salary)
FROM `employees`
WHERE `department_id` = 50;
SELECT `department_id`,MIN(salary)
FROM `employees`
GROUP BY `department_id`
HAVING MIN(salary) > (
SELECT MIN(salary)
FROM `employees`
WHERE `department_id` = 50
);
SELECT department_id
FROM `departments`
WHERE location_id IN(1400,1700);
SELECT `last_name`
FROM `employees`
WHERE `department_id` IN(
SELECT department_id
FROM `departments`
WHERE location_id IN(1400,1700)
);
SELECT salary
FROM `employees`
WHERE `job_id` = 'IT_PROG';
SELECT `employee_id`,`last_name`,`job_id`,`salary`
FROM `employees`
WHERE `salary` < ANY(
SELECT salary
FROM `employees`
WHERE `job_id` = 'IT_PROG'
)
AND job_id<>'IT_PROG';
SELECT `employee_id`,`last_name`,`job_id`,`salary`
FROM `employees`
WHERE `salary` < ALL(
SELECT 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
);
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 d.*,(
SELECT COUNT(*)
FROM employees e
WHERE e.`department_id` = d.`department_id`
) 个数
FROM `departments` d;
SELECT `department_name`
FROM `employees` e
INNER JOIN `departments` d
ON e.`department_id` = d.`department_id`
WHERE e.`employee_id` = 102;
SELECT AVG(salary),`department_id`
FROM `employees`
GROUP BY `department_id`
SELECT ag_dep.*,g.job_grade
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;
SELECT EXISTS(SELECT employee_id FROM employees);
SELECT `department_name`
FROM `departments` d
WHERE EXISTS(
SELECT *
FROM `employees` e
WHERE e.`department_id` = d.`department_id`
)
SELECT DISTINCT `department_name`
FROM `departments` d
LEFT JOIN `employees` e
ON e.`department_id` = d.`department_id`
WHERE e.`employee_id` IS NOT NULL;
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.`id`
);