想利用perl语言操作数据库&＃xff0c;这里简单使用perl语言进行插入数据并且删除数据库记录
有数据库library&＃xff0c;里面有表members
首先进入数据库
| sudo -u root -p
密码&＃xff1a;******
|
2.创建数据库library
3.创建表members
| create table members (member_id int(11) NOT NULL auto_increment ,fname varchar(20) NOT NULL,lname varchar(20) NOT NULL,tel varchar(15) NOT NULL ,email varchar(50) NOT NULL,PRIMARY KEY (member_id));
|
4.插入数据
| insert into members (1,&＃39;ceagle&＃39;,&＃39;jiangfeng&＃39;,&＃39;123456789&＃39;,&＃39;123&＃64;gmail.com&＃39;);
|
| insert into members (1,&＃39;hu&＃39;,&＃39;xuelin&＃39;,&＃39;123456789&＃39;,&＃39;123&＃64;gmail.com&＃39;);
|
...
5.查看数据
| &＃43;-----------&＃43;--------&＃43;-----------&＃43;-----------&＃43;-----------------------&＃43;
| member_id | fname | lname | tel | email |
&＃43;-----------&＃43;--------&＃43;-----------&＃43;-----------&＃43;-----------------------&＃43;
| 1 | 123 | 1232323 | 123456789 | 123&＃64;gmail.com |
| 2 | 12 | 123123 | 123456789 | 123&＃64;gmail.com |
&＃43;-----------&＃43;--------&＃43;-----------&＃43;-----------&＃43;-----------------------&＃43;
2 rows in set (0.00 sec)
|
6编写perl语言访问数据库
| #!/usr/bin/perl -w
#The script is used to connect mysql and show table members of the
#database library
use DBI();
#connect
my $dbh &＃61; DBI->connect("DBI:mysql:database&＃61;library;host&＃61;localhost","root","123456",
{&＃39;RaiseError&＃39;&＃61;>1,&＃39;AutoCommit&＃39;&＃61;>0});
#iterate through resultset
my $sth &＃61; $dbh->prepare("SELECT * from members");
$sth->execute();
print "member_id\tfname\tlname\ttel\t\temail","\n";
while (my $ref &＃61; $sth->fetchrow_hashref()) {
print "$ref->{&＃39;member_id&＃39;}\t\t$ref->{&＃39;fname&＃39;}\t$ref->{&＃39;lname&＃39;}\t$ref->{&＃39;tel&＃39;}\t$ref->{&＃39;email&＃39;}\n";
}
if ($&＃64;){
print "Transaction aborted:$&＃64;";
$dbh->rollback();
}
#clean up
$sth->finish();
$dbh->disconnect();
|
此脚本可以访问library下的members表
7修改脚本使其能够向数据库插入数据并且将插入的数据删除
| #!/usr/bin/perl -w
#The script is used to connect mysql and show table members of the
#database library
use DBI();
#connect
my $dbh &＃61; DBI->connect("DBI:mysql:database&＃61;library;host&＃61;localhost","root","ceagle",
{&＃39;RaiseError&＃39;&＃61;>1,&＃39;AutoCommit&＃39;&＃61;>0});
#execute query
#my $sth &＃61; $dbh->prepare("SELECT * from members");
#$sth->execute();
eval
{
#insert data to members
$dbh->do("insert into members values (NULL,&＃39;cao&＃39;,&＃39;jiang&＃39;,&＃39;55256262&＃39;,&＃39;ceaglechina\&＃64;gmail.com&＃39;)");
$dbh->commit();
};
#iterate through resultset
my $sth &＃61; $dbh->prepare("SELECT * from members");
$sth->execute();
print "member_id\tfname\tlname\ttel\t\temail","\n";
while (my $ref &＃61; $sth->fetchrow_hashref()) {
print "$ref->{&＃39;member_id&＃39;}\t\t$ref->{&＃39;fname&＃39;}\t$ref->{&＃39;lname&＃39;}\t$ref->{&＃39;tel&＃39;}\t$ref->{&＃39;email&＃39;}\n";
} #Delete the data
eval
{
$dbh->do("delete from members where fname &＃61; &＃39;cao&＃39; ");
$dbh->commit(); };
if ($&＃64;){
print "Transaction aborted:$&＃64;";
$dbh->rollback();
}
#clean up
$sth->finish();
$dbh->disconnect();
|
好了&＃xff0c;到此简单的数据库操作就完成了
京公网安备 11010802041100号