Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
It can be possible by using the DISTINCT keyword in SELECT clause. The DISTINCT applies to the combination of all data fields specified in SELECT clause.ExampleWe have the table ‘Student’ on which we have applied DISTINCT keyword as follows −mysql> Select * from student; +------+---------+---------+-----------+ | Id | Name | Address | Subject | +------+---------+---------+-----------+ | 1 | Gaurav | Delhi | Computers | | 2 | Aarav | Mumbai | History | | 15 | Harshit | Delhi | Commerce | | 17 | Raman | ... Read More
In MySQL, UNIX TIMESTAMPS are stored as 32-bit integers. On the other hand MySQL TIMESTAMPS are also stored in similar manner but represented in readable YYYY-MM-DD HH:MM:SS format.Examplemysql> Select UNIX_TIMESTAMP('2017-09-25 02:05:45') AS 'UNIXTIMESTAMP VALUE'; +---------------------+ | UNIXTIMESTAMP VALUE | +---------------------+ | 1506285345 | +---------------------+ 1 row in set (0.00 sec)The query above shows that UNIX TIMESTAMPS values are stored as 32 bit integers whose range is same as MySQL INTEGER data type range.mysql> Select FROM_UNIXTIME(1506283345) AS 'MySQLTIMESTAMP VALUE'; +----------------------+ | MySQLTIMESTAMP VALUE | +----------------------+ | 2017-09-25 01:32:25 | +----------------------+ 1 row in set (0.00 sec)The query ... Read More
As we know that group function operates on the sets of value that is why if group functions will be used in SELECT clause then they will be used on the rows that meet the query selection criteria and the output of group functions will be returned as the output of the query.ExampleIn the example below, we have used some group functions in a SELECT statement on the fields of ‘Student’ table and the output of the statement is the output of those group functions −mysql> Select COUNT(Name), MIN(Id), AVG(Id), MAX(Id), COUNT(*) from Student; +-------------+---------+---------+---------+----------+ | COUNT(Name) | MIN(Id) | ... Read More
We need to use COUNT(*) function with SELECT clause to count the total number of rows in a table.Examplemysql> Select COUNT(*) from Student; +----------+ | COUNT(*) | +----------+ | 4 | +----------+ 1 row in set (0.06 sec)The query above counts the total number of rows of ‘Student’ table.We can also use WHERE clause with COUNT(*) function as follows:mysql> Select COUNT(*) from Student where Address = 'Delhi'; +----------+ | COUNT(*) | +----------+ | 2 | +----------+ 1 row in set (0.00 sec)
MySQL would return the output in time value after converting the difference between date-and-time values being provided to TIMEDIFF() function as arguments.Example mysql> Select TIMEDIFF('2017-10-22 04:05:45', '2017-10-21 03:04:44')AS 'Difference in Time'; +--------------------+ | Difference in Time | +--------------------+ | 25:01:01 | +--------------------+ 1 row in set (0.00 sec)Here in this example, we can see that MySQL converts the difference in date-and-time values into time values and return the output in time value as well.
With the help of TIMEDIFF() MySQL function the difference between two-time values can be calculated.Examplemysql> Select TIMEDIFF('04:05:45','03:05:45') AS ‘Difference in Time’; +---------------------------------+ | Difference in Time | +---------------------------------+ | 01:00:00 | +---------------------------------+ 1 row in set (0.00 sec)
Compound INTERVAL unit keywords are made up of two keywords and separated by an underscore (_). For using them in MySQL the unit values must be enclosed in single quotes and separated by space.Example − Following query will add 2 years and 2 months in the date value.mysql> Select timestamp('2017-10-22 04:05:36' + INTERVAL '2 2' year_month) AS'Date After 2 Years and 2 Months'; +---------------------------------+ | Date After 2 Years and 2 Months | +---------------------------------+ | 2019-12-22 04:05:36 | +---------------------------------+ 1 row in set (0.00 sec)
We need to specify ASC (short form for ASCENDING) keyword in ORDER BY clause if we want to sort out the result set in ascending order.SyntaxSelect column1, column2,…,columN From table_name ORDER BY column1[column2,…] ASC;ExampleIn the following example, we have sorted the result set by column ‘Name’ in the ascending order.mysql> Select * from Student ORDER BY Name ASC; +------+---------+---------+-----------+ | Id | Name | Address | Subject | +------+---------+---------+-----------+ | 2 | Aarav | Mumbai | History | | 1 | Gaurav | Delhi | Computers | | 15 | Harshit | Delhi | Commerce | | 17 | Raman | Shimla | Computers | +------+---------+---------+-----------+ 4 rows in set (0.00 sec)
We need to specify DESC (short form for DESCENDING) keyword in ORDER BY clause if we want to sort out the result set in descending order.SyntaxSelect column1, column2,…,columN From table_name ORDER BY column1[column2,…] DESC;ExampleIn the following example, we have sorted the result set by column ‘Id’ in the descending order.mysql> Select * from Student ORDER BY Id DESC; +------+---------+---------+-----------+ | Id | Name | Address | Subject | +------+---------+---------+-----------+ | 17 | Raman | Shimla | Computers | | 15 | Harshit | Delhi | Commerce | | 2 | Aarav | Mumbai | History | | 1 | Gaurav | Delhi | Computers | +------+---------+---------+-----------+ 4 rows in set (0.00 sec)
We can specify multiple columns in ORDER BY clause to get the sorted output based on those multiple columns. Following as an example to make this concept clearer −mysql> Select * from Student ORDER BY Name, Address; +------+---------+---------+-----------+ | Id | Name | Address | Subject | +------+---------+---------+-----------+ | 2 | Aarav | Mumbai | History | | 1 | Gaurav | Delhi | Computers | | 15 | Harshit | Delhi | Commerce | | 17 | Raman | Shimla | Computers | +------+---------+---------+-----------+ 4 rows in set (0.12 sec)