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 78 of 355
How can I convert the epoch stored in MySQL table into readable dates?
To illustrate it we are using the following example of a table named ‘vistors’ which have the epoch as follows −mysql> Create table visitors(userid int not null, name varchar(20), epoch int NOT NULL); Query OK, 0 rows affected (0.42 sec) mysql> Insert into visitors Values(1, 'Gaurav', 1358658942); Query OK, 1 row affected (0.04 sec) mysql> Insert into visitors Values(2, 'Raman', 1358659585); Query OK, 1 row affected (0.04 sec) mysql> Select userid, name, FROM_UNIXTIME(epoch) from visitors; +--------+--------+----------------------+ | userid | name | FROM_UNIXTIME(epoch) | +--------+--------+----------------------+ | 1 | Gaurav | 2013-07-24 16:05:42 | | 2 | Raman | 2013-07-24 16:16:25 | +--------+--------+----------------------+ 2 rows in set (0.00 sec)
Read MoreWhat are MySQL group functions?
Group functions are the function which applied to a group of rows or in other words group functions operate on sets of values. The following table gives the Description of MySQL group functions −Sr.No.Name & Description1AVG()It returns the average value of the argument.2BIT_AND()It returns bitwise AND.3BIT_ORIt returns bitwise OR.4BIT_XOR()It returns bitwise XOR.5COUNT()It returns a count of the number of rows returned.6COUNT(DISTINCT)It returns the count of a number of different values.7GROUP_CONCAT()It returns a concatenated string.8MAX()It returns the maximum value.9MIN()It returns the minimum value.10ST()It returns the population standard deviation.11STDDEV()It returns the population standard deviation.12STDDEV_POP()It returns the population standard deviation.13STDDEV_SAMP()It returns the sample ...
Read MoreHow can I write a MySQL stored function that calculates the factorial of a given number?
Following is the example of a stored function that can calculate the factorial of a given number −CREATE FUNCTION factorial (n DECIMAL(3,0)) RETURNS DECIMAL(20,0) DETERMINISTIC BEGIN DECLARE factorial DECIMAL(20,0) DEFAULT 1; DECLARE counter DECIMAL(3,0); SET counter = n; factorial_loop: REPEAT SET factorial = factorial * counter; SET counter = counter - 1; UNTIL counter = 1 END REPEAT; RETURN factorial; END // mysql> Select Factorial(5)// +--------------+ | Factorial(5) | +--------------+ | 120 | +--------------+ 1 row in set (0.27 sec) mysql> Select Factorial(6)// +--------------+ | Factorial(6) | +--------------+ | 720 | +--------------+ 1 row in set (0.00 sec)
Read MoreWhat are MySQL subqueries and its general categories?
A subquery is best defined as a query within a query. Subqueries enable you to write queries that select data rows for criteria that are actually developed while the query is executing at runtime. More formally, it is the use of a SELECT statement inside one of the clauses of another SELECT statement. In fact, a subquery can be contained inside another subquery, which is inside another subquery, and so forth. A subquery can also be nested inside INSERT, UPDATE, and DELETE statements. Subqueries must be enclosed within parentheses.A subquery can be used any place where an expression is allowed ...
Read MoreHow can group functions be used in ORDER BY clause?
We can sort the result set groups by using group functions in the ORDER BY clause. By default, the sort order is ascending but we can reverse it by using DESC keyword.Examplemysql> Select designation, YEAR(Doj), count(*) from employees GROUP BY designation, YEAR(DoJ) ORDER BY Count(*) DESC; +-------------+-----------+----------+ | designation | YEAR(Doj) | count(*) | +-------------+-----------+----------+ | Prof | 2009 | 2 | | Asst.Prof | 2015 | ...
Read MoreWhat are recursive stored procedures and why MySQL limits the recursion?
A stored procedure is called recursive if it calls itself. Basically, this concept is called recursion. MySQL limits the recursion so the errors will be less rigorous. We can check this limit with the help of the following query −mysql> Show variables LIKE '%recur%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | max_sp_recursion_depth | 0 | +------------------------+-------+ 1 row in set (0.01 sec)We can change this value up to 255 with the help of the following query −mysql> SET @@GLOBAL.max_sp_recursion_depth = 255// Query OK, 0 rows affected (0.00 sec) mysql> Show variables LIKE ...
Read MoreCreate a MySQL stored procedure which fetches the rows from a table by using a cursor?
Following is a stored procedure which fetches the records from name column of table ‘student_info’ having the following data −mysql> Select * from Student_info; +-----+---------+------------+------------+ | id | Name | Address | Subject | +-----+---------+------------+------------+ | 101 | YashPal | Amritsar | History | | 105 | Gaurav | Chandigarh | Literature | | 125 | Raman | Shimla | Computers | | 127 | Ram | Jhansi | ...
Read MoreWhat MySQL MAKE_SET() function returns if there are all NULL at the place of strings?
MySQL MAKE_SET() function will return nothing if there are all NULL at the place of strings. Following example will demonstrate it −Examplemysql> Select MAKE_SET(2, NULL,NULL,NULL); +-----------------------------+ | MAKE_SET(2, NULL,NULL,NULL) | +-----------------------------+ | | +-----------------------------+ 1 row in set (0.00 sec)
Read MoreWhat are the most significant differences between MySQL functions and procedures?
The most significant difference between procedures and functions is that they are invoked differently and for different purposes. Other than that following are the differences between procedure and functions −A procedure does not return a value. Instead, it is invoked with a CALL statement to perform an operation such as modifying a table or processing retrieved records.On the other hand, a function is invoked within an expression and returns a single value directly to the caller to be used in the expression. That is, a function is used in expressions the same way as a constant, a built-in function, or ...
Read MoreHow can I return the values of columns from MySQL table as a set of values?
With the help of MySQL MAKE_SET() function, we can return the values of columns from MySQL table as a set of values. To understand it, we are taking the example of Student_Name table which has the following data −mysql> Select * from Student_Name; +---------+-------+---------+ | FName | Mname | Lname | +---------+-------+---------+ | Rahul | NULL | Singh | | Gaurav | Kumar | NULL | | Harshit | NULL | Khurana | | Yash | Pal | Sharma | ...
Read More