子查询
子查询或内查询分类按结果集的行列数不同按子查询出现的位置where或having后标量子查询列子查询(多行子查询)行子查询放在select后:仅仅支持标量子查询放在from后exists后:相关子查询例题:子查询或内查询
含义:出现在其他语句中的select 语句
外部的查询语句:成为主查询或外查询
分类
按结果集的行列数不同
按子查询出现的位置
where或having后
特点:
子查询放在小括号内子查询一般放在条件的右侧标量子查询:一般搭配着单行操作符使用
< > >= <= = <>列子查询:一般搭配多行操作符使用
in,any/some,all
标量子查询
例1:查询工资比Abel高的员工
SELECT last_name,salary
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name=‘Abel’
);
例2:返回job_id与141号员工相比,salary比143号员工多的员工姓名,job_id和工资
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
);
列子查询(多行子查询)
例如:#返回其他部门中比job_id为’it-prog‘部门任意工资低的员工的员工号、姓名、job_id以及salary
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 *
FROM employees
WHERE (employee_id,salary)=(
SELECT MIN(employee_id),MAX(salary)
FROM employees
);
放在select后:仅仅支持标量子查询
放在from后
将子查询充当一张表,必须起别名
exists后:相关子查询
语法:
exists(完整的查询语句)
结果:1或0
例如:查询没有女朋友的男神信息
方法1:采用exists
SELECT boys.*
FROM boys
WHERE NOT EXISTS(
SELECT *
FROM boys,beauty
WHERE boys.
id
=beauty.boyfriend_id
);
方法2:采用in
SELECT boys.*
FROM boys
WHERE boys.
id
NOT IN(SELECT beauty.
boyfriend_id
FROM beauty
) ;
例题:
#1.查询和zlotkey相同部门的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE
department_id
=(SELECT
department_id
FROM employees
WHERE last_name=‘Zlotkey’
);
#2.查询工资比公司平均工资高的员工的员工号,姓名和工资
SELECT employee_id,last_name,salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
#3. 查询各部门中工资比本部门平均工资高的员工的员工号,姓名和工资
SELECT employee_id,last_name,salary,平均工资
FROM employees e
INNER JOIN(
SELECT AVG(salary) 平均工资,
department_id
FROM employees
GROUP BY department_id
) avg_sal
ON e.
department_id
=avg_sal.department_id
WHERE salary> 平均工资
;
#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT employee_id,last_name
FROM employees
WHERE department_id IN(
SELECT department_id
FROM employees
WHERE last_name LIKE ‘%u%’
);
#5.查询在部门location_id为1700的部门工作的员工的员工号
SELECT employee_id,department_id
FROM employees
WHERE department_id IN (
SELECT
department_id
FROM
departments
WHERE location_id=1700
);
#6.查询管理者是King的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE manager_id IN (
SELECT employee_id
FROM employees
WHERE last_name=‘K_ing’
);
7.查询工资最高的员工姓名,按要求first_name和last_name显示为一列,列名为姓名
SELECT CONCAT(last_name,first_name) 姓名,employee_id
FROM employees
WHERE salary= (
SELECT MAX(salary)
FROM employees
);
“一个能够升起月亮的身体,必然驮住了无数次的日落”
小徐加油!
昨天失眠了无误
-----.2.2