No, definer part is not compulsory when you are creating a stored procedure. It is used when you want to create a definer.Check all the user and host from the MySQL.user table −mysql> select user, host from mysql.user;The following is the output −+------------------+-----------+ | user             | host      | +------------------+-----------+ | Manish           | %         | | User2            | %         | | mysql.infoschema | %         | | mysql.session    | %   ... Read More

You need to use cast() method to perform comparison on an INT field. The syntax is as follows −SELECT yourColumnName1, yourColumnName2, ......N yourTableName WHERE CAST(yourColumnName as CHAR) LIKE ‘%yourIntegerValue%’;To understand the above syntax, let us create a table. The following is the query to create a table for performing a LIKE comparison on INT field −mysql> create table ComparisonOnIntField    -> (    -> StudentId int NOT NULL,    -> StudentName varchar(20),    -> StudentAge int    -> ); Query OK, 0 rows affected (1.00 sec)Insert some records in the table to perform a MySQL LIKE comparison on an INT ... Read More

To query a list of values, you can use IN operator. The syntax is as follows −SELECT * FROM yourTableName WHERE yourColumnName IN(Value1, Value2, ...N) ORDER BY FIELD(yourColumnName, Value1, Value2, ...N);To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table ListOfValues    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(30),    -> Age int,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.72 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert ... Read More

You can add static value when you use INSERT INTO SELECT MySQL query. Write the value directly in the select statement or you can add with the help of variable which initializes the value.Case 1 − Place the value directly in the INSERT INTO SELECT statement. The syntax is as follows −INSERT INTO yourSecondTableName(yourColumnName1, yourColumnName2, ....N)    SELECT yourColumnName1 ,yourColumnName2, .....N, yourStaticValue from yourFirstTableName;Case 2 − Add using variable. The syntax is as follows −SET @yourVariableName − = yourstaticValue;    INSERT INTO yourSecondTableName(yourColumnName1, yourColumnName2, ....N)    SELECT yourColumnName1 ,yourColumnName2, .....N, @yourVariableName from yourFirstTableName;To understand the above syntax, you need to ... Read More

You can use ORDER BY RIGHT() function to order by last 3 chars in MySQL. The syntax is as follows −SELECT *FROM yourTableName ORDER BY RIGHT(yourColumnName, 3) yourSortingOrder;Just replace the ‘yourSortingOrder’ to ASC or DESC to set the ascending or descending order respectively.To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table OrderByLast3Chars    -> (    -> EmployeeId int NOT NULL AUTO_INCREMENT,    -> EmployeeName varchar(20),    -> EmployeeAge int,    -> PRIMARY KEY(EmployeeId)    -> ); Query OK, 0 rows affected (0.56 sec)Insert some records in ... Read More

You can quote values using concat() and grop_concat() function from MySQL. The syntax is as follows −SELECT GROUP_CONCAT(CONCAT(' '' ', yourColumnName, ' '' ' )) as anyVariableName from yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table Group_ConcatDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,      -> Value int,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (1.56 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into Group_ConcatDemo(Value) ... Read More

You can pass a variable to a MySQL script using session variable. First you need to set a session variable using SET command. After that you need to pass that variable to a MySQL script.The syntax is as follows −First Step: Use of Set command.SET @anyVariableName − = ’yourValue’;Second Step: Pass a variable to a MySQL script.UPDATE yourTableName SET yourColumnName1 = yourColumnName1+integerValue WHERE yourColumnName2 = @anyVariableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table Employee_Information    -> (    -> EmployeeId int NOT NULL AUTO_INCREMENT,    -> ... Read More

You can insert sequential number in MySQL using session variable. The syntax is as follows −SELECT @anyVariableName − = anyIntegerValue; UPDATE yourTableName SET yourColumnName = @anyVariableName − = @anyVariableName+IncrementStep;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table SequentialNumberDemo    -> (    -> SequentialNumber int not null    -> ); Query OK, 0 rows affected (0.84 sec)Insert records in the table using insert command. The query is as follows −mysql> insert into SequentialNumberDemo values(100); Query OK, 1 row affected (0.11 sec) mysql> insert into SequentialNumberDemo values(10); ... Read More

You can use below syntax to sum values of a single row −Case 1 − The following is the syntax if your column does not have NULL value −SELECT yourColumnName1+yourColumnName2+yourColumnName3+.......+N as anyVariableName FROM yourTableName;Case 2 − If your column has NULL value then use this syntax −SELECT IFNULL(yourColumnName1, 0)+ IFNULL(yourColumnName2, 0)+ IFNULL(yourColumnName3, 0)+.............+N AS anyVariableName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table SumValueOfSingleRow    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> FirstValue int,    -> SecondValue int,    -> ThirdValue ... Read More

Yes, you can use if() function from MySQL to reverse a boolean field. The syntax is as follows −UPDATE yourTableName SET yourBooleanColumnName = IF(yourBooleanColumnName, 0, 1);To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table ReverseBooleanField    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> isValid BOOLEAN    -> ,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.56 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into ReverseBooleanField(isValid) values(true); ... Read More