To achieve this, the following is the syntax.UPDATE yourTableName set yourColumnName=concat(ifnull(yourColumnName, ””), ’anyValue1, anyValue2, anyValue);To understand the above syntax, let us first create a table. The query to create a table is as follows -mysql> create table AppendDataDemo -> ( -> StudentId int, -> StudentName varchar(100), -> StudentAge int -> ); Query OK, 0 rows affected (1.54 sec)Insert some records in the table using insert command. The query is as follows.mysql> insert into AppendDataDemo values(101, 'John', 23); Query OK, 1 row affected (0.24 sec) mysql> insert into AppendDataDemo values(102, null, 24); Query OK, 1 row affected (0.74 sec) ... Read More

You can use LIKE with OR operator which works same as IN operator.Let us see the syntax for both the cases −Case 1 − Using Like with OR operator.select *from yourTableName where yourColumnName Like ‘Value1’ or yourColumnName Like ‘Value2’ or yourColumnName Like ‘Value3’ . . . NCase 2 − Using IN operator.select *from yourTableName where IN(value1, value2, value3, .....N);To understand both the syntaxes, let us create a table. The query to create a table is as follows −mysql> create table LikeDemo −> ( −> Id varchar(20) −> ); Query OK, 0 rows affected (0.58 sec)Now you can insert records in ... Read More

You can do this with the help of information_schema.tables. The syntax is as follows -SELECT TABLE_NAME, table_rows, data_length, index_length, round(((data_length + index_length) / 1024 / 1024), 2) "MB Size" FROM information_schema.TABLES WHERE table_schema = "yourDatabaseName" ORDER BY (data_length + index_length) ASC;To understand the above syntax, let us implement it for any database. Here, I am using database TEST. Let us see the query for our database TEST.mysql> SELECT TABLE_NAME, table_rows, data_length, index_length, -> round(((data_length + index_length) / 1024 / 1024), 2) "MB Size" -> FROM information_schema.TABLES WHERE table_schema = "test" -> ORDER BY (data_length + index_length) ASC;The following is the ... Read More

To avoid this type of error in MySQL stored procedure, you need to change the delimiter ; to //.Suppose if you are using stored procedure or triggers or even function then you need to change the delimiter. The syntax is as follows.DELIMITER // CREATE PROCEDURE yourProcedureName() BEGIN Statement1, . . N END; // DELIMITER ;To understand the above syntax, let us create a stored procedure. The query to create a stored procedure is ... Read More

To list non-empty tables in MySQL database, use “information_schema.tables”. The following is the query for all database tables −mysql> select table_type, table_name from information_schema.tables    −> where table_rows >= 1;Above, we have considered only the table that have 1 or more than 1 rows i.e. non-empty table.The following is the output −+------------+------------------------------------------------------+ | TABLE_TYPE | TABLE_NAME                                           | +------------+------------------------------------------------------+ | BASE TABLE | innodb_table_stats                                 ... Read More

To re-order columns in MySQL, use the ALTER TABLE MODIFY COLUMN. The syntax is as follows -ALTER TABLE yourTableName MODIFY COLUMN yourColumnName data type after yourColumnName.To understand the above syntax, let us first create a table. The query to create a table is as follows.mysql> create table reOrderColumn -> ( -> ProductId int, -> DeliveryDate datetime, -> ProductName varchar(100) -> ); Query OK, 0 rows affected (0.76 sec)Now check the description of the table. The query is as follows.mysql> desc reOrderColumn;The following is the output.+--------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+-------+ | ... Read More

To substring a MySQL table column, use the in-built SUBSTR() function from MySQL. The syntax is as follows −select substr(yourColumnName, AnyValue) as anyVariableName from yourTableName;To understand the function substr(), let us create a table. The query to create a table is as follows −mysql> create table SubStringDemo −> ( −> UserId varchar(200) −> ); Query OK, 0 rows affected (0.55 sec)Now insert some records in the table. The query to insert records is as follows −mysql> insert into SubStringDemo values('Bob10015'); Query OK, 1 row affected (0.29 sec) mysql> insert into ... Read More

Alter multiple columns in a single statement with the help of CHANGE command. The syntax is as follows −alter table yourTableName change yourOldColumnName1 yourNewColumnName1 dataType, yourOldColumnName2 yourNewColumnName2 dataType, . . . NTo understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table AlterMultipleColumns −> ( −> Id int, −> Name varchar(200) −> ); Query OK, 0 rows affected (0.93 sec)Now we have two columns with Id and Name. We will alter both the columns.Here, we will alter ... Read More

The maximum value of SMALLINT(6) unsigned in MySQL is 65535. The number 6 does not affect the actual range. It can only be used to display width on the command line.The Minimum Value signed is-32768The Maximum Value unsigned is65535The Maximum value signed is32767Let us understand this with zerofill and create a table using the following query.mysql> create table smallIntDemo -> ( -> FirstNumber smallint(6) zerofill -> ); Query OK, 0 rows affected (1.95 sec)Now you can insert records in the table using insert command. Whenever you insert beyond the range 65535, it will not insert in the table, since this ... Read More

Select an empty result set with the help of dummy table ‘dual’ from MySQL. The query is as follows −mysql> select 1 from dual where false; Empty set (0.00 sec)In the above query, “dual” is a dummy table and the above condition false. Therefore, it returns empty set.Let us check with true condition. It will return the selected value. The query is as follows −mysql> select 1 from dual where true;The following is the output −+---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec)