Updating Data with UPDATE

We first click on Browse for table cities, displayed our single row of data.

By clicking on the small pencil-shaped icon (or Edit link), we go to the Edit panel for this row. We decide to change the population value to 130000. After a click on Save, phpMyAdmin shows the following statement:

UPDATE `cities` SET `population` = '130000' WHERE `id` = '1' LIMIT 1 ;


Here we have the UPDATE keyword, followed by the table name. The SET keyword introduces the list of modifications (here only the population), which follows the format column = new value.


We now see the condition WHERE `id` = '1', which uses the primary key information to limit the change to only this row, i.e. only this city.
The limit 1 part is a safeguard added by phpMyAdmin, in case there would be no primary key defined, to avoid doing the change to more than one row.
More than one column can be changed in a single UPDATE operation:
UPDATE `cities` SET `city_name` = 'Sherbrooke, Qubec',

`population` = '130001' WHERE `id` = '1' LIMIT 1 ;

Deleting Data with DELETE
In Browse mode on table cities, clicking on the small red trash-can icon (or Delete link) brings up a dialog to confirm the execution of the following statement:
DELETE FROM `cities` WHERE `id` = '1' LIMIT 1 ;


The syntax is simple, involving just the table name, and the condition to apply for the delete operation.


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).