The previous article gave you an overview of the phpMyAdmin interface and functionality. It's now time to dive in further and learn how to construct and execute queries.
You'll need a database to work with, so download one from http://dev.mysql.com/doc/. Scroll down to -Example Databases" and download Sakila. Unzip the file and you will notice two .sql files; sakila-schema.sql and sakila-data.sql. We are going to import these files via the phpMyAdmin Import feature.
On your phpMyAdmin home page click Import. Browse to the files you just unzipped, select sakila-schema.sql and click Go. We are importing this file first, as it contains all the CREATE statements to create the structure of the database. Repeat the steps to import the sakila-data.sql file. This contains the INSERT statements to populate the database with data. Try to familiarise yourself with the schema before running the queries.
You should now see sakila in the Database drop-down menu on the left. Select it and then select the Query tab to start executing queries.
Below are some examples to help you get started:
To display the information about actors do the following:
|> Select `actor`.* in the Field drop-down
|> Tick the Show check box
|> In the Use Tables at the bottom, select actor
|> Click Update Query
This will generate the relevant SQL statement, which will appear in the SQL query on database sakila box at the bottom. Then, click Submit Query.
The results of our query are shown below.
Now, let's see how to incorporate the WHERE clause into a query. We're going to display the first and last name of the person with this e-mail address — MELISSA.KING@sakilacustomer.org.
|> Select `customer`.`first_name` and `customer`.`last_name` and tick the Show check boxes underneath them
|> In the last field drop-down select `customer`.`email`, as shown below
|> Type in ='MELISSA.KING@sakilacustomer.org' in the Criteria text box below
|> In the Use Tables, select customer
|> Click Update Query and the Submit Query
Next we are going to display the last name and the address of customers, by performing a join on customer and address tables.
|> Select `customer`.`last_name` and `address`.`address` and tick the Show check boxes underneath them
|> Select `address`.`address_id` in the last field
|> In the Criteria text field enter =`customer`.`customer_id`
|> In the Use Tables, select address and customer
|> Click on Update Query
This is the SQL statement you should get:
SELECT `customer`.`last_name`, `address`.`address` FROM customer, address WHERE (`address`.`address_id` =`customer`.`customer_id`)
The results of the query are shown below:
Now that you have an understanding of how to execute queries using phpMyAdmin, play around with the sakila database. Some columns however, are not populated with data, so keep this in mind when constructing queries. You might want to do a test first to see if there is any data there. Alternatively, you can make your own database.