Found 4219 Articles for MySQLi

A subquery that contains a reference to a table that also appears in the outer query is called a correlated subquery. In this case,  MySQL evaluates from inner query to the outer query. To understand it we are having the following data from table ‘cars’ −mysql> Select * from Cars; +------+--------------+---------+ | ID   | Name         | Price   | +------+--------------+---------+ | 1    | Nexa         | 750000  | | 2    | Maruti Swift | 450000  | | 3    | BMW          | 4450000 | | 4 ... Read More

The subquery can return at most one value. The value can be the result of an arithmetic expression or a column function. MySQL then compares the value that results from the subquery with the value on the other side of the comparison operator. MySQL subquery can be used before or after any of the comparison operators like =, >, >=,

As we know that DESCRIBE statement will provide the information/structure of the whole table. With the help of DESCRIBE statement along with table name and the column name, we can get the information about that column.SyntaxDESCRIBE table_name col_name;Example1mysql> Describe employee ID; +-------+---------+------+-----+---------+----------------+ | Field | Type    | Null | Key | Default | Extra          | +-------+---------+------+-----+---------+----------------+ | ID    | int(11) | NO   | PRI | NULL    | auto_increment | +-------+---------+------+-----+---------+----------------+ 1 row in set (0.11 sec)The query above will give the information about the column ‘ID’ of a table named ‘employee’.Example2mysql> Describe ... Read More

The DESCRIBE statement gives information about MySQL table’s structure.ExampleConsider the constructing of the following table name ‘Employee’ with Create Table statement as follows −mysql> Create table Employee(ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, Name Varchar(20)); Query OK, 0 rows affected (0.20 sec)Now with the help of ‘DESCRIBE Employee‘ statement, we can get the information about the employee table.mysql> Describe Employee; +-------+-------------+------+-----+---------+------------------+ | Field | Type        | Null | Key | Default | Extra            | +-------+-------------+------+-----+---------+------------------+ | ID    | int(11)     | NO   | PRI | NULL    | auto_increment ... Read More

The default sort order in MySQL tables is ascending. Whenever we use ORDER BY clause to sort out the rows of a table, MySQL gives output in ascending order, with the smallest value first. Consider the following example from a table named ‘student’ −mysql> Select * from student order by name; +--------+--------+--------+ | Name   | RollNo | Grade  | +--------+--------+--------+ | Aarav  | 150    | M.SC   | | Aryan  | 165    | M.tech | | Gaurav | 100    | B.tech | +--------+--------+--------+ 3 rows in set (0.00 sec)We can see the query above gives the output, ordered by name, in ascending order, start with smallest value ‘Name’ column.

It can be understood with the help of an example in which we would copy the values of a table into other table. We are using the data from table ‘cars’ and copy its data to table ‘copy_cars’ −mysql> CREATE TABLE copy_cars LIKE cars; Query OK, 0 rows affected (0.86 sec) mysql> SELECT * from copy_cars; Empty set (0.08 sec)The following query using the subquery will insert the values same as ‘cars’ to table ‘copy_cars’ −mysql> INSERT INTO Copy_cars Select * from Cars; Query OK, 8 rows affected (0.07 sec) mysql> SELECT * from copy_cars; +------+--------------+---------+ | ID ... Read More

For sorting the rows in a meaningful way we can use ORDER BY clause. Suppose we want to sort the rows of the following table −mysql> Select * from Student; +--------+--------+--------+ | Name   | RollNo | Grade  | +--------+--------+--------+ | Gaurav |    100 | B.tech | | Aarav  |    150 | M.SC   | | Aryan  |    165 | M.tech | +--------+--------+--------+ 3 rows in set (0.00 sec)The query below sorted the table by ‘Name’.mysql> Select * from student order by name; +--------+--------+--------+ | Name   | RollNo | Grade  | +--------+--------+--------+ | Aarav  |   ... Read More

For combining ROW selection with COLUMN selection, we can use the ‘WHERE’ clause. For example, we have a table below −mysql> Select * from Student; +--------+--------+--------+ | Name   | RollNo | Grade  | +--------+--------+--------+ | Gaurav | 100    | B.tech | | Aarav  | 150    | M.SC   | | Aryan  | 165    | M.tech | +--------+--------+--------+ 3 rows in set (0.00 sec)Now, the following query will show how we can combine ROW selection with COLUMN selection using WHERE clause.mysql> Select Name, RollNo, Grade from Student where Grade='M.Sc' or Grade='B.Tech'; +--------+--------+--------+ | Name   | RollNo ... Read More

LOAD DATAThis statement is used for importing the data from data files into our database. It reads data records directly from a file and inserts them into a table. Its syntax would be as follows −SyntaxLOAD DATA LOCAL INFILE '[path/][file_name]' INTO TABLE [table_name ];Here, a path is the address of the file.file_name is the name of the .txt filetable_name is the table where the data will be loaded.To illustrate the concept we are having the following data, separated by tab, in ‘A.txt’ whose path is d:/A.txt −100 John USA 10000 101 Paul UK 12000 102 Henry NZ 11000 103 Rick ... Read More

Suppose we have the following table named stock_item in which the column quantity is having duplicate values i.e. for item name ‘Notebooks’ and ‘Pencil’, the column ‘Quantity’ is having duplicate values ‘40’ and for items ‘Shirts’, ‘Shoes’ and ‘Trousers’ triplicate value 29 is hold by column ‘quantity’ as shown in the table.mysql> Select * from stock_item; +------------+----------+ | item_name  |quantity  | +------------+----------+ | Calculator | 89       | | Notebooks  | 40       | | Pencil     | 40       | | Pens       | 32       | | ... Read More