- Trending Categories
- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Found 4378 Articles for MySQL
151 Views
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
263 Views
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.
73 Views
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
1K+ Views
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
221 Views
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
106 Views
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)
2K+ Views
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
183 Views
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
54 Views
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
302 Views
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