Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
MySQL NULLIF() control flow function will return NULL if both the arguments are the same, otherwise, it returns the first argument.SyntaxNULLIF(expression1, expression2)Here if expression1 = expression2, NULL will be returned by NULLIF() otherwise expression1 will be returned. Following example will exhibit this −mysql> Select NULLIF('Ram','Ram'); +---------------------+ | NULLIF('Ram','Ram') | +---------------------+ | NULL | +---------------------+ 1 row in set (0.00 sec) mysql> Select NULLIF('Ram','Shyam'); +-----------------------+ | NULLIF('Ram','Shyam') | +-----------------------+ | Ram | +-----------------------+ 1 row in set (0.00 sec)
Suppose one of the queries fails or generates errors and another query (s) properly executed the MySQL still commit the changes of the properly executed query(s). It can be understood from the following example in which we are using the table ‘employee.tbl’ having the following data −Examplemysql> Select * from employee.tbl; +----+---------+ | Id | Name | +----+---------+ | 1 | Mohan | | 2 | Gaurav | | 3 | Sohan | | 4 | Saurabh | | 5 | Yash | +----+---------+ 5 rows in set (0.00 sec) mysql> Delimiter // mysql> ... Read More
As we know that MySQL NULLIF() control flow function will return NULL if both the arguments are the same, otherwise it returns the first argument. Hence it is similar to the following CASE statement −CASE WHEN expression1=expression2 THEN NULL ELSE Expression2 END.
If we have ALTER ROUTINE privileges for the procedure then with the help of DROP PROCEDURE statement we can drop a MySQL stored procedure. To demonstrate it, we are dropping a stored procedure named ‘coursedetails’ as follows −mysql> DROP PROCEDURE coursedetails; Query OK, 0 rows affected (0.68 sec)The above query will drop the procedure named ‘coursedetails’. It can be confirmed by running the statement SHOW CREATE PROCEDURE which will return an error because the procedure does not exist.
If we have ALTER ROUTINE privileges for the procedure then with the help of ALTER PROCEDURE statement we can alter a MySQL stored procedure. To demonstrate it we are taking an example of a stored procedure named ‘delete_studentinfo’ which have the following create a statement −mysql> SHOW CREATE PROCEDURE Delete_studentinfo\G *************************** 1. row *************************** Procedure: Delete_studentinfo sql_mode: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `Delete_studentinfo`( IN p_id INT) BEGIN DELETE FROM student_info WHERE ID=p_id; END character_set_client: cp850 collation_connection: cp850_general_ci Database Collation: ... Read More
We can use INTERVAL() function with a column of a table by providing the first argument as the name of the column. In this case, al the values in that column would be compared with the values given as the other arguments of INTERVAL() function and on that basis of comparison, the result set is provided. To understand it, the data from the employee table is used which is as follows −mysql> Select* from employee568; +----+--------+--------+ | ID | Name | Salary | +----+--------+--------+ | 1 | Gaurav | 50000 | | 2 | Rahul | 20000 | | ... Read More
MySQL REVERSE() function can be used to reverse a string. Following example will demonstrate it −mysql> Select REVERSE('Tutorialspoint'); +---------------------------+ | REVERSE('Tutorialspoint') | +---------------------------+ | tniopslairotuT | +---------------------------+ 1 row in set (0.00 sec) mysql> Select Reverse('10-11-12'); +---------------------+ | Reverse('10-11-12') | +---------------------+ | 21-11-01 | +---------------------+ 1 row in set (0.00 sec)
Suppose if we want to see all the records of a table by passing its name as the parameter of a stored procedure then following example will create a procedure named ‘details’ which accepts the name of the table as its parameter −mysql> DELIMITER // mysql> Create procedure details(tab_name Varchar(40)) -> BEGIN -> SET @t:= CONCAT('Select * from', ' ', tab_name); -> Prepare stmt FROM @t; -> EXECUTE stmt; -> END // Query OK, 0 rows affected (0.00 sec)Now invoke this procedure by giving the name of the table as its parameter and it will ... Read More
Following example will create a procedure named ‘tabledetails’ which gives all the details of a particular table stored in database.Examplemysql> DELIMITER // mysql> Create Procedure tabledetails() -> BEGIN -> DESCRIBE Student_detail; -> END // Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ; mysql> CALL tabledetails; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | Studentid | int(11) | NO | PRI | NULL | | | StudentName | varchar(20) | YES | | NULL | | | address | varchar(20) | YES | | NULL | | +-------------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) Query OK, 0 rows affected (0.04 sec)
Suppose currently we are using a database named ‘query’ and it is having the following tables in it −mysql> Show tables in query; +-----------------+ | Tables_in_query | +-----------------+ | student_detail | | student_info | +-----------------+ 2 rows in set (0.00 sec)Now, following is a stored procedure, which will give us the list of tables with detailed information −mysql> DELIMITER// mysql> CREATE procedure tablelist() -> BEGIN -> Select * from Information_schema.tables WHERE table_schema = 'query'; -> END // Query OK, 0 rows affected (0.06 sec) mysql> DELIMITER; mysql> CALL tablelist()\G *************************** 1. row *************************** ... Read More