perl下十种fetch数据的区别以及返回数据结构

1.fetchrow_array 提取下一行数据并将字段保存在数组中返回

[codesyntax lang="perl"]

#!/usr/bin/perl

###################################

### author: www.ttlsa.com ###

### QQ群: 39514058 ###

### E-mail: [email protected] ###

###################################

 

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: [email protected] ###

###################################

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: [email protected]

[

2,

"auto-gre-2\@ttlsa.com",

"UqHxPoLmKY7ClyCZaXPdHepjUOo",

"auto-gre-2",

]

email: [email protected]

 

3.fetchrow_hashref 提取下一行数据并返回一个包含字段名和字段值对的哈希引用

[codesyntax lang="perl"]

#!/usr/bin/perl

###################################

### author: www.ttlsa.com ###

### QQ群: 39514058 ###

### E-mail: [email protected] ###

###################################

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: [email protected] ###

###################################

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: [email protected] ###

###################################

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″,

},

}

——————–

[email protected]

 

$user_id: 6

passwd ==> ZWCagapChduSFnB2nJcQ3vOCYI4

email ==> [email protected]

user_id ==> 6

user_name ==> auto-gre-6

$user_id: 3

passwd ==> 9YiNWIw1bjqEMrCOmbtwFBBqc3U

email ==> [email protected]

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: [email protected] ###

###################################

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: [email protected] ###

###################################

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: [email protected] ###

###################################

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: [email protected] ###

###################################

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′,’[email protected]’,'Y9MoErtE+iZG5PkYHMJobhij58E’,'auto-gre-1′,

[

2,

"auto-gre-2\@ttlsa.com",

"UqHxPoLmKY7ClyCZaXPdHepjUOo",

"auto-gre-2",

]

——————————

’2′,’[email protected]’,'UqHxPoLmKY7ClyCZaXPdHepjUOo’,'auto-gre-2′,

 

10.selectall_hashref($statement,$key_field) 取出所有行并返回每行字段名和字段值对的哈希引用

[codesyntax lang="perl"]

#!/usr/bin/perl

###################################

### author: www.ttlsa.com ###

### QQ群: 39514058 ###

### E-mail: [email protected] ###

###################################

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