You can use CASE statement for this. Let us first create a table −mysql> create table DemoTable    (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    Value1 int,    Value2 int    ); Query OK, 0 rows affected (0.76 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Value1, Value2) values(10, 5); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable(Value1, Value2) values(20, 0); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable(Value1, Value2) values(40, 10); Query OK, 1 row affected (0.28 sec) mysql> insert into DemoTable(Value1, Value2) ... Read More

Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Number int ); Query OK, 0 rows affected (0.64 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Number) values(10); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable(Number) values(50); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable(Number) values(100); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable(Number) values(190); Query OK, 1 row affected (0.12 sec)Display all ... Read More

Use MONTH() and YEAR() for this. Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, ShippingDate datetime ); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(ShippingDate) values('2019-01-21 10:40:21'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable(ShippingDate) values('2015-07-01 11:15:30'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable(ShippingDate) values('2012-12-31 10:45:56'); Query OK, 1 row affected (0.14 sec)Display all records from the table ... Read More

To order by word in MySQL, you need to use ORDER BY FIELD(). Let us first create a table −mysql> create table DemoTable (    StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    StudentFirstName varchar(20),    StudentFavouriteSubject varchar(100) ); Query OK, 0 rows affected (0.52 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(StudentFirstName, StudentFavouriteSubject) values('Larry', 'Java'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable(StudentFirstName, StudentFavouriteSubject) values('Sam', 'C'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable(StudentFirstName, StudentFavouriteSubject) values('Bob', 'MongoDB'); Query OK, 1 row affected (0.17 sec) mysql> ... Read More

Let us first create a Stored Procedure. Following is the query to create a stored procedure using MySQL Workbench.use business; DELIMITER // DROP PROCEDURE IF EXISTS SP_GETMESSAGE; CREATE PROCEDURE SP_GETMESSAGE() BEGIN DECLARE MESSAGE VARCHAR(100); SET MESSAGE="HELLO"; SELECT CONCAT(MESSAGE, ' ', 'MYSQL!!!!'); END // DELIMITER ;Here is the screenshot of stored procedure in MySQL workbench −You need to execute the above stored procedure with the help of below symbol shown in the screenshot −Now you can call the stored procedure with the help of CALL command.call SP_GETMESSAGE();The screenshot is as follows −Now again you can execute the above statement with the ... Read More

Removing Value from an enum in MySQL We can use the ALTER command to remove a value from an ENUM in MySQL. The ALTER database command lets you modify the type of a column, add or remove columns, or even modify ENUM values in a current database. You cannot delete a value from an ENUM type. To accomplish that task, you change the column definition - by changing the list of allowed values. This way, the structure of the table is not changed while the set of possible values for the ENUM column is changed. Example Let us first create ... Read More

The short is equivalent to MySQL’s small int. The Java short takes 2 bytes that has the range -32768 to 32767 while MySQL smallint also take 2 bytes with same range.Here is the demo code of short in Java −public class SmallIntAsShortDemo {    public static void main(String[] args) {       short value = 32767;       System.out.println(value);       value = -32768;       System.out.println(value);       // value = 32768;       // System.out.println(value);    } }The snapshot is as follows −This will produce the following output −32767 -32768Here is the snapshot of the output we ran in EclipseIDE −The MySQL smallint takes 2 bytes with same range.

You can use trim() for this.Let us first create a table −mysql> create table DemoTable    (    UserId varchar(100)    ); Query OK, 0 rows affected (0.63 sec)Insert some records in the table using insert command. Here, we have added a question mark (?) to the end of some of the strings −mysql> insert into DemoTable values('User123?'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('User777'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('User456'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('User133?'); Query OK, 1 ... Read More

You cannot use except in MySQL. You can work with NOT IN operator to get the same result. Let us first create a table −mysql> create table DemoTable1  (  Number1 int  ); Query OK,  0 rows affected (0.71 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values(100); Query OK,  1 row affected (0.14 sec) mysql> insert into DemoTable1 values(200); Query OK,  1 row affected (0.13 sec) mysql> insert into DemoTable1 values(300); Query OK,  1 row affected (0.13 sec)Display all records from the table using select statement:mysql> select *from DemoTable1This will produce the following output −+---------+ | Number1 | +---------+ | 100 | | 200 | | 300 | +---------+ 3 rows in set (0.00 sec)Following ... Read More

Both pow() and power() are synonyms. Let us see the syntax −select pow(yourValue1, yourValue2); OR select power(yourValue1, yourValue2);Now we will see some examples.Using pow()mysql> select POW(4, 3);This will produce the following output −+----------+ | POW(4, 3) | +----------+ | 64 | +----------+ 1 row in set (0.00 sec)Using power()mysql> select POWER(4, 3);This will produce the following output −+------------+ | POWER(4, 3) | +------------+ | 64 | +------------+ 1 row in set (0.00 sec)Let us first create a table and look into the above concept ... Read More