MySQLi Articles - Page 330 of 341

In this matter, MySQL permits us to use a relaxed format to date. We can use any punctuation character between date parts as a delimiter. Some examples are as follows −mysql> Select date ('2016/10/20'); +---------------------+ | date ('2016/10/20') | +---------------------+ | 2016-10-20          | +---------------------+ 1 row in set (0.00 sec) mysql> Select date('2016^10^20'); +--------------------+ | date('2016^10^20') | +--------------------+ | 2016-10-20         | +--------------------+ 1 row in set (0.00 sec) mysql> Select date ('2016@10@20'); +---------------------+ | date ('2016@10@20') | +---------------------+ | 2016-10-20          | +---------------------+ 1 row in set (0.00 sec) mysql> Select date ('2016+10+20'); +---------------------+ | date ('2016+10+20') | +---------------------+ | 2016-10-20          | +---------------------+ 1 row in set (0.00 sec)

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)

The SELECT command can be used to fetch one or more columns as output from MySQL table. An  example is given below to fetch one or more columnsmysql> Select * from Student; +------+---------+---------+-----------+ | Id   | Name    | Address | Subject   | +------+---------+---------+-----------+ | 1    | Gaurav  | Delhi   | Computers | | 2    | Aarav   | Mumbai  | History   | | 15   | Harshit | Delhi   | Commerce  | | 17   | Raman   | Shimla  | Computers | +------+---------+---------+-----------+ 4 rows in set (0.01 sec) ... Read More

As we know that SELECT clause is used to show all the rows and columns hence SELECT clause is evaluated in the last by the database server.

It is possible with the help of keyword Quarter as follows −mysql> Select '2017-06-20' + INTERVAL 1 Quarter AS 'After 3 Months Interval'; +-------------------------+ | After 3 Months Interval | +-------------------------+ | 2017-09-20              | +-------------------------+ 1 row in set (0.00 sec)

We can add ‘half year interval’ in date ith the following ways −(A) By adding interval of 6 Monthsmysql> Select '2017-06-20' + INTERVAL 6 Month AS 'After Half Year Interval'; +--------------------------+ | After Half Year Interval | +--------------------------+ |  2017-12-20              | +--------------------------+ 1 row in set (0.00 sec)(B) By adding interval of 2 quartersmysql> Select '2017-06-20' + INTERVAL 2 Quarter AS 'After Half Year Interval'; +--------------------------+ | After Half Year Interval | +--------------------------+ | 2017-12-20               | +--------------------------+ 1 row in set (0.00 sec)Above query will add half year interval in date with the help of Quarter keyword.

We can fetch all the record from a MySQL table by using SELECT * from table_name; query. An example is as follows, fetched all the records from ‘Employee’ table −mysql> Select * from Employee; +------+--------+ | Id   | Name   | +------+--------+ | 100  | Ram    | | 200  | Gaurav | | 300  | Mohan  | +------+--------+ 3 rows in set (0.00 sec)

We can create a function, which accepts the date values as its argument and returns the difference in year, month and days, as followsmysql> CREATE FUNCTION date_difference(Date1 DATE, date2 DATE) RETURNS VARCHAR(30)    -> RETURN CONCAT(    -> @years := TIMESTAMPDIFF(YEAR, date1, date2), IF (@years = 1, ' year, ', ' years, '),    -> @months := TIMESTAMPDIFF(MONTH, DATE_ADD(date1, INTERVAL @years YEAR), date2), IF (@months = 1, ' month, ', ' months, '),    -> @days := TIMESTAMPDIFF(DAY, DATE_ADD(date1, INTERVAL @years * 12 + @months MONTH), date2), IF (@days = 1, ' day', ' days')) ;    Query OK, 0 ... Read More

As we know that DATEDIFF() function is used to get the difference in a number of days between two dates. Hence, it is quite possible that it returns negative value as well.mysql> select * from differ; +------------+-------------+ | OrderDate  | WorkingDate | +------------+-------------+ | 2017-10-22 | 2017-10-29  | | 2017-10-25 | 2017-10-30  | | 2017-10-25 | 2017-11-30  | +------------+-------------+ 3 rows in set (0.00 sec)Above query will return the values from table ‘differ’. Now, if someone wants to get the difference between OrderDate and WorkingDate then the output would be negative as follows −mysql> Select DATEDIFF(OrderDate, WorkingDate)AS 'DIFFERENCE IN DAYS' ... Read More

As we know that we can copy the data and structure from an existing table by CTAS script. Use of WHERE clause is demonstrated in the example belowmysql> Create table EMP_BACKUP2 AS SELECT * from EMPLOYEE WHERE id = 300 AND Name = 'Mohan'; Query OK, 1 row affected (0.14 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> Select * from EMP_BACKUP2; +------+-------+ | Id   | Name  | +------+-------+ | 300  | Mohan | +------+-------+ 1 row in set (0.00 sec)In the example above, we have created a table named EMP_BACKUP1 from table ‘Employee’ with some conditions. MySQL creates the table with only one row based on those conditions.