Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
MySQL Articles
Page 81 of 355
What is the use of MySQL IFNULL() control flow function?
MySQL IFNULL() control flow function will return the first argument if it is not NULL otherwise it returns the second argument.SyntaxIFNULL(expression1, expression2)Here if expression1 is not NULL then IFNULL() will return expression1 otherwise expression2. It will return NULL if both of the arguments are NULL. Following example will exhibit this −mysql> Select IFNULL(NULL, 'Ram'); +--------------------+ | IFNULL(NULL, 'Ram') | +--------------------+ | Ram | +--------------------+ 1 row in set (0.00 sec) mysql> Select IFNULL('Shyam', 'Ram'); +-----------------------+ | IFNULL('Shyam', 'Ram') | +-----------------------+ | Shyam ...
Read MoreWhat is the difference between = and: = assignment operators?
Actually, they both are assignment operator and used to assign values but the significant difference between them is as follows −= operator assigns a value either as a part of the SET statement or as a part of the SET clause in an UPDATE statement, in any other case = operator is interpreted as a comparison operator. On the other hand, := operator assigns a value and it is never interpreted as a comparison operator.mysql> Update estimated_cost1 SET Tender_value = '8570.000' where id = 2; Query OK, 1 row affected (0.06 sec) Rows matched: 1 Changed: 1 Warnings: 0 ...
Read MoreWhat is the use of MySQL NULLIF() control flow function?
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)
Read MoreWhat happens when we use COMMIT in MySQL stored procedure and one of the transaction, under START transaction, fails?
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 | +----+---------+ ...
Read MoreHow can we drop a MySQL stored procedure?
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.
Read MoreHow can we alter a MySQL stored procedure?
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 ...
Read MoreHow can I use MySQL INTERVAL() function with a column of a table?
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 MoreHow can I get all the records of a table by passing its name as the parameter of MySQL stored procedure?
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 MoreHow can we perform START transactions inside MySQL stored procedure?
As we know the START transaction will start the transaction and set the auto-commit mode to off. In the following example, we have created a stored procedure with a START transaction which will insert a new record in table employee.tbl having the following data −mysql> Select * from employee.tbl; +----+---------+ | Id | Name | +----+---------+ | 1 | Mohan | | 2 | Gaurav | | 3 | Rahul | +----+---------+ 3 rows in set (0.00 sec)Examplemysql> Delimiter // mysql> Create Procedure st_transaction() -> BEGIN -> START TRANSACTION; -> INSERT INTO employee.tbl(name) values ...
Read MoreCreate a MySQL stored procedure, which takes the name of the database as its parameter, to list the tables with detailed information in a particular database.
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 accept the name of the database as its parameter and give us the list of tables with detailed information −mysql> DELIMITER// mysql> CREATE procedure tb_list(db_name varchar(40)) -> BEGIN -> SET @z := CONCAT('Select * from information_schema.tables WHERE table_schema = ', ''', db_name, ...
Read More