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
Database Articles
Page 161 of 546
How can we find the most recent and most oldest date from a table with the help of MySQL MAX() and MIN() functions?
For getting the most recent date from a table, we need to provide the name of the column, having a date as value, as the argument of MAX() function. Similarly, forgetting the oldest date from a table, we need to provide the name of a column, having a date as value, as the argument of MIN() function. To understand it, consider the following example of table ‘Collegedetail’, having the following details −mysql> Select * from collegedetail; +------+---------+------------+ | ID | Country | estb | +------+---------+------------+ | 111 | INDIA | 2010-05-01 | | 130 | ...
Read MoreWhat is the method with the help of which the hexadecimal value can be treated as a number?
For treating the hexadecimal value as a number, we can use the CAST(… AS UNSIGNED) function. Following example will demonstrate it −mysql> Select 0x54, CAST(0x54 AS UNSIGNED); +------+------------------------+ | 0x54 | CAST(0x54 AS UNSIGNED) | +------+------------------------+ | T | 84 | +------+------------------------+ 1 row in set (0.01 sec)
Read MoreHow can I create MySQL stored procedure with IN parameter?
To make it understand we are using the table named ‘student_info’ which have the following values −mysql> Select * from student_info; +-----+---------+------------+------------+ | id | Name | Address | Subject | +-----+---------+------------+------------+ | 101 | YashPal | Amritsar | History | | 105 | Gaurav | Jaipur | Literature | | 110 | Rahul | Chandigarh | History | | 125 | Raman | Shimla | Computers | +------+--------+------------+------------+ 4 rows in set (0.00 sec)Now, with the help of the following query, we will create a stored procedure ...
Read MoreHow can I create MySQL stored procedure with INOUT parameter?
Following example will demonstrate MySQL stored procedure with INOUT parameter −mysql> DELIMITER // ; mysql> Create PROCEDURE counter(INOUT count INT, IN increment INT) -> BEGIN -> SET count = count + increment; -> END // Query OK, 0 rows affected (0.03 sec)Here, ‘count’ is the INOUT parameter, which can store and return values and ‘increment’ is the IN parameter, which accepts the values from user.mysql> DELIMITER ; mysql> SET @counter = 0; Query OK, 0 rows affected (0.00 sec) mysql> CALL counter(@Counter, 1); Query OK, 0 rows affected (0.00 sec) mysql> Select @Counter; ...
Read MoreHow can we see the list of stored procedures and stored functions in a particular MySQL database?
We can see the list of the stored procedure and stored functions in a particular database by using the following query on INFORMATION_SCHEMA.ROUTINES as follows −mysql> SELECT ROUTINE_TYPE, ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'query'; +--------------+--------------+ | ROUTINE_TYPE | ROUTINE_NAME | +--------------+--------------+ | PROCEDURE | allrecords | | FUNCTION | Hello | +--------------+--------------+ 2 rows in set (0.04 sec)The above query returns the procedure named ‘allrecords’, and function named ‘Hello’ which are stored in the database named ‘query’.
Read MoreHow can we get some starting number of characters from the data stored in a MySQL table’s column?
To get some starting number of characters from the data stored in the MySQL table’s column, we can use MySQL LEFT() function. It will return the number of characters specified as its argument. We need to provide the name of the column, having the particular record from which we want to get starting characters, as its first argument. To demonstrate it we are taking the example of a table named ‘examination_btech’ having the following examination details of students −mysql> Select * from examination_btech; +-----------+----------+--------+ | RollNo | Name | Course | +-----------+----------+--------+ | 201712001 | Rahul ...
Read MoreHow can I customize the output of MySQL SUM() function to 0 instead of NULL when there are no matching rows?
As we know that the SUM() function returns NULL if there is no matching row but sometimes we want it to return zero instead of NULL. For this purpose, we can use the MySQL COALESCE() function which accepts two arguments and returns the second argument if the first argument is NULL, otherwise, it returns the first argument. To understand the above concept, consider an ‘employee_tbl’ table, which is having the following records −mysql> SELECT * FROM employee_tbl; +------+------+------------+--------------------+ | id | name | work_date | daily_typing_pages | +------+------+------------+--------------------+ | 1 | John | 2007-01-24 | ...
Read MoreWhat are the advantages and disadvantages of using MySQL stored procedures?
There are numerous advantages and disadvantages of using MySQL stored procedures which are as follows −MySQL Stored Procedure AdvantagesFollowings are the advantages of using MySQL Stored Procedures −Increasing the performance of applications − As we know that after creating the stored procedure it is compiled and stored in the database. But MySQL implements stored procedures slightly different which helps in increasing the performance of the applications. MySQL stored procedures are compiled on demand. After compiling a stored procedure, MySQL puts it into a cache. And MySQL maintains its own stored procedure cache for every single connection. If an application uses ...
Read MoreHow can we see only name and types of the stored routines in a particular MySQL database?
We can write the following query to see only the name and types of procedures in a particular MySQL database. To make it understand we are using the database named ‘query’ −mysql> Select Name, Type from mysql.proc where db = 'query'; +------------+-----------+ | Name | Type | +------------+-----------+ | allrecords | PROCEDURE | | Hello | FUNCTION | +------------+-----------+ 2 rows in set (0.18 sec)
Read MoreHow can we get some last number of characters from the data stored in a MySQL table’s column?
To get some last number of characters from the data stored in MySQL table’s column, we can use MySQL RIGHT() function. It will return the number of characters specified as it argument. We need to provide the name of the column, having the particular record from which we want to get last characters, as its first argument. To demonstrate it we are taking the example of a table named ‘examination_btech’ having the following examination details of students −mysql> Select * from examination_btech; +-----------+----------+--------+ | RollNo | Name | Course | +-----------+----------+--------+ | 201712001 | Rahul | ...
Read More