【MySQL】数据表基础查询

于 2025-05-13 05:00:00 发布
阅读量1.2k 收藏 15
点赞数 20
CC 4.0 BY-SA版权
分类专栏： MySQL 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/Go_ahead1025/article/details/147908053

在这里插入图片描述

个人主页：Guiat
归属专栏：MySQL

在这里插入图片描述

文章目录

正文

1. 查询概述

数据查询是数据库中最常用的操作，它允许我们从数据库表中检索和分析数据。MySQL提供了强大而灵活的查询功能，可以满足各种业务需求。

1.1 查询的作用

  • 检索所需数据
  • 筛选符合条件的记录
  • 对数据进行排序和分组
  • 聚合和计算数据
  • 将多个表的数据关联起来

1.2 MySQL查询的基本结构

MySQL查询主要基于SQL的SELECT语句，基本结构如下：
MySQL查询
SELECT子句
FROM子句
WHERE子句
GROUP BY子句
HAVING子句
ORDER BY子句
LIMIT子句

2. SELECT 语句基础

2.1 查询所有列

最基本的查询是检索表中的所有列和所有行：

-- 查询表中的所有列
SELECT * FROM customers;

-- 查询表中的指定列
SELECT first_name, last_name, email FROM customers;

2.2 列别名

可以为查询结果中的列指定别名，使输出结果更具可读性：

-- 使用AS关键字指定列别名
SELECT 
    product_id AS ID,
    product_name AS Name,
    price AS "Unit Price"
FROM products;

-- 可以省略AS关键字
SELECT 
    product_id ID,
    product_name "Product Name",
    price "Price ($)"
FROM products;

2.3 去除重复值

使用DISTINCT关键字可以去除查询结果中的重复行：

-- 查询不同的城市
SELECT DISTINCT city FROM customers;

-- 查询不同的城市和国家组合
SELECT DISTINCT city, country FROM customers;

2.4 计算字段

可以在查询中使用表达式创建计算字段：

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

-- 字符串连接
SELECT 
    CONCAT(first_name, ' ', last_name) AS full_name,
    email
FROM customers;

-- 日期计算
SELECT 
    order_id,
    order_date,
    ship_date,
    DATEDIFF(ship_date, order_date) AS processing_days
FROM orders;

3. WHERE 子句与条件过滤

3.1 基本条件操作符

WHERE子句允许我们根据条件筛选数据：

-- 等于条件
SELECT * FROM products WHERE price = 19.99;

-- 不等于条件
SELECT * FROM products WHERE category_id <> 5;

-- 大于、小于条件
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE stock < 10;

-- 大于等于、小于等于条件
SELECT * FROM products WHERE price >= 50 AND price <= 100;

3.2 范围条件 (BETWEEN)

BETWEEN操作符用于筛选在指定范围内的值：

-- 筛选价格在50到100之间的产品
SELECT * FROM products WHERE price BETWEEN 50 AND 100;

-- 筛选日期范围
SELECT * FROM orders 
WHERE order_date BETWEEN '2023-01-01' AND '2023-03-31';

-- 排除范围
SELECT * FROM products 
WHERE price NOT BETWEEN 50 AND 100;

3.3 列表条件 (IN)

IN操作符用于匹配指定列表中的任何值：

-- 筛选特定分类的产品
SELECT * FROM products 
WHERE category_id IN (1, 3, 5);

-- 筛选特定状态的订单
SELECT * FROM orders 
WHERE status IN ('pending', 'processing', 'shipped');

-- 排除列表
SELECT * FROM products 
WHERE category_id NOT IN (2, 4, 6);

3.4 模糊匹配 (LIKE)

LIKE操作符用于模式匹配，通常与通配符一起使用：

-- 筛选名字以'A'开头的客户
SELECT * FROM customers 
WHERE first_name LIKE 'A%';

-- 筛选包含'phone'的产品
SELECT * FROM products 
WHERE product_name LIKE '%phone%';

-- 筛选以特定模式匹配的电子邮件
SELECT * FROM customers 
WHERE email LIKE '%@gmail.com';

-- 使用下划线通配符匹配单个字符
SELECT * FROM products 
WHERE product_code LIKE 'AB_123';

通配符说明：

  • %: 匹配任意数量的字符（包括零个字符）
  • _: 匹配单个字符

3.5 NULL值检查

NULL表示"无值"或"未知值"，需要特殊处理：

-- 查找缺少电话号码的客户
SELECT * FROM customers 
WHERE phone IS NULL;

-- 查找有电话号码的客户
SELECT * FROM customers 
WHERE phone IS NOT NULL;

-- 查找没有完成日期的订单
SELECT * FROM orders 
WHERE completion_date IS NULL;

3.6 逻辑运算符

可以使用逻辑运算符组合多个条件：

-- 使用AND组合条件
SELECT * FROM products 
WHERE price > 100 AND stock > 0;

-- 使用OR组合条件
SELECT * FROM customers 
WHERE country = 'USA' OR country = 'Canada';

-- 使用NOT取反条件
SELECT * FROM products 
WHERE NOT category_id = 5;

-- 混合使用逻辑运算符（注意优先级）
SELECT * FROM products 
WHERE (category_id = 1 OR category_id = 2) 
AND price < 50;

4. 排序查询结果

4.1 基本排序 (ORDER BY)

ORDER BY子句用于对查询结果进行排序：

-- 按单一列升序排序（默认）
SELECT * FROM products 
ORDER BY price;

-- 显式指定升序排序
SELECT * FROM products 
ORDER BY price ASC;

-- 降序排序
SELECT * FROM customers 
ORDER BY last_name DESC;

-- 按多列排序
SELECT * FROM customers 
ORDER BY country, city, last_name;

-- 混合排序方向
SELECT * FROM products 
ORDER BY category_id ASC, price DESC;

4.2 按别名排序

可以按照SELECT中定义的列别名排序：

SELECT 
    product_id,
    product_name,
    price * 1.1 AS increased_price
FROM products
ORDER BY increased_price DESC;

4.3 按位置排序

可以使用列在SELECT中的位置来排序（不推荐，可读性低）：

-- 按第3列排序
SELECT product_id, product_name, price, stock
FROM products
ORDER BY 3 DESC;

4.4 NULL值排序

MySQL中，NULL值默认排在最前面：

-- NULL值在前（默认）
SELECT * FROM employees
ORDER BY manager_id;

-- NULL值在后
SELECT * FROM employees
ORDER BY manager_id IS NULL, manager_id;

5. 限制查询结果

5.1 LIMIT 子句

LIMIT子句用于限制返回的结果行数：

-- 只返回前5个客户
SELECT * FROM customers
LIMIT 5;

-- 指定偏移量和行数
SELECT * FROM customers
LIMIT 10, 5; -- 跳过前10行，返回接下来的5行

-- 替代语法
SELECT * FROM customers
LIMIT 5 OFFSET 10; -- 同上例

5.2 分页查询

通常使用LIMIT实现分页功能：

-- 第1页（每页10项）
SELECT * FROM products
ORDER BY product_name
LIMIT 0, 10;

-- 第2页（每页10项）
SELECT * FROM products
ORDER BY product_name
LIMIT 10, 10;

-- 第n页的通用公式
-- LIMIT (page_number - 1) * page_size, page_size

6. 聚合函数与分组

6.1 常用聚合函数

聚合函数对一组值执行计算并返回单一值：

-- 计数
SELECT COUNT(*) FROM customers;
SELECT COUNT(customer_id) FROM customers;
SELECT COUNT(DISTINCT country) FROM customers;

-- 求和
SELECT SUM(price * quantity) FROM order_items;

-- 平均值
SELECT AVG(price) FROM products;

-- 最大值和最小值
SELECT MAX(price), MIN(price) FROM products;

-- 组合使用多个聚合函数
SELECT 
    COUNT(*) AS total_products,
    AVG(price) AS average_price,
    MIN(price) AS lowest_price,
    MAX(price) AS highest_price,
    SUM(stock) AS total_inventory
FROM products;

6.2 GROUP BY 子句

GROUP BY子句将查询结果分组，然后对每个组应用聚合函数：

-- 按国家分组计算客户数量
SELECT country, COUNT(*) AS customer_count
FROM customers
GROUP BY country;

-- 按多个列分组
SELECT country, city, COUNT(*) AS customer_count
FROM customers
GROUP BY country, city;

-- 按产品类别统计价格信息
SELECT 
    category_id,
    COUNT(*) AS product_count,
    AVG(price) AS average_price,
    MIN(price) AS lowest_price,
    MAX(price) AS highest_price
FROM products
GROUP BY category_id;

6.3 HAVING 子句

HAVING子句用于过滤分组后的结果：

-- 找出客户数量超过5的国家
SELECT country, COUNT(*) AS customer_count
FROM customers
GROUP BY country
HAVING COUNT(*) > 5;

-- 找出平均价格超过100的产品类别
SELECT 
    category_id,
    AVG(price) AS average_price
FROM products
GROUP BY category_id
HAVING AVG(price) > 100;

-- 结合WHERE和HAVING
SELECT 
    category_id,
    COUNT(*) AS product_count,
    AVG(price) AS average_price
FROM products
WHERE price > 10
GROUP BY category_id
HAVING COUNT(*) >= 3;

6.4 分组和排序结合

通常结合GROUP BY和ORDER BY来排序分组结果：

-- 按订单总额对客户排序
SELECT 
    customer_id,
    COUNT(*) AS order_count,
    SUM(total) AS total_spent
FROM orders
GROUP BY customer_id
ORDER BY total_spent DESC;

-- 找出销量最高的产品类别
SELECT 
    category_id,
    SUM(quantity_sold) AS total_sold
FROM sales
GROUP BY category_id
ORDER BY total_sold DESC
LIMIT 5;

7. 查询中的子查询

7.1 子查询基础

子查询是嵌套在另一个查询内部的SELECT语句：

-- 查找高于平均价格的产品
SELECT *
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
);

-- 查找订单数量最多的客户
SELECT *
FROM customers
WHERE customer_id = (
    SELECT customer_id
    FROM orders
    GROUP BY customer_id
    ORDER BY COUNT(*) DESC
    LIMIT 1
);

7.2 IN 子查询

使用IN操作符与子查询结合：

-- 查找有订单的客户
SELECT *
FROM customers
WHERE customer_id IN (
    SELECT DISTINCT customer_id
    FROM orders
);

-- 查找有高价产品的类别
SELECT *
FROM categories
WHERE category_id IN (
    SELECT category_id
    FROM products
    WHERE price > 1000
);

7.3 比较运算符与子查询

可以使用比较运算符与子查询结合：

-- 查找大于平均库存的产品
SELECT *
FROM products
WHERE stock > (
    SELECT AVG(stock)
    FROM products
);

-- 使用ANY操作符
SELECT *
FROM products
WHERE price > ANY (
    SELECT price
    FROM products
    WHERE category_id = 5
);

-- 使用ALL操作符
SELECT *
FROM products
WHERE price > ALL (
    SELECT price
    FROM products
    WHERE category_id = 2
);

7.4 EXISTS 子查询

EXISTS用于检查子查询是否返回任何行：

-- 查找有订单的客户
SELECT *
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

-- 查找没有订单的客户
SELECT *
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

7.5 相关子查询

相关子查询依赖于外部查询的数据：

-- 查找每个类别中价格最高的产品
SELECT *
FROM products p1
WHERE price = (
    SELECT MAX(price)
    FROM products p2
    WHERE p2.category_id = p1.category_id
);

-- 查找高于其类别平均价格的产品
SELECT *
FROM products p
WHERE price > (
    SELECT AVG(price)
    FROM products
    WHERE category_id = p.category_id
);

8. 多表联接查询

8.1 内联接 (INNER JOIN)

内联接返回两个表中匹配的行：

-- 客户及其订单
SELECT 
    c.customer_id,
    c.first_name,
    c.last_name,
    o.order_id,
    o.order_date,
    o.total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

-- 多表联接
SELECT 
    o.order_id,
    c.first_name,
    c.last_name,
    p.product_name,
    oi.quantity,
    oi.price
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id;

8.2 左外联接 (LEFT JOIN)

左外联接返回左表所有行，即使右表没有匹配行：

-- 所有客户及其订单（包括没有订单的客户）
SELECT 
    c.customer_id,
    c.first_name,
    c.last_name,
    o.order_id,
    o.order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

-- 查找没有订单的客户
SELECT 
    c.customer_id,
    c.first_name,
    c.last_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

8.3 右外联接 (RIGHT JOIN)

右外联接返回右表所有行，即使左表没有匹配行：

-- 所有订单及其客户（包括没有匹配客户的订单）
SELECT 
    o.order_id,
    o.order_date,
    c.customer_id,
    c.first_name,
    c.last_name
FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.customer_id;

8.4 自联接 (Self Join)

自联接是表与自身的联接：

-- 查找员工及其经理
SELECT 
    e1.employee_id,
    e1.first_name,
    e1.last_name,
    e2.first_name AS manager_first_name,
    e2.last_name AS manager_last_name
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id;

-- 查找同一城市的客户
SELECT 
    c1.customer_id,
    c1.first_name,
    c1.last_name,
    c2.customer_id AS related_customer_id,
    c2.first_name AS related_first_name,
    c2.last_name AS related_last_name
FROM customers c1
INNER JOIN customers c2 ON c1.city = c2.city
WHERE c1.customer_id < c2.customer_id;

8.5 交叉联接 (CROSS JOIN)

交叉联接返回两个表的笛卡尔积（所有可能的组合）：

-- 产品与颜色的所有组合
SELECT 
    p.product_id,
    p.product_name,
    c.color_name
FROM products p
CROSS JOIN colors c;

9. 常用查询案例

9.1 销售数据分析

-- 按月统计销售额
SELECT 
    YEAR(order_date) AS year,
    MONTH(order_date) AS month,
    SUM(total) AS monthly_sales
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY year, month;

-- 销售额最高的客户
SELECT 
    c.customer_id,
    c.first_name,
    c.last_name,
    SUM(o.total) AS total_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY total_spent DESC
LIMIT 10;

-- 产品销售排行
SELECT 
    p.product_id,
    p.product_name,
    SUM(oi.quantity) AS total_quantity,
    SUM(oi.quantity * oi.price) AS total_revenue
FROM products p
INNER JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name
ORDER BY total_revenue DESC;

9.2 库存管理查询

-- 库存不足的产品
SELECT 
    product_id,
    product_name,
    stock,
    reorder_level
FROM products
WHERE stock <= reorder_level
ORDER BY (reorder_level - stock) DESC;

-- 库存价值统计
SELECT 
    category_id,
    SUM(stock * price) AS inventory_value
FROM products
GROUP BY category_id
ORDER BY inventory_value DESC;

-- 滞销产品分析
SELECT 
    p.product_id,
    p.product_name,
    p.stock,
    COALESCE(SUM(oi.quantity), 0) AS total_sold
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.order_id AND o.order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 3 MONTH)
GROUP BY p.product_id, p.product_name, p.stock
HAVING total_sold < 5 AND p.stock > 20
ORDER BY total_sold, p.stock DESC;

9.3 客户行为分析

-- 客户购买频率
SELECT 
    c.customer_id,
    c.first_name,
    c.last_name,
    COUNT(o.order_id) AS order_count,
    MIN(o.order_date) AS first_order,
    MAX(o.order_date) AS last_order,
    DATEDIFF(MAX(o.order_date), MIN(o.order_date)) / COUNT(o.order_id) AS avg_days_between_orders
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
HAVING COUNT(o.order_id) > 1
ORDER BY order_count DESC;

-- 客户留存分析
SELECT 
    YEAR(first_order) AS cohort_year,
    MONTH(first_order) AS cohort_month,
    COUNT(DISTINCT customer_id) AS total_customers,
    COUNT(DISTINCT CASE WHEN months_since = 1 THEN customer_id END) AS retained_after_1_month,
    COUNT(DISTINCT CASE WHEN months_since = 3 THEN customer_id END) AS retained_after_3_months,
    COUNT(DISTINCT CASE WHEN months_since = 6 THEN customer_id END) AS retained_after_6_months,
    COUNT(DISTINCT CASE WHEN months_since = 12 THEN customer_id END) AS retained_after_12_months
FROM (
    SELECT 
        c.customer_id,
        MIN(o1.order_date) AS first_order,
        o2.order_date,
        PERIOD_DIFF(
            YEAR(o2.order_date) * 12 + MONTH(o2.order_date),
            YEAR(MIN(o1.order_date)) * 12 + MONTH(MIN(o1.order_date))
        ) AS months_since
    FROM customers c
    INNER JOIN orders o1 ON c.customer_id = o1.customer_id
    LEFT JOIN orders o2 ON c.customer_id = o2.customer_id AND o2.order_date > o1.order_date
    GROUP BY c.customer_id, o2.order_date
) cohort_analysis
GROUP BY cohort_year, cohort_month
ORDER BY cohort_year, cohort_month;

10. 查询优化技巧

10.1 编写高效查询

提高查询性能的基本原则：

-- 1. 只查询需要的列，避免SELECT *
SELECT customer_id, first_name, last_name
FROM customers
WHERE country = 'USA';

-- 2. 使用索引列作为查询条件
SELECT *
FROM orders
WHERE customer_id = 123; -- 假设customer_id有索引

-- 3. 避免在WHERE子句中使用函数
-- 低效：
SELECT *
FROM orders
WHERE YEAR(order_date) = 2023;

-- 更好：
SELECT *
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

-- 4. 尽量减少子查询，使用JOIN代替
-- 使用JOIN：
SELECT c.customer_id, c.first_name, COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name;

10.2 EXPLAIN 分析

使用EXPLAIN命令分析查询执行计划：

-- 查看查询执行计划
EXPLAIN SELECT *
FROM customers
WHERE country = 'USA' AND city = 'New York';

-- 分析联接查询性能
EXPLAIN SELECT 
    c.first_name,
    c.last_name,
    o.order_id
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date > '2023-01-01';

10.3 索引使用技巧

正确使用索引可大幅提高查询性能：

-- 为常用查询条件创建索引
CREATE INDEX idx_customers_country_city ON customers(country, city);

-- 复合索引的顺序很重要
-- 有效使用索引：
SELECT * FROM customers WHERE country = 'USA' AND city = 'New York';

-- 可能无法充分利用索引：
SELECT * FROM customers WHERE city = 'New York';  -- 如果索引是(country, city)

10.4 查询缓存与限制

利用LIMIT减少结果集大小：

-- 大数据集分页查询
SELECT *
FROM large_table
WHERE some_condition
ORDER BY id
LIMIT 100 OFFSET 10000;

-- 使用索引优化大偏移量查询
SELECT *
FROM large_table
WHERE id > 10000 AND some_condition
ORDER BY id
LIMIT 100;

10.5 通用查询优化建议

问题解决方案
查询速度慢添加合适的索引，优化查询条件
联接操作慢确保联接列有索引，减少联接表数量
排序慢在排序列上建立索引，减少排序数据量
分组操作慢在分组列上建立索引，预先过滤数据
子查询性能差尝试重写为联接查询
全表扫描添加适当的WHERE条件，建立索引

通过掌握MySQL的基础查询技巧，可以从数据库中高效地检索所需信息，满足业务需求。随着数据量和查询复杂度的增加，查询优化变得越来越重要。合理使用索引、编写高效的SQL语句、以及定期进行查询分析，都是提高数据库查询性能的关键步骤。

结语
感谢您的阅读！期待您的一键三连！欢迎指正！

在这里插入图片描述

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Guiat

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值