Programming on the Server Side
35 Four SQL Queries
You talk to a MySQL database using SQL (Structured Query Language). Each statement in SQL is known as a query. For our purposes, all we need are four queries that operate on the rows of a table: INSERT for adding rows, SELECT for retrieving rows or parts of rows, UPDATE for changing rows, and DELETE for removing rows.
Do it Yourself
The main text in this chapter references the poll table you may have created in the last chapter. If you didn’t create that table, you can create a new local database, then download and import poll.sql from the data folder of the Full Stack Example Pack.
If you want to do these Do it Yourself activities, you should also download and import world.sql from the data folder of the Full Stack Example Pack. This will import 3 tables: city, country, and countrylanguage.
If you have forgotten how to create databases and import tables, go back to the Database chapter.
Troubleshooting: If you mess up any of the tables when trying the Do it Yourself activities, you can always delete (drop) them from the database and import again.
SQL INSERT
The INSERT query can be used to add a row to a database table. Here is the basic syntax:
INSERT INTO table (columns…) VALUES (values…);
In the columns list, you must list any column that does not have a default value defined. You can optionally list other columns as well if you want to give them a value other than the default. In the values list, you must put legal values for the each column in the same order as you listed them earlier. For the poll table from the previous chapter, we would not have to list userid, option3, or option4 because they can all be NULL. We also do not have to list ID, since it is auto-incrementing.
These are both legal INSERT queries for the poll table as created in the last chapter:
INSERT INTO poll (title, question, option1, option2) VALUES ('My Poll', 'How are you?', 'Fine', 'Awesome');
INSERT INTO poll (ID, title, question, option1, option2, option3) VALUES (982, 'Monkeys', 'Best monkey?', 'Spider', 'Macaque', 'Capuchin');
Note that VARCHAR and TEXT values must be single-quoted, but in the second INSERT statement, the ID value is an integer so it does not have quotes.
Do it Yourself
If you have not yet imported the world.sql tables, follow the instructions in the first Do it Yourself box.
Step 1: Use the Structure tab in phpMyAdmin to look at the columns in the Country table. Take note of the column names that do not have default values associated with them. Take note of the Primary Key of this table. Also, take note of the type of each of those columns (char a text type is similar to VARCHAR, char(50) means a string of maximum length 50).
Step 2: Go to the SQL tab of phpMyAdmin and type an INSERT query to create a new country of your very own. Only specify column values that do not have default values associated with them. Scroll down and press GO in the bottom right of the tab to execute the query.
Step 3 (if it failed): Read the error message carefully to see if you can figure out what went wrong. Perhaps you used a Code that is already in use, or perhaps you forgot a quote or a comma. Now click “edit inline“, and fix the query and press GO again. Keep trying until it works. Once it works, go on to Step 4.
Step 4 (it worked): Congrats! Copy the query so you can paste it later. Then go to the Browse tab and see if you can find your new country (click on “show all rows” and use the Filter to find your country). Do all the values make sense?
Step 5: If you haven’t already seen a Duplicate Primary Key error, go back to the SQL tab and try to enter the same INSERT query again (if you copied it in the last step, you can paste it now). The INSERT should be refused because of the duplicate Code value.
Step 5: Edit the INSERT command from the last step so that the value you’re entering for Code2 is longer than 2 characters. Does it work? If so, what happened to the Code2 value? Check in the Browse tab.
SQL SELECT
The SELECT query can be used to retrieve rows or parts of rows from a database table. The basic syntax is shown below (the square brackets indicate optional clauses):
SELECT fields… FROM table [WHERE condition] [ORDER BY field [DESC] …] [LIMIT n];
The following SELECT query retrieves the entire poll table, specifying a wild card for the fields to retrieve:
SELECT * FROM poll;
If you only need certain columns, you can name them in a comma-separated list. This query retrieves the question and options for all rows in the poll table:
SELECT question, option1, option2, option3, option4 FROM poll;
Gotcha: == vs =
The SQL Boolean equality operator is a single equal sign =. The double equal == causes a syntax error.
The other comparison and Boolean operators are what you would expect: <, >, !=, <=, >=, AND, OR, and NOT.
Usually a SELECT query is looking for something specific – we don’t want every single row. The optional WHERE clause limits the rows returned to only those that satisfy a Boolean expression. The following retrieves titles for polls owned by userid ‘samscott’.
SELECT title FROM poll WHERE userid='samscott';
This SELECT query retrieves all fields for polls with ID values from 3 to 6:
SELECT * FROM poll WHERE ID>=3 AND ID<=6;
The optional ORDER BY clause can be used to sort by a given column, and the optional LIMIT clause places a maximum on the number of rows returned. The query below will return the title and question of the first 10 polls owned by userid ‘ss1234’, sorted by title.
SELECT title, question FROM poll WHERE userid='ss1234' ORDER BY title LIMIT 10;
You can use the keyword DESC to sort in descending order:
SELECT title, question FROM poll WHERE userid='ss1234' ORDER BY title DESC LIMIT 10;
Do it Yourself
If you have not yet imported the world.sql tables, follow the instructions in the first Do it Yourself box.
In the SQL tab of phpMyAdmin, construct and execute the following SELECT queries:
- Tiny Countries
- Get all columns for any country with a SurfaceArea less than 1000.
- Small Countries
- Get just the Code and Name of any country with a Population between 5 and 10 million, sorted by Code in ascending order.
- The Biggest Country
- Get the Name and Population of the largest country (hint: use ORDER BY and LIMIT)
- Canada
- Get the HeadOfState of Canada. Use the Code not the Name to retrieve it (just in case there are two Canadas)
SQL UPDATE
The UPDATE query can be used to set new values in particular fields of particular table rows. Here’s the syntax:
UPDATE table SET field=value… [WHERE condition];
For example, the following UPDATE query sets the value of the last two options to “Because” and “Don’t Ask” in all polls owned by userid ‘fd3423’.
UPDATE poll SET option3='Because', option4='Don\'t ask' WHERE userid='fd3423';
You can also use the previous value of a field to set a new value. The query below records a vote by adding one to the vote1 field of the poll table. To run this, you have to have that column in the poll table. Maybe you added it in the Coding Practice from the last chapter. If not, the poll.sql export in the data folder of the Full Stack Example Pack has this column.
UPDATE poll SET vote1=vote1+1 WHERE ID=1;
Do it Yourself
If you have not yet imported the world.sql tables, follow the instructions in the first Do it Yourself box.
In the SQL tab, construct and execute the following UPDATE queries:
- Update Canada’s Head of State
- You may have noticed some info in these tables is a little out of date. Canada’s head of state is listed as Elizabeth II. Use an UPDATE query to change it to Charles III. Use Canada’s Code so that you can be sure only one row is changed. Check in the Browse view to make sure it worked.
- No More Regions
- From now on, there is only one region – planet Earth! Use an UPDATE query to set the region to “Earth” for all countries. How many rows were affected? Check the Browse view to make sure it worked.
- Tiny Countries are Prospering
- Tiny countries did really well this year. Use an UPDATE query to increase by 2000 the GNP of any country with a SurfaceArea less than 1000. How many rows were affected? Check the Browse view to make sure it worked.
- The Population is Growing
- How much has Canada’s population grown since this data was created? Figure out the percentage of growth, and then use an UPDATE query to apply that growth factor across the board. (For example, if Canada’s population has grown by 5%, grow all country’s populations by that much.) How many rows were affected? Check the Browse view to make sure it worked
SQL DELETE
Gotcha: DELETE
Don’t ever leave off the WHERE clause!
DELETE FROM polls;
The query above wipes out your entire table and leaves it empty.
The DELETE query can be used to remove rows from a table. Careful with this one, you can do a lot of damage quickly if you get the WHERE clause wrong or accidentally omit it. Here’s the syntax:
DELETE FROM table [WHERE condition];
For example, the DELETE query below removes all polls from the database with the title of ‘My Poll’.
DELETE FROM poll WHERE title='My Poll';
Do it Yourself
DELETE some information.
If you have not yet imported the world.sql tables, follow the instructions in the first Do it Yourself box.
In the SQL tab, construct and execute the following DELETE queries:
- Delete a Country
- Construct a DELETE query to delete Canada, or some other country if you prefer. Use the country’s Code to remove it from the country table. How many rows were affected? Check the Browse view to make sure it worked.
- No more big countries
- Construct a DELETE query to remove any country with a population of 50 million or more. How many rows were affected? Check the Browse view to make sure it worked.
Coding Practice
For these exercises, you will write SQL queries using the Try it Yourself window in the w3Schools SQL Tutorial section. Go to https://www.w3schools.com/sql and click the first Try it Yourself button. You will get an SQL window that lets you interact with your own private copy of a training database known as the Northwind Database. Any changes you make to this database will persist for you until you press the restore button or restart your browser.
SELECT
- View the entire OrderDetails table
- Select all the columns for OrderID 10281
- Retrieve just the product ids for all large orders (quantity > 90) sorted in descending order
- Retrieve just the product id for the single largest order (without using its OrderID)
INSERT
- Add an employee named Amy Smith to the Employees table
- Born November 2, 1969,
- has a B.A. in Basket Weaving from University of South South Western Ontario
- Don’t specify EmployeeID or Photo
- Do a SELECT to check that you entered her record correctly.
UPDATE
- Use SELECT to get the ID of the supplier named “Bigfoot Breweries”.
- Use the ID to change their contact to Mary Shelley, phone number (503) 555-4242.
- Do a SELECT to check the record after the update.
- Do an UPDATE that adds 10 to the Quantity field of every order in the OrderDetails table.
- Apply a 50% discount to the price of every entry in the Products table that costs more than $10.
DELETE
- Use a SELECT to find the low priced products ($10 or less).
- Delete all these products.
- Use a new SELECT to make sure it worked.
