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)

The number 1 used in parenthesis is only for width display. The INT(1) and TINYINT(1) does not influence the storage.The TINYINT takes 1 byte that means it has range -128 to +127 while int takes 4 bytes; it has range -2147483648 to +2147483647To understand the width display, let us create a table −mysql> create table intAndTinyint    −> (    −> FirstNumber int(1) zerofill,    −> SecondNumber tinyint(1) zerofill    −> ); Query OK, 0 rows affected (0.52 sec)Now you can insert records in the table. The query is as follows −mysql> insert into intAndTinyint values(1, 1); Query OK, 1 ... Read More

You can get the first and last date of next month using date_add() function from MySQL.The syntax is as follows -select date_sub(    last_day(       date_add(now(), interval anyIntervalTime)    ),    interval day(       last_day(          date_add(now(), interval anyIntervalTime)       )    )-1 DAY ) as anyVariableName, last_day ( date_add(now(), anyIntervalTime) ) as anyVariableName;Implement the above syntax to get the first and last date of next month using interval 1 month in date_add() function. The query is as follows.mysql> select -> date_sub( ->    last_day( ->     ... Read More

To select multiple values, you can use where clause with OR and IN operator.The syntax is as follows −Case 1 − Using ORselect *from yourTablename where yourColumnName = value1 or yourColumnName = value2 or yourColumnName = value3, .........N;Case 2 − Using INselect *from yourTableName where yourColumnName IN(value1, value2, ....N);To understand the above syntax, let us create a table. The following is the query to create a table −mysql> create table selectMultipleValues −> ( −> BookId int, −> BookName varchar(200) −> ); Query OK, 0 rows affected (1.68 sec)Now you can insert some records in the table with the help of ... Read More