SQL查询

引言 窗口函数（Window Functions）是MySQL 8.0引入的强大分析功能，可以在不改变行数的情况下进行复杂的计算和排序。 一、窗口函数基础 1.1 基本语法 function_name() OVER ( [PARTITION BY column] [ORDER BY column] [frame_specification] ) 核心概念： PARTITION BY：分组（类似GROUP BY，但不合并行） ORDER BY：排序 frame_specification：窗口框架（可选） 1.2 与GROUP BY的区别 -- GROUP BY：合并行 SELECT category, COUNT(*) FROM products GROUP BY category; -- 结果：3行 -- 窗口函数：保留所有行 SELECT name, category, COUNT(*) OVER (PARTITION BY category) AS category_count FROM products; -- 结果：10行（每行都显示） 二、ROW_NUMBER() - 行号 为每行分配唯一的序号。 2.1 基础用法 -- 为所有商品编号 SELECT name, price, ROW_NUMBER() OVER (ORDER BY price DESC) AS row_num FROM products; 结果： ...

引言 UNION用于合并多个SELECT语句的结果集，适用于需要从不同表或不同条件查询结果合并的场景。 一、UNION基础 1.1 基本语法 SELECT column1, column2 FROM table1 UNION SELECT column1, column2 FROM table2; 要求： 列数必须相同 列的数据类型要兼容 默认去重 1.2 示例 -- 合并2024年和2025年的订单 SELECT order_id, amount, '2024' AS year FROM orders_2024 UNION SELECT order_id, amount, '2025' AS year FROM orders_2025; 二、UNION vs UNION ALL 2.1 UNION - 去重合并 SELECT name FROM products WHERE category = '手机' UNION SELECT name FROM products WHERE price > 5000; 特点： 自动去重 性能较差（需要排序去重） 2.2 UNION ALL - 保留重复 SELECT name FROM products WHERE category = '手机' UNION ALL SELECT name FROM products WHERE price > 5000; 特点： 保留所有记录（包括重复） 性能好（不需要去重） 2.3 性能对比 -- UNION：去重需要额外排序，慢 EXPLAIN SELECT id FROM orders_2024 UNION SELECT id FROM orders_2025; -- Extra: Using temporary -- UNION ALL：直接合并，快 EXPLAIN SELECT id FROM orders_2024 UNION ALL SELECT id FROM orders_2025; -- Extra: (无) 建议： ...

引言 子查询（Subquery）是嵌套在其他查询中的SELECT语句，用于实现复杂的查询逻辑。 一、子查询分类 1.1 按返回结果分类 标量子查询：返回单个值（1行1列） 列子查询：返回一列多行 行子查询：返回一行多列 表子查询：返回多行多列 1.2 按执行方式分类 非相关子查询：独立执行，与外层查询无关 相关子查询：依赖外层查询，每行都执行一次 二、标量子查询 返回单个值，可用于比较运算。 -- 查询价格高于平均价格的商品 SELECT name, price FROM products WHERE price > (SELECT AVG(price) FROM products); -- 查询销量最高的商品 SELECT name, sales FROM products WHERE sales = (SELECT MAX(sales) FROM products); 三、列子查询 返回一列多行，通常配合IN、ANY、ALL使用。 3.1 IN / NOT IN -- 查询有订单的用户 SELECT name FROM users WHERE id IN (SELECT DISTINCT user_id FROM orders); -- 查询没有订单的用户 SELECT name FROM users WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL); ⚠️ NOT IN的陷阱：子查询包含NULL会返回空结果。 ...

引言 实际开发中，数据通常分散在多个表中。要获取完整信息，需要将多个表关联起来查询。本文讲解MySQL的多表连接。 一、连接的本质：笛卡尔积 1.1 笛卡尔积 -- 创建测试表 CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50) ); CREATE TABLE orders ( id INT PRIMARY KEY, user_id INT, amount DECIMAL(10,2) ); INSERT INTO users VALUES (1, '张三'), (2, '李四'), (3, '王五'); INSERT INTO orders VALUES (1, 1, 100), (2, 1, 200), (3, 2, 150); -- 笛卡尔积：3 * 3 = 9 条记录 SELECT * FROM users, orders; 结果：所有可能的组合（9条）。 1.2 加上连接条件 -- 只保留有意义的组合 SELECT * FROM users, orders WHERE users.id = orders.user_id; 这就是连接的本质：笛卡尔积 + 过滤条件。 二、INNER JOIN - 内连接 2.1 基本语法 SELECT columns FROM table1 INNER JOIN table2 ON table1.key = table2.key; 2.2 示例 -- 查询用户及其订单信息 SELECT u.name, o.id AS order_id, o.amount FROM users u INNER JOIN orders o ON u.id = o.user_id; 结果： ...

引言 在上一篇我们学习了聚合函数（COUNT、SUM、AVG、MAX、MIN），它们能对整个结果集进行统计。但在实际开发中，我们经常需要分组统计： 统计每个类别的商品数量和平均价格 分析每个用户的订单总额和订单数 计算每个月的销售额和订单量 按地区统计客户数量和消费金额 这些需求都需要通过 GROUP BY 来实现——先分组，再对每组数据进行聚合计算。 为什么GROUP BY如此重要？ 多维度分析：从不同角度分析数据（按时间、地区、类别等） 报表统计的核心：几乎所有报表都需要分组统计 业务洞察：发现不同群体的差异和规律 决策支持：为精细化运营提供数据依据 本文将系统讲解GROUP BY的原理、语法、使用技巧，以及HAVING子句的应用。 一、GROUP BY 基础 1.1 什么是分组查询？ 分组查询将数据按照某个或某些列的值进行分组，然后对每个组分别进行聚合计算。 执行流程： FROM：确定要查询的表 WHERE：过滤行（在分组之前） GROUP BY：将数据分组 HAVING：过滤分组（在分组之后） SELECT：选择要返回的列和聚合结果 ORDER BY：对结果排序 LIMIT：限制返回行数 1.2 基本语法 SELECT column1, aggregate_function(column2) FROM table_name WHERE condition GROUP BY column1 HAVING group_condition ORDER BY column1; 1.3 准备测试数据 继续使用上一篇的订单表，并补充一些数据： -- 补充更多测试数据 INSERT INTO orders (user_id, product_name, price, quantity, order_date, status) VALUES (1, '小米13', 3299.00, 1, '2024-11-11', 'completed'), (2, 'iPad Air', 4799.00, 1, '2024-11-12', 'completed'), (3, 'AirPods Pro', 1999.00, 1, '2024-11-13', 'completed'), (4, '华为Mate 60', 6999.00, 1, '2024-11-14', 'completed'), (5, '小米14', 3999.00, 1, '2024-11-15', 'pending'), (6, 'MacBook Pro', 14999.00, 1, '2024-11-16', 'completed'), (7, 'iPhone 15 Pro', 7999.00, 1, '2024-11-17', 'cancelled'); 二、单字段分组 2.1 基础分组统计 -- 统计每个用户的订单数量 SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id; 结果： ...

引言 在数据分析和报表统计中,我们经常需要对数据进行汇总计算： 统计商品总数、总销售额 计算平均价格、平均评分 找出最高价、最低价 统计用户数、订单数 这些需求都需要通过聚合函数（Aggregate Functions）来实现。 为什么聚合函数如此重要？ 数据分析的基础：90%的报表都需要聚合统计 业务指标计算：GMV、客单价、转化率等核心指标 性能优化关键：数据库层面的聚合比应用层效率高 决策支持：为业务决策提供数据依据 本文将系统讲解MySQL的五大聚合函数，以及它们在实际开发中的应用。 一、聚合函数基础 1.1 什么是聚合函数？ 聚合函数对一组值执行计算，返回单个值。 五大聚合函数： COUNT()：计数 SUM()：求和 AVG()：平均值 MAX()：最大值 MIN()：最小值 1.2 基本语法 SELECT aggregate_function(column_name) FROM table_name WHERE condition; 1.3 聚合函数的特点 输入多行，输出一行：对多条记录进行计算，返回一个结果 忽略NULL值：除了 COUNT(*) 外，其他聚合函数都忽略NULL 可与GROUP BY结合：对分组后的每组数据分别聚合 不能在WHERE中使用：WHERE是在聚合之前执行的 1.4 准备测试数据 -- 创建订单表 CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, product_name VARCHAR(100), price DECIMAL(10, 2), quantity INT, order_date DATE, status VARCHAR(20) ); -- 插入测试数据 INSERT INTO orders (user_id, product_name, price, quantity, order_date, status) VALUES (1, 'iPhone 15 Pro', 7999.00, 1, '2024-11-01', 'completed'), (1, 'AirPods Pro', 1999.00, 1, '2024-11-02', 'completed'), (2, '华为Mate 60', 6999.00, 1, '2024-11-03', 'completed'), (2, '小米14', 3999.00, 2, '2024-11-04', 'completed'), (3, 'MacBook Pro', 14999.00, 1, '2024-11-05', 'pending'), (3, 'iPad Air', 4799.00, 1, '2024-11-06', 'completed'), (4, '小米13', 3299.00, 1, '2024-11-07', 'cancelled'), (5, '联想ThinkPad', NULL, 1, '2024-11-08', 'completed'), -- 价格为NULL (6, 'AirPods Pro', 1999.00, 2, '2024-11-09', 'completed'), (7, '索尼WH-1000XM5', 2499.00, 1, '2024-11-10', 'completed'); 二、COUNT() - 计数函数 2.1 COUNT(*) - 统计总行数 统计所有行数，包括NULL值的行。 ...

引言 在实际开发中，我们经常需要对查询结果进行排序和分页： 商品列表按价格从低到高排序 文章列表按发布时间倒序显示 用户列表分页展示，每页20条 排行榜按得分从高到低排序 这些需求都需要通过 ORDER BY 和 LIMIT 来实现。 为什么排序和分页如此重要？ 用户体验：有序的数据更符合用户的阅读习惯 性能优化：分页可以减少数据传输量，提升响应速度 业务需求：排行榜、Top N查询等场景必不可少 数据管理：便于数据的浏览和检索 本文将深入讲解排序和分页的原理、语法、性能优化技巧，以及如何解决深分页问题。 一、ORDER BY 排序基础 1.1 基本语法 SELECT column1, column2, ... FROM table_name WHERE condition ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...; 执行顺序： FROM：确定要查询的表 WHERE：过滤出符合条件的行 ORDER BY：对结果进行排序 SELECT：选择要返回的列 排序方向： ASC：升序（Ascending），从小到大，默认值 DESC：降序（Descending），从大到小 1.2 准备测试数据 继续使用上一篇的商品表，并添加一些新数据： -- 补充更多测试数据 INSERT INTO products (name, category, price, stock, created_at, description) VALUES ('iPhone 14', '手机', 5999.00, 100, '2024-09-01', 'Apple上代旗舰'), ('小米13', '手机', 3299.00, 150, '2024-03-15', '小米上代旗舰'), ('华为P60', '手机', 4999.00, 80, '2024-04-20', '华为影像旗舰'), ('戴尔XPS', '电脑', 9999.00, 25, '2024-05-10', '戴尔高端笔记本'), ('Surface Pro', '平板', 6999.00, 40, '2024-06-15', '微软二合一平板'); 二、单字段排序 2.1 数值字段排序 -- 按价格升序排列（从低到高） SELECT name, price FROM products ORDER BY price ASC; -- 等价写法（ASC可省略） SELECT name, price FROM products ORDER BY price; 结果： ...

引言 在第一阶段我们学习了基础的 SELECT 查询，但在实际开发中，简单的 SELECT * FROM table 远远不够。我们需要根据各种复杂的业务条件来过滤数据，比如： 查询价格在100-500元之间的商品 查找姓"张"的所有员工 筛选订单金额大于1000且状态为"已支付"的订单 查询手机号为空或邮箱未验证的用户 这些都需要通过 WHERE 子句配合各种运算符来实现。 为什么WHERE查询如此重要？ 数据过滤的核心：90%的查询都需要条件过滤 性能的关键：合理的WHERE条件能利用索引，大幅提升查询速度 业务逻辑的体现：复杂的业务规则需要通过条件组合来实现 数据安全：通过WHERE条件控制数据访问范围 本文将系统讲解WHERE子句的各类运算符和使用技巧，让你能够编写出精准、高效的条件查询。 一、WHERE子句基础 1.1 WHERE的基本语法 SELECT column1, column2, ... FROM table_name WHERE condition; 执行顺序： FROM：确定要查询的表 WHERE：过滤出符合条件的行 SELECT：选择要返回的列 1.2 准备测试数据 让我们创建一个商品表来演示各种查询： -- 创建商品表 CREATE TABLE products ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, category VARCHAR(50), price DECIMAL(10, 2), stock INT, created_at DATE, description TEXT ); -- 插入测试数据 INSERT INTO products (name, category, price, stock, created_at, description) VALUES ('iPhone 15 Pro', '手机', 7999.00, 50, '2024-09-20', 'Apple最新旗舰手机'), ('华为Mate 60', '手机', 6999.00, 80, '2024-08-15', '华为高端旗舰'), ('小米14', '手机', 3999.00, 120, '2024-10-01', '小米年度旗舰'), ('MacBook Pro', '电脑', 14999.00, 30, '2024-07-10', 'Apple笔记本电脑'), ('联想ThinkPad', '电脑', 8999.00, 45, '2024-06-20', '商务笔记本'), ('AirPods Pro', '耳机', 1999.00, 200, '2024-09-01', '苹果降噪耳机'), ('索尼WH-1000XM5', '耳机', 2499.00, 60, '2024-05-15', '索尼降噪耳机'), ('iPad Air', '平板', 4799.00, 70, '2024-08-20', 'Apple平板电脑'), ('小米平板6', '平板', 1999.00, 90, '2024-07-25', '小米高性价比平板'), ('罗技MX Master', '鼠标', 699.00, 150, '2024-04-10', '罗技旗舰鼠标'); 二、比较运算符 比较运算符用于比较两个值的大小关系。 ...