Do you speak SQL? (Part 1)

SQL stands for Structured Query Language and it is used to talk to relational databases. While there is a standard for SQL, many databases also have their own dialects, so to speak. This is a quick intro to basic SQL, nothing fancy. In the examples, I will use the following sample data:

Friends table:

FirstName LastName FavoriteColor
James Smith blue
Mary Johnson green
David Williams red
Linda Jones purple
Daniel Brown green
Jenny Davis blue
Nancy Smith orange

SELECT

This is the most common statement used, as it gets data from the database. The basic syntax of a SELECT statement is:

SELECT FirstName, LastName, FavoriteColor FROM Friends;

Multiple fields are generally separated by commas, as are multiple tables in more advanced SELECT statements. The SELECT statement may also have optional WHERE and ORDER BY clauses and other refinements.

One of those refinements is the asterisk (*) used in place of the fields:

SELECT * FROM Friends;

* stands for all fields in the table. This certainly saves time, but can cause issues if the fields returned are not the fields expected, say because someone has changed the structure of the table. I generally never use * in my applications for that reason.

Another refinement is the DISTINCT keyword:

SELECT DISTINCT FavoriteColor FROM Friends;

This will return:

FavoriteColor
blue
green
red
purple
orange

Without the DISTINCT keyword, we would get:

FavoriteColor
blue
green
red
purple
green
blue
orange

WHERE limits the data returned to that which matches the expression in the WHERE clause.

SELECT FirstName, LastName FROM Friends WHERE FavoriteColor = 'purple';

FirstName LastName
Linda Jones

‘purple’ needs to be quoted because it is a string rather than a number. Numbers are not quoted.

Multiple conditions in a WHERE clause can be joined by the AND keyword. There is also an OR keyword.

SELECT FavoriteColor FROM Friends WHERE FirstName = 'Jenny' AND LastName = 'Davis';

FavoriteColor
blue

LIKE looks for data that matches a certain condition. LIKE has two wildcard characters: ‘_’ which matches any single character in a string, and ‘%’ which matches any number of characters in a string.

SELECT FirstName, LastName FROM Friends WHERE FirstName LIKE 'J%';

FirstName LastName
James Smith
Jenny Davis

ORDER BY sorts the returned data. ASC specifies ascending order and DESC specifies descending order.

SELECT FirstName, LastName FROM Friends ORDER BY LastName ASC;

FirstName LastName
Jenny Davis
Daniel Brown
Mary Johnson
Linda Jones
James Smith
Nancy Smith
David Williams

INSERT, UPDATE, DELETE

These are used to add, change, and delete rows in tables.

An INSERT statement adds a row to a table.  The syntax for an INSERT statement is:

INSERT INTO Friends (FirstName, LastName, FavoriteColor) VALUES ('David', 'Peterson', 'orange');

An UPDATE statement changes an existing row in a table.

UPDATE Friends SET LastName = 'Anderson' WHERE FirstName = 'Nancy';

A DELETE statement deletes a row from a table.

DELETE FROM Friends WHERE FirstName = 'David' AND LastName = 'Williams';

After executing these INSERT, UPDATE, and DELETE statements, our data looks like so:

Friends table:

FirstName LastName FavoriteColor
James Smith blue
Mary Johnson green
Linda Jones purple
Daniel Brown green
Jenny Davis blue
Nancy Anderson orange
David Peterson orange

4 thoughts on “Do you speak SQL? (Part 1)”

  1. And my favorite color is orange! ~:D

    I’m going to ask a number of questions which may be answered by future posts, but I haven’t gotten there yet, so bare with me.

    So you really never use *? What if you just want to select everything at the beginning so it’s available? My old book reviews page (which was based on Gary Shannon’s Kalusa engine) frequently had things like this:

    $query = "SELECT * FROM $lexicon ORDER BY word ASC LIMIT $start, $per_page";

    That’s how the query would start. This is the basic query used to order, in my case, book reviews (to determine which ones would show up on a given page and how they were ordered). You would recommend doing something different?

    A short question: Can you give an example of how the _ variable works in a LIKE statement?

    I have a project in mind; hopefully I can figure out how to do it by reading along!

  2. I never use * except on a database that is completely under my control (like the one on my laptop.) Otherwise, I name all the columns needed, even if that is all the columns in the table.

    As for _, it replaces a single character. So, if I have two entries in my Friends table, one with a FirstName = Jamie and the other Janie, then I can find both with this SELECT statement: SELECT FirstName FROM Friends WHERE FirstName LIKE ‘Ja_ie’;

    Hope that helps.

  3. Also, your query up there isn’t naming a table directly either. It is using the PHP variable $lexicon to hold the table name. That’s probably why * is being used, too, since the line can’t specify columns from a table that it doesn’t yet know.

Comments are closed.