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:
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:
Without the DISTINCT keyword, we would get:
WHERE limits the data returned to that which matches the expression in the WHERE clause.
SELECT FirstName, LastName FROM Friends WHERE FavoriteColor = 'purple';
‘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';
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%';
ORDER BY sorts the returned data. ASC specifies ascending order and DESC specifies descending order.
SELECT FirstName, LastName FROM Friends ORDER BY LastName ASC;
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: