To select the top 2 rows from each group, use the where condition with subquery. Let us create a table. The query to create a table is as follows:mysql> create table selectTop2FromEachGroup    -> (    -> Name varchar(20),    -> TotalScores int    -> ); Query OK, 0 rows affected (0.80 sec)Now insert some records in the table using insert command. The query is as follows:mysql> insert into selectTop2FromEachGroup values('John', 32); Query OK, 1 row affected (0.38 sec) mysql> insert into selectTop2FromEachGroup values('John', 33); Query OK, 1 row affected (0.21 sec) mysql> insert into selectTop2FromEachGroup values('John', 34); Query OK, ... Read More

To add a new column that counts the number of rows as serial number, you can use the global variable in select statement.Let us create a table. The query to create a table is as follows:mysql> create table addColumnToCountAsSerialNumber    -> (    -> Id int,    -> Name varchar(20),    -> Age int,    -> Salary int    -> ); Query OK, 0 rows affected (0.80 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert into addColumnToCountAsSerialNumber values(10, 'John', 23, 8576); Query OK, 1 row affected (0.10 sec) mysql> insert into addColumnToCountAsSerialNumber values(12, ... Read More

The equivalent of SQL Server function SCOPE_IDENTITY() is equal to LAST_INSERT_ID() in MySQL. The syntax is as follows:SELECT LAST_INSERT_ID().This returns the id of last inserted record.Here, I am going to create a table with primary key column. The following is the demo of last_insert_id().First, let us create two tables. The query to create the first table table is as follows:mysql> create table TestOnLastInsertIdDemo    -> (    -> StudentId int NOT NULL AUTO_INCREMENT,    -> PRIMARY KEY(StudentId)    -> ); Query OK, 0 rows affected (0.95 sec)Now creating the second table. The query is as follows:mysql> create table TestOnLastInsertIdDemo2   ... Read More

To match only one character in MySQL, you can use underscore(_) in place of %. The syntax is as follows:SELECT *FROM yourTableName WHERE yourColumnName LIKE ‘yourString_’;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table OneCharactermatchDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> PassoutYear year,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.76 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert into OneCharactermatchDemo(PassoutYear) values('2008'); Query OK, 1 row affected (0.14 sec) mysql> ... Read More

You need to use CAST() or CONVERT() function to convert output of MySQL query to UTF8. Here, I am using MySQL version 8.0.12. Let us first check the version:mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.12 | +-----------+ 1 row in set (0.00 sec)In this if you use utf8 then you will get warning of aliases because it has utf8mb4. Therefore, you can avoid the warning by placing utf8mb4.Note: Never use UTF8. For current versions, use UTF8MB4Here is the syntax to convert output of MySQL query to UTF8:SELECT yourColumnName1, convert(yourColumnName2 USING utf8) as anyVariableName FROM ... Read More

There is a single way by which you can set a global event_scheduler=ON even if MySQL is restarted. You need to set global system variable ON and need to use this system variable even if MySQL restart.For this, I am using system variable @@event_scheduler using select statement. The query is as follows:mysql> select @@event_scheduler;The following is the output:+-------------------+ | @@event_scheduler | +-------------------+ | ON | +-------------------+ 1 row in set (0.00 sec)Now, restart MySQL. The query is as follows:mysql> restart; Query OK, 0 rows affected ... Read More

You need to use LIKE with OR operator to find all the names that starts with a or b or c. The syntax is as follows:SELECT *FROM yourTableName WHERE yourColumnName like 'A%' or yourColumnName like 'B%' or yourColumnName like 'C%';The above query finds all names that starts only with the letter ‘a’ or ‘b’ or ‘c’. To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table AllNamesStartWithAorBorC    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> EmployeeName varchar(20),    -> PRIMARY KEY(Id)    -> ); Query ... Read More

NOTE: There is only one difference between AND and && is that AND is a standard while && is ownership syntax.Except the above statement, there is no difference between AND and &&. Let us look at all the conditions.The result of AND and && will always be either 1 or 0. As we know the AND and && both are logical operators, if there are more than one operand and any one of them has value 0 then result becomes 0 otherwise 1.Here is the demo of AND and &&.Case 1(a): If both operands are 1. Using AND.The query is ... Read More

NULL and empty string ‘ ’ both aren’t similar in MySQL. To check the field is empty like ‘ ’or is null, you need to use the IS NULL property or something else. You can check all the conditions with CASE statement. The syntax is as follows:SELECT *, CASE WHEN yourColumnName =’ ‘ THEN ‘yourMessage1’ WHEN yourColumnName IS NULL THEN ‘yourMessage2’ ELSE CONCAT(yourColumnName ,' is a Name') END 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 checkFieldDemo    -> (    -> Id int ... Read More

In order to see the global locks in MySQL (Innodb), use the SHOW command. The below query shows the global locks as well owner of locks and waiters. The following query will also show transaction id and more related to Innodb.The query is as follows:mysql> SHOW ENGINE INNODB STATUS\GThe following is the output:*************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2019-01-23 14:46:58 0x2914 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 23 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 87 srv_active, 0 srv_shutdown, 51953 srv_idle srv_master_thread log flush and writes: 0 ---------- SEMAPHORES ---------- OS WAIT ... Read More