MySQL进阶查询五剑客：插入结果、聚合统计、分组剖析、多表联合、子查询

MySQL进阶查询五大核心技能

数据库查询优化与复杂数据处理能力是开发者的关键技能。掌握插入结果集、聚合统计计算、分组维度分析以及多表关联操作，能够高效解决实际业务中的数据整合、分析需求。这些技术将显著提升数据操作效率，实现从基础增删改查到高级分析的跨越。

INSERT INTO ... SELECT：将查询结果插入新表或现有表

语法详解
基本语法分为两种形式：

1. 插入到新表：CREATE TABLE new_table AS SELECT * FROM source_table WHERE conditions;
2. 插入到现有表：INSERT INTO target_table (col1, col2) SELECT col1, col2 FROM source_table WHERE conditions;

应用场景

• 数据备份：快速创建表的数据副本。
• 表结构变更：将数据迁移到新结构的表中。
• 数据汇总：将多表查询结果存储到目标表。

示例演示
插入到新表：

CREATE TABLE employee_backup AS SELECT * FROM employees WHERE department = 'IT';

插入到现有表：

INSERT INTO sales_report (product_id, total_sales)
SELECT product_id, SUM(amount) FROM orders GROUP BY product_id;

注意事项

• 字段数量和数据类型需匹配。
• 插入现有表时需处理主键或唯一键冲突。
• 大数据量操作可能影响性能，建议在低峰期执行。

---

聚合查询 (Aggregate Queries)

核心聚合函数

• COUNT()：统计行数。
• SUM()：计算数值列总和。
• AVG()：计算平均值。
• MAX()/MIN()：获取最大值/最小值。

基本语法

SELECT AVG(salary), MAX(age) FROM employees WHERE department = 'Finance';

处理NULL值
聚合函数通常忽略NULL值。例如：

SELECT AVG(bonus) FROM employees; -- NULL值不计入分母

---

分组查询 (GROUP BY)

语法与作用
通过GROUP BY将数据按指定字段分组，常与聚合函数联用：

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

HAVING子句
对分组结果筛选（类似WHERE但对组生效）：

SELECT product_category, SUM(sales) 
FROM orders 
GROUP BY product_category 
HAVING SUM(sales) > 10000;

多字段分组

SELECT department, job_title, COUNT(*) 
FROM employees 
GROUP BY department, job_title;

---

联合查询 (UNION)

UNION vs UNION ALL

• UNION：合并并去重，默认排序。
• UNION ALL：简单合并，更快但保留重复。

语法与规则

SELECT product_id FROM orders_2022
UNION
SELECT product_id FROM orders_2023;

要求：列数相同，对应列数据类型兼容。

---

联合查询 (JOIN)

INNER JOIN
仅返回两表匹配的行：

SELECT users.name, orders.amount
FROM users
INNER JOIN orders ON users.id = orders.user_id;

LEFT JOIN
保留左表所有行，右表无匹配时填充NULL：

SELECT departments.name, employees.id
FROM departments
LEFT JOIN employees ON departments.id = employees.dept_id;

RIGHT JOIN
保留右表所有行，左表无匹配时填充NULL（可用LEFT JOIN替代）：

SELECT A.name, B.value
FROM tableA
RIGHT JOIN tableB ON A.id = B.a_id;

多表连接

SELECT users.name, orders.amount, products.title
FROM users
JOIN orders ON users.id = orders.user_id
JOIN products ON orders.product_id = products.id;

连接条件优化

• 确保关联字段有索引。
• 避免无条件的笛卡尔积（如漏写ON子句）

 

简化基础子查询模块

子查询用于在主查询中嵌套另一个查询，通常作为条件或数据源。简化后基础结构如下：

SELECT column1, column2
FROM table1
WHERE column1 IN (
    SELECT column1
    FROM table2
    WHERE condition
)

常见基础子查询类型

WHERE子句中的子查询

SELECT product_name, price
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
)

FROM子句中的子查询（派生表）

SELECT a.category, a.avg_price
FROM (
    SELECT category, AVG(price) AS avg_price
    FROM products
    GROUP BY category
) AS a

SELECT子句中的子查询（标量子查询）

SELECT employee_name, 
       salary,
       (SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees

基础注意事项

子查询必须用括号包围 大多数情况下需要为派生表设置别名 标量子查询必须返回单值 IN操作符常与子查询配合使用

其他查询要点:

可以使用ROUND(数值,小数点位数)来格式化小数输出格式

可在FROM表后面给表添加简化名,之后可直接使用简化名

SELECT st.name,c.name from student st join class c on st.classid=c.classid;
 

 自连接

select * from table1 t1, table1 t2 where t1.xxx = t2.xxx;

1. 通过自连接 把行转换成列（注：实际是通过别名将单表逻辑拆分为两个虚拟表）。

2. 在查询时使用 WHERE 条件进行过滤，实现 行与行之间的比较。

一条SQL语句中各部分的执行顺序 

select distinct id, name, avg(age) 
from student 
join class on student.class_id = class.id 
where class_id = 1  
group by student.id 
having avg(age) > 0 
order by student.id asc 
limit 100;

SQL关键字的逻辑执行顺序：
FROM → JOIN ON → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT