🔍 MySQL 查询数据

SELECT 语句概述

SELECT 语句是 SQL 中最重要的语句之一，用于从数据库表中检索数据。MySQL 的 SELECT 语句功能强大，支持复杂的查询操作、数据聚合、连接查询等。

基本 SELECT 语法

查询所有列

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

-- 查询指定表的所有数据（带数据库名）
SELECT * FROM mydb.users;

-- 查询结果示例
+----+----------+-------------------+-----+---------------------+
| id | username | email             | age | created_at          |
+----+----------+-------------------+-----+---------------------+
|  1 | john_doe | [email protected]  |  25 | 2024-01-15 10:30:00 |
|  2 | jane     | [email protected]  |  30 | 2024-01-16 14:20:00 |
+----+----------+-------------------+-----+---------------------+

查询指定列

-- 查询特定列
SELECT username, email FROM users;

-- 查询多个列（指定顺序）
SELECT id, username, age, email FROM users;

-- 使用表别名
SELECT u.username, u.email FROM users u;

-- 查询结果示例
+----------+-------------------+
| username | email             |
+----------+-------------------+
| john_doe | [email protected]  |
| jane     | [email protected]  |
+----------+-------------------+

列别名和表达式

使用列别名

-- 使用 AS 关键字定义别名
SELECT 
    username AS '用户名',
    email AS '邮箱地址',
    age AS '年龄'
FROM users;

-- 省略 AS 关键字
SELECT 
    username '用户名',
    email '邮箱地址',
    YEAR(created_at) '注册年份'
FROM users;

-- 使用反引号处理特殊字符
SELECT 
    username AS `User Name`,
    email AS `E-mail Address`
FROM users;

计算字段和表达式

-- 数学运算
SELECT 
    product_name,
    price,
    quantity,
    price * quantity AS total_value,
    price * 0.9 AS discounted_price
FROM products;

-- 字符串连接
SELECT 
    CONCAT(first_name, ' ', last_name) AS full_name,
    CONCAT(username, '@company.com') AS company_email
FROM users;

-- 条件表达式
SELECT 
    username,
    age,
    CASE 
        WHEN age < 18 THEN '未成年'
        WHEN age BETWEEN 18 AND 65 THEN '成年人'
        ELSE '老年人'
    END AS age_group
FROM users;

数据过滤和排序

DISTINCT 去重

-- 去除重复值
SELECT DISTINCT city FROM users;

-- 多列去重
SELECT DISTINCT city, country FROM users;

-- 计算唯一值数量
SELECT COUNT(DISTINCT city) AS unique_cities FROM users;

-- 去重后排序
SELECT DISTINCT age FROM users ORDER BY age;

LIMIT 限制结果

-- 限制返回行数
SELECT * FROM users LIMIT 5;

-- 分页查询（跳过前10行，返回5行）
SELECT * FROM users LIMIT 10, 5;

-- 使用 OFFSET（MySQL 8.0+）
SELECT * FROM users LIMIT 5 OFFSET 10;

-- 获取最新的10条记录
SELECT * FROM orders 
ORDER BY created_at DESC 
LIMIT 10;

ORDER BY 排序

-- 单列排序
SELECT * FROM users ORDER BY age;
SELECT * FROM users ORDER BY age DESC;

-- 多列排序
SELECT * FROM users 
ORDER BY city ASC, age DESC;

-- 使用列位置排序
SELECT username, email, age FROM users 
ORDER BY 3 DESC;  -- 按第3列（age）降序

-- 使用表达式排序
SELECT * FROM products 
ORDER BY price * quantity DESC;

-- NULL 值排序
SELECT * FROM users 
ORDER BY last_login DESC NULLS LAST;

聚合函数

基本聚合函数

-- 计数
SELECT COUNT(*) AS total_users FROM users;
SELECT COUNT(email) AS users_with_email FROM users;
SELECT COUNT(DISTINCT city) AS unique_cities FROM users;

-- 求和
SELECT SUM(price) AS total_price FROM products;
SELECT SUM(quantity * price) AS total_value FROM order_items;

-- 平均值
SELECT AVG(age) AS average_age FROM users;
SELECT AVG(price) AS average_price FROM products;

-- 最大值和最小值
SELECT MAX(age) AS oldest, MIN(age) AS youngest FROM users;
SELECT MAX(created_at) AS latest_order FROM orders;

GROUP BY 分组

-- 按单列分组
SELECT city, COUNT(*) AS user_count 
FROM users 
GROUP BY city;

-- 按多列分组
SELECT city, country, COUNT(*) AS user_count 
FROM users 
GROUP BY city, country;

-- 分组后排序
SELECT city, COUNT(*) AS user_count 
FROM users 
GROUP BY city 
ORDER BY user_count DESC;

-- 复杂分组查询
SELECT 
    YEAR(created_at) AS year,
    MONTH(created_at) AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_sales
FROM orders 
GROUP BY YEAR(created_at), MONTH(created_at)
ORDER BY year DESC, month DESC;

HAVING 子句

-- HAVING 过滤分组结果
SELECT city, COUNT(*) AS user_count 
FROM users 
GROUP BY city 
HAVING COUNT(*) > 5;

-- 复杂 HAVING 条件
SELECT 
    category_id,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price
FROM products 
GROUP BY category_id 
HAVING COUNT(*) > 3 AND AVG(price) > 100;

-- HAVING 与 WHERE 结合
SELECT 
    user_id,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_spent
FROM orders 
WHERE status = 'completed'
GROUP BY user_id 
HAVING COUNT(*) >= 5 AND SUM(total_amount) > 1000;

子查询

标量子查询

-- 返回单个值的子查询
SELECT username, age 
FROM users 
WHERE age > (SELECT AVG(age) FROM users);

-- 在 SELECT 列表中使用子查询
SELECT 
    username,
    age,
    (SELECT AVG(age) FROM users) AS avg_age,
    age - (SELECT AVG(age) FROM users) AS age_diff
FROM users;

-- 相关子查询
SELECT 
    p.product_name,
    p.price,
    (SELECT AVG(price) FROM products p2 WHERE p2.category_id = p.category_id) AS category_avg_price
FROM products p;

EXISTS 子查询

-- 检查是否存在
SELECT username 
FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id
);

-- NOT EXISTS
SELECT username 
FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id
);

-- 复杂 EXISTS 查询
SELECT product_name 
FROM products p
WHERE EXISTS (
    SELECT 1 FROM order_items oi 
    JOIN orders o ON oi.order_id = o.id
    WHERE oi.product_id = p.id 
    AND o.created_at >= '2024-01-01'
);

IN 子查询

-- IN 子查询
SELECT username 
FROM users 
WHERE id IN (
    SELECT DISTINCT user_id 
    FROM orders 
    WHERE total_amount > 1000
);

-- NOT IN 子查询
SELECT product_name 
FROM products 
WHERE id NOT IN (
    SELECT DISTINCT product_id 
    FROM order_items 
    WHERE product_id IS NOT NULL
);

-- 多列 IN 子查询
SELECT * FROM users 
WHERE (city, country) IN (
    SELECT city, country 
    FROM popular_locations
);

连接查询基础

内连接 (INNER JOIN)

-- 基本内连接
SELECT 
    u.username,
    u.email,
    o.id AS order_id,
    o.total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 多表连接
SELECT 
    u.username,
    o.id AS order_id,
    p.product_name,
    oi.quantity,
    oi.unit_price
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;

外连接 (LEFT/RIGHT JOIN)

-- 左外连接（显示所有用户，包括没有订单的）
SELECT 
    u.username,
    u.email,
    COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username, u.email;

-- 右外连接
SELECT 
    u.username,
    o.id AS order_id,
    o.total_amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- 查找没有订单的用户
SELECT u.username 
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

窗口函数 (MySQL 8.0+)

排名函数

-- ROW_NUMBER() 行号
SELECT 
    username,
    age,
    ROW_NUMBER() OVER (ORDER BY age DESC) AS row_num
FROM users;

-- RANK() 排名（相同值相同排名，后续排名跳跃）
SELECT 
    product_name,
    price,
    RANK() OVER (ORDER BY price DESC) AS price_rank
FROM products;

-- DENSE_RANK() 密集排名（相同值相同排名，后续排名连续）
SELECT 
    username,
    score,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM user_scores;

聚合窗口函数

-- 累计求和
SELECT 
    order_date,
    daily_sales,
    SUM(daily_sales) OVER (ORDER BY order_date) AS cumulative_sales
FROM daily_sales_summary;

-- 移动平均
SELECT 
    order_date,
    daily_sales,
    AVG(daily_sales) OVER (
        ORDER BY order_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7_days
FROM daily_sales_summary;

-- 分组内排名
SELECT 
    category_name,
    product_name,
    price,
    RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS category_price_rank
FROM products p
JOIN categories c ON p.category_id = c.id;

常用函数

字符串函数

-- 字符串操作
SELECT 
    username,
    UPPER(username) AS upper_name,
    LOWER(email) AS lower_email,
    LENGTH(username) AS name_length,
    SUBSTRING(email, 1, LOCATE('@', email) - 1) AS email_prefix,
    CONCAT(first_name, ' ', last_name) AS full_name
FROM users;

-- 字符串查找和替换
SELECT 
    product_name,
    REPLACE(product_name, 'iPhone', 'Apple iPhone') AS updated_name,
    LEFT(product_name, 10) AS short_name,
    RIGHT(product_name, 5) AS suffix
FROM products;

日期时间函数

-- 日期时间操作
SELECT 
    username,
    created_at,
    DATE(created_at) AS created_date,
    YEAR(created_at) AS created_year,
    MONTH(created_at) AS created_month,
    DAYOFWEEK(created_at) AS day_of_week,
    DATEDIFF(NOW(), created_at) AS days_since_created
FROM users;

-- 日期计算
SELECT 
    order_id,
    order_date,
    DATE_ADD(order_date, INTERVAL 7 DAY) AS delivery_date,
    DATE_SUB(order_date, INTERVAL 1 MONTH) AS one_month_ago
FROM orders;

数学函数

-- 数学运算
SELECT 
    product_name,
    price,
    ROUND(price, 2) AS rounded_price,
    CEIL(price) AS ceiling_price,
    FLOOR(price) AS floor_price,
    ABS(price - 100) AS price_diff_from_100,
    POWER(price, 2) AS price_squared
FROM products;

-- 随机和统计
SELECT 
    RAND() AS random_number,
    ROUND(RAND() * 100) AS random_percentage;

性能优化技巧

🚀 查询性能优化使用索引：在 WHERE、ORDER BY、JOIN 条件中使用索引列避免 SELECT *：只查询需要的列合理使用 LIMIT：限制返回的行数优化 JOIN：确保连接条件使用索引避免函数：在 WHERE 条件中避免对列使用函数

-- 性能优化示例

-- 不好的查询（全表扫描）
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- 优化后的查询（使用索引）
SELECT id, username, email FROM users 
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- 使用 EXPLAIN 分析查询
EXPLAIN SELECT u.username, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

实际应用示例

电商数据分析

-- 销售报表查询
SELECT 
    DATE(o.created_at) AS sale_date,
    COUNT(DISTINCT o.id) AS order_count,
    COUNT(DISTINCT o.user_id) AS customer_count,
    SUM(o.total_amount) AS daily_revenue,
    AVG(o.total_amount) AS avg_order_value
FROM orders o
WHERE o.status = 'completed'
  AND o.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY DATE(o.created_at)
ORDER BY sale_date DESC;

-- 热销产品分析
SELECT 
    p.product_name,
    c.category_name,
    SUM(oi.quantity) AS total_sold,
    SUM(oi.quantity * oi.unit_price) AS total_revenue,
    COUNT(DISTINCT oi.order_id) AS order_count
FROM products p
JOIN categories c ON p.category_id = c.id
JOIN order_items oi ON p.id = oi.product_id
JOIN orders o ON oi.order_id = o.id
WHERE o.status = 'completed'
  AND o.created_at >= DATE_SUB(NOW(), INTERVAL 90 DAY)
GROUP BY p.id, p.product_name, c.category_name
ORDER BY total_sold DESC
LIMIT 20;

用户行为分析

-- 用户活跃度分析
SELECT 
    u.username,
    u.created_at AS registration_date,
    COUNT(o.id) AS total_orders,
    SUM(o.total_amount) AS total_spent,
    MAX(o.created_at) AS last_order_date,
    DATEDIFF(NOW(), MAX(o.created_at)) AS days_since_last_order,
    CASE 
        WHEN MAX(o.created_at) >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 'Active'
        WHEN MAX(o.created_at) >= DATE_SUB(NOW(), INTERVAL 90 DAY) THEN 'Inactive'
        ELSE 'Churned'
    END AS user_status
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed'
GROUP BY u.id, u.username, u.created_at
ORDER BY total_spent DESC;

📋 查询最佳实践明确需求：只查询必要的列和行使用索引：确保查询条件能够使用索引避免 N+1：使用 JOIN 而不是循环查询分页处理：大结果集使用 LIMIT 分页监控性能：使用 EXPLAIN 分析查询计划缓存结果：对于复杂查询考虑结果缓存定期优化：根据数据增长调整查询策略