MySQL分组查询练习题
在MySQL数据库中,分组查询是一种强大的功能,它能够根据一定的条件将数据进行分组并进行统计、计算等操作。本文将介绍一些常见的分组查询练习题,并提供相应的代码示例。
1. 统计每个部门的员工数量
假设我们有一个员工表(employee),包含以下字段:员工ID(id)、员工姓名(name)、所属部门(department)。
首先,我们需要创建这张表:
CREATE TABLE employee (
id INT,
name VARCHAR(50),
department VARCHAR(50)
);
然后,插入一些示例数据:
INSERT INTO employee (id, name, department)
VALUES (1, '李明', '销售部'),
(2, '王红', '销售部'),
(3, '张三', '人事部'),
(4, '李四', '人事部'),
(5, '王五', '技术部');
现在,我们可以使用分组查询来统计每个部门的员工数量:
SELECT department, COUNT(*) AS employee_count
FROM employee
GROUP BY department;
上述查询语句使用了GROUP BY
子句来指定按部门进行分组,并使用COUNT(*)
函数来统计每个部门的员工数量。
2. 求每个部门的平均工资
假设我们有另外一张表(salary),包含以下字段:员工ID(id)、工资(amount)。
首先,我们需要创建这张表:
CREATE TABLE salary (
id INT,
amount DECIMAL(10, 2)
);
然后,插入一些示例数据:
INSERT INTO salary (id, amount)
VALUES (1, 5000),
(2, 6000),
(3, 4000),
(4, 4500),
(5, 7000);
现在,我们可以使用分组查询来求每个部门的平均工资:
SELECT e.department, AVG(s.amount) AS average_salary
FROM employee e
JOIN salary s ON e.id = s.id
GROUP BY e.department;
上述查询语句使用了JOIN
子句将员工表和工资表进行连接,并使用AVG()
函数来计算每个部门的平均工资。
3. 查找每个部门的最高和最低工资
为了解决这个问题,我们可以使用子查询来找到每个部门的最高和最低工资。
SELECT e.department, s.min_salary, s.max_salary
FROM employee e
JOIN (
SELECT department, MIN(amount) AS min_salary, MAX(amount) AS max_salary
FROM employee e
JOIN salary s ON e.id = s.id
GROUP BY department
) s ON e.department = s.department;
上述查询语句使用了子查询来计算每个部门的最高和最低工资。首先,子查询使用MIN()
和MAX()
函数来找到每个部门的最低和最高工资。然后,外部查询使用JOIN
子句将结果与员工表进行连接,以获取每个部门的相关信息。
序列图
下面是一个使用序列图表示上述查询过程的示例:
sequenceDiagram
participant Client
participant MySQL
Client->>MySQL: 执行查询语句
MySQL->>MySQL: 执行分组查询
MySQL-->>Client: 返回结果
序列图展示了客户端发送查询请求,MySQL数据库执行分组查询并返回结果的过程。
关系图
下面是一个使用关系图表示员工表和工资表之间关系的示例:
erDiagram
entity Employee {
id INT
name VARCHAR(50)
department VARCHAR(50)
}
entity Salary {
id INT
amount DECIMAL(10, 2)
}
Employee }|..|{ Salary
关系图清晰地展示了员工表和工资表之间的一对一关系。
总结:
本文介绍了一些常见的MySQL分组查询练习题,并提供了相应的代码示例。通过学习这些练习题,你可以更好地理解分组查询的用法和原理