Use IFNULL or COALESCE() function in order to convert MySQL NULL to 0.The syntax is as followsSELECT IFNULL(yourColumnName, 0) AS anyAliasName FROM yourTableName; The second syntax is as follows: SELECT COALESCE(yourColumnName, 0) AS anyAliasName FROM yourTableName;Let us first create a table. The query to create a table is as followsmysql> create table convertNullToZeroDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> Name varchar(20),    -> Salary int    -> ); Query OK, 0 rows affected (1.28 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into convertNullToZeroDemo(Name, Salary) values('John', ... Read More

You need to use ORDER BY clause to get the second last row of a table in MySQL.The syntax is as follows.select *from yourTableName order by yourColumnName DESC LIMIT 1, 1;To understand the above syntax, let us create a table. The query to create a table is as follows.mysql> create table secondLastDemo    -> (    -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> StudentName varchar(10)    -> ); Query OK, 0 rows affected (0.52 sec)Insert some records in the table using insert command.The query is as follows.mysql> insert into secondLastDemo(StudentName) values('Larry'); Query OK, 1 row affected (0.15 ... Read More

You can use distinct keyword to select all values from a table only once if they are repeated.The syntax is as followsselect distinct yourColumnName from yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows.mysql> create table displayOnlyDistinctValue    -> (    -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> UserName varchar(100),    -> UserAge int    -> ); Query OK, 0 rows affected (0.47 sec)Insert some records in the table using insert command.The query is as follows.mysql> insert into displayOnlyDistinctValue(UserName, UserAge) values('Larry', 23); Query OK, 1 row affected ... Read More

To select all entries from a particular month in MySQL, use the monthname() or month() function.The syntax is as follows.select *from yourTableName where monthname(yourColumnName)='yourMonthName';To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table selectAllEntriesDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> ShippingDate datetime -> ); Query OK, 0 rows affected (0.63 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into selectAllEntriesDemo(ShippingDate) values('2019-01-21'); Query OK, 1 row affected ... Read More

You need to use ALTER TABLE command along with MODIFYThe syntax is as followsALTER TABLE yourTableName MODIFY COLUMN yourColumnName varchar(100) NOT NULL;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table syntaxOfAlterCommandDemo    -> (    -> UserId int,    -> UserName varchar(30),    -> UserAge int,    -> UserCityName varchar(50)    -> ); Query OK, 0 rows affected (0.51 sec)Let us check the description of the table.The query is as followsmysql> desc syntaxOfAlterCommandDemo;The following is the output+--------------+-------------+------+-----+---------+-------+ | Field | Type ... Read More

To replace values in a table, use the CASE statement. To understand the concept, let us create a table. The query to create a table is as followsmysql> create table replaceValueDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> Name varchar(10),    -> isGreaterThan18 varchar(10)    -> ); Query OK, 0 rows affected (0.49 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into replaceValueDemo(Name, isGreaterThan18) values('John', 'YES'); Query OK, 1 row affected (0.24 sec) mysql> insert into replaceValueDemo(Name, isGreaterThan18) values('Carol', 'NO'); Query OK, 1 row affected (0.16 sec) ... Read More

To retrieve time as HH:MM format, use the DATE_FORMAT() function. To understand the function and retrieve time, let us create a table.The query to create a table is as followsmysql> create table retrieveTimeDemo    -> (    -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> UserArrivalTime datetime    -> ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into retrieveTimeDemo(UserArrivalTime) values('2013-01-21 13:45:34'); Query OK, 1 row affected (0.16 sec) mysql> insert into retrieveTimeDemo(UserArrivalTime) values('2014-11-25 11:40:56'); Query OK, 1 row affected (0.11 sec) mysql> insert into retrieveTimeDemo(UserArrivalTime) ... Read More

To get the maximum value from three different columns, use the GREATEST() function.The syntax is as followsSELECT GREATEST(yourColumnName1, yourColumnName2, yourColumnName3) AS anyAliasName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table MaxOfThreeColumnsDemo    -> (    -> First int,    -> Second int,    -> Third int    -> ); Query OK, 0 rows affected (0.73 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into MaxOfThreeColumnsDemo values(30, 90, 60); Query OK, 1 row affected (0.16 sec) mysql> insert into MaxOfThreeColumnsDemo values(100, ... Read More

Restart the MySQL Server with the help of restart command.The syntax is as followsRestartLet us first check the MySQL version.The query is as followsSELECT version();Now, implement the above command in order to restart the MySQL Server.The query is as followsmysql> restart; Query OK, 0 rows affected (0.00 sec)Case 1Now, MySQL Server is being restarted. If you try to perform any query during the restart command, you will get an error.The query is as followsmysql> show databases; ERROR 2013 (HY000): Lost connection to MySQL server during queryCase 2If the MySQL restart process is complete, then a new connection id will be ... Read More

To set auto increment initial value for MySQL table, use ALTER command. The first step would bealter table yourTableName modify yourColumnName int NOT NULL AUTO_INCREMENT PRIMARY KEY, add index(yourColumnName);The second step is as followsalter table yourTableName AUTO_INCREMENT=yourStartingValue;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table setAutoIncrementDemo    -> (    -> UserId int,    -> UserName varchar(20)    -> ); Query OK, 0 rows affected (0.75 sec)Now implement the above two steps to set auto increment initial value for MySQL table.Step 1 -The query is as followsmysql> alter ... Read More