MySQL Articles - Page 366 of 439

The restriction on MySQL query having a notable list is that it can return, as a result, exactly one row but that result can contain multiple columns.Examplemysql> Select 65/NULL,65+NULL,65*NULL,65-NULL,65%NULL; +------------+--------------+-------------+-------------+---------+ | 65/NULL    | 65+NULL      | 65*NULL     | 65-NULL     | 65%NULL | +------------+--------------+-------------+-------------+---------+ |       NULL |         NULL |        NULL |        NULL |    NULL | +------------+--------------+-------------+-------------+---------+ 1 row in set (0.00 sec)In the above example, we can see that MySQL returns only one row with five columns, having the result of five expressions, as a result when we do not have any table list in the statement.

It is very rarely used to use two columns of the same table in WHERE clause but still we can perform a query with two columns of the same table. Consider the below example −mysql> Select F_name, L_name     -> From Customer     -> where F_name = L_name;     Empty set (0.00 sec)Here we are using both the columns(F_Name and L_Name) from the same table(Customer) hence the result is an Empty set.

As we know that a NULL is no value and it is not the same as zero. MySQL represents a database column as NULL if it does not contain any data. Now, if we will use NULL in any arithmetic expression then the result will be NULL also.Examplemysql> Select 65/NULL, 65+NULL, 65*NULL, 65-NULL, 65%NULL; +---------+---------+---------+---------+---------+ | 65/NULL | 65+NULL | 65*NULL | 65-NULL | 65%NULL | +---------+---------+---------+---------+---------+ |    NULL |    NULL |    NULL |    NULL |    NULL | +---------+---------+---------+---------+---------+ 1 row in set (0.00 sec)From the above example, it can be observed that if we ... Read More

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.

With the use of ego, \G option at end of a statement, we can get the result set in vertical format. Consider the following example −mysql> Select * from Student where name = 'Aarav'\G *************************** 1. row ***************************   Name: Aarav RollNo: 150  Grade: M.SC 1 row in set (0.00 sec)

MySQL determines the end of a statement when it encounters any one of the followings −Semicolon(;)Generally, MySQL determines the end of the statement, single-line or multiple-line, when it encounters the termination semicolon(;). Consider the examples below, mysql> Select * from employee; (Single line statement) mysql> Select *     -> from     -> employee; (Multiple line statement)In both cases, MySQL returns the result set after encountering the semicolon, which means the end of the statement.\G option\G option means to send the current state to the server to be executed and display the result in a vertical format. When we ... Read More

With the help of \G or \g option just at the end of the MySQL statement, we can run it without the semicolon. Consider the example below −mysql> Select * from Stock_item\G *************************** 1. row *************************** item_name: Calculator     Value: 15  Quantity: 89 *************************** 2. row *************************** item_name: Notebooks     Value: 63  Quantity: 40 *************************** 3. row *************************** item_name: Pencil     Value: 15  Quantity: 40 *************************** 4. row *************************** item_name: Pens   Value : 65 Quantity: 32 *************************** 5. row *************************** item_name: Shirts     Value: 13  Quantity: 29 *************************** 6. row *************************** item_name: Shoes     ... Read More

As we know that after writing the first line of multiple-line queries, MySQL changes the prompt. The following table shows different MySQL prompts and it's meaning −PromptMeaning         mysql>It means MySQL is ready for a new command. →It means that MySQL is waiting for the next line of multiple-line command. ‘>It means that MySQL is waiting for the next line, waiting for the completion of a string that began with a single quote. “>It means that MySQL is waiting for the next line, waiting for the completion of a string that began with a double quote. `>It means that MySQL is waiting ... Read More

After writing the first line of multiple-line queries, MySQL changes promptly from ‘mysql>’ to ‘→’. It is significant because with the help of it we got an indication that MySQL has not seen a complete statement yet and is waiting for the rest. Consider the example below,mysql> Select *     -> from     -> stock_item;We know that after writing the first line i.e. ‘Select *’ Mysql changes its prompts which means that yet the state has not been completed. After the semicolon, MySQL considers the statement completed and throws the output.

Single Row Sub QueryA single-row subquery is used when the outer query's results are based on a single, unknown value. Although this query type is formally called "single-row, " the name implies that the query returns multiple columns-but only one row of results. However, a single-row subquery can return only one row of results consisting of only one column to the outer query.In the below SELECT query, inner MySQL returns only one row i.e. the minimum salary for the company. It, in turn, uses this value to compare the salary of all the employees and displays only those, whose salary ... Read More