tags: shell perl db mysql unix select script

system:

db:

**@***:~$ mysql --version
mysql Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (x86_64) using readline 6.1

mysql>use test;

mysql> desc user;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> select * from user;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | a | 11 |
| 2 | b | 12 |
+----+------+------+
2 rows in set (0.00 sec)
mysql>

bash shell: db_select.sh

#!/bin/bash
HOST=127.0.0.1
USER=****
PASS=****

DATABASE=test
TABLE=user

QUERY=`mysql -h$HOST -u$USER -p$PASS << EOF
use $DATABASE;
select * from $TABLE where 1=1;
exit
EOF`

echo $QUERY

Result:
**@***-laptop:/***/shell$ ./db_select.sh
id name age 1 a 11 2 b 12

perl shell: db_select.pl

#!/usr/bin/perl
use strict;
my $HOST="127.0.0.1";
my $USER="******";
my $PASS="*****";

my $DB="test";
my $TABLES="user";

my $sql = "select * from $TABLES where 1=1";
my $result = `mysql -h$HOST -u$USER -p$PASS $DB -e"$sql"`;

print $result;
print "\n";

foreach my $line (split(/\n/, $result)){
printf("%s\n",$line);
for my $var (split(/\s/, $line))
{
print $var."\n";
}
print "\n";
}

Result:

**@***-laptop:/***/shell$ ./db_select.pl
id name age
1 a 11
2 b 12

id name age
id
name
age

1 a 11
1
a
11

2 b 12
2
b
12

--EOF--