Day 06 | 第一阶段综合实战

实战日：今天是第一周的”期末考试”。你需要融合前 5 天学到的 Shell 逻辑、SQL 查询、管道与重定向，编写一个完整的 自动化报表生成工具。今天不只是写代码，而是要彻底理解 “如何将 Shell 和 SQL 结合起来”、“如何生成格式化的报表” 以及 “如何编写健壮的生产级脚本”。

实战目标 | Project Goal

我们将开发一个名为 06_sql_shell_integrate.sh 的工具，用于自动化统计业务数据。

核心功能

自动连接 MySQL 数据库
执行复杂的联表统计查询
格式化输出 Markdown 报表
添加时间戳并归档
错误处理和日志记录

涉及技能

Shell: 变量、函数、Here Document、错误处理
SQL: GROUP BY, JOIN, COUNT, SUM
Linux: 输出重定向、日期处理、文件操作
Markdown: 表格格式化

代码任务 (90 mins)

环境准备与数据模拟

首先确保数据库中有足够的测试数据。执行以下 SQL 准备环境：任务分解：

确保 orders 表存在
插入模拟订单数据
验证数据完整性

-- ============================================
-- Day 06: 初始化测试数据
-- ============================================

USE infra_db;

-- 确保 orders 表存在（Day 03 已创建，这里确保存在）
CREATE TABLE IF NOT EXISTS orders (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '订单ID',
    user_id INT UNSIGNED NOT NULL COMMENT '用户ID',
    amount DECIMAL(10, 2) COMMENT '订单金额',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

-- 确保 users 表有数据（Day 02 已插入，这里确保有数据）
-- 如果没有数据，先执行：
-- INSERT INTO users (username, email) VALUES 
-- ('Alice', 'alice@example.com'),
-- ('Bob', 'bob@example.com'),
-- ('Charlie', 'charlie@example.com');

-- 插入模拟订单数据
-- 注意：user_id 必须存在于 users 表中
INSERT INTO orders (user_id, amount) VALUES 
(1, 100.00),   -- Alice 的订单
(1, 200.00),   -- Alice 的第二个订单
(2, 50.00),    -- Bob 的订单
(3, 300.00),   -- Charlie 的订单
(1, 150.00),   -- Alice 的第三个订单
(2, 75.00)     -- Bob 的第二个订单
ON DUPLICATE KEY UPDATE id=id;  -- 如果已存在则跳过

-- 验证数据
SELECT 
    u.username,
    COUNT(o.id) as order_count,
    SUM(o.amount) as total_spend
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

验证步骤：

连接 MySQL：

docker exec -it mysql-learn mysql -uroot -proot

执行初始化 SQL：
source /path/to/init_data.sql;
或者直接复制粘贴执行

验证数据：

SELECT * FROM orders;
-- 应该看到 6 条订单记录

编写自动化脚本

这是今天的重头戏。我们将使用 mysql -e 命令在 Shell 中执行 SQL，并利用 Here Document (EOF) 处理多行 SQL。任务分解：

定义配置变量
编写数据库连接检查函数
编写 SQL 查询函数
编写报表生成函数
添加错误处理

#!/bin/bash
# 06_sql_shell_integrate.sh - Shell + SQL 报表生成器

# ============================================
# 配置区
# ============================================

# 数据库配置
DB_HOST="mysql-learn"      # Docker 容器名称
DB_USER="root"
DB_PASS="root"
DB_NAME="infra_db"

# 报表配置
REPORT_DIR="./reports"
REPORT_PREFIX="weekly_report"
DATE_FORMAT=$(date +%Y%m%d)
REPORT_FILE="$REPORT_DIR/${REPORT_PREFIX}_${DATE_FORMAT}.md"

# ============================================
# 颜色输出函数
# ============================================

function echo_red() {
    echo -e "\033[31m$1\033[0m"
}

function echo_green() {
    echo -e "\033[32m$1\033[0m"
}

function echo_yellow() {
    echo -e "\033[33m$1\033[0m"
}

# ============================================
# 日志函数
# ============================================

function log_info() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] INFO: $1"
}

function log_error() {
    echo_red "[$(date '+%Y-%m-%d %H:%M:%S')] ERROR: $1"
}

# ============================================
# 数据库连接检查函数
# ============================================

function check_db_connection() {
    log_info "Checking database connection..."
    
    # 使用 mysqladmin ping 检查连接
    # --silent：静默模式，只返回退出码
    if docker exec "$DB_HOST" mysqladmin -u"$DB_USER" -p"$DB_PASS" ping --silent 2>/dev/null; then
        echo_green "Database connection: OK"
        return 0
    else
        log_error "Database connection failed!"
        return 1
    fi
}

# ============================================
# 执行 SQL 查询函数
# ============================================

function execute_sql() {
    local sql_query="$1"
    
    # 使用 docker exec 执行 MySQL 命令
    # -i：交互式（虽然这里不需要，但保留）
    # -N：不显示表头（只显示数据）
    # -e：执行 SQL 语句
    docker exec -i "$DB_HOST" mysql -u"$DB_USER" -p"$DB_PASS" -D "$DB_NAME" -N -e "$sql_query" 2>/dev/null
    
    # 检查执行结果
    if [ $? -eq 0 ]; then
        return 0
    else
        log_error "SQL execution failed: $sql_query"
        return 1
    fi
}

# ============================================
# 生成报表函数
# ============================================

function generate_report() {
    log_info "Generating report..."
    
    # 创建报表目录
    mkdir -p "$REPORT_DIR"
    
    # 生成报表头部
    cat > "$REPORT_FILE" <<EOF
# 📊 Weekly Business Report

**Generated at**: $(date '+%Y-%m-%d %H:%M:%S')

---

## User Statistics

| 用户 | 订单数 | 总消费 | 平均订单金额 |
| :--- | :---: | :---: | :---: |
EOF
    
    # 定义 SQL 查询（使用 Here Document）
    # 这里使用多行 SQL，通过 cat <<EOF 定义
    SQL_QUERY=$(cat <<EOF
SELECT 
    u.username AS '用户',
    COALESCE(COUNT(o.id), 0) AS '订单数',
    COALESCE(SUM(o.amount), 0) AS '总消费',
    COALESCE(AVG(o.amount), 0) AS '平均订单金额'
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username
ORDER BY SUM(o.amount) DESC;
EOF
)
    
    # 执行 SQL 查询并格式化输出
    # sed 命令用于格式化：
    #   's/\t/ | /g'：将制表符替换为 " | "（Markdown 表格格式）
    #   's/^/| /'：在每行开头添加 "| "
    #   's/$/ |/'：在每行末尾添加 " |"
    execute_sql "$SQL_QUERY" | \
        sed 's/\t/ | /g' | \
        sed 's/^/| /' | \
        sed 's/$/ |/' >> "$REPORT_FILE"
    
    # 添加报表尾部
    cat >> "$REPORT_FILE" <<EOF

---

## Summary

**Total Users**: $(execute_sql "SELECT COUNT(*) FROM users;")
**Total Orders**: $(execute_sql "SELECT COUNT(*) FROM orders;")
**Total Revenue**: $(execute_sql "SELECT COALESCE(SUM(amount), 0) FROM orders;")

---

*Report generated by Shell + SQL Integration Script*
EOF
    
    if [ $? -eq 0 ]; then
        echo_green "Report generated: $REPORT_FILE"
        return 0
    else
        log_error "Report generation failed!"
        return 1
    fi
}

# ============================================
# 主流程
# ============================================

echo "=========================================="
echo "Shell + SQL Report Generator"
echo "=========================================="
echo ""

# 1. 检查数据库连接
if ! check_db_connection; then
    log_error "Cannot proceed without database connection"
    exit 1
fi

echo ""

# 2. 生成报表
if generate_report; then
    echo ""
    echo_green "=========================================="
    echo_green "Report generation completed!"
    echo_green "=========================================="
    echo ""
    echo "Report file: $REPORT_FILE"
    echo ""
    echo "Preview:"
    echo "----------------------------------------"
    head -20 "$REPORT_FILE"
    echo "----------------------------------------"
    exit 0
else
    echo ""
    log_error "Report generation failed!"
    exit 1
fi

常见错误：

❌ mysql -u$DB_USER -p$DB_PASS - 密码参数格式错误，应该是 -p$DB_PASS（无空格）
❌ docker exec mysql-learn mysql ... - 容器名称错误或容器未运行
❌ sed 's/\t/ /g' - 制表符替换不正确，Markdown 表格需要 ” | ”
✅ mysql -u$DB_USER -p$DB_PASS - 正确（注意 -p 和密码之间无空格）
✅ docker exec "$DB_HOST" mysql ... - 正确（使用变量）
✅ sed 's/\t/ | /g' - 正确（替换为表格格式）

验证步骤：

确保 MySQL 容器运行：
docker ps | grep mysql-learn

运行脚本：

chmod +x 06_sql_shell_integrate.sh
./06_sql_shell_integrate.sh

检查输出：
- 应该看到数据库连接检查结果
- 应该看到报表生成成功信息
- 应该看到报表预览
查看生成的报表：
cat reports/weekly_report_*.md
验证报表内容：
- 应该包含用户统计表格
- 应该包含汇总信息
- 表格格式应该正确

添加异常处理 (Bonus)

目前的脚本比较”脆弱”，如果数据库连接失败会怎样？让我们增强错误处理。改进点：

数据库连接检查
SQL 执行错误处理
文件操作错误处理
更详细的日志记录

#!/bin/bash
# 06_sql_shell_integrate_enhanced.sh - 增强版报表生成器

# ... (前面的配置和函数保持不变) ...

# ============================================
# 增强的数据库连接检查
# ============================================

function check_db_connection() {
    log_info "Checking database connection..."
    
    # 检查容器是否运行
    if ! docker ps | grep -q "$DB_HOST"; then
        log_error "Container '$DB_HOST' is not running!"
        return 1
    fi
    
    # 检查数据库连接
    if docker exec "$DB_HOST" mysqladmin -u"$DB_USER" -p"$DB_PASS" ping --silent 2>/dev/null; then
        echo_green "Database connection: OK"
        return 0
    else
        log_error "Database connection failed!"
        log_error "Please check:"
        log_error "  1. Container is running: docker ps | grep $DB_HOST"
        log_error "  2. Database credentials are correct"
        log_error "  3. Database is accessible"
        return 1
    fi
}

# ============================================
# 增强的 SQL 执行函数
# ============================================

function execute_sql() {
    local sql_query="$1"
    local result
    
    # 执行 SQL 并捕获输出和错误
    result=$(docker exec -i "$DB_HOST" mysql -u"$DB_USER" -p"$DB_PASS" -D "$DB_NAME" -N -e "$sql_query" 2>&1)
    local exit_code=$?
    
    if [ $exit_code -eq 0 ]; then
        echo "$result"
        return 0
    else
        log_error "SQL execution failed!"
        log_error "Query: $sql_query"
        log_error "Error: $result"
        return 1
    fi
}

# ============================================
# 增强的报表生成函数
# ============================================

function generate_report() {
    log_info "Generating report..."
    
    # 创建报表目录（带错误检查）
    if ! mkdir -p "$REPORT_DIR"; then
        log_error "Failed to create report directory: $REPORT_DIR"
        return 1
    fi
    
    # 检查目录是否可写
    if [ ! -w "$REPORT_DIR" ]; then
        log_error "Report directory is not writable: $REPORT_DIR"
        return 1
    fi
    
    # ... (报表生成逻辑保持不变) ...
    
    # 验证报表文件是否生成
    if [ ! -f "$REPORT_FILE" ]; then
        log_error "Report file was not created: $REPORT_FILE"
        return 1
    fi
    
    # 检查报表文件是否为空
    if [ ! -s "$REPORT_FILE" ]; then
        log_error "Report file is empty: $REPORT_FILE"
        return 1
    fi
    
    return 0
}

# ============================================
# 主流程（带错误处理）
# ============================================

# 设置错误时退出
set -e  # 遇到错误立即退出（可选，根据需求）

# 或者使用 trap 捕获错误
trap 'log_error "Script failed at line $LINENO"; exit 1' ERR

# ... (主流程保持不变) ...

错误处理最佳实践：

错误检查

检查命令执行结果：if [ $? -eq 0 ]; then ... fi
检查文件是否存在：if [ -f "$file" ]; then ... fi
检查目录是否可写：if [ -w "$dir" ]; then ... fi
检查变量是否为空：if [ -z "$var" ]; then ... fi

日志记录

记录所有操作：成功和失败都要记录
包含时间戳：便于追踪问题
包含详细信息：错误消息要具体
使用不同级别：INFO、WARNING、ERROR

优雅退出

使用 exit 码：0 表示成功，非零表示失败
清理资源：退出前清理临时文件
提供错误信息：告诉用户发生了什么

今日产出物

day06/06_sql_shell_integrate.sh - Shell + SQL 报表生成脚本
day06/init_data.sql - 测试数据初始化脚本
day06/reports/weekly_report_*.md - 生成的报表文件

参考代码

查看参考代码

在 GitHub 查看完整的示例代码

在线运行

使用在线编辑器测试代码

实际应用场景

Shell + SQL 整合在生产环境的应用

自动化报表：定时生成业务报表，发送给管理层
数据导出：将数据库数据导出为 CSV、JSON 等格式
数据同步：在不同数据库之间同步数据
监控告警：监控数据库指标，异常时发送告警
数据备份：结合 Shell 脚本和 SQL 实现数据备份

报表生成的最佳实践

模板化：使用模板生成报表，便于维护
格式化：生成 Markdown、HTML、PDF 等格式
自动化：使用 cron 定时生成报表
归档：保存历史报表，便于对比分析
通知：报表生成后自动发送邮件或消息

第一周总结：

你已经掌握了 Shell 脚本的基础到进阶：变量、流程控制、函数、管道、三剑客
你已经掌握了 SQL 的基础到优化：CRUD、联表、聚合、索引、慢查询优化
你已经能够编写生产级的自动化脚本，整合 Shell 和 SQL
明天是复盘日，回顾和巩固这一周的学习成果

上一天: 优化实战

Day 05 | Shell 脚本实战与 SQL 优化

下一天: 第一阶段复盘

Day 07 | 第一阶段复盘

网站概览 | Overview

项目愿景 | Project Vision

内部培训 | Internal Training

技术规范 | Protocols

基础设施 | Infrastructure

工作安排 | Work Plan

团队协作 | Team Collab

Day 06 | 第一阶段综合实战

实战目标 | Project Goal

核心功能

涉及技能

代码任务 (90 mins)

今日产出物

参考代码

查看参考代码

在线运行

实际应用场景

Shell + SQL 整合在生产环境的应用

报表生成的最佳实践

上一天: 优化实战

下一天: 第一阶段复盘

​ 实战目标 | Project Goal

核心功能

涉及技能

​ 代码任务 (90 mins)

​ 今日产出物

​ 参考代码

查看参考代码

在线运行

​实际应用场景

Shell + SQL 整合在生产环境的应用

报表生成的最佳实践

上一天: 优化实战

下一天: 第一阶段复盘

实战目标 | Project Goal

代码任务 (90 mins)

今日产出物

参考代码

实际应用场景