perl语言访问数据库(mysql)

8516830

　　想利用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();

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