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
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:
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:
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:
- Create a Connection
- Write Delete Command
- Execute Query
- 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)
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)
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)
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.