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)

As we know that the default MySQL database would be the database that is currently in use for subsequent queries. We can get the list of tables in that database by using SHOW TABLES statement. mysql> SHOW TABLES; +------------------+ | Tables_in_sample | +------------------+ | employee | | new_student | | student | +------------------+ 3 rows in set (0.00 sec) The above statement shows the list of table in Sampledatabase.

MySQL can perform date arithmetic with addition and subtraction operators by adding together INTERVAL keyword with a unit of time, date or datetime.Example1Adding 2 days to a particular date.mysql> Select '2017-05-20' + INTERVAL 2 day; +-------------------------------+ | '2017-05-20' + INTERVAL 2 day | +-------------------------------+ | 2017-05-22                    | +-------------------------------+ 1 row in set (0.00 sec)Example2Subtracting 2 days from a particular date.mysql> Select '2017-05-20' - INTERVAL 2 day; +-------------------------------+ | '2017-05-20' - INTERVAL 2 day | +-------------------------------+ | 2017-05-18                    | +-------------------------------+ 1 row in ... Read More