- Trending Categories
- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Found 6702 Articles for Database
126 Views
The range of date time value that we can pass as an argument to MySQL UNIX_TIMESTAMP function is the same as the range of TIMESTAMP data type i.e. between ‘1970-01-01 00:00:01’ to ‘2038-01-19 08:44:07’. If we give the date time values in UNIX_TIMESTAMP function beyond or below TIMESTAMP range, MySQL will return 0 as output. It can be understood with the help of the following example −mysql> select UNIX_TIMESTAMP('2038-01-19 08:44:07'); +---------------------------------------+ | UNIX_TIMESTAMP('2038-01-19 08:44:07') | +---------------------------------------+ | 2147483647 | +---------------------------------------+ 1 row in set (0.00 sec) ... Read More
130 Views
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)
120 Views
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)
138 Views
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 More
76 Views
While considering the year as 4-digit value, minimum of 8 digits in a string or number is required for MySQL to specify it as a date value. In this case, if we also want to store microseconds then the value can be up to a maximum of 20 digits.mysql> Select TIMESTAMP('20171022040536.100000'); +-----------------------------------+ | TIMESTAMP('20171022040536100000') | +-----------------------------------+ | 2017-10-22 04:05:36.100000 | +-----------------------------------+ 1 row in set, 1 warning (0.00 sec)The query above is taking 20 digits string for TIMESTAMP value. Last 6 digits are for microseconds.mysql> Select TIMESTAMP(20171022); +---------------------+ | TIMESTAMP(20171022) | +---------------------+ | 2017-10-22 00:00:00 | +---------------------+ ... Read More
115 Views
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 MySQL ... Read More
88 Views
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 More
71 Views
We know that MySQL SELECT command is used to fetch data from a MySQL table. When you select rows, the MySQL server is free to return them in any order, unless you instruct it otherwise by saying how to sort the result. But, we can sort a result set by adding an ORDER BY clause that names the column or columns which you want to sort.SyntaxSelect column1, column2, …, columN From table_name ORDER BY column1[column2, …];ExampleIn the example below, MySQL returns the result set sorted on the basis of column ‘Name’;mysql> Select Id, Name, Address from Student ORDER BY Subject; ... Read More
172 Views
If a string or number, even without any delimiter, in the format of YYYYMMDDHHMMSS or YYMMDDHHMMSS is making sense as the date is provided then MySQL interpret that string as a valid date.Examples are given for valid as well as invalid dates −mysql> Select Timestamp(20171022040536); +---------------------------+ | Timestamp(20171022040536) | +---------------------------+ | 2017-10-22 04:05:36 | +---------------------------+ 1 row in set (0.00 sec) mysql> Select Timestamp('20171022040536'); +-----------------------------+ | Timestamp('20171022040536') | +-----------------------------+ | 2017-10-22 04:05:36 | +-----------------------------+ 1 row in set (0.00 sec) mysql> Select Timestamp('171022040536'); +---------------------------+ | Timestamp('171022040536') | +---------------------------+ | 2017-10-22 04:05:36 ... Read More
47 Views
In that case, MySQL will return all zeros at the place of time along with correct date part. An example is as follows in which we used character ‘W’ at the place of ‘T’ or ‘Space’ between date and time part −mysql> Select TIMESTAMP('2017-10-20W06:10:36'); +----------------------------------+ | TIMESTAMP('2017-10-20W06:10:36') | +----------------------------------+ | 2017-10-20 00:00:00 | +----------------------------------+ 1 row in set, 1 warning (0.00 sec)