循环和判断语句 - Powered by MinDoc

一、数学计算示例
1.1 求3位数的水仙花数
1.2 扩展：求任意位数的水仙花数
1.3 求1000以内的质数
二、MySQL 数据库操作示例
2.1 MySQL 批量创建用户
2.2 MySQL 批量删除表
三、综合实战：数据库管理脚本
3.1 综合数据库管理工具
四、总结
循环技巧总结：

一、数学计算示例

1.1 求3位数的水仙花数

水仙花数：一个n位数，其各位数字的n次方之和等于该数本身。

#!/bin/bash
# narcissistic_numbers.sh - 求3位数的水仙花数

echo "=== 三位数的水仙花数 ==="
echo "说明：一个三位数，其各位数字的立方和等于该数本身"

count=0
for num in {100..999}; do
    # 分解数字
    hundreds=$((num / 100))
    tens=$(( (num / 10) % 10 ))
    units=$((num % 10))

    # 计算立方和
    sum=$((hundreds*hundreds*hundreds + tens*tens*tens + units*units*units))

    # 判断是否为水仙花数
    if [ $sum -eq $num ]; then
        echo "水仙花数: $num = $hundreds³ + $tens³ + $units³"
        echo "         = $((hundreds*hundreds*hundreds)) + $((tens*tens*tens)) + $((units*units*units))"
        echo
        ((count++))
    fi
done

echo "共找到 $count 个水仙花数"

1.2 扩展：求任意位数的水仙花数

#!/bin/bash
# all_narcissistic_numbers.sh - 求所有水仙花数

echo "=== 求任意位数的水仙花数 ==="
read -p "请输入最大位数 (1-7): " max_digits

if ! [[ "$max_digits" =~ ^[1-7]$ ]]; then
    echo "错误：请输入1-7之间的数字"
    exit 1
fi

echo "正在计算 $max_digits 位数的水仙花数..."
echo

for ((digits=1; digits<=max_digits; digits++)); do
    echo "=== $digits 位数 ==="
    count=0

    start=$((10**(digits-1)))
    end=$((10**digits - 1))

    # 对于1位数特殊处理
    if [ $digits -eq 1 ]; then
        start=0
    fi

    for ((num=start; num<=end; num++)); do
        # 计算各位数字的digits次方和
        sum=0
        temp=$num

        # 分解数字并计算幂和
        for ((i=0; i<digits; i++)); do
            digit=$((temp % 10))

            # 计算digit的digits次方
            power=1
            for ((j=0; j<digits; j++)); do
                power=$((power * digit))
            done

            sum=$((sum + power))
            temp=$((temp / 10))
        done

        if [ $sum -eq $num ]; then
            echo "✓ $num"
            ((count++))
        fi
    done

    if [ $count -eq 0 ]; then
        echo "无"
    fi
    echo
done

1.3 求1000以内的质数

#!/bin/bash
# prime_numbers.sh - 求1000以内的质数

echo "=== 1000以内的质数 ==="
read -p "请输入上限 (默认1000): " limit
limit=${limit:-1000}

if ! [[ "$limit" =~ ^[0-9]+$ ]] || [ $limit -lt 2 ]; then
    echo "错误：请输入大于等于2的整数"
    exit 1
fi

echo "计算 $limit 以内的质数..."
echo

# 方法1：基础算法
echo "方法1：基础算法"
primes=()
start_time=$(date +%s%N)

for ((num=2; num<=limit; num++)); do
    is_prime=true

    for ((i=2; i*i<=num; i++)); do
        if [ $((num % i)) -eq 0 ]; then
            is_prime=false
            break
        fi
    done

    if $is_prime; then
        primes+=($num)
    fi
done

end_time=$(date +%s%N)
time_taken=$(( (end_time - start_time) / 1000000 ))
echo "找到质数个数: ${#primes[@]}"
echo "耗时: ${time_taken}ms"

# 每行显示10个
echo -n "质数列表: "
for ((i=0; i<${#primes[@]}; i++)); do
    if [ $(( (i+1) % 10 )) -eq 0 ]; then
        echo "${primes[i]}"
        [ $i -ne $(( ${#primes[@]} - 1 )) ] && echo -n "         "
    else
        echo -n "${primes[i]} "
    fi
done
echo
echo

# 方法2：埃拉托斯特尼筛法
echo "方法2：埃拉托斯特尼筛法"
start_time=$(date +%s%N)

# 初始化数组
declare -a sieve
for ((i=0; i<=limit; i++)); do
    sieve[i]=1
done
sieve[0]=0
sieve[1]=0

# 筛法
for ((i=2; i*i<=limit; i++)); do
    if [ ${sieve[i]} -eq 1 ]; then
        for ((j=i*i; j<=limit; j+=i)); do
            sieve[j]=0
        done
    fi
done

# 收集质数
sieve_primes=()
for ((i=2; i<=limit; i++)); do
    if [ ${sieve[i]} -eq 1 ]; then
        sieve_primes+=($i)
    fi
done

end_time=$(date +%s%N)
time_taken=$(( (end_time - start_time) / 1000000 ))
echo "找到质数个数: ${#sieve_primes[@]}"
echo "耗时: ${time_taken}ms"

二、MySQL 数据库操作示例

2.1 MySQL 批量创建用户

#!/bin/bash
# mysql_create_users.sh - 批量创建MySQL用户

# 配置
MYSQL_USER="root"
MYSQL_PASS="your_password"
MYSQL_HOST="localhost"
DATABASE_NAME="company_db"
LOG_FILE="mysql_users_$(date +%Y%m%d_%H%M%S).log"

# 颜色定义
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
NC='\033[0m'

# 日志函数
log() {
    local level="$1"
    local message="$2"
    echo -e "[$(date '+%Y-%m-%d %H:%M:%S')] [$level] $message" | tee -a "$LOG_FILE"
}

# 检查MySQL连接
check_mysql_connection() {
    log "INFO" "检查MySQL连接..."
    if mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" -e "SELECT 1" > /dev/null 2>&1; then
        log "INFO" "MySQL连接成功"
        return 0
    else
        log "ERROR" "MySQL连接失败"
        return 1
    fi
}

# 创建数据库
create_database() {
    log "INFO" "创建数据库: $DATABASE_NAME"

    if mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" -e "CREATE DATABASE IF NOT EXISTS $DATABASE_NAME CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"; then
        log "INFO" "数据库创建成功"
        return 0
    else
        log "ERROR" "数据库创建失败"
        return 1
    fi
}

# 批量创建用户
create_users() {
    local user_list=(
        # 格式: 用户名:密码:权限:主机
        "alice:AlicePass123:SELECT,INSERT,UPDATE:localhost"
        "bob:BobPass456:SELECT,INSERT:%"
        "charlie:CharliePass789:ALL:192.168.1.%"
        "david:DavidPass101:SELECT,EXECUTE:10.0.0.%"
        "eva:EvaPass202:SELECT:localhost"
    )

    local created_count=0
    local failed_count=0

    log "INFO" "开始批量创建用户..."
    echo "="*50 | tee -a "$LOG_FILE"

    for user_info in "${user_list[@]}"; do
        IFS=':' read -r username password privileges host <<< "$user_info"

        log "INFO" "创建用户: $username@$host"

        # 创建用户SQL
        create_user_sql="CREATE USER IF NOT EXISTS '$username'@'$host' IDENTIFIED BY '$password';"

        # 授予权限SQL
        if [ "$privileges" = "ALL" ]; then
            grant_sql="GRANT ALL PRIVILEGES ON $DATABASE_NAME.* TO '$username'@'$host';"
        else
            grant_sql="GRANT $privileges ON $DATABASE_NAME.* TO '$username'@'$host';"
        fi

        # 执行SQL
        if mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" -e "$create_user_sql $grant_sql FLUSH PRIVILEGES;" 2>> "$LOG_FILE"; then
            log "INFO" "✓ 用户 $username 创建成功"
            ((created_count++))

            # 记录用户信息
            echo "用户: $username" >> user_credentials.txt
            echo "密码: $password" >> user_credentials.txt
            echo "权限: $privileges" >> user_credentials.txt
            echo "主机: $host" >> user_credentials.txt
            echo "数据库: $DATABASE_NAME" >> user_credentials.txt
            echo "---" >> user_credentials.txt
        else
            log "ERROR" "✗ 用户 $username 创建失败"
            ((failed_count++))
        fi

        echo "-"*40 | tee -a "$LOG_FILE"
    done

    log "INFO" "用户创建完成: 成功 $created_count 个, 失败 $failed_count 个"
    return $failed_count
}

# 验证用户创建
verify_users() {
    log "INFO" "验证已创建的用户..."

    verify_sql="SELECT user, host FROM mysql.user WHERE user NOT IN ('mysql.session','mysql.sys','root');"

    echo "已创建的用户列表:" | tee -a "$LOG_FILE"
    mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" -e "$verify_sql" 2>> "$LOG_FILE" | tee -a "$LOG_FILE"
}

# 生成报告
generate_report() {
    log "INFO" "生成报告..."

    echo "=== MySQL用户创建报告 ===" > report.txt
    echo "生成时间: $(date)" >> report.txt
    echo "数据库: $DATABASE_NAME" >> report.txt
    echo "主机: $MYSQL_HOST" >> report.txt
    echo "" >> report.txt

    echo "用户凭证已保存到: user_credentials.txt" >> report.txt
    echo "详细日志: $LOG_FILE" >> report.txt

    cat report.txt
}

# 主函数
main() {
    log "INFO" "开始MySQL用户批量创建脚本"

    if ! check_mysql_connection; then
        exit 1
    fi

    if ! create_database; then
        exit 1
    fi

    if ! create_users; then
        log "WARN" "有用户创建失败"
    fi

    verify_users
    generate_report

    log "INFO" "脚本执行完成"
    echo -e "${GREEN}✓ 所有操作已完成${NC}"
    echo -e "${YELLOW}⚠ 请妥善保管 user_credentials.txt 文件${NC}"
}

# 安全提示
echo -e "${YELLOW}警告：此脚本将创建数据库用户，请确保您有相应权限${NC}"
read -p "是否继续? (y/n): " -n 1 -r
echo
if [[ $REPLY =~ ^[Yy]$ ]]; then
    main
else
    echo "操作已取消"
    exit 0
fi

2.2 MySQL 批量删除表

#!/bin/bash
# mysql_drop_tables.sh - 批量删除MySQL表

# 配置
MYSQL_USER="root"
MYSQL_PASS="your_password"
MYSQL_HOST="localhost"
DATABASE_NAME="test_db"
BACKUP_DIR="./backups"
LOG_FILE="mysql_drop_$(date +%Y%m%d_%H%M%S).log"

# 颜色定义
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
NC='\033[0m'

# 日志函数
log() {
    local level="$1"
    local message="$2"
    echo -e "[$(date '+%Y-%m-%d %H:%M:%S')] [$level] $message" | tee -a "$LOG_FILE"
}

# 检查MySQL连接
check_mysql_connection() {
    if mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" -e "SELECT 1" > /dev/null 2>&1; then
        log "INFO" "MySQL连接成功"
        return 0
    else
        log "ERROR" "MySQL连接失败"
        return 1
    fi
}

# 检查数据库是否存在
check_database() {
    log "INFO" "检查数据库: $DATABASE_NAME"

    check_sql="SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '$DATABASE_NAME';"

    if mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" -e "$check_sql" 2>> "$LOG_FILE" | grep -q "$DATABASE_NAME"; then
        log "INFO" "数据库存在"
        return 0
    else
        log "ERROR" "数据库不存在"
        return 1
    fi
}

# 备份数据库
backup_database() {
    log "INFO" "开始备份数据库..."

    # 创建备份目录
    mkdir -p "$BACKUP_DIR"

    local backup_file="${BACKUP_DIR}/${DATABASE_NAME}_$(date +%Y%m%d_%H%M%S).sql"

    if mysqldump -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" \
        --single-transaction \
        --routines \
        --triggers \
        --events \
        "$DATABASE_NAME" > "$backup_file" 2>> "$LOG_FILE"; then

        # 压缩备份文件
        gzip "$backup_file"
        log "INFO" "备份成功: ${backup_file}.gz"

        # 保留最近7天的备份
        find "$BACKUP_DIR" -name "*.sql.gz" -mtime +7 -delete
        return 0
    else
        log "ERROR" "备份失败"
        return 1
    fi
}

# 获取表列表
get_table_list() {
    log "INFO" "获取表列表..."

    local tables_file="tables_to_drop.txt"

    # 获取所有表名
    mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" \
        -N -B -e "SHOW TABLES FROM $DATABASE_NAME" 2>> "$LOG_FILE" > "$tables_file"

    local table_count=$(wc -l < "$tables_file")
    log "INFO" "找到 $table_count 个表"

    # 显示表列表
    if [ $table_count -gt 0 ]; then
        echo "=== 数据库 $DATABASE_NAME 中的表 ==="
        cat "$tables_file" | nl
        echo
    fi

    return $table_count
}

# 批量删除表
drop_tables() {
    local mode="$1"  # all, prefix, list
    local pattern="$2"

    case "$mode" in
        "all")
            log "INFO" "模式: 删除所有表"
            drop_all_tables
            ;;
        "prefix")
            log "INFO" "模式: 删除前缀为 '$pattern' 的表"
            drop_tables_by_prefix "$pattern"
            ;;
        "list")
            log "INFO" "模式: 从文件读取表名删除"
            drop_tables_from_list "$pattern"
            ;;
        *)
            log "ERROR" "未知模式: $mode"
            return 1
            ;;
    esac
}

# 删除所有表
drop_all_tables() {
    log "WARN" "准备删除所有表！"

    # 获取所有表名
    tables=$(mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" \
        -N -B -e "SELECT GROUP_CONCAT(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '$DATABASE_NAME'" 2>> "$LOG_FILE")

    if [ -n "$tables" ]; then
        # 生成删除语句
        drop_sql="SET FOREIGN_KEY_CHECKS = 0;"
        IFS=',' read -ra table_array <<< "$tables"

        for table in "${table_array[@]}"; do
            drop_sql="$drop_sql DROP TABLE IF EXISTS \`$table\`;"
        done

        drop_sql="$drop_sql SET FOREIGN_KEY_CHECKS = 1;"

        # 执行删除
        if mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" \
            "$DATABASE_NAME" -e "$drop_sql" 2>> "$LOG_FILE"; then

            log "INFO" "所有表删除成功"
            return 0
        else
            log "ERROR" "删除表失败"
            return 1
        fi
    else
        log "INFO" "数据库为空，无需删除"
        return 0
    fi
}

# 按前缀删除表
drop_tables_by_prefix() {
    local prefix="$1"

    # 获取匹配前缀的表名
    tables=$(mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" \
        -N -B -e "SELECT GROUP_CONCAT(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '$DATABASE_NAME' AND TABLE_NAME LIKE '${prefix}%'" 2>> "$LOG_FILE")

    if [ -n "$tables" ]; then
        log "INFO" "找到以下表: $tables"

        # 生成删除语句
        drop_sql="SET FOREIGN_KEY_CHECKS = 0;"
        IFS=',' read -ra table_array <<< "$tables"

        for table in "${table_array[@]}"; do
            drop_sql="$drop_sql DROP TABLE IF EXISTS \`$table\`;"
            log "INFO" "将删除表: $table"
        done

        drop_sql="$drop_sql SET FOREIGN_KEY_CHECKS = 1;"

        # 执行删除
        if mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" \
            "$DATABASE_NAME" -e "$drop_sql" 2>> "$LOG_FILE"; then

            log "INFO" "表删除成功"
            return 0
        else
            log "ERROR" "删除表失败"
            return 1
        fi
    else
        log "INFO" "没有找到前缀为 '$prefix' 的表"
        return 0
    fi
}

# 从文件读取表名删除
drop_tables_from_list() {
    local list_file="$1"

    if [ ! -f "$list_file" ]; then
        log "ERROR" "文件不存在: $list_file"
        return 1
    fi

    local tables=()
    while IFS= read -r table || [ -n "$table" ]; do
        # 跳过空行和注释
        [[ -z "$table" ]] && continue
        [[ "$table" =~ ^# ]] && continue

        table=$(echo "$table" | xargs)  # 去除空格
        tables+=("$table")
    done < "$list_file"

    if [ ${#tables[@]} -eq 0 ]; then
        log "INFO" "文件中没有有效的表名"
        return 0
    fi

    log "INFO" "从文件读取到 ${#tables[@]} 个表名"

    # 生成删除语句
    drop_sql="SET FOREIGN_KEY_CHECKS = 0;"
    for table in "${tables[@]}"; do
        # 检查表是否存在
        check_sql="SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '$DATABASE_NAME' AND TABLE_NAME = '$table'"

        if mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" \
            -N -B -e "$check_sql" 2>> "$LOG_FILE" | grep -q "1"; then

            drop_sql="$drop_sql DROP TABLE IF EXISTS \`$table\`;"
            log "INFO" "将删除表: $table"
        else
            log "WARN" "表不存在: $table"
        fi
    done

    drop_sql="$drop_sql SET FOREIGN_KEY_CHECKS = 1;"

    # 执行删除
    if mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" \
        "$DATABASE_NAME" -e "$drop_sql" 2>> "$LOG_FILE"; then

        log "INFO" "表删除成功"
        return 0
    else
        log "ERROR" "删除表失败"
        return 1
    fi
}

# 验证删除结果
verify_deletion() {
    log "INFO" "验证删除结果..."

    local remaining_tables=$(mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" \
        -N -B -e "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '$DATABASE_NAME'" 2>> "$LOG_FILE")

    log "INFO" "剩余表数量: $remaining_tables"

    if [ "$remaining_tables" -eq 0 ]; then
        log "INFO" "所有表已成功删除"
    else
        # 显示剩余表
        mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" \
            -e "SHOW TABLES FROM $DATABASE_NAME" 2>> "$LOG_FILE" | tee -a "$LOG_FILE"
    fi
}

# 交互式菜单
show_menu() {
    echo -e "${BLUE}=== MySQL 表删除工具 ===${NC}"
    echo
    echo "1. 删除所有表"
    echo "2. 按前缀删除表"
    echo "3. 从文件读取表名删除"
    echo "4. 查看当前表列表"
    echo "5. 退出"
    echo
}

# 主函数
main() {
    log "INFO" "开始MySQL表删除脚本"

    if ! check_mysql_connection; then
        exit 1
    fi

    if ! check_database; then
        exit 1
    fi

    # 询问是否备份
    echo -e "${YELLOW}⚠ 警告：删除表操作不可逆！${NC}"
    read -p "是否先备份数据库? (y/n): " -n 1 -r
    echo
    if [[ $REPLY =~ ^[Yy]$ ]]; then
        if ! backup_database; then
            echo -e "${RED}备份失败，是否继续? (y/n): ${NC}" 
            read -n 1 -r
            echo
            if ! [[ $REPLY =~ ^[Yy]$ ]]; then
                exit 0
            fi
        fi
    fi

    # 显示菜单
    while true; do
        show_menu
        read -p "请选择操作 (1-5): " choice

        case $choice in
            1)
                if drop_tables "all" ""; then
                    verify_deletion
                fi
                ;;
            2)
                read -p "请输入表名前缀: " prefix
                if [ -n "$prefix" ]; then
                    if drop_tables "prefix" "$prefix"; then
                        verify_deletion
                    fi
                else
                    echo -e "${RED}前缀不能为空${NC}"
                fi
                ;;
            3)
                read -p "请输入表名列表文件: " list_file
                if [ -f "$list_file" ]; then
                    if drop_tables "list" "$list_file"; then
                        verify_deletion
                    fi
                else
                    echo -e "${RED}文件不存在: $list_file${NC}"
                fi
                ;;
            4)
                get_table_list
                ;;
            5)
                log "INFO" "用户退出"
                break
                ;;
            *)
                echo -e "${RED}无效选择${NC}"
                ;;
        esac

        echo
        read -p "按回车键继续..."
    done

    log "INFO" "脚本执行完成"
    echo -e "${GREEN}✓ 操作已完成${NC}"
}

# 安全确认
echo -e "${RED}⚠ 警告：此脚本将删除数据库表，操作不可逆！${NC}"
echo -e "${YELLOW}请确保您了解此操作的后果${NC}"
read -p "是否继续? (输入 'YES' 确认): " confirmation

if [ "$confirmation" = "YES" ]; then
    main
else
    echo "操作已取消"
    exit 0
fi

三、综合实战：数据库管理脚本

3.1 综合数据库管理工具

#!/bin/bash
# db_manager.sh - 综合数据库管理工具

# 配置
CONFIG_FILE="db_config.conf"
LOG_FILE="db_manager_$(date +%Y%m%d_%H%M%S).log"

# 颜色定义
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
PURPLE='\033[0;35m'
CYAN='\033[0;36m'
NC='\033[0m'

# 加载配置
load_config() {
    if [ -f "$CONFIG_FILE" ]; then
        source "$CONFIG_FILE"
    else
        # 默认配置
        DB_HOST="localhost"
        DB_PORT="3306"
        DB_USER="root"
        DB_PASS=""
        DEFAULT_DB="test_db"
    fi
}

# 保存配置
save_config() {
    cat > "$CONFIG_FILE" << EOF
# 数据库配置
DB_HOST="$DB_HOST"
DB_PORT="$DB_PORT"
DB_USER="$DB_USER"
DB_PASS="$DB_PASS"
DEFAULT_DB="$DEFAULT_DB"
EOF
    echo -e "${GREEN}配置已保存到 $CONFIG_FILE${NC}"
}

# MySQL执行函数
mysql_exec() {
    local sql="$1"
    local db="${2:-}"

    local cmd="mysql -h $DB_HOST -P $DB_PORT -u $DB_USER"

    if [ -n "$DB_PASS" ]; then
        cmd="$cmd -p$DB_PASS"
    fi

    if [ -n "$db" ]; then
        cmd="$cmd $db"
    fi

    eval "$cmd -e \"$sql\" 2>> $LOG_FILE"
}

# 显示菜单
show_menu() {
    clear
    echo -e "${BLUE}╔══════════════════════════════════════════╗${NC}"
    echo -e "${BLUE}║        综合数据库管理工具 v1.0          ║${NC}"
    echo -e "${BLUE}╚══════════════════════════════════════════╝${NC}"
    echo
    echo -e "${CYAN}[1]${NC} 配置数据库连接"
    echo -e "${CYAN}[2]${NC} 数据库管理"
    echo -e "${CYAN}[3]${NC} 用户管理"
    echo -e "${CYAN}[4]${NC} 表管理"
    echo -e "${CYAN}[5]${NC} 数据操作"
    echo -e "${CYAN}[6]${NC} 批量操作"
    echo -e "${CYAN}[7]${NC} 性能监控"
    echo -e "${CYAN}[8]${NC} 备份恢复"
    echo -e "${CYAN}[0]${NC} 退出"
    echo
    echo -e "当前连接: ${YELLOW}$DB_USER@$DB_HOST:$DB_PORT${NC}"
    echo
}

# 配置数据库连接
config_connection() {
    echo -e "${PURPLE}=== 配置数据库连接 ===${NC}"
    echo

    read -p "主机 [$DB_HOST]: " input
    DB_HOST="${input:-$DB_HOST}"

    read -p "端口 [$DB_PORT]: " input
    DB_PORT="${input:-$DB_PORT}"

    read -p "用户名 [$DB_USER]: " input
    DB_USER="${input:-$DB_USER}"

    read -sp "密码: " DB_PASS
    echo

    read -p "默认数据库 [$DEFAULT_DB]: " input
    DEFAULT_DB="${input:-$DEFAULT_DB}"

    # 测试连接
    echo -n "测试连接..."
    if mysql_exec "SELECT 1" > /dev/null 2>&1; then
        echo -e " ${GREEN}成功${NC}"
        save_config
    else
        echo -e " ${RED}失败${NC}"
    fi

    read -p "按回车键继续..."
}

# 数据库管理菜单
db_management() {
    while true; do
        clear
        echo -e "${PURPLE}=== 数据库管理 ===${NC}"
        echo
        echo "1. 列出所有数据库"
        echo "2. 创建数据库"
        echo "3. 删除数据库"
        echo "4. 数据库大小统计"
        echo "5. 字符集和排序规则"
        echo "0. 返回主菜单"
        echo

        read -p "请选择: " choice

        case $choice in
            1)
                echo -e "${YELLOW}数据库列表:${NC}"
                mysql_exec "SHOW DATABASES" | grep -v "Database"
                ;;
            2)
                read -p "数据库名: " dbname
                read -p "字符集 (默认utf8mb4): " charset
                charset="${charset:-utf8mb4}"
                read -p "排序规则 (默认utf8mb4_unicode_ci): " collation
                collation="${collation:-utf8mb4_unicode_ci}"

                mysql_exec "CREATE DATABASE \`$dbname\` CHARACTER SET $charset COLLATE $collation"
                echo -e "${GREEN}数据库创建成功${NC}"
                ;;
            3)
                read -p "数据库名: " dbname
                echo -e "${RED}警告：这将永久删除数据库 $dbname${NC}"
                read -p "确认删除? (输入数据库名确认): " confirm

                if [ "$confirm" = "$dbname" ]; then
                    mysql_exec "DROP DATABASE \`$dbname\`"
                    echo -e "${GREEN}数据库删除成功${NC}"
                else
                    echo -e "${YELLOW}操作取消${NC}"
                fi
                ;;
            4)
                echo -e "${YELLOW}数据库大小统计:${NC}"
                mysql_exec "
SELECT 
    table_schema AS '数据库',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS '大小(MB)',
    ROUND(SUM(data_length) / 1024 / 1024, 2) AS '数据(MB)',
    ROUND(SUM(index_length) / 1024 / 1024, 2) AS '索引(MB)',
    COUNT(*) AS '表数量'
FROM information_schema.tables 
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;
                "
                ;;
            5)
                echo -e "${YELLOW}数据库字符集:${NC}"
                mysql_exec "
SELECT 
    SCHEMA_NAME AS '数据库',
    DEFAULT_CHARACTER_SET_NAME AS '默认字符集',
    DEFAULT_COLLATION_NAME AS '默认排序规则'
FROM information_schema.SCHEMATA;
                "
                ;;
            0)
                return
                ;;
        esac

        read -p "按回车键继续..."
    done
}

# 批量创建测试数据
batch_create_test_data() {
    echo -e "${PURPLE}=== 批量创建测试数据 ===${NC}"
    echo

    read -p "要创建多少条记录? (默认1000): " count
    count=${count:-1000}

    read -p "表名前缀 (默认test_): " prefix
    prefix=${prefix:-test_}

    echo "开始创建测试数据..."
    echo "数量: $count"
    echo "表前缀: $prefix"
    echo

    # 创建用户表
    echo "创建用户表..."
    mysql_exec "
CREATE TABLE IF NOT EXISTS ${prefix}users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_username (username),
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    " "$DEFAULT_DB"

    # 创建订单表
    echo "创建订单表..."
    mysql_exec "
CREATE TABLE IF NOT EXISTS ${prefix}orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    order_no VARCHAR(50) NOT NULL UNIQUE,
    amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'paid', 'shipped', 'completed', 'cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES ${prefix}users(id) ON DELETE CASCADE,
    INDEX idx_order_no (order_no),
    INDEX idx_user_id (user_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    " "$DEFAULT_DB"

    # 批量插入用户数据
    echo "插入用户数据..."
    for ((i=1; i<=count; i++)); do
        username="user_${i}"
        email="user${i}@example.com"
        age=$((RANDOM % 50 + 18))

        mysql_exec "
INSERT INTO ${prefix}users (username, email, age) 
VALUES ('$username', '$email', $age)
ON DUPLICATE KEY UPDATE age = VALUES(age);
        " "$DEFAULT_DB"

        # 显示进度
        if [ $((i % 100)) -eq 0 ]; then
            echo -ne "已插入 $i/$count 条用户记录\r"
        fi
    done
    echo

    # 批量插入订单数据
    echo "插入订单数据..."
    for ((i=1; i<=count; i++)); do
        user_id=$((RANDOM % count + 1))
        order_no="ORD$(date +%Y%m%d)${i}"
        amount=$(awk -v i=$i 'BEGIN { printf "%.2f", (i % 1000) + (RANDOM % 1000) / 100.0 }')
        statuses=("pending" "paid" "shipped" "completed" "cancelled")
        status=${statuses[$((RANDOM % 5))]}

        mysql_exec "
INSERT INTO ${prefix}orders (user_id, order_no, amount, status) 
VALUES ($user_id, '$order_no', $amount, '$status')
ON DUPLICATE KEY UPDATE amount = VALUES(amount), status = VALUES(status);
        " "$DEFAULT_DB"

        # 显示进度
        if [ $((i % 100)) -eq 0 ]; then
            echo -ne "已插入 $i/$count 条订单记录\r"
        fi
    done
    echo

    echo -e "${GREEN}测试数据创建完成${NC}"
    echo "用户表: ${prefix}users"
    echo "订单表: ${prefix}orders"
    echo "每表记录数: $count"

    read -p "按回车键继续..."
}

# 主循环
main() {
    load_config

    while true; do
        show_menu
        read -p "请选择操作 (0-8): " choice

        case $choice in
            1) config_connection ;;
            2) db_management ;;
            3) 
                # 用户管理（使用之前的脚本）
                echo -e "${YELLOW}调用用户管理模块...${NC}"
                ./mysql_create_users.sh
                ;;
            4)
                # 表管理（使用之前的脚本）
                echo -e "${YELLOW}调用表管理模块...${NC}"
                ./mysql_drop_tables.sh
                ;;
            5)
                echo -e "${YELLOW}数据操作功能开发中...${NC}"
                read -p "按回车键继续..."
                ;;
            6)
                batch_create_test_data
                ;;
            7)
                echo -e "${YELLOW}性能监控功能开发中...${NC}"
                read -p "按回车键继续..."
                ;;
            8)
                echo -e "${YELLOW}备份恢复功能开发中...${NC}"
                read -p "按回车键继续..."
                ;;
            0)
                echo -e "${GREEN}感谢使用，再见！${NC}"
                exit 0
                ;;
            *)
                echo -e "${RED}无效选择${NC}"
                read -p "按回车键继续..."
                ;;
        esac
    done
}

# 启动脚本
main

四、总结

循环技巧总结：

数学计算循环：
- 使用 for ((i=0; i<N; i++)) 进行精确计数
- 使用 {start..end} 进行范围迭代
- 数学运算使用 $(( ))
数据库操作循环：
- 批量操作时先备份
- 使用事务确保数据一致性
- 添加进度显示
最佳实践：
- 总是添加错误处理
- 记录详细日志
- 提供回滚机制
- 验证操作权限

这些示例展示了如何在Shell循环中结合数学计算和数据库操作，创建实用的管理脚本。

作者：严锋创建时间：2023-09-15 21:29
最后编辑：严锋更新时间：2025-12-25 10:39