依兰县建设局网站,白云网站制作,wordpress 支持代码高亮的插件,玉泉路做网站三、MySQL子查询学习笔记
7#xff1a;子查询
含义#xff1a; 一条查询语句中又嵌套了另一条完整的select语句#xff0c;其中被嵌套的select语句#xff0c;称为子查询或内查询#xff1b;在外面的查询语句#xff0c;称为主查询或外查询 分类#xff1a; 一、按子查…三、MySQL子查询学习笔记
7子查询
含义 一条查询语句中又嵌套了另一条完整的select语句其中被嵌套的select语句称为子查询或内查询在外面的查询语句称为主查询或外查询 分类 一、按子查询出现的位置
1select后面 仅支持标量子查询
2from后面 支持表子查询
3where或having后面 标量子查询1行1列 列子查询n行1列 行子查询1行n列
4exists后面相关子查询: 表子查询
二、按结果集的行列数不同
标量子查询单行子查询结果集为1行1列列子查询多行子查询结果集为n行1列行子查询结果集为1行n列表子查询结果集为n行n列
特点
1、子查询都放在小括号内 2、子查询可以放在from后面、select后面、where后面、having后面但一般放在条件的右侧 3、子查询优先于主查询执行主查询使用了子查询的执行结果 4、子查询根据查询结果的行数不同分为以下两类
① 单行子查询结果集只有一行 一般搭配单行操作符使用 非法使用子查询的情况 a、子查询的结果为一组值 b、子查询的结果为空
② 多行子查询结果集有多行 一般搭配多行操作符使用any、all、in、not in in 属于子查询结果中的任意一个就行 any和all往往可以用其他查询代替
一、where 或 having后面可以加一下三种子查询
标量子查询单行子查询1行1列列子查询多行子查询n行1列行子查询1行n列
特点
子查询放在小括号内子查询一遍放在条件的右侧 3. 标量子查询一般会搭配着单行操作符来使用 列子查询一般搭配着多行操作符来使用in、any/some、all 注意 all 是“任意”是与所有值比较 例如小于 any() 等价于 小于 min() any 是“任一个” 是与其中的某个值比较满足即可。 例如小于 any() 等价于 小于 max()子查询的执行优先于主查询执行因为 主查询的条件用到了子查询的结果
1.标量子查询
# 1.标量子查询一行一列# 【案例1】查询谁的工资比Abel高
# 1查询Abel的工资
SELECT salary
FROM employees
WHERE last_name Abel;# 2) 查询员工的信息满足 salary 1)的结果
SELECT *
FROM employees
WHERE salary (SELECT salaryFROM employeesWHERE last_name Abel
);# 【案例2】返回job_id工种号 与 141号员工相同salary 比 143 号 员工多的员工姓名job_id和工资
# 1查询 员工号 为141 的员工的job_id
SELECT job_id
FROM employees
WHERE employee_id 141;# 2查询 员工号为143的员工的工资
SELECT salary
FROM employees
WHERE employee_id 143;# 3)查询员工的姓名job_id和工资要求job_id 1并且salary 2
SELECT last_name , job_id ,salary
FROM employees
WHERE job_id (SELECT job_id FROM employeesWHERE employee_id 141
)AND salary (SELECT salaryFROM employeesWHERE employee_id 143
) # 【案例3】返回公司工资最少的员工的last_name ,job_id 和salary
# 1) 查询公司的最低工资
SELECT MIN(salary)
FROM employees;# 2) 查询 last_name , job_id和salary要求salary 1)
SELECT last_name, job_id,salary
FROM employees
WHERE salary (SELECT MIN(salary)FROM employees
)# 【案例4】 查询最低工资大于 50号部门最低工资 的部门id 和其最低工资# 1) 查询50号部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id 50;# 2) 查询每个部门的最低工资
SELECT department_id , min(salary)
FROM employees
GROUP BY department_id;# 3) 在2的基础上筛选满足min(salary)1) 查询department_id 50 的部门号 和 最低工资
SELECT department_id , MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) (SELECT MIN(salary) FROM employeesWHERE department_id 50
)2.列子查询
# 2.列子查询n行1列
# 【案例1】 返回location_id是1400或1700 的部门中的所有员工姓名# 1查询location_id 是1400或1700的部门编号多行1列
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700);# 2查询员工姓名要求部门号是1列表中的某一个
SELECT last_name
FROM employees
WHERE department_id in(SELECT DISTINCT department_idFROM departmentsWHERE location_id IN(1400,1700)
)# 【案例2】 返回其他工种中比 job_id为“IT_PROG” 工种的所有员工的工资 中 任一 低的员工的员工号、姓名、job_id和salary# 1) 查询job_id为“IT_PROG” 部门的所有员工的工资
SELECT DISTINCT salary
FROM employees
WHERE job_id IT_PROG;# 2) 查询员工的员工号、姓名、job_id和salary要求salary 1中的任一比其中某一个小就行一个结果
SELECT last_name ,employee_id , job_id , salary
FROM employees
WHERE salary ANY(SELECT DISTINCT salaryFROM employeesWHERE job_id IT_PROG
) AND job_id IT_PROG;# 2) 或者 小于其中任何一个就行 等价于 小于最大值
SELECT last_name ,employee_id , job_id , salary
FROM employees
WHERE salary (SELECT max(salary)FROM employeesWHERE job_id IT_PROG
) AND job_id IT_PROG;# 【案例3】 返回其他部门中比job_id为‘IT_PROG’部门所有工资 ”都要“ 低的员工 的员工号、姓名、job_id 和salary
SELECT last_name ,employee_id , job_id , salary
FROM employees
WHERE salary ALL(SELECT DISTINCT salaryFROM employeesWHERE job_id IT_PROG
) AND job_id IT_PROG;# 或者 小于所有任意的 等价于 大于最小值
SELECT last_name ,employee_id , job_id , salary
FROM employees
WHERE salary (SELECT MIN(salary)FROM employeesWHERE job_id IT_PROG
) AND job_id IT_PROG;
3.行子查询结果集为1行多列 或 多行多列 # 3.行子查询结果集为1行多列 或 多行多列# 【案例1】 查询员工编号最小并且工资最高的员工信息# 先用标量子查询来实现
# 1) 查询最小的员工编号
SELECT MIN(employee_id)
FROM employees;# 2) 查询最高的工资
SELECT MAX(salary)
FROM employees;# 3) 查询员工的信息
SELECT *
FROM employees
WHERE employee_id (SELECT MIN(employee_id)FROM employees
)AND salary (SELECT MAX(salary)FROM employees
)# 再用行子查询来实现
SELECT *
FROM employees
WHERE (employee_id,salary) (SELECT MIN(employee_id),MAX(salary)FROM employees
)
二、select后面仅仅支持标量子查询
二、select后面仅仅支持标量子查询# 【案例1】 查询每个部门的员工个数
SELECT d.* , (SELECT COUNT(*) FROM employees eWHERE e.department_id d.department_id
) 个数
FROM departments d# 【案例2】 查询员工号 102 的部门名
SELECT (SELECT department_nameFROM departments dINNER JOIN employees eON e.department_id d.department_idWHERE e.employee_id 102
) 部门名;三、from后面 注意将子查询结果充当一张表时必须要起别名否则会报错
# 三、from后面
# 注意将子查询结果充当一张表时必须要起别名否则会报错
# 【案例1】 查询每个部门的平均工资的工资等级# 1) 查询每个部门的平均工资
SELECT AVG(salary) , department_id
FROM employees
GROUP BY department_idSELECT * FROM job_grades;# 2) 连接1的结果集 和 job_grades表筛选条件为 平均工资 between lowest_sal and highest_sal
SELECT ag_dep.* , g.grade_level
FROM (SELECT AVG(salary) ag , department_idFROM employeesGROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;四、exists后面相关子查询:只关心查询的东西是否存在存在返回1不存在返回0 语法EXISTS(完整的查询语句) 结果1或0
# 四、exists后面相关子查询:只关心查询的东西是否存在存在返回1不存在返回0
# 语法
# EXISTS(完整的查询语句)
# 结果
# 1或0
SELECT EXISTS (SELECT employee_id FROM employees);SELECT EXISTS (SELECT employee_id FROM employees WHERE salary 300000);# 【案例1】 查询有员工的部门名
# 方法1 , exists
SELECT department_name
FROM departments d
WHERE EXISTS(SELECT *FROM employees e , departments dWHERE d.department_id e.department_id
);# 方法2 , in
SELECT department_name
FROM departments d
WHERE d.department_id IN(SELECT department_id FROM employees
);# 【案例2】 查询没有女朋友的男神信息
USE girls;
# 方法一not in
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN (SELECT boyfriend_idFROM beauty
);# 方法二 not EXISTS
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(SELECT boyfriend_idFROM beauty bWHERE bo.id b.boyfriend_id
);子查询练习部分
# 子查询练习部分# 1、查询和Zlotkey相同部门的员工姓名和工资
use myemployees;
# 1查询Zlotkey的部门id
SELECT department_id
FROM employees
WHERE last_name Zlotkey;# 2) 查询部门号等于 1的查询结果的 员工姓名和工资
SELECT last_name , salary,department_id
FROM employees
WHERE department_id (SELECT department_idFROM employeesWHERE last_name Zlotkey
);# 2、查询工资比公司平均工资高的员工的 员工号、姓名和工资# 1) 查询平均工资
SELECT AVG(salary)
FROM employees;# 2) 查询工资高于 1的查询结果的 员工的 员工号、姓名和工资
SELECT employee_id , last_name , salary
FROM employees
WHERE salary (SELECT AVG(salary)FROM employees
);# 3、查询各部门中工资比本部门平均工资高的员工 的 员工号、姓名和工资# 1) 查询每个部门的平均工资
SELECT department_id , AVG(salary)
FROM employees
GROUP BY department_id;# 2) 把1)查询的结果 与 employees表连接起来进行筛选
SELECT employee_id , last_name , salary,e.department_id
FROM employees e
INNER JOIN (SELECT AVG(salary) ag, department_idFROM employees GROUP BY department_id
) ag_dep
ON e.department_id ag_dep.department_id
WHERE salary ag_dep.ag# 4、查询和 姓名中包含字母 u 的员工 在相同部门的员工的员工号和姓名# 1) 查询姓名中包含字母 u 的员工的部门
SELECT DISTINCT last_name,department_id
FROM employees e
WHERE last_name LIKE %u%;# 2)查询 部门号 1中任意一个的 员工的员工号和姓名
SELECT employee_id ,last_name ,department_id
FROM employees
WHERE department_id IN(SELECT DISTINCT department_idFROM employeesWHERE last_name LIKE %u%
)# 5、查询在部门的location_id 为 1700的部门工作的员工的员工号# 1) 查询location_id为1700的部门号
SELECT DISTINCT department_id
FROM departments
WHERE location_id 1700;# 2) 查询部门号 1) 中任意一个部门号的 员工号
SELECT employee_id
FROM employees
WHERE department_id ANY(SELECT DISTINCT department_idFROM departmentsWHERE location_id 1700
);# 6、查询管理者是K_ing 的员工编号# 1) 查询姓名为King的员工编号
SELECT employee_id
FROM employees
WHERE last_name K_ing;# 2) 查询哪个员工的manger_id 1的查询结果
SELECT last_name ,salary
FROM employees
WHERE manager_id IN (SELECT employee_idFROM employeesWHERE last_name K_ing
);# 7、查询工资最高的员工的姓名要求first_name 和 last_name显示为一列列名为 姓.名# 1) 查询最高工资
SELECT MAX(salary)
FROM employees;# 2) 查询工资 1)的姓.名
SELECT CONCAT(first_name , last_name) 姓.名
FROM employees
WHERE salary (SELECT MAX(salary)FROM employees);
