As we know a one-time event means the events that will be executed only once on a particular schedule. To illustrate the creation of such kind of events we are using the following example in which we are creating an event which will execute at the current time −Examplemysql> Create table event_message(ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, MESSAGE VARCHAR(255) NOT NULL, Generated_at DATETIMENOT NULL); Query OK, 0 rows affected (0.61 sec) mysql> CREATE EVENT testing_event ON SCHEDULE AT CURRENT_TIMESTAMP DO INSERT INTO event_message(message, generated_at) Values('Hello', NOW()); Query OK, 0 rows affected (0.00 sec) mysql> Select * from ... Read More

Sometimes we need to identify the unmatched data from two tables, especially in the case when data is migrated. It can be done by comparing the tables. Consider the example below in which we have two tables named ‘students’ and ‘student1’.mysql> Select * from students; +--------+--------+----------+ | RollNo | Name   | Subject  | +--------+--------+----------+ |    100 | Gaurav | Computer | |    101 | Raman  | History  | |    102 | Somil  | Computer | +--------+--------+----------+ 3 rows in set (0.00 sec) mysql> select * from student1; +--------+--------+----------+ | RollNo | Name | Subject | ... Read More

When we use GROUP BY clause in the SELECT statement without using aggregate functions then it would behave like DISTINCT clause. For example, we have the following table −mysql> Select * from Student_info; +------+---------+------------+------------+ | id   | Name    | Address    | Subject    | +------+---------+------------+------------+ | 101  | YashPal | Amritsar   | History    | | 105  | Gaurav  | Chandigarh | Literature | | 125  | Raman   | Shimla     | Computers  | | 130  | Ram     | Jhansi     | Computers  | | 132  | Shyam   | Chandigarh ... Read More

As we know that a NULL is not a value and it is also not the same as zero. MySQL arithmetic expression returns NULL if we will use NULL in it. It can be understood with the help of the following example −Examplemysql> Select 100*NULL; +----------+ | 100*NULL | +----------+ |     NULL | +----------+ 1 row in set (0.00 sec) mysql> Select 100+NULL; +----------+ | 100+NULL | +----------+ |     NULL | +----------+ 1 row in set (0.00 sec)From the above example, it can be observed that if we will use NULL in an arithmetic expression then the result would be NULL itself.

As we know that there is no BOOLEAN data type in MySQL hence by using TRUE or true, FALSE or false we can enter Boolean values in MySQL statement.Examplemysql> Select TRUE,FALSE; +------+-------+ | TRUE | FALSE | +------+-------+ | 1    | 0     | +------+-------+ 1 row in set (0.00 sec) mysql> Select true,false; +------+-------+ | TRUE | FALSE | +------+-------+ | 1    | 0     | +------+-------+ 1 row in set (0.00 sec)

Following are the two approaches with the help of which we can enter numeric values as a BINARY number −By prefix ‘B’In this approach we need to quote binary numbers within single quotes with a prefix of B. Then BINARY number string will be automatically converted into a numerical value based on the expression context.Examplemysql> Select B'1110'+0; +-----------+ | B'1110'+0 | +-----------+ |        14 | +-----------+ 1 row in set (0.00 sec)By prefix 0bIn this approach, we need to write BINARY numbers without any quotes with a prefix of 0b. Then BINARY number string will be automatically ... Read More

Following are the two approaches with the help of which we can enter characters as a BINARY number −By prefix ‘B’In this approach we need to quote binary numbers within single quotes with a prefix of B. Then BINARY number string will be automatically converted into a character string.Examplemysql> Select B'01000001'; +-------------+ | B'01000001' | +-------------+ | A           | +-------------+ 1 row in set (0.00 sec)By prefix 0bIn this approach, we need to write BINARY numbers without any quotes with a prefix of 0b. Then BINARY number string will be automatically converted into a character ... Read More

It can be done with the help of the following query −mysql> SELECT trigger_name,action_order FROM INFORMATION_SCHEMA.triggers WHERE TRIGGER_SCHEMA = 'query' ORDER BY event_object_table,action_timing,event_manipulation; +------------------------------+--------------+ | trigger_name                 | action_order | +------------------------------+--------------+ | studentdetail_before_update  |            1 | | studentdetail_before_update2 |            2 | +------------------------------+--------------+ 2 rows in set (0.10 sec)The above result set shows the order of multiple triggers created on the same event and action time in the database ‘query’.

In MySQL, triggers are a mechanism that allow for specific actions to be executed automatically when specific events like Insert, Update, or Delete occur in a table. They are useful as they automate processes and maintain data integrity. Using BEGIN...END in trigger In a trigger, if we want to perform more than one action we write it within the BEGIN…END block. This block simplifies the way we can manage a larger number of actions for a more complicated task. Let us see how the BEGIN...END construct allows multiple statements to be executed within a trigger. We can group ... Read More

Following are the two approaches with the help of which we can enter numeric values as a Hexadecimal number −By prefix ‘X’In this approach we need to quote hexadecimal numbers within single quotes with a prefix of X. Then HEX number string will be automatically converted into a number based on the expression context.Examplemysql> Select X'5152545678'+ 10; +-------------------+ | X'5152545678'+ 10 | +-------------------+ | 349273609858      | +-------------------+ 1 row in set (0.00 sec)By prefix 0xIn this approach, we need to write hexadecimal numbers without any quotes with a prefix of 0x. Then HEX number string will be automatically ... Read More