Introduction to SQL
The Structured Query Language (SQL) is a computer language, standardised by the American National Standards Institute (ANSI) that is used to access and manipulate a relational database created with a database application such as MS Access, Oracle, or MySQL. SQL commands can be used to create a new database, add, modify or delete tables and views, and insert, retrieve, modify or delete database records. Although most SQL commands meet the requirements of the ANSI standard there are, as with other computer languages, a number of proprietary extensions, and different database applications may use a different syntax for some commands. In the following sections, we will be discussing the use of SQL in the context of MySQL and PHP. MySQL is a popular and open source Relational Database Management System (RDBMS) that is particularly suited for use with PHP to create powerful, cross-platform web applications.
Any relational database will include at least one table. A table has a name that is unique within the database (e.g. "Customer"), and is a collection of related records (for example, the name, address and contact details for all of the customers of a particular organisation). Each column is a field that represents a particular attribute of the entity (for example firstName, lastName or telephoneNo). Each row represents a single entity record (for example the name, address and telephone number of a particular customer).
SQL commands
Nearly all of the operations that are performed on a database are carried out using SQL commands. As a language, SQL is not case-sensitive, although for the sake of readability many people like to type SQL keywords in upper-case characters. Depending on the precise implementation, each SQL statement may or may not need to be terminated using a semi-colon, although using a semi-colon where it is not actually required does not usually cause a problem. SQL commands can be divided into two categories, depending on whether they form part of SQL’s Data Manipulation Language (DML), or its Data Definition Language (DDL). Some of the commonly used commands that form part of the DML are listed below.
- SELECT - retrieves records from a database
- UPDATE - updates records in a database
- DELETE - deletes records from a database
- INSERT INTO - inserts new records into a database
Some common DDL commands are listed below.
- CREATE DATABASE - creates a new database
- ALTER DATABASE - modifies a database
- CREATE TABLE - creates a new table
- ALTER TABLE - modifies a table
- DROP TABLE - deletes a table
- CREATE INDEX - creates an index (search key)
- DROP INDEX - deletes an index
The SELECT command
The SELECT command is probably the most frequently used SQL command, and has the following syntax:
SELECT <column_name(s)> FROM <table_name>
Consider the example "Customer" table shown below.
| CustID | LastName | FirstName | TelNo | |
|---|---|---|---|---|
| 000001 | Wells | Chris | 01752 667788 | cwells@blueyonder.co.uk |
| 000002 | Bloggs | Fred | 01752 110077 | fbloggs@technologyuk.net |
| 000003 | Smith | John | 01752 774499 | jsmith@btinternet.com |
| 000004 | Jones | David | 01752 123456 | djones@aol.com |
| 000005 | Miles | Michael | 01752 987654 | mmiles@hotmail.com |
| 000006 | Johnson | Matthew | 01752 364759 | mjohnson@technologyuk.net |
Supposing we wanted to retrieve a list consisting of the first name, last name and telephone number for all of the customer records in the table. The SQL command would need to use the SELECT statement, and would appear as follows:
SELECT LastName, FirstName, TelNo FROM Customer;
The set of results that will be returned is shown below.
| LastName | FirstName | TelNo |
|---|---|---|
| Wells | Chris | 01752 667788 |
| Bloggs | Fred | 01752 110077 |
| Smith | John | 01752 774499 |
| Jones | David | 01752 123456 |
| Miles | Michael | 01752 987654 |
| Johnson | Matthew | 01752 364759 |
Now suppose we want to retrieve all of the columns from the customer table. We could use either of the two SQL commands shown below, which are functionally equivalent.
SELECT CustID, LastName, FirstName, TelNo, Email FROM Customer
or
SELECT * FROM Customer;
The asterisk (*) is used in this context as a wildcard (i.e. it represents any and all fields in the record). Obviously, from the point of view of having to type these commands into a database application to retrieve a set of records, the shorter version is much more convenient. Whichever version of the command we use, the set of results that will be returned are shown below.
| CustID | LastName | FirstName | TelNo | |
|---|---|---|---|---|
| 000001 | Wells | Chris | 01752 667788 | cwells@blueyonder.co.uk |
| 000002 | Bloggs | Fred | 01752 110077 | fbloggs@technologyuk.net |
| 000003 | Smith | John | 01752 774499 | jsmith@btinternet.com |
| 000004 | Jones | David | 01752 123456 | djones@aol.com |
| 000005 | Miles | Michael | 01752 987654 | mmiles@hotmail.com |
| 000006 | Johnson | Matthew | 01752 364759 | mjohnson@technologyuk.net |
The SELECT DISTINCT statement
In a table that contains a significant number of records, it is almost inevitable that one or more fields will contain duplicate values. Although this is not normally a problem, you may occasionally want to only list values that are different (or distinct). The DISTINCT keyword can be used to modify a SELECT command for this purpose. A SELECT DISTINCT command has the following syntax:
SELECT DISTINCT <column_name(s)> FROM <table_name>
Consider the example "Towns" table shown below.
| Town | County |
|---|---|
| Bodmin | Cornwall |
| Bridgwater | Somerset |
| Exeter | Devon |
| Glastonbury | Somerset |
| Minehead | Somerset |
| Newquay | Cornwall |
| Newton Abbot | Devon |
| Paignton | Devon |
| Penzance | Cornwall |
| Plymouth | Devon |
| Redruth | Cornwall |
| Taunton | Somerset |
| Torquay | Devon |
| Truro | Cornwall |
| Yeovil | Somerset |
Supposing we wanted to retrieve a list consisting of the county names only. We only need each county to be included in the list only once. The SQL command required to achieve this would use the SELECT DISTINCT statement, as follows:
SELECT DISTINCT County FROM Towns;
The set of results that will be returned is shown below.
| County |
|---|
| Cornwall |
| Somerset |
| Devon |
The WHERE keyword
The WHERE clause is used with the SELECT command to return only records matching a specified criterion (or set of criteria). The syntax of a SELECT command that uses the WHERE clause is shown below.
SELECT <column_name(s)> FROM <table_name> WHERE <column_name> <operator> <value>
Consider the "Towns" example again. If we wanted to retrieve a list consisting of only the towns in Devon, we could use the following SQL command (note that where text values are used in SQL commands, the text is enclosed by single quotes):
SELECT Town FROM Towns WHERE County = 'Devon';
The set of results that will be returned is shown below.
| Town |
|---|
| Exeter |
| Newton Abbot |
| Paignton |
| Plymouth |
| Torquay |
The operators that may be used with the WHERE clause are listed in the table below.
| Operator | Description |
|---|---|
| = | Equal to |
| <> | Not equal to |
| > | Greater than |
| < | Less than |
| >= | Greater than or equal to |
| <= | Less than or equal to |
| BETWEEN | Between defined lower and upper bounds in a given range of values |
| LIKE | Pattern-matching operator |
| IN | Specify a column in which a value should occur |
The AND and OR operators
The AND and OR operators are used to return records based on the evaluation of two expressions. The AND operator displays a record if both expressions evaluate to TRUE, while the OR operator displays a record if either expression (or both) evaluates to TRUE. To demonstrate the AND operator, supposing we want to find the record in the "Customer" table that relates to the customer "Chris Wells". We could use the following SQL command:
SELECT * FROM Customer WHERE LastName = 'Wells' AND FirstName = 'Chris';
The result that will be returned is shown below.
| CustID | LastName | FirstName | TelNo | |
|---|---|---|---|---|
| 000001 | Wells | Chris | 01752 667788 | cwells@blueyonder.co.uk |
To demonstrate the OR operator, suppose for argument’s sake that we wanted to find all the towns in our "Town" table that were either in Devon or Cornwall. We could use the following SQL command:
SELECT * FROM Towns WHERE County = 'Devon' OR County = 'Cornwall';
The set of results that will be returned is shown below.
| Town | County |
|---|---|
| Bodmin | Cornwall |
| Exeter | Devon |
| Newquay | Cornwall |
| Newton Abbot | Devon |
| Paignton | Devon |
| Penzance | Cornwall |
| Plymouth | Devon |
| Redruth | Cornwall |
| Torquay | Devon |
| Truro | Cornwall |
The AND and OR operators can be used together to create more complex queries. For example, if we wanted to select a customer from the "Customers" table whose last name was "Smith", and whose first name might be either "John" or "Fred", we might use a command like the following (note the use of parentheses):
SELECT * FROM Customer WHERE LastName = 'Smith' AND (FirstName = 'John' OR FirstName = 'Fred');
The result that will be returned is shown below.
| CustID | LastName | FirstName | TelNo | |
|---|---|---|---|---|
| 000003 | Smith | John | 01752 774499 | jsmith@btinternet.com |
The ORDER BY clause
The ORDER BY clause is used to return results that are sorted into a specific order based on the values in the column specified. By default, records are sorted in ascending order, but the keywords ASC or DESC can be used to specify whether records should be sorted in ascending or descending order. To select all customer records from the "customer" table, sorted alphabetically by last name, we could use:
SELECT * FROM Customer ORDER BY LastName;
The set of results that will be returned is shown below.
| CustID | LastName | FirstName | TelNo | |
|---|---|---|---|---|
| 000002 | Bloggs | Fred | 01752 110077 | fbloggs@technologyuk.net |
| 000006 | Johnson | Matthew | 01752 364759 | mjohnson@technologyuk.net |
| 000004 | Jones | David | 01752 123456 | djones@aol.com |
| 000005 | Miles | Michael | 01752 987654 | mmiles@hotmail.com |
| 000003 | Smith | John | 01752 774499 | jsmith@btinternet.com |
| 000001 | Wells | Chris | 01752 667788 | cwells@blueyonder.co.uk |
We can reverse the ordering of the customer list by forcing the ORDER BY clause to sort the records into descending order using the DESC keyword, as follows:
SELECT * FROM Customer ORDER BY LastName DESC;
The set of results that will be returned is shown below.
| CustID | LastName | FirstName | TelNo | |
|---|---|---|---|---|
| 000001 | Wells | Chris | 01752 667788 | cwells@blueyonder.co.uk |
| 000003 | Smith | John | 01752 774499 | jsmith@btinternet.com |
| 000005 | Miles | Michael | 01752 987654 | mmiles@hotmail.com |
| 000004 | Jones | David | 01752 123456 | djones@aol.com |
| 000006 | Johnson | Matthew | 01752 364759 | mjohnson@technologyuk.net |
| 000002 | Bloggs | Fred | 01752 110077 | fbloggs@technologyuk.net |
The INSERT INTO statement
The INSERT INTO statement is used to insert a new record into a table. The syntax used varies, depending on whether or not we wish to explicitly specify which column a value should be inserted into or not. In some cases, you may want to omit certain columns (for example because they are automatically incremented numbers, or will be allocated appropriate default values). The two syntactical forms are shown below.
INSERT INTO <table_name> VALUES (<value1, value2, Value3 . . .>)
or
INSERT INTO <table_name> (<column1, column2, column3 . . .>) VALUES (<value1, value2, Value3 . . .>)
The first syntactical form is appropriate for relatively simple tables like our "Towns" table. The following example inserts a new record:
INSERT INTO Towns VALUES ('Launceston', 'Cornwall')
Following successful execution of the above command, the "Towns" table will have an additional record, as illustrated below.
| Town | County |
|---|---|
| Bodmin | Cornwall |
| Bridgwater | Somerset |
| Exeter | Devon |
| Glastonbury | Somerset |
| Minehead | Somerset |
| Newquay | Cornwall |
| Newton Abbot | Devon |
| Paignton | Devon |
| Penzance | Cornwall |
| Plymouth | Devon |
| Redruth | Cornwall |
| Taunton | Somerset |
| Torquay | Devon |
| Truro | Cornwall |
| Yeovil | Somerset |
| Launceston | Cornwall |
Our "Customer" table may have a numeric "CustID" field that is automatically incremented as records are added. If that is the case, we do not need to specify a value for this field, as it will be added automatically when we create a new record. We will need to specify which columns we are inserting data into, however, to ensure that each value is inserted into the correct column. The following SQL command inserts a new record into the "Customer" table:
INSERT INTO Customer (LastName, FirstName, TelNo) VALUES ('Evans', 'Gareth', '01752 112233')
Following successful execution of the above command, the "Customer" table will have an additional record, as illustrated below. Note that the "Email" column has not been assigned a value - this is perfectly acceptable if the database rules allow a null value in this field.
| CustID | LastName | FirstName | TelNo | |
|---|---|---|---|---|
| 000001 | Wells | Chris | 01752 667788 | cwells@blueyonder.co.uk |
| 000002 | Bloggs | Fred | 01752 110077 | fbloggs@technologyuk.net |
| 000003 | Smith | John | 01752 774499 | jsmith@btinternet.com |
| 000004 | Jones | David | 01752 123456 | djones@aol.com |
| 000005 | Miles | Michael | 01752 987654 | mmiles@hotmail.com |
| 000006 | Johnson | Matthew | 01752 364759 | mjohnson@technologyuk.net |
| 000007 | Evans | Gareth | 01752 112233 |
The UPDATE statement
The UPDATE statement is used to modify existing records. The syntax of the UPDATE statement is shown below.
UPDATE <table_name> SET <column1> = <value1>, <column2> = <value2>, <column3> = <value3> . . . WHERE <some_column> = <some_value>
The WHERE clause specifies the record (or records) to be updated (note that if the WHERE clause is omitted, all records will be updated!). If we wanted to add the email address for customer Gareth Evans in our "Customer" table, we could use the following SQL command:
UPDATE Customer SET Email = 'gevans@technologyuk.net' WHERE LastName = 'Evans' AND FirstName = 'Gareth'
Following successful execution of the above command, the "Customer" table should appear as shown below.
| CustID | LastName | FirstName | TelNo | |
|---|---|---|---|---|
| 000001 | Wells | Chris | 01752 667788 | cwells@blueyonder.co.uk |
| 000002 | Bloggs | Fred | 01752 110077 | fbloggs@technologyuk.net |
| 000003 | Smith | John | 01752 774499 | jsmith@btinternet.com |
| 000004 | Jones | David | 01752 123456 | djones@aol.com |
| 000005 | Miles | Michael | 01752 987654 | mmiles@hotmail.com |
| 000006 | Johnson | Matthew | 01752 364759 | mjohnson@technologyuk.net |
| 000007 | Evans | Gareth | 01752 112233 | gevans@technologyuk.net |
The DELETE statement
The DELETE statement (as the name suggests) is used to delete records in a table. The general syntax of the DELETE statement is as follows:
DELETE FROM <table_name> WHERE <some_column> = <some_value>
The WHERE clause specifies which record (or records) are to be deleted (note that if the WHERE clause is omitted, all records will be deleted!). The following SQL command will delete all records from the "Towns" table that relate to Somerset:
DELETE FROM Towns WHERE County = 'Somerset'
Following successful execution of the above command, the "Towns" table should appear as shown below.
| Town | County |
|---|---|
| Bodmin | Cornwall |
| Exeter | Devon |
| Newquay | Cornwall |
| Newton Abbot | Devon |
| Paignton | Devon |
| Penzance | Cornwall |
| Plymouth | Devon |
| Redruth | Cornwall |
| Torquay | Devon |
| Truro | Cornwall |
| Launceston | Cornwall |