- Trending Categories
- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Found 6702 Articles for Database
320 Views
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
67 Views
In this case, MySQL INTERVAL() function returns the index number of the last number in argument list plus 1. In other words, the last index number in the list plus 1 would be returned by this function. Following example will demonstrate it −mysql> Select INTERVAL(50,20,32,38,40); +--------------------------+ | INTERVAL(50,20,32,38,40) | +--------------------------+ | 4 | +--------------------------+ 1 row in set (0.00 sec)
65 Views
Actually, INTERVAL() function uses the binary search for searching the bigger number than the number at first argument. So, that is why if we want INTERVAL() function to work efficiently the list of numbers would be in ascending order. Following is a good way to use INTERVAL() function −mysql> Select INTERVAL(50,20,32,38,40,50,55);
85 Views
MySQL returns -1 as output if the first argument of INTERVAL() function is NULL. Following example will demonstrate it −mysql> Select INTERVAL(NULL, 20, 32, 38, 40, 50, 55); +--------------------------------------+ | INTERVAL(NULL, 20, 32, 38, 40, 50, 55) | +--------------------------------------+ | -1 | +--------------------------------------+ 1 row in set (0.00 sec)It will return -1 even if any of the other arguments is NULL along with the first argument.mysql> Select INTERVAL(NULL, 20, 32, NULL, 40, 50, NULL); +--------------------------------------+ | INTERVAL(NULL, 20, 32, NULL, 40, 50, NULL) ... Read More
418 Views
MySQL INTERVAL() function returns the index value of the argument which is greater than the first argument. Syntax INTERVAL(N,N1,N2,N3,…) Here, this function will compare 1st argument i.e. N with the other arguments i.e. N1, N2, N3 and so on. All the arguments are treated as integers. It returns the output as follows − If N
263 Views
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
256 Views
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)
259 Views
Following are the two cases when MySQL IN() function returns NULL as result −Case-1 − When expression on left side is NULL IN() function will return NULL if the expression on the left side is NULL. Following example will demonstrate it −mysql> Select NULL IN (1, 2, 3, 4, 10); +----------------------+ | NULL IN (1, 2, 3, 4, 10) | +----------------------+ | NULL | +----------------------+ 1 row in set (0.00 sec)Case-2 − When one of expression in the list is NULL and no match is foundIN() function will return NULL if it does ... Read More
195 Views
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.
638 Views
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