Command line sql in linux

How to Run SQL Query Using Bash Script and Command-Line?

While working as a developer, one needs to automate some existing SQL Queries using bash script without accessing the interactive MySQL prompt. In this blog post, I will show the different ways to run SQL queries using Bash Script or using a command line. I will be using the MySQL database in this blog.

Before following this blog post, please make sure that you have a MySQL client and server installed. Use the below command to check if MySQL is installed on your machine.

[[email protected] root]$ which mysql /bin/mysql [[email protected] root]$ mysql --help mysql Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using EditLine wrapper Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Usage: mysql [OPTIONS] [database] -?, --help Display this help and exit. -I, --help Synonym for -? --auto-rehash Enable automatic rehashing. One doesn't need to use 'rehash' to get table and field completion, but startup and reconnecting may take a longer time. Disable with --disable-auto-rehash. (Defaults to on; use --skip-auto-rehash to disable.) -A, --no-auto-rehash No automatic rehashing. One has to use 'rehash' to get table and field completion. This gives a quicker start of mysql and disables rehashing on reconnect. --auto-vertical-output Automatically switch to vertical output mode if the result is wider than the terminal width. -B, --batch Don't use history file. Disable interactive behavior. (Enables --silent.) --bind-address=name IP address to bind to.

Execute SQL query from the Linux command-line

mysql -u USER_NAME -pPASS_WORD -h hostname -D database -e ""

The below table shows the different options we can have while running Queries.

Option	Description
–user, -u	MySQL user name account that is used to connect SQL Database Server.
–password, -p	The password that is used to connect to SQL Database.
–database, -D	Name of the Database that we need to connect.
–host, -h	Name of the host where the Database is Installed.
–skip-column-names, -N	It makes sure that Column names are not written in
–batch, -B	It is used to print results using a tab as the column separator, with each row on a new line.

Example To Show all Tables from Customer Tables

mysql -u root -padmin -e "USE customer;SHOW TABLES"

-pPASS_WORD When passing the PASS_WORD through the command line, we should not have a space between password and -p

Note: Here I am passing the password in the command Line for demonstration purposes. It is not recommended when running Queries in a Production environment.

Run SQL query on the explicitly specified host

To run a SQL query on an explicitly specified host, we use the -h option and specified hostname as HOSTNAME .

mysql -u USER -pPASSWORD -h HOSTNAME -e "SQL_QUERY"

Use the Below Link as an Example to connect to the localhost Host.

mysql -u retail_dba -phadoop -h localhost -e "USE world;SELECT * FROM CITY LIMT 20;"

Run SQL query on the specified database:

It will run the SQL query using the hostname defined by the -h parameter for specified HOSTNAME and database identified by DATABASE option with D parameter.

mysql -u USER -pPASSWORD -h -D -e "SQL_QUERY"

~/tutorials/rdbms$ mysql -u root -padmin -D employees -h localhost -e "SELECT * FROM dept_emp LIMIT 20; " +--------+---------+------------+------------+ | emp_no | dept_no | from_date | to_date | +--------+---------+------------+------------+ | 10001 | d005 | 1986-06-26 | 9999-01-01 | | 10002 | d007 | 1996-08-03 | 9999-01-01 | | 10003 | d004 | 1995-12-03 | 9999-01-01 | | 10004 | d004 | 1986-12-01 | 9999-01-01 | | 10005 | d003 | 1989-09-12 | 9999-01-01 | | 10006 | d005 | 1990-08-05 | 9999-01-01 | | 10007 | d008 | 1989-02-10 | 9999-01-01 | | 10008 | d005 | 1998-03-11 | 2000-07-31 | | 10009 | d006 | 1985-02-18 | 9999-01-01 | | 10010 | d004 | 1996-11-24 | 2000-06-26 | | 10010 | d006 | 2000-06-26 | 9999-01-01 | | 10011 | d009 | 1990-01-22 | 1996-11-09 | | 10012 | d005 | 1992-12-18 | 9999-01-01 | | 10013 | d003 | 1985-10-20 | 9999-01-01 | | 10014 | d005 | 1993-12-29 | 9999-01-01 | | 10015 | d008 | 1992-09-19 | 1993-08-22 | | 10016 | d007 | 1998-02-11 | 9999-01-01 | | 10017 | d001 | 1993-08-03 | 9999-01-01 | | 10018 | d004 | 1992-07-29 | 9999-01-01 | | 10018 | d005 | 1987-04-03 | 1992-07-29 | +--------+---------+------------+------------+

Suppressing column headings:

We use the -N option to Suppress the Column heading.

mysql -u USER -pPASSWORD -N -e "SQL_QUERY"

Save the output to a file

We can redirect the result to a certain location to save the output of a Query in the File using the > sign and the file name.

mysql -u USER -pPASSWORD -e "SQL_QUERY" > FILE

Here we are trying to save the result of the query in the /home/hduser/tutorials/rdbms/result.txt location.

[email protected]:~/tutorials/rdbms$ mysql -u root -padmin -e "USE employees;SELECT * FROM departments LIMIT 200;" >/home/hduser/tutorials/rdbms/result.txt [email protected]:~/tutorials/rdbms$ cat result.txt dept_no dept_name d009 Customer Service d005 Development d002 Finance d003 Human Resources d001 Marketing d004 Production d006 Quality Management d008 Research d007 Sales

Run SQL Queries From A Bash Script

Example: Create a Shell script named shell_sql_single_query.sh

#!/bin/bash mysql -u root -padmin -e "USE employees;SELECT * FROM departments LIMIT 200;"

Give proper permission to the shell script and run the script.

~$/tutorials/rdbms$ chmod a+x shell_sql_single_query.sh ~$/tutorials/rdbms$ ./shell_sql_single_query.sh +---------+--------------------+ | dept_no | dept_name | +---------+--------------------+ | d009 | Customer Service | | d005 | Development | | d002 | Finance | | d003 | Human Resources | | d001 | Marketing | | d004 | Production | | d006 | Quality Management | | d008 | Research | | d007 | Sales | +---------+--------------------+

Running Multiple SQL Queries using Shell Script

We need to make sure that there are no spaces before the second EOF Tag. If there is any space, it will be considered as part of the SQL Query.

Instead of EOF we can use names like

Passing Variable to SQL Query from Bash Script

Here we are passing the Database from the bash script to the SQL query.

#!/bin/bash mysql -u root -psecret ; SHOW tables; BASH_QUERY

Below is an example script that shows how to run multiple SQL scripts with your own variable defined. We are defining two-variable called DATABASE_NAME and TABLE_NAME that are being used in the SQL query.

#!/bin/bash #Example Script that shows passing of Variable DATABASE_NAME=employees TABLE_NAME=employees mysql -u root -padmin 
When we run the above query, we get the below results.
~$/tutorials/rdbms$ ./shell_sql_multiple_query.sh dept_no dept_name d009 Customer Service d005 Development d002 Finance d003 Human Resources d001 Marketing d004 Production d006 Quality Management d008 Research d007 Sales COUNT(*) 300024
 
Running a .sql file
 
I created a simple SQL file in this location /home/hduser/tutorials/rdbms
 
USE employees; SELECT * FROM salaries LIMIT 20;
 
To run this SQL file through the command line or Bash script, we need to use the below syntax.
 
[email protected]:~/tutorials/rdbms$ mysql -u root -padmin < /home/hduser/tutorials/rdbms/sample_script.sql emp_no salary from_date to_date 10001 60117 1986-06-26 1987-06-26 10001 62102 1987-06-26 1988-06-25 10001 66074 1988-06-25 1989-06-25 10001 66596 1989-06-25 1990-06-25 10001 66961 1990-06-25 1991-06-25 10001 71046 1991-06-25 1992-06-24 10001 74333 1992-06-24 1993-06-24 10001 75286 1993-06-24 1994-06-24 10001 75994 1994-06-24 1995-06-24 10001 76884 1995-06-24 1996-06-23 10001 80013 1996-06-23 1997-06-23 10001 81025 1997-06-23 1998-06-23 10001 81097 1998-06-23 1999-06-23 10001 84917 1999-06-23 2000-06-22 10001 85112 2000-06-22 2001-06-22 10001 85097 2001-06-22 2002-06-22 10001 88958 2002-06-22 9999-01-01 10002 65828 1996-08-03 1997-08-03 10002 65909 1997-08-03 1998-08-03 10002 67534 1998-08-03 1999-08-03
 
Conclusion
 
In this blog post, we have read how to use a Unix shell script to connect to the database and execute a SQL query. We also learn how to pass variables to SQL queries using a bash script. We also learn about how to run multiple SQL at once.
 
Источник
 
MySQL: Run Query from Bash Script or Linux Command Line
 
Sometimes it is needed to run some MySQL queries from the Linux command-line interface without accessing the interactive MySQL prompt.
 
For example, when it is required to schedule a backup of MySQL database or to automate execution of some SQL queries with a Bash script.
 
In this article i will show the most useful, from my point of view, options of the MySQL command-line client and show how to run multiple SQL queries to a database from a Bash script.
 
Cool Tip: List MySQL users, their passwords and granted privileges from the command-line prompt! Read more →
 
MySQL: Execute SQL Queries From The Linux Shell
 
Execute SQL query from the Linux command-line:
 
$ mysql -u USER -pPASSWORD -e "SQL_QUERY"
 
-pPASSWORD: This is not a typo! There should not be a space between your password and the -p when you pass it on the command-line!
 
Run SQL query on the explicitly specified database:
 
$ mysql -u USER -pPASSWORD -D DATABASE -e "SQL_QUERY"
 
Run SQL query on the explicitly specified host:
 
$ mysql -u USER -pPASSWORD -h HOSTNAME -e "SQL_QUERY"
 
Suppressing column headings:
 
$ mysql -u USER -pPASSWORD -N -e "SQL_QUERY"
 
$ mysql -u USER -pPASSWORD -B -e "SQL_QUERY"
 
Save the output to a file:
 
$ mysql -u USER -pPASSWORD -e "SQL_QUERY" > FILE
 
Cool Tip: Create a MySQL database and GRANT ALL PRIVILEGES on it to a user! Simple and clear MySQL tutorial with good examples! Read more →
 
The most useful MySQL options when executing SQL queries from the Linux command-line or a Bash script:
 
 
 
 
Option
 
Description
 
 
 
--user , -u
 
The MySQL user name to use when connecting to the server.
 
 
 
--password , -p
 
The password to use when connecting to the server.
 
 
 
--database , -D
 
The database to use.
 
 
 
--host , -h
 
Connect to the MySQL server on the given host.
 
 
 
--skip-column-names , -N
 
Do not write column names in results.
 
 
 
--batch , -B
 
Print results using tab as the column separator, with each row on a new line.
 
 
 
 
MySQL: Run SQL Queries From A Bash Script
 
Note that here shouldn’t be any spaces just before the second EOF tag, otherwise it will be considered as a part of the SQL query.
 
By the way, you can rename the EOF to anything you want, e.g. 
 
Cool Tip: Do you have a backup? You MUST have it! Backup MySQL databases from the command-line! This is really easy! Read more →
 
Use the following construction if you need to run multiple SQL queries from a Bash script:
 
mysql -u USER -pPASSWORD SQL_QUERY 1 SQL_QUERY 2 SQL_QUERY N EOF
 
#!/bin/bash mysql -u root -psecret 
Источник
How to use SQL in terminal?
I want to create simple tables, insert values in it, and do queries. How to perform them in the terminal?
5 Answers 5
Here is the syntax to execute sql statement from terminal
I'm assuming that you are using MySQL .
mysql -u user_name -p password -e 'SQL Query' database 
 
-u : Specify mysql database user name -p : Prompt for password -e : Execute sql query database : Specify database name 
 
 If you want to create a table person then:
 
 
mysql -u root -p -e 'Create table person(PersonID int, LastName varchar(255), FirstName varchar(255))' mydb 
 
mysql -u root -p -e 'Insert into person(PersonID,LastName,FirstName) Values(100,"Kumar","Saurav")' mydb 
 
mysql -u root -p -e 'Select * from person' mydb > personinfo 
 
And of-course you can create a database using terminal itself
 
 To create database mydb execute following command in terminal:
 
 
mysql -u root -p -e 'create database mydb' 
 
mysql -u root -p -e 'show databases' 
 
Hope it helps you.. Reply if you need further assistance..
 
You make a database by typing
 
In the prompt you enter, you then start by creating your database (as explained by onik in the comments):
 
Once you have made that database, you can experiment with it. You can simply type mysql in a terminal and you can get do anything sql related you want. It is possible that you have create to a role in your database with your username.
 
As described in Sauruv's answer you can also connect to the database as follows (without the space between p and your password or better, just do not use the -p option and you will get a password prompt [credits go to onik]):
 
mysql -u user_name -ppassword dbname -u : Specify mysql database user name -p : Prompt for password dbname : Specify database name 
 
Источник