SQL stays with you as whatever the project they’ll be some data

The SQL in MySQL refers to … well er … SQL. Originally developed in the 1970s, SQL is a language used to access and manipulate data in relational databases. SQL became widely adopted in the 1980s after the publication of a SQL standard by the International Standards Organisation (ISO). 

I first learn some SQL in the mid-1990s certainly before anything like HTML had crossed my path. The nice thing about SQL is that it easy to learn and at it’s most basic almost verbatim in syntax.

Here are some of the basic functions of SQL to enable you to perform the four classic database transactions – selecting, updating, inserting and deleting.

SELECT Statements

The SELECT statement in SQL is used whenever we wish to extract data from the database usually to output to the screen.  An example would be:

SELECT Pupil ID, Firstname, Surname, Class (the fieldnames)
FROM Pupils (the database table name)
WHERE Surname = ‘Smith’ (the query condition)
ORDER BY Firstname (the output order – optional)

The above query would extract the fields ‘Pupil ID’, ‘Firstname’, ‘Surname’ and ‘Class’ from a table called ‘Pupils’, where the ‘Surname’ of a pupil was equal to ‘Smith’.  Finally the output is ordered by the pupil’s first name – so that Albert Smith would appear before ‘Zack Smith’.

A SELECT statement does not have to include a WHERE or ORDER BY clause.  A SELECT statement without a WHERE clause would extract all the records from a table.

Notes on Syntax:SQL commands are placed in CAPS and field/table names in lowercase.  It is also common practice to end an SQL statement with a semi-colon.  This would be to allow a second SQL statement to be executed if the database application allowed it.

The WHERE clause can have a number of different criteria.  These can be mathematical comparisons  ‘=’, ‘<‘, ‘>’, ‘<=’ etc to more verbatim ones such as ‘LIKE’ and ‘BETWEEN value1 AND value2’.  The LIKE clause must be used in conjunction with the wildcard ‘%’.  Multiple wildcards can be used to create ‘contains’ type queries.  Such that:

SELECT Pupil ID, Firstname, Surname, Class
FROM Pupils
WHERE Surname LIKE ‘%on%’

Returns all the records where the pupils surname contain ‘on’ ie Jones, Ronald.  Note that text/string values are enclosed by inverted commas.  Numeric values do not have to be enclosed in quotes.

The WHERE clause can also be extended with logical comparisons with the inclusion of AND, NOT and OR eg

SELECT Pupil ID, Firstname, Surname, Class
FROM Pupils
WHERE Surname = ‘Smith’ AND Firstname = ‘John’

This would return all the pupils with the name John Smith.  A NOT example would be:

SELECT Pupil ID, Firstname, Surname, Class
FROM Pupils
WHERE NOT Surname = ‘Smith’ AND NOT Surname = ‘Jones’

This would return all the pupils without the surname ‘Smith’ and ‘Jones’.

The ORDER BY command can be used to extract records in order – alphabetical for strings (A – Z) and lowest to highest for numbers .  Choose more than one record separated by commas to add to levels of ordering ie strict alphabetical.

SELECT Pupil ID, Firstname, Surname, Class
FROM Pupils
ORDER BY Surname, Firstname

By placing DESC after a field the order is reversed.  Ie

SELECT Pupil ID, Firstname, Surname, Class
FROM Pupils
ORDER BY Surname DESC, Firstname DESC

There are many other SQL commands related to SELECT

The DISTINCT keyword is used to return only distinct (unique) values.  When placed in the SELECT statement it can be used to extract only one instance of a particular value.

SELECT DISTINCT Class
FROM Pupils

This would extract a list of the classes in the school.  But even though there are many pupils in each class only one instance of each class is extracted.

UPDATE, INSERT and DELETE Statements

The UPDATE statement is used to update records within the database.  It has the following syntax:

UPDATE table
SET fieldtitle2 = value2, fieldtitle3 = value3
WHERE fieldtitle1 = value1

The INSERT statement will create a brand new record within a database.  It has the following syntax:

INSERT INTO table (fieldtitle1, fieldtitle2, etc)
VALUES (value1, value2, etc)

Finally the DELETE statements does exactly as you would expect and will remove a record from the database.  The syntax is:

DELETE fields
FROM table
WHERE field = value

Each of the above is often used with a HTML form to submit the modifications to the database.

Leave a Comment