The MySQL DateTime instance can be converted into seconds with the help of UNIX_TIMESTAMP() function in the following way −mysql> Select UNIX_TIMESTAMP('2017-05-15 04:05:30') AS 'NUMBER OF SECONDS'; +-------------------+ | NUMBER OF SECONDS | +-------------------+ |        1494801330 | +-------------------+ 1 row in set (0.00 sec)Above query will convert the given datetime instance into total number of seconds.mysql> Select UNIX_TIMESTAMP(NOW()) AS 'NUMBER OF SECONDS'; +-------------------+ | NUMBER OF SECONDS | +-------------------+ |        1509248856 | +-------------------+ 1 row in set (0.00 sec)Above query will convert the current DateTime instance into a total number of seconds.mysql> ... Read More

CREATE TABLE command with LIKE keyword will be able to define the structure of a MySQL table same as the structure of another table.SyntaxCREATE TABLE new_table LIKE old_table;Examplemysql> Create table employee(ID INT PRIMARY KEY NOT NULL AUTO_INCREMENT, NAME VARCHAR(20)); Query OK, 0 rows affected (0.21 sec) mysql> Describe employee; +-------+-------------+------+-----+---------+----------------+ | Field | Type        | Null | Key | Default | Extra          | +-------+-------------+------+-----+---------+----------------+ | ID    | int(11)     | NO   | PRI | NULL    | auto_increment | | NAME  | varchar(20) | YES  |     ... Read More

For creating a new table just like old one along with its data, trigger, and indexes, we need to run following two queriesCREATE TABLE new_table LIKE old_table; INSERT new_table SELECT * from old_table;Examplemysql> Create table employee(ID INT PRIMARY KEY NOT NULL AUTO_INCREMENT, NAME VARCHAR(20)); Query OK, 0 rows affected (0.21 sec) mysql> Describe employee; +-------+-------------+------+-----+---------+----------------+ | Field | Type        | Null | Key | Default | Extra          | +-------+-------------+------+-----+---------+----------------+ | ID    | int(11)     | NO   | PRI | NULL    | auto_increment | | NAME  | varchar(20) | ... Read More

We can apply EXTRACT() function on the dates stored in MySQL table in the following way −The following query is showing what dates are entered in table ‘testing’mysql> Select * from testing; +-------------+---------------------+ | StudentName | Dateofreg           | +-------------+---------------------+ | Ram         | 2017-10-28 21:24:24 | | Shyam       | 2017-10-28 21:24:30 | | Mohan       | 2017-10-28 21:24:47 | | Gaurav      | 2017-10-29 08:48:33 | +-------------+---------------------+ 4 rows in set (0.00 sec)Now, we can apply EXTRACT() function, to obtain the value of the year, on ... Read More

By using EXTRACT() function we can obtain the part from current date or from given date. The parts of the date can be obtained in the form of year, month, days, hours, minutes, seconds and microseconds.Examplesmysql> Select EXTRACT(Year from NOW()) AS YEAR; +-------+ | YEAR  | +-------+ |   2017| +-------+ 1 row in set (0.00 sec)Above MySQL query will obtain the year from current date.mysql> Select EXTRACT(Month from '2017-09-21')AS MONTH; +-------+ | MONTH | +-------+ |     9 | +-------+ 1 row in set (0.00 sec)Above MySQL query will obtain the month from given date.

We can use both the format characters together in DATE_FORMAT() function. The following example will clarify this −mysql> SELECT DATE_FORMAT(NOW(), 'The time is %a %h:%i:%s:%f %p'); +-----------------------------------------------------+ | DATE_FORMAT(NOW(), 'The time is %a %h:%i:%s:%f %p') | +-----------------------------------------------------+ | The time is Sun 06:35:06:000000 AM                  | +-----------------------------------------------------+ 1 row in set (0.00 sec)Above query is using date format character ‘%a’ along with other time format characters.Following is another example in which both format characters are used together −mysql> SELECT DATE_FORMAT(NOW(), 'The date & time is %a %D %M %Y %h:%i:%s:%f %p'); ... Read More

It can be done with the help of following query which uses adddate() function and we are generating the days between ‘2016-12-15’ and ‘2016-12-31’ −mysql> select * from     -> (select adddate('1970-01-01', t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date from     -> (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,     -> (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union ... Read More

UPDATE command along with WHERE clause can be used to change the value of an instance of a row. Basically, MySQL will change the value on the basis of the condition given in the query. Following example can demonstrate itSuppose we want to change the name from ‘Ram’ to ‘Mohit’ in the ‘testing’ table given below −mysql> Select * from testing; +----+---------+ | Id | Name    | +----+---------+ | 1  | Harshit | | 2  | Lovkesh | | 3  | Ram     | | 4  | Gaurav  | +----+---------+ 4 rows in set (0.00 sec)Now ... Read More

RENAME command is used to change the name of a MySQL table. Its syntax is as follows −RENAME table old_tablename to new_tablename2;ExampleIn the example below, we rename the table ‘testing’ to ‘test’.mysql> RENAME table testing to test; Query OK, 0 rows affected (0.17 sec) mysql> Select * from testing; ERROR 1146 (42S02): Table 'query.testing' doesn't exist mysql> Select * from test; +-----+---------+ | id1 | Name    | +-----+---------+ | 1   | Harshit | | 2   | Lovkesh | | 3   | MOHIT   | | 4   | MOHIT   | +-----+---------+ 4 rows in set (0.02 sec)

It can be done by using ‘COMMENT’ keyword while modifying the column with ALTER TABLE command. For example if we want to add comment in column ‘id’ of table ‘testing’ then following query will do itmysql> ALTER TABLE testing MODIFY id INT COMMENT 'id of employees'; Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0With following query it can be checked in the comment field of a column.mysql> Show full columns from testing\G *************************** 1. row ***************************      Field: id       Type: int(11)  Collation: NULL       Null: NO       ... Read More