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 174 of 546
How can we use existing values of the rows to provide new values in the SET clause of UPDATE statement?
Existing values of the row can be used to provide new values in the SET clause if that row matches the WHERE clause in an UPDATE statement. Following is the example to demonstrate it.ExampleSuppose we have a table named ‘tender’ as follows −mysql> Select * from tender; +-----------+---------+------+ | tender_id | company | rate | +-----------+---------+------+ | 200 | ABC | 1000 | | 300 | ABD | 6000 | | 301 | ABE | 7000 | | 302 | ABF ...
Read MoreHow MySQL evaluates if I store date along with time value in a column having DATE data type?
As we know that the default format for MySQL DATE data type is “YYYY-MM-DD” and in this format, there is no possibility to store the time value. But still, if we store date along with time value in a column having DATE data type then MySQL will show a warning and store only the date value in that column. Following example will exhibit it −Examplemysql> Create table date_time_test(date DATE); Query OK, 0 rows affected (0.44 sec) mysql> Insert into date_time_test(date) values(‘2017-09-09 09:34:21’); Query OK, 1 row affected, 1 warning (0.03 sec) mysql> Select * from date_time_test; +------------+ | ...
Read MoreHow can we update columns values on multiple rows with a single MySQL UPDATE statement?
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 MoreIn MySQL, how can we use FROM_UNIXTIME() function with format string?
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 MoreHow can we apply filtering criteria at group levels of the result set returned by MySQL?
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 MoreHow can we divide the result set returned by MySQL into groups?
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 MoreOn passing an out-of-range value in UNIX_TIMESTAMP() or FROM_UNIXTIME() function, what MySQL will return?
When we pass an out-of-range value in UNIX_TIMESTAMP, MySQL returns 0. The valid range of value is same as for the TIMESTAMP data type.Examplemysql> Select UNIX_TIMESTAMP('1969-01-01 04:05:45'); +---------------------------------------+ | UNIX_TIMESTAMP('1969-01-01 04:05:45') | +---------------------------------------+ | 0 | +---------------------------------------+ 1 row in set (0.00 sec)When we pass an out-of-range value in FROM_UNIXTIME, MySQL returns NULL. The valid range of values is same as for the INTEGER data type.Examplemysql> Select FROM_UNIXTIME(2147483648); +---------------------------+ | FROM_UNIXTIME(2147483648) | +---------------------------+ | NULL | +---------------------------+ 1 row in set (0.00 sec)
Read MoreWhat MySQL will return on adding microseconds in the timestamp value for converting it into an integer?
As we know that the value of timestamp can be converted to a number of seconds with the help of UNIX_TIMESTAMP() function. MySQL would ignore the microseconds added to the value of timestamp because the value of UNIX_TIMESTAMP is only 10digits long.Examplemysql> SELECT UNIX_TIMESTAMP('2017-10-22 04:05:36')AS 'Total Number of Seconds'; +-------------------------+ | Total Number of Seconds | +-------------------------+ | 1508625336 | +-------------------------+ 1 row in set (0.00 sec) mysql> SELECT UNIX_TIMESTAMP('2017-10-22 04:05:36.200000')AS 'Total Number of Seconds'; +-------------------------+ | Total Number of Seconds | +-------------------------+ | 1508625336 | +-------------------------+ ...
Read MoreHow is it possible to filter out the duplications in the rows of result set return by MySQL?
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 MoreWhat is the difference between UNIX TIMESTAMPS and MySQL TIMESTAMPS?
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