perl语言访问数据库(mysql)

想利用perl语言操作数据库，这里简单使用perl语言进行插入数据并且删除数据库记录
有数据库library，里面有表members
首先进入数据库

sudo -u root -p
密码：******


2.创建数据库library

create database 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,'ceagle','jiangfeng','123456789','123@gmail.com');


insert into members (1,'hu','xuelin','123456789','123@gmail.com');


...
5.查看数据

select * from members;


+-----------+--------+-----------+-----------+-----------------------+
| member_id | fname  | lname     | tel       | email |
+-----------+--------+-----------+-----------+-----------------------+
| 1         | 123    | 1232323   | 123456789 | 123@gmail.com |
| 2         | 12     | 123123    | 123456789 | 123@gmail.com |
+-----------+--------+-----------+-----------+-----------------------+
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 = DBI->connect("DBI:mysql:database=library;host=localhost","root","123456",
        {'RaiseError'=>1,'AutoCommit'=>0});
  #iterate through resultset

my $sth = $dbh->prepare("SELECT * from members");
$sth->execute();
print "member_id\tfname\tlname\ttel\t\temail","\n";
while (my $ref = $sth->fetchrow_hashref()) {
  print "$ref->{'member_id'}\t\t$ref->{'fname'}\t$ref->{'lname'}\t$ref->{'tel'}\t$ref->{'email'}\n";
}

if ($@){
    print "Transaction aborted:$@";
    $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 = DBI->connect("DBI:mysql:database=library;host=localhost","root","ceagle",
        {'RaiseError'=>1,'AutoCommit'=>0});
#execute query

#my $sth = $dbh->prepare("SELECT * from members");

#$sth->execute();

eval
{
    #insert data to members

        $dbh->do("insert into members values (NULL,'cao','jiang','55256262','ceaglechina\@gmail.com')");
        $dbh->commit();

};
#iterate through resultset

my $sth = $dbh->prepare("SELECT * from members");
$sth->execute();
print "member_id\tfname\tlname\ttel\t\temail","\n";
while (my $ref = $sth->fetchrow_hashref()) {
  print "$ref->{'member_id'}\t\t$ref->{'fname'}\t$ref->{'lname'}\t$ref->{'tel'}\t$ref->{'email'}\n";
}

#Delete the data

eval
{
    $dbh->do("delete from members where fname = 'cao' ");
    $dbh->commit();

};
if ($@){
    print "Transaction aborted:$@";
    $dbh->rollback();
}
#clean up

$sth->finish();
$dbh->disconnect();

好了，到此简单的数据库操作就完成了