Database Articles - Page 654 of 671

In MySQL, we can simply specify the SELECT conditions all alone to get the self-computed output. Following example will demonstrate it −mysql> Select 1+1; +-----+ | 1+1 | +-----+ | 2   | +-----+     1 row in set (0.02 sec) mysql> Select 1; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec)

We can insert the values into a table with the help of the self-computed output returned by MySQL. In this case, we do not need to use dummy ‘dual’ table. The syntax can be as follows −INSERT INTO table_name(column1, column2, column3, …) Select value1, value2, value3, …;ExampleIn the example below, we have inserted the values in ‘testing’ table by using the MySQL self-computed output.mysql> 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'; Query OK, 1 row affected (0.11 sec) Records: 1 Duplicates: 0 Warnings: 0 ... Read More

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