Found 4219 Articles for MySQLi

Column values on multiple rows can be updated in a single UPDATE statement if the condition specified in WHERE clause matches multiple rows. In this case, the SET clause will be applied to all the matched rows.ExampleSuppose we have a table ‘tender’ as follows −mysql> Select * from tender; +-----------+---------+------+ | tender_id | company | rate | +-----------+---------+------+ | 200       | ABC     | 1000 | | 300       | ABD     | 5000 | | 301       | ABE     | 6000 | | 302       ... Read More

By specifying multiple columns in GROUP BY clause we can split the result set into smaller groups. The more columns specified in GROUP BY clause, the smaller the groups will be.Examplemysql> Select designation, YEAR(Doj), count(*) from employees GROUP BY designation, YEAR(DoJ); +-------------+-----------+----------+ | designation | YEAR(Doj) | count(*) | +-------------+-----------+----------+ | Asso.Prof   | 2013      | 1        | | Asst.Prof   | 2015      | 1        | | Asst.Prof   | 2016      | 1        | | Prof        | 2009      | 2        | | Prof        | 2010      | 1        | +-------------+-----------+----------+ 5 rows in set (0.00 sec)

Suppose if we want the output of FROM_UNIXIME() function in a particular format then we can use date format string or time format string or both in it. Following is the example of using the format string in FROM_UNIXTIME() function −mysql> Select FROM_UNIXTIME(1555033470 '%Y %M %D')AS 'Formatted Output'; +------------------+ | Formatted Output | +------------------+ | 2019 April 12th  | +------------------+ 1 row in set (0.00 sec)In the query above, it is using only date format string.mysql> Select FROM_UNIXTIME(1555033470 '%h:%i:%s')AS 'Formatted Output'; +------------------+ | Formatted Output | +------------------+ | 07:14:30         | +------------------+ 1 row in set (0.00 ... Read More

In that case, MySQL returns the Unix timestamp of the current date and time. Hence we can say that using no argument is same as using NOW() as an argument to UNIX_TIMESTAMP() function.For example, if we run the query for UNIX_TIMESTAMP() with no value and with NOW() as an argument that MySQL returns the same result.mysql> Select UNIX_TIMESTAMP(); +------------------+ | UNIX_TIMESTAMP() | +------------------+ |       1509405559 | +------------------+ 1 row in set (0.00 sec) mysql> Select UNIX_TIMESTAMP(NOW()); +-----------------------+ | UNIX_TIMESTAMP(NOW()) | +-----------------------+ |            1509405559 | +-----------------------+ 1 row in set (0.00 sec)The ... Read More

It can be done inflowing two ways −(A) With the help of EXRACT() function - EXTRACT() function can fetch any part from MySQL TIMESTAMP value. Following is the example of fetching month and day from a given date.mysql> Select EXTRACT(Month from '2017-10-22') AS 'MONTH'; +-------+ | MONTH | +-------+ |    10 | +-------+ 1 row in set (0.00 sec) mysql> Select EXTRACT(day from '2017-10-22')AS 'DAY'; +------+ | DAY  | +------+ |   22 | +------+ 1 row in set (0.00 sec)(B) With the help of MONTH() or DAY() function -  Rather than passing month and day as one of ... Read More

TIME_FORMAT() function can be used in a similar fashion as DATE_FORMAT() function but it can only be used for offloading time values. MySQL returns a NULL value if TIME_FORMAT() function is used for offloading date values.For example, when we pass the time format units as arguments to MySQL TIME_FORMAT() function then MySQL offloaded only the time as follows −mysql> Select TIME_FORMAT("2017-10-22 13:03:45", "%h %i %s %p")AS 'OFFLOADED TIME'; +----------------+ | OFFLOADED TIME | +----------------+ | 01 03 45 PM    | +----------------+ 1 row in set (0.00 sec)Whereas, when we pass the date format units as arguments to MySQL TIME_FORMAT() ... Read More

We can offload the time/date handling to MySQL with the help of DATE_FORMAT() function. The date and time would be offloaded on the basis of format units passed as arguments to the function.For example, when we pass the date format units as arguments to MySQL DATE_FORMAT() function then MySQL offloaded only the date as follows −mysql> Select DATE_FORMAT("2017-10-22 13:03:45", "%Y %M %D")AS 'OFFLOADED DATE'; +-------------------+ | OFFLOADED DATE    | +-------------------+ | 2017 October 22nd | +-------------------+ 1 row in set (0.00 sec)Whereas, when we pass the time format units as arguments to MySQL DATE_FORMAT() function then MySQL offloaded only ... Read More

MySQL converts Unix timestamp to timestamp data type value with the help of  FROM_UNIXTIME() function.Examplemysql> Select FROM_UNIXTIME(1508622563); +-----------------------------+ | FROM_UNIXTIME(1508622563)   | +-----------------------------+ | 2017-10-22 03:19:23         | +-----------------------------+ 1 row in set (0.00 sec)

It can be done by using GROUP BY clause in the SELECT statement. We can specify a column as grouping criteria with the help of GROUP BY clause. Due to the specified grouping criteria, rows with the same value in a particular column are considered as a single group. In this way, the result set returned by MySQL SELECT statement will be divided into groups.ExampleFollowing is a good example to understand it −We have a table named ‘employees’ as follows −mysql> Select * from employees; +------+-------------+--------+------------+ | id   | designation | Salary | DoJ        | +------+-------------+--------+------------+ ... Read More

As we know that GROUP BY clause in a SELECT statement can divide the result set, returned by MySQL, in groups. Now if we want to return only some specific groups then need to apply filtering criteria at the group level. It can be done by using HAVING clause inside the GROUP BY clause. The example below will demonstrate it −ExampleSuppose we want to return only the group which is having an average salary of 55000 then we need to use filtering criteria as follows in HAVING clause −mysql> Select count(*), AVG(salary), Designation from employees GROUP BY designation having AVG(salary) ... Read More