8 - PHP:MySQL - Select statement
SQL provides statements for working with the information in tables. The most basic and common one is the Select statement.
The SQL SELECT Statement
The select query is used to retrieve records from a database. The keywords used in a select query are summarised in the following table.
| Keyword | Description |
|---|---|
| SELECT | Retrieves fields from one or more tables. |
| FROM | Tables containing the fields. |
| WHERE | Criteria to restrict the records returned. |
| GROUP BY | Determines how the records should be grouped. |
| HAVING | Used with GROUP BY to specify the criteria for the grouped records. |
| ORDER BY | Criteria for ordering the records. |
| LIMIT | Limits the number of records returned. |
The simplest SELECT query is to retrieve all records from a single table. The following example lists all fields from the search table.
mysql> SELECT * FROM userInfo;
To select specific fields from a table, you provide a comma-separated list of field names. The following example selects the "firstName" and "lastName" from the userInfo table:
mysql> SELECT firstName, lastName FROM userInfo;
Limiting Records
The WHERE clause may be used to limit records.
When working with strings, the % character may be used as a wildcard. The following example retrieves all fields from the userInfo table where the lastName field contains the text, "ingo".
mysql> SELECT * FROM userInfo WHERE lastName LIKE '%on%';
This should return the John Lennon and George Harrison records.
Now let's modify our query just a little.
mysql> SELECT firstName FROM userInfo WHERE lastName LIKE '%on%';
The ORDER BY Clause
The ORDER BY clause may be used to order the records returned. The following example lists all last names in the userInfo table in alphabetical order.
mysql> SELECT lastName FROM userInfo ORDER BY lastName;
The ORDER BY clause may use the ASC or DESC modifiers to determine if the records should be in ascending or descending order. If neither are provided, the records are shown in ascending order. The following example lists all last names in the userInfo table in descending order.
mysql> SELECT lastName FROM userInfo ORDER BY lastName DESC;
(from juicystudio)