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();