第04章：SQL基础 - DQL数据查询语言

MySQL教程 / 基础入门

第04章：SQL基础 - DQL数据查询语言

DQL (Data Query Language) 是SQL中最常用的部分，用于从数据库中查询数据

4.1 DQL概述

DQL主要语句：

SELECT：查询数据（最核心、最常用）

SELECT语句的执行顺序：

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

4.2 SELECT基本查询

4.2.1 查询所有列

-- 查询表中所有列的所有数据
SELECT * FROM users;

-- ⚠️ 生产环境不推荐使用SELECT *，原因：
-- 1. 性能差：查询不需要的列浪费资源
-- 2. 网络传输：传输不需要的数据
-- 3. 索引失效：无法使用覆盖索引

4.2.2 查询指定列

-- 推荐：只查询需要的列
SELECT id, username, email FROM users;

-- 查询结果中使用表达式
SELECT id, username, age, age + 1 AS next_year_age FROM users;

-- 查询结果中使用函数
SELECT id, username, UPPER(email) AS email_upper FROM users;

4.2.3 列别名

-- 使用AS关键字（推荐）
SELECT id AS user_id, username AS name FROM users;

-- 省略AS关键字
SELECT id user_id, username name FROM users;

-- 别名包含空格时使用引号
SELECT id AS 'user id', username AS '用户名' FROM users;

4.2.4 去重查询

-- 创建测试数据
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    product_name VARCHAR(100),
    amount DECIMAL(10,2)
);

INSERT INTO orders (user_id, product_name, amount) VALUES
(1, 'iPhone', 5999.00),
(1, 'iPad', 3999.00),
(2, 'iPhone', 5999.00),
(3, 'MacBook', 9999.00),
(2, 'AirPods', 1299.00);

-- 查询所有下单的用户ID（有重复）
SELECT user_id FROM orders;
-- 结果：1, 1, 2, 3, 2

-- 使用DISTINCT去重
SELECT DISTINCT user_id FROM orders;
-- 结果：1, 2, 3

-- 多列去重（组合去重）
SELECT DISTINCT user_id, product_name FROM orders;

4.3 WHERE条件过滤

4.3.1 比较运算符

-- 等于
SELECT * FROM users WHERE age = 25;

-- 不等于（两种写法）
SELECT * FROM users WHERE age != 25;
SELECT * FROM users WHERE age <> 25;

-- 大于、小于
SELECT * FROM users WHERE age > 25;
SELECT * FROM users WHERE age < 25;
SELECT * FROM users WHERE age >= 25;
SELECT * FROM users WHERE age <= 25;

4.3.2 逻辑运算符

-- AND：多个条件同时满足
SELECT * FROM users WHERE age >= 18 AND age <= 30;

-- OR：满足任一条件
SELECT * FROM users WHERE age < 18 OR age > 60;

-- NOT：取反
SELECT * FROM users WHERE NOT (age < 18);

-- 组合使用（注意优先级，AND优先于OR）
SELECT * FROM users WHERE (age < 18 OR age > 60) AND status = 1;

4.3.3 范围查询

-- BETWEEN...AND（包含边界值）
SELECT * FROM users WHERE age BETWEEN 18 AND 30;
-- 等价于：WHERE age >= 18 AND age <= 30

-- NOT BETWEEN
SELECT * FROM users WHERE age NOT BETWEEN 18 AND 30;

-- IN：在指定列表中
SELECT * FROM users WHERE age IN (18, 20, 25, 30);
-- 等价于：WHERE age = 18 OR age = 20 OR age = 25 OR age = 30

-- NOT IN
SELECT * FROM users WHERE age NOT IN (18, 20, 25);

4.3.4 模糊查询

-- LIKE模糊匹配
-- %：匹配任意多个字符（包括0个）
-- _：匹配单个字符

-- 查询姓张的用户
SELECT * FROM users WHERE username LIKE '张%';

-- 查询名字中包含"明"的用户
SELECT * FROM users WHERE username LIKE '%明%';

-- 查询姓张且名字是两个字的用户
SELECT * FROM users WHERE username LIKE '张_';

-- 查询姓张且名字是三个字的用户
SELECT * FROM users WHERE username LIKE '张__';

-- ⚠️ 性能警告：前导模糊查询无法使用索引
-- ❌ 慢：SELECT * FROM users WHERE username LIKE '%张%';
-- ✅ 快：SELECT * FROM users WHERE username LIKE '张%';

4.3.5 NULL值查询

-- 查询email为NULL的用户
SELECT * FROM users WHERE email IS NULL;

-- 查询email不为NULL的用户
SELECT * FROM users WHERE email IS NOT NULL;

-- ⚠️ 错误写法（NULL不能用=比较）
-- ❌ SELECT * FROM users WHERE email = NULL;  -- 查不到数据
-- ✅ SELECT * FROM users WHERE email IS NULL;

4.4 ORDER BY排序

4.4.1 单列排序

-- 升序排序（ASC，默认）
SELECT * FROM users ORDER BY age ASC;
SELECT * FROM users ORDER BY age;  -- 省略ASC

-- 降序排序（DESC）
SELECT * FROM users ORDER BY age DESC;

-- 按创建时间倒序（最新的在前）
SELECT * FROM users ORDER BY create_time DESC;

4.4.2 多列排序

-- 先按年龄升序，年龄相同时按创建时间降序
SELECT * FROM users ORDER BY age ASC, create_time DESC;

-- 先按状态，再按年龄
SELECT * FROM users ORDER BY status, age DESC;

4.4.3 按表达式排序

-- 按列别名排序
SELECT id, username, age, age + 1 AS next_age 
FROM users 
ORDER BY next_age DESC;

-- 按函数结果排序
SELECT * FROM users ORDER BY LENGTH(username) DESC;

4.4.4 NULL值排序

-- MySQL中NULL值排序规则：
-- ASC：NULL值排在最前面
-- DESC：NULL值排在最后面

SELECT * FROM users ORDER BY email ASC;  -- NULL在前
SELECT * FROM users ORDER BY email DESC; -- NULL在后

4.5 LIMIT分页

4.5.1 基本用法

-- 查询前5条记录
SELECT * FROM users LIMIT 5;

-- 查询前10条记录
SELECT * FROM users ORDER BY create_time DESC LIMIT 10;

4.5.2 分页查询

-- 语法：LIMIT offset, count
-- offset：偏移量（从0开始）
-- count：返回的记录数

-- 第1页（每页10条）
SELECT * FROM users LIMIT 0, 10;

-- 第2页（每页10条）
SELECT * FROM users LIMIT 10, 10;

-- 第3页（每页10条）
SELECT * FROM users LIMIT 20, 10;

-- 通用公式：第n页
-- LIMIT (n-1) * pageSize, pageSize

4.5.3 另一种语法

-- LIMIT count OFFSET offset（MySQL 5.7支持）
SELECT * FROM users LIMIT 10 OFFSET 0;  -- 第1页
SELECT * FROM users LIMIT 10 OFFSET 10; -- 第2页
SELECT * FROM users LIMIT 10 OFFSET 20; -- 第3页

4.5.4 深分页性能问题

-- ⚠️ 性能问题：深分页很慢
-- ❌ 慢：SELECT * FROM users LIMIT 1000000, 10;
-- 原因：MySQL需要扫描前1000010条记录，然后丢弃前1000000条

-- ✅ 优化方案1：使用主键过滤
SELECT * FROM users WHERE id > 1000000 LIMIT 10;

-- ✅ 优化方案2：延迟关联
SELECT u.* FROM users u
INNER JOIN (SELECT id FROM users LIMIT 1000000, 10) t ON u.id = t.id;

4.6 聚合函数

4.6.1 COUNT - 计数

-- 统计总记录数
SELECT COUNT(*) FROM users;

-- 统计指定列非NULL的记录数
SELECT COUNT(email) FROM users;

-- 统计去重后的记录数
SELECT COUNT(DISTINCT user_id) FROM orders;

-- COUNT(*)、COUNT(1)、COUNT(列名)的区别：
-- COUNT(*)：统计所有行（包括NULL）
-- COUNT(1)：统计所有行（包括NULL），性能与COUNT(*)相同
-- COUNT(列名)：统计该列非NULL的行数

4.6.2 SUM - 求和

-- 计算订单总金额
SELECT SUM(amount) FROM orders;

-- SUM会忽略NULL值
SELECT SUM(age) FROM users;  -- NULL不参与计算

-- 结合WHERE使用
SELECT SUM(amount) FROM orders WHERE user_id = 1;

4.6.3 AVG - 平均值

-- 计算平均年龄
SELECT AVG(age) FROM users;

-- 计算平均订单金额
SELECT AVG(amount) FROM orders;

-- AVG会忽略NULL值
-- 如果所有值都是NULL，返回NULL

4.6.4 MAX和MIN - 最大值和最小值

-- 查询最大年龄
SELECT MAX(age) FROM users;

-- 查询最小年龄
SELECT MIN(age) FROM users;

-- 查询最高订单金额
SELECT MAX(amount) FROM orders;

-- 查询最早的注册时间
SELECT MIN(create_time) FROM users;

4.6.5 聚合函数组合使用

-- 一次查询多个聚合结果
SELECT
    COUNT(*) AS total_users,
    AVG(age) AS avg_age,
    MAX(age) AS max_age,
    MIN(age) AS min_age
FROM users;

-- 订单统计
SELECT
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount,
    AVG(amount) AS avg_amount,
    MAX(amount) AS max_amount,
    MIN(amount) AS min_amount
FROM orders;

4.7 GROUP BY分组查询

4.7.1 基本分组

-- 按用户ID分组，统计每个用户的订单数
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id;

-- 按用户ID分组，统计每个用户的订单总金额
SELECT user_id, SUM(amount) AS total_amount
FROM orders
GROUP BY user_id;

4.7.2 多列分组

-- 按用户ID和产品名称分组
SELECT user_id, product_name, COUNT(*) AS buy_count
FROM orders
GROUP BY user_id, product_name;

-- 按日期分组统计
SELECT DATE(create_time) AS order_date, COUNT(*) AS order_count
FROM orders
GROUP BY DATE(create_time);

4.7.3 GROUP BY注意事项

-- ⚠️ 重要规则：SELECT后的列必须是：
-- 1. GROUP BY中的列
-- 2. 聚合函数

-- ❌ 错误示例：
-- SELECT user_id, username, COUNT(*) FROM orders GROUP BY user_id;
-- 错误原因：username不在GROUP BY中，也不是聚合函数

-- ✅ 正确示例：
SELECT user_id, COUNT(*) FROM orders GROUP BY user_id;

4.8 HAVING分组过滤

4.8.1 HAVING vs WHERE

-- WHERE：在分组前过滤（过滤行）
-- HAVING：在分组后过滤（过滤组）

-- 查询订单数量大于2的用户
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 2;

-- 查询总消费金额大于10000的用户
SELECT user_id, SUM(amount) AS total_amount
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 10000;

4.8.2 WHERE和HAVING结合使用

-- 查询2024年订单数量大于2的用户
SELECT user_id, COUNT(*) AS order_count
FROM orders
WHERE YEAR(create_time) = 2024  -- 分组前过滤
GROUP BY user_id
HAVING COUNT(*) > 2;            -- 分组后过滤

-- 执行顺序：WHERE → GROUP BY → HAVING

4.8.3 HAVING中使用别名

-- 可以在HAVING中使用SELECT中定义的别名
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING order_count > 2;  -- 使用别名

-- 也可以直接使用聚合函数
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 2;

4.9 完整查询示例

4.9.1 综合示例1：用户订单统计

-- 准备测试数据
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    age INT,
    city VARCHAR(50),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    product_name VARCHAR(100),
    amount DECIMAL(10,2),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (username, age, city) VALUES
('张三', 25, '北京'),
('李四', 30, '上海'),
('王五', 28, '广州'),
('赵六', 35, '深圳'),
('钱七', 22, '杭州');

INSERT INTO orders (user_id, product_name, amount) VALUES
(1, 'iPhone', 5999.00),
(1, 'iPad', 3999.00),
(2, 'MacBook', 9999.00),
(2, 'AirPods', 1299.00),
(3, 'iPhone', 5999.00),
(1, 'Apple Watch', 2999.00),
(4, 'iPad', 3999.00);

-- 查询每个用户的订单数量和总金额，按总金额降序
SELECT
    user_id,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount,
    AVG(amount) AS avg_amount
FROM orders
GROUP BY user_id
HAVING total_amount > 5000
ORDER BY total_amount DESC;

4.9.2 综合示例2：销售数据分析

-- 查询购买次数大于1次的用户，显示购买次数和总金额
SELECT
    user_id,
    COUNT(*) AS buy_times,
    SUM(amount) AS total_spent,
    MAX(amount) AS max_order,
    MIN(amount) AS min_order
FROM orders
WHERE amount > 1000  -- 只统计金额大于1000的订单
GROUP BY user_id
HAVING buy_times > 1  -- 购买次数大于1次
ORDER BY total_spent DESC
LIMIT 10;  -- 只显示前10名

4.9.3 综合示例3：日期统计

-- 按日期统计每天的订单数量和金额
SELECT
    DATE(create_time) AS order_date,
    COUNT(*) AS order_count,
    SUM(amount) AS daily_amount,
    AVG(amount) AS avg_amount
FROM orders
GROUP BY DATE(create_time)
ORDER BY order_date DESC;

-- 按月份统计
SELECT
    DATE_FORMAT(create_time, '%Y-%m') AS order_month,
    COUNT(*) AS order_count,
    SUM(amount) AS monthly_amount
FROM orders
GROUP BY DATE_FORMAT(create_time, '%Y-%m')
ORDER BY order_month DESC;

4.10 查询执行顺序详解

4.10.1 SQL执行顺序

-- 书写顺序：
SELECT column_list
FROM table_name
WHERE condition
GROUP BY column_list
HAVING condition
ORDER BY column_list
LIMIT offset, count;

-- 实际执行顺序：
-- 1. FROM：确定数据来源
-- 2. WHERE：过滤行
-- 3. GROUP BY：分组
-- 4. HAVING：过滤分组
-- 5. SELECT：选择列
-- 6. ORDER BY：排序
-- 7. LIMIT：限制结果数量

4.10.2 理解执行顺序的重要性

-- 示例：为什么WHERE不能使用聚合函数？
-- ❌ 错误：
SELECT user_id, COUNT(*) AS cnt
FROM orders
WHERE COUNT(*) > 2  -- 错误！WHERE执行时还没有分组
GROUP BY user_id;

-- ✅ 正确：
SELECT user_id, COUNT(*) AS cnt
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 2;  -- HAVING在分组后执行

-- 示例：为什么WHERE不能使用SELECT中的别名？
-- ❌ 错误：
SELECT age, age + 1 AS next_age
FROM users
WHERE next_age > 30;  -- 错误！WHERE执行时还没有SELECT

-- ✅ 正确：
SELECT age, age + 1 AS next_age
FROM users
WHERE age + 1 > 30;  -- 直接使用表达式

4.11 常见问题与最佳实践

4.11.1 性能优化建议

-- ✅ 1. 避免SELECT *
-- ❌ 慢：SELECT * FROM users WHERE id = 1;
-- ✅ 快：SELECT id, username, email FROM users WHERE id = 1;

-- ✅ 2. 避免在WHERE中使用函数
-- ❌ 慢：SELECT * FROM users WHERE YEAR(create_time) = 2024;
-- ✅ 快：SELECT * FROM users WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';

-- ✅ 3. 避免前导模糊查询
-- ❌ 慢：SELECT * FROM users WHERE username LIKE '%张%';
-- ✅ 快：SELECT * FROM users WHERE username LIKE '张%';

-- ✅ 4. 使用LIMIT限制结果集
SELECT * FROM users ORDER BY create_time DESC LIMIT 100;

-- ✅ 5. 合理使用索引
-- 在WHERE、ORDER BY、GROUP BY的列上创建索引

4.11.2 常见错误

-- 错误1：NULL值比较
-- ❌ SELECT * FROM users WHERE email = NULL;
-- ✅ SELECT * FROM users WHERE email IS NULL;

-- 错误2：GROUP BY后选择非聚合列
-- ❌ SELECT user_id, username, COUNT(*) FROM orders GROUP BY user_id;
-- ✅ SELECT user_id, COUNT(*) FROM orders GROUP BY user_id;

-- 错误3：WHERE中使用聚合函数
-- ❌ SELECT user_id FROM orders WHERE COUNT(*) > 2 GROUP BY user_id;
-- ✅ SELECT user_id FROM orders GROUP BY user_id HAVING COUNT(*) > 2;

-- 错误4：ORDER BY使用不存在的列
-- ❌ SELECT id, username FROM users ORDER BY age;  -- age未在SELECT中
-- ✅ SELECT id, username, age FROM users ORDER BY age;

4.12 本章总结

本章学习内容：

✅ SELECT基本查询（指定列、别名、去重）
✅ WHERE条件过滤（比较、逻辑、范围、模糊、NULL）
✅ ORDER BY排序（单列、多列、表达式）
✅ LIMIT分页（基本用法、深分页优化）
✅ 聚合函数（COUNT、SUM、AVG、MAX、MIN）
✅ GROUP BY分组查询
✅ HAVING分组过滤
✅ SQL执行顺序

重点掌握：

SELECT只查询需要的列，避免SELECT *
WHERE条件过滤的各种运算符
LIKE模糊查询的性能问题
NULL值必须用IS NULL判断
聚合函数的使用场景
GROUP BY和HAVING的区别
SQL的执行顺序

性能优化要点：

避免SELECT *
避免在WHERE中使用函数
避免前导模糊查询（LIKE '%xxx'）
深分页使用主键过滤优化
合理使用索引

下一章预告： SQL进阶查询（多表连接、子查询、联合查询）

练习题

基础练习

查询年龄在20-30岁之间的用户
查询用户名以"张"开头的用户
查询email不为空的用户，按年龄降序排列
查询前10个最新注册的用户
统计用户总数、平均年龄、最大年龄、最小年龄

进阶练习

按城市分组，统计每个城市的用户数量
查询用户数量大于5的城市
查询每个用户的订单数量和总消费金额
查询消费总金额大于10000的用户
按日期统计每天的订单数量和总金额

综合练习

查询2024年消费金额前10名的用户
查询购买过iPhone的用户ID（去重）
统计每个产品的销售数量和总金额，按销售额降序
查询平均订单金额大于5000的用户
查询每个月的订单数量和总金额，按月份降序

继续学习： 第05章：SQL进阶查询

第04章：SQL基础 - DQL数据查询语言 - MySQL教程 - jackssybin 的个人博客

第04章：SQL基础 - DQL数据查询语言

第04章：SQL基础 - DQL数据查询语言

4.1 DQL概述

4.2 SELECT基本查询

4.2.1 查询所有列

4.2.2 查询指定列

4.2.3 列别名

4.2.4 去重查询

4.3 WHERE条件过滤

4.3.1 比较运算符

4.3.2 逻辑运算符

4.3.3 范围查询

4.3.4 模糊查询

4.3.5 NULL值查询

4.4 ORDER BY排序

4.4.1 单列排序

4.4.2 多列排序

4.4.3 按表达式排序

4.4.4 NULL值排序

4.5 LIMIT分页

4.5.1 基本用法

4.5.2 分页查询

4.5.3 另一种语法

4.5.4 深分页性能问题

4.6 聚合函数

4.6.1 COUNT - 计数

4.6.2 SUM - 求和

4.6.3 AVG - 平均值

4.6.4 MAX和MIN - 最大值和最小值

4.6.5 聚合函数组合使用

4.7 GROUP BY分组查询

4.7.1 基本分组

4.7.2 多列分组

4.7.3 GROUP BY注意事项

4.8 HAVING分组过滤

4.8.1 HAVING vs WHERE

4.8.2 WHERE和HAVING结合使用

4.8.3 HAVING中使用别名

4.9 完整查询示例

4.9.1 综合示例1：用户订单统计

4.9.2 综合示例2：销售数据分析

4.9.3 综合示例3：日期统计

4.10 查询执行顺序详解

4.10.1 SQL执行顺序

4.10.2 理解执行顺序的重要性

4.11 常见问题与最佳实践

4.11.1 性能优化建议

4.11.2 常见错误

4.12 本章总结

练习题

基础练习

进阶练习

综合练习