Actually ‘FOR EACH ROW’ means for each of the matched rows that get either updated or deleted. In other words, we can say that trigger is not applied to each row, it just says to execute the trigger body for each affected table row. We can illustrate this by the following example −ExampleIn this example, we are creating two tables, Sample and Sample_rowaffected, as follows −mysql> Create table Sample(id int, value varchar(20)); Query OK, 0 rows affected (0.47 sec) mysql> Insert into Sample(id, value) values(100, 'same'), (101, 'Different'), (500, 'excellent'), (501, 'temporary'); Query OK, 4 rows affected (0.04 sec) ... Read More

‘\c’ option means ‘clear’ and is used to clear the current input. Suppose if we do not want to execute a command that we are entering, then we can use a clear \c option which clears the current input. For example, the use of \c option can be done as follows −mysql> Select *     -> from\cIn the example above, when we use \c in a statement, MySQL clears the current input and returns back to the MySQL prompt for accepting other statements.

Actually, MySQL determines the end of the statement when it got termination semicolon. Suppose if we are writing a single statement in different lines then after writing the first line, MySQL changes promptly from ‘mysql>’ to ‘->’ which indicates that MySQL has not seen a complete statement yet and is waiting for the rest. When it got semicolon then MySQL executes the statement. It can be understood with the help of the following example −Examplemysql> Select Id, Name     -> From     -> Student_info     -> ; +------+---------+ | Id   | Name    | +------+---------+ ... Read More

FROM clause after SELECT shows the references to a table. But if there is no reference to any table then we can use SELECT without the FROM clause. In other words, we can say that SELECT can be used to retrieve rows computed without reference to any table. Consider the following statements −mysql> Select concat_ws(" ","Hello", "World"); +---------------------------------+ | concat_ws(" ","Hello", "World") | +---------------------------------+ | Hello World                     | +---------------------------------+ 1 row in set (0.00 sec) mysql> Set @var1=100; Query OK, 0 rows affected (0.00 sec) mysql> Select @var1; +-------+ | @var1 | +-------+ |   100 | +-------+ 1 row in set (0.00 sec)

As we know that a query can have multiple MySQL statements followed by a semicolon. Suppose if we want to get the result from multiple tables then consider the following example to get the result set from ‘Student_info’ and ‘Student_detail’ by writing a single query −mysql> Select Name, Address from Student_info; Select Studentid, Address from Student_detail; +---------+------------+ | Name    | Address    | +---------+------------+ | YashPal | Amritsar   | | Gaurav  | Chandigarh | | Raman   | Shimla     | | Ram     | Jhansi     | | Shyam   | Chandigarh | | ... Read More

The representation of an empty string in result set depends on data type when we insert an empty string into a MySQL column which is declared as NOT NULL. As we know that on inserting empty string we are providing value to MySQL that has integer representation as INT 0.Now, if that column is having INTEGER data type then MySQL would show 0 in the result set as that empty string has been mapped to zero as an integer.Examplemysql> create table test(id int NOT NULL, Name Varchar(10)); Query OK, 0 rows affected (0.19 sec) mysql> Insert into test(id, name) ... Read More

By executing SELECT @@TX_ISOLATION command we can check the current MySQL transaction isolation level.Examplemysql> SELECT @@TX_ISOLATION; +-----------------+ | @@TX_ISOLATION  | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec)

It is because inserting an empty string means that we are inserting some value and not NULL. The empty string apparently maps to zero as an integer. In other words, we can say that by inserting empty string we are providing a value to MySQL that has integer representation as INT 0. Consider the following example in which we inserted an empty string and it mapped to 0 by MySQL.mysql> create table test(id int NOT NULL, Name Varchar(10)); Query OK, 0 rows affected (0.19 sec) mysql> Insert into test(id, name) values('1', 'Gaurav'), ('0', 'Rahul'), ('', 'Aarav'); Query OK, 3 ... Read More

Declaring a column ‘NOT NULL’ means that this column would not accept NULL values but zero (0) and an empty string is itself a value. Hence there would be no issue if we want to insert zero or an empty string into a MySQL column which is defined as NOT NULL. Following comparisons of 0 and empty string with NULL would make it clear −mysql> Select 0 IS NULL, 0 IS NOT NULL; +-----------+---------------+ | 0 IS NULL | 0 IS NOT NULL | +-----------+---------------+ |         0 |             1 | ... Read More

The reason behind it is that we will not receive any meaningful results from the comparisons when we use NULL with the comparison operators like ‘=’, ‘ Select 10 = NULL, 10< NULL, 10NULL; +-----------+----------+----------+ | 10 = NULL | 10< NULL | 10NULL | +-----------+----------+----------+ |      NULL |     NULL |     NULL | +-----------+----------+----------+ 1 row in set (0.07 sec)The above result set is not meaningful in any sense.