SQL(四)

一、分组函数

-- MAX()  MIN()  AVG()  SUM()  COUNT()

SELECT MAX(salary), MIN(salary), AVG(salary), SUM(salary), COUNT(salary)
FROM employees;

SELECT MAX(last_name), MAX(LENGTH(last_name)), MIN(last_name), COUNT(last_name)
FROM employees;

SELECT COUNT(employee_id)
FROM employees;

SELECT COUNT(*)
FROM employees;

#组函数不计算空值
SELECT COUNT(commission_pct)
FROM employees;

SELECT SUM(commission_pct) / COUNT(*), AVG(commission_pct)
FROM employees;

SELECT AVG(salary)
FROM employees;

-- GROUP BY 分组（重点、难点）
-- 求出各个部门的平均工资
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;

-- 注意：出现在 select 子句中的非分组函数，一定出现在 group by 子句后
SELECT employee_id, department_id, AVG(salary)
FROM employees
GROUP BY department_id, employee_id; -- 多列分组

-- 求出各个部门中每个工种(job_id) 的平均工资
SELECT department_id, job_id, AVG(salary)
FROM employees
GROUP BY department_id, job_id;

-- 注意：where 子句中不能使用组函数，若需要过滤组函数 having
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) >= 8000;

-- 部门最高工资比 10000 高的部门

SELECT department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000;

-- MYSQL 中组函数不能嵌套，在 Oracle 中组函数可以嵌套
SELECT MAX(AVG(salary))
FROM employees
GROUP BY department_id;

SELECT AVG(salary)
FROM employees
GROUP BY department_id;

SELECT MAX(avg_sal)
FROM (
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id

) emp

二、子查询

-- 谁的工资比 Abel 高?

SELECT salary
FROM employees
WHERE last_name = 'Abel';

SELECT *
FROM employees
WHERE salary > 11000;

-- 主查询（外查询）
SELECT *
FROM employees
WHERE salary > (
-- 子查询（内查询）
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);

-- 题目：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id 和工资
SELECT last_name, job_id, salary
FROM employees
WHERE job_id = (
SELECT job_id
FROM employees
WHERE employee_id = 141
) AND salary > (
SELECT salary
FROM employees
WHERE employee_id = 143
);

-- 题目：返回公司工资最少的员工的last_name,job_id和salary
SELECT last_name, job_id, salary
FROM employees
WHERE salary = (
SELECT MIN(salary)
FROM employees
);

-- 题目：查询最低工资大于50号部门最低工资的部门id和其最低工资

SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
SELECT MIN(salary)
FROM employees
WHERE department_id = 50
)

-- 非法使用子查询
SELECT employee_id, last_name
FROM   employees
WHERE  salary = (
SELECT MIN(salary)
        FROM employees
        GROUP BY department_id
);

-- 空值问题
SELECT last_name, job_id
FROM   employees
WHERE  job_id =
                (SELECT job_id
                 FROM   employees
                 WHERE  last_name = 'Haas');

-- 多行子查询
SELECT employee_id, last_name
FROM   employees
WHERE  salary IN (
SELECT MIN(salary)
        FROM employees
        GROUP BY department_id
);

-- 题目：返回其它部门中比job_id为‘IT_PROG’部门 任一 工资低的员工的员工号、姓名、job_id 以及salary

-- any : 若与值列表中任何一个条件满足，则结果为满足条件数据
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';


-- 题目：返回其它部门中比job_id为‘IT_PROG’部门 所有 工资低的员工的员工号、姓名、job_id 以及salary

-- all ：若与值列表中所有条件都满足，则结果为满足条件数据
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ALL(
SELECT salary
FROM employees
WHERE job_id = 'IT_PROG'
) AND job_id <> 'IT_PROG';


42. 查询工资最低的员工信息: last_name, salary
SELECT last_name, salary
FROM employees 
WHERE salary = (
SELECT MIN(salary)
FROM employees
)

43. 查询平均工资最低的部门信息
SELECT *
FROM departments
WHERE department_id = (


SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (

SELECT MIN(avg_sal) 
FROM (
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
) e

)


)

-- 查询平均工资最低的部门信息和 "该" 部门的平均工资
SELECT d.*, (SELECT AVG(salary) FROM employees WHERE department_id = d.department_id)
FROM departments d
WHERE department_id = (


SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (

SELECT MIN(avg_sal) 
FROM (
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
) e

)


)


44. 查询平均工资最高的 job 信息
SELECT *
FROM jobs
WHERE job_id = (


SELECT job_id 
FROM employees
GROUP BY job_id
HAVING AVG(salary) = (


SELECT MAX(avg_sal)
FROM (
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY job_id
) e

)


)

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

SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (
SELECT AVG(salary)
FROM employees
)


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

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

-- 1. 查询各个部门的最高工资
SELECT MAX(salary)
FROM employees
GROUP BY department_id

-- 2. 在 1 的基础上找出最高工资中“最低工资”
SELECT MIN(max_sal)
FROM (
SELECT MAX(salary) max_sal
FROM employees
GROUP BY department_id
) e

-- 3. 在 2 的基础上找出所在部门
SELECT department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary) = (
SELECT MIN(max_sal)
FROM (
SELECT MAX(salary) max_sal
FROM employees
GROUP BY department_id
) e
)

-- 4. 在 3 的基础上找出最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id = (


SELECT department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary) = (
SELECT MIN(max_sal)
FROM (
SELECT MAX(salary) max_sal
FROM employees
GROUP BY department_id
) e
)

)

48. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
SELECT last_name, department_id, email, salary
FROM employees
WHERE employee_id IN (
SELECT manager_id
FROM employees
WHERE department_id = (

SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (

SELECT MAX(avg_sal)
FROM (
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
) e

)

)
)

三、分组

①limit子句

      [1]格式：limit a,b

      [2]本质：a=查询结果显示起始位置(从零开始)，b=显示多少条记录

      [3]注意：limit子句必须放在整个查询语句的最后！切记！

②使用limit子句实现分页

      [1]需求：根据页码pageNo和每页显示多少条记录pageSize，取回对应的数据

      [2]使用limit子句实现分页

               (1)pageSize可以直接用于参数b

               (2)将页码换算为参数a：a=(pageNo-1)*pageSize

               (3)结论：limit (pageNo-1)*pageSize,pageSize

 

#返回第一页数据 SELECT emp_id,emp_name,salary,birthday,telephone FROM emps LIMIT 0,5; #返回第二页数据 SELECT emp_id,emp_name,salary,birthday,telephone FROM emps LIMIT 5,5; #返回第三页数据 SELECT emp_id,emp_name,salary,birthday,telephone FROM emps LIMIT 10,5;