第09章_子查询

最新推荐文章于 2024-10-02 00:27:17 发布

阿Y加油吧

最新推荐文章于 2024-10-02 00:27:17 发布

阅读量361

点赞数 8

文章标签：数据库 sql mysql

本文链接：https://blog.csdn.net/2401_84526799/article/details/139706668

版权

1. 需求分析与问题解决

1.1 实际问题

#查询工资大于Able的工资

#方式1：内连接
SELECT  e1.last_name,e1.salary
FROM employees e1,employees e2
WHERE e1.`salary`>e2.`salary`
AND  e2.`last_name`='Abel';

#方式2：子查询
SELECT  last_name,salary
FROM employees
WHERE  salary>(
               SELECT salary
               FROM employees
               WHERE last_name='Abel'  
               );

1.2 子查询的基本使用

SELECT  last_name,salary
FROM employees
WHERE  salary>(
               SELECT salary
               FROM employees
               WHERE last_name='Abel'  
               );

#2.称谓的规范：外查询（或者主查询），内查询（子查询）
/*
子查询在主查询之前一次完成
子查询的结果被主查询一次使用
注意：
子查询要放在括号内
将子查询放在条件的右侧
单行函数对应单行的子查询，多行函数对应多行子查询
*/

1.3 子查询的分类

#子查询的分类
#角度1：内容查询返回的结果条目数
#单行子查询VS多行子查询
#角度2:
#相关子查询VS不相关子查询
/*

2. 单行子查询

2.1 单行比较操作符

#单行查询的操作符：> = ！= >= <=
#子查询技巧：1.由外往里写 2.由里往外写

2.2 代码示例

题目：查询工资大于149号员工工资的员工的信息

#练习：查询工资大于149号员工的工资
SELECT  employee_id,last_name,salary
FROM  employees
WHERE salary  >(
             SELECT salary
             FROM employees
             WHERE employee_id=149
               );

#题目：返回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
              ) ;            

#返回公司工资最少的员工的last_name,job_id和salary
SELECT last_name,job_id,salary
FROM employees
WHERE  salary=(
               SELECT MIN(salary)
               FROM employees
               );

#题目：查询与141号的manager_id和department_id相同的其他员工的employee_id
#方式1：
SELECT employee_id,manager_id,department_id
FROM employees
WHERE manager_id=( 
                  SELECT manager_id
                  FROM employees
                  WHERE employee_id=141 
                  )
  AND  department_id=(SELECT department_id
                  FROM employees
                  WHERE employee_id=141 
                  )
    AND  employee_id!=141;                             
#方式2：了解
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!=141;

2.3 HAVING 中的子查询

#题目：查询最低工资大于50号部门最低工资的部门id和其最低工资

SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
               SELECT MIN(salary)
               FROM employees
               WHERE department_id=50
)
AND department_id!=50;

2.4 CASE中的子查询

#题目：显式员工的employee_id,last_name和location。
#     其中，若员工departhment_id与location_id为1800的department_id相同
#    ，则location为’Canada’，其余则为’USA’
SELECT employee_id,last_name,CASE department_id WHEN (
                                  SELECT department_id
                                  FROM departments
                                 WHERE location_id=1800) THEN 'Canada'
                                 ELSE 'USA' END   "location"
FROM employees;

2.5 子查询中的空值问题

#4.2子查询的空值问题，
SELECT last_name,job_id
FROM employees
WHERE job_id=(
             SELECT job_id
             FROM employees
             WHERE last_name='bna'


);

2.5 非法使用子查询

#2.5 非法使用子查询
 SELECT employee_id, last_name
 FROM   employees
 WHERE  salary =
                (SELECT   MIN(salary)
                 FROM     employees
                 GROUP BY department_id);

3. 多行子查询

#5.1 多行子查询的操作符：IN ANY ALL

3.1 ANY/ALL 的区别

#题目：返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的
#         员工号、姓名、job_id 以及salar                 
SELECT  employee_id,last_name,job_id,salary
FROM  employees
WHERE job_id!='IT_PROG'
AND salary < ANY(
SELECT salary
FROM employees
WHERE job_id='IT_PROG'

);

#题目:查询平均工资最低的部门id
#MSQL中聚合函数不能嵌套，将聚合函数变成一个字段
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id
HAVING  AVG(salary)=(

	SELECT MIN(avl)
        FROM(
	SELECT AVG(salary) avl
	FROM employees 
	GROUP BY department_id
	) avg_avl

 );

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

	
	SELECT AVG(salary) avl
	FROM employees 
	GROUP BY department_id
	

 );

3.3 空值问题

# 3.3 空值问
 #要考虑有没有空值，特别注意
 SELECT last_name
 FROM employees
 WHERE employee_id NOT  IN (
            SELECT manager_id
            FROM employees
            WHERE manager_id IS NOT NULL
            );

4. 相关子查询

4.1 相关子查询执行流程

如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次

4.2 代码示例

#练习：：查询员工中工资大于本部门平均工资的员工
#         的last_name,salary和其department_id 
#方式1：使用相关子查询           
SELECT  e.last_name,e.salary,e.department_id
FROM   employees e 
WHERE  e.salary > (
             SELECT  AVG(salary)
             FROM  employees   d     
               WHERE d.`department_id`=e.department_id
             ) ;           
#方式2：在from中声明子查询
SELECT  e.last_name,e.salary,e.department_id
FROM employees e ,(
         SELECT department_id,AVG(salary) avl
         FROM employees
         GROUP BY department_id        
               )  d
WHERE  e.`salary`> d.avl && e.`department_id`=d.department_id;

#练习：查询员工的id,salary,按照department_name 排序
SELECT employee_id,salary
FROM employees e
ORDER BY ( 
          SELECT department_name
          FROM departments
          WHERE  e.department_id=department_id
           );

在ORDER BY 中使用子查询

练习：查询员工的id,salary,按照department_name 排序
SELECT employee_id,salary
FROM employees e
ORDER BY ( 
          SELECT department_name
          FROM departments
          WHERE  e.department_id=department_id
           );

#
/* 除了GROUP by 之外，其他地方都可以声明子查询
SELECT ..... (存在符合函数)
FROM .... (LEFT/RIGHT)JOIN ... ON 多表的连接条件
(LEFT/RIGHT)JOIN ... ON 多表的连接条件
WHERE 多表的连接条件 AND 过滤条件不包含聚合函数
GROUP BY ....
HAVING 过滤条件包含聚合函数
ORDER BY ....(ASC,DESC)
LIMIT .....;


#题目：若employees表中employee_id与job_history表中employee_id相同的
#      数目不小于2，输出这些相同
#      id的员工的employee_id,last_name和其job_id
 SELECT  e.employee_id,e.last_name,e.job_id
 FROM employees e
 WHERE  2<=(
           SELECT COUNT(*)
            FROM  job_history d
            WHERE   d.`employee_id`=e.`employee_id`
            );

4.3 EXISTS 与 NOT EXISTS关键字

#题目：查询公司管理者的employee_id，last_name，job_id，department_id信息         
#方式1：自连接
SELECT e.employee_id,e.last_name,e.job_id,e.department_id
FROM employees e,employees d
WHERE  e.`manager_id`=d.`employee_id`;

#方式2：子查询
SELECT employee_id,last_name,job_id,department_id
FROM employees 
WHERE  employee_id IN(
                  SELECT  DISTINCT manager_id
                  FROM employees
                 ); 
#方式3：使用exists
SELECT employee_id,last_name,job_id,department_id
FROM employees e
WHERE   EXISTS (
                SELECT *
                FROM   employees d
                WHERE  e.`employee_id`=d.`manager_id`
                  );

#题目：查询departments表中，
#       不存在于employees表中的部门的department_id和department_name 
#方式1：
SELECT d.department_id,d.department_name
FROM employees e RIGHT JOIN departments d
ON  e.`department_id`=d.`department_id`
WHERE  e.`department_id` IS NULL;
#方式2：
SELECT department_id,department_name
FROM  departments d
WHERE NOT EXISTS(
                SELECT *
                FROM employees e
                WHERE e.department_id=d.`department_id`
                  );

4.4 相关更新

题目：在employees中增加一个department_name字段，数据为员工对应的部门名称

# 1）
ALTER TABLE employees
ADD(department_name VARCHAR2(14));

# 2）
UPDATE employees e
SET department_name =	(SELECT department_name FROM departments d
WHERE	e.department_id = d.department_id);

4.4 相关删除

题目：删除表employees中，其与emp_history表皆有的数据

DELETE FROM employees e
WHERE employee_id in
(SELECT employee_id
FROM	emp_history
WHERE	employee_id = e.employee_id);

课后练习题

#第09章 子查询练习题1
【题目】
SELECT * FROM employees;
SELECT * FROM  locations;
SELECT * FROM jobs	;
SELECT * FROM countries;
#1.查询和Zlotkey相同部门的员工姓名和工资
    SELECT  employee_id,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.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary
       SELECT last_name,job_id,salary
       FROM  employees
       WHERE  salary > ALL (
                       SELECT salary
                       FROM employees
                       WHERE  job_id='SA_MAN'
                       );
 #4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
     SELECT employee_id,last_name,department_id
     FROM   employees e
     WHERE  last_name LIKE '%u%' && department_id IN (
                                       SELECT department_id
                                       FROM   employees d
                                        WHERE  e.`department_id`=d.`department_id`
                                        
                                                               
                                   );
     
#5.查询在部门的location_id为1700的部门工作的员工的员工号
        SELECT  employee_id
        FROM employees
        WHERE  department_id IN (
                             SELECT department_id
                             FROM departments
                             WHERE  location_id=1700
        
                             );
        
#6.查询管理者是King的员工姓名和工资
   SELECT e.last_name,e.salary
   FROM employees e
   WHERE e.`manager_id`	=(
                     SELECT d.`employee_id`
                     FROM employees d
                     WHERE  e.`manager_id`=d.`employee_id`&& d.`last_name`='King'
                      )    && e.`last_name`!='King';
#7.查询工资最低的员工信息: last_name, salary
           SELECT  last_name,salary
           FROM employees
           WHERE  salary=(
                         SELECT MIN(salary)
                         FROM employees
                       
                          );
                
 #8.查询平均工资最低的部门信息
          SELECT  d.department_id,d.department_name
          FROM    departments d,employees e  
          WHERE   e.`department_id`=d.`department_id` 
          GROUP  BY department_id
          HAVING  AVG(e.`salary`)   =(
                                 SELECT MIN(val)
                                  FROM (
                                 SELECT department_id ,AVG(salary) val
                                 FROM employees
                                 GROUP BY department_id
                                 )  md
                                  )   ;
                                  
    #方法2：
    SELECT  *
    FROM departments
    WHERE department_id=(
    
          SELECT  department_id
          FROM    employees   
          GROUP  BY department_id
          HAVING  AVG(salary)   =(
                                 SELECT MIN(val)
                                  FROM (
                                 SELECT department_id ,AVG(salary) val
                                 FROM employees
                                 GROUP BY department_id
                                 )  md
                                  )            
                        ) ;
  #方式3：
   SELECT  d.*
  FROM  departments d,(
   SELECT  department_id,AVG(salary) val
   FROM  employees
   GROUP BY department_id
   ORDER BY  val ASC
   LIMIT  0,1
       )  md 
   WHERE d.`department_id`=md.department_id;                         
                                  
#9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）
       SELECT  department_id,AVG(salary)
       FROM  employees
       GROUP BY department_id
       HAVING  AVG(salary)=(
                           SELECT  MIN(val)
                          FROM(
                          SELECT AVG(salary)  val
                          FROM employees 
                          GROUP BY  department_id
                          ) md
                     );
  #方式2：
  SELECT  d.*,(SELECT  AVG(salary) FROM employees WHERE department_id= d.`department_id`)
  FROM  departments d,( 
                      SELECT   department_id,AVG(salary) val
                      FROM     employees
                      GROUP BY department_id
                      ORDER BY  val
                      LIMIT 0,1
                       )md
  WHERE    d.`department_id`=md.department_id  ;             
                     
                     
                     
#10.查询平均工资最高的 job 信息
      SELECT j.*
      FROM  employees e,jobs j
      WHERE  e.`job_id`=j.`job_id`
      GROUP BY job_id
      HAVING  AVG(salary) =(
                          SELECT  MAX(val)
                           FROM(
                           SELECT  AVG(salary) val
                           FROM  employees
                           GROUP BY  job_id
                        ) md
                         
                           );
      
      
#11.查询平均工资高于公司平均工资的部门有哪些?
         SELECT   department_id
         FROM     employees
         WHERE    department_id IS NOT NULL
         GROUP BY department_id
         HAVING   AVG(salary) > (
                                SELECT AVG(salary)
                                FROM employees
                                 );
 #12.查询出公司中所有 manager 的详细信息
     SELECT  e.employee_id,e.last_name
     FROM employees e
     WHERE       EXISTS (
                        SELECT  employee_id
                        FROM  employees d
                        WHERE  e.employee_id=d.manager_id
                         );     
#13.各个部门中 最高工资中最低的那个部门的 最低工资是多少?
       SELECT MIN(salary)
       FROM employees 
       GROUP BY  department_id
       HAVING    MAX(salary)=(
                           SELECT MIN(val)
                           FROM (
                           SELECT  MAX(salary) val
                           FROM  employees
                           GROUP BY department_id
                           ) md
                           );
                         
 #14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
         SELECT e.last_name, e.department_id,e.email, e.salary
         FROM employees e
         WHERE    EXISTS (
                                SELECT d.employee_id
                                FROM  employees d
                                WHERE e.`manager_id`=d.`employee_id`
                               )
         GROUP BY department_id
         HAVING   AVG(salary)=(
                            SELECT  MAX(val)                            
                            FROM(
                            SELECT  AVG(salary) val
                            FROM employees
                            GROUP BY department_id
                                )md
                            
                           ) 
         ; 
         SELECT e.last_name, e.department_id,e.email, e.salary
         FROM employees e   
       #  where e.`manager_id` is not null                    
         WHERE EXISTS (
                                SELECT employee_id
                                FROM  employees d
                                WHERE e.`manager_id`=d.`employee_id`
                               ) ;                  
                                
    #改正：方式3：
    SELECT  last_name,department_id,email,salary
    FROM  employees
    WHERE  employee_id IN (
                           SELECT DISTINCT e.manager_id
                           FROM  employees e,(
                                           SELECT department_id,AVG(salary) val
                                           FROM employees
                                           GROUP BY department_id
                                           ORDER BY  val DESC
                                           LIMIT 0,1
                                          
                                           )md
                           WHERE  e.`department_id`=md.department_id              
                                                             
                            );                            
                                
                                                
 #15. 查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号
          SELECT DISTINCT department_id
          FROM 	 employees
          WHERE  department_id NOT IN (
                                SELECT  department_id
                                FROM  employees
                                WHERE  job_id="ST_CLERK"
                                 );
#16. 选择所有没有管理者的员工的last_name
       SELECT last_name
       FROM  employees e
       WHERE  NOT EXISTS(   
                       SELECT *
                       FROM employees d
                       WHERE  e.`manager_id`=d.`employee_id`    
                            );
       
 #17．查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan'
         SELECT  employee_id,last_name,hire_date,salary
         FROM  employees
         WHERE   manager_id=(
                               SELECT  employee_id
                               FROM 	employees
                               WHERE   last_name='De Haan'
                            );
 #18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（相关子查询）
           SELECT  e.employee_id,e.last_name,e.salary
           FROM  employees e
           WHERE  e.salary >  ( 
                                        SELECT  AVG(d.`salary`)
                                        FROM employees d
                                        WHERE   e.`department_id`=d.`department_id`
                                        GROUP BY department_id
                                              );
#19.查询每个部门下的部门人数大于 5 的部门名称（相关子查询）
           SELECT  d.department_name
           FROM  departments d
           WHERE  5 <= (
                          SELECT  COUNT(*)    
                          FROM employees e
                          WHERE  e.`department_id`=d.`department_id`
                        ); 
#20.查询每个国家下的部门个数大于 2 的国家编号（相关子查询）
            SELECT  country_id
            FROM  locations  l
            WHERE  2 < (
                          SELECT  COUNT(*)
                          FROM departments  d
                          WHERE l.`location_id`=d.`location_id`
                         
                           );