• MySQL子查询_分页查询_联合查询


    进阶7:子查询

     

    含义

            一条查询语句中又嵌套了另一条完整的select语句,其中被嵌套的select语句,称为子查询或内查询

            在外面的查询语句,称为主查询或外查询

     

    特点:

            1、子查询都放在小括号内

            2、子查询可以放在

            from后面仅仅支持子查询、

            select后面支持标量子查询(单行)、

            where后面having后面标量子查询,列子查询

    ,但一般放在条件的右侧,也可以放在

    exists后面(相关子查询)表子查询

            3、子查询优先于主查询执行,主查询使用了子查询的执行结果

            4、子查询根据查询结果的行数不同分为以下两类:

            ① 单行子查询

                   结果集只有一行

                   一般搭配单行操作符使用:> < = <> >= <=

                   非法使用子查询的情况:

                   a、子查询的结果为一组值

                   b、子查询的结果为空

                  

            ② 多行子查询/列子查询(一列多行)

                   结果集有多行

                  

            行子查询,多行多列

     

     

    Where或者having后面

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

    列子查询(多行子查询)

    行子查询(多列多行)

    特点:子查询都放在小括号内,子查询一般防止条件的右侧,标量子查询一般搭配单行操作符   > < >= ….

    列子查询一般搭配多行操作符号一般搭配多行操作符使用:any/some、all、in、not in

                   in: 属于子查询结果中的任意一个就行

                   any和all往往可以用其他查询代替

    标量子查询

    #谁的工资比Abel高

    1. 查询Abel工资高
    2. 然后找比这个标量大的

    mysql> select last_name from employees where salary>(select salary from employees where last_name='Abel');

     

    #找job_id 与141号员工相同,salary比143号员工多的员工 姓名,job_id和工资

    mysql> select job_id,salary from employees where job_id=(select job_id from employees where employee_id =141) andd salary>(select salary from employees where employee_id =143);

    +----------+---------+

    | job_id   | salary  |

    +----------+---------+

    | ST_CLERK | 3200.00 |

     

    #返回公司工资最少的员工的last_name,job_id和salary

    查询公司的最低工资

    mysql> select last_name,job_id,salary from employees where salary=(select min(salary) from employees);

    +-----------+----------+---------+

    | last_name | job_id   | salary  |

    +-----------+----------+---------+

    | Olson     | ST_CLERK | 2100.00 |

    +-----------+----------+---------+

    mysql> select last_name,job_id,salary from employees order by salary asc limit 1;

    +-----------+----------+---------+

    | last_name | job_id   | salary  |

    +-----------+----------+---------+

    | Olson     | ST_CLERK | 2100.00 |

    +-----------+----------+---------+

     

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

    mysql> select department_id,min(salary) from employees group by department_id having min(salary)>(select min(salaary) from employees where department_id=50);

    +---------------+-------------+

    | department_id | min(salary) |

    +---------------+-------------+

    |          NULL |     7000.00 |

    |            10 |     4400.00 |

    |            20 |     6000.00 |

    |            30 |     2500.00 |

    |            40 |     6500.00 |

    +---------------+-------------+

     

    列子查询

     

    in/not in   等于列表中的任意一个

    any/some    和子查询返回的某一个比较

    all         和子查询返回的所有值比较

    >any  可以替换成>min

    >all   可以替换成>max

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

    1. 查询location 1400或者1700

    mysql> select last_name,department_id from employees where department_id in(select distinct department_id from departments s where location_id in(1400,1700));

    +------------+---------------+

    | last_name  | department_id |

    +------------+---------------+

    | Hunold     |            60 |

    | Ernst      |            60 |

     

    mysql> select last_name,e.department_id from employees e join departments d on e.department_id=d.department_id where d.location_id in((1400,1700);

     

    返回其他部门中比job_id 为’IT_PROG’部门任一工资低的员工号、姓名、job_id以及salary    任一不是任意

     

    mysql> select employee_id,last_name,job_id,salary from employees where salary<any(select salary from employees where job_id='IT_PROG')) and job_id!='IT_PROG';

     

    mysql> select employee_id,last_name,job_id,salary from employees where salary<(select max(salary) from employees where job_id='IT_PROGG') and job_id!='IT_PROG';

     

    行子查询  一行多列或者多行多列

    查询员工编号最小且工资最高的员工信息(不一定存在)

    可以分开来查  

    两个条件都是等于(相同的关系)

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

    +-------------+------------+-----------+-------+--------------+---------+----------+----------------+------------+---------------+---------------------+

    | employee_id | first_name | last_name | email | phone_number | job_id  | salary   | commission_pct | manager_id | department_id | hiredate            |

    +-------------+------------+-----------+-------+--------------+---------+----------+----------------+------------+---------------+---------------------+

    |         100 | Steven     | K_ing     | SKING | 515.123.4567 | AD_PRES | 24000.00 |           NULL |       NULL |            90 | 1992-04-03 00:00:00 |

    +-------------+------------+-----------+-------+--------------+---------+----------+----------------+------------+---------------+---------------------+

     

     

    select后面仅仅支持标量子查询

    查询每个部门的员工数

     

     

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

    +---------------+-----------------+------------+-------------+------+

    | department_id | department_name | manager_id | location_id | nums |

    +---------------+-----------------+------------+-------------+------+

    |            10 | Adm             |        200 |        1700 |    1 |

     

     

    查询员工号=102的部门名

    mysql> select (select department_name from departments d inner join employees e on d.department_id=e.department_id where employee_id=1102)name;

    +------+

    | name |

    +------+

    | Exe  |

    +------+

    1 row in set (0.00 sec)

     

     

    from后面   子查询的表格充当数据源,必须起别名

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

    先查每个部门的平均工资

    mysql> select ag.*,g.grade_level from (select avg(salary) ave,department_id from employees group by department_id) ag inner join job_grades g on ag.ave between lowest_sal and highest_sal;

    +--------------+---------------+-------------+

    | ave          | department_id | grade_level |

    +--------------+---------------+-------------+

    |  7000.000000 |          NULL | C           |

    |  4400.000000 |            10 | B           |

    |  9500.000000 |            20 | C           |

     

    Exists后面相关子查询     0或者1,有没有值

    mysql> select exists(select employee_id from employees);

    +-------------------------------------------+

    | exists(select employee_id from employees) |

    +-------------------------------------------+

    |                                         1 |

     

    mysql> select exists(select employee_id from employees where salary=20000000);

    +-----------------------------------------------------------------+

    | exists(select employee_id from employees where salary=20000000) |

    +-----------------------------------------------------------------+

    |                                                               0 |

    +-----------------------------------------------------------------+

     

    查询有员工的部门名

    mysql> select department_name from departments d where exists(select* from employees e where d.departmeent_id=e.department_id);

    +-----------------+

    | department_name |

    +-----------------+

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

    +-----------------+

    | department_name |

    +-----------------+

    | Adm             |

     

     

    子查询 测 试

     

    #查询工资最低的员工信息: last_name, salary

     

    mysql> select last_name,salary from employees where salary=(select min(salary) from employees);

     

    +-----------+---------+

     

    | last_name | salary  |

     

    +-----------+---------+

     

    | Olson     | 2100.00 |

     

    +-----------+---------+

     

    1 row in set (0.00 sec)

     

     

     

    #查询平均工资最低的部门信息

     

    mysql> select * from departments d where d.department_id=(select department_id from employees group by department_id having avg(salary)=(select min(ag) from ( select department_id,avg(salary) ag from employees group by department_id) ag_dep));

     

    +---------------+-----------------+------------+-------------+

     

    | department_id | department_name | manager_id | location_id |

     

    +---------------+-----------------+------------+-------------+

     

    |            50 | Shi             |        121 |        1500 |

     

    +---------------+-----------------+------------+-------------+

     

    1 row in set (0.00 sec)

     

     

     

    这样很麻烦,所以可以用limit

     

    mysql> select * from departments where department_id =(select department_id from employees group by department_id order by avg(salary) asc limit 1);

     

    +---------------+-----------------+------------+-------------+

     

    | department_id | department_name | manager_id | location_id |

     

    +---------------+-----------------+------------+-------------+

     

    |            50 | Shi             |        121 |        1500 |

     

    +---------------+-----------------+------------+-------------+

     

    1 row in set (0.00 sec)

     

     

     

    #查询平均工资最低的部门信息和该部门的平均工资

     

    mysql> select d.*,dv.ag from departments d inner join (select avg(salary) ag,department_id from employees group by department_id) dv on d.department_id=dv.department_id order by dv.ag limit 1;

     

    +---------------+-----------------+------------+-------------+-------------+

     

    | department_id | department_name | manager_id | location_id | ag          |

     

    +---------------+-----------------+------------+-------------+-------------+

     

    |            50 | Shi             |        121 |        1500 | 3475.555556 |

     

    +---------------+-----------------+------------+-------------+-------------+

     

     

     

     

     

    #查询平均工资最高的 job 信息

     

    mysql> select * from jobs where job_id=(select job_id from employees group by job_id order by avg(salary) asc limit 1);

     

    +----------+------------------+------------+------------+

     

    | job_id   | job_title        | min_salary | max_salary |

     

    +----------+------------------+------------+------------+

     

    | PU_CLERK | Purchasing Clerk |       2500 |       5500 |

     

    +----------+------------------+------------+------------+

     

    1 row in set (0.00 sec)

     

     

     

    #查询平均工资高于公司平均工资的部门有哪些?

     

     

     

    mysql> select department_id from employees group by department_id having avg(salary)>(select avg(salary) from employees);

     

     

     

    #查询出公司中所有 manager 的详细信息.

     

    mysql> select * from employees where employee_id=any( select distinct manager_id from employees);

     

     

     

    #各个部门中 最高工资中最低的那个部门的 最低工资是多少

     

     

     

    mysql> select department_id,min(salary) from employees group by department_id having department_id=(select department_id from employees group by department_id order by max(salary) asc limit 1);

     

    +---------------+-------------+

     

    | department_id | min(salary) |

     

    +---------------+-------------+

     

    |            10 |     4400.00 |

     

    +---------------+-------------+

     

    #查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

     

     

     

    mysql> select last_name,department_id,email,salary from employees where employee_id=(select manager_id from departments where department_id=(select department_id from employees grroup by department_id order by avg(salary) desc limit 1));

     

    +-----------+---------------+-------+----------+

     

    | last_name | department_id | email | salary   |

     

    +-----------+---------------+-------+----------+

     

    | K_ing     |            90 | SKING | 24000.00 |

     

    +-----------+---------------+-------+----------+

     

     

     

    上述方法三层嵌套子查询

     

     

     

    下方法将两个表链接后将第一步作为条件

     

    mysql> select last_name,d.department_id,email,salary from employees e inner join departments d on d.manager_id=e.employee_id where d.department_id=(select department_id from employees group by department_id order by avg(salary) desc limit 1);

     

    +-----------+---------------+-------+----------+

     

    | last_name | department_id | email | salary   |

     

    +-----------+---------------+-------+----------+

     

    | K_ing     |            90 | SKING | 24000.00 |

     

    +-----------+---------------+-------+----------+

     

    ##进阶8:分页查询

     

     

    应用场景:

     

            实际的web项目中需要根据用户的需求提交对应的分页查询的sql语句

     

    语法:

     

            select 字段|表达式,...

            from 表

            【where 条件】

            【group by 分组字段】

            【having 条件】

            【order by 排序的字段】

            limit 【起始的条目索引,】条目数;

     

    特点:

     

            1.起始条目索引从0开始

           

            2.limit子句放在查询语句的最后

           

            3.公式:select * from  表 limit (page-1)*sizePerPage,sizePerPage

            假如:

            每页显示条目数sizePerPage

            要显示的页数 page

     

    查询前五条员工信息

    mysql> select * from employees limit 0,5;

    或者limit 5

     

    ##进阶9:联合查询

     

    引入:

            union 联合、合并

     

    语法:

     

            select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】

            select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】

            select 字段|常量|表达式|函数 【from 表】 【where 条件】 union  【all】

            .....

            select 字段|常量|表达式|函数 【from 表】 【where 条件】

     

    特点:

     

            1、多条查询语句的查询的列数必须是一致的

            2、多条查询语句的查询的列的类型几乎相同

            3、union代表去重,union all代表不去重

     

  • 相关阅读:
    【转】Install libimobiledevice on Mac OSX
    【转】Mac端包管理工具——Homebrew简介及安装
    Appium的安装-Mac平台(命令行 & dmg)
    【转】NO.3、python+appium+ios,遍历真机元素,得到webview
    【转】NO.2、Appium之IOS第一个demo
    【转】NO.1、 appium之ios环境搭建
    如何在 iOS 真机运行 Appium
    【转】adb shell dumpsys 命令
    Mac下抓包工具charles及破解方法
    2017.08 笔记
  • 原文地址:https://www.cnblogs.com/BetterThanEver_Victor/p/8752786.html
Copyright © 2020-2023  润新知