Found 4219 Articles for MySQLi

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)

We can use 2-digit year value either in single date expression or in both date expressions used as argument/s in MySQL DATEDIFF() function.For example, the query below is using 2-digit year value in first date expression and other is having 4-digit year value.mysql> Select DATEDIFF('18-10-22', '2017-10-22'); +-----------------------------------+ | DATEDIFF('18-10-22', '2017-10-22') | +-----------------------------------+ |                               365 | +-----------------------------------+ 1 row in set (0.00 sec)And the query below is using 2-digit year value in both date expressions.mysql> Select DATEDIFF('18-10-22', '17-10-22'); +---------------------------------+ | DATEDIFF('18-10-22', '17-10-22') | +---------------------------------+ |   ... Read More

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

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 DATEDIFF() function also works with date and time values but it ignores the time value. Hence even if we include the time value in DATEDIFF() function MySQL will return the difference, in days, between dates by ignoring the time values.mysql> Select DATEDIFF('2018-10-22 04:05:36', '2017-10-22 03:05:45'); +-------------------------------------------------------+ | DATEDIFF('2018-10-22 04:05:36', '2017-10-22 03:05:45') | +-------------------------------------------------------+ |                                                   365 | +-------------------------------------------------------+ 1 row in set (0.00 sec) mysql> Select DATEDIFF('2017-10-22 04:05:36', '2017-10-22 03:05:45'); +-------------------------------------------------------+ | DATEDIFF('2017-10-22 04:05:36', ... Read More

It is quite possible to get the sorted output on the basis of the column which is not even the part of that output or not in the result set. It can be done by selecting the required fields and writing the name of the fields on the basis of which sorting order is desired. Following is an example to demonstrate it, in which we sorted out the result set, having ‘Name’ and ‘Address’ fields, on the basis of column ‘id’.mysql> Select Name, Subject From Student ORDER BY Id; +---------+-----------+ | Name    | Subject   | +---------+-----------+ | Gaurav ... Read More

In this case, MySQL will take into consideration the first value out of two provided in the enclosed set of unit values. It will return the output along with warning after calculating the interval, based on the considered value from an enclosed set, on the unit given in INTERVAL keyword. The following example will clarify it −mysql> Select TIMESTAMP('2017-10-22 04:05:36' + INTERVAL '4 2' Hour)AS 'HOUR VALUE INCREASED BY 4'; +---------------------------+ | HOUR VALUE INCREASED BY 4 | +---------------------------+ | 2017-10-22 08:05:36       | +---------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> Show warnings; +---------+------+------------------------------------------+ | ... Read More

In this case, MySQL will take into consideration right most unit given in compound INTERVAL unit. It will return the output after calculating the interval on the basis of single value provided in the enclosed set of unit values. The following example will clarify it −mysql> Select TIMESTAMP('2017-10-22 04:05:36' + INTERVAL '2 ' year_month) AS 'Only Month Value Changed'; +--------------------------+ | Only Month Value Changed | +--------------------------+ | 2017-12-22 04:05:36      | +--------------------------+ 1 row in set (0.00 sec)The query above changes the month (right most in compound INTERVAL unit) from 10 to 12 based on the single value ... Read More

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)