INSERT command is used to add values to the columns of a MySQL table. We need to specify the values in INSERT command for all the columns as follows −SyntaxINSERT INTO table_name values(value1, value2, …)ExampleSuppose we have a table named ‘Stock’ with three columns ‘Item_id’, ‘Item_name’ and ‘Item_rate’ then with the help of following query we can add values in these columns.mysql> INSERT INTO Stock values(1, 'HistoryBook', 250); Query OK, 1 row affected (0.07 sec) mysql> INSERT INTO Stock values(2, 'DBMSBook', 280); Query OK, 1 row affected (0.10 sec) mysql> Select * from Stock; +---------+-------------+-----------+ | item_id | ... Read More

With the help of DATE_ADD() function, we can add one day to the DATETIME field of a table.mysql> Select StudentName, RegDate, Date_ADD(RegDate, INTERVAL +1 day) AS 'NEXT DAY'        from testing where StudentName = 'gaurav'; +-------------+---------------------+---------------------+ | StudentName | RegDate             | NEXT DAY            | +-------------+---------------------+---------------------+ | Gaurav      | 2017-10-29 08:48:33 | 2017-10-30 08:48:33 | +-------------+---------------------+---------------------+ 1 row in set (0.00 sec)Above query will add one day to the RegDate where StudentName is Gaurav in MySQL table named ‘testing’.

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

When we try to extract hour value from a date, then EXTRACT() function will give the output 0 with a warning as shown in the below-given example −mysql> Select EXTRACT(Hour from '2017-10-20'); +---------------------------------+ | EXTRACT(Hour from '2017-10-20') | +---------------------------------+ | 0                               | +---------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> Show Warnings; +---------+------+----------------------------------------------+ | Level   | Code | Message                                      | +---------+------+----------------------------------------------+ ... Read More

We can do it by providing unit value ‘quarter’ to EXTARCT() function. Examples are as follows −mysql> Select EXTRACT(Quarter from '2017-07-29'); +------------------------------------+ | EXTRACT(Quarter from '2017-07-29') | +------------------------------------+ |                                  3 | +------------------------------------+ 1 row in set (0.00 sec)Above query will give the value of quarter from a particular given date.mysql> Select EXTRACT(Quarter from now()); +-----------------------------+ | EXTRACT(Quarter from now()) | +-----------------------------+ |                           4 | +-----------------------------+ 1 row in set (0.00 sec)Above query will give the value of quarter from the current date.

We can use ‘COMMENT’ keyword with ALTER TABLE command while modifying the column to add comments on columns. For example if we want to add comment in column ‘id’ of table ‘testing’ then following query will do it −mysql> 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

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