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();
好了，到此简单的数据库操作就完成了
页:
[1]