Category: mysql

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

MySQL – Create Index Table of content Creating Indexes on New Table Creating Indexes on Existing Table Simple and Unique Index Composite Indexes Creating an Index Using Client Program ”; Previous Next A database index improves the speed of operations in a database table. They can be created on one or more columns, providing the basis for both rapid random lookups and efficient ordering of access to records. Practically, indexes are a special type of lookup tables, that hold a pointer to each record into the actual table. We can create indexes on a MySQL table in two scenarios: while creating a new table and on an existing table. Creating Indexes on New Table If we want to define an index on a new table, we use the CREATE TABLE statement. Syntax Following is the syntax to create an index on a new table − CREATE TABLE( column1 datatype PRIMARY KEY, column2 datatype, column3 datatype, … INDEX(column_name) ); Example In this example, we are create a new table CUSTOMERS and adding an 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), INDEX(ID) ); To verify whether the index has been defined or not, we check the table definition using the following DESC statement. DESC CUSTOMERS; Output The table structure displayed will contain a MUL index on the ID column as shown − Field Type Null Key Default Extra ID int NO MUL NULL NAME varchar(20) NO NULL AGE int NO NULL ADDRESS char(25) YES NULL SALARY decimal(18, 2) YES NULL Creating Indexes on Existing Table To create an index on existing table, we use the following SQL statements − With CREATE INDEX Statement With ALTER Command CREATE INDEX Statement The basic syntax of the CREATE INDEX statement is as follows − CREATE INDEX index_name ON table_name; In the following example, let us create an index on CUSTOMERS table. We are using CREATE INDEX statement here − CREATE INDEX NAME_INDEX ON CUSTOMERS (Name); To check if the index is created on the table or not, let us display the table structure using DESC statement as shown below − DESC CUSTOMERS; Output As we can see in the table below, a composite index is created on the ”NAME” column of CUSTOMERS table. Field Type Null Key Default Extra ID int NO MUL NULL NAME varchar(20) NO MUL NULL AGE int NO NULL ADDRESS char(25) YES NULL SALARY decimal(18, 2) YES NULL ALTER… ADD Command Following is the basic syntax of ALTER statement − ALTER TABLE tbl_name ADD INDEX index_name (column_list); Let us use ALTER TABLE… ADD INDEX statement in the following example to add an index to the CUSTOMERS table − ALTER TABLE CUSTOMERS ADD INDEX AGE_INDEX (AGE); Output As we can see in the table below, another composite index is created on the ”AGE” column of CUSTOMERS table. Field Type Null Key Default Extra ID int NO MUL NULL NAME varchar(20) NO MUL NULL AGE int NO MUL NULL ADDRESS char(25) YES NULL SALARY decimal(18, 2) YES NULL Simple and Unique Index A unique index is the one which cannot be created on two rows at once. Following is the syntax to create a unique index − CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,…); Example Following example creates a unique index on the table temp − CREATE UNIQUE INDEX UNIQUE_INDEX ON CUSTOMERS (Name); Composite Indexes We can also create an index on more than one column and it is called a composite index the basic syntax to create a composite index is as follows − CREATE INDEX index_name on table_name (column1, column2); Example Following query creates a composite index on the ID and Name columns of the above created table − CREATE INDEX composite_index on CUSTOMERS (ID, Name); Creating an Index Using Client Program In addition to using SQL queries, we can also create an index on a table in a MySQL database using a client program. Syntax Following are the syntaxes to create an index in a MySQL database using various programming languages − PHP NodeJS Java Python The MySQL PHP connector mysqli provides a function named query() to execute the CREATE INDEX query in the MySQL database. $sql=” CREATE INDEX index_name ON table_name (column_name)”; $mysqli->query($sql); The MySQL NodeJS connector mysql2 provides a function named query() to execute the CREATE INDEX query in the MySQL database. sql = “CREATE INDEX index_name

MySQL – SHOW TRIGGERS Table of content Show Triggers in MySQL With FROM or IN Clause With WHERE clause Showing Trigger Using Client Program ”; Previous Next Triggers in MySQL are stored programs similar to procedures. These can be created on a table, schema, view and database that are associated with an event and whenever an event occurs the respective trigger is invoked. MySQL provides a statement to list out all the existing triggers present in a database. Knowing the trigger information can be useful while creating new triggers, so that a user wouldn”t use the same name for multiple triggers. Show Triggers in MySQL The SHOW TRIGGERS Statement is used in MySQL to display information about all the triggers defined in the current database. Syntax Following is the syntax of the MySQL SHOW TRIGGERS Statement − SHOW TRIGGERS [{FROM | IN} db_name] [LIKE ”pattern” | WHERE expr] Example In this example, we are creating a table named STUDENT using the query below − CREATE TABLE STUDENT( Name varchar(35), Age INT, Score INT ); Following query creates a trigger sample_trigger which will set the score value to 0 if you enter a negative score value into the table. DELIMITER // CREATE TRIGGER sample_trigger BEFORE INSERT ON STUDENT FOR EACH ROW BEGIN IF NEW.score < 0 THEN SET NEW.score = 0; END IF; END // DELIMITER ; Assume we have created another trigger using the AFTER clause − DELIMITER // CREATE TRIGGER testTrigger AFTER UPDATE ON Student FOR EACH ROW BEGIN INSERT INTO Student SET action = ”update”, Name = OLD.Name, Age = OLD.age, Score = OLD.score; END; END // DELIMITER ; Following query shows the existing triggers in the current database − SHOW TRIGGERS G; Output The list of triggers will be displayed as follows − *************************** 1. row *************************** Trigger: sample_trigger Event: INSERT Table: student Statement: BEGIN IF NEW.score < 0 THEN SET NEW.score = 0; END IF; END Timing: BEFORE Created: 2021-05-12 19:08:04.50 sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION Definer: root@localhost character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci *************************** 2. row *************************** Trigger: testTrigger Event: UPDATE Table: student Statement: INSERT INTO Student SET Name = OLD.Name, Age = OLD.age, Score = OLD.score Timing: AFTER Created: 2021-05-12 19:10:44.49 sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION Definer: root@localhost character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci 2 rows in set (0.00 sec) With FROM or IN Clause You can retrieve the information of triggers from a specific database using the FROM clause. Example Assume that the current database is named demo. Following query shows the triggers present in the database demo − SHOW TRIGGERS FROM demoG You can also use the IN clause instead of FROM, to get the same output. SHOW TRIGGERS IN demoG Output The existing triggers present in the demo database − *************************** 1. row *************************** Trigger: sample_trigger Event: INSERT Table: student Statement: BEGIN IF NEW.score < 0 THEN SET NEW.score = 0; END IF; END Timing: BEFORE Created: 2023-09-29 11:42:33.58 sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION Definer: root@localhost character_set_client: cp850 collation_connection: cp850_general_ci Database Collation: utf8mb4_0900_ai_ci *************************** 2. row *************************** Trigger: testTrigger Event: UPDATE Table: student Statement: BEGIN INSERT INTO Student SET action = ”update”, Name = OLD.Name, Age = OLD.age, Score = OLD.score; END Timing: AFTER Created: 2023-09-29 11:43:10.27 sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION Definer: root@localhost character_set_client: cp850 collation_connection: cp850_general_ci Database Collation: utf8mb4_0900_ai_ci 2 rows in set (0.00 sec) With WHERE Clause You can use the WHERE clause of the SHOW TRIGGERS statements to retrieve info about the triggers which match the specified condition. Example Following query retrieves the triggers in the current database whose event is update − SHOW TRIGGERS FROM demo WHERE Event = ”UPDATE” G; Output The required list of triggers is displayed as follows − *************************** 1. row *************************** Trigger: testTrigger Event: UPDATE Table: student Statement: BEGIN INSERT INTO Student SET action = ”update”, Name = OLD.Name, Age = OLD.age, Score = OLD.score; END Timing: AFTER Created: 2023-09-29 11:43:10.27 sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION Definer: root@localhost character_set_client: cp850 collation_connection: cp850_general_ci Database Collation: utf8mb4_0900_ai_ci 1 row in set (0.00 sec) Showing Trigger Using Client Program We can also Show a trigger using a client program. Syntax PHP NodeJS Java Python To show a trigger through a PHP program, we need to execute the SHOW TRIGGERS statement using the mysqli function query() as follows − $sql = “Show TRIGGER”; $mysqli->query($sql); To show a trigger through a JavaScript program, we need to execute the SHOW TRIGGERS statement using the query() function of mysql2 library as follows − sql = “Show TRIGGER”; con.query(sql); To show a trigger through a Java program, we need to execute the SHOW TRIGGERS statement using the JDBC function executeQuery() as follows − String sql = “Show TRIGGER”; statement.executeQuery(sql); To show a trigger through a python program, we need to execute the SHOW TRIGGERS statement using the execute() function of the MySQL Connector/Python as follows − Show_trigger_query = ”SHOW TRIGGER” cursorObj.execute(Show_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.”); // Create a trigger $sql = “CREATE TRIGGER testTrigger AFTER UPDATE ON Student FOR EACH ROW INSERT INTO Student SET action = ”update”, Name = OLD.Name, age = OLD.age, score = OLD.score”; if ($mysqli->query($sql)) { printf(“Trigger created successfully…!”); } else { printf(“Trigger creation failed: %s”, $mysqli->error);

MySQL – Foreign Key Table of content Creating MySQL Foreign Key Creating Foreign Key on Existing Column Dropping MySQL Foreign Key Primary Key vs Foreign Key Creating Foreign Key Using Client Program ”; Previous Next In MySQL, a Foreign Key is a column (or combination of columns) in a table whose values match the values of a Primary Key column in another table. Thus, using the Foreign key, we can link two tables together. A Foreign Key is also known as a Referencing key of a table because it can reference any field defined as unique. The table that has the primary key is known as the parent table and the key with the foreign key is known as the child table. In addition to linking to tables, the Foreign Key constraint ensures referential integrity by preventing changes to data in the primary key table from invalidating the link to data in the foreign key table. i.e, a Foreign key prevents operations, like “dropping the table”, that would eliminate the connection between two tables. Creating MySQL Foreign Key We can create a Foreign Key on a MySQL table using the CONSTRAINT… FOREIGN KEY… REFERENCES keyword in the CREATE TABLE statement. Syntax Following is the syntax to add Foreign Key constraints on a column of a table − CREATE TABLE table2( column1 datatype, column2 datatype, … CONSTRAINT constraint_name FOREIGN KEY (column2) REFERENCES table1(column1) ); Example Let us create a table named CUSTOMERS using the CREATE TABLE statement − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) UNIQUE, SALARY DECIMAL (18, 2), PRIMARY KEY(ID) ); To demonstrate the foreign key we need two tables so lets create another table as − CREATE TABLE ORDERS ( OID INT NOT NULL, DATE VARCHAR (20) NOT NULL, CUSTOMER_ID INT NOT NULL, AMOUNT DECIMAL (18, 2), CONSTRAINT fk_customers FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS(ID) ); Verification To verify if the foreign key is created, let us drop the CUSTOMERS table without dropping the ORDERS table using the following statement − DROP TABLE CUSTOMERS; An error is displayed as follows − ERROR 3730 (HY000): Cannot drop table ”customers” referenced by a foreign key constraint ”fk_customers” on table ”orders”. Creating Foreign Key on Existing Column We can also create a Foreign Key constraint on a column of an existing table using the ALTER TABLE… ADD CONSTRAINT statement. Syntax Following is the syntax to add foreign key constraint on an existing table − ALTER TABLE table_name2 ADD CONSTRAINT constraint_name FOREIGN KEY(column_name2) REFERENCES table_name1(column_name1); Example Following is the MySQL query to add a foreign key constraint FK_CUSTOMERS on an existing column of an existing table ORDERS referencing primary key of CUSTOMERS table − ALTER TABLE ORDERS ADD CONSTRAINT FK_CUSTOMERS FOREIGN KEY(CUSTOMER_ID) REFERENCES CUSTOMERS(ID); Output The table structure displayed will contain a FOREIGN KEY constraint on the CUSTOMER_ID column as shown − Field Type Null Key Default Extra OID int NO NULL DATE varchar(20) NO NULL CUSTOMER_ID int NO MUL NULL AMOUNT decimal(18,2) YES NULL Verification To verify if the foreign key we created on ORDERS is referenced to CUSTOMERS table or not, let us drop the CUSTOMERS table without dropping the ORDERS table using the following statement − DROP TABLE CUSTOMERS; An error is displayed as follows − ERROR 3730 (HY000): Cannot drop table ”customers” referenced by a foreign key constraint ”fk_customers” on table ”orders”. Dropping MySQL Foreign Key We can also drop the foreign key, created on a MySQL table, whenever it is no longer needed in that table. We can do this using the ALTER TABLE… DROP CONSTRAINT statement in MySQL. Syntax Following is the syntax to drop the foreign key from a table − ALTER TABLE table_name DROP CONSTRAINT constraint_name; Example Using the following MySQL query, we are dropping the foreign key constraint from a table − ALTER TABLE CUSTOMERS DROP CONSTRAINT fk_customers; Verification Let us verify whether the foreign key is dropped or not by dropping the CUSTOMERS table using the following query − DROP TABLE CUSTOMERS; Primary Key vs Foreign Key Even though both the primary key and foreign key refer to the same column, there are many differences to be observed in the way they work. They are listed below. Primary Key Foreign Key The Primary Key is always unique. The Foreign Key can be duplicated. The Primary Key can not be NULL. The Foreign Key can be NULL. A table can contain only one Primary Key. We can have more than one Foreign Key per table. Creating Foreign Key Using Client Program We can also apply a Foreign Key constraint on a table field using a client program. Syntax PHP NodeJS Java Python To apply foreign key on a field through a PHP program, we need to execute the FOREIGN KEY keyword in CREATE statement using the mysqli function query() as follows − $sql = ”CREATE TABLE customers(Customer_Id INT, Customer_Name VARCHAR(30), CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customer(cust_ID))”; $mysqli->query($sql); To apply foreign key on a field through a

MySQL – Regular Expressions Table of content MySQL Regular Expressions Patterns used with REGEXP Regular Expression Functions and Operators ”; Previous Next MySQL supports various types of pattern matching operations to retrieve filtered result-sets from huge database tables. In previous chapters, we have already learned about the LIKE operator for pattern matching. In this chapter, we will see another pattern matching operation based on regular expressions. MySQL Regular Expressions A regular expression is loosely 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. MySQL implements regular expression support using International Components for Unicode (ICU), which provides full Unicode support and is multi-byte safe. In MySQL, it is a powerful way to perform a complex search operations in a database to retrieve desired content. And unlike the LIKE operator, the regular expressions are not restricted on search patterns (like % and _) as they use several other meta characters to expand the flexibility and control during pattern matching. This is performed using the REGEXP operator. Syntax Following is the basic syntax of the REGEXP operator in MySQL − expression REGEXP pattern Patterns used with REGEXP Following is the table of pattern, which can be used along with the REGEXP operator. Pattern What the pattern matches ^ Beginning of string $ End of string . Any single character […] Any character listed between the square brackets [^…] Any character not listed between the square brackets p1|p2|p3 Alternation; matches any of the patterns p1, p2, or p3 * Zero or more instances of preceding element + One or more instances of preceding element {n} n instances of preceding element {m,n} m through n instances of preceding element [A-Z] Any uppercase letter [a-z] Any lowercase letter [0-9] Any digit (from 0 to 9) [[:<:]] Beginning of words [[:>:]] Ending of words [:class:] A character class, i.e. use [:alpha:] to match letters from the alphabet Examples The following example demonstrates the usage of some patterns mentioned in the table above, along with the REGEXP operator. For that, we are first creating a database table to perform the search on. Assume we are creating a table called 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) ); Now, insert some values into it using the INSERT statements given below − 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 above created table − SELECT * FROM CUSTOMERS; Following are the records present in 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 REGEXP with Patterns − Now, we are finding all the records in the CUSTOMERS table whose name starts with ”k” − SELECT * FROM CUSTOMERS WHERE NAME REGEXP ”^k”; Executing the query above will produce the following output − ID NAME AGE ADDRESS SALARY 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 6 Komal 22 Hyderabad 4500.00 The following query retrieves all records in CUSTOMERS table whose name ends with ”sh” − SELECT * FROM CUSTOMERS WHERE NAME REGEXP ”sh$”; Executing the query above will produce the following output − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 Here, we are retrieving all the records whose name contain ”sh” − SELECT * FROM CUSTOMERS WHERE NAME REGEXP ”sh”; As we can see the output, there are only two names that contain ”sh”. ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 3 Kaushik 23 Kota 2000.00 In the following query, we are finding all the names starting with a vowel and ending with ”ol” − SELECT * FROM CUSTOMERS WHERE NAME REGEXP ”^[aeiou].*ol$”; It returned an empty set because the CUSTOMERS table do not have any names who starts with vowel and ends with ”ol” Empty set (0.00 sec) The following query finds all the names in the CUSTOMERS table whose name starts with a consonant − SELECT * FROM CUSTOMERS WHERE NAME REGEXP ”^[^aeiou]”; Executing the query above will produce the following output − 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 Regular Expression Functions and Operators Following is the table of functions and operators of regular expressions. S. No Function or Operator 1 NOT REGEXP Negation of REGEXP 2 REGEXP Checks whether the string matches regular expression or not 3 REGEXP_INSTR() Returns the starting index of substring matching regular expression 4 REGEXP_LIKE() Returns whether the string matches the regular expression 5 REGEXP_REPLACE() Replaces substrings matching the regular expression 6 REGEXP_SUBSTR() Returns substrings matching the regular expression 7 RLIKE Checks whether the string matches regular expression or not Print Page Previous Next Advertisements ”;

MySQL – After Insert Trigger ”; Previous Next A Trigger is simply 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 whenever an event is performed. These events include SQL statements like INSERT, UPDATE and DELETE etc. 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. MySQL After Insert Trigger The After Insert Trigger is a row-level trigger supported by the MySQL database. As its name suggests, the After Insert Trigger is executed right after a value is 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. To elaborate, whenever an INSERT statement is executed in the database, the value is inserted into the table first followed by the trigger execution. Hence, one cannot update a newly inserted row using the AFTER INSERT trigger. Syntax Following is the syntax to create the AFTER INSERT trigger in MySQL − CREATE TRIGGER trigger_name AFTER INSERT ON table_name FOR EACH ROW BEGIN — trigger body END; Example Let us see an example demonstrating the AFTER INSERT trigger. In here, we are creating a new table named USERS, which contains the details of users of an application, using the following query − CREATE TABLE USERS( ID INT AUTO_INCREMENT, NAME VARCHAR(100) NOT NULL, AGE INT NOT NULL, BIRTH_DATE VARCHAR(100), PRIMARY KEY(ID) ); Now, let us create another table ”PUSH_NOTIFICATIONS” that is used to store messages to send as push notifications to the users on their birthdays − CREATE TABLE PUSH_NOTIFICATIONS( ID INT AUTO_INCREMENT, BIRTH_DATE VARCHAR(100), NOTIFICATIONS VARCHAR(255) NOT NULL, PRIMARY KEY(ID) ); Using the following CREATE TRIGGER statement, create a new trigger after_trigger on the USERS table to insert values into the PUSH_NOTIFICATIONS table − DELIMITER // CREATE TRIGGER after_trigger AFTER INSERT ON USERS FOR EACH ROW BEGIN IF NEW.BIRTH_DATE IS NOT NULL THEN INSERT INTO PUSH_NOTIFICATIONS VALUES (new.ID, new.BIRTH_DATE, CONCAT(”Happy Birthday, ”, NEW.NAME, ”!”)); END IF; END // DELIMITER ; Insert values into the USERS table using the regular INSERT statement as shown below − INSERT INTO USERS (NAME, AGE, BIRTH_DATE) VALUES (”Sasha”, 23, ”24/06/1999”); (”Alex”, 21, ”12/01/2001”); Verification To verify if the trigger has been executed, display the PUSH_NOTIFICATIONS table using the SELECT statement − ID BIRTH_DATE NOTIFICATIONS 1 24/06/1999 Happy Birthday, Sasha! 2 12/01/2001 Happy Birthday, Alex! Print Page Previous Next Advertisements ”;

MySQL – Distinct Clause Table of content MySQL DISTINCT clause DISTINCT Clause with COUNT() Function DISTINCT on Multiple Columns DISTINCT with NULL values Distinct Clause Using a Client Program ”; Previous Next MySQL DISTINCT clause The DISTINCT clause in MySQL is used with a SELECT statement to return the distinct values (unique values) from a single or multiple of columns in a table. It ignores all the duplicates values present in the particular column(s) and returns only the distinct values. We can use this clause in various scenarios, such as identifying unique customer names, unique customer id”s, etc. It can be combined with other clauses such as WHERE, ORDER BY, and GROUP BY to filter the data further. Syntax Following is the syntax of the DISTINCT clause in MySQL − SELECT DISTINCT column1, column2, …, columnN FROM table_name WHERE conditions // optional Where, (column1, column2,…,columnN) are the columns from which we want the distinct (unique) values. table_name is the name of the table from which we want to select data. WHERE conditions is optional. These are used to filter the data. Example Firstly, let us create a create a table named CUSTOMERS using the following INSERT 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 INSERT INTO statement adds 7 records into the above-created table − INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES (1, ”Ramesh”, 32, ”Hyderabad”, NULL), (2, ”Khilan”, 25, ”Delhi”, 1500.00), (3, ”Kaushik”, 23, ”Hyderabad”, 2000.00), (4, ”Chaital”, 25, ”Mumbai”, NULL), (5, ”Hardik”, 27, ”Vishakapatnam”, 8500.00), (6, ”Komal”, 22, ”Vishakapatnam”, 4500.00), (7, ”Muffy”, 24, ”Indore”, 10000.00); Execute the below query to display all the inserted records in the CUSTOMERS table − SELECT * FROM CUSTOMERS; Following is the CUSTOMERS table − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Hyderabad NULL 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Hyderabad 2000.00 4 Chaitali 25 Mumbai NULL 5 Hardik 27 Vishakapatnam 8500.00 6 Komal 22 Vishakapatnam 4500.00 7 Muffy 24 Indore 10000.00 Now, let us retrieve the ADDRESS column from CUSTOMERS table without using the DISTINCT clause. SELECT ADDRESS FROM CUSTOMERS; Duplicate values are not ignored in the ADDRESS column. ADDRESS Hyderabad Delhi Hyderabad Mumbai Vishakapatnam Vishakapatnam Indore Here, we are using the DISTINCT clause on the ADDRESS column − SELECT DISTINCT ADDRESS FROM CUSTOMERS; Output As we can see in the output below, duplicate values are ignored in the ADDRESS column. ADDRESS Hyderabad Delhi Mumbai Vishakapatnam Indore DISTINCT Clause with COUNT() Function The MySQL count() function allows us to count the number of distinct values present in one or more columns of a table. Let us understand with the example below Example In the following query, we are using the MySQL COUNT() function to count the DISTINCT records in ADDRESS column of CUSTOMERS table − SELECT COUNT(DISTINCT ADDRESS) FROM CUSTOMERS; Output There are 5 distinct records present in the ADDRESS column. COUNT(DISTINCT ADDRESS) 5 Example In this query, we are retrieving unique SALARY records from the CUSTOMERS table where the ADDRESS is “Hyderabad”. SELECT DISTINCT SALARY FROM CUSTOMERS WHERE ADDRESS = “HYDERABAD”; Output The output for the program above is produced as given below − SALARY NULL 2000.00 DISTINCT on Multiple Columns We can use the MySQL DISTINCT keyword on multiple columns of a table to return all the unique combinations of values across those columns, i.e. removing redundant records in a table. Example In the following query, we are retrieving the distinct combinations of ADDRESS and SALARY columns from the CUSTOMERS table and orders the result set by the ADDRESS column in ascending order. SELECT DISTINCT ADDRESS, SALARY FROM CUSTOMERS ORDER BY ADDRESS; Output As we can see in the output below, the duplicate values “Hyderabad” and “Vishakapatnam” appears twice in the result set because each combination of Hyderabad and Vishakapatnam with SALARY is unique. ADDRESS SALARY Delhi 1500.00 Hyderabad NULL Hyderabad 2000.00 Indore 10000.00 Mumbai NULL Vishakapatnam 4500.00 Vishakapatnam 8500.00 DISTINCT with NULL values If there are NULL values present in a specific column, the MySQL DISTINCT will treat them as unique values and includes them in the result set. Example Here, we are returning the distinct salary of the customers using the following query − SELECT DISTINCT SALARY FROM CUSTOMERS ORDER BY SALARY; Output On executing the given program, the output is displayed as follows − SALARY NULL 1500.00 2000.00 4500.00 8500.00

MySQL – NOT EQUAL Operator Table of content MySQL NOT EQUAL Operator NOT EQUAL with String Values NOT EQUAL with GROUP BY Clause NOT EQUAL with Multiple Conditions Negating a Condition using NOT EQUAL NOT EQUAL Operator Using a Client Program ”; Previous Next MySQL NOT EQUAL Operator The MySQL NOT EQUAL operator is used to compare two values and return true if they are not equal. It is represented by “<>” and “!=”. The difference between these two is that <> follows the ISO standard, but != doesn”t. So, it is recommended to use the <> operator. We can use this operator in WHERE clauses to filter records based on a specific condition and in GROUP BY clauses to group results. Note: The comparison is case-sensitive by default when using this operator with text values. Syntax Following is the syntax of the NOT EQUAL operator in MySQL − SELECT column1, column2, … FROM table_name WHERE column_name <> value; 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 below query uses INSERT INTO statement to add 7 records into 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 NOT EQUAL with String Values In MySQL, we can also use the NOT EQUAL to compare two string values. It returns true if both values are not equal. We can use “<>” or “!=” in the WHERE clause of a SQL statement and exclude rows that match a specific value. Example In the following query, we are selecting all the records from the CUSTOMERS table whose NAME is not “Khilan”. SELECT * FROM CUSTOMERS WHERE NAME <> “Khilan”; Output The output of the above code is as shown below − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 3 Kaushik 23 Kota 2000.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 NOT EQUAL with GROUP BY Clause MySQL”s NOT EQUAL operator can be used along with the GROUP BY clause. It will group the results by the values that are not equal to the specified text value. The aggregate functions such as COUNT(), MAX(), MIN(), SUM(), and AVG() are frequently used with the GROUP BY statement. Example In this query, we are counting the number of records with distinct ”ID” values for each ”AGE” in the ”CUSTOMERS” table. We are excluding records where ”AGE” is equal to ”22”, and grouping the results based on the ”AGE” column. SELECT COUNT(ID), AGE FROM CUSTOMERS WHERE AGE ”22” GROUP BY AGE; Output COUNT(ID) AGE 1 32 2 25 1 23 1 27 1 24 NOT EQUAL with Multiple Conditions Depending on the situation, the NOT EQUAL operator can be used with multiple conditions in a WHERE clause to filter out rows that match specific criteria. Example Here, we are going to select all the customers whose salary is either “>2000” or “=2000”. At the same time, the customer must not be from “Bhopal”. SELECT * FROM CUSTOMERS WHERE ADDRESS ”Bhopal” AND (SALARY>”2000” OR SALARY=”2000”); Output When we execute the query above, the output is obtained 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 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Negating a Condition Using NOT EQUAL The MySQL NOT EQUAL operator can be combined with the NOT operator to negate a condition and filter out rows that meet a specific condition. Example The following query retrieves all rows from the “CUSTOMERS” table where the “SALARY” is equal to ”2000” − SELECT * FROM CUSTOMERS WHERE NOT SALARY != ”2000”; Output When the query gets executed it will