Pinterest Stumbleupon Whatsapp
Advertisement

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 7 Programming Skills All Website Developers Should Know 7 Programming Skills All Website Developers Should Know Website design and development offers good pay and benefits, decent working hours, and the prospect of advancement. We'll show you which essential skills you can start learning now to get into this exciting field. Read More 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.

Preface

All of the examples today will be based on four fictional tables. The customer table contains the name and age of customers:

SQL Table Customers

The heights table contains the name and height of any person:

Advertisement

SQL Table Heights

The staff table contains the name and age of staff members — exactly the same as the customer table:

SQL Staff Table

The final table called people contains the name and age of people, just like the customer and staff tables:

People Table

1. Select

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;

SQL Select

Any string inside single quotes will be returned instead of matching a column name.

2. Where

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;

SQL Where

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';

SQL Where

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).

3. Order

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;

SQL Order

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

SQL Order

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.

4. Join

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);

SQL Join

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.

5. Alias

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;

SQL Alias

When this query is run, the column will now be called “person_age” instead of “age”.

6. Union

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;

SQL Union

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;

SQL Union

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.

7. Insert

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.

8. Update

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.

9. Upsert

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.”

10. Delete

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 How to Make a Website: For Beginners How to Make a Website: For Beginners Today I'll be guiding you through the process of making a complete website from scratch. Don't worry if this sounds difficult. I'll guide you through it every step of the way. Read More , and then use your newfound skills to make it dynamic — just make sure you don’t make these mistakes 9 Mistakes You Shouldn't Make When Building a Web Page 9 Mistakes You Shouldn't Make When Building a Web Page These following HTML coding mistakes are easy to make, but if you head them off earlier rather than later, your page will look better, be easier to maintain, and function how you want it to. Read More or leave it vulnerable to SQL injection What Is An SQL Injection? [MakeUseOf Explains] What Is An SQL Injection? [MakeUseOf Explains] The world of Internet security is plagued with open ports, backdoors, security holes, Trojans, worms, firewall vulnerabilities and a slew of other issues that keep us all on our toes every day. For private users,... Read More . If you’re not sure you need to learn SQL, have you considered a static site generator 7 Reasons to Ditch Your CMS and Consider a Static Site Generator 7 Reasons to Ditch Your CMS and Consider a Static Site Generator For many years, publishing a website was difficult for many users. CMSs like WordPress changed that, but they can still be confusing. Another alternative is a Static Site Generator. Read More ?

Why not leave a comment below with your favorite SQL tips and tricks?

Image Credits: HYS_NP/Shutterstock

Leave a Reply

Your email address will not be published. Required fields are marked *

  1. MrX
    June 25, 2017 at 5:41 am

    All programmers should know these basic commands.