Accessing SQL databases remotely or locally

In this section, we are going to learn how to automate SQL queries by connecting to a server using a shell script. Bash scripting is used for automating things.

Getting ready

Make sure that mysql, postgres, and sqlite are installed. Ensure that the user is created in MySQL and that you have granted permission to that user.

How to do it...

1. MySQL queries in script: We are going to write a script called mysql_version.shto get the latest version of MySQL:

#!/bin/bash
mysql -u root -pTraining2@^ <<MY_QUERY
SELECT VERSION();
MY_QUERY

Now, we are going to create a script called create_database.sh to create the database:

#!/bin/bash
mysql -u root -pTraining2@^ <<MY_QUERY
create database testdb;
MY_QUERY

2. SQLite queries in script: Now, we are going to create a sqlite database. You can create the sqlite database by simply writing sqlite3 and a name for the database. For example:

$ sqlite3 testdb

Now, we are going to create a table in the sqlite console...