PhpMyAdmin Tutorial 6

Retrieving Data with SELECT

Retrieving information from our tables is probably the operation we do most of the times. This is the way to get answers to questions like �what are the cities with a population over a certain number?�.

In fact, we previously did a SELECT when we clicked on the Browse link for table cities. This generated a simple form of the SELECT statement:

SELECT * FROM `cities` LIMIT 0,30;

Here, the asterisk means �all the columns�. We add FROM and the name of the table which we want to query. The LIMIT 0,30 means to start at row number 0 (the first one), and select a maximum of 30 rows.

Let's try a Search to see more options for the SELECT. We go to the Search sub-page for table cities, and we choose only some columns we need:

Then at the bottom of the page, we choose to display by the result by population in descending order:

   

Executing the search generates the following query:

SELECT `city_name` , `population`
FROM `cities`
WHERE 1
ORDER BY `population` DESC LIMIT 0,30

We see that the asterisk has been replaced by a comma-separated list of columns. A condition WHERE 1 has been added by phpMyAdmin, this is a condition which is always true and selects all rows. We will see in a moment that we can replace it with some other condition. Also, the clause ORDER BY appears, followed by the column on which we want to sort results, and the keyword DESC for descending order (we could also use ASC for ascending).

Conditions

To easily add a condition, on the results page we can click on SQL-query: Edit, which brings the Query window popup. We add a condition on the country:

SELECT `city_name` , `population`

FROM `cities`

WHERE country_code = 'zh'

ORDER BY `population`  DESC

which displays all cities located in China (ok, we were a bit lazy with data entry, but you get the picture).

Conditions can be expressed using a rich array of operators and functions. Here are two examples:

Finding the Canadian cities with a population over 100000:

WHERE population > 100000 AND country_code = 'ca'

Finding the cities whose name starts with �A�:

WHERE city_name like 'A%'

 

 

Aggregate functions

Summary information may be generated by grouping on a specific column. Here we ask the average city population per country:

SELECT country_code, AVG(population)

FROM cities

GROUP BY country_code

Other possible aggregate functions are MIN(), MAX(), SUM() and COUNT(), which compute respectively the minimum value, maximum value, sum of values, and number of values. For example, we could get the number of cities per country with:

SELECT country_code, count(city_name)

FROM cities

GROUP BY country_code

Joins

Normally, a relational database involves many tables, linked on common keys. We may need at times to run queries on more than one table. Linking, or joining, tables can be done using different techniques; we will focus on a simple method involving key comparison.

In the following query, the FROM clause contains a comma-separated list of tables. In the columns list, we use the table name and a dot as a prefix before each column name (not strictly necessary if each column name is only present in one table).

SELECT cities.city_name, cities.population, countries.country_name
FROM cities, countries
WHERE cities.country_code = countries.country_code LIMIT 0,30

Conclusion

The SQL language has much more to it than the basic statements that we have covered here. However, this article has covered the basics of SQL and how to use the phpMyAdmin tool to advance your knowledge of SQL.


<<< PREVIOUS                                                                                                             Will come.....