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.

Some common DDL commands are listed below.



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.



The "Customer" table
CustIDLastNameFirstNameTelNoEmail
000001WellsChris01752 667788cwells@blueyonder.co.uk
000002BloggsFred01752 110077fbloggs@technologyuk.net
000003SmithJohn01752 774499jsmith@btinternet.com
000004JonesDavid01752 123456djones@aol.com
000005MilesMichael01752 987654mmiles@hotmail.com
000006JohnsonMatthew01752 364759mjohnson@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.



LastNameFirstNameTelNo
WellsChris01752 667788
BloggsFred01752 110077
SmithJohn01752 774499
JonesDavid01752 123456
MilesMichael01752 987654
JohnsonMatthew01752 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.



CustIDLastNameFirstNameTelNoEmail
000001WellsChris01752 667788cwells@blueyonder.co.uk
000002BloggsFred01752 110077fbloggs@technologyuk.net
000003SmithJohn01752 774499jsmith@btinternet.com
000004JonesDavid01752 123456djones@aol.com
000005MilesMichael01752 987654mmiles@hotmail.com
000006JohnsonMatthew01752 364759mjohnson@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.



The "Towns" table
TownCounty
BodminCornwall
BridgwaterSomerset
ExeterDevon
GlastonburySomerset
MineheadSomerset
NewquayCornwall
Newton AbbotDevon
PaigntonDevon
PenzanceCornwall
PlymouthDevon
RedruthCornwall
TauntonSomerset
TorquayDevon
TruroCornwall
YeovilSomerset

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.



Operators used with WHERE
OperatorDescription
=Equal to
<>Not equal to
>Greater than
<Less than
>=Greater than or equal to
<=Less than or equal to
BETWEENBetween defined lower and upper bounds in a given range of values
LIKEPattern-matching operator
INSpecify 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.



CustIDLastNameFirstNameTelNoEmail
000001WellsChris01752 667788cwells@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.



TownCounty
BodminCornwall
ExeterDevon
NewquayCornwall
Newton AbbotDevon
PaigntonDevon
PenzanceCornwall
PlymouthDevon
RedruthCornwall
TorquayDevon
TruroCornwall

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.



CustIDLastNameFirstNameTelNoEmail
000003SmithJohn01752 774499jsmith@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.



CustIDLastNameFirstNameTelNoEmail
000002BloggsFred01752 110077fbloggs@technologyuk.net
000006JohnsonMatthew01752 364759mjohnson@technologyuk.net
000004JonesDavid01752 123456djones@aol.com
000005MilesMichael01752 987654mmiles@hotmail.com
000003SmithJohn01752 774499jsmith@btinternet.com
000001WellsChris01752 667788cwells@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.



CustIDLastNameFirstNameTelNoEmail
000001WellsChris01752 667788cwells@blueyonder.co.uk
000003SmithJohn01752 774499jsmith@btinternet.com
000005MilesMichael01752 987654mmiles@hotmail.com
000004JonesDavid01752 123456djones@aol.com
000006JohnsonMatthew01752 364759mjohnson@technologyuk.net
000002BloggsFred01752 110077fbloggs@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.



The "Towns" table
TownCounty
BodminCornwall
BridgwaterSomerset
ExeterDevon
GlastonburySomerset
MineheadSomerset
NewquayCornwall
Newton AbbotDevon
PaigntonDevon
PenzanceCornwall
PlymouthDevon
RedruthCornwall
TauntonSomerset
TorquayDevon
TruroCornwall
YeovilSomerset
LauncestonCornwall

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.



The "Customer" table
CustIDLastNameFirstNameTelNoEmail
000001WellsChris01752 667788cwells@blueyonder.co.uk
000002BloggsFred01752 110077fbloggs@technologyuk.net
000003SmithJohn01752 774499jsmith@btinternet.com
000004JonesDavid01752 123456djones@aol.com
000005MilesMichael01752 987654mmiles@hotmail.com
000006JohnsonMatthew01752 364759mjohnson@technologyuk.net
000007EvansGareth01752 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.



The "Customer" table
CustIDLastNameFirstNameTelNoEmail
000001WellsChris01752 667788cwells@blueyonder.co.uk
000002BloggsFred01752 110077fbloggs@technologyuk.net
000003SmithJohn01752 774499jsmith@btinternet.com
000004JonesDavid01752 123456djones@aol.com
000005MilesMichael01752 987654mmiles@hotmail.com
000006JohnsonMatthew01752 364759mjohnson@technologyuk.net
000007EvansGareth01752 112233gevans@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.



The "Towns" table
TownCounty
BodminCornwall
ExeterDevon
NewquayCornwall
Newton AbbotDevon
PaigntonDevon
PenzanceCornwall
PlymouthDevon
RedruthCornwall
TorquayDevon
TruroCornwall
LauncestonCornwall