MYSQL(子查询)

一、子查询

子查询指的就是在一个查询之中嵌套了其他的若干查询，在使用select语句查询数据时,有时候会遇到这样的情况，在where查询条件中的限制条件不是一个确定的值，而是一个来自于另一个查询的结果。子查询一般出现在FROM和WHERE子句中。

----------------------------------------------------------
1、子查询在主查询前执行一次
2、主查询使用子查询的结果
----------------------------------------------------------
练习
1、查询大于公司平均工资的员工姓名
SELECT ename,sal FROM emp WHERE sal>(SELECT AVG(sal) FROM emp)
2、查询出工资比MARTIN还要高的全部雇员信息

SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename = 'MARTIN')

 

使用子查询的注意事项：
    1、子查询要用括号括起来
    2、将子查询放在比较运算符的右边(增强可读性)
    3、对单行子查询使用单行运算符
    4、对多行子查询使用多行运算符

 

 

二、子查询的分类（根据子查询的结果）

    1):单行单列子查询：只包含一个字段的查询，返回的查询结果也只包含一行数据, 看做是一个值. 使用在WHERE之后。
    2):多行单列子查询：只包含了一个字段，但返回的查询结果可能多行或者零行,看做是多个值,使用在WHERE之后。
    3):多列子查询：包含多个字段的返回，查询结构可能是单行或者多行,看做是临时表,使用在FROM之后。

 

单行单列子查询
子查询返回一行一列记录,看做是一个值. 使用在WHERE之后.
1、返回一行记录
2、使用单行记录比较运算符：=；>；>=；<；<=；<>
--------------------------------------------------------------------------------
练习：
1、查询大于公司平均工资的员工姓名
SELECT ename,sal FROM emp WHERE sal >
    (SELECT AVG(sal) FROM emp)
2、查询出工资比MARTIN还要高的全部雇员信息
SELECT * FROM emp WHERE sal >
    (SELECT sal FROM emp WHERE ename = 'MARTIN')
 

多行单列子查询
1、返回多行
2、使用多行比较运算符
     IN：与列表中的任意一个值相等             
     ANY：与子查询返回的任意一个值比较
        1): = ANY:此时和IN操作符相同.             需求:查询工资等于任意部门经理的员工信息.
        2): > ANY:大于子查询中最小的数据.      需求:查询工资大于任意部门经理的员工信息.
        3): < ANY:大于子查询中最大的数据.      需求:查询工资小于任意部门经理的员工信息.
     ALL：与子查询返回的每一个值比较
        1): > ALL:大于子查询中最大的数据.
        2): < ALL:小于子查询中最小的数据.
----------------------------------------------------------------------------------------------
练习：

select * from departments where department_id in (select department_id from employees group by department_id having avg(salary)>(select avg(salary) from employees))

 

多行多列子查询
一般会把子查询返回的结果当成一个临时表，接着在临时表上继续查询或者连接查询；
注意，多行多列的子查询返回的结果必须要设置一个临时表名；

查询出每个部门的编号、名称、部门人数、平均工资:
SELECT d.deptno,d.dname,COUNT(e.empno),IFNULL(AVG(e.sal),0)
FROM dept d JOIN emp e USING (deptno)
GROUP BY d.deptno,d.dname

--------------------------------------------------
可以先把每一个部门的编号,总人数,平均工资先查询出来.
SELECT deptno dno,COUNT(empno) count ,AVG(sal) avg  FROM emp GROUP BY dno
再和dept表联合查询部门名称.
SELECT  dept.deptno,temp.count,temp.avg  FROM dept JOIN (SELECT deptno dno,COUNT(empno) count ,AVG(sal) avg  FROM emp GROUP BY deptno) temp ON dept.deptno = temp.dno
 

UNION/UNION ALL
JOIN是用于把表横向连接，UNION/UNION ALL是用于把表纵向连接(一般用于做查询的临时表)
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

注意，
1，UNION 内部的 SELECT 语句必须拥有相同数量的列。
2，列也必须拥有兼容的数据类型。
3，每条 SELECT 语句中的列的顺序必须相同。
4，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名
5，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL(性能高).

语法：
SELECT column_name(s) FROM table_name1
UNION|UNION ALL
SELECT column_name(s) FROM table_name2

在MYSQL 中实现FULL JOIN:
查询员工的编号,名称和部门名称.

1:先在emp表中插入一条数据,并设置depto为NULL.
2:查询
SELECT empno,ename,dname FROM emp LEFT JOIN dept USING (deptno)
UNION
SELECT empno,ename,dname FROM emp RIGHT JOIN dept USING (deptno)