MySQL --- 11♪ 进阶7 - 子查询

/*SQL-进阶7 - 子查询

　

   含义:出现在其他语句中的select 语句,称为子查询或内查询

   外部的查询语句,称为主查询 或者 外查询

 

    标量子查询（结果集只有一行一列）

    列子查询（结果集只有一列多行）

    行子查询（结果集有一行多列）

    表子查询（结果集一般为多行多列）

 

   分类1:按子查询出现的位置

   

    select 后面:仅仅支持标量子查询(仅能支持一行一列,下面有例子)

    

    from 后面:支持表 子查询

        将子查询的结果充当成一张表,要求必须起别名,不然会引起冲突!

 

    where 或 having 后面: [重点]

        标量子查询   √

        列子查询     √    [还一般搭配着多行操作符使用,in/ any/some/ all ]

        [  in(not in):等于列表中的任意一个

                  弃用 ####  any/some : (和子查询返回的某一个值比较)

                   弃用 #####  all : 和子查询返回的所有值比较   

         ]

        行子查询

    {共同特点:

        ①子查询放在小括号里

        ②子查询放在条件的右侧

        ③标量子查询,一般搭配着单行操作符使用

    }

    

    exists后面(相关子查询)

        exists后面的子查询 #结果返回1或者0;先执行主查询,再执行子查询

        

    分类2:按结果集的行列数不同:(用的很少)

    标量子查询(结果集)

    .......

        

*/

 

#联系: 多表查询 ,查询部门名为SAL 或IT的员工的所有信息

SELECT e.*,d.`department_name`

FROM employees e,departments d

WHERE e.`department_id`=d.`department_id` AND d.department_name IN ('SAL','IT');

 

#where 或 having 后面: [重点]

 

   #1.单行子查询(标量子查询)

    #案例1:谁的工资比ABel的高

 

SELECT e.*

FROM employees e

WHERE e.`salary` >(

    SELECT salary

    FROM employees

    WHERE last_name ='Abel'

);

    

    #案例2:返回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

    )

);

 

   #案例3: 返回公司工资最低的员工的last_name ,job_id 和 salary

 

SELECT *

FROM employees

WHERE salary =(

    SELECT MIN(salary)

    FROM employees

);

 

#二: select 后面的子查询

 

    #案例:查询每个部门的员工个数

 

SELECT d.*,(

    SELECT COUNT(*)

    FROM employees e

    WHERE d.department_id=e.department_id

) 个数

FROM departments d;

 

    ##也可以用连接查询???? ：employees 右外连接 departments

 

SELECT d.*,COUNT(*)

FROM employees e

RIGHT OUTER JOIN departments d

ON e.department_id=d.department_id

GROUP BY department_id;    

 

    #查询员工号=102的部门号

 

SELECT    (

    SELECT    d.department_name

    FROM departments d,employees e

    WHERE d.department_id = e.department_id

    AND e.employee_id = 102

) 部门名;

 

#三: from后面

 

    #案例:查询每个部门的平均工资的工资等级

 

     #1.查询每个部门的平均工资

 

SELECT AVG(salary),department_id

FROM employees

GROUP BY department_id;

 

    #2.查询工资等级

 

SELECT * FROM job_grades;

 

    #3.进行组合 , 连接1的两个结果集和job_grades表,筛选条件平均工资 between low and high

 

SELECT ag_dep.* ,g.grade_level

FROM (

    SELECT AVG(salary) ag,department_id   #查询后生成的表

    FROM employees

    GROUP BY department_id

) ag_dep

INNER JOIN job_grades g

ON ag_dep.ag BETWEEN g.lowest_sal AND g.highest_sal;

 

#四: exists后面的(相关子查询)  #结果返回1或者0;先执行主查询,再执行子查询

 

    #案例1:查询有员工名的部门名

 

SELECT department_name

FROM departments d

WHERE  EXISTS(

    SELECT *

    FROM employees e

    WHERE    d.department_id = e.`department_id`

);

 

#----------------例子练习--------------

 

    #一:查询各部门中工资比本部门平均工资高的员工的员工号,姓名和工资

 

  #①本部门平均工资

 

SELECT  AVG(e.`salary`) ag,department_id

FROM employees e

GROUP BY e.`department_id`;

 

  #(2) 与1进行连接

 

SELECT e.`department_id`,e.`employee_id`,e.`last_name`,e.`salary`,ag 平均工资

FROM employees e

INNER JOIN(

    SELECT  AVG(e.`salary`) ag,department_id

FROM employees e

GROUP BY e.`department_id`

)ag_dep

ON e.`department_id`=ag_dep.department_id  #内连接的连接条件!

WHERE salary > ag_dep.ag;   #加上筛选条件