You can select specific month with the help of MONTH() function. The syntax is as follows −SELECT yourColumnName FROM yourTableName WHERE MONTH(yourColumnName) = yourValue;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table UserLoginTimeInformation    -> (    -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> UserLoginDatetime datetime    -> ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into UserLoginTimeInformation(UserLoginDatetime) values(date_add(now(), interval 3 month)); Query OK, 1 row affected (0.14 sec) ... Read More

You need to use DateTime class if you want to extract the only date from datetime field. The syntax is as follows −DateTime::createFromFormat("Y-m-d H:i:s",yourDateTimeValue)->format("yourFormatSpecifier");Now you can implement the above syntax in your PHP code to extract the only date from datetime field. The PHP code is as follows −$MySQLDataBaseDateTime = "2018-02-13 13:10:15"; echo DateTime::createFromFormat("Y-m-d H:i:s",$MySQLDataBaseDateTime)->format("d/m/Y");Here is the screenshot of the PHP code −Output13/02/2018

You can add auto_increment to a column in MySQL database with the help of ALTER command.The syntax is as follows −ALTER TABLE yourTableName MODIFY yourColumnName INT NOT NULL AUTO_INCREMENT;To open PhpMyAdmin on localhost, you need to type the following on localhost and press enter −localhost/phpmyadminThe screenshot is as follows −Above, we already have a table “AutoIncrementDemo”. In that, we have a column “UserId” set as Primary key. Let’s say we need to add auto_increment to the same column.For auto_increment, check the A.I as shown above. The same is marked below as well −After that press the Save button.Let us also ... Read More

Yes, we can add a column to a table from another table. Let us first create two tables. The query to create a table is as follows −mysql> create table FirstTable    -> (    -> UserId int,    -> UserName varchar(20)    -> ); Query OK, 0 rows affected (1.48 sec)Now create the second table. The query to create the second table is as follows −mysql> create table SecondTable    -> (    -> UserId int,    -> UserAge int    -> ); Query OK, 0 rows affected (1.57 sec)Now, add column Age to the first table. Firstly, ... Read More

First, you need to open the CMD with the help of shortcut key Windows+R key.After typing cmd, press the OK button. On pressing, you will get a command prompt. The screenshot is as follows −After that, you need to reach the /bin directory. Follow the below instructions. If you are a Windows user, then use the below query to reach the /bin directory.The query is as follows −mysql> select @@datadir;The following is the output displaying the path −+---------------------------------------------+ | @@datadir ... Read More

To get the exact number if table and columns in a MySQL database, use the DISTINCT inside COUNT().Let’s say we have a database ‘sample’ and we need to work on it to get the exact number of table and columns.To achieve it, the query is as follows −mysql> SELECT COUNT(DISTINCT TABLE_NAME) AS TotalTable, Count(Column_Name) AS TOTALColumn -> FROM INFORMATION_SCHEMA.COLUMNS -> WHERE TABLE_SCHEMA = 'sample';The following is the output displaying the count of table and columns in the database ‘sample’ −+------------+-------------+ | TotalTable | TOTALColumn | +------------+-------------+ | ... Read More

Let us first create a stored procedure −mysql> DELIMITER // mysql> CREATE PROCEDURE ExtenddatesWithMonthdemo(IN date1 datetime, IN NumberOfMonth int )    -> BEGIN    -> SELECT DATE_ADD(date1, INTERVAL NumberOfMonth MONTH) AS ExtendDate;    -> END;    -> // Query OK, 0 rows affected (0.20 sec) mysql> DELIMITER ;Now you check whether the stored procedure exists with the help SHOW CREATE command.The query is as follows −mysql> SHOW CREATE PROCEDURE ExtenddatesWithMonthdemo; The following is the output displaying the details of the stored procedure we created above: +--------------------------+--------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | Procedure                | sql_mode ... Read More

You can achieve case sensitive uniqueness and case insensitive search with the help of the following two ways −VARBINARY data type_bin collationVARBINARY data typeTo work with the VARBINARY data type, let us first create a table. The query to create a table is as follows −mysql> create table SearchingDemo2 -> ( -> UserId VARBINARY(128) NOT NULL, -> UNIQUE KEY index_on_UserId2(UserId ) -> )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; Query OK, 0 rows affected, 1 warning (0.99 sec)Keep in mind UserId has data type VARBINARY(128) and Index(‘index_on_UserId2’) on a column ‘UserId’._bin ... Read More

The following is the syntax to check whether a table is empty or not using MySQL EXISTS −SELECT EXISTS(SELECT 1 FROM yourTableName);ExampleFirst, let us create a table. The query to create a table is as follows −mysql> create table ReturnDemo    -> (    -> Id int,    -> Name varchar(10)    -> ); Query OK, 0 rows affected (0.79 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into ReturnDemo values(100, 'Larry'); Query OK, 1 row affected (0.18 sec) mysql> insert into ReturnDemo values(101, 'Bob'); Query OK, 1 row affected (0.28 sec) ... Read More

If you use COUNT(*) around the LEAST() then MySQL scans at least one index, therefore avoid LEAST(COUNT(*)) and use LIMIT.Let us first create a table. The query to create a table is as follows −mysql> create table ReturnDemo -> ( -> Id int, -> Name varchar(10) -> ); Query OK, 0 rows affected (0.79 sec)ExampleNow you can insert some records in the table using insert command. The query is as follows −mysql> insert into ReturnDemo values(100, 'Larry'); Query OK, 1 row affected (0.18 sec) mysql> insert into ReturnDemo values(101, 'Bob'); Query OK, 1 row affected (0.28 sec) mysql> insert into ... Read More