You can update MySQL with IF condition as well as CASE statement. For this purpose, let us first create a table. The query to create a table −mysql> create table UpdateWithIfCondition    −> (    −> BookId int,    −> BookName varchar(200)    −> ); Query OK, 0 rows affected (0.60 sec)Now you can insert records in the table using insert command. The query is as follows −mysql> insert into UpdateWithIfCondition values(1000, 'C in Depth'); Query OK, 1 row affected (0.12 sec) mysql> insert into UpdateWithIfCondition values(1001, 'Introduction to Java'); Query OK, 1 row affected (0.14 sec)Display all records ... Read More

To convert int to current format, use CONCAT() with FORMAT() function from MySQL.The syntax is as follows −SELECT CONCAT(‘CurrencySymbol’, FORMAT(yourColumnName, valueAfterDecimal)) as AnyVariableName from yourTableName;To understand the above syntax, let us create a table. The query to create a table −mysql> create table AddingCurrencySymbolDemo −> ( −> Amount int −> ); Query OK, 0 rows affected (1.50 sec)Insert records in the table using insert command. The query is as follows −mysql> insert into AddingCurrencySymbolDemo values(250); Query OK, 1 row affected (0.22 sec) mysql> insert into AddingCurrencySymbolDemo values(500); Query OK, 1 ... Read More

You can change the current count of an auto_increment in MySQL using ALTER command.The syntax is as follows −ALTER TABLE yourTableName AUTO_INCREMENT = IntegerValue;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table changeCurrentAutoIncrementValue    −> (    −> CurrentCount int auto_increment,    −> PRIMARY KEY(CurrentCount)    −> ); Query OK, 0 rows affected (0.60 sec)Insert records in the table using select statement. The auto_increment by default starts from 1 and increments by 1. The query to insert record is as follows −mysql> insert into changeCurrentAutoIncrementValue values(); Query ... Read More

To add days to a date, you can use DATE_ADD() function from MySQL. The syntax is as follows to add days to a date −INSERT INTO yourTableName VALUES(DATE_ADD(now(), interval n day));In the above syntax, you can use curdate() instead of now(). The curdate() will store only date while now() will store both date and time.Here is the demo of both the functions. To understand the above syntax, let us create a table.mysql> create table addingDaysDemo −> ( −> yourDateTime datetime −> ); Query OK, 0 rows affected (1.09 sec)Use both the ... Read More

Use the IS NOT NULL operator to compare with NULL values. The syntax is as follows −SELECT *FROM yourTableName where yourColumnName1 is not null or yourColumnName2 anyIntegerValue;To check the not equal to in presence of null, let us create a table. The query to create a table is as follows −mysql> create table IsNullDemo    −> (    −> ProductId int,    −> ProductName varchar(100),      −> ProductBackOrder int    −> ); Query OK, 0 rows affected (0.54 sec)Insert some records in the table with null value to avoid the presence of null. The query to insert records ... Read More

To get the first n characters of string with MySQL, use LEFT(). To get the last n char of string, the RIGHT() method is used in MySQL.The syntax for RIGHT() method is as follows −SELECT RIGHT(yourColumnName, valueOfN) as anyVariableName from yourTableName;To understand the above concept, let us create a table. The query to create a table is as follows −mysql> create table gettingLast5Characters    −> (    −> BookName varchar(100)    −> ); Query OK, 0 rows affected (0.73 sec)Now you can insert records in the table using insert command. The query is as follows −mysql> insert into gettingLast5Characters values('Introduction ... Read More

To get order by column and place empty records at the end, use ORDER By and “is null” from MySQL. The syntax is as follows −select *from yourTableName order by if(yourColumName = ’ ’ or yourColumName is null, 1, 0), yourColumnName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table emptyCellsAtEnd    −> (    −> ProductId varchar(100)    −> ); Query OK, 0 rows affected (0.65 sec)Insert some records in the table using insert command. Some of these records are empty. The query is as follows −mysql> ... Read More

We will see here how to display all tables inside a MySQL database using Java. You can use show command from MySQL to get all tables inside a MySQL database.Let’s say our database is ‘test’. The Java code is as follows to show all table names inside a database ‘test’.The Java code is as follows. Here, connection is established between MySQL and Java −import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import com.mysql.jdbc.Connection; import com.mysql.jdbc.DatabaseMetaData; public class GetAllTables {    public static void main(String[] args) throws SQLException {       Connection conn = null;       try { ... Read More

You can bulk update MySQL data with one query using CASE command. The syntax is as follows −update yourTableName set yourUpdateColumnName = ( Case yourConditionColumnName WHEN Value1 THEN ‘’UpdatedValue’ WHEN Value2 THEN ‘UpdatedValue’ . . N END) where yourConditionColumnName IN(Value1, Value2, .....N);To understand the above concept, let us create a table. The query to create a table is as follows −mysql> create table UpdateAllDemo −> ( −> BookId int, −> BookName varchar(200) −> ); Query OK, 0 rows affected (1.18 sec)Insert some records in the table using insert ... Read More

To select domain name from email address, you can use in-built SUBSTRING_INDEX() function from MySQL.To understand the concept, let us create a table. The following is the query to create a table.mysql> create table selectDomainNameOnly −> ( −> UserEmailAddress varchar(200) −> ); Query OK, 0 rows affected (0.52 sec)Insert records in the table using insert command. The record will have email-ids from which we need to fetch the domain name. The query is as follows −mysql> insert into selectDomainNameOnly values('John123@yahoo.com'); Query OK, 1 row affected (0.10 sec) mysql> insert into selectDomainNameOnly ... Read More