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.

The current transaction will be committed and ended if START TRANSACTION is executed in the middle of a current transaction. All the database changes made in the current transaction will be made permanent. This is called an implicit commit by a START TRANSACTION command.ExampleSuppose we have the following values in table ‘marks’mysql> select * from marks; +------+---------+-----------+-------+ | Id   | Name    | Subject   | Marks | +------+---------+-----------+-------+ | 1    | Aarav   | Maths     | 50    | | 1    | Harshit | Maths     | 55    | | 3   ... Read More

The current MySQL transaction will be committed and ended when any of the DDL statement such as CREATE or DROP databases, Create, ALTER or DROP tables or stored routines is executed in the middle of the current transaction. All the database changes made in the current transaction will be made permanent and cannot be rolled back.Examplemysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO MARKS Values(6, 'Manak', 'History', 70); Query OK, 1 row affected (0.26 sec) mysql> Create table student(id int, Name Varchar(10), ); Query OK, 0 rows affected (0.84 sec)As we can see ... Read More