Found 4219 Articles for MySQLi

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

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)