Perl MySQL: How to Delete Data from MySQL table using Perl

By /
Perl Tutorial How To Delete Data From MySQL Table Using Perl

Introduction

Whenever we are working on huge projects such as System Management, development of Web Applications, etc, then a huge amount of data also exists. To maintain this data we incorporated Database Management System(DBMS). MySQL is one such DBMS which is a Relational Database Management System and is a widely used RDBMS. In this tutorial, we’ll be using MySQL RDBMS.

Perl is a platform-independent multi-purpose programming language. It is used for a wide range of applications from text manipulation to cloud management. In this tutorial, we’ll be using the Perl programming language.

In this tutorial, we’ll learn how to delete a MySQL table’s data using the Perl programming language.

Also read: Perl MySQL: How to Update MySQL table using Perl

Pre-requisites

For this tutorial there are three system pre-requisites, if you have all of them installed then you can skip this section. The list of pre-requisites is mentioned below:

1. Perl: Perl must be installed on your system, with a basic understanding of programming. If Perl is not installed on your system, download it from the below link:
https://www.perl.org/get.html

2. MySQL: MySQL must be installed on your systems, and you must have a basic understanding of SQL. If MySQL is not installed on your desktop then download it from the below link:
https://dev.mysql.com/downloads/

3. Perl DBD: This is a database driver for Perl, you’ll be able to install it if you have Perl installed on your system. To install Perl MySQL DBD. You must write the below-mentioned commands, open the command prompt, and follow the instructions.

Firstly, we need to open the CPAN shell, CPAN is a module manager for Perl, to open CPAN in the command prompt enter this command:

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

Once, your CPAN shell is up and running write the below command to install MySQL DBD.

install DBD:mysqlCode language: Bash (bash)

Steps to Delete Data from MySQL Table using Perl

There are four steps to delete MySQL table data using Perl:

  1. Create a Connection
  2. Write Delete Command
  3. Execute Query
  4. Close Connection

Create a Connection

The first step is to connect MySQL with Perl, to do that we’ll need the Database name, username, and password. Below is the Perl code to create a connection with MySQL:

$myconn = DBI->connect('DBI:mysql:db','sam','root');Code language: Perl (perl)

Write Delete Command

DELETE statement is a Data Manipulation Language(DML) in SQL. Below is the syntax for the DELETE statement.

DELETE FROM tableName WHERE condition;Code language: SQL (Structured Query Language) (sql)

Execute Query

The fourth step is to execute the DELETE statement, do() method is used to execute a query. Below is the code for executing a query:

$myconn -> do(DELETE FROM tableName WHERE condition);Code language: Perl (perl)

Close Connection

The fourth and the last step is to close the connection to the database, the code below is for disconnecting from MySQL and we use the disconnect() method:

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

A Few Examples To Delete MySQL Data Using Perl

In this section, we’ll see a few examples for MySQL Perl delete table data.

First, let’s see an example where we’ll use the employee table and delete the record where the empid is 4.

use DBI;

#Create connection
$myconn = DBI->connect('DBI:mysql:db','sam','root');

#Write SQL query and execute
$myconn->do("DELETE FROM employee WHERE empid = 4");

#disconnect connection
$myconn->disconnect();Code language: Perl (perl)

Now, what if we want to delete all the records from a particular table. To do that we’ll use the below code:

use DBI;

#Create connection
$myconn = DBI->connect('DBI:mysql:db','sam','root');

#Write SQL query and execute
$myconn->do("DELETE FROM employee");

#disconnect connection
$myconn->disconnect();Code language: Perl (perl)

To execute these codes, we need to first save these files with a .pl extension. Afterward, we have to execute these codes in order to make them work. To execute the code, we need to open the command prompt and write the below code.

perl fileName.plCode language: Bash (bash)

We can check if the code worked properly by checking the table, for that let’s open the command prompt and login to MySQL.

mysql -u username -pCode language: Bash (bash)

After logging into MySQL, let’s view the table and see if the record with empid 4 is deleted or not. The query is written down below:

SELECT * FROM table;Code language: Bash (bash)
Delete Data Mysql Perl

Conclusion

In this tutorial, we learn about the steps that are used whenever we are connecting to MySQL using Perl programming language and how to delete records of a particular table in MySQL using Perl programming language. We also saw a few examples of Perl MySQL delete statements.