Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
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.
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
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)
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
As we know that TRUNCATE will remove all the rows without removing table’s structure from the database. Same work can be done with the help of DELETE command on removing all the rows from the table. But there is a significant difference of re-initialization of PRIMARY KEY AUTO_INCREMENT between both the commands.Suppose a column is defined AUTO_INCREMENT having PRIMARY KEY CONSTRAINT, then on deleting all the rows with DELETE command would not re-initialize the table i.e. on entering the new rows, the AUTO_INCREMENT number will start after the last inserted row. In contrast, on using TRUNCATE, the table will be ... Read More
MySQL DELETE command is used to delete the row/s from a table. It is used with WHERE clause.SyntaxDELETE From Table_name WHERE Condition;ExampleIn the example below, we have deleted the rows from table ‘employee’ where id >=100.mysql> select * from employee; +------+--------+ | Id | name | +------+--------+ | 100 | Ram | | 200 | Gaurav | | 300 | MOHAN | +------+--------+ 3 rows in set (0.00 sec) mysql> delete from employee where id >=100; Query OK, 3 rows affected (0.06 sec) mysql> select * from employee; Empty set (0.00 sec)
The most significant difference between MySQL TRUNCATE and DROP command is that TRUNCATE command will not destroy table’s structure but in contrast DROP command will destroy table’s structure too.Examplemysql> Create table testing(id int PRIMARY KEY NOT NULL AUTO_INCREMENT, Name Varchar(20)); Query OK, 0 rows affected (0.24 sec) mysql> Insert into testing(Name) Values('Ram'), ('Mohan'), ('John'); Query OK, 3 rows affected (0.12 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> Select * from testing; +----+-------+ | id | Name | +----+-------+ | 1 | Ram | | 2 | Mohan | | 3 | John | +----+-------+ 3 rows ... Read More
As the name explains, it is used to completely remove the table from the database.SyntaxDrop table table_name;Examplemysql> Drop table Student; Query OK, 0 rows affected (0.09 sec)The query above completely removes the ‘Student’ table from the database. We can observe as MySQL returns an error message after running the following query −mysql> Select * from Student; ERROR 1146 (42S02): Table 'query.student' doesn't exist
Different date format characters used by MySQL DATE_FORMAT() function are as follows −Date Format CharacterMeaning %aIt is used to abbreviate the names of the weekdays like Sun, Mon, Tue, Wed, Thu, Fri and Sat.%bIt is used to abbreviate the names of the month like Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov and Dec.%cIt is used to abbreviate the number of the month like 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11 and 12. There will be no leading zero.%DIt is used to abbreviate the day of the month with an ordinal suffix like 1st, ... Read More
By using MODIFY keyword with ALTER TABLE command we can modify the column/s of MySQL table. The syntax for the same is as follows;SyntaxAlter table table_name MODIFY column_name1 datatype, MODIFY column_name2 datatype… MODIFY column_nameN datatype);ExampleIn the example below, with the help of MODIFY keyword in ALTER Command, the size of column ‘Address’ and ‘Grade’ has been modified.mysql> Alter table student modify address varchar(45),Modify Grade Varchar(15); Query OK, 5 rows affected (0.38 sec) Records: 5 Duplicates: 0 Warnings: 0