Found 4220 Articles for MySQLi

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

As we know that this function converts a number of seconds into TIMESTAMP value. So by providing 0 seconds as the argument, it will give us the starting range of TIMESTAMP data type.mysql> Select FROM_UNIXTIME(0); +-------------------------+ | FROM_UNIXTIME(0)        | +-------------------------+ | 1970-01-01 05:30:00     | +-------------------------+ 1 row in set (0.00 sec)Now if we will change the argument from 0 to 60 seconds then the time will be changed by 01 minutes.mysql> Select FROM_UNIXTIME(60); +-------------------------+ | FROM_UNIXTIME(60)       | +-------------------------+ | 1970-01-01 05:31:00     | +-------------------------+ 1 row in set (0.00 sec)

It is exactly reverse of UNIX_TIMESTAMP() and can be done with the help of FROM_UNIXTIME() function. For example, 11576070 seconds would be TIMESTAMP ‘1970-05-15 05:04:30’.mysql> Select FROM_UNIXTIME(11576070); +--------------------------------+ | FROM_UNIXTIME(11576070)        | +--------------------------------+ |      1970-05-15 05:04:30       | +--------------------------------+ 1 row in set (0.00 sec)

As we know that with the help of MySQL UNIX_TIMESTAMP function, we can produce the number of seconds from given date/DateTime. But when we try to convert a date like ‘1965-05-15’ it would give 0(Zero) as output because the range of TIMESTAMP is between ‘1970-01-01 00:00:01’ to ‘2038-01-19 08:44:07’. Hence, the date values beyond TIMESTAMP range cannot be converted and will return 0 as output always.Examples are given below −mysql> Select UNIX_TIMESTAMP ('1965-05-15'); +----------------------------------------------+ | unix_timestamp('1965-05-15 05:04:30')        | +----------------------------------------------+ |                                   ... Read More