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 100 of 355
What 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 MoreHow to use compound INTERVAL unit in MySQL?
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)
Read MoreHow can we sort MySQL output in ascending order?
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)
Read MoreHow can we get sorted output based on multiple columns?
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)
Read MoreHow can I use the arithmetic operators (+,-,*,/) with unit values of INTERVAL keyword in MySQL?
We can use arithmetic operators (+, -, *, /) with the unit values of INTERVAL keyword as follows −Use of Addition (+)mysql> Select date('2017-10-22' + INTERVAL 2+2 Year) AS 'Date After (2+2)Years'; +------------------------+ | Date After (2+2) Years | +------------------------+ | 2021-10-22 | +------------------------+ 1 row in set (0.00 sec)Use of Subtraction (-)mysql> Select date('2017-10-22' + INTERVAL 2-2 Year) AS 'Date After (2-2)Years'; +------------------------+ | Date After (2-2) Years | +------------------------+ | 2017-10-22 | +------------------------+ 1 row in set (0.00 sec)Use of Multiplication (*)mysql> Select date('2017-10-22' + INTERVAL ...
Read MoreWhile fetching the data as output, how can I use multiple conditions on same column?
Followings are the ways in which we can write a query that returns only records that matches multiple conditions on the same columnBy using ‘OR’ logical operatorAs we know that MySQL ‘OR’ operator compares two expressions and returns TRUE if either of the expression is TRUE. Following example demonstrate that how we can use ‘OR’ operator for multiple conditions on the same columnmysql> Select * from Student WHERE Name = 'Gaurav' OR Name = 'Aarav'; +------+--------+---------+-----------+ | Id | Name | Address | Subject | +------+--------+---------+-----------+ | 1 | Gaurav | Delhi | Computers ...
Read MoreWhy is it not good practice to use date values with two-digits years in MySQL?
As we know that, YEAR(2) stores a year in 2-digit format. For example, we can write 69 to store 1969 as a year. In YEAR (2), the year can be specified from 1970 to 2069 (70 to 69).MySQL interprets 2-digit year values with the help of following rules −Year values in the range 00-69 are converted to 2000-2069. Year values in the range 70-99 are converted to 1970-1999.We must not store date values as a 2-digit format because values stored in this format becomes vague as the century is unknown.It can be understood more clearly with the help of following ...
Read MoreHow can we fetch a particular row as output from a MySQL table?
For fetching a particular row as output, we need to use WHERE clause in the SELECT statement. It is because MySQL returns the row based on the condition parameter given by us after WHERE clause.ExampleSuppose we want to fetch a row which contains the name ‘Aarav’ from student table then it can be done with the help of the following query −mysql> Select * from Student WHERE Name = 'Aarav'; +------+-------+---------+---------+ | Id | Name | Address | Subject | +------+-------+---------+---------+ | 2 | Aarav | Mumbai | History | +------+-------+---------+---------+ 1 row in set (0.00 sec)
Read MoreHow can we use WHERE clause with MySQL INSERT INTO command?
We can use conditional insert i.e. WHERE clause with INSERT INTO command in the case of new row insertion. It can be done with following ways −With the help of dummy tableIn this case, we insert the value from dummy table along with some conditions. The syntax can be as follows −INSERT INTO table_name(column1, column2, column3, …) Select value1, value2, value3, … From dual WHERE [conditional predicate];Examplemysql> Create table testing(id int, item_name varchar(10)); Query OK, 0 rows affected (0.15 sec) mysql> Insert into testing (id, item_name)Select 1, 'Book' From Dual Where 1=1; Query OK, 1 row affected (0.11 sec) ...
Read MoreWhat kind of compound units can be used in MySQL EXTRACT() function?
MySQL EXTRACT() function can use following compound units −SECOND_MICROSECONDMINUTE_MICROSECONDHOUR_MICROSECONDDAY_MICROSECONDMINUTE_SECONDHOUR_SECONDHOUR_MINUTEDAY_SECONDDAY_MINUTEDAY_HOURYEAR_MONTHSome of the examples of these compound units used in EXTRACT() function are as follows −mysql> Select EXTRACT(YEAR_MONTH from '2017-10-20'); +---------------------------------------+ | EXTRACT(YEAR_MONTH from '2017-10-20') | +---------------------------------------+ | 201710 | +---------------------------------------+ 1 row in set (0.00 sec)Above query will return the year and month value from the date.mysql> Select EXTRACT(DAY_HOUR from '2017-10-20 05:46:45'); +----------------------------------------------+ | EXTRACT(DAY_HOUR from '2017-10-20 05:46:45') | +----------------------------------------------+ | ...
Read More