perl--DBI 模块中提取数据的fetch方法汇总（三）

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