perl下十种fetch数据的区别以及返回数据结构
1.fetchrow_array 提取下一行数据并将字段保存在数组中返回文章源自运维生存时间-https://www.ttlsa.com/perl/perl-fetch-10/
[codesyntax lang="perl"]文章源自运维生存时间-https://www.ttlsa.com/perl/perl-fetch-10/
#!/usr/bin/perl文章源自运维生存时间-https://www.ttlsa.com/perl/perl-fetch-10/
###################################文章源自运维生存时间-https://www.ttlsa.com/perl/perl-fetch-10/
### author: www.ttlsa.com ###文章源自运维生存时间-https://www.ttlsa.com/perl/perl-fetch-10/
### QQ群: 39514058 ###文章源自运维生存时间-https://www.ttlsa.com/perl/perl-fetch-10/
### E-mail: service@ttlsa.com ###文章源自运维生存时间-https://www.ttlsa.com/perl/perl-fetch-10/
###################################文章源自运维生存时间-https://www.ttlsa.com/perl/perl-fetch-10/
文章源自运维生存时间-https://www.ttlsa.com/perl/perl-fetch-10/
use DBI;文章源自运维生存时间-https://www.ttlsa.com/perl/perl-fetch-10/
use Data::Dump qw(dump);文章源自运维生存时间-https://www.ttlsa.com/perl/perl-fetch-10/
文章源自运维生存时间-https://www.ttlsa.com/perl/perl-fetch-10/
$driver="DBI:mysql";文章源自运维生存时间-https://www.ttlsa.com/perl/perl-fetch-10/
$host="localhost:3306";文章源自运维生存时间-https://www.ttlsa.com/perl/perl-fetch-10/
$dbname="test";文章源自运维生存时间-https://www.ttlsa.com/perl/perl-fetch-10/
$user="root";文章源自运维生存时间-https://www.ttlsa.com/perl/perl-fetch-10/
$passwd="123456";文章源自运维生存时间-https://www.ttlsa.com/perl/perl-fetch-10/
文章源自运维生存时间-https://www.ttlsa.com/perl/perl-fetch-10/
$dbh=DBI->connect("$driver:$dbname:$host","$user","$passwd") or die DBI->errstr;文章源自运维生存时间-https://www.ttlsa.com/perl/perl-fetch-10/
$sql="select * from 115_users limit 10";文章源自运维生存时间-https://www.ttlsa.com/perl/perl-fetch-10/
$sth=$dbh->prepare($sql);
$rv=$sth->execute;
while(@row_ary=$sth->fetchrow_array){
dump(@row_ary);
print "password: " .$row_ary[2]."\n";
}
[/codesyntax]
输出内容:
(
1,
"auto-gre-1\@ttlsa.com",
"Y9MoErtE+iZG5PkYHMJobhij58E",
"auto-gre-1",
)
password: Y9MoErtE+iZG5PkYHMJobhij58E
(
2,
"auto-gre-2\@ttlsa.com",
"UqHxPoLmKY7ClyCZaXPdHepjUOo",
"auto-gre-2",
)
password: UqHxPoLmKY7ClyCZaXPdHepjUOo
2.fetchrow_arrayref 提取下一行数据并返回一个包含字段值的引用数组
[codesyntax lang="perl"]
#!/usr/bin/perl
###################################
### author: www.ttlsa.com ###
### QQ群: 39514058 ###
### E-mail: service@ttlsa.com ###
###################################
use DBI;
use Data::Dump qw(dump);
$driver="DBI:mysql";
$host="localhost:3306";
$dbname="test";
$user="root";
$passwd="123456";
$dbh=DBI->connect("$driver:$dbname:$host","$user","$passwd") or die DBI->errstr;
$sql="select * from ttlsa_user_00 limit 10";
$sth=$dbh->prepare($sql);
$rv=$sth->execute;
while($ary_ref=$sth->fetchrow_arrayref){
dump($ary_ref);
print "email: ".$$ary_ref[1]."\n";
}
[/codesyntax]
输出内容:
[
1,
"auto-gre-1\@ttlsa.com",
"Y9MoErtE+iZG5PkYHMJobhij58E",
"auto-gre-1",
]
email: auto-gre-1@ttlsa.com
[
2,
"auto-gre-2\@ttlsa.com",
"UqHxPoLmKY7ClyCZaXPdHepjUOo",
"auto-gre-2",
]
email: auto-gre-2@ttlsa.com
3.fetchrow_hashref 提取下一行数据并返回一个包含字段名和字段值对的哈希引用
[codesyntax lang="perl"]
#!/usr/bin/perl
###################################
### author: www.ttlsa.com ###
### QQ群: 39514058 ###
### E-mail: service@ttlsa.com ###
###################################
use DBI;
use Data::Dump qw(dump);
$driver="DBI:mysql";
$host="localhost:3306";
$dbname="test";
$user="root";
$passwd="123456";
$dbh=DBI->connect("$driver:$dbname:$host","$user","$passwd") or die DBI->errstr;
$sql="select * from ttlsa_user_00 limit 10";
$sth=$dbh->prepare($sql);
$rv=$sth->execute;
while ($hash_ref=$sth->fetchrow_hashref) {
dump($hash_ref);
print "password: " . $$hash_ref{'passwd'} . "\n";
}
[/codesyntax]
输出内容:
{
email => "auto-gre-1\@ttlsa.com",
passwd => "Y9MoErtE+iZG5PkYHMJobhij58E",
user_id => 1,
user_name => "auto-gre-1",
}
password: Y9MoErtE+iZG5PkYHMJobhij58E
{
email => "auto-gre-2\@ttlsa.com",
passwd => "UqHxPoLmKY7ClyCZaXPdHepjUOo",
user_id => 2,
user_name => "auto-gre-2",
}
password: UqHxPoLmKY7ClyCZaXPdHepjUOo
4.fetchall_arrayref 取出所有行内容并返回包含每行字段值的引用数组
[codesyntax lang="perl"]
#!/usr/bin/perl
###################################
### author: www.ttlsa.com ###
### QQ群: 39514058 ###
### E-mail: service@ttlsa.com ###
###################################
use DBI;
use Data::Dump qw(dump);
$driver="DBI:mysql";
$host="localhost:3306";
$dbname="test";
$user="root";
$passwd="123456";
$dbh=DBI->connect("$driver:$dbname:$host","$user","$passwd") or die DBI->errstr;
$sql="select * from ttlsa_user_00 limit 10";
$sth=$dbh->prepare($sql);
$rv=$sth->execute;
$ary_ref=$sth->fetchall_arrayref;
dump($ary_ref);
print "@$ary_ref\n";
foreach (@$ary_ref) {
print "$_->[2]\n";
}
[/codesyntax]
输出内容:
[
[
1,
"auto-gre-1\@ttlsa.com",
"Y9MoErtE+iZG5PkYHMJobhij58E",
"auto-gre-1",
],
[
2,
"auto-gre-2\@ttlsa.com",
"UqHxPoLmKY7ClyCZaXPdHepjUOo",
"auto-gre-2",
],
]
ARRAY(0x9e7f5d8) ARRAY(0x9e7f578)
password: Y9MoErtE+iZG5PkYHMJobhij58E
password: UqHxPoLmKY7ClyCZaXPdHepjUOo
5.fetchall_hashref($key_field) 取出所有内容并返回每行字段名和字段值对的哈希引用
[codesyntax lang="perl"]
#!/usr/bin/perl
###################################
### author: www.ttlsa.com ###
### QQ群: 39514058 ###
### E-mail: service@ttlsa.com ###
###################################
use DBI;
use Data::Dump qw(dump);
$driver="DBI:mysql";
$host="localhost:3306";
$dbname="test";
$user="root";
$passwd="123456";
$dbh=DBI->connect("$driver:$dbname:$host","$user","$passwd") or die DBI->errstr;
$sql="select * from ttlsa_user_00 limit 10";
$sth=$dbh->prepare($sql);
$rv=$sth->execute;
$hash_ref=$sth->fetchall_hashref(user_id);
dump($hash_ref);
print '-' x 20 ."\n";
print $hash_ref->{'10'}->{'email'}."\n\n\n";
while (($user_id,$value)=each %$hash_ref){
print "\$user_id: $user_id\n";
while (($key,$value1)=each %$value) {
print "$key ==> $value1\n";
}
}
[/codesyntax]
输出内容:
{
1 => {
email => "auto-gre-1\@ttlsa.com",
passwd => "Y9MoErtE+iZG5PkYHMJobhij58E",
user_id => 1,
user_name => "auto-gre-1",
},
2 => {
email => "auto-gre-2\@ttlsa.com",
passwd => "UqHxPoLmKY7ClyCZaXPdHepjUOo",
user_id => 2,
user_name => "auto-gre-2",
},
}
--------------------
auto-gre-10@ttlsa.com
$user_id: 6
passwd ==> ZWCagapChduSFnB2nJcQ3vOCYI4
email ==> auto-gre-6@ttlsa.com
user_id ==> 6
user_name ==> auto-gre-6
$user_id: 3
passwd ==> 9YiNWIw1bjqEMrCOmbtwFBBqc3U
email ==> auto-gre-3@ttlsa.com
user_id ==> 3
user_name ==> auto-gre-3
融合prepare( ),execute( )和fetchrow_arrayref( )方法的操作:
6.selectrow_array($statement) 返回一行数据的数组
[codesyntax lang="perl"]
#!/usr/bin/perl
###################################
### author: www.ttlsa.com ###
### QQ群: 39514058 ###
### E-mail: service@ttlsa.com ###
###################################
use DBI;
use Data::Dump qw(dump);
$driver="DBI:mysql";
$host="localhost:3306";
$dbname="test";
$user="root";
$passwd="123456";
$dbh=DBI->connect("$driver:$dbname:$host","$user","$passwd",{ PrintError=>0, RaiseError=>1 }) or die DBI->errstr;
$sql="select * from ttlsa_user_00 limit 10";
@row_ary=$dbh->selectrow_array($sql);
dump(@row_ary);
my ($count,$max)=@row_ary=$dbh->selectrow_array("select count(*),max(user_id) from ttlsa_user_00");
print "number: $count; max: $max\n";
[/codesyntax]
输出内容:
(
1,
"auto-gre-1\@ttlsa.com",
"Y9MoErtE+iZG5PkYHMJobhij58E",
"auto-gre-1",
)
number: 10; max: 10
7.selectrow_arrayref($statement) 返回一行数据的引用数组
[codesyntax lang="perl"]
#!/usr/bin/perl
###################################
### author: www.ttlsa.com ###
### QQ群: 39514058 ###
### E-mail: service@ttlsa.com ###
###################################
use DBI;
use Data::Dump qw(dump);
$driver="DBI:mysql";
$host="localhost:3306";
$dbname="test";
$user="root";
$passwd="123456";
$dbh=DBI->connect("$driver:$dbname:$host","$user","$passwd",{ PrintError=>0, RaiseError=>1 }) or die DBI->errstr;
$sql="select * from ttlsa_user_00 limit 10";
$ary_ref=$dbh->selectrow_arrayref($sql);
dump($ary_ref);
$ary_ref=$dbh->selectrow_arrayref("select count(*),max(user_id) from ttlsa_user_00");
my ($count,$max)=@$ary_ref;
print "number: $count; max: $max\n";
[/codesyntax]
输出内容:
[
1,
"auto-gre-1\@ttlsa.com",
"Y9MoErtE+iZG5PkYHMJobhij58E",
"auto-gre-1",
]
number: 10; max: 10
8.selectrow_hashref($statement) 返回一行数据的字段名与字段值的哈希引用
[codesyntax lang="perl"]
#!/usr/bin/perl
###################################
### author: www.ttlsa.com ###
### QQ群: 39514058 ###
### E-mail: service@ttlsa.com ###
###################################
use DBI;
use Data::Dump qw(dump);
$driver="DBI:mysql";
$host="localhost:3306";
$dbname="test";
$user="root";
$passwd="123456";
$dbh=DBI->connect("$driver:$dbname:$host","$user","$passwd",{ PrintError=>0, RaiseError=>1 }) or die DBI->errstr;
$sql="select * from ttlsa_user_00 limit 10";
$hash_ary=$dbh->selectrow_hashref($sql);
dump($hash_ary);
$hash_ary=$dbh->selectrow_hashref("select count(*) as count ,max(user_id) as max from ttlsa_user_00");
print "number: $$hash_ary{'count'}; max: $$hash_ary{'max'}\n";
[/codesyntax]
输出内容:
{
email => "auto-gre-1\@ttlsa.com",
passwd => "Y9MoErtE+iZG5PkYHMJobhij58E",
user_id => 1,
user_name => "auto-gre-1",
}
number: 10; max: 10
9.selectall_arrayref($statement) 取出所有行并返回包含所有字段值的引用数组
[codesyntax lang="perl"]
#!/usr/bin/perl
###################################
### author: www.ttlsa.com ###
### QQ群: 39514058 ###
### E-mail: service@ttlsa.com ###
###################################
use DBI;
use Data::Dump qw(dump);
$driver="DBI:mysql";
$host="localhost:3306";
$dbname="test";
$user="root";
$passwd="123456";
$dbh=DBI->connect("$driver:$dbname:$host","$user","$passwd",{ PrintError=>0, RaiseError=>1 }) or die DBI->errstr;
$sql="select * from ttlsa_user_00 limit 2";
$array_ary=$dbh->selectall_arrayref($sql);
dump($array_ary);
print "------------------------------\n";
foreach $row (@$array_ary){
dump($row);
print "------------------------------\n";
foreach $element (@$row) {
print "'$element',";
}
print "\n";
}
[/codesyntax]
输出内容:
[
[
1,
"auto-gre-1\@ttlsa.com",
"Y9MoErtE+iZG5PkYHMJobhij58E",
"auto-gre-1",
],
[
2,
"auto-gre-2\@ttlsa.com",
"UqHxPoLmKY7ClyCZaXPdHepjUOo",
"auto-gre-2",
],
]
------------------------------
[
1,
"auto-gre-1\@ttlsa.com",
"Y9MoErtE+iZG5PkYHMJobhij58E",
"auto-gre-1",
]
------------------------------
'1','auto-gre-1@ttlsa.com','Y9MoErtE+iZG5PkYHMJobhij58E','auto-gre-1',
[
2,
"auto-gre-2\@ttlsa.com",
"UqHxPoLmKY7ClyCZaXPdHepjUOo",
"auto-gre-2",
]
------------------------------
'2','auto-gre-2@ttlsa.com','UqHxPoLmKY7ClyCZaXPdHepjUOo','auto-gre-2',
10.selectall_hashref($statement,$key_field) 取出所有行并返回每行字段名和字段值对的哈希引用
[codesyntax lang="perl"]
#!/usr/bin/perl
###################################
### author: www.ttlsa.com ###
### QQ群: 39514058 ###
### E-mail: service@ttlsa.com ###
###################################
use DBI;
use Data::Dump qw(dump);
$driver="DBI:mysql";
$host="localhost:3306";
$dbname="test";
$user="root";
$passwd="123456";
$dbh=DBI->connect("$driver:$dbname:$host","$user","$passwd",{ PrintError=>0, RaiseError=>1 }) or die DBI->errstr;
$sql="select * from ttlsa_user_00 limit 2";
$hash_ary=$dbh->selectall_hashref($sql,user_id);
dump($hash_ary);
print "------------------------------\n";
foreach $user_id (keys %$hash_ary) {
print "user_id: $user_id\n";
print "user_name: $hash_ary->{$user_id}->{user_name}\n";
}
[/codesyntax]
输出内容:
{
1 => {
email => "auto-gre-1\@ttlsa.com",
passwd => "Y9MoErtE+iZG5PkYHMJobhij58E",
user_id => 1,
user_name => "auto-gre-1",
},
2 => {
email => "auto-gre-2\@ttlsa.com",
passwd => "UqHxPoLmKY7ClyCZaXPdHepjUOo",
user_id => 2,
user_name => "auto-gre-2",
},
}
------------------------------
user_id: 1
user_name: auto-gre-1
user_id: 2
user_name: auto-gre-2
评论