MySQLi Articles - Page 292 of 341

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

If a subquery is nested inside another subquery then it is called a nested subquery. To make it understand we are creating the nested subquery from the following tables data −mysql> Select * from Cars; +------+--------------+---------+ | ID   | Name         | Price   | +------+--------------+---------+ |    1 | Nexa         | 750000  | |    2 | Maruti Swift | 450000  | |    3 | BMW          | 4450000 | |    4 | VOLVO        | 2250000 | |    5 | Alto   ... Read More

If a subquery, used with EXIST operator, returns NULL, the expression EXIST NULL returns TRUE and MySQL returns the result based on an outer query. It can be understood with the help of simple example using the following data from table ‘Customers’ −mysql> Select * from Customers; +-------------+----------+ | Customer_Id | Name     | +-------------+----------+ | 1           | Rahul    | | 2           | Yashpal  | | 3           | Gaurav   | | 4           | Virender | +-------------+----------+ 4 rows in ... Read More

We can use MySQL EXIST operator to test for the existence of a record in the subquery. In other words, we can say that EXIST operator checks if a subquery returns any rows. The syntax of using EXIST operator with MySQL subquery is as follows −SyntaxWHERE EXISTS (Subquery)The above EXIST (subquery) expression returns TRUE if the subquery returns at least one row, otherwise it returns false.ExampleTo make it understand we are using the data from the following tables −mysql> Select * from Customers; +-------------+----------+ | Customer_Id | Name     | +-------------+----------+ | 1           | ... Read More

We can write multiple-line statements because MySQL determines the end of a statement by looking for the termination semicolon, not by looking for the end of the input line.Examplemysql> Select *     -> from     -> stock_item; +------------+-------+----------+ | item_name  | Value | Quantity | +------------+-------+----------+ | Calculator | 15    | 89      | | Notebooks  | 63    | 40      | | Pencil     | 15    | 40      | | Pens       | 65    | 32      | | Shirts     | 13   ... Read More

For creating a table with such kinds of names we must have to use quote character. The quotes can be single or double depends upon ANSI_QUOTES SQL mode. If this mode is disabled then the identifier quote character is the backtick (“`”). Consider the following example in which we created a table named ‘select’ −mysql> Create table `a^b`(`a^b` int); Query OK, 0 rows affected (0.19 sec) mysql> Create table "a^g"("a^g" int); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"a^g" ("a^g" ... Read More

It can be done with the help of ALTER TABLE command of MySQL. Consider the table ‘Student’ in which the size of ‘Grade’ column is declared as Varchar(10), can be seen from the following query −mysql> DESCRIBE Student; +--------+-------------+------+-----+---------+-------+ | Field  | Type        | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | Name   | varchar(20) | YES  |     | NULL    |       | | RollNo | int(11)     | YES  |     | NULL    |       | | Grade  | varchar(10) | YES  | ... Read More

We can sort multiple columns in a single query by giving more than one column name with ORDER BY Clause. The syntax of the above is as follows −SyntaxSelect Col1, Col2, … from table_name ORDER BY Col1, Col2, …ExampleSuppose we want to sort the table named ‘Student’ by columns ‘Name’ and ‘RollNo’ both then we can write the single query for this as follows −mysql> Select Name, RollNo from student order by name, rollno; +--------+--------+ | name   | rollno | +--------+--------+ | Aarav  |    150 | | Aryan  |    165 | | Gaurav |    100 | ... Read More

Followings are the synonyms statements of MySQL DESCRIBE i.e. the statements with the help of which we can get the same kind of information/structure of the table as we get from DESCRIBE −EXPLAIN StatementEXPLAIN is the synonym of the DESCRIBE statement. Its syntax is also similar to the DESCRIBE statement. Consider the following example −mysql> Explain Employee; +-------+-------------+------+-----+---------+------------------+ | Field | Type        | Null | Key | Default | Extra            | +-------+-------------+------+-----+---------+------------------+ | ID    | int(11)     | NO   | PRI | NULL    | auto_increment   | | ... Read More