Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.mysqltutorial.org/perl-mysql/perl-mysql-create-table/ below:

Perl MySQL Create Table Tutorial

Summary: in this tutorial, we will show you how to use the Perl DBI API to create tables in a MySQL database.

Before doing anything else with the databases e.g., insert, update, delete, and query data, you need to create new tables to store the data.

To create a new table, you use the following steps:

1. Connect to the MySQL database.
2. Execute the CREATE TABLE statement by calling the  do() method of the database handle object.
3. Disconnect from the MySQL database.

We are going to create three tables in the perlmysqldb sample database:

• links: stores URLs including link_id, title, URL, and target.
• tags: stores tags for links including tag_id and tag.
• link_tags: stores the relationship between the links and tags tables, containing two fields link_id and tag_id.

The following diagram illustrates the tables:

The following is the Perl script that creates the tables:

use strict;
use warnings;
use v5.10; 

use DBI;

say "Perl MySQL Create Table Demo";


my $dsn = "DBI:mysql:perlmysqldb";
my $username = "root";
my $password = '';


my %attr = (PrintError=>0, RaiseError=>1);

my $dbh = DBI->connect($dsn,$username,$password, \%attr);


my @ddl =     (
		
		"CREATE TABLE tags (
			tag_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
			tag varchar(255) NOT NULL
	        ) ENGINE=InnoDB;",
	       
	       "CREATE TABLE links (
		  link_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
		  title varchar(255) NOT NULL,
		  url varchar(255) NOT NULL,
		  target varchar(45) NOT NULL
		) ENGINE=InnoDB;",
		
		"CREATE TABLE link_tags (
		  link_id int(11) NOT NULL,
		  tag_id int(11) NOT NULL,
		  PRIMARY KEY (link_id,tag_id),
		  KEY fk_link_idx (link_id),
		  KEY fk_tag_idx (tag_id),
		  CONSTRAINT fk_tag FOREIGN KEY (tag_id) 
		     REFERENCES tags (tag_id),
		  CONSTRAINT fk_link FOREIGN KEY (link_id) 
			 REFERENCES links (link_id) 
		) ENGINE=InnoDB"
	       );


for my $sql(@ddl){
  $dbh->do($sql);
}	       

say "All tables created successfully!";


$dbh->disconnect();Code language: Perl (perl)

How it works.

• First, create a connection to the perlmysqldb database by using the  connect() method.
• Next, define an array  @ddl that contains three  CREATE TABLE statements.
• Then, call  do() method of the database handles the object to execute each  CREATE TABLE statement inside the loop. We often use the  do() method for executing a non-select statement that does not return a result set.
• After that, display a message to indicate that all tables were created successfully.
• Finally, disconnect from the database.

The output of the script is as follows:

Perl MySQL Create Table Demo
All tables created successfully!

Now, you can check the perlmysqldb database to verify if the tables have been created successfully.

In this tutorial, you have learned how to create new tables from a Perl program in a MySQL database by using the  do() method of the database handle object.

Was this tutorial helpful?

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4