perl下十種fetch數據的區別以及返回數據結構
1.fetchrow_array 提取下一行數據并將字段保存在數組中返回
[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 115_users limit 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
新聞熱點
疑難解答
圖片精選
