Category: mysql

MySQL – SQL Injection Table of content How SQL Injection Works Preventing SQL Injection ”; Previous Next The SQL Injection in MySQL is a harmful approach where an attacker inserts or “injects” harmful SQL code into a database query. This can be done through user inputs such as forms, URL parameters, or cookies. The attacker takes advantage of weaknesses in the software to steal information from the database. How SQL Injection Works Imagine you have a web application with a login page. When a user enters their username and password, the application checks these credentials against a MySQL database. The SQL query might look like as given below − SELECT * FROM users WHERE username = ”user” AND password = ”password”; In a secure application, the ”user” and ”password” would be the actual values entered by the user. However, in an SQL Injection attack, an attacker can manipulate the input fields to inject malicious SQL code. For example, they might enter the following as the username − ” OR ”1” = ”1 Now, the SQL query becomes − SELECT * FROM users WHERE username = ”” OR ”1” = ”1” AND password = ”password”; Because ”1” always equals ”1”, this condition is always true, and the attacker gains unauthorized access to the application. In this way, they trick the application into granting access without a valid password. Preventing SQL Injection To prevent SQL injection, it is important to handle escape characters properly when using scripting languages like PERL and PHP. When working with PHP and MySQL, you can use the mysql_real_escape_string() function to escape input characters that have special meaning in MySQL. Following is an example of how to do this − if (get_magic_quotes_gpc()) { $name = stripslashes($name); } // escape input characters $name = mysql_real_escape_string($name); // Perform the MySQL query with the escaped ”name” mysqli_query(“SELECT * FROM CUSTOMERS WHERE name=”{$name}””); The LIKE Quandary Now, let us address the issue with the LIKE clause. When dealing with user-provided data that may include ”%” and ”_” characters, it is important to create a custom escaping mechanism to treat them as literals. You can achieve this by combining “mysql_real_escape_string()” function with “addcslashes()” function, which allows you to specify a character range to escape. Following is an example of how you can do it − // Escape and convert ”%” and ”_” in the user-provided string $sub = addcslashes(mysql_real_escape_string(“%str”), “%_”); // $sub will be equal to %str_ // Use the escaped string in the LIKE query mysqli_query(“SELECT * FROM messages WHERE subject LIKE ”{$sub}%””); In this way, you ensure that the ”%” and ”_” characters in the user input are treated as literal characters in the SQL query, preventing SQL injection and maintaining the integrity of your database operations. Print Page Previous Next Advertisements ”;

MySQL – AND Operator Table of content MySQL AND Operator AND Operator with WHERE Multiple AND Operators AND with UPDATE statement AND with DELETE Statement AND Operator Using a Client Program ”; Previous Next MySQL AND Operator In MySQL, there isn”t a built-in Boolean type. Rather, the Boolean values are represented using numeric data types, where zero is considered false and any non-zero value is considered true. The MySQL AND operator is a logical operator that combines two or more Boolean expressions and returns 1, 0, or NULL: A AND B Here, A and B are operands. The AND operator will return true (1) only if both A and B are non-zero and not Null. If either A or B is false, the AND operator will return false (0). If either A or B is NULL, the AND operator will return NULL. The following table below demonstrates the possible outcomes of using the AND operator to combine true, false, and null values: 1 0 NULL 1 1 0 NULL 0 0 0 0 NULL NULL 0 NULL Example The logical AND operator returns 1 if both A and B are non-zero and NOT NULL − SELECT 1 AND 1; Output The output for the program above is produced as given below − 1 AND 1 1 Example The logical AND operator returns 0 if either A or B is zero, or if both A and B are zero. SELECT 1 AND 0, 0 AND 1, 0 AND 0, 0 AND NULL; Output When we execute the above query, the output is obtained as follows − 1 AND 0 0 AND 1 0 AND 0 0 AND NULL 0 0 0 0 Example The logical AND operator returns NULL if at least one operand is non-zero or both operands are NULL − SELECT 1 AND NULL, NULL AND NULL; Output On executing the given query, the output is displayed as follows − 1 AND NULL NULL AND NULL NULL NULL AND Operator with WHERE The MySQL AND operator can be used with the WHERE clause to retrieve only the rows that meet all the specified conditions. When the AND operator is used, both conditions must be true for a row to be included in the result set. Else, it returns an empty set. Syntax Following is the syntax of the AND operator with WHERE clause in MySQL − SELECT column1, column2, …, columnN FROM table_name [WHERE condition1 AND condition2 AND condition3 …;] Example Firstly, let us create a MySQL table named CUSTOMERS using the below 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 rows 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, let us select all the columns from the CUSTOMERS table where the ADDRESS is ”Hyderabad” and AGE is 22. SELECT * FROM CUSTOMERS WHERE ADDRESS = “Hyderabad” AND AGE = 22; Output On executing the given query, the output is displayed as follows − ID NAME AGE ADDRESS SALARY 6 Komal 22 Hyderabad 4500.00 Example The logical AND operator returns the records only if all the conditions separated by AND are true. In the following query, we are providing a false value to one of the AND operands. SELECT * FROM CUSTOMERS WHERE ADDRESS = “Kerala” AND AGE = 27; Output As the ADDRESS column in the CUSTOMERS table doesn”t contain the value ”Kerala”, it returns an empty set as an output. Empty set (0.00 sec) Multiple AND Operators In MySQL, we can use multiple AND operators in a query to combine multiple conditions or expressions together. Conditions combined with these multiple ”AND” operators are evaluated from left to right. If any of the conditions evaluate to false, the entire condition will be false and the record will not be included in the result set. Example In the following query, we are selecting all records from the CUSTOMERS table where the NAME starts

MySQL – NOT REGEXP Operator Table of content MySQL NOT REGEXP Operator NOT REGEXP Operator Using a Client Program ”; Previous Next MySQL NOT REGEXP Operator Technically, a regular expression is defined as a sequence of characters that represent a pattern in an input text. It is used to locate or replace text strings using some patterns; this pattern can either be a single character, multiple characters or words, etc. In MySQL, the REGEXP operator is a powerful tool to perform complex search operations in a database to retrieve required data using regular expressions. And unlike the LIKE operator, the REGEXP operator is not restricted on search patterns (like % and _), as it uses several other meta characters to expand the flexibility and control during pattern matching. NOT REGEXP Operator is a negation to this REGEXP operator. It is used to retrieve all the records present in a database that do not satisfy the specified pattern. Let us learn about this operator in detail further in this chapter. Syntax Following is the basic syntax of the MySQL NOT REGEXP operator − expression NOT REGEXP pattern Note − Since this is just a negation, the functionality of a NOT REGEXP operator will be the same as a REGEXP operator. Examples Let us start by creating 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 below INSERT statement 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 display all the records present in 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 show the usage of NOT REGEXPM operator using several queries on this table. Query to find all the names not starting with ”ra” − SELECT * FROM CUSTOMERS WHERE NAME NOT REGEXP ”^ra”; Following is the output − 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 Query to retrieve all the records whose names not ending with ”ik” − SELECT * FROM CUSTOMERS WHERE NAME NOT REGEXP ”ik$”; Following is the output − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 4 Chaitali 25 Mumbai 6500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Following is the query to find all the names that do not contain ”an” − SELECT * FROM CUSTOMERS WHERE NAME NOT REGEXP ”an”; Following is the output − 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 Example The NOT RLIKE is alternative syntax to the NOT REGEXP in MySQL. Both the operators have same result. If either of the first two operands is NULL, this operator returns NULL. In the below query, the string value is NULL. Thus it gives output as NULL. SELECT NULL NOT RLIKE ”value”; Following is the output − NULL NOT RLIKE ”value” NULL Here, the specified pattern is NULL. So, the output will be retrieved as NULL. SELECT ”Tutorialspoint” NOT REGEXP NULL; Following is the output − NULL NOT RLIKE ”value” NULL NOT REGEXP Operator Using a Client Program Besides using MySQL queries to perform the NOT REGEXP operator, we can also use client programs such as PHP, Node.js, Java, and Python to achieve the same result. Syntax Following are the syntaxes of this operation in various programming languages − PHP

MySQL – Export Table into CSV File Table of content Export MySQL Table into CSV File Storage Location of Exported .csv File Exporting MySQL Data in CSV Format Exporting Table Data Along with Column Headings Exporting Table Data Without Specifying Column Names Client Program ”; Previous Next MySQL is an open-source relational database management system that allows us to store and manage large volume of data. One of its key feature is to export data from a table into various formats and CSV is one of it. CSV stands for “Comma Separated Values” file. This allows users to extract data from a table in a structured format that can be easily manipulated and analysed using other tools such as Microsoft Excel, Google documents, open office etc. Export MySQL Table into CSV File To export the MySQL table data into a CSV file, we can use the MySQL “SELECT INTO … OUTFILE” statement. Before exporting any table data into CSV files in the database server, we must ensure the following things − The MySQL server”s process must have the read/write privileges to the specified target folder, where CSV file will be created. The specified CSV file should be already present in the system (No duplicate file). The exported CSV file can contain data from one or more tables, and it can be modified to include only particular columns or rows. Syntax Following is the syntax of SELECT INTO … OUTFILE statement − SELECT column_name1, column_name2,… INTO OUTFILE ”C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/file_name.csv” FIELDS TERMINATED BY ”,” OPTIONALLY ENCLOSED BY ””” LINES TERMINATED BY ”rn”; Where, INTO OUTFILE is the path and name of the CSV file that we want to export the table data to. FIELDS TERMINATED BY is the delimiter that separates the fields in the exported CSV file. LINES TERMINATED is the line terminator character for exported CSV file. Storage Location of Exported .csv File In MySQL, when you export a file, such as a .csv file, the default storage location for the exported file is determined by the “secure_file_priv” variable. To find out the default path for exported files, you can use the following SQL query − SHOW VARIABLES LIKE “secure_file_priv”; We get the following output − Variable_name Value secure_file_priv C:ProgramDataMySQLMySQL Server 8.0Uploads Before exporting data to a .csv file, you will need to have at least one table in your MySQL database. Let us create a table named “CUSTOMERS” using the following SQL query − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now, we are inserting data into the above created table as shown below − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ), (2, ”Khilan”, 25, ”Delhi”, 1500.00 ), (3, ”Kaushik”, 23, NULL, 2000.00 ), (4, ”Chaitali”, NULL, ”Mumbai”, 6500.00 ), (5, ”Hardik”, 27, ”Bhopal”, 8500.00 ), (6, ”Komal”, 22, NULL, 4500.00 ), (7, ”Muffy”, 24, ”Indore”, 10000.00 ); The CUSTOMERS 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 NULL 2000.00 4 Chaitali NULL Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 NULL 4500.00 7 Muffy 24 Indore 10000.00 Exporting MySQL Data in CSV Format You can export MySQL data in CSV file using the SELECT INTO … OUTFILE statement. Here, we are exporting the data of CUSTOMERS table into a CSV file named “CUSTOMERS_BACKUP” using the following query − SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS INTO OUTFILE ”C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv” FIELDS ENCLOSED BY ””” TERMINATED BY ”;” ESCAPED BY ””” LINES TERMINATED BY ”rn”; After executing the above query, the CSV format file will be created at the specified path. Following is the output obtained after executing the above query − Query OK, 7 rows affected (0.01 sec) Following is the image of “CUSTOMERS_BACKUP.csv” file when we opened it − Handling File Already Exists Error − If you attempt to export data into a file that already exists, MySQL will generate an error − SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS INTO OUTFILE ”C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv” FIELDS ENCLOSED BY ””” TERMINATED BY ”;” ESCAPED BY ””” LINES TERMINATED BY ”rn”; Following is the error obtained − ERROR 1086 (HY000): File ”C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv” already exists To avoid this error, you can either choose a different filename or delete the existing file before executing the export query. Removing Enclosing Quotes for Numeric Records − By default, all records in the CSV file will be enclosed in double quotes, including numeric values. If you want to remove the quotes for numeric records, you can use the OPTIONALLY clause before the ENCLOSED BY clause, as shown below − SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS INTO OUTFILE ”C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv” FIELDS TERMINATED BY ”,” OPTIONALLY ENCLOSED BY ””” LINES TERMINATED BY ”rn”; Following is the output obtained after executing the above query − Query OK, 7 rows affected (0.00 sec) As we can see the image of CSV file below, the double quotes (“”) are removed

MySQL – Date and Time Functions ”; Previous Next In MySQL, we have a set of functions using which we can manipulate the date and time values. Following are the MySQL date time functions − Sr.No. Name & Description 1 ADDDATE() This function adds two given dates 2 ADDTIME() This function adds given time values 3 CONVERT_TZ() This function converts from one timezone to another 4 CURDATE() This function returns the current date 5 CURRENT_DATE(), CURRENT_DATE Synonyms for CURDATE() 6 CURRENT_TIME(), CURRENT_TIME Synonyms for CURTIME() 7 CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP Synonyms for NOW() 8 CURTIME() This function returns the current time 9 DATE_ADD() Adds two dates 10 DATE_FORMAT() This function formats the given date as specified 11 DATE_SUB() This function subtracts two dates 12 DATE() This function extracts the date part of a date or datetime expression 13 DATEDIFF() This function subtracts two dates 14 DAY() This function retrieves the day of the month from the given date 15 DAYNAME() This function returns the name of the weekday 16 DAYOFMONTH() This function returns the day of the month (1-31) 17 DAYOFWEEK() This function returns the weekday index of the argument 18 DAYOFYEAR() This function returns the day of the year (1-366) 19 EXTRACT This function extracts part of a date 20 FROM_DAYS() This function converts a day number to a date 21 FROM_UNIXTIME() This function formats date as a UNIX timestamp 22 HOUR() This function Extracts the hour 23 LAST_DAY This function returns the last day of the month for the argument 24 LOCALTIME(), LOCALTIME Synonym for NOW() 25 LOCALTIMESTAMP, LOCALTIMESTAMP() Synonym for NOW() 26 MAKEDATE() This function creates a date from the year and day of year 27 MAKETIME() This function creates a time value from the given hours, minutes, and seconds. 28 MICROSECOND() This function returns the microseconds from argument 29 MINUTE() This function returns the minute from the argument 30 MONTH() This function returns the month from the date passed 31 MONTHNAME() This function returns the name of the month 32 NOW() This function returns the current date and time 33 PERIOD_ADD() This function adds a period to a year-month 34 PERIOD_DIFF() This function returns the number of months between periods 35 QUARTER() This function returns the quarter from a date argument 36 SEC_TO_TIME() This function converts seconds to ”HH:MM:SS” format 37 SECOND() This function returns the second (0-59) 38 STR_TO_DATE() This function converts a string to a date 39 SUBDATE() This function subtracts the specified interval to a date value 40 SUBTIME() This function subtracts the specified time interval to a date time or, time value 41 SYSDATE() This function returns the time at which the function executes 42 TIME_FORMAT() This function formats the given date in the specified format 43 TIME_TO_SEC() This function returns the argument converted to seconds 44 TIME() This function extracts the time portion of the expression passed 45 TIMEDIFF() This function subtracts two time values 46 TIMESTAMP() With a single argument, this function returns the date or datetime expression. With two arguments, the sum of the arguments 47 TIMESTAMPADD() This function adds an interval to a datetime expression 48 TIMESTAMPDIFF() This function subtracts an interval from a datetime expression 49 TO_DAYS() This function returns the date argument converted to days 50 UNIX_TIMESTAMP() This function returns a UNIX timestamp 51 UTC_DATE() This function returns the current UTC date 52 UTC_TIME() This function returns the current UTC time 53 UTC_TIMESTAMP() This function returns the current UTC date and time 54 WEEK() This function returns the week number 55 WEEKDAY() This function returns the weekday index 56 WEEKOFYEAR() This function returns the calendar week of the date (1-53) 57 YEAR() This function returns the year 58 YEARWEEK() This function returns the year and week 59 TO_SECONDS() This function converts the date or date-time values into seconds and returns the result. Print Page Previous Next Advertisements ”;

MySQL – After Delete Trigger Table of content MySQL After Delete Trigger After Delete Trigger Using a Client Program ”; Previous Next In general, a Trigger is defined as a response to an event. 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. Triggers are categorized into two types: Before Triggers and 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 After Delete Trigger The After Delete Trigger is a row-level trigger supported by the MySQL database. This trigger is executed right after a value is deleted from a row of a database table. A row-level trigger is a type of trigger that is executed every time a row is modified. For every single transaction made in a table (like insertion, deletion, update operation), one trigger acts automatically. When a DELETE statement is executed in the database, the trigger is performed first and then the said value is deleted from the table. Syntax Following is the syntax to create the AFTER DELETE trigger in MySQL − CREATE TRIGGER trigger_name AFTER 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 AFTER 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 ”after_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 after_delete_trigger AFTER 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 CUSTOMERS table and added onto the OLD_CUSTOMERS table, let us try to retrieve both of their result-sets using the 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. However, the only difference that is not visible on the application level is that the trigger is performed after the deletion is done, in contrast to the BEFORE DELETE trigger. After Delete Trigger Using a Client Program We can also execute the After Delete trigger statement using a client program, instead of SQL queries. Syntax PHP NodeJS Java Python To execute the After Delete Trigger through a PHP program, we need to query the CREATE TRIGGER statement using the mysqli function query() as follows − $sql = “CREATE TRIGGER after_delete_trigger AFTER 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 After 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 after_delete_trigger AFTER 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 After 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 after_delete_trigger AFTER 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 After 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 − afterDelete_trigger_query = ”CREATE TRIGGER {trigger_name} AFTER 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(afterDelete_trigger_query)

MySQL – WHERE Clause Table of content MySQL WHERE Clause Fetching Data Using Where Clause WHERE Clause Using a Client Program ”; Previous Next MySQL WHERE Clause We know that the SQL SELECT command is used to fetch records from a MySQL table. In addition to that, we can also use a conditional clause called the WHERE Clause in conjunction with the SELECT statement to filter out the results. Using this WHERE clause, we can specify a selection criteria to select the required records from a table. The WHERE clause works like an if condition in any programming language. This clause is used to compare the given value with the field value available in a MySQL table. If the given value from outside is equal to the available field value in the MySQL table, then it returns that row. Operators Used in WHERE Clause Here is the list of comparison operators, which can be used with the WHERE clause. =: Checks if the values of the two operands are equal or not, if yes, then the condition becomes true. !=: Checks if the values of the two operands are equal or not, if the values are not equal then the condition becomes true. >: Checks if the value of the left operand is greater than the value of the right operand, if yes, then the condition becomes true. <: Checks if the value of the left operand is less than the value of the right operand, if yes then the condition becomes true. >=: Checks if the value of the left operand is greater than or equal to the value of the right operand, if yes, then the condition becomes true. <=: Checks if the value of the left operand is less than or equal to the value of the right operand, if yes, then the condition becomes true. Along with these, the WHERE clause can also contain logical operators, like AND, OR and NOT. AND: If an AND operator is used in WHERE Clause with two conditions, the query will return true only if both the conditions are satisfied. OR: If an OR operator is used in WHERE Clause with two conditions, the query will return true only if either of the conditions are satisfied. NOT: If a NOT operator is used in WHERE Clause with a condition, the query will return true only if the table records does not satisfy the condition. Fetching Data Using Where Clause The WHERE clause is very useful when you want to fetch the selected rows from a table, especially when you use the MySQL Join. Joins are discussed in another chapter. If the given condition does not match any record in the table, then the query would not return any row. Syntax Following is the generic SQL syntax of the SELECT command with the WHERE clause to fetch data from the MySQL table − SELECT field1, field2,…fieldN table_name1, table_name2… [WHERE condition1 [AND [OR]] condition2….. You can use one or more tables separated by a comma to include various conditions using a WHERE clause, but the WHERE clause is an optional part of the SELECT command. You can specify any condition using the WHERE clause. You can specify more than one condition using the AND or the OR operators. A WHERE clause can be used along with DELETE or UPDATE SQL command also to specify a condition. Example Firstly, 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 query to fetch all the records of 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 fetch the CUSTOMERS whose AGE is greater than 23 using the MySQL WHERE clause in conjunction with SELECT statement − Select * From CUSTOMERS Where AGE > 23; Output Following are the records − 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 7 Muffy 24 Indore 10000.00 WHERE Clause Using a Client Program Besides using MySQL Where clause to fetch the selected rows from a table, we can also use client programs like PHP, Node.js, Java, and Python to achieve

MySQL – IS NULL Operator Table of content MySQL IS NULL Operator IS NULL with SELECT statement IS NULL with COUNT() function IS NULL with UPDATE statement IS NULL with DELETE statement IS NULL Operator Using Client Program ”; Previous Next NULL values in a MySQL table fields indicate that no (or unknown) values are present in them. These values are different from zeroes or invalid values. In MySQL, it is not possible to check NULL values with comparison operators such as =, <, or <>. Instead, we use the IS NULL and IS NOT NULL (filtering non-null values) operators. MySQL IS NULL Operator The IS NULL operator in MySQL is used to check whether a value in a column is NULL. Using the IS NULL operator with a conditional clause allows us to filter records that contain NULL values in a particular column. We can also use this operator with SELECT, UPDATE, and DELETE SQL statements. Syntax Following is the syntax of IS NULL in MySQL − SELECT column_name1, column_name2, … FROM table_name WHERE column_name IS NULL; Example Firstly, 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, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); In the following query, we are using the INSERT statement to insert values to the table − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, NULL), (2, ”Khilan”, 25, ”Delhi”, 1500.00), (3, ”Kaushik”, NULL, ”Kota”, 2000.00), (4, ”Chaitali”, 25, ”Mumbai”, NULL), (5, ”Hardik”, 27, ”Bhopal”, 8500.00), (6, ”Komal”, NULL, ”Hyderabad”, 4500.00), (7, ”Muffy”, 24, ”Indore”, 10000.00); The table is created as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad NULL 2 Khilan 25 Delhi 1500.00 3 Kaushik NULL Kota 2000.00 4 Chaitali 25 Mumbai NULL 5 Hardik 27 Bhopal 8500.00 6 Komal NULL Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 IS NULL with SELECT statement The MySQL IS NULL operator can be used with the SELECT statement to filter the records with NULL values. Example In the following query, we are going to return all the records from the CUSTOMERS table where the AGE is null. SELECT * FROM CUSTOMERS WHERE AGE IS NULL; Output On executing the above query, it will generate an output as shown below − ID NAME AGE ADDRESS SALARY 3 Kaushik NULL Kota 20000.00 6 Komal NULL Hyderabad 20000.00 IS NULL with COUNT() function We can use the MySQL IS NULL operator with the COUNT() function to count the number of records with NULL values in a particular column. Syntax Following is the syntax of the IS NULL with COUNT() function in MySQL − SELECT COUNT(column_name1, column_name2, …) FROM table_name WHERE condition IS NULL; Example The following query returns the count of records have a blank field (NULL) in ADDRESS column of the CUSTOMERS table. SELECT COUNT(*) FROM CUSTOMERS WHERE ADDRESS IS NULL; Output On executing the above query, it will generate an output as shown below − COUNT(*) 2 IS NULL with UPDATE statement In MySQL, we can use the IS NULL operator with the UPDATE statement to update records with NULL values in a particular column. Syntax Following is the syntax of the IS NULL operator with the UPDATE statement in MySQL – UPDATE table_name SET column1 = value1, column2 = value2, … WHERE columnname1, columnname2, … IS NULL; Example In the following query, we are updating the blank (NULL) records of the SALARY column to a value of 9000. UPDATE CUSTOMERS SET SALARY = 9000 WHERE SALARY IS NULL; Verification To check whether the table has been updated or not, execute the SELECT query to display the table. ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 9000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik NULL Kota 2000.00 4 Chaitali 25 Mumbai 9000.00 5 Hardik 27 Bhopal 8500.00 6 Komal NULL Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 IS NULL with DELETE statement In MySQL, we can use the IS NULL operator with the DELETE statement to delete records with NULL values in a particular column. Syntax Following is the syntax of the IS NULL operator with the DELETE statement in MySQL – DELETE FROM table_name WHERE column_name(s) IS NULL; Example In the following query, we are trying to delete the blank (NULL) records present in the ADDRESS column of CUSTOMERS table. DELETE FROM CUSTOMERS WHERE AGE IS NULL; Verification To check whether the table has been changed or not, execute the SELECT query to display the table. ID NAME AGE ADDRESS

MySQL – OR Operator Table of content MySQL OR Operator OR operator with WHERE Multiple OR Operators OR with UPDATE statement OR with DELETE Statement OR Operator Using a Client Program ”; Previous Next MySQL OR Operator MySQL does not have a built-in Boolean data type. Instead, Boolean values are represented using numeric data types, where zero is used as false and any non-zero value is used as true. The MySQL OR operator is a logical operator that combines two or more Boolean expressions and returns 1, 0, or NULL: A AND B Here, A and B are operands. The OR operator will return true (1) only if either A or B, or both, is non-zero and not Null. If both A and B are false, the OR operator will return false (0). If either A or B is NULL, the OR operator will return NULL. The following table below demonstrates the possible outcomes of using the OR operator to combine true (1), false (0), and null values: 1 0 NULL 1 1 1 1 0 1 0 NULL NULL 1 NULL NULL Example The logical OR operator will return true (1) if both A and B are not NULL, and if either A or B is non-zero. SELECT 1 OR 1, 1 OR 0, 0 OR 1; Output The output for the program above is produced as given below − 1 OR 1 1 OR 0 0 OR 1 1 1 1 Example The OR operator returns false (0) if both A and B are false (0). SELECT 0 OR 0; Output When we execute the above query, the output is obtained as follows − 0 OR 0 0 Example If A is true (1) and B is NULL, the OR operator will return 1. If A is false (0) and B is NULL, the OR operator will return NULL. If both A and B are NULL, the OR operator will return NULL. SELECT 1 OR NULL, 0 OR NULL, NULL or NULL; Output On executing the given program, the output is displayed as follows − 1 OR NULL 0 OR NULL NULL OR NULL 1 NULL NULL OR operator with WHERE MySQL”s logical OR operator can be used along with the WHERE clause to return the rows that meet any of the specified conditions. When the OR operator is used, at least one of the conditions must be true for a row to be included in the result set. If none of the conditions are true, an empty set is returned. Syntax Following is the syntax of the OR operator with WHERE clause in MySQL − SELECT column1, column2, …, columnN FROM table_name [WHERE condition1 OR condition2 OR condition3 …;] 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 INSERT INTO statement adds 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 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, we are selecting all the columns from the CUSTOMERS table where SALARY is greater than 5000 or ADDRESS = “Hyderabad”. SELECT * FROM CUSTOMERS WHERE SALARY > 5000 OR ADDRESS = “Hyderabad”; Output The output for the program above is produced as given below − 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 Example The logical OR operator returns the records only if either of the conditions separated by OR is true. In the following query, we are providing false values to both operands of the OR operator. SELECT * FROM CUSTOMERS WHERE NAME = “Mahesh” OR AGE = 42; Output As there are no records present in the CUSTOMERS table with NAME “Mahesh” or AGE is 42, it returns an empty set as an output.

MySQL – Inner Join Table of content MySQL Inner Join Joining Multiple Tables Using Inner Join Inner Join with WHERE Clause Inner Join Using a Client Program ”; Previous Next MySQL Inner Join MySQL Inner Join is a type of join that is used to combine records from two related tables, based on common columns from both the tables. These tables are joined together on a specific condition. If the records in both tables satisfy the condition specified, they are combined. This is a default join; that is, even if the JOIN keyword is used instead of INNER JOIN, tables are joined using matching records of common columns. They are also referred to as an Equijoin. Syntax Following is the basic syntax of MySQL Inner Join − SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name = table_name2.column_name Example Creating 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 ORDERS Table − 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 Inner Join Query − Let us now combine these two tables using the Inner Join query as shown below − SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS INNER JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID; Output The table is displayed as follows − ID NAME DATE AMOUNT 3 Kaushik 2009-10-08 00:00:00 3000.00 3 Kaushik 2009-10-08 00:00:00 1500.00 2 Khilan 2009-11-20 00:00:00 1560.00 4 Chaitali 2008-05-20 00:00:00 2060.00 Joining Multiple Tables Using Inner Join Using the Inner Join query, we can also join as many tables as possible. However, only two tables can be joined together on a single condition. This process is done sequentially until all the tables are combined. Syntax Following is the syntax to join more than two tables using Inner Join − SELECT column_name1, column_name2… FROM table_name1 INNER JOIN table_name2 ON condition_1 INNER JOIN table_name3 ON condition_2 . . . Example In this example, let us join three tables including CUSTOMERS and ORDERS along with a new table EMPLOYEE. We will first create the EMPLOYEE table using the query below − CREATE TABLE EMPLOYEE ( EID INT NOT NULL, EMPLOYEE_NAME VARCHAR (30) NOT NULL, SALES_MADE DECIMAL (20) ); Now, we can insert values into this empty tables using the INSERT statement as follows − INSERT INTO EMPLOYEE VALUES (102, ”SARIKA”, 4500), (100, ”ALEKHYA”, 3623), (101, ”REVATHI”, 1291), (103, ”VIVEK”, 3426); The details of EMPLOYEE table are seen below. EID EMPLOYEE_NAME SALES_MADE 102 SARIKA 4500 100 ALEKHYA 3623 101 REVATHI 1291 103 VIVEK 3426 Using the following query, we are combining three tables CUSTOMERS, ORDERS and EMPLOYEE. SELECT OID, DATE, AMOUNT, EMPLOYEE_NAME FROM CUSTOMERS INNER JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID INNER JOIN EMPLOYEE ON ORDERS.OID = EMPLOYEE.EID; Output The output is obtained as follows − OID DATE AMOUNT EMPLOYEE_NAME 102 2009-10-08 00:00:00 3000.00 SARIKA 100 2009-10-08 00:00:00 1500.00 ALEKHYA 101 2009-11-20 00:00:00 1560.00 REVATHI 103 2008-05-20 00:00:00 2060.00 VIVEK Inner Join with WHERE Clause Inner Join uses WHERE clause to apply constraints on the records to be retrieved from a table. Syntax The syntax of Inner Join when used with WHERE