Found 4220 Articles for MySQLi

The database is created only once but it is necessary to select it each time we begin a MySQLsession. It can be done with the help of USE db_name statement on MySQL command line tool.mysql> Use Query; Database changedIt shows that we are now using query database.We can also select the database while invoking the MySQL from Windows command line. It can be done with the help of the following command −C:\Program Files\MySQL\bin>mysql -u root -p query Enter password: *****Here, query is the name of the database we are going to use for current MySQL sessionRead More

Actually, the case sensitivity of database and table name depends a lot on the case sensitivity of the underlying operating system. Hence, we can say that such names are not case sensitive in Windows but are case sensitive in most varieties of Unix.

The SHOW DATABASES command is used to display the list of currently existing MySQL databases.mysql> Show Databases; +-----------------------------+ | Database                    | +-----------------------------+ | information_schema          | | gaurav                      | | mysql                       | | performance_schema          | | query                       | | query1                      | | sys                         | | tutorials                   | +-----------------------------+ 8 rows in set (0.02 sec)

The function UNIX_TIMESTAMP produce the output in seconds i.e this function will convert the specified date or datetime value into a total number of seconds.For example, the date ‘1970-05-15 05:04:30’ would be converted to total 11576070 seconds by UNIX_TIMESTAMP function.mysql> select UNIX_TIMESTAMP('1970-05-15 05:04:30'); +---------------------------------------+ | UNIX_TIMESTAMP('1970-05-15 05:04:30') | +---------------------------------------+ | 11576070                              | +---------------------------------------+ 1 row in set (0.09 sec)

CREATE DATABASE db_name can be used to create our own choice MySQL database. For example to create a database named Sample, we should have to run the command as follows −mysql> CREATE DATABASE Sample; Query OK, 1 row affected (0.04 sec)

For storing the date like ‘0000-00-00’ in a column of MySQL table, we must have to set the SQL mode to ‘allow_invalid_date’. Following example will demonstrate it −mysql> SET sql_mode = 'allow_invalid_dates'; Query OK, 0 rows affected, 1 warning (0.03 sec) mysql> Create table test_date(date_order date); Query OK, 0 rows affected (0.45 sec) mysql> Insert into test_date(date_order) values('0000-00-00'); Query OK, 1 row affected (0.04 sec) mysql> Select * from test_date; +------------+ | date_order | +------------+ | 0000-00-00 | +------------+ 1 row in set (0.00 sec)

YEAR(2) stores a year in 2-digit format. For example, we can write 69 to store 1969 a year. In YEAR (2), the year can be specified from 1970 to 2069 (70 to 69).YEAR(4) stores a year in 4-digit format. For example, we need to write 19669 to store 1969 as a year. In YEAR (4), the year can be specified from 1901 to 2155.MySQL interprets 2-digit year values with the assistance of following rules:Year values within the vary 00-69 are converted to 2000-2069.Year values in the range 70-99 are converted to 1970-1999.We must not store date values as a 2-digit ... Read More

To store such kind of dates where the day, month or both month & day are zero we must have to set mode of sql to allow_invalid_dates mode.mysql> set sql_mode = 'allow_invalid_dates'; Query OK, 0 rows affected (0.00 sec) mysql> insert into check_date(OrderDate) values('2017-00-00'); Query OK, 1 row affected (0.06 sec) mysql> select * from check_date; +-------------+ | OrderDate | +-------------+ | 2017-00-00 | +-------------+ 1 row in set (0.00 sec)Above query will insert the date in which both month & day values are zero.mysql> insert into check_date(Orderdate) values ('2017-00-05'); Query OK, 1 row affected (0.07 sec) ... Read More

In DATEDIFF() function only the date parts of the values are used in calculation hence we can use TIMESTAMPDIFF() function to calculate full 24 hour days between two specified dates.For example, if we want to find full 24hour days between ‘2017-05-27 11:59:00’ and 2017-05-23 12:00:00’ then following would be MySQL query −mysql> Select TIMESTAMPDIFF(DAY, '2017-05-23 12:00:00' , '2017-05-27 11:59:00'); +---------------------------------------------------------------------------+ | TIMESTAMPDIFF(DAY, '2017-05-23 12:00:00' , '2017-05-27 11:59:00')         | +---------------------------------------------------------------------------+ | 3                                                                         | +---------------------------------------------------------------------------+ 1 row in set (0.00 sec)

With the help of DATEDIFF(expr1, expr2) we can find the business days between two specified dates.For example, if we want to find business days between ‘2017-05-27’ and ‘2017-05-23’ then following would be MySQL query −mysql> Select DATEDIFF('2017-05-27','2017-05-23') AS 'Total Business Days'; +----------------------+ | Total Business Days  | +----------------------+ | 4                    | +----------------------+ 1 row in set (0.00 sec)