自娱自乐，sqlite3 数据库查看脚本

1. 创建数据库

#!/bin/bash

sqlite3 example.db <<EOF
-- 创建表
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    age INTEGER
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    user_id INTEGER,
    product TEXT NOT NULL,
    amount INTEGER,
    FOREIGN KEY(user_id) REFERENCES users(id)
);

-- 插入一些示例数据
INSERT INTO users (name, email, age) VALUES ('Alice', '[email protected]', 30);
INSERT INTO users (name, email, age) VALUES ('Bob', '[email protected]', 25);
INSERT INTO users (name, email, age) VALUES ('Charlie', '[email protected]', 35);

INSERT INTO orders (user_id, product, amount) VALUES (1, 'Laptop', 1200);
INSERT INTO orders (user_id, product, amount) VALUES (2, 'Smartphone', 800);
INSERT INTO orders (user_id, product, amount) VALUES (3, 'Tablet', 300);
INSERT INTO orders (user_id, product, amount) VALUES (1, 'Monitor', 200);

-- 查询数据
SELECT * FROM users;
SELECT * FROM orders;
EOF

2. sqlite3 数据库脚本

sqlite3_beautify_show.sh

#!/bin/bash
# sqlite3
db_name=$1

OUTPUT_MODE="column"  # 默认输出模式

# 定义颜色
RED='\033[31m'
GREEN='\033[32m'
YELLOW='\033[33m'
BLUE='\033[34m'
MAGENTA='\033[35m'
CYAN='\033[36m'
RESET='\033[0m'


function color_echo() 
{
    color_str=`echo $1 | tr '[:upper:]' '[:lower:]'`
    msg=$2

    case "$color_str" in
        red)
            echo -e "${RED}${msg}${RESET}"
            ;;
        green)
            echo -e "${GREEN}${msg}${RESET}"
            ;;
        yellow)
            echo -e "${YELLOW}${msg}${RESET}"
            ;;
        blue)
            echo -e "${BLUE}${msg}${RESET}"
            ;;
        magenta)
            echo -e "${MAGENTA}${msg}${RESET}"
            ;;
        cyan)
            echo -e "${CYAN}${msg}${RESET}"
            ;;
        *)
            echo -e "${msg}"
            ;;
    esac
}


function Usage()
{
    color_echo normal "Usage:${0} [db_path]"
    exit 0
}

if [[ ${db_name} == "" ]];then
    Usage
fi

if [[ ! -e ${db_name} ]];then
    color_echo red "db:${db_name} not found"
    Usage
else
    clear
    color_echo green "find db:${db_name}"
fi

# 延时回到main
function screen_pause()
{
    # echo -e "\npress any key to return"
    read -e OPERATION
}

# 展示表名
function show_table_names() 
{
    color_echo green "Tables in the database $DATABASE:"
    echo -e ".tables\n.exit\n"|sqlite3 tms.db |tr ' ' '\n'|awk '{print $1}'|column
    screen_pause
}


# 展示表结构
function show_table_detail()
{
    echo "Input a table_name:"
    read -e table_name
    color_echo green "\n------------------ TABLE_INFO:${table_name} ------------------"
    echo -e ".mode ${OUTPUT_MODE}\n pragma table_info(${table_name});\n.exit\n"|sqlite3 tms.db
    screen_pause
}

# 展示所有表
function show_all_tables_detail()
{
    color_echo green "\n------------------ DB INFO:${db_name} ------------------"
    tables=`sqlite3 ${db_name} ".tables"`
    for table_name in $tables; do
         color_echo green "TABLE:${table_name}"
         echo -e ".mode ${OUTPUT_MODE}\n pragma table_info($table_name);\n.exit\n"|sqlite3 ${db_name}
    done
    screen_pause
}

# 展示表中的数据
function show_table_data()
{
    echo "Input a table_name:"
    read -e table_name
    color_echo green "\n ------ TABLE_DATA:${table_name} ------ "
    echo -e ".mode ${OUTPUT_MODE}\nselect * from ${table_name};\n.exit\n"|sqlite3 ${db_name}
    screen_pause
}

# 选择 sqlite3 输出格式
function choose_mode() {
    # ascii box column csv html insert json line list markdown quote table tabs tcl
    echo -e "------ MODE LIST ------"
    echo "1. column"
    echo "2. box"
    echo "3. csv"
    echo "4. html"
    echo "5. insert"
    echo "6. line"
    echo "7. list"
    echo "8. tabs"
    echo "9. json"
    echo -e "----------------------"
    echo "Choose a mode:"
    read -e MODE_CHOICE
    case $MODE_CHOICE in
        1|column) OUTPUT_MODE="column";;
        2|box) OUTPUT_MODE="box";;
        3|csv) OUTPUT_MODE="csv";;
        4|html) OUTPUT_MODE="html";;
        5|insert) OUTPUT_MODE="insert";;
        6|line) OUTPUT_MODE="line";;
        7|list) OUTPUT_MODE="list";;
        8|tabs) OUTPUT_MODE="tabs";;
        9|json) OUTPUT_MODE="json";;
        *) color_echo red "Invalid choice";;
    esac
    echo "Output mode set to $OUTPUT_MODE"
    screen_pause
}


# 主程序
function main()
{
    while true; do
        clear
        echo -e "\n------ OPTIONS ------"
        echo "1. show_table_names"
        echo "2. show_table_detail"
        echo "3. show_all_tables_detail"
        echo "4. show_table_data"
        echo "5. choose_mode"
        echo "6. exit"
        echo -e "---------------------"
        color_echo green "DB: ${db_name}, MODE_NOW: ${OUTPUT_MODE}"
        echo "Choose an option:"
        read -e OPTION
        case $OPTION in
            1) show_table_names;;
            2) show_table_detail;;
            3) show_all_tables_detail;;
            4) show_table_data;;
            5) choose_mode;;
            6|exit|quit) exit;;
            *) color_echo red "Invalid choice: ${OPTION}";;
        esac
    done
}

main