MySQLi Articles - Page 291 of 341

To make it understand we are using the data from the following tables −mysql> Select * from Customers; +-------------+----------+ | Customer_Id | Name     | +-------------+----------+ | 1           | Rahul    | | 2           | Yashpal  | | 3           | Gaurav   | | 4           | Virender | +-------------+----------+ 4 rows in set (0.00 sec) mysql> Select * from Reserve; +------+------------+ | ID   | Day        | +------+------------+ | 1    | 2017-12-30 | | ... Read More

To make it understand we are using the data from the following tables −mysql> Select * from customers; +-------------+----------+ | Customer_Id | Name     | +-------------+----------+ |           1 | Rahul    | |           2 | Yashpal  | |           3 | Gaurav   | |           4 | Virender | +-------------+----------+ 4 rows in set (0.00 sec) mysql> Select * from reserve; +------+------------+ | ID   | Day        | +------+------------+ |    1 | 2017-12-30 | | ... Read More

With the help of SHOW TABLES From Database_name query, we can see the tables of another database. Here Database_name is the name of the database which we are not using currently. Consider the following example in which we run the query for getting the list of tables in database name ‘tutorial’.mysql> show tables from tutorial; +--------------------+ | Tables_in_tutorial | +--------------------+ | employee           | | showzerofill       | | student            | +--------------------+ 3 rows in set (0.00 sec)

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.

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