SQL stands for “Structured Query Language” , and is the de facto standard language for manipulating data in databases. In part 2, we discussed the “CRUD” operations – Create, Read, Update and Delete – SQL is used to complete these operations.

SQL is a standard which is largely database platform-independent; in general it works with MySQL, SQL Server, PostgreSQL, SQLite – all of which are different database types. If you learn the basics of SQL using one type of database, you should be able to manipulate other database types.

SQL commands which manipulate data in this way are often referred to as ‘queries’.

 

Basics of the syntax

The SQL syntax often follows this pattern:

Do <action> from <something> where <criteria>

Executing this command on a database may result in a record set – a subset of records from the database which meet the criteria and format we define in our SQL query.

For example:

Select firstname from contacts where business = ‘Terzo’

We’re going to run this against the following database, which contains two tables – ‘contacts’ and ‘orders’.

 

Let’s break this query down into its components:

Select firstname

This defines what we want as the output from the SQL query. We want the firstnames in our result recordset. The records may have other fields in (like surname) but we’re not interested in those.

from contacts

This defines where the data is held – very often this is one of the tables in the database. We’d like the data from the ‘contacts’ table. We’re going to ignore the ‘orders’ table.

where business = ‘Terzo’

This is the criteria. Each contact has a ‘business’ field – and we’re interested in the records where the business field is Terzo.

This results in the following record set:

 

Now let’s make this a little more sophisticated. Firstly, let’s say we want the firstname and the surname for our contacts. We modify the query as follows:

Select firstname, surname from contacts where business = ‘Terzo’

Running this query gives us the following result (or record set):

 

 

Or, if we want every field in the table, we run the following query:

Select * from contacts where business = ‘Terzo’

Which gives us the same records, but with all of the fields:

Using ‘select *’ is also useful if you don’t know the field names.

 

You could run the following query:

Select * from contacts

Which results in:

This query omits the ‘criteria’ part of the statement. This gives us everything in the table – all fields (because of ‘select *’) and all records (as there was no criteria).

 

If we modified the criteria a little:

Select * from contacts where business like ‘%T%’

We’d get:

The ‘%’ symbol is a wildcard and allows anything in its place (including an empty string). Any record with the business field containing ‘T’ will be returned.

 

We could make the criteria more stringent by adding another required match:

Select * from contacts where business = ‘Terzo’ and firstname = ‘Alice’

This returns:

Note the criteria used ‘and’ – both conditions had to be true. We could have used ‘or’ instead – only one of the conditions had to be true to get any particular record.

 

Now, if we wanted to get a particular order, we should query the ‘order’ table instead of the ‘contacts’ table:

Select * from orders where Order ID = ‘1’

Returns:

Note that we got all the fields back (because of the ‘select *’ command) but the fields are different – because we queried a different table. Also, we only obtained one record – because the Order ID is unique for each record.

 

Note that you can combine tables in the SQL query – for example you can define criteria against multiple tables. We’ve kept it simple here but statements can get complex and more powerful.

 

Other CRUD actions

Those are very basic examples for reading data out of a database. We can perform other CRUD actions though, such as:

Creating records using the ‘insert’ action, for example:

Insert into contacts (firstname, lastname, business, email_address) values (‘Neil’, ‘Tubman’, ‘Terzo’, ‘nt@terzo.biz’)

This results in a new record being added to the contacts table. Note that the order of the fields matches the order of the values. Also note that we didn’t need to add the ID field, as this is auto-generated by the database.

 

Updating records using the ‘update’ action, for example:

Update contacts set business = ‘Newco’ where surname = ‘Doe’

This updates the business field for anyone with the surname ‘Doe’.

 

Deleting records using the ‘delete’ action, for example:      

Delete from contacts where company = ‘Terzo’

This would delete all the records that match the criteria.

 

These parts of SQL are grouped together and referred to as DML – Data Manipulation Language. We’re altering the data that’s stored in the database.

 

Other tasks

SQL also allows you to create and delete the tables themselves in the database; it’s possible to create an entire database with a series of SQL commands. Such commands are referred to as DDL – Data Definition Language, because they define the structure (or definition) of the data.

 

Summary

This article builds on our other ‘database basics’ articles. You can use SQL to manipulate the data in the database and perform the CRUD actions. The SQL language is largely independent of the database type.

 

Neil Tubman, Terzo Digital, March 2018