Databases drive the modern web. Every big or dynamic website uses a database in some way, and when combined with Structured Query Language (SQL), the possibilities for manipulating data really are endless. If you already know SQL, make sure you checkout these programming skills all website developers should know.
Today I’ll be showing you some of the core commands you need to know as a programmer.
There are many names for data returned from a database table. Data is commonly referred to as Rows, Records, or Tuples. I’ll be using these terms interchangeably throughout this article.
All of the examples today will be based on four fictional tables. The customer table contains the name and age of customers:
The heights table contains the name and height of any person:
The staff table contains the name and age of staff members — exactly the same as the customer table:
The final table called people contains the name and age of people, just like the customer and staff tables:
The select statement is the simplest, and it’s essential you understand it as it underpins nearly all of the other commands. It’s considered a best practice to write your reserved SQL words in uppercase, as it makes the command easier to read and understand.
As its name implies, select is used to select data from a database. Here’s the simplest usage:
SELECT * FROM table;
There are two parts to this. The first part (SELECT *) specifies which columns you would like to select. The asterisk indicates that you wish to select all the columns in the table. The second part (FROM table) tells your database engine where you would like to retrieve this data from. Replace “table” with the name of your database table.
This select is known as “select star.” Using the asterisk is a good way to figure out what data is in a table, but I don’t recommend you use it for any production code. When using a select star, it’s up to the database engine to present you with the data you want. You don’t have any control over the order the data is returned, so if somebody adds a new column to the table, you may find your variables in your programming language no longer represent the correct data. Fortunately, there is a solution.
You can explicitly state which columns you would like to retrieve, like this:
SELECT age, name FROM people;
This query retrieves the “age” and “name” columns from the “people” table. Being this explicit can be slightly tedious if you have a lot of data, but doing so will reduce problems in the future, along with making your SQL easier to understand by any future programmers.
If you want to select an additional piece of data, but it’s not stored in any of your tables, you can do that like this:
SELECT age, '1234' FROM people;
Any string inside single quotes will be returned instead of matching a column name.
The select command is excellent for retrieving data, but what if you wanted to filter the results a bit more? What about retrieving only people who have blue eyes? What about people born in January who work as mechanics? This is where the where command comes in. This allows you to apply conditions to the select, and you simply append it to the end of the statement:
SELECT age, name FROM people WHERE age > 10;
This query is now restricted to people who are older than 10 years old. You can combine multiple conditions using the AND operator:
SELECT age, name FROM people WHERE age > 10 AND age < 20;
The AND command works exactly like it does in the English language: it applies another condition to the statement. In this example, the data returned would be any records with an age between 10 and 20. As there are no matching results, no data is returned.
Another command that can be used in conjunction with this is OR. Here’s an example:
SELECT age, name FROM people WHERE age > 10 OR name = 'Joe';
This query returns records where the age is more than 10, or the name is equal to “Joe”. Notice how there is only one equals sign? Most programming languages use double equals (==) to check for equivalence. This is not needed for the vast majority of database engines (but it can very per environment, so double-check first).
The order command is used to sort the results returned. It’s another easy one to use. Simply append it to the end of your statement:
SELECT name, age FROM people ORDER BY age DESC;
You need to specify the column and the order, which can be ASC for ascending or DESC for descending. You can order by multiple columns like this:
SELECT name, age FROM people ORDER BY name ASC, age DESC
ORDER BY is perhaps the most useful when combined with other commands. Not all queries will return data in a logical or ordered way — this command lets you change that.
The join command is used to join related data stored in one or more tables. You join the second table to the first table, and specify how the data is connected. Here’s a basic example:
SELECT age, name, height FROM people LEFT JOIN heights USING (name);
There’s a few things going on here. You have to start with the “LEFT JOIN” syntax, which specifies that you want to join a table using a join of type left. Next, specify the table you wish to join (heights). The USING (name) syntax states that the column “name” can be found in both tables, and that this should be used as a key to join the tables together.
Don’t worry if your columns have different names in each table. You can use “ON” instead of “USING”:
SELECT age, name, height FROM people LEFT JOIN heights ON (namea = nameb);
The on statement explicitly states which columns to key on. There are many types of join, and it would take a long time to go into details for each one, so here’s a quick summary of their uses:
- (INNER) JOIN — Returns rows with a match in both tables.
- LEFT (OUTER) JOIN — Returns all the rows from the left table, with any matches from the right table. If there are no matches, the left table records are still returned.
- RIGHT (OUTER) JOIN — This is the opposite of a left join: all the rows from the right table are returned, along with any matches in the left table.
- FULL (OUTER) JOIN — Returns any records with a match in either table.
The “INNER” or “OUTER” syntax is optional. It can makes things easier to understand, but you don’t have to specify it for the vast majority of the time.
Now you know the basics, let’s look at the alias command. This is used to temporarily rename a table — more of a nickname than anything else, as this new name only exists inside the individual transaction you are running. Here’s how you use it:
SELECT A.age FROM people A;
You can use any valid name you like, but I like to use letters of the alphabet. Before each column name, the alias is prefixed. This alias is assigned to the table immediately after it is declared. This is exactly the same as doing this:
SELECT people.age FROM people;
Rather than having to type a long table name, you can type a simple and easy to remember letter — but what’s the point? Well, if you’re selecting from more than one table, it’s easy to get confused about which columns belong to which table. If both your tables happen to have columns with the same name, your database query may even fail to run without explicitly referencing the table name or alias. Here’s an example with two tables:
SELECT staff.age, staff.name, customers.age, customers.name FROM staff, customers;
And here’s that same query with aliases:
SELECT A.age, A.name, B.age, B.name FROM staff A, customers B;
The staff table is given the alias of “A”, and the customers table is given the alias of “B”. Aliasing tables really does help to make your code easier to understand, and reduces the amount of typing you have to do.
You can also rename a column with an alias using the “AS” command:
SELECT age AS person_age FROM people;
When this query is run, the column will now be called “person_age” instead of “age”.
Union is a great command. It allows you to append rows to each other. Unlike joins which append matching columns, union can append unrelated rows provided they have the same number and name of columns. Here’s how you use it:
SELECT age, name FROM customers UNION SELECT age, name FROM staff;
You can think of union as a way of combining the results of two queries. A union will only return results where there is a unique row between the two queries. You can use the “UNION ALL” syntax to return all the data, regardless of duplicates:
SELECT age, name FROM customers UNION ALL SELECT age, name FROM staff;
Notice how the order of the rows changes? Union operates in the most efficient way, so the returned data can vary in order.
A possible use case for union is a subtotal: you could union a query of the sum total onto a query of the individual totals for a particular scenario.
You now know all about retrieving data from a database, but what about inserting it? This is where the insert command comes in. Here’s an example:
INSERT INTO people(name, age) VALUES('Joe', 102);
You have to specify the table name (people), and the columns you wish to use (name and age). The “VALUES” syntax is then used to provide the values to insert. These have to be in the same order as the columns which were previously specified.
You cannot specify a where clause for inserts, and you need to ensure you follow any necessary table constraints that are present.
After inserting some data, it’s only natural to need to change specific rows. Here’s the update command syntax:
UPDATE people SET name = 'Joe', age = 101;
You have to specify the table you wish to change, and then use the “SET” syntax to specify the columns and their new values. This example is good, but it will update every single record — something that’s not always desirable!
In order to be more specific, you can use “WHERE” clauses just like when doing a select:
UPDATE people SET name = 'Joe', age = 101 WHERE name = 'James';
You can even specify multiple conditions using “AND” and “OR”:
UPDATE people SET name = 'Joe', age = 101 WHERE (name = 'James' AND age = 100) OR name = 'Ryan';
Notice how the brackets are used to constrain the conditions.
Upsert is a strange sounding word, but it is an incredibly useful command. Say you have a constraint on your table, and you’ve specified that you only ever want records with unique names — you don’t want to store two rows with the same name, for example. If you tried to insert multiple values of ‘Joe’, your database engine would throw an error and refuse to do it (quite rightly). An UPSERT allows you to update a record if it already exists. This is incredibly useful! Without this command, you would have to write a lot of logic to first check if a record exists, insert if it does not, otherwise retrieve the correct primary key and then update.
Unfortunately, upserts are implemented differently in different database engines. PostgreSQL has only recently gained this ability, whereas MySQL has had it for quite a while. Here’s the MySQL syntax for reference:
INSERT INTO people(name, age) VALUES('Joe', 101) ON DUPLICATE KEY UPDATE age = 101;
Notice how this is essentially an update and an insert statement, which can be summed up as “update if insert failed.”
Delete is used to remove records entirely — it can be quite damaging if abused! The basic syntax is very easy to use:
DELETE FROM people;
Like most of the other commands, this will delete everything! You need to use a where to restrict it to a slightly more sane number of rows — ideally one:
DELETE FROM people WHERE name = 'Joe';
If you’re developing a a system, it’s often wise to implement a “soft delete.” You never actually run the delete command, rather you create a deleted column, and then check that column in your selects — it can avoid a lot of potential embarrassment if you can quickly and easily retrieve supposedly deleted records. This is no substitute for proper backups, however.
11. Create Table
The create table command is used to create tables. It’s another very simple one:
CREATE TABLE people ( name TEXT, age, INTEGER, PRIMARY KEY(name) );
Notice how the column names and constraints are inside brackets, and the columns are given an appropriate datatype. A primary key is specified, as is required in any good database design.
12. Alter Table
The alter table command is used to modify the structure of a table. This is slightly limited, as your database will not let you alter a table if the existing data would cause a conflict — changing a string to an integer, for example. In those instances, fix the data first, then modify the table. Here’s an example:
ALTER TABLE people ADD height integer;
This example adds a column called “height” of type integer to the people table. There’s not really a limit on what you can alter.
13. Drop Table
The final command is drop table. Think of this as delete, but rather than deleting a single record, it removes every single record along with the table! Here’s how you use it:
DROP TABLE people;
It’s quite a drastic command, and there’s no reason it needs to be programmed into your system. It should only be performed manually in the vast majority of cases, and it can be very destructive.
That’s all for today. I hope you learned some useful tricks! You could learn how to make a website, and then use your newfound skills to make it dynamic — just make sure you don’t make these mistakes or leave it vulnerable to SQL injection. If you’re not sure you need to learn SQL, have you considered a static site generator?
Why not leave a comment below with your favorite SQL tips and tricks?
Image Credits: HYS_NP/Shutterstock