Category: mysql

MySQL – Before Insert Trigger Table of content MySQL Before Insert Trigger Before Insert Trigger Using a Client Program ”; Previous Next As we have already learned, a Trigger is defined as a response to an event performed. In MySQL, a trigger is called a special stored procedure as it does not require to be invoked explicitly like other stored procedures. The trigger acts automatically whenever the desired event is fired. These events include executing SQL statements like INSERT, UPDATE and DELETE etc. MySQL Before Insert Trigger The Before Insert Trigger is a row-level trigger supported by the MySQL database. As its name suggests, this trigger is executed right before a value is being inserted into a database table. A row-level trigger is a type of trigger that goes off every time a row is modified. Simply, for every single transaction made in a table (like insertion, deletion, update), one trigger acts automatically. Whenever an INSERT statement is queried in the database, this Trigger is automatically executed first and then only the value is inserted into the table. Syntax Following is the syntax to create the BEFORE INSERT trigger in MySQL − CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW BEGIN — trigger body END; Example Let us see an example demonstrating the BEFORE INSERT trigger. In here, we are creating a new table STUDENT which contains the details of students in an institution, using the following query − CREATE TABLE STUDENT( Name varchar(35), Age INT, Score INT, Grade CHAR(10) ); Using the following CREATE TRIGGER statement, create a new trigger sample_trigger on the STUDENT table. Here, we are checking the score of each student and assigning them with a suitable grade. DELIMITER // CREATE TRIGGER sample_trigger BEFORE INSERT ON STUDENT FOR EACH ROW BEGIN IF NEW.Score < 35 THEN SET NEW.Grade = ”FAIL”; ELSE SET NEW.Grade = ”PASS”; END IF; END // DELIMITER ; Insert values into the STUDENT table using the regular INSERT statement as shown below − INSERT INTO STUDENT VALUES (”John”, 21, 76, NULL), (”Jane”, 20, 24, NULL), (”Rob”, 21, 57, NULL), (”Albert”, 19, 87, NULL); Verification To verify if the trigger has been executed, display the STUDENT table using the SELECT statement − Name Age Score Grade John 21 76 PASS Jane 20 24 FAIL Rob 21 57 PASS Albert 19 87 PASS Before Insert Trigger Using a Client Program In addition to create or show a trigger, we can also Perform the “Before Insert trigger” statement using a client program. Syntax PHP NodeJS Java Python To Perform the Before Insert Trigger through a PHP program, we need to execute the CREATE TRIGGER statement using the mysqli function query() as follows − $sql = “Create Trigger sample_trigger BEFORE INSERT ON STUDENT”.” FOR EACH ROW BEGIN IF NEW.Score < 35 THEN SET NEW.Grade = ”FAIL”; ELSE SET NEW.Grade = ”PASS”; END IF; END”; $mysqli->query($sql); To Perform the Before Insert Trigger through a JavaScript program, we need to execute the CREATE TRIGGER statement using the query() function of mysql2 library as follows − sql = `Create Trigger sample_trigger BEFORE INSERT ON STUDENT FOR EACH ROW BEGIN IF NEW.Score < 35 THEN SET NEW.Grade = ”FAIL”; ELSE SET NEW.Grade = ”PASS”; END IF; END`; con.query(sql); To Perform the Before Insert Trigger through a Java program, we need to execute the CREATE TRIGGER statement using the JDBC function execute() as follows − String sql = “Create Trigger sample_trigger BEFORE INSERT ON STUDENT FOR EACH ROW BEGIN IF NEW.Score < 35 THEN SET NEW.Grade = ”FAIL”; ELSE SET NEW.Grade = ”PASS”; END IF; END”; statement.execute(sql); To Perform the Before Insert Trigger through a python program, we need to execute the CREATE TRIGGER statement using the execute() function of the MySQL Connector/Python as follows − beforeInsert_trigger_query = ”CREATE TRIGGER sample_trigger BEFORE INSERT ON student FOR EACH ROW BEGIN IF NEW.Score < 35 THEN SET NEW.Grade = ”FAIL”; ELSE SET NEW.Grade = ”PASS”; END IF; END” cursorObj.execute(drop_trigger_query) Example Following are the programs − PHP NodeJS Java Python $dbhost = ”localhost”; $dbuser = ”root”; $dbpass = ”password”; $db = ”TUTORIALS”; $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db); if($mysqli->connect_errno ) { printf(“Connect failed: %s”, $mysqli->connect_error); exit(); } //printf(”Connected successfully.”); $sql = “Create Trigger sample_trigger BEFORE INSERT ON STUDENT”.” FOR EACH ROW BEGIN IF NEW.Score < 35 THEN SET NEW.Grade = ”FAIL”; ELSE SET NEW.Grade = ”PASS”; END IF; END”; if($mysqli->query($sql)){ printf(“Trigger created successfully…!n”); } $q = “INSERT INTO STUDENT VALUES (”John”, 21, 76, NULL)”; $result = $mysqli->query($q); if ($result == true) { printf(“Record inserted successfully…!n”); } $q1 = “SELECT * FROM STUDENT”; if($r = $mysqli->query($q1)){ printf(“Select query executed successfully…!”); printf(“Table records(Verification): n”); while($row = $r->fetch_assoc()){ printf(“Name: %s, Age: %d, Score %d, Grade %s”, $row[“Name”], $row[“Age”], $row[“Score”], $row[“Grade”]); printf(“n”); } } if($mysqli->error){ printf(“Failed..!” , $mysqli->error); } $mysqli->close(); Output The output obtained is as follows − Trigger created successfully…! Record inserted successfully…! Select query executed successfully…!Table records(Verification): Name: Jane, Age: 20, Score 24, Grade FAIL Name: John, Age: 21, Score 76, Grade PASS var mysql = require(”mysql2”); var con = mysql.createConnection({ host:”localhost”, user:”root”, password:”password” }); //Connecting to MySQL con.connect(function(err) { if (err) throw err; //console.log(“Connected successfully…!”); //console.log(“————————–“); sql = “USE TUTORIALS”; con.query(sql); sql = `Create Trigger sample_trigger BEFORE INSERT ON STUDENT FOR EACH ROW BEGIN IF NEW.Score <

MySQL – Before Delete Trigger Table of content MySQL Before Delete Trigger Before Delete Trigger Using Client Program ”; Previous Next In MySQL, a trigger is defined a special stored procedure that resides in the system catalogue, and is executed whenever an event is performed. It is called a special stored procedure as it does not require to be invoked explicitly like other stored procedures. The trigger acts automatically whenever the desired event is fired. Triggers are categorized into two types − Before Triggers After Triggers These triggers can be a response to either insertion operation on a table, update operation or deletion operation. Thus, these special stored procedures respond whenever INSERT, UPDATE or DELETE statements are executed. MySQL Before Delete Trigger The Before Delete Trigger is a row-level trigger supported by the MySQL database. The Before Delete Trigger is executed right before a value is deleted from a row of a database table. A row-level trigger is a type of trigger that goes off every time a row is modified. Simply, for every single transaction made in a table (like insertion, deletion, update), one trigger acts automatically. With this trigger, whenever a DELETE statement is executed in the database, the value is deleted from a table first followed by execution of the trigger set. Syntax Following is the syntax to create the BEFORE DELETE trigger in MySQL − CREATE TRIGGER trigger_name BEFORE DELETE ON table_name FOR EACH ROW BEGIN — trigger body END; Example In this example, we are creating a table named ”CUSTOMERS”, to demonstrate the BEFORE DELETE trigger on, using the following query − CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS VARCHAR(25), SALARY DECIMAL(18, 2), PRIMARY KEY(ID) ); Insert values into this table created using the following INSERT statements − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ), (2, ”Khilan”, 25, ”Delhi”, 1500.00 ), (3, ”Kaushik”, 23, ”Kota”, 2000.00 ), (4, ”Chaitali”, 25, ”Mumbai”, 6500.00 ), (5, ”Hardik”, 27, ”Bhopal”, 8500.00 ), (6, ”Komal”, 22, ”MP”, 4500.00 ), (7, ”Muffy”, 24, ”Indore”, 10000.00 ); Creating Another Table Now, let us create another empty table to store all former customers after being deleted from the main table ”CUSTOMERS” − CREATE TABLE OLD_CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS VARCHAR(25), SALARY DECIMAL(18, 2), PRIMARY KEY(ID) ); Using the following CREATE TRIGGER statement, create a new trigger ”before_delete_trigger” on the CUSTOMERS table to delete the customer details from CUSTOMERS table and insert them into another table “OLD_CUSTOMERS” − DELIMITER // CREATE TRIGGER before_delete_trigger BEFORE DELETE ON CUSTOMERS FOR EACH ROW BEGIN INSERT INTO OLD_CUSTOMERS VALUES (OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY); END // DELIMITER ; Delete details of ”old” customers from the CUSTOMERS table using the regular DELETE statement as shown below − DELETE FROM CUSTOMERS WHERE ID = 3; Verification To verify whether the details are deleted from the OCUSTOMERS table and added onto the OLD_CUSTOMERS table, let us try to retrieve both of their result-sets using SELECT queries. The records in CUSTOMERS table are as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 The records in OLD_CUSTOMERS table are as follows − ID NAME AGE ADDRESS SALARY 3 Kaushik 23 Kota 2000.00 As you can in the tables above, the data has been deleted from the CUSTOMERS table and added to the OLD_CUSTOMERS table. Before Delete Trigger Using Client Program We can also execute the Before Delete trigger statement using a client program, instead of SQL queries. Syntax PHP NodeJS Java Python To execute the Before Delete Trigger through a PHP program, we need to query the CREATE TRIGGER statement using the mysqli function query() as follows − $sql = “CREATE TRIGGER before_delete_trigger BEFORE DELETE ON CUSTOMERS FOR EACH ROW BEGIN INSERT INTO OLD_CUSTOMERS VALUES (OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY); END”; $mysqli->query($sql); To execute the Before Delete Trigger through a JavaScript program, we need to query the CREATE TRIGGER statement using the query() function of mysql2 library as follows − sql = `CREATE TRIGGER before_delete_trigger BEFORE DELETE ON CUSTOMERS FOR EACH ROW BEGIN INSERT INTO OLD_CUSTOMERS VALUES (OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY); END`; con.query(sql); To execute the Before Delete Trigger through a Java program, we need to query the CREATE TRIGGER statement using the JDBC function execute() as follows − String sql = “CREATE TRIGGER before_delete_trigger BEFORE DELETE ON CUSTOMERS FOR EACH ROW BEGIN INSERT INTO OLD_CUSTOMERS VALUES (OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY); END”; statement.execute(sql); To execute the Before Delete Trigger through a python program, we need to query the CREATE TRIGGER statement using the execute() function of the MySQL Connector/Python as follows − beforeDelete_trigger_query = ”CREATE TRIGGER {trigger_name} BEFORE DELETE ON {table_name} FOR EACH ROW BEGIN INSERT INTO {another_table} VALUES (OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY); END” cursorObj.execute(beforeDelete_trigger_query) Example Following are the programs − PHP NodeJS Java

MySQL – ANY Operator Table of content ANY Operator in MySQL ANY with “>” Operator ANY with “<” Operator ANY with “=” Operator ANY with “<>” Operator ANY with “<=” Operator ANY with “>=” Operator ANY Operator Using a Client Program ”; Previous Next The operators in MySQL have the same meaning as that of operators in mathematics. They are keywords that are used in MySQL statements for performing comparisons or logical operations. ANY Operator in MySQL The MySQL ANY keyword can be used with a comparison operator (such as =, <, >, <=, >=, <>) to compare a value with a set of values returned by the subquery. This operator will return true if the given condition is satisfied for any of the values in the set. This operator will return false if none of the values in the specified set satisfy the given condition. The ANY operator must be preceded by a standard comparison operator i.e. >, >=, , !=, and followed by a subquery. Syntax Following is the syntax of the ANY operator in MySQL − SELECT column_name1, column_name2, … FROM table_name WHERE column_name operator ANY (subquery); Where, column_name is the name of a column to be compared with a subquery. operator is a comparison operator such as =, <, >, <=, >=, or <>. subquery is a SELECT statement that returns a single column of values. Example Firstly, let us create a table named CUSTOMERS using the following query − CREATE TABLE CUSTOMERS ( ID INT AUTO_INCREMENT, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); The following query inserts 7 records into the above-created MySQL table − INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ), (2, ”Khilan”, 25, ”Delhi”, 1500.00 ), (3, ”Kaushik”, 23, ”Kota”, 2000.00 ), (4, ”Chaitali”, 25, ”Mumbai”, 6500.00 ), (5, ”Hardik”, 27, ”Bhopal”, 8500.00 ), (6, ”Komal”, 22, ”Hyderabad”, 4500.00 ), (7, ”Muffy”, 24, ”Indore”, 10000.00 ); Execute the below query to retrieve all the records present in the CUSTOMERS table − SELECT * FROM CUSTOMERS; Following is the CUSTOMERS table − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 ANY with “>” Operator The MySQL ANY operator can be used with the comparison operator “>” (greater than) to verify whether a particular column value is greater than the column value of any of the other records returned by the subquery. Example In the following query, we are selecting all records from the CUSTOMERS table where the SALARY column is greater than any of the salaries associated with customers whose age is 22. SELECT * FROM CUSTOMERS WHERE SALARY > ANY (SELECT SALARY FROM CUSTOMERS WHERE AGE = 22); Output The salary of customer with age 22 is 4500. The following are the customers whose salaries are greater than 4500 (age=22). ID NAME AGE ADDRESS SALARY 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 7 Muffy 24 Indore 10000.00 ANY with “<” Operator We can use the comparison operator “<“ (less than) with the MySQL ANY operator to verify whether a particular column value is less than the column value of any of the records returned by the subquery. Example In this query, we are selecting all records from the CUSTOMERS table where the SALARY column is less than any of the salaries associated with customers whose age is 32. SELECT * FROM CUSTOMERS WHERE SALARY < ANY (SELECT SALARY FROM CUSTOMERS WHERE AGE = 32); Output The salary of 32 aged customer is 2000. The only customer with salary less than 2000 is ”Khilan” − ID NAME AGE ADDRESS SALARY 2 Khilan 25 Delhi 1500.00 ANY with “=” operator We can use the MySQL ANY operator with the comparison operator “=” (equal to) to fetch the records from a table where a column value is equal to any value returned by a subquery. Example Here, we are trying to select all records from the CUSTOMERS table where the AGE column matches any of the AGE associated with customers named “Khilan”. SELECT * FROM CUSTOMERS WHERE AGE = ANY (SELECT AGE FROM CUSTOMERS WHERE NAME = “Khilan”); Output The age of ”khilan” is 25. Another customer whose age is equal to 25 is ”Chaitali”. ID NAME AGE ADDRESS SALARY 2 Khilan 25 Delhi 1500.00 4 Chaitali 25 Mumbai 6500.00 ANY with “<>” Operator We can use the MySQL ANY operator with “<>” (not equal to) comparison operator to fetch the records from a table where a column value is not equal to any value returned

MySQL – Show Indexes Table of content The MySQL SHOW INDEX Statement With IN Clause With WHERE Clause Show Indexes Using Client Program ”; Previous Next A MySQL Index is a type of special lookup table that is used to make data retrieval easier in a database. It points to the actual data in the database. MySQL allows various types of indexes to be created on one or more columns in a table. They are: Primary Key Index Unique Index Simple Index Composite Index Implicit Index To check if any of these indexes are defined on a table or not, MySQL provides the SHOW INDEX statement. The MySQL SHOW INDEX Statement The SHOW INDEX Statement of MySQL is used to list out the information about table index. The vertical-format output (specified by G) in MySQL often is used with this statement, to avoid a long line wraparound. Syntax Following is the basic syntax of the SHOW INDEX Statement − SHOW INDEX FROM table_name; Example In this example, we are create a new table CUSTOMERS and adding a PRIMARY KEY index to one of its columns using the following CREATE TABLE query − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY(ID), INDEX(NAME) ); Now, we can display the indexes present on the CUSTOMERS table using the following SHOW INDEX query − SHOW INDEX FROM CUSTOMERSG Output The vertical-output will be displayed as − *************************** 1. row ************************ Table: customers Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: ID Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL *************************** 2. row ************************ Table: customers Non_unique: 1 Key_name: NAME Seq_in_index: 1 Column_name: NAME Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL 2 rows in set (0.01 sec) With IN Clause In this example, let us first create an index on the AGE column of CUSTOMERS table using the following CREATE INDEX query − CREATE INDEX AGE_INDEX ON CUSTOMERS (AGE); You can also retrieve the information by specifying the database name as − SHOW INDEX IN CUSTOMERS FROM sampleG Output The output will be the same as above − *************************** 1. row *************************** Table: customers Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: ID Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL *************************** 2. row *************************** Table: customers Non_unique: 1 Key_name: NAME Seq_in_index: 1 Column_name: NAME Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL 2 rows in set (0.01 sec) With WHERE Clause As the indexes are displayed in a table format, we can use a WHERE clause with SHOW INDEX statement to retrieve specified indexes matching a given condition. SHOW INDEX IN CUSTOMERS WHERE Column_name = ”NAME”G Output The index created on NAME column is displayed − *************************** 1. row ************************ Table: customers Non_unique: 1 Key_name: NAME Seq_in_index: 1 Column_name: NAME Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL 1 row in set (0.00 sec) Show Indexes Using Client Program We can also display index information on a MySQL table using a client program. Syntax Following are the syntaxes to show indexes on a MySQL table using various programming languages − PHP NodeJS Java Python To show an index from MySQL table through a PHP program, we need to execute the SHOW INDEX statement using the query() function provided by mysqli connector as follows − $sql = “SHOW INDEX FROM tutorials_table”; $mysqli->query($sql); To show an index from MySQL table through a JavaScript program, we need to execute the SHOW INDEX statement using the query() function of mysql2 library as follows − sql = “SHOW INDEXES FROM temp”; con.query(sql); To show an index from MySQL table through a Java program, we need to execute the SHOW INDEX statement using the executeQuery() function of JDBC as follows − String sql = “SHOW INDEXES FROM tutorials_tbl”; st.executeQuery(sql); To show an index from MySQL table through a Python program, we need to execute the SHOW INDEX statement using the execute() function of the MySQL Connector/Python as follows − rename_view_query = “SHOW INDEXES FROM tutorials_tbl” cursorObj.execute(rename_view_query) Example Following are the programs − PHP NodeJS Java Python $dbhost = ”localhost”; $dbuser = ”root”; $dbpass = ”password”; $dbname = ”TUTORIALS”; $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname); if ($mysqli->connect_errno) { printf(“Connect failed: %s”, $mysqli->connect_error); exit(); } // printf(”Connected successfully.”); // SHOW INDEX $sql = “SHOW INDEX FROM tutorials_table”; if ($index = $mysqli->query($sql)) { printf(“Index shown successfully!.”); while ($indx = mysqli_fetch_row($index)) { print_r($indx); } } if ($mysqli->errno) { printf(“Index could not be shown!.”, $mysqli->error); } $mysqli->close(); Output The output obtained is as follows − Index shown successfully!. Array ( [0] => tutorials_tbl [1] => 0 [2] => PRIMARY [3] => 1 [4] => tutorial_id [5] => A [6] => 3 [7] => [8] => [9] => [10] => BTREE [11] => [12] => [13] => YES [14] => ) Array ( [0] => tutorials_tbl [1] => 0 [2] => UIID [3] => 1 [4] => tutorial_id [5] => A [6] => 3 [7] => [8] => [9] => [10] => BTREE [11] => [12] => [13]

MySQL – Limit Table of content MySQL Limit Clause LIMIT with WHERE Clause LIMIT with ORDER BY clause Limit Clause Using a Client Program ”; Previous Next MySQL Limit Clause The LIMIT clause in MySQL can be used to specify the number of records to return. This clause is mostly used when dealing with tables that have thousands of records. It accepts one or two arguments (offset or count). The values of both arguments should be either be positive integers or zero. The offset of the first row starts from 0, not from 1 and the count of the first row starts from 1. Let us understand it better using the following picture: Assume the name of the above table is students. If we execute the above-mentioned query, we will get the output as Mahika, Aarohi, and Nikhil. Syntax Following is the generic syntax of MySQL Limit clause − SELECT column1, column2, … FROM table_name LIMIT number; Where, the LIMIT clause specifies the maximum number of rows from the table to return. Example The following example demonstrates the usage of the MySQL Limit query. First of all, let us create a table named CUSTOMERS using the following query − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Here, we are inserting 7 records into the above-created table using the following INSERT INTO statement − INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ), (2, ”Khilan”, 25, ”Delhi”, 1500.00 ), (3, ”Kaushik”, 23, ”Kota”, 2000.00 ), (4, ”Chaitali”, 25, ”Mumbai”, 6500.00 ), (5, ”Hardik”, 27, ”Bhopal”, 8500.00 ), (6, ”Komal”, 22, ”Hyderabad”, 4500.00 ), (7, ”Muffy”, 24, ”Indore”, 10000.00 ); Execute the following query to fetch all the records present in the CUSTOMERS table − SELECT * FROM CUSTOMERS; Following is the CUSTOMERS table − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Now, we are selecting the first four records from the CUSTOMERS table using the MySQL LIMIT clause in conjunct with SELECT statement − SELECT * FROM CUSTOMERS LIMIT 4; Output As we can see the output below, it returned the first four rows from the CUSTOMERS table − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 Example In the following query, we are selecting rows from the CUSTOMERS table starting from the third row (offset 2) from then four rows − SELECT * FROM CUSTOMERS LIMIT 2,4; Output When we execute the above query, the output is obtained as follows − ID NAME AGE ADDRESS SALARY 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 LIMIT with WHERE Clause In MySQL, we can use the LIMIT clause along with the WHERE clause in a SELECT statement to specify the number of rows returned from the query based on the conditions. Syntax Following is the generic syntax − SELECT column1, column2, … FROM table_name WHERE condition LIMIT number; Example In the query below, we are selecting the first two rows from the CUSTOMERS table where the AGE is greater than 21 − SELECT * FROM CUSTOMERS WHERE AGE > 21 LIMIT 2; Output On executing the above query, the output is displayed as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 Example In the following query, we are selecting the next 3 records from the CUSTOMERS table starting from the 2nd record (off set) where the value of the AGE column is greater than 21: SELECT * FROM CUSTOMERS WHERE AGE > 21 LIMIT 1,3; Output The output for the above query is produced as given below − ID NAME AGE ADDRESS SALARY 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 LIMIT with ORDER BY clause The ORDER BY clause will sort the rows of a column in the specified order (ASC or DESC). In MySQL, we can use the LIMIT clause along with the ORDER BY clause to limit the number of rows returned in the sorted result set. Syntax

MySQL – Triggers Table of content Types of Triggers in MySQL Advantages of Triggers Disadvantages of Triggers Restrictions on Triggers ”; Previous Next Generally, a Trigger is defined as a response to an event. In MySQL, a trigger is a special stored procedure that resides in the system catalogue, that is executed automatically (without being called explicitly like regular stored procedures) whenever an event is performed. These events include statements like INSERT, UPDATE and DELETE etc. To run a MySQL trigger, the user must have admin/superuser privileges. As per the SQL standard, triggers are usually divided into two categories − Row-level Trigger: Triggers that are only executed when each row is either inserted, updated or deleted in a database table. MySQL only supports these type of triggers. Statement-level Trigger: Triggers like these are executed on the transaction level, once, no matter how many rows are modified in a table. MySQL does not support these trype of triggers. Types of Triggers in MySQL There are six types of row-level triggers in MySQL. They are: Before Insert Trigger After Insert Trigger Before Update Trigger After Update Trigger Before Delete Trigger After Delete Trigger Before Insert Trigger The Before Insert Trigger is performed before any value is inserted into the table. Whenever an INSERT statement is executed, the Before Insert trigger goes off, followed by the insertion transaction. After Insert Trigger The After Insert Trigger works opposite to the Before Insert Trigger. As implied by its name, it is performed after any value is inserted into the table. Whenever an INSERT statement is executed, the value is inserted into the table first followed by the execution of the trigger. Before Update Trigger The Before Update Trigger is performed before any value is updated or modified in the table. Whenever an UPDATE statement is executed, the Before Update trigger goes off, followed by the update transaction. After Update Trigger The After Update Trigger works opposite to the Before Update Trigger. As implied by its name, it is performed after any value is updated in the table. Whenever an UPDATE statement is executed, the value is updated in the table first followed by the execution of the trigger. Before Delete Trigger The Before Delete Trigger is performed before any value is deleted from the table. Whenever a DELETE statement is executed, the Before Delete trigger goes off, followed by the deletion transaction. After Delete Trigger The After Delete Trigger works opposite to the Before Delete Trigger. As implied by its name, it is performed after any value is deleted from the table. Whenever an DELETE statement is executed, the value is deleted from the table first followed by the execution of the trigger. Advantages of Triggers Triggers hold a lot of advantages in MySQL database. They are listed as follows − Triggers help the database to maintain the integrity of the data stored. Triggers are also a means to handle errors from the database layer itself. As triggers are invoked automatically without being called explicitly, you don”t have to wait for the scheduled events to run. Triggers can be useful to track the data changes made in the tables, by logging the events. MySQL Triggers can also prevent invalid transactions from being executed. Disadvantages of Triggers However, there are disadvantages of using triggers in a MySQL database. Some of them are listed as follows − Triggers cannot replace all validations, and only provide extended validations. For simple validations, you can use the NOT NULL, UNIQUE, CHECK and FOREIGN KEY constraints. As triggers are invisible to the client application, it is impossible to understand what goes on in the database layer. Hence, making it difficult to troubleshoot. Triggers are not beneficial for use with high-velocity data i.e. the data when a number of events per second are high. Triggers may increase the overhead of the MySQL Server. Restrictions on Triggers Following are some of the restrictions that apply to MySQL triggers − One trigger for each event − Each table can have only one trigger for each event combination, i.e. you can”t define two same triggers for the same table. RETURN statement is not permitted − As triggers don”t return any values, the RETURN statement is not permitted. Foreign key restriction − Triggers are not activated by foreign key actions. Outdated metadata − Suppose, if a trigger is loaded into cache, it is not automatically reloaded when the table metadata changes. In this case, a trigger can operate using outdated metadata. Cannot use ”CALL” statement − We cannot use the CALL statement in triggers. Cannot create a TEMPORARY table or a view − We cannot create a view for a temporary table or a view. Not activated by changes in INFORMATION_SCHEMA − Actually, triggers are not activated by changes made in INFORMATION_SCHEMA or performance_schema tables. It is because these tables are views and triggers are not permitted on views. Print Page Previous Next Advertisements ”;

MySQL – Delete Query Table of content MySQL DELETE Statement Deleting Data from a MySQL Table Delete Query in MySQL Using a Client Program ”; Previous Next MySQL DELETE Statement If we want to delete a record from any MySQL table, then we can use the SQL command DELETE FROM. This statement is a part of Data Manipulation Language in SQL as it interacts with the data in a MySQL table rather than the structure. DELETE statement can be used to delete multiple rows of a single table and records across multiple tables. However, in order to filter the records to be deleted, we can use the WHERE clause along with the DELETE statement. We can use this command at the mysql> prompt as well as in any script like PHP, Node.js, Java, and Python. Syntax Following is the basic SQL syntax of the DELETE command to delete data from a MySQL table − DELETE FROM table_name [WHERE Clause] If the WHERE clause is not specified, then all the records will be deleted from the given MySQL table. We can specify any condition using the WHERE clause. We can delete records in a single table at a time. The WHERE clause is very useful when you want to delete selected rows in a table. Deleting Data from a MySQL Table We use the MySQL DELETE query to delete records from a database table. However, we can delete single, multiple rows at once or even delete all records from a table in a single query. Let us discuss them one by one futher in this tutorial with appropriate examples. Example First of all, let us create a table named CUSTOMERS using the following query − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); The following query inserts 7 records into the above created table − INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ), (2, ”Khilan”, 25, ”Delhi”, 1500.00 ), (3, ”Kaushik”, 23, ”Kota”, 2000.00 ), (4, ”Chaitali”, 25, ”Mumbai”, 6500.00 ), (5, ”Hardik”, 27, ”Bhopal”, 8500.00 ), (6, ”Komal”, 22, ”Hyderabad”, 4500.00 ), (7, ”Muffy”, 24, ”Indore”, 10000.00 ); Execute the below SELECT statement to retrieve all the records present in the CUSTOMERS table − Select * From CUSTOMERS; Following is the CUSTOMERS table − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Now, let us delete a single record (ID = 1) from the CUSTOMERS table using the DELETE statement as follows − DELETE FROM CUSTOMERS WHERE ID = 1; Output On executing the given query, the output is displayed as follows − Query OK, 1 row affected (0.00 sec) Verification Execute the following query to verify whether the above record have been deleted or not − Select * From CUSTOMERS; As we can see in the output, the row with ID=1 has been deleted − ID NAME AGE ADDRESS SALARY 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Deleting Multiple Rows We can also delete multiple rows using the DELETE statement. For this, we just have to specify multiple conditions in the WHERE clause that are satisfied by all the records that are supposed to be deleted. Example Here, we are deleting records from previously created CUSTOMERS table whose ID is 2 and 3 − DELETE FROM CUSTOMERS WHERE ID = 2 OR ID = 3; Output The output for the program above is produced as given below − Query OK, 2 rows affected (0.01 sec) Verification Execute the following query to verify whether the above records have been deleted or not − Select * From CUSTOMERS; As we can see in the output, the row with ID values 2 and 3 are deleted − ID NAME AGE ADDRESS SALARY 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Deleting All Rows If we want to delete all records from a MySQL table, simply execute the DELETE statement without using the WHERE clause. Following is the syntax − DELETE FROM table_name; Example The following query will delete all records from the CUSTOMERS table − DELETE FROM CUSTOMERS; Output On executing the given program, the output is displayed as follows − Query

MySQL – LIKE Operator Table of content MySQL LIKE Operator Using LIKE Operator with Wildcards Using LIKE Operator with AND/OR Operators Using NOT Operator with LIKE Operator Client Program ”; Previous Next MySQL LIKE Operator The LIKE Operator in MySQL database is a logical operator that is used to retrieve the data from a table, based on a specified pattern. To filter and search for some records in a table, in a very basic way is using a WHERE clause. To elaborate, a WHERE clause with the ”equal to” sign (=) works fine whenever we want search for an exact match. But there may be a requirement where we want to filter out all the results wherever the values in a table have a particular pattern. This can be handled by using a LIKE Operator in a WHERE clause. The LIKE operator is usually used along with a pattern. However, the placement of this pattern (like at the beginning of the record, or at the ending) is decided using some characters known as wildcards. Without a wildcard character, the LIKE operator is very same as the equal to (=) sign in the WHERE clause. Syntax Following is the basic syntax of the LIKE operator in MySQL − SELECT column_name(s) FROM table_name WHERE column_name LIKE [condition]; You can specify any condition using the WHERE clause. You can use the LIKE Operator along with the WHERE clause. You can use the LIKE Operator in place of the equals to sign. When LIKE is used along with % sign then it will work like a meta character search. You can specify more than one condition using AND or OR operators. A WHERE…LIKE clause can also be used in DELETE or UPDATE SQL commands to specify a condition. Using LIKE Operator with Wildcards Wildcards are special characters used in SQL queries to match patterns in the data. Following are the four wildcards used in conjunction with the LIKE operator − S.No WildCard & Definition 1 % The percent sign represents zero, one or multiple characters. 2 _ The underscore represents a single number or character. 3 [] This matches any single character within the given range in the []. 4 [^] This matches any single character excluding the given range in the [^]. Note− In the LIKE operator, the above wildcard characters can be used individually as well as in combinations with each other. The two mainly used wildcard characters are ”%” and ”_”. Example In the following query, we are creating a table named CUSTOMERS using the CREATE statement − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); The below query inserts 7 records into the above-created table − INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ), (2, ”Khilan”, 25, ”Delhi”, 1500.00 ), (3, ”Kaushik”, 23, ”Kota”, 2000.00 ), (4, ”Chaitali”, 25, ”Mumbai”, 6500.00 ), (5, ”Hardik”, 27, ”Bhopal”, 8500.00 ), (6, ”Komal”, 22, ”Hyderabad”, 4500.00 ), (7, ”Muffy”, 24, ”Indore”, 10000.00 ); Execute the following query to fetch all the records from the CUSTOMERS table − Select * From CUSTOMERS; Following is the CUSTOMERS table − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Now, we are retrieving the name of the customers ending with “esh” using the LIKE operator with wildcards − SELECT * from CUSTOMERS WHERE NAME LIKE ”%esh”; Output The output for the above query is produced as given below − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 Using LIKE Operator with AND/OR Operators In MySQL, we can also use the LIKE operator with multiple string patterns for selecting rows by using the AND or OR operators. Syntax Following is the basic syntax of using LIKE operator with AND/OR operator − SELECT column_name(s) FROM table_name WHERE column1 LIKE pattern1 [AND|OR] column2 LIKE pattern2 [AND|OR] …; Example The following query retrieves the customers whose names start with ”M” and ”R” − SELECT * FROM CUSTOMERS WHERE Name LIKE ”M%” OR Name LIKE ”R%”; Output Following is the CUSTOMERS table − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 7 Muffy 24 Indore 10000.00 Using NOT Operator with LIKE Operator We can use the NOT operator in conjunction with LIKE operator to extract the rows which does not contain a particular string provided in the search pattern. Syntax Following is the basic syntax of NOT LIKE operator in SQL − SELECT column1, column2, … FROM table_name WHERE column1 NOT LIKE pattern; Example In the following query, we are retrieving all

MySQL – Insert Query Table of content The MySQL INSERT Statement Inserting Data from the Command Prompt Inserting Data Into a Table Using Another Table INSERT… SET Inserting Data Using a Client Program ”; Previous Next After creating a table in a MySQL database with the CREATE TABLE statement, we will only have an empty table that only has its structure defined. To populate it with data, we need to add records manually using separate queries. The MySQL INSERT Statement To insert data into a MySQL table, we would need to use the MySQL INSERT statement. We can insert data into the MySQL table by using the ”mysql>” prompt or by using any client program such as PHP, Java etc. Since the structure of a table is already defined, the MySQL INSERT statement will only accept the data which is according to the structure of the table. Data inserted into a table must have same data types, satisfy the constraints (if any), etc. If the inserted data does not satisfy these conditions, the INSERT INTO statement displays an error. Syntax Following is the syntax of the MySQL INSERT statement − INSERT INTO TABLE_NAME (column1, column2, column3,…columnN) VALUES (value1, value2, value3,…valueN); To insert string values, it is required to keep all the values into double or single quotes. For example “value”. Inserting Data from the Command Prompt To insert data from the command prompt, we will use SQL INSERT INTO statement to insert data into an MySQL table. Example First of all, let us create a table named CUSTOMERS using the following query − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now, we will insert a single record into the above created table − INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ); We can also insert multiple records simultaneously using the following query − INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, ”Khilan”, 25, ”Delhi”, 1500.00 ), (3, ”Kaushik”, 23, ”Kota”, 2000.00 ), (4, ”Chaitali”, 25, ”Mumbai”, 6500.00 ), (5, ”Hardik”, 27, ”Bhopal”, 8500.00 ); Inserting records into a database is also possible even if you do not specify the column name if the comma separated values in the query match the attributes of corresponding columns as shown below − INSERT INTO CUSTOMERS VALUES (6, ”Komal”, 22, ”Hyderabad”, 4500.00 ), (7, ”Muffy”, 24, ”Indore”, 10000.00 ); Verification We can verify whether the the data is inserted using this statement as shown below − SELECT * FROM CUSTOMERS; The CUSTOMERS table produced is as shown below − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Inserting Data Into a Table Using Another Table Sometimes, we just need to copy the data from one existing table in a database to another table in the same database. And there are various ways to do so − Using INSERT… SELECT Using INSERT… TABLE INSERT… SELECT Statement We can populate the data into a table through the select statement over another table; provided the other table has a set of fields, which are required to populate the first table. Here is the syntax − INSERT INTO table_name1 [(column1, column2, … columnN)] SELECT column1, column2, …columnN FROM table_name2 [WHERE condition]; Example In the following query, we are creating another table CUSTOMERS_Copy with the same structure as CUSTOMERS table − CREATE TABLE CUSTOMERS_Copy ( ID INT AUTO_INCREMENT, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now, let us use the INSERT…INTO statement to insert the records into the CUSTOMERS_Copy table from CUSTOMERS table. INSERT INTO CUSTOMERS_Copy SELECT * from CUSTOMERS; Output This will generate the following output − Query OK, 7 rows affected (0.01 sec) Records: 7 Duplicates: 0 Warnings: 0 Verification Execute the following query to verify whether the the records are inserted from CUSTOMERS table or not − SELECT * FROM CUSTOMERS_Copy; The CUSTOMERS_Copy table obtained is as shown below − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 INSERT…TABLE Statement On the other hand, instead of selecting specific columns, we can insert the contents of one table into another using the INSERT…TABLE statement. Following is the syntax to do so − INSERT INTO table1 TABLE table2; Example In this example, let us use the same CUSTOMERS table we have created in the previous example

MySQL – Delete Join Table of content MySQL DELETE… JOIN DELETE… JOIN with WHERE Clause Delete Join Using Client Program ”; Previous Next Simple deletion operation in MySQL can be performed on a single entity or multiple entities of a table. But what if this deletion operation is to be performed on multiple entities of multiple tables? This is where Joins come into picture. MySQL DELETE… JOIN As we have discussed in this tutorial previously, Joins are used to retrieve records from two or more tables, by combining columns of these tables based on the common fields. This merged data can be deleted with all the changes reflected in original tables. Syntax Following is the basic syntax of DELETE… JOIN statement in MySQL − DELETE table(s) FROM table1 JOIN table2 ON table1.common_field = table2.common_field; We can use any join clause (INNER JOIN, LEFT JOIN, RIGHT JOIN etc.) while performing deletion. Example In this example, we first create a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary etc. CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now insert values into this table using the INSERT statement as follows − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00), (2, ”Khilan”, 25, ”Delhi”, 1500.00), (3, ”Kaushik”, 23, ”Kota”, 2000.00), (4, ”Chaitali”, 25, ”Mumbai”, 6500.00), (5, ”Hardik”, 27, ”Bhopal”, 8500.00), (6, ”Komal”, 22, ”Hyderabad”, 4500.00), (7, ”Muffy”, 24, ”Indore”, 10000.00); The table will be created as − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Let us create another table ORDERS, containing the details of orders made and the date they are made on. CREATE TABLE ORDERS ( OID INT NOT NULL, DATE VARCHAR (20) NOT NULL, CUSTOMER_ID INT NOT NULL, AMOUNT DECIMAL (18, 2), ); Using the INSERT statement, insert values into this table as follows − INSERT INTO ORDERS VALUES (102, ”2009-10-08 00:00:00”, 3, 3000.00), (100, ”2009-10-08 00:00:00”, 3, 1500.00), (101, ”2009-11-20 00:00:00”, 2, 1560.00), (103, ”2008-05-20 00:00:00”, 4, 2060.00); The table is displayed as follows − OID DATE CUSTOMER_ID AMOUNT 102 2009-10-08 00:00:00 3 3000.00 100 2009-10-08 00:00:00 3 1500.00 101 2009-11-20 00:00:00 2 1560.00 103 2008-05-20 00:00:00 4 2060.00 The delete operation is performed by applying the DELETE… JOIN query on these tables. DELETE a FROM CUSTOMERS AS a INNER JOIN ORDERS AS b ON a.ID = b.CUSTOMER_ID; Verification To verify if the changes are reflected in the tables, we can use SELECT statement to print the tables. ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 DELETE… JOIN with WHERE Clause The ON clause in DELETE… JOIN query is used to apply constraints on the records. In addition to it, we can also use WHERE clause to make the filtration stricter. Observe the query below; here, we are trying to delete the records of customers, in the CUSTOMERS table, whose salary is lower than Rs. 2000.00. DELETE a FROM CUSTOMERS AS a INNER JOIN ORDERS AS b ON a.ID = b.CUSTOMER_ID WHERE a.SALARY < 2000.00; Verification To verify whether the changes are reflected in the original tables or not, we will use the SELECT statement. The CUSTOMERS table after deletion is as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Delete Join Using Client Program In addition to joining two or more than two tables using the MySQL query, we can also perform the Delete Join operation using a client program. Syntax PHP NodeJS Java Python To perform Delete Join through a PHP program, we need to execute the DELETE statement with JOIN clause using the mysqli function query() as follows − $sql = ”DELETE tutorials_tbl, tcount_tbl FROM tcount_tbl INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author”; $mysqli->query($sql); To perform Delete Join through a JavaScript program, we need to execute the DELETE statement with JOIN clause using the query() function of mysql2 library as follows − sql = “DELETE tutorials_tbl, tcount_tbl FROM tcount_tbl INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author”;