Database Articles - Page 655 of 671

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

Different time format characters used by MySQL DATE_FORMAT() function are as follows −Time Format CharacterMeaning %HIt is used to abbreviate Hour on a 24-hour clock in two digits format like 00, 01, 02 up to 23. %hIt is used to abbreviate Hour on 12-hour clock in two digits format like 01, 02 up to 12. %iIt is used to abbreviate minutes in two digits format like 01, 02 up to 59. %lIt is used to abbreviate Hour on 12-hour clock in two digits format without zero-like 01, 02 up to 12. %pIt is used for A.M or P.M.%rIt is used to display time on 12-hour ... Read More

We can also use DATE_FORMAT() function to display the time in other formats. In this case, there would be two arguments of this function, first would be the time and second would be the format string.The following example will change the current time in specified format −mysql> SELECT DATE_FORMAT(NOW(), 'The time is %h:%i:%s %p'); +-----------------------------------------------+ | DATE_FORMAT(NOW(), 'The time is %h:%i:%s %p') | +-----------------------------------------------+ | The time is 06:02:28 AM                       | +-----------------------------------------------+ 1 row in set (0.00 sec)The following example will change the given time in specified format ... Read More