目录
二 SELECT 后的子查询(标量子查询 返回值只能是一行)
含义:
出现在其他语句中的select ,称为子查询或内查询
分类:
按位置 :
select 后 :标量
from 后 :表子查询
where having 后 :标量,列,行 ¥¥
exist 后(相关子查询) 表
按功能 结果集的行列数:
标量子查询(只有一行一列 ¥¥
列子查询 (一列多行 ¥¥
行子查询 (一行多列
表子查询 (一般多行多列
where 后的子查询
# where 或 having 后 用的多
1.标量(单行 单行操作符 比较运算符
2. 列 (多行 多行操作符 IN ANY ALL
标量查询
案例 :谁的工资比abel 高、
SELECT `salary`
FROM `employees`
WHERE `last_name`='Abel'
SELECT *
FROM `employees`
WHERE salary>(
SELECT `salary`
FROM `employees`
WHERE `last_name`='Abel'
);
列子查询(多行子查询)
知识
in(列表值) 等于列表中任意一个
IN(10,20,30) 之前这样用,现在括号里面是个查询语句
ANY/SOME (min)
ALL (max)
案例:返回lovation id是1400和1700部门中所有员工姓名
SELECT DISTINCT `department_id`
FROM `departments`
WHERE `location_id` IN(1400,1700);
SELECT `last_name`
FROM `employees`
WHERE `department_id` IN(
SELECT DISTINCT `department_id`
FROM `departments`
WHERE `location_id` IN(1400,1700)
);
案例: 返回其他工种中 比“It——prog”部门任意工资第的员工编号和姓名
SELECT salary
FROM `employees`
WHERE `job_id`='IT_PROG';
SELECT `last_name` ,`salary`,`department_id`
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 `employees`
WHERE employee_id=(
SELECT MIN(`employee_id`)
FROM `employees`
)
AND
salary=(
SELECT MAX(`salary`)
FROM `employees`
);
二 SELECT 后的子查询(标量子查询 返回值只能是一行)
案例:查询每个部门的员工个数,同时显示部门所有信息
SELECT d.*,(
SELECT COUNT(*)
FROM `employees` e
WHERE e.`department_id`=d.`department_id`
)个数
FROM `departments` d;
案例:员工号等于102 的部门名
连接查询
SELECT `department_name`
FROM `employees` e
INNER JOIN `departments` d
ON e.`department_id`=d.`department_id`
AND e.`employee_id`=102;
三 from后面(表必须起别名)
案例 每个部门的平均工资的工资等级
SELECT *,j.`grade_level`
FROM(
SELECT AVG(`salary`) av,`department_id`
FROM `employees`
GROUP BY department_id
) ag
INNER JOIN `job_grades` j
ON ag.av BETWEEN j.`lowest_sal` AND j.`highest_sal`;
四 exists 后的子查询 (相关子查询)
/*
语法 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`
);
案 查询没有女朋友的男神信息
SELECT *
FROM `boys`
WHERE id NOT IN(
SELECT `boyfriend_id`
FROM `beauty`
);