Automation in shell

Run SQL

counter=`mysql -u $DB_USER -p $DB_PASS $DB_NAME -e "select count(*) from tablename where id=XX;"`

 

CRUD Functions

#!/bin/bash

# MySQL login details
MYSQL_USER="user"
MYSQL_PASSWORD="password"

# Function to execute a MySQL query and print the results
execute_query() {
    local query=$1
    local database=$2
    mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" "$database" -e "$query"
}

# Function to create a new database
# Usgae: create_database {db-name}
create_database() {
    local database=$1
    execute_query "CREATE DATABASE $database"
}

# Function to create a new table in a database
# Usage: create_table {db-name} {table-name} {schema}
# {schema}: "id INTEGER PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255), salary INTEGER"
create_table() {
    local database=$1
    local table=$2
    local schema=$3
    execute_query "CREATE TABLE $table ($schema)" "$database"
}

# Function to insert data into a table
# Usage: insert_data {db-name} {table-name} ${data}
# ${data}: "num1, 'str1', num2, 'str2'"
insert_data() {
    local database=$1
    local table=$2
    local data=$3
    execute_query "INSERT INTO $table VALUES ($data)" "$database"
}

# Function to update data in a table
# Usage: update_data {db-name} {table-name} {data} ${condition}
# {data}: "salary=7000"
# ${condition}: "id=2"
update_data() {
    local database=$1
    local table=$2
    local data=$3
    local condition=$4
    execute_query "UPDATE $table SET $data WHERE $condition" "$database"
}

# Function to delete data from a table
# Usage: delete_data ${db-name} ${table-name} ${condition}
# ${condition}: "id=2"
delete_data() {
    local database=$1
    local table=$2
    local condition=$3
    execute_query "DELETE FROM $table WHERE $condition" "$database"
}

# Function to display data from a table
# Usage: display_data ${db-name} ${table-name}
display_data() {
    local database=$1
    local table=$2
    execute_query "SELECT * FROM $table" "$database"
}