perl--DBI 模塊中提取數據的fetch方法匯總（三）

2019-11-06 08:22:03

字體：大中小

來源：轉載

供稿：網友

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]

輸出內容:

(

"auto-gre-1/@ttlsa.com",

"Y9MoErtE+iZG5PkYHMJobhij58E",

"auto-gre-1",

)

password: Y9MoErtE+iZG5PkYHMJobhij58E

(

"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]

輸出內容:

[

"auto-gre-1/@ttlsa.com",

"Y9MoErtE+iZG5PkYHMJobhij58E",

"auto-gre-1",

]

email: auto-gre-1@ttlsa.com

[

"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]

輸出內容:

[

"auto-gre-1/@ttlsa.com",

"Y9MoErtE+iZG5PkYHMJobhij58E",

"auto-gre-1",

[

"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]

輸出內容:

(

"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]

輸出內容:

[

"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]

輸出內容:

[

"auto-gre-1/@ttlsa.com",

"Y9MoErtE+iZG5PkYHMJobhij58E",

"auto-gre-1",

[

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

"UqHxPoLmKY7ClyCZaXPdHepjUOo",

"auto-gre-2",

]

------------------------------

[

"auto-gre-1/@ttlsa.com",

"Y9MoErtE+iZG5PkYHMJobhij58E",

"auto-gre-1",

]

------------------------------

'1','auto-gre-1@ttlsa.com','Y9MoErtE+iZG5PkYHMJobhij58E','auto-gre-1',

[

"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

上一篇：Bioperl的安裝（一）

下一篇：perl--DBI使用詳解（二）

學習交流

索泰發布一款GTX 1070 Mini迷你版本:小機

索泰發布一款GTX 1070 Mini迷你版本:小機箱大愛...

熱門圖片

猜你喜歡的新聞

猜你喜歡的關注

新聞熱點

榮耀總裁趙明烏鎮演講：榮耀首款5G手機V30下月發布

2019-10-23 09:17:05

搜狐張朝陽：回歸媒體是搜狐重新崛起的關鍵

2019-10-21 09:20:02

華為輪值董事長郭平：虛擬技術創造現實價值

2019-10-21 09:00:12

滴滴英文服務上線兩周年用戶已超200萬

2019-09-26 08:57:12

華為推出全球至快AI訓練集群Atlas900

2019-09-25 08:46:36

馬斯克：特斯拉正組建中國技術團隊

2019-09-25 08:15:43

疑難解答

圖片精選

網友關注