mysql 满足条件得交差获取交差部分 mysql求差值

转载

jkfox 2024-06-25 20:37:13

文章标签 sql 子查询 IT 操作符 文章分类 MySQL 数据库

1.子查询规范

2.子查询分类

2.1 单行子查询

2.2 多行子查询

临时表

2.3 相关子查询

EXISTS 与 NOT EXISTS关键字

子查询可以理解为嵌套

问题：想查出表里工资比 ‘ Abel ’ 工资高的人的姓名和工资

方法一：先查出Abel 的工资是11000，再查出工资大于11000的人

方法二：自连接（并不适用于所有表）

mysql 满足条件得交差获取交差部分 mysql求差值_sql

注意，如果对应的数据库写错，会带来错误的效果：

mysql 满足条件得交差获取交差部分 mysql求差值_子查询_02

一定要注意对应，将e表作为一个参考表，所有的数据都是从e1里挑选的，只需要借用一下e表中的Abel ，所以SELECT中的数据都是e1的数据，但是AND后的拣选条件中，是对照表e的条件

方式三：子查询

在方式一的基础上，把第一个查询嵌套到第二个查询的WHERE里

mysql 满足条件得交差获取交差部分 mysql求差值_子查询_03

↓ ↓ ↓ ↓

自连接好于子查询

1.子查询规范

外查询：也称为主查询

内查询：也称为子查询

1.子查询要包含在一个括号（）内

2. 子查询通常放在右侧

3.单行操作符对应单行子查询，多行操作符对应多行子查询

2.子查询分类

1. 按返回结果的条目数分为：单行子查询和多行子查询

单行子查询：返回的结果是单行的信息，例如上题中的某个人的工资

多行子查询：返回结果是多行的信息

2.按内查询是否被执行多次：相关子查询和不相关子查询

不相关子查询：首先计算内查询的结果，然后遍历外查询，一条条和内查询的结果进行比较

2.1 单行子查询

单行比较操作符：在主查询的 WHERE 后可以使用的连接符

操作符	含义
=	等于
>	大于
>=	大于等于
<	小于
<=	小于等于
<>	不等于

子查询编写及技巧：从里往外写，或从外往里写（废话）

单行子查询返回的字段一定和主查询里 WHERE 处等着的字段是一样的，否则就是非法使用子查询

题目1 ：返回 job_id 与141号员工相同，且工资大于143号员工的员工 last_name , job_id , salary

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
)

题目2 ：返回公司工资最少的员工的 last_name , job_id , salary

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

题目3 ：查询与141号员工的manager_id和department_id相同的其他员工的employee_id，manager_id，department_id

SELECT employee_id,manager_id,department_id
FROM employees 
WHERE  manager_id=(
	SELECT manager_id
	FROM employees
WHERE employee_id = 147
)
AND department_id=(
	SELECT department_id
	FROM employees
WHERE employee_id = 147
);
AND employee_id <> 147;-- 其他员工

SELECT employee_id,manager_id,department_id
FROM employees 
WHERE (manager_id，department_id) = (
	                                SELECT manager_id,department_id
	                                FROM employees
                                    WHERE employee_id = 141
                                    );
AND employee_id <> 147;-- 其他员工

第二种方式虽然效率高一点，但是应用场景比较局限

题目4 ,HAVING与子查询：查询最低工资大于50号部门最低工资的部门 id 和其最低工资

USE atguigudb;
SELECT department_id,MIN(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary)>(
					SELECT MIN(salary)
					FROM employees
					WHERE department_id = '50'
					);

题目5 ,CASE与子查询：查询员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800的department_id相同，则location为’Canada’，其余为’USA’。

USE atguigudb;
SELECT employee_id,last_name,CASE department_id WHEN 20 THEN 'Canada'																						                                        
                                           ELSE 'USA' END "location"
FROM employees

2.2 多行子查询

当内查询返回多于一条的记录，这时候用单行运算符就不合适了

mysql 满足条件得交差获取交差部分 mysql求差值_子查询_04

问题1： 找出员工最低工资与部门最低工资相等的员工

USE atguigudb;
SELECT last_name,job_id,salary
FROM employees 
WHERE salary in(
	        SELECT MIN(salary)
	        FROM employees
            GROUP BY department_id
                );

问题2： 找出其他 job_id 中比 job_id 为 ‘ IT_PROG ’ 部门任一工资低的员工的信息

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

问题3： 找出其他 job_id 中比 job_id 为 ‘ IT_PROG ’ 部门所有员工工资低的员工的信息

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

把上题 ANY 换成ALL即可

临时表

问题4： 查询平均工资最低的部门 id

方式一思路：将各个部门的平均工资形成一张临时表，再去查询这个临时表里最低的工资即可。注意，临时表必须有别名

USE atguigudb;
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG (salary) = (
        SELECT MIN(avg_sal) "最低工资"
        FROM
                    (
                    SELECT AVG(salary) avg_sal
                    FROM employees
                    GROUP BY department_id
                    ) 
        AS t_avg_sal
                        );

mysql 满足条件得交差获取交差部分 mysql求差值_IT_05

方式二:

SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)<=ALL(
                        SELECT AVG( salary)
                        FROM employees
                        GROUP BY department_id
                        );

以上案例都是不相关子查询

2.3 相关子查询

首先从外查询取得某个信息，然后带入内查询，再将内查询结果返回给外查询。只要是内查询里出现外查询的表了，就是相关子查询

问题一：查询员工中工资大于本部门平均工资的员工信息

方式一：运用自相关的方法，创建两个表互相引用

首先过度一下，查询工资低于60号部门平均工资的员工

SELECT last_name,salary,department_id
FROM employees 
WHERE salary>(
            SELECT AVG( salary)
            FROM employees 
            WHERE department_id = 60
            );

再将两个表赋予不同的别名，用内表引用外表的信息，就ok了

SELECT last_name,salary,department_id
FROM employees outer -- 外表
WHERE salary>(
              SELECT AVG( salary)
              FROM employees inner -- 内表
              WHERE department_id = outer.department_id -- 外表的department_id传进来，进行内                                                                                                                                
                                                         -- 查询，然后再传出去
            );

方式二：在FROM中声明子查询

SELECT e.last_name,e.salary,e.department_id
FROM employees e,(
                SELECT department_id,AVG(salary) avg_sal
                FROM employees
                GROUP BY department_id) "t_avg_sal"
WHERE e.department_id = t_avg_sal.department_id
AND e.salary > t_avg_sal.avg_sal

问题2(在ORDER BY 中使用子查询)：查询员工信息，按照department_name排序

SELECT employee_id,salary
FROM employees e
ORDER BY(
	    SELECT department_name
	    FROM departments d
	    WHERE e.department_id = d.department_id
        ) ASC;

结论：在 SELECT , FROM , WHERE , HAVING , ORDER BY 中都可以使用子查询
GROUP BY 和 LIMIT 处不可以写子查询

问题3：若 employees 表中 employee_id 与 job_history 表中 employee_id 相同的数目不小于2(调过岗)，输出这些相同 id 的员工的 employee_id,last_name和其 job_id

SELECT employee_id,last_name,job_id
FROM employees e
WHERE 2 <= (
	        SELECT COUNT(*)
	        FROM job_history j
	        WHERE e.employee_id = j.employee_id
           );

EXISTS 与 NOT EXISTS关键字

关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。
        如果在子查询中不存在满足条件的行：
                                                                条件返回 FALSE
                                                                继续在子查询中查找
        如果在子查询中存在满足条件的行：
                                                                不在子查询中继续查找
                                                                条件返回 TRUE
NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。

可以用 WHERE IN 的都可以换成 EXIST

问题1：查询公司管理者的 employee_id，last_name，job_id，department_id信息

方式一：自连接

SELECT mgr.employee_id,mgr.last_name,mgr.job_id
DISTINCT FROM employees emp JOIN employees mgr -- 加个DISTINCT，去重
ON emp.manager_id = mgr.employee_id

方式二：子查询

SELECT employee_id,last_name,job_id
FROM employees
WHERE employee_id IN(
                    SELECT DISTINCT manager_id
                    FROM employees
                    );

方式三：使用 EXISTS

SELECT employee_id,last_name,job_id
FROM employees e1
WHERE EXISTS(
            SELECT *  -- 这里写什么都可以，返回的只是这条记录
            FROM employees e2
            WHERE e1.employee_id = e2.manager_id
            );

问题2：查询 departments 表中，不存在于 employees 表中的部门的 department_id， department_name

SELECT department_id,department_name
FROM departments d
WHERE NOT EXISTS(
                SELECT *
                FROM employees e
                WHERE e.department_id = d.department_id
                );

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。