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

You cannot use from as a column name directly because from is a reserved word in MySQL. To avoid this, you need to use backtick symbol. Let us first create a table −mysql> create table DemoTable    (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    `from` varchar(100),    Name varchar(10)    ); Query OK, 0 rows affected (0.92 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(`from`, Name) values('US', 'John'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable(`from`, Name) values('UK', 'Carol'); Query OK, 1 row affected (0.14 sec) mysql> ... Read More

The SQL TRUNCATE statement is used to delete all the records from a table.SyntaxTRUNCATE TABLE table_name;To delete all the records from a table from using JDBC API you need to −Register the Driver: Register the driver class using the registerDriver() method of the DriverManager class. Pass the driver class name to it, as parameter.Establish a connection: Connect to the database using the getConnection() method of the DriverManager class. Passing URL (String), username (String), password (String) as parameters to it.Create Statement: Create a Statement object using the createStatement() method of the Connection interface.Execute the Query: Execute the query using the execute() method of the Statement interface.Following JDBC program ... Read More

Stored procedures are sub routines, segment of SQL statements which are stored in SQL catalog. All the applications that can access Relational databases (Java, Python, PHP etc.), can access stored procedures.Stored procedures contain IN and OUT parameters or both. They may return result sets in case you use SELECT statements. Stored procedures can return multiple result sets.To create a stored procedure in (MySQL) a database using JDBC API you need to −Register the Driver: Register the driver class using the registerDriver() method of the DriverManager class. Pass the driver class name to it, as parameter.Establish a connection: Connect to the database using the getConnection() method ... Read More