Found 4219 Articles for MySQLi

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

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.