MySQL SELECT

SELECT statement is used to get data from a MySQL database. You can use this command at the mysql> command prompt as well as in any script such as PHP.
 

Syntax

Here is the syntax of SELECT statement to extract data from a MySQL table.

SELECT field1, field2,...fieldN FROM table1, table2... [WHERE Clause][OFFSET M ][LIMIT N]

• You can get one or more fields in a single SELECT statement.
• You can specify an asteristic symbol (*) to return all the fields.
• You can specify any condition using the WHERE clause.
• You can specify an offset using OFFSET at which SELECT will start returning records. By default, the offset starts at zero.
• You can limit the number of rows using the LIMIT attribute.

 

Example 1:

The following example returns all records from “Persons” table:

mysql> SELECT * from Persons;

Output:

+----------+-----------+--------+-------------------------------+
| PersonID |    Name   |  age   |             Address           |
+----------+-----------+--------+-------------------------------+
|    101   |    Alex   |   25   | 819 Saint Francis Way         |
|    102   |   Emily   |   15   | 171 Jarvisville Road Michigan |
|    103   |   Jean    |   35   | 188 Clay Street Indiana       |
|    104   |    Bob    |   40   | 285 Java Lane Missouri        |
+----------+-----------+--------+-------------------------------+

 

Example 2:

In the following example, we have used * to indicate that we want to select all fields in “Persons” table where the age of the person is greater than or equal to 20. The result is sorted by age in descending order.

SELECT *
FROM Persons
WHERE Age >= 20
ORDER BY Age DESC;

Output:

+----------+-----------+--------+-------------------------------+
| PersonID |    Name   |  age   |             Address           |
+----------+-----------+--------+-------------------------------+
|    104   |    Bob    |   40   | 285 Java Lane Missouri        |
|    103   |   Jean    |   35   | 188 Clay Street Indiana       |
|    101   |    Alex   |   25   | 819 Saint Francis Way         |
+----------+-----------+--------+-------------------------------+

 

Example 3:

This example returns only the ‘PersonID’ and ‘Name’ fields of the “Persons” table where the age is less than 30. The results are sorted by age in ascending order, then by name in descending order.

SELECT PersonID, Nom
FROM Persons
WHERE Age < 30
ORDER BY Age ASC, Name DESC;

Output:

+----------+-----------+
| PersonID |    Name   |
+----------+-----------+
|    103   |    Jean   |
|    104   |    Bob    |
|    101   |    Alex   |
+----------+-----------+

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More