MySQL--基础十一（分页查询、子查询经典案例）

本是古典何须时尚 2021-08-13 21:26 423阅读 0赞

#### 分页查询 ####

应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求

语法：
    1	select 查询列表 
    2	from 表
    3	【join type join 表
    4	on 连接条件
    5	where 筛选条件
    6	group by 分组字段
    7	having 分组后的筛选
    8	order by 排序的字段】
    9	limit 【offset,】size;
    	
    	offset要显示条目的起始索引（起始索引从0开始）
    	size 要显示的条目个数

执行顺序：2->3->4->5->6->7->1->8->9

特点：

*  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 MIN(salary)
    FROM employees
    
    #②查询last_name,salary，要求salary=①
    SELECT last_name,salary
    FROM employees
    WHERE salary=(
    	SELECT MIN(salary)
    	FROM employees
    );
    
    # 2. 查询平均工资最低的部门信息
    
    #方式一：
    #①各部门的平均工资
    SELECT AVG(salary),department_id
    FROM employees
    GROUP BY department_id
    #②查询①结果上的最低平均工资
    SELECT MIN(ag)
    FROM (
    	SELECT AVG(salary) ag,department_id
    	FROM employees
    	GROUP BY department_id
    ) ag_dep
    
    #③查询哪个部门的平均工资=②
    
    SELECT AVG(salary),department_id
    FROM employees
    GROUP BY department_id
    HAVING AVG(salary)=(
    	SELECT MIN(ag)
    	FROM (
    		SELECT AVG(salary) ag,department_id
    		FROM employees
    		GROUP BY department_id
    	) ag_dep
    
    );
    
    #④查询部门信息
    
    SELECT d.*
    FROM departments d
    WHERE d.`department_id`=(
    	SELECT department_id
    	FROM employees
    	GROUP BY department_id
    	HAVING AVG(salary)=(
    		SELECT MIN(ag)
    		FROM (
    			SELECT AVG(salary) ag,department_id
    			FROM employees
    			GROUP BY department_id
    		) ag_dep
    
    	)
    
    );
    
    #方式二：
    #①各部门的平均工资
    SELECT AVG(salary),department_id
    FROM employees
    GROUP BY department_id
    
    #②求出最低平均工资的部门编号
    SELECT department_id
    FROM employees
    GROUP BY department_id
    ORDER BY AVG(salary) 
    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 AVG(salary),department_id
    FROM employees
    GROUP BY department_id
    #②求出最低平均工资的部门编号
    SELECT AVG(salary),department_id
    FROM employees
    GROUP BY department_id
    ORDER BY AVG(salary) 
    LIMIT 1;
    #③查询部门信息
    SELECT d.*,ag
    FROM departments d
    JOIN (
    	SELECT AVG(salary) ag,department_id
    	FROM employees
    	GROUP BY department_id
    	ORDER BY AVG(salary) 
    	LIMIT 1
    
    ) ag_dep
    ON d.`department_id`=ag_dep.department_id;
    
    
    
    # 4. 查询平均工资最高的 job 信息
    #①查询最高的job的平均工资
    SELECT AVG(salary),job_id
    FROM employees
    GROUP BY job_id
    ORDER BY AVG(salary) DESC
    LIMIT 1
    
    #②查询job信息
    SELECT * 
    FROM jobs
    WHERE job_id=(
    	SELECT job_id
    	FROM employees
    	GROUP BY job_id
    	ORDER BY AVG(salary) DESC
    	LIMIT 1
    
    );
    # 5. 查询平均工资高于公司平均工资的部门有哪些?
    
    #①查询平均工资
    SELECT AVG(salary)
    FROM employees
    
    #②查询每个部门的平均工资
    SELECT AVG(salary),department_id
    FROM employees
    GROUP BY department_id
    
    #③筛选②结果集，满足平均工资>①
    
    SELECT AVG(salary),department_id
    FROM employees
    GROUP BY department_id
    HAVING AVG(salary)>(
    	SELECT AVG(salary)
    	FROM employees
    
    );
    
    # 6. 查询出公司中所有 manager 的详细信息.
    #①查询所有manager的员工编号
    SELECT DISTINCT manager_id
    FROM employees
    
    #②查询详细信息，满足employee_id=①
    SELECT *
    FROM employees
    WHERE employee_id =ANY(
    	SELECT DISTINCT manager_id
    	FROM employees
    
    );
    
    # 7. 各个部门中 最高工资中最低的那个部门的 最低工资是多少
    
    #①查询各部门的最高工资中最低的部门编号
    SELECT department_id
    FROM employees
    GROUP BY department_id
    ORDER BY MAX(salary)
    LIMIT 1
    
    
    #②查询①结果的那个部门的最低工资
    
    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 
        department_id 
    FROM
        employees 
    GROUP BY department_id 
    ORDER BY AVG(salary) DESC 
    LIMIT 1 
    
    #②将employees和departments连接查询，筛选条件是①
        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) ;