Perl MySQL Tutorial – A Complete Guide

By /
Perl MySQL Tutorial A Complete Guide

In this tutorial, we’ll learn how to Update, Delete, or Insert records into MySQL table using Perl programming language. We’ll see the system dependencies as well and also set up the work environment.

Also read: Perl Tutorial: How to connect to MySQL with Perl

Pre-requisites

There are two system dependencies for this tutorial, if you want to follow along with this tutorial then you must ensure these two applications are installed on your system:

1. MySQL: You can check if MySQL is installed on your desktop by writing the below command on the command prompt-

mysql --versionCode language: Bash (bash)

If MySQL is not installed, download MySQL (Server, Workbench, and Command-Line) using the below-mentioned link:
https://dev.mysql.com/downloads/installer/

2. Perl: You can check if Perl is installed on your system by using the exact command which we saw for MySQL-

perl --versionCode language: Bash (bash)

If Perl is not installed, download and install using the below-mentioned link:
https://www.perl.org/get.html

3. Perl DBD install: Perl DBD is for database driver, basically, it is installed when we want a Perl program to establish a connection with a database, in our case, we’ll install MySQL Perl DBD.

To install Perl DBD for MySQL, we need to open the CPAN shell using the command prompt. In the command prompt type:

CPAN -MCPAN -e shellCode language: Bash (bash)

After entering into CPAN, we’ll install MySQL DBD using the below command:

install DBD:mysqlCode language: Bash (bash)

Steps to connect to MySQL using Perl

There are four steps whenever you want to establish a connection with MySQL or any other DBMS using Perl:

  1. Establish Connection
  2. Prepare SQL statement
  3. Execute the statement
  4. Fetch ResultSet

Create Table using Perl

CREATE TABLE command is used whenever we want to create a table. In this section we’ll see how we can create a table in MySQL using Perl, we’ll create a table called “example” and there will be two columns, number, and alphabet. Let’s dive right into the code:

use DBI;
 
#Create connection
$myconn = DBI->connect('DBI:mysql:db','sam','root');
 
#Write SQL query and execute
$myconn->do("CREATE TABLE example (number int, alphabet varchar(1)) ENGINE=InnoDB");
 
#disconnect connection
$myconn->disconnect();Code language: Perl (perl)

Save this code with .pl extension and execute the code by opening a command prompt, changing the directory where you saved the file, and writing the below command:

perl filename.plCode language: Bash (bash)

Let’s check if the changes are successful, for that we will open MySQL using either command promt or MySQL Workbench. I’ll show you the output from the command prompt. use the below SQL command to see the table:

DESC example; Code language: SQL (Structured Query Language) (sql)
Create Table Mysql Perl

In the above snippet, we can see that the table is created successfully using Perl.

Insert Data into the Table using Perl

INSERT statement is used when we want to insert records into the table, in this section, we’ll see how to insert records into the MySQL table with the help of Perl. We are going to use a STUDENTS table that has fields such as Roll Number (rollno), first name (fname), last name (lname), division, and section.

Let’s write our code, we’ll be following the four steps that we understood from the previous section.

use DBI;
 
#Create connection (DBI:mysql: database name , user name, password)
$myconn = DBI->connect('DBI:mysql:db','sam','root');
 
#Write SQL query and execute
$myconn->do("INSERT INTO student VALUES (8, 'Ridhi', 'Maan', 11, 'A')");
 
#disconnect connection
$myconn->disconnect();Code language: Perl (perl)

Save this code with .pl extension and execute the code in the same way as explained in the previous section.

Perl Mysql Insert Record Into Table

In the above snippet, you can see that the 8th record has been inserted into the table successfully after executing the Perl code.

Delete Data from Database using Perl

In this section we will go through the same steps we mentioned in the previous section, let’s jump into writing the code when we want to delete a record, we will work with the student table, and will delete the record that has 3 as roll number.

use DBI;
 
#Create connection
$myconn = DBI->connect('DBI:mysql:db','sam','root');
 
#Write SQL query and execute
$myconn->do("DELETE FROM student WHERE rollno = 3");
 
#disconnect connection
$myconn->disconnect();Code language: Perl (perl)

Save the file with .pl and execute the code using the below command in the command prompt.

perl filename.plCode language: Bash (bash)
Delete Record Using Perl Mysql

You can see, that the student detail where the roll number was 3 has been deleted.

Update Record in a Table using Perl

UPDATE command is used whenever we want to update any records or a particular value of a record. Let’s quickly see Perl code for updating the table value using Perl. We’ll be using the students’ table and the problem is, a student’s name was mistyped as ‘Kris’ instead of ‘Krish’, and his roll number is 8. let’s change the name using the below code:

use DBI;
 
#Create connection
$myconn = DBI->connect('DBI:mysql:db','sam','root');
 
#Write SQL query and execute
$myconn->do("UPDATE student SET fname = 'Krish' WHERE rollno = 7");
 
#disconnect connection
$myconn->disconnect();Code language: Perl (perl)

Let’s save the code and execute it. After executing we’ll see check whether it updated the name or not.

Update Record Mysql Perl

We can see that the name has been successfully updated as ‘Krish’.

Conclusion

In this tutorial, we learned everything about MySQL Perl to get started. We learned about Insert, Update, Create and Delete on MySQL table using Perl programming.