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

When we try to do such kind of operations with date values stored in the table then MySQL is assuming the date values as the number and perform the arithmetic.Suppose we have a table named ‘example’ having a date value in ‘orderdate’ column then following arithmetic operation will clarify the above −mysql> select * from example; +------------+ | orderdate  | +------------+ | 2017-05-25 | +------------+ 1 row in set (0.00 sec) mysql> select orderdate+10 from example; +--------------+ | orderdate+10 | +--------------+ |     20170535 | +--------------+ 1 row in set (0.00 sec) mysql> select orderdate*10 from example; ... Read More

With the help of CREATE DATABASE db-name command, we can check the statement of creating any MySQL database.mysql> SHOW CREATE DATABASE Sample; +----------+-------------------------------------------------------------------+ | Database | Create Database                                                   | +----------+-------------------------------------------------------------------+ | sample   | CREATE DATABASE `sample` /*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+-------------------------------------------------------------------+ 1 row in set (0.00 sec)The output shows how MySQL database named Sample has been created.

We can display the name of MySQL database that is currently in use by Select Database() command.mysql> select database(); +------------+ | database() | +------------+ | tutorial   | +------------+ 1 row in set (0.00 sec)This command shows that we currently use tutorial database.

Suppose currently we are using a tutorial database so it would be the default MySQL database for subsequent queries. Now, with the help of USE db_name statement, we can change the default database to other given database subsequent queries.mysql> USE Sample Database changedThe database has been changed to Sample from the tutorial. To verify this we can run the following command −mysql> select database(); +------------+ | database() | +------------+ | sample     | +------------+ 1 row in set (0.00 sec)

Such kind of calculations can cause unpredictable result because when the date is represented as MySQL string then MySQL tries to perform numeric operations on a string by taking only the first that appears. Following examples will clarify it −mysql> select '2017-10-17' + 20; +-------------------+ | '2017-10-17' + 20 | +-------------------+ |      2037         | +-------------------+ 1 row in set, 1 warning (0.00 sec) mysql> select '2017-10-25' - 17; +-------------------+ | '2017-10-25' - 17 | +-------------------+ |              2000 | +-------------------+ 1 row in set, 1 warning (0.00 sec) ... Read More

Comparison operator between dates will work in a logical way. In the following example, while comparing two dates, MySQL is simply comparing two numbers or string −mysql> select 20171027 < 20150825; +---------------------------+ | 20171027 < 20150825       | +---------------------------+ |                      0    | +---------------------------+ 1 row in set (0.00 sec)The 0 output shows that the result of the above query is FALSE.mysql> select 20171027 > 20150825; +--------------------------+ | 20171027 > 20150825      | +--------------------------+ |                      1   | +--------------------------+ 1 row in set (0.00 sec)The output ‘1’ shows that the result of the above query is TRUE.

There is a database named test in the list of databases displayed by the statement SHOW DATABASES. We can use test database but the main disadvantage is that anything created in this database can be removed/changed by anyone else with access to it. To avoid this we should take permission from MySQL administrator to use a database of our own. For taking permission following command must be run − mysql> grant all on tutorial.* to root@localhost; Query OK, 0 rows affected (0.10 sec) In the above command, I am taking permission for the tutorial database. Root is the ... Read More

Conversion of TIME(N) and DATETIME(N) values to numeric form can be done by adding 0(+0) to them. Followings are the rules for such kind of conversion −Converted to INTEGERThe TIME(N) and DATETIME(N) values will be converted to an integer when N is 0.For example, the values of CURTIME() and NOW() can be converted to integer values as follows −mysql> SELECT CURTIME(), CURTIME()+0; +-----------+-------------------+ | CURTIME() | CURTIME()+0       | +-----------+-------------------+ | 19:42:54  | 194254            | +-----------+-------------------+ 1 row in set (0.04 sec) mysql> SELECT NOW(), NOW()+0; +-------------------------+----------------------------------+ | NOW()       ... Read More