With the help of CURDATE() and NOW() function, we can insert current date automatically in a column of MySQL table.ExampleSuppose we want to insert current date automatically in an OrderDate column of table year_testing the following query will do this −mysql> Insert into year_testing (OrderDate) Values(CURDATE()); Query OK, 1 row affected (0.11 sec) mysql> Select * from year_testing; +------------+ | OrderDate  | +------------+ | 2017-10-28 | +------------+ 1 row in set (0.00 sec) mysql> Insert into year_testing (OrderDate) Values(NOW()); Query OK, 1 row affected, 1 warning (0.12 sec) mysql> Select * from year_testing; +------------+ | OrderDate  | +------------+ ... Read More

We can store a date with only year value and have zero months as well as zero-days in MySQL table by disabling NO_ZERO_IN_DATE mode. If this mode is enabled then MySQL will count such kind of date as invalid date and stores all zeros.mysql> Insert into year_testing (OrderDate) values('2017:00:00'); Query OK, 1 row affected (0.09 sec) mysql> select * from year_testing; +------------+ | OrderDate  | +------------+ | 2017-00-00 | +------------+ 1 row in set (0.00 sec) mysql> SET sql_mode = 'NO_ZERO_IN_DATE'; Query OK, 0 rows affected (0.00 sec) mysql> Insert into year_testing(OrderDate) values('2017:00:00'); Query OK, 1 row ... Read More

Actually, there is no default database for the user. But we have default database for the current session. It can be seen from the following query −mysql> Select Database(); +------------+ | Database() | +------------+ | sample     | +------------+ 1 row in set (0.00 sec)The above result set shows that we are using ‘sample’ database currently. It is set for the current session. We can set another database, with the help of USE statement, also for the current session as follows −mysql> USE query; Database changed mysql> Select Database(); +------------+ | Database() | +------------+ | query     ... Read More

As we know that CURDATE() only returns the date unit so it would be ambiguous to use INTERVAL of time unit with CURDATE(). MySQL always represents current date with ‘00:00:00’ time hence when we use INTERVAL of time unit with CURDATE() then such kind of time arithmetic would take this time into consideration. Following examples will clarify it −mysql> Select CURDATE() + INTERVAL 0 hour; +-----------------------------+ | curdate() + Interval 0 hour | +-----------------------------+ | 2017-10-28 00:00:00         | +-----------------------------+ 1 row in set (0.00 sec) mysql> select CURDATE() + INTERVAL 1 hour; +-----------------------------+ | curdate() ... Read More

INTERVAL keyword with NOW() and CURDATE() MySQL functions can be used in similar fashion as it can be used with time, date or datetime units of a date value.ExampleUsing INTERVAL with MySQL NOW()mysql> Select NOW() + INTERVAL 2 day; +------------------------+ | NOW() + INTERVAL 2 day | +------------------------+ | 2017-10-30 09:19:31    | +------------------------+ 1 row in set (0.00 sec) mysql> Select NOW() - INTERVAL 2 day; +------------------------+ | NOW() - INTERVAL 2 day | +------------------------+ | 2017-10-26 09:19:45    | +------------------------+ 1 row in set (0.00 sec) mysql> Select NOW() - INTERVAL 2 hour; +-------------------------+ | NOW() ... Read More

As we have checked the size of the MySQL database, similarly we can also check the size of tables in a particular database. It can be done as follows −mysql> SELECT     -> table_name AS "Table",     -> round(((data_length + index_length) / 1024 / 1024), 2) as SIZE     -> FROM information_schema.TABLES     -> WHERE table_schema = "SAMPLE"     -> ORDER BY SIZE; +-------------+-------+ | Table       | SIZE  | +-------------+-------+ | employee    | 0.02  | | student     | 0.02  | | new_student | 0.02  | +-------------+-------+ 3 rows in set (0.00 sec)Here this output gives the size of three tables in the Sample database.

Actually, the behavior of MySQL depends upon the allow_invalid_dates mode. If this mode is enabled then MySQL will accept the invalid date and it would perform the date arithmetic as it performs with a valid date. Otherwise, if this mode is inactive then it will not accept the invalid date and would produce NULL as output. mysql> select '2017-02-30' + INTERVAL 7 day; +-------------------------------+ | '2017-02-30' + INTERVAL 7 day | +-------------------------------+ | NULL ... Read More

We can check the size of all MySQL databases with the help of the following statementmysql> SELECT table_schema "Database", -> SUM(data_length + index_length)/1024/1024 "Size in MB" -> FROM information_schema.TABLES GROUP BY table_schema; +--------------------+----------------+ | Database           | Size in MB     | +--------------------+----------------+ | gaurav             | 1.04785156     | | information_schema | 0.00878906     | | mysql              | 0.68551350     | | performance_schema | 0.00000000     | | sample             | 0.04687500     | | tutorial           | 0.03125000     | +--------------------+----------------+ 6 rows in set, 1 warning (0.33 sec)

As the name suggests CURDATE() function will return the current date. In simple words, we can say that it would return only the date not time.mysql> select CURDATE(); +------------+ | CURDATE()  | +------------+ | 2017-10-28 | +------------+ 1 row in set (0.00 sec)In contrast, NOW() function will return current date and time.mysql> Select NOW(); +---------------------+ | NOW()               | +---------------------+ | 2017-10-28 09:10:16 | +---------------------+ 1 row in set (0.00 sec)

The following statement shows the list of two tables having a column ‘email’ in Sample database −mysql> SELECT DISTINCT TABLE_NAME     -> FROM INFORMATION_SCHEMA.COLUMNS     -> WHERE COLUMN_NAME IN('EMAIL')     -> AND TABLE_SCHEMA = 'SAMPLE'; +---------------+ | TABLE_NAME    | +---------------+ | employee      | | new_student   | +---------------+ 2 rows in set (0.04 sec)