MySQL-进阶7-子查询 - select后/where后/from后/ []where后/having后] / exists后面的相关子查询...

红太狼 2021-09-30 05:04 275阅读 0赞

/*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;        ###加上筛选条件

转载于:https://www.cnblogs.com/zhazhaacmer/p/9858256.html