Query to a MySQL database from shell

Question

I need to make several queries to MySql database. My question is the following:

Do I add the following in a file and then execute the file:

echo "Check "
mysql -h 192.168.100.1 -t -u umane -pass tablename < query.sql
echo "Check "
mysql -h 192.168.100.1 -t -u umane -pass tablename < query2.sql
echo "Check "
mysql -h 192.168.100.1 -t -u umane -pass tablename < query3.sql
echo "Check "
mysql -h 192.168.100.1 -t -u umane -pass tablename < query4.sql

Or I create a single "query.sql" file that contains all the sql querys. If so, can I in any way echo the "Check" statments somehow?

Aman Agarwal · Accepted Answer · 2011-09-29 12:23:36Z

1

username="my_sql username"
password="my_sql password"
result=`mysql -h 192.168.100.1  --port=$port -u $username --password=$password -e 'select * from database.table_name'`

echo $result

answered Sep 29, 2011 at 12:23

Aman Agarwal

7556 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joost · Accepted Answer · 2011-09-29 12:55:43Z

You could create one sql file and then add some SELECT statements like this

// Does not print anyting
CREATE TABLE test (col1 int, col2 varchar(2)); 
// Prints a non-existing table with 1 column and 1 row
SELECT "CHECK 1" AS "Test result"; 
// This does prints some output
DESCRIBE test;
// Prints the check 'table' again
SELECT "CHECK 2" AS "Test result";

The result of this looks like

my@server:~$ mysql -t -u root databasename < sqltest 
+-------------+
| Test result |
+-------------+
| CHECK 1     |
+-------------+
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| col1  | int(11)    | YES  |     | NULL    |       |
| col2  | varchar(2) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
+-------------+
| Test result |
+-------------+
| CHECK 2     |
+-------------+

-- edit, yeah the same as Dimitre's suggestion

Milind · Accepted Answer · 2011-09-29 13:03:11Z

1

Put all your sql statements in source.sql and you redirect the output into output.txt

mysql -t -uuserid -ppassword -hhostname < source.sql > output.txt

answered Sep 29, 2011 at 13:03

Milind

111 bronze badge

Comments

LSerni · Accepted Answer · 2012-11-13 09:39:07Z

0

You can add text (and timestamp) selects in the SQL code:

% mysql -NBe "select now(); select 'check'; select now();select 'check'"
2011-09-29 14:46:15
check
2011-09-29 14:46:15
check

So your single SQL file would look like:

--- queries from sql1...
SELECT NOW()
SELECT 'check';
-- queries from sql2...

edited Nov 13, 2012 at 9:39

LSerni

57.7k10 gold badges70 silver badges110 bronze badges

answered Sep 29, 2011 at 12:46

Dimitre Radoulov

28.1k4 gold badges42 silver badges50 bronze badges

Collectives™ on Stack Overflow

Query to a MySQL database from shell

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related