MYSQL初阶学习笔记——进阶8：分页查询

#进阶8：分页查询
/*
应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
语法：                        执行顺序
    select 查询列表                7        
    from 表1 别名                 1
    关键字（连接类型） join 表2 别名    2
    on 连接条件                3
    【where 筛选条件】            4
    【group by 分组】            5
    【having 分组筛选条件】            6
    【order by 排序列表】            8
    limit offset,size;   #分页查询子句    9
    
    offset    要显示条目的起始索引（起始索引从0开始）
    size    要显示的条目个数

特点：
    1.limit在查询语句最后，且执行顺序也是最后
    2.公式：
        要显示页数page，每页条目数size：
        limit (page-1)*size,size
*/

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

SELECT * FROM employees LIMIT 0,5;    #默认起始索引就是0，第一个
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
limit 10;



#=================综合大测试-子查询经典案例======================

# 1. 查询工资最低的员工信息: last_name, salary
select last_name,salary
from employees
where salary=(
    select min(salary)
    from employees
    );
# 2. 查询平均工资最低的部门信息

###最低的平均工资
SELECT AVG(salary) ags,department_id
FROM employees
group by department_id
order by ags
limit 1;

#部门信息
SELECT *
FROM departments
WHERE department_id=(
    SELECT department_id
    FROM employees
    GROUP BY department_id
    ORDER BY AVG(salary)
    LIMIT 1
    );

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

##方式一
select d.*,avg_dep.asl
from departments d
inner join (
    select avg(salary) asl,e.department_id
    from employees e
    group by e.department_id
) as avg_dep
on d.`department_id`=avg_dep.department_id

where d.department_id=(
    SELECT e.department_id
    FROM employees e
    GROUP BY e.department_id
    ORDER BY AVG(salary)
    LIMIT 1
    );

##方式二
SELECT d.*,avg_dep.asl
FROM departments d
INNER JOIN (
    SELECT AVG(salary) asl,e.department_id
    FROM employees e
    GROUP BY e.department_id
    ORDER BY asl
    LIMIT 1
) AS avg_dep
ON d.`department_id`=avg_dep.department_id;

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

select *
from jobs j
where j.`job_id`=(
    select e.job_id
    from employees e
    group by e.job_id
    order by avg(salary) desc
    limit 1
);

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

##1.公司平均工资
select avg(salary)
from employees;

##2.部门平均工资
SELECT AVG(salary)
FROM employees
group by department_id;

##3.比较筛选
SELECT AVG(salary) ags_d,department_id
FROM employees
GROUP BY department_id
having ags_d>(
    SELECT AVG(salary)
    FROM employees
);

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

select *
from employees
where employee_id in (
    select distinct manager_id
    from departments
);

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

SELECT min(salary),department_id
FROM employees
WHERE department_id=(
    SELECT department_id
    FROM employees
    group by department_id
    ORDER BY max(salary)
    LIMIT 1
    );


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

select last_name,d.department_id,email,salary
from employees e
inner join departments d
on d.manager_id=e.employee_id
where e.department_id=(
    SELECT department_id
    FROM employees
    group by department_id
    ORDER BY AVG(salary) desc
    LIMIT 1
);


#==================查询作业=================================
#一、查询每个专业的学生人数


#二、查询参加考试的学生中，每个学生的平均分、最高分


#三、查询姓张的每个学生的最低分大于60的学号、姓名


#四、查询每个专业生日在“1988-1-1”后的学生姓名、专业名称


#五、查询每个专业的男生人数和女生人数分别是多少


#六、查询专业和张翠山一样的学生的最低分


#七、查询大于60分的学生的姓名、密码、专业名


#八、按邮箱位数分组，查询每组的学生个数


#九、查询学生名、专业名、分数


#十、查询哪个专业没有学生，分别用左连接和右连接实现


#十一、查询没有成绩的学生人数