分页查询

应用场景

当要显示的数据一页显示不全，需要分页提交提交sql请求。

语法

select 查询列表
from 表
[join type join 表2
on 连接条件
group by 分组字段
having 分组后的筛选条件
order by 排序的字段]
limit offset, size;

offset 要显示条目的起始索引（起始索引从0开始）

size 要显示的条目个数。

案例

# 分页查询
USE myemployees;

# 案例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. limit语句放在查询语句的最后

2. 公式

   要显示的页数page，每页的条目数size

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

练习

USE myemployees;
# 1. 查询工资最低的员工信息： last_name salary
SELECT last_name, salary
FROM employees
WHERE salary = (
    SELECT MIN(salary)
    FROM employees
    );

# 2. 查询平均工资最低的部门信息
# SELECT
#     d.*
# FROM
#     (SELECT AVG(salary) AS avg_salary,
#             department_id
#      FROM employees
#      GROUP BY department_id) AS department_avg_salary,
#     departments AS d
# WHERE d.department_id = department_avg_salary.department_id;

SELECT
    d.*
FROM
    (SELECT AVG(salary) AS avg_salary,
            department_id
     FROM employees
     GROUP BY department_id
     ORDER BY avg_salary
     LIMIT 1) AS department_avg_salary,
    departments AS d
WHERE d.department_id = department_avg_salary.department_id;

SELECT *
FROM departments
WHERE department_id = (
    SELECT department_id
    FROM employees
    GROUP BY department_id
    ORDER BY AVG(salary)
    LIMIT 1);

# 3. 查询平均工资最低的部门的信息和该部门的平均工资
SELECT
    d.*,
    department_avg_salary.avg_salary
FROM
    (SELECT AVG(salary) AS avg_salary,
            department_id
     FROM employees
     GROUP BY department_id
     ORDER BY  avg_salary
     LIMIT 1) AS department_avg_salary,
    departments AS d
WHERE d.department_id = department_avg_salary.department_id;


# 4. 查询平均工资最高的job信息
SELECT job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1;

SELECT *
FROM jobs
WHERE job_id = (
    SELECT job_id
    FROM employees
    GROUP BY job_id
    ORDER BY AVG(salary) DESC
    LIMIT 1
);

# 5. 查询平均工资高于公司平均工资的部门有哪些？
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (
    SELECT AVG(salary)
    FROM employees
);

# 6. 查询出公司中所有manager的详细信息
SELECT *
FROM employees
WHERE employee_id IN (
    SELECT DISTINCT manager_id
    FROM employees
);

# 7. 各个部门中 最高工资中最低的部门的最低工资是多少
SELECT MIN(salary)
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM employees
    GROUP BY department_id
    ORDER BY MAX(salary)
    LIMIT 1
);

# 8. 查询平均工资最高的部门的manager的详细信息
SELECT e.*
FROM employees AS e
INNER JOIN departments AS 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
    );

联合查询

union，将多条查询语句的结果合并成一个结果。

语法

查询语句1
union
查询语句2
union
...

引入案例：查询部门编号>90或邮箱包含a的员工信息

USE myemployees;
SELECT *
FROM employees
WHERE department_id>90 OR email LIKE '%a%';

## 联合查询
SELECT *
FROm employees
WHERE department_id > 90
UNION
SELECT *
FROM employees
WHERE email LIKE '%a%';

应用场景

要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时。

特点

1. 要求查询多条查询语句的查询列数是一致的
2. 要求多条查询语句的查询的每一列的类型和顺序最好是一致的
3. union关键字默认去重，如果使用union all可以包含重复项