MySQL必知必会_分页查询

最新推荐文章于 2024-07-18 18:15:00 发布

zjxxyz123

最新推荐文章于 2024-07-18 18:15:00 发布

阅读量353

点赞数

分类专栏：数据库笔记文章标签： MySQL

本文链接：https://blog.csdn.net/zjxxyz123/article/details/79155828

版权

数据库笔记专栏收录该内容

24 篇文章 1 订阅

订阅专栏

进阶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

#

    一、应用场景
    当要查询的条目数太多，一页显示不全
    二、语法

    select 查询列表
    from 表
    limit 【offset，】size;
    注意：
    offset代表的是起始的条目索引，默认从0开始
    size代表的是显示的条目数

    公式：
    假如要显示的页数为page，每一页条目数为size
    select 查询列表
    from 表
    limit (page-1)*size,size;

#####################案例演示

    #进阶8：分页查询 ★
    /*

    应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
    语法：
        select 查询列表          第7步  （选出查询的字段）
        from 表                 第1步（执行完后就生成了一个虚拟表，锁定了数据源）
        【join type join 表2           第2步（两个表连接，笛卡尔乘积形成一个虚拟大表）
        on 连接条件            第3步 （将之前的大表根据连接条件进行筛选，形成一个新的表）
        where 筛选条件          第4步  （在3基础上根据筛选条件进行筛选，形成一个新的表）
        group by 分组字段          第5步 （ 分组，形成分组后的结果集）
        having 分组后的筛选          第6步 （分组后筛选）
        order by 排序的字段】          第8步 （排序）
        limit 【offset,】size;          第9步  （进行分页显示）

        注意标注的执行顺序，每当执行一步，便会生成一个虚拟结果集

        offset要显示条目的起始索引（起始索引从0开始）
        size 要显示的条目个数
    特点：
        ①limit语句放在查询语句的最后
        ②公式
        要显示的页数 page，每页的条目数size

        select 查询列表
        from 表
        limit (page-1)*size,size;

        size=10
        page   
        1   0
        2   10
        3   20

    */
    #案例1：查询前五条员工信息


    SELECT * FROM  employees LIMIT 0,5;
    SELECT * FROM  employees LIMIT 5;


    #案例2：查询第11条——第25条
    SELECT * FROM  employees LIMIT 10,15;


    #案例3：有奖金的员工信息，并且工资较高的前10名显示出来
    SELECT 
        * 
    FROM
        employees 
    WHERE commission_pct IS NOT NULL 
    ORDER BY salary DESC 
    LIMIT 10 ;

    ###########测试题###################
    # 1.  查询工资最低的员工信息：last_name,salary

    SELECT last_name,salary
    FROM employees
    WHERE salary = (
        SELECT MIN(salary)
        FROM employees

    );

    #2. 查询平均工资最低的部门信息(注意如何查找最值的方法，通过分组后，排序，在用limit取第一个)

    # 第一步：先求出最低平均工资的部门编号（通过分组后，排序，在用limit取第一个!!!!!!）
    SELECT department_id
    FROM employees
    GROUP BY department_id
    ORDER BY AVG(salary)
    LIMIT 0,1;
    #第二步，根据部门编号，查找该部门信息（要么结合第一步用表的连接，要么用标量子查询）

    SELECT d.*
    FROM employees e
    INNER JOIN departments d
    ON e.`department_id` = d.`department_id`  #也可以用标量子查询代替 表的连接
    GROUP BY e.department_id
    ORDER BY AVG(salary)
    LIMIT 0,1;

    #3. 查询平均工资最低的部门信息和该部门的平均工资（此时，用表的连接更为简洁）
    #（或者，把查询出来的 最低的department_id 和相应salary组成一个表，将该表与departments表进行连接）

    ###方法一：
    SELECT AVG(salary),d.*
    FROM employees e
    INNER JOIN departments d
    ON e.`department_id` = d.`department_id`
    GROUP BY e.department_id
    ORDER BY AVG(salary)
    LIMIT 0,1;

    ###方法二
    #首先将查询出来的 最低的department_id 和相应salary组成一个表
    SELECT department_id,AVG(salary) av
    FROM employees
    GROUP BY department_id
    ORDER BY AVG(salary)
    LIMIT 0,1;
    #然后内连接
    SELECT d.*,avt.av
    FROM departments d
    INNER JOIN (
        SELECT department_id,AVG(salary) av
        FROM employees
        GROUP BY department_id
        ORDER BY AVG(salary)
        LIMIT 0,1
    ) avt
    ON avt.department_id = d.`department_id`;


    #4.查询平均工资最高的job信息(方法一：直接表的连接（两个表直接合成一个表） 方法二：先查询出平均工资最高的job_id（用limit），在对jobs表进行标量子查询)

    ###方法一：
    SELECT j.*
    FROM employees e
    INNER JOIN jobs j
    ON e.`job_id` = j.`job_id`
    GROUP BY job_id
    ORDER BY AVG(salary)
    LIMIT 0,1;

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

    ###首先查询公司平均公司
    SELECT AVG(salary)
    FROM employees e;
    ###然后查询每个部门的平均工资，然后分组后筛选
    SELECT AVG(em.salary),em.department_id
    FROM employees em
    GROUP BY em.department_id
    HAVING AVG(em.salary) >(
        SELECT AVG(e.salary)
        FROM employees e
    );

    #6. 查询出公司中所有mananger的详细信息
    ####方法一：自连接（也是一种特殊的内连接，自己和自己连接注意去重）
    SELECT DISTINCT m.*
    FROM employees e
    JOIN employees m
    ON e.`manager_id` = m.`employee_id`;

    ###方法二：列子查询
    #首先查找出所有的manager_id(注意去重)，返回一列多行
    SELECT DISTINCT manager_id
    FROM employees;
    #然后 对 emoloyees表进行列子查询
    SELECT *
    FROM employees e
    WHERE employee_id IN (
        SELECT DISTINCT manager_id
        FROM employees
    );

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

    # 求最高工资中最低的那个部门的部门编号
    SELECT e.department_id
    FROM employees e
    GROUP BY e.department_id
    ORDER BY MAX(e.salary)
    LIMIT 0,1;

    SELECT MIN(em.salary),em.`department_id`
    FROM employees em
    WHERE em.`department_id` = (
        SELECT e.department_id
        FROM employees e
        GROUP BY e.department_id
        ORDER BY MAX(e.salary)
        LIMIT 0,1
    );

    # 8. 查询平均工资最高的部门的manager的详细信息，last_name,department_id,email,salary
    ####方法一：标量子查询 （每一次都过子查询得到一个标量，然后去匹配另一个表）方法二 ：表的连接
    #  首先查询平均工资最高的部门 的部门编号

    SELECT e.department_id
    FROM employees e 
    GROUP BY e.`department_id`
    ORDER BY AVG(e.`salary`) DESC
    LIMIT 0,1;

    # 然后查询 平均工资最高的部门的manager的编号（通过部门编号找manage编号）
    SELECT manager_id
    FROM departments d
    WHERE d.`department_id` = (
        SELECT e.department_id
        FROM employees e
        GROUP BY e.`department_id`
        ORDER BY AVG(e.`salary`) DESC
        LIMIT 0,1
    );


    # 通过manamger编号 找manager的详细信息，last_name,department_id,email,salary


    SELECT em.last_name,em.department_id,em.email,em.salary
    FROM employees em
    WHERE em.`employee_id` = (
        SELECT manager_id
        FROM departments d
        WHERE d.`department_id` = (
            SELECT e.department_id
            FROM employees e
            GROUP BY e.`department_id`
            ORDER BY AVG(e.`salary`) DESC
            LIMIT 0,1
    )   
    );

#############################案例讲解

    #一、查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用null填充


    SELECT b.id,b.name,bo.*
    FROM beauty b
    LEFT OUTER JOIN boys bo
    ON b.`boyfriend_id` = bo.`id`
    WHERE b.`id`>3;
    #二、查询哪个城市没有部门

    SELECT city
    FROM departments d
    RIGHT OUTER JOIN locations l 
    ON d.`location_id`=l.`location_id`
    WHERE  d.`department_id` IS NULL;

    #三、查询部门名为SAL或IT的员工信息

    SELECT e.*,d.department_name,d.`department_id`
    FROM departments  d
    LEFT JOIN employees e
    ON d.`department_id` = e.`department_id`
    WHERE d.`department_name` IN('SAL','IT');


    SELECT * FROM departments
    WHERE `department_name` IN('SAL','IT');