MySQL中的聚合查询和联合查询怎么实现

在MySQL中，聚合查询和联合查询是两种非常常见的查询方式。它们分别用于处理数据的汇总和多表数据的合并。本文将详细介绍这两种查询的实现方法，并通过示例代码帮助读者更好地理解。

1. 聚合查询

聚合查询是指对一组数据进行汇总计算，常见的聚合函数包括COUNT、SUM、AVG、MAX、MIN等。聚合查询通常与GROUP BY子句一起使用，以便对数据进行分组汇总。

1.1 常见的聚合函数

1.1.1 COUNT

COUNT函数用于计算某列的行数。它可以用于统计表中的记录数，或者统计某一列中非空值的数量。

-- 统计表中的总记录数
SELECT COUNT(*) FROM employees;

-- 统计某一列中非空值的数量
SELECT COUNT(salary) FROM employees;

1.1.2 SUM

SUM函数用于计算某列的总和。

-- 计算某列的总和
SELECT SUM(salary) FROM employees;

1.1.3 AVG

AVG函数用于计算某列的平均值。

-- 计算某列的平均值
SELECT AVG(salary) FROM employees;

1.1.4 MAX

MAX函数用于找出某列的最大值。

-- 找出某列的最大值
SELECT MAX(salary) FROM employees;

1.1.5 MIN

MIN函数用于找出某列的最小值。

-- 找出某列的最小值
SELECT MIN(salary) FROM employees;

1.2 GROUP BY 子句

GROUP BY子句用于将结果集按一个或多个列进行分组。通常与聚合函数一起使用，以便对每个分组进行汇总计算。

-- 按部门分组，计算每个部门的平均工资
SELECT department_id, AVG(salary) 
FROM employees 
GROUP BY department_id;

1.3 HAVING 子句

HAVING子句用于过滤分组后的结果集。它与WHERE子句类似，但WHERE子句用于过滤行，而HAVING子句用于过滤分组。

-- 按部门分组，计算每个部门的平均工资，并筛选出平均工资大于5000的部门
SELECT department_id, AVG(salary) 
FROM employees 
GROUP BY department_id
HAVING AVG(salary) > 5000;

1.4 示例

假设我们有一个employees表，结构如下：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10, 2)
);

插入一些示例数据：

INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES
(1, 'John', 'Doe', 1, 5000.00),
(2, 'Jane', 'Smith', 1, 6000.00),
(3, 'Bob', 'Johnson', 2, 4500.00),
(4, 'Alice', 'Williams', 2, 5500.00),
(5, 'Charlie', 'Brown', 3, 7000.00);

示例1：计算每个部门的平均工资

SELECT department_id, AVG(salary) 
FROM employees 
GROUP BY department_id;

结果：

department_id	AVG(salary)
1	5500.00
2	5000.00
3	7000.00

示例2：计算每个部门的员工数量

SELECT department_id, COUNT(*) 
FROM employees 
GROUP BY department_id;

结果：

department_id	COUNT(*)
1	2
2	2
3	1

示例3：筛选出平均工资大于5000的部门

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

结果：

department_id	AVG(salary)
1	5500.00
3	7000.00

2. 联合查询

联合查询是指将多个查询的结果集合并成一个结果集。MySQL中常用的联合查询操作包括UNION、UNION ALL、INTERSECT和EXCEPT。需要注意的是，MySQL本身不支持INTERSECT和EXCEPT操作，但可以通过其他方式实现类似功能。

2.1 UNION

UNION操作符用于合并两个或多个SELECT语句的结果集，并去除重复的行。

-- 合并两个查询的结果集，并去除重复的行
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

2.2 UNION ALL

UNION ALL操作符用于合并两个或多个SELECT语句的结果集，但不去除重复的行。

-- 合并两个查询的结果集，不去除重复的行
SELECT column1, column2 FROM table1
UNION ALL
SELECT column1, column2 FROM table2;

2.3 INTERSECT

INTERSECT操作符用于返回两个查询结果集的交集。MySQL本身不支持INTERSECT操作，但可以通过INNER JOIN或EXISTS子查询来实现类似功能。

-- 使用INNER JOIN实现INTERSECT功能
SELECT t1.column1, t1.column2 
FROM table1 t1
INNER JOIN table2 t2 
ON t1.column1 = t2.column1 AND t1.column2 = t2.column2;

2.4 EXCEPT

EXCEPT操作符用于返回第一个查询结果集中存在但第二个查询结果集中不存在的行。MySQL本身不支持EXCEPT操作，但可以通过LEFT JOIN或NOT EXISTS子查询来实现类似功能。

-- 使用LEFT JOIN实现EXCEPT功能
SELECT t1.column1, t1.column2 
FROM table1 t1
LEFT JOIN table2 t2 
ON t1.column1 = t2.column1 AND t1.column2 = t2.column2
WHERE t2.column1 IS NULL;

2.5 示例

假设我们有两个表table1和table2，结构如下：

CREATE TABLE table1 (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE table2 (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

插入一些示例数据：

INSERT INTO table1 (id, name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

INSERT INTO table2 (id, name) VALUES
(2, 'Bob'),
(3, 'Charlie'),
(4, 'David');

示例1：使用UNION合并两个表的数据

SELECT id, name FROM table1
UNION
SELECT id, name FROM table2;

结果：

id	name
1	Alice
2	Bob
3	Charlie
4	David

示例2：使用UNION ALL合并两个表的数据

SELECT id, name FROM table1
UNION ALL
SELECT id, name FROM table2;

结果：

id	name
1	Alice
2	Bob
3	Charlie
2	Bob
3	Charlie
4	David

示例3：使用INNER JOIN实现INTERSECT功能

SELECT t1.id, t1.name 
FROM table1 t1
INNER JOIN table2 t2 
ON t1.id = t2.id AND t1.name = t2.name;

结果：

id	name
2	Bob
3	Charlie

示例4：使用LEFT JOIN实现EXCEPT功能

SELECT t1.id, t1.name 
FROM table1 t1
LEFT JOIN table2 t2 
ON t1.id = t2.id AND t1.name = t2.name
WHERE t2.id IS NULL;

结果：

id	name
1	Alice

3. 总结

本文详细介绍了MySQL中的聚合查询和联合查询的实现方法。聚合查询通过使用COUNT、SUM、AVG、MAX、MIN等聚合函数，结合GROUP BY和HAVING子句，可以对数据进行分组汇总和筛选。联合查询通过UNION、UNION ALL、INNER JOIN、LEFT JOIN等操作符，可以将多个查询的结果集合并或进行交集、差集操作。

掌握这些查询技巧，可以帮助我们更高效地处理和分析数据库中的数据。希望本文的内容能够对读者在实际工作中使用MySQL有所帮助。