from Ada and Euclid to Quantum Computing and the World Wide Web
|Schedule - Problem Sets - Exams - Lectures - Syllabus - Links|
Schemer's Guide to Structured Query Language
Structured Query Language, or SQL, is a language used to manipulate and extract the information in a database. Data is stored in tables. Tables are structured by rows and fields (columns). Fields are general categories of information, while rows represent related information. SQL is used to manage the tables in a database through queries.
Creating a DatabaseBefore you can start, you need to create a database account on the UVa Server. ITC (Office of Information, Technology and Communication) provides free access to a MySQL database to all UVa students.
- Open https://dbm1.itc.virginia.edu in a web browser
- Click "Get/Reset an Account"
- Enter the information required. For security reasons, you should not use a password you use for anything important. Since you need to use the database password in plaintext in your PHP files to access the database, it is not secure.
- You'll get a confirmation page if everything went according to plan.
- Go to https://dbm1.itc.virginia.edu/phpMyAdmin/index.php3
- A little box pops up asking for your username and the password you just entered.
- Create a new database for all your CS 200 needs. Call it abc1x_cs200, where abc1x is your username, of course. Hit the "create" button to do this.
- Now, on the left, click on abc1x_cs200. You are now in the database administration page, where you can run SQL commands (described below) to manipulate your database.
The basic SQL commands are described by the following grammar:
Commands ::= Command ; CommandsSQL commands are case-insensitive (create means the same thing as CREATE). By convention, we will use all capitals to distinguish SQL commands. The non-terminals use in these productions will be described later. This document describes the most important SQL commands; for full details see http://www.mysql.org/doc/.
Commands ::= Command
Command ::= CreateTableCommand | InsertCommand | SelectCommand | UpdateCommand | DeleteCommand | DropTableCommand
CreateTable ::= CREATE TABLE Name (FieldDefinitions)
InsertCommand ::= INSERT INTO Table ( FieldNames ) VALUES (Values)
DeleteCommand ::= DELETE FROM Table WhereClause
SelectCommand ::= SELECT Fields FROM Table JoinClause WhereClause OrderClause
UpdateCommand ::= UPDATE Table SET SetList WhereClause
DropTable ::= DROP TABLE Table
Creating a Table
You create a table with the CREATE TABLE command (MySQL Documentation):CreateTable ::= CREATE TABLE Name (FieldDefinitions)
FieldDefininitions::= FieldDefintion, FieldDefinitions
FieldDefinitions ::= FieldDefinition
FieldDefinition ::= FieldName FieldType PrimaryModifier UniqueModifier NullModifier IncrementModifier
PrimaryModifier ::= PRIMARY KEY
UniqueModifier ::= UNIQUE
NullModifier ::= NOT NULL | NULL
OptIncrement ::= AUTO_INCREMENT
FieldType ::= DATE | TIME | DATETIME | INT | FLOAT | VARCHAR (Number) | TEXT
FieldName ::= Name
FieldName ::= Table.Name
Table ::= name of a table
You can create as many tables in your database as you want. Each table has any number of rows, but must have at least one field (note that CreateDefinitions cannot be replaced with the empty string, there must always be at least on definition).
In addition, at least one of the fields must be a primary key. A primary key is used to uniquely identify rows in the table. The easiest example usually concerns names. Let's say you had a table of your friends. You may have two friends named "John Smith". How would you tell them apart in your table? You need another field, in addition to name, that assigns them something unique. It could be their e-mail address, or their social security number. For our puposes, however, let's just use integers that counts starting from 1. Here's how:
CREATE TABLE CSBooks ( BookID INT PRIMARY KEY NOT NULL AUTO_INCREMENT, Title VARCHAR (30), Author VARCHAR (30), Price FLOAT, Publisher VARCHAR (30), Year INT );
Try this yourself by pasting this code in the Run SQL query ... form you get from https://dbm1.itc.virginia.edu/phpMyAdmin/index.php3 (after creating an account and database as described above) and clicking the Go button.
Inserting RowsNow that we have created a table, we can put data in our table using the insert command: (MySQL Documentation)InsertCommand ::= INSERT INTO Table ( FieldNames ) VALUES (Values)To put a new field into our CSBooks table we could use the following insert command:
Values ::= Value Values
Values ::= Value ::= Number | String | Date | Boolean
FieldNames ::= FieldName , FieldNames
FieldNames ::= FieldName
INSERT INTO CSBooks (Title, Author, Price, Publisher, Year)
VALUES ('The Mind''s I', 'Hofstadter', 18.95, 'Bantam', 1985)
Try evaluating this yourself by pasting this code in the Run SQL query ... form you get from https://dbm1.itc.virginia.edu/phpMyAdmin/index.php3. Click on Browse to see the table.
Two idiosyncrasies to note. Firstly, notice that we enclose a string with single quotes ('). Numbers and dates are not enclosed. However, one of the fields (Title) has a single quote in the text. If we wrote 'The Mind's I' , then SQL would think the book is called "The Mind" and that "s I" was some sort of SQL command. Instead, we need to use '' to make the single quote. SQL treats two consecutive single quotes differently as a single quote in the quoted string.
Secondly, note that we do not tell SQL what to insert in the BookID field. This is because when we created the table we used AUTO_INCREMENT to indicate that the BookID field should automatically increment when we insert new entries in our table. This means that the database will automatically assign the next available integer (in this case, 7) to be the key for this row.
The table now looks like this:
BookID Title Author Price Pubisher Year 1 The Mind's I Hofstadter 18.95 Bantam 1985
We can insert a few more entries into our table the same way:After this, our table is:INSERT INTO CSBooks (Title, Author, Price, Publisher, Year) VALUES ('GEB', 'Hofstadter', 19.95, 'Basic', 1979);
INSERT INTO CSBooks (Title, Author, Price, Publisher, Year) VALUES ('Cryptonomicon', 'Stephenson', 14.25, 'Perennial', 1999);
INSERT INTO CSBooks (Title, Author, Price, Publisher, Year) VALUES ('The Code Book', 'Singh', 14.00, 'Anchor', 2000);
INSERT INTO CSBooks (Title, Author, Price, Publisher, Year) VALUES ('Snow Crash', 'Stephenson', 13.75, 'Bantam', 1992);
INSERT INTO CSBooks (Title, Author, Price, Publisher, Year) VALUES ('Fermat''s Enigma', 'Singh', 11.85, 'Anchor', 1997);
BookID Title Author Price Publisher Year 1 The Mind's I Hofstadter 18.95 Bantam 1985 2 GEB Hofstadter 19.95 Basic 1979 3 Cryptonomicon Stephenson 14.25 Perennial 1999 4 The Code Book Singh 14.00 Anchor 2000 5 Snow Crash Stephenson 13.75 Bantam 1992 6 Fermat's Enigma Singh 11.85 Anchor 1997
The delete command removes entries from a table: (MySQL Documentation)DeleteCommand ::= DELETE FROM Table WhereClauseThe where clause of a delete command specifies which rows to delete using boolean conditions. All rows for which the Conditions specified in the where clause are true are permanently removed from the table. For example, to remove all books published by Anchor that cost more than $12.00, we could use the following query:
WhereClause ::= WHERE Conditions
Conditions ::= Condition Conjunction Conditions
Conditions ::= Condition
Conjunction ::= AND | OR
Condition ::= Field Comparator Value
Comparator ::= < | > | = | <= | >=
DELETE FROM CSBooks WHERE Publisher='Anchor' AND Price > 12.00
This removes the entry for The Code Book from our table. If no conditions are specified in the delete command every entry will be deleted, so be careful! If you only want to delete one entry, the safest bet is to tell it to delete based on the primary key. For example, to delete The Mind's I do:After this, our table is:
DELETE FROM CSBooks WHERE BookID=1
BookID Title Author Price Publisher Year 2 GEB Hofstadter 19.95 Basic 1979 3 Cryptonomicon Stephenson 14.25 Perennial 1999 5 Snow Crash Stephenson 13.75 Bantam 1992 6 Fermat's Enigma Singh 11.85 Anchor 1997
The select command is used to extract a table from a table: (MySQL Documentation)SelectCommand ::= SELECT FieldSpecifier FROM Table JoinClause WhereClause OrderClauseA select command evaluates to a new table containing the selected entries. For example, if we wanted to get all the titles and their authors from our CSBooks table we could use the following query:
FieldSpecifier ::= Fields Fields ::= Field, Fields
Fields ::= FieldModifier Field
Fields ::= *
Field ::= name of a field in the table AS Name
Field ::= name of a field in the table
FieldSpecifier ::= Operator ( Fields ) AS Name
Operator ::= AVG | COUNT | MIN | MAX | SUM
FieldModifier ::= DISTINCT
JoinClause ::= INNER JOIN Table ON expandedField = expandedField
OrderClause ::= ORDER BY Field Descending
Descending ::= DESC
SELECT Title, Author
The result would be a table that looks like this:
Title Author GEB Hofstadter Cryptonomicon Stephenson Snow Crash Stephenson Fermat's Enigma Singh
To select all of the fields, the asterix (*) can be used instead of listing field names.
We can add a where clause to our query to make it more specific. Where clauses are the same as was introduced for the delete command. For example, we could use the following query to find all the books by Stephenson that are less then $15:
SELECT * FROM CSBooks
WHERE Author='Stephenson' AND Price < 14.00
This would result in the table:
BookID Title Author Price Publisher Year 5 Snow Crash Stephenson 13.75 Bantam 1992
We can also use SELECT queries to do simple calculations on our results. To find the total price of all books published before 1990 we could use the following query:
SELECT SUM(Price) AS Total FROM CSBooks WHERE Year < 1995
When performing calculations, you are, in essence, creating a new field. Therefore you must give it a name using AS. The result of this query would be:
Other calculations available include AVG to find an average of a set of numbers, COUNT to find out how many rows of results there are, MIN to find the minimum value, and MAX to find the maximum. The following query will count how many books Anchor has published:
SELECT COUNT(*) FROM CSBooks WHERE Publisher="Anchor"
We may want a list of the authors in our table. For this situation we would not want duplicate names. This can be accomplished using the DISTINCT keyword in the following manner:
SELECT DISTINCT Author
This will automatically filter all duplicates from the results and will produce the table
Author Hofstadter Singh Stephenson
SQL can also specify that the results should be sorted by a particular field by adding an order clause at the end of the select statement. To get a list of the books sorted by date we could use the query
SELECT * FROM CSBooks ORDER BY Year
which will result in the table
BookID Title Author Price Pubisher Year 2 GEB Hofstadter 19.95 Basic 1979 5 Snow Crash Stephenson 13.75 Bantam 1992 6 Fermat's Enigma Singh 11.85 Anchor 1997 3 Cryptonomicon Stephenson 14.25 Perennial 1999
To sort the list in the reverse order, add the keyword DESC to the end of the query.
There is one option in a select command, called a join, that is useful when dealing with multiple tables. A join combines two tables by a common field. Suppose we have the following table named Orders, of people who have ordered books. Note that we reference the books by BookID since that is the primary key of the CSBooks table.
BookID Name 2 Ada 5 Gödel 3 Bach 6 Bach
To get a full list of orders we could use the following query:
SELECT Name, Title, Author, Price, Publisher, Year
Orders INNER JOIN CSBooks
ON Orders.BookID = CSBooks.BookID
The resulting table looks like this:
Name Title Author Price Publisher Year Ada GEB Hofstadter 19.95 Basic 1979 Gödel Snow Crash Stephenson 13.75 Bantam 1992 Bach Cryptonomicon Stephenson 14.25 Perennial 1999 Bach Fermat's Enigma Singh 11.85 Anchor 1997
The join is a very powerful mechanism. By combining joins with order and where clauses you can express compilcate queries on databases.
There are many different ways to express the same join. Just so you're not confused by other people's code, the following three SQL statements are precisely identical to each other:Select can do much more than what is described here, see the MySQL Documentation if you need to do a more complicated selection.
SELECT Name, Title, Author, Price, Publisher, Year FROM CSBooks
JOIN Orders WHERE CSBooks.Publisher = 'Anchor' AND CSBooks.BookID = Orders.BookID
SELECT Name, Title, Author, Price, Publisher, Year FROM CSBooks, Orders
WHERE CSBooks.Publisher = 'Anchor' AND CSBooks.BookID = Orders.BookID
SELECT Name, Title, Author, Price, Publisher, Year FROM Orders
INNER JOIN CSBooks USING (Title) WHERE CSBooks.Publisher = 'Anchor'
An update command can be used to change information already in the table. Update has a similar syntax to the other queries with some extra information to specify the information to change using the SET keyword:UpdateCommand ::= UPDATE Table SET SetList WhereClauseFor example, the following query will change all of Stephenson's publishers to Doubleday and raise the price to 49.95.
SetList ::= Set, SetList
Set ::= Field = Value
UPDATE CSBooks SET Publisher='Doubleday', Price=49.95 WHERE Author='Stephenson'
Like the delete command, if no conditions are specified all of the rows will be set with the given information. Also like the DELETE query, you'll also want to reference the primary key to ensure that you're operating on the rows that you think you are.
The drop table command permanently removes an entire table from the database:DropTable ::= DROP TABLE TableBe careful! You lose everything in the table after a drop command.
Credits: This guide was created by Jon Erdman, Portman Wills and David Evans.