perl创建MySql数据库的脚本

##--------------------------------------------------------------
##利用perl DBI创建数据库stucourse,并创建student,course,grade表
##--------------------------------------------------------------
use DBI;
my $db_name = "stucourse"; #数据库名,如果与现有数据库冲突，可改为其他名字
my $db_host = "localhost"; #主机名
my $db_port = '3306'; #端口号
my $username = "root"; #用户名
my $password = "123"; #密码
my $dsn = "dbi:mysql:database=${db_name};hostname=${db_host};port=${db_port}";#数据源

#获取驱动程序对象句柄
my $drh=DBI->install_driver("mysql");
#如果存在数据库$db_name,则删除之
if($rc = $drh->func("dropdb",$db_name ,$db_host,$username,$password,"admin") ){
print "drop database `",$db_name,"` successfully!n";
}
#创建数据库$db_name
$rc = $drh->func("createdb",$db_name ,$db_host,$username,$password,"admin")or
die "failed to create database ",$db_name,"!n";
print "create database `stucourse` successfully!n";

#获取数据库句柄
my $dbh = DBI -> connect ($dsn, $username, $password,{RaiseError => 1, PrintError => 0})or
die "failed to connect to the database!n",DBI->errstr();

#设置数据库字符集，防止中文乱码
my $charset = "set character_set_database=utf8";
my $sth = $dbh->prepare($charset);
$sth->execute();

#创建表course
my $query = "CREATE TABLE `course` ( "
."`cid` int(10) NOT NULL auto_increment,"
."`cno` varchar(20) NOT NULL, "
."`cname` varchar(20) default NULL, "
."PRIMARY KEY (`cid`)"
.")ENGINE=innodb DEFAULT CHARSET=utf8;";
my $sth = $dbh->prepare($query);
$sth->execute() or die "create table course error: ".$sth->errstr();
print "create table `course` successfully!n";

#创建表student
my $query = "CREATE TABLE `student` ("
."`sid` int(10) NOT NULL auto_increment,"
."`sno` varchar(20) NOT NULL,"
."`sname` varchar(20) default NULL,"
."PRIMARY KEY (`sid`)"
.")ENGINE=InnoDB DEFAULT CHARSET=utf8;";
my $sth = $dbh->prepare($query);
$sth->execute() or die "create table student error: ".$sth->errstr();
print "create table `student` successfully!n";

#创建表grade
my $query = "CREATE TABLE `grade` ("
 ."`gid` int(10) NOT NULL,"
 ."`cid` int(10) NOT NULL,"
 ."`sid` int(10) NOT NULL,"
 ."`grade` int(10) default NULL,"
 ."PRIMARY KEY (`gid`),"
 ."KEY `cid` (`cid`),"
 ."KEY `sid` (`sid`),"
 ."CONSTRAINT `cid` FOREIGN KEY (`cid`) REFERENCES `course`(`cid`),"
 ."CONSTRAINT `sid` FOREIGN KEY (`sid`) REFERENCES `student`(`sid`)"
 .") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
my $sth = $dbh->prepare($query);
$sth->execute() or die "create table grade error: ".$sth->errstr();
print "create table `grade` successfully!n";

#关闭数据库连接
$dbh->disconnect();