mysql(7):子查询

mysql基础---查询语句---进价7 子查询

1.子查询

        1.含义：出现在其它语句中的select语句，称为子查询或内查询，外部的查询称为主查询或外查询

 

        2.select first_name from employees where department_id in (select department_id from departments where location_id = 1700);---红色为主查询，绿色为子查询

 

        3.按照子查询的结果可分为：

                1.标量子查询-----结果集为一行一列

                2.列子查询-------结果集为一列多行

                3.行子查询-------结果集为一行多列，或者多行多列

                4.表子查询-------结果集一般为多行多列

 

        4.按子查询出现的位置分类：

                1.select后面-----仅仅支持标量子查询

                2.from后面------支持表子查询

                3 where或having后面----一般支持标量/列/行子查询

                4.exists(存在)后面----支持表子查询

 

2.where或having后面

    1.支持：

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

        2.列子查询(一列多行子查询)

        3.行子查询(一行多列子查询)

    2.特点

        1.子查询放在小括号内

        2.子查询一般在条件右侧

        3.标量子查询，一般搭配着单行操作符使用(单行操作符: <,>,>=,<=,=等)

        4.列子查询，一般搭配着多行操作符使用(in【等于列表中的任一个】,any/some【子查询返回的某一个值比较】,all [【子查询返回的所有值比较】

        5.子查询的执行优于主查询的执行，主查询的条件用到子查询的结果

案例：

    3.标量子查询

            1.谁的工资比DavidAustin高

            mysql > select concat(first_name,last_name) 员工姓名,salary 工资 from employees where salary > (select salary from employees where concat(frist_name,last_name) = 'DavidAustin');

            mysql > select salary from employees where concat(frist_name,last_name) = 'DavidAustin'

              

            2.返回job_id与141号员工相同，salary比143号员工高的员工姓名，job_id和工资

            mysql >select concat(first_name,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.返回公司工资最小的员工的姓名，job_id，salary

            msyql > select concat(first_name,last_name) 姓名,job_id as 工种编号,salary 工资 from employees where salary = (select min(salary) from employees);

            

            4.查询最低工资大于50号部门最低工资的部门id和其最低工资

            mysql > select department_id 部门编号,min(salary) 最低工资 from employees group by department_id having min(salary) > (select min(salary) from employees where department_id = 50);

            

4.列子查询(一列多行)

    案例：

            1.返回location_id是1400或1700的部门中的所有员工的姓名

            mysql > select concat(last_name,first_name) 员工姓名 from employees where department_id in (select department_id from departments where location_id in (1400,1700));

            

            2.返回其他部门中比job_id为'IT_PROG'部门任一工资低的员工的员工号，姓名，Job_id,salary

            mysql > select employee_id 员工号,concat(last_name,first_name) 姓名,job_id 工种号,salary 工资 from employees  where salary < any (select salary from employees where job_id = 'IT_PROG') and job_id <> 'IT_PROG';

            

 

5.行子查询(结果为一行多列或者多行多列)

    案例：

            1.查询员工编号最小并且工资最高的员工信息

            mysql > select * from employees where employee_id = (select min(employee_id) from employees) and salary = ( select max(salary) from employees);

            

            mysql > select * from employees where(employee_id,salary) = (select min(employee_id),max(salary) from employees);

           

 

3.放在select后面---仅仅支持标量子查询

    案例：

            1.查询每个部门的员工个数

            mysql > select  d.*,(select count(*) from employees e where e.department_id = d.department_id) 个数 from departments d;

            

            mysql > select d.*,count(e.employee_id) 个数 from employees e right join departments d on e.department_id = d.department_id group by department_id;

            

            2.查询员工号102的部门名

            mysql > select department_name 部门名 from employees e right join departments d on e.department_id = d.department_id where e.employee_id = 102;

            mysql > select (select department_name 部门名 from employees e inner join departments d on e.department_id = d.department_id where e.employee_id = 102) 部门名；

            

              mysql > select (select department_name 部门名,e.employee_id 员工编号 from employees e inner join departments d on e.department_id = d.department_id where e.employee_id = 102) 部门名；

                ---这是因为select后面的子查询只能是标量子查询

 

4.放在from后面----表子查询

        案例：

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

             mysql > select e.avgSalary 平均工资， grade_level 工资等级 from (select avg(salary) avgSalary from employees  group by department_id) e inner join job_grades j on e.avgSalary between lowest_sal and highest_sal;

            ---先黄色部分查出一个表e，然后表e与job_grades表进行内连接

 

5.exists后面(相关子查询)

        案例：

            1.select exists(select employee_id from employees);------exists表示是否存在，即子查询是否有值，如果有则为1，否则为0

            

            2.查询有员工名的部门名

            mysql > select deparment_name 部门名 from departments d where exists(select * from employees e where e.department_id = d.department_id);

            ----先查子查询，后查子查询

            mysql > select department_name from departments d where d.department_id in (select department_id from employees);

 

            3.查询没有女朋友的男神信息

            mysql > select bo.* from boys bo where not exists(select boyfriend_id from beauty b where bo.id = b.boyfriend_id);

            mysql > select bo.* from boys bo where bo.id not in (select boyfriend_id from beauty);

             

          

案例：查询各部门中工资比本部门平均工资高的员工的员工号，姓名，工资

            msyql >select employee_id 员工号,concat(first_name,last_name) 姓名,salary 工资 from employees e inner join (select avg(salary) ag,department_id from employees) avg_de on e.department_id = avg_de.department_id where e.salary > avg_de.ag;