【MySql学习笔记】 子查询

子查询

混乱的开始！套娃的开始！

子查询的基本使用

• 子查询（内查询）在主查询之前一次执行完成。
• 子查询的结果被主查询（外查询）使用 。
• 注意事项
  • 子查询要包含在括号内
  • 将子查询放在比较条件的右侧
  • 单行操作符对应单行子查询，多行操作符对应多行子查询

子查询的分类

• 分类方式1：

  • 我们按内查询的结果返回一条还是多条记录，将子查询分为单行子查询、多行子查询。
    • 单行 子查询查询结果只有一行
    • 多行 子查询查询结果行数 > 1

• 分类方式2：

  • 我们按内查询是否被执行多次，将子查询划分为相关(或关联)子查询和不相关(或非关联)子查询。

单行子查询

子查询返回单行数据

行比较操作符

操作符	含义
=	equal to
>	greater than
>=	greater than or equal to
<	less than
<=	less than or equal to
<>	not equal to

例子：

emp 员工表{
	id, '员工id'
	name,'姓名'
	salary,'工资'
	depId,'部门Id'
	...
}

dep 员工表{
	id, '部门Id'
	name,'部门姓名'
	loc, '部门地址'
	...
}
 -----------------------------------						
//单行子查询
'查询工资 > id = 123 员工工资 的员工信息'

1.子查询的方式
	select * from emp e1  
		where salary > (
            select salary from emp e2 
                        	where e2.id = 'id'
        ) 
 2.自连接的方式
 	select * from emp e1 
 		Join emp e2 
 			on e2.name = 'id' 
 						and e1.salary > e2.salary.
 						
 -----------------------------------						
'查询公司 工资最少的员工信息'

1.子查询的方式
	select * from emp e1 
		where salary = (
            select MIN(salary) from e1 
            )
            
            
 -----------------------------------						
 '查询 在 与id=123的员工部门相同 的员工中 的最高工资'
 
 1.子查询的方式
	select MAX(e1.salary) '最高工资' from emp e1 
		where e1.deptid = (
            select deptid from emp e2 WHERE e2.id = 123	
            )
            
              
 -----------------------------------						
 '查询最低工资大于50号部门最低工资的部门id和其最低工资'
 
  select e1.dept_id '部门id', MIN(e1.salary) '最低工资' from emp e1 
	GROUP BY e1.dept_id 
		HAVING  最低工资 > (
            select MIN(e2.salary) from emp e2 
            	where e2.dept_id = 20	
            )
            
            
 -----------------------------------		
'显式员工上id, name, loc 其中若员工的 id 与 id=0的员工的 depId相同 则loc为北京 否则上海' 
#case when else 写法

select e1.id    '部门id',
       e1.ename '员工姓名',
       (case e1.id
            when
            	(select e2.dept_id from emp e2 where e1.id = 0) then '北京'
            else '上海'
           end
           )    'loc'
from emp e1;

#if 写法

select e1.id    '部门id',
       e1.ename '员工姓名',
       (IF(e1.id = (select e2.dept_id from emp e2 where e1.id = 0), '北京', '上海')
           )    '工作地点'
from emp e1;

            
...

多行子查询

子查询返回多行数据

多行比较操作符

操作符	含义
IN	等于列表中的任意一个
ANY	需要和单行比较操作符一起使用，和子查询返回的任意值比较
ALL	需要和单行比较操作符一起使用，和子查询返回的所有值比较
SOME	实际上是ANY的别名，作用相同，一般常使用ANY

例子：

emp 员工表{
	id, '员工id'
	name,'姓名'
	salary,'工资'
	depId,'部门Id'
	...
}

dep 员工表{
	id, '部门Id'
	name,'部门姓名'
	loc, '部门地址'
	...
}
 -----------------------------------						
//多行子查询
'查询部门WXG的员工工资 > 部门IEG员工的工资 的员工信息'

select * from emp e1 
		Join dep d1 
			on d1.name = 'WXG' and e1.depId = d1.id 
				and e1.salary > ANY (
                     elect e2salary from emp e2 join dep d2
                     on d1.name = 'IEG' and e1.depId = d1.id 
                     )
                     
 -----------------------------------						             
 '查询平均工资最低的部门Id'
 
' 有点绕 先查询到每个部门的平均工资 然后从中取最小 再算一遍各部门的平均工资 使其 等于 算出的最小平均工资'
 1.方法一
 select depId '最小的平均工资' from emp e 
 	roup by e.depId
 	having AVG(e.salary) = (
                     select MIN(avg_Salary) '平均工资' 
                         from (
                               select AVG(salary) avg_Salary 
                             		from emp e1
                             		group by e1.depId 
                                ) avg_Table //把查询结果设置别名 当作一张表
                          )
                          
2.方法二						
 		select depId '最小的平均工资' from emp e 
 			group by e.depId 
 			having
 				AVG(e.salary) <= ALL (
                               select AVG(salary)
                             		from emp e1
                             		group by e1.depId 
                          )
            
...

MySQL中聚合函数是不能嵌套使用的

相关子查询

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

例子：

emp 员工表{
	id, '员工id'
	name,'姓名'
	salary,'工资'
	depId,'部门Id'
	...
}

dep 员工表{
	id, '部门Id'
	name,'部门姓名'
	loc, '部门地址'
	...
}
 -----------------------------------						
// 相关子查询
'查找哪些 员工工资 低于 所在部门的平均工资'

1.子查询在where中 相关是'内查询利用了外查询的表'
	select e1.id, e1.name, e1.salary
		from emp e1
			where e1.salary < (
    			select AVG(e2.salary) AVG_salary
    			from emp e2
    			where e2.depId = e1.depId
				)
				
 2.子查询在from中 相关是'外查询利用了内查询的表'
 select e1.id, e1.name, e1.salary
	from emp e1 join (
    	select e2.depId, AVG(e2.salary) AVG_salary
   		 from  emp e2
    		group by e2.depId
		) AVG_e2
    on e1.salary < AVG_e2.AVG_salary 
    and e1.depId = AVG_e2.depId;

 -----------------------------------
 '查询员工的id, salary 按照 部门名字排序'
 
 select e1.id, e1.salary
    from emp e1 
    	order by (
         select d1.name
             from dep d1
              where d1.id = e1.depId
        )
        
 -----------------------------------
 
 
 
 

...

• 在查询中，除了 Group by 和 Limit 之外，其他位置都可以声明子查询

EXISTS 与 NOT EXISTS 关键字

• 关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。

• 如果在子查询中不存在满足条件的行：

  • 条件返回 FALSE
  • 继续在子查询中查找

• 如果在子查询中存在满足条件的行：

  • 不在子查询中继续查找
  • 条件返回 TRUE

• NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。

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

  方式一：

SELECT employee_id, last_name, job_id, department_id
FROM employees e1
WHERE EXISTS ( 
    SELECT * 
    FROM employees e2 
    WHERE e2.manager_id = e1.employee_id
);

方式二：自连接

SELECT DISTINCT e1.employee_id, e1.last_name, e1.job_id, e1.department_id
FROM employees e1 JOIN employees e2 
WHERE e1.employee_id = e2.manager_id;

方式三：

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

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

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

题目中可以使用子查询，也可以使用自连接。一般情况建议你使用自连接，因为在许多 DBMS 的处理过程中，对于自连接的处理速度要比子查询快得多。