目录

表结构

1. 数据月表（zbr_data_monthly_data_YYYYMM）

2. 分类表（zbr_category）

3. 用户表（zbr_user）

4. 交易表（zbr_transaction）

查询知识点

1. 基本查询

2. 连接查询

3. 分组和排序

4. 日期与时间函数

5. 条件表达式

6. 子查询

练习：

1. 查询所有用户的信息

2. 查询所有分类的名称和描述

3. 查询特定用户的所有交易记录

4. 查询特定分类下的所有数据月记录

5. 查询某个月的数据总金额

6. 查询所有交易的金额和对应的用户

7. 查询某一分类的交易总金额

8. 查询每个分类的交易次数

9. 查询每个用户的总交易金额

10. 查询最近的交易记录

11. 查询每个用户的平均交易金额和交易次数

12. 查询金额最高的交易记录及其对应的用户和分类信息

---

表结构

1. 数据月表（zbr_data_monthly_data_YYYYMM）

CREATE TABLE zbr_data_monthly_data_202407 (id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键，唯一标识每条记录',month_date DATE NOT NULL COMMENT '记录的月份日期',category_id INT NOT NULL COMMENT '关联的分类ID',amount DECIMAL(10, 2) NOT NULL COMMENT '该月份的金额数据',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建的时间',is_deleted TINYINT(4) DEFAULT 0 COMMENT '是否已删除：0->未删除；1->已删除',INDEX idx_month_date (month_date) COMMENT '按月份日期建立索引',INDEX idx_category_id (category_id) COMMENT '按分类ID建立索引'
) COMMENT='数据月表，记录每月的金额数据';

2. 分类表（zbr_category）

CREATE TABLE zbr_category (id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键，唯一标识每个分类',name VARCHAR(100) NOT NULL COMMENT '分类名称',description TEXT COMMENT '分类的详细描述',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建的时间',is_deleted TINYINT(4) DEFAULT 0 COMMENT '是否已删除：0->未删除；1->已删除'
) COMMENT='分类表，存储商品或服务的分类信息';

3. 用户表（zbr_user）

CREATE TABLE zbr_user (id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键，唯一标识每个用户',username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名，唯一且不可为空',email VARCHAR(100) NOT NULL UNIQUE COMMENT '用户邮箱，唯一且不可为空',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建的时间',is_deleted TINYINT(4) DEFAULT 0 COMMENT '是否已删除：0->未删除；1->已删除'
) COMMENT='用户表，存储系统用户的信息';

4. 交易表（zbr_transaction）

CREATE TABLE zbr_transaction (id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键，唯一标识每条交易记录',user_id INT NOT NULL COMMENT '关联的用户ID',category_id INT NOT NULL COMMENT '关联的分类ID',transaction_date DATETIME NOT NULL COMMENT '交易的日期和时间',amount DECIMAL(10, 2) NOT NULL COMMENT '交易的金额',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建的时间',is_deleted TINYINT(4) DEFAULT 0 COMMENT '是否已删除：0->未删除；1->已删除',INDEX idx_transaction_date (transaction_date) COMMENT '按交易日期建立索引'
) COMMENT='交易表，记录用户的交易信息';

INSERT INTO zbr_category (name, description, created_at, is_deleted) VALUES
('食品', '与食品相关的分类', NOW(), 0),
('电子产品', '与电子产品相关的分类', NOW(), 0),
('服装', '与服装相关的分类', NOW(), 0),
('家居用品', '与家居用品相关的分类', NOW(), 0);
INSERT INTO zbr_user (username, email, created_at, is_deleted) VALUES
('user1', 'user1@qq.com', NOW(), 0),
('user2', 'user2@qq.com', NOW(), 0),
('user3', 'user3@qq.com', NOW(), 0),
('user4', 'user4@qq.com', NOW(), 0);
INSERT INTO zbr_data_monthly_data_202407 (month_date, category_id, amount, created_at, is_deleted) VALUES
('2024-07-01', 1, 1500.00, NOW(), 0),
('2024-07-01', 2, 3000.00, NOW(), 0),
('2024-07-01', 3, 1200.00, NOW(), 0),
('2024-07-01', 4, 2500.00, NOW(), 0);
INSERT INTO zbr_transaction (user_id, category_id, transaction_date, amount, created_at, is_deleted) VALUES
(1, 1, '2024-07-05 10:00:00', 300.00, NOW(), 0),
(2, 2, '2024-07-06 11:00:00', 1500.00, NOW(), 0),
(3, 3, '2024-07-07 12:00:00', 600.00, NOW(), 0),
(4, 4, '2024-07-08 13:00:00', 800.00, NOW(), 0),
(1, 1, '2024-07-09 14:00:00', 200.00, NOW(), 0),
(2, 2, '2024-07-10 15:00:00', 1200.00, NOW(), 0);

查询知识点

1. 基本查询

• 选择所有字段：使用 SELECT * 可以获取表中所有列的数据。

  • SELECT * FROM zbr_user;
    

• 条件过滤：使用 WHERE 子句进行条件过滤，例如 WHERE user_id = 1。

  • SELECT * FROM zbr_transaction WHERE user_id = 1;
    

• 聚合函数：使用 SUM(), AVG(), COUNT() 等聚合函数进行统计。

  • SELECT SUM(amount) AS total_amount FROM zbr_transaction;
    

2. 连接查询

• 内连接 (INNER JOIN)：通过 JOIN 语句连接相关表，获取匹配的数据。例如，获取用户和交易记录的关联信息。

  • SELECT u.username, t.amount
    FROM zbr_user u
    JOIN zbr_transaction t ON u.id = t.user_id;
    

• 外连接 (LEFT JOIN 左连接/RIGHT JOIN 右连接)：左连接即使在右表中没有匹配的记录，也能从左表中获取所有记录，右连接相反。例如，查找每个用户及其交易记录，即使某些用户没有交易。

  • SELECT u.username, t.amount
    FROM zbr_user u
    LEFT JOIN zbr_transaction t ON u.id = t.user_id;
    

• 自连接：在同一表内连接自身，用于处理层级或关系数据。

  • SELECT a.username AS user1, b.username AS user2
    FROM zbr_user a, zbr_user b
    WHERE a.id != b.id;
    

3. 分组和排序

• 分组 (GROUP BY)：将结果集中的记录按照某一列或多列进行分组，可以结合聚合函数计算每组的数据。

  • SELECT category_id, SUM(amount) AS total_amount
    FROM zbr_transaction
    GROUP BY category_id;

• 排序 (ORDER BY)：使用 ORDER BY 进行结果集排序，可以指定升序（ASC）或降序（DESC）。

  • SELECT username, amount
    FROM zbr_transaction
    ORDER BY amount DESC;
    

4. 日期与时间函数

• 日期过滤：使用 BETWEEN 或比较运算符对日期范围进行查询，例如查询特定月份内的交易记录。

  • SELECT * FROM zbr_transaction
    WHERE transaction_date BETWEEN '2024-07-01' AND '2024-07-31';
    

• 日期格式化：使用 DATE_FORMAT() 对日期进行格式化，例如按年月分组。

  • SELECT DATE_FORMAT(transaction_date, '%Y-%m') AS transaction_month, SUM(amount) AS monthly_total
    FROM zbr_transaction
    GROUP BY transaction_month;
    

5. 条件表达式

• 逻辑运算符：可以使用 AND、OR 等逻辑运算符组合多个条件。

  • SELECT * FROM zbr_transaction
    WHERE amount > 1000 AND user_id = 1;

• NULL 值处理：使用 IS NULL 和 IS NOT NULL 处理可能为 NULL 的情况。

  • SELECT * FROM zbr_user
    WHERE email IS NULL;
    

6. 子查询

• 嵌套查询：可以在查询中嵌套其他查询，例如在 SELECT、WHERE 子句中使用子查询。

  • SELECT username FROM zbr_user
    WHERE id IN (SELECT user_id FROM zbr_transaction WHERE amount > 1000);
    

练习：

1. 查询所有用户的信息

2. 查询所有分类的名称和描述

3. 查询特定用户的所有交易记录

4. 查询特定分类下的所有数据月记录

5. 查询某个月的数据总金额

6. 查询所有交易的金额和对应的用户

7. 查询某一分类的交易总金额

8. 查询每个分类的交易次数

9. 查询每个用户的总交易金额

10. 查询最近的交易记录

11. 查询每个用户的平均交易金额和交易次数

12. 查询金额最高的交易记录及其对应的用户和分类信息