Shell Perl 脚本查询 MySQL DB
tags: shell perl db mysql unix select script
system:
**@***:~$ uname -a
Linux **-laptop 2.6.32-21-generic #32-Ubuntu SMP Fri Apr 16 08:09:38 UTC 2010 x86_64 GNU/Linux
**@***:~$
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--