SQL - The basics

Formatting:

In the following examples, specific parts of the queries are marked to make it more understandable:

table name field name value

Security information

When you use user input (for example from a search box) inside a statement, make sure to sanitize (filter special chars like " or ') the input. Depending on your programming language, you can use Prepared Statements for more security. 

Don't trust ANY user input!

You know this already and want to do some more advanced stuff?

Check out the SQL - Advanced article.

1. Create a table

To get started, create a table:

CREATE TABLE IF NOT EXISTS users (
   id INT NOT NULL,
   name VARCHAR(200)
);

This results in a table called users with 2 columns: id and name

Note: Do NOT include any special chars in the column names! Usually they are named for example "user", "user_name", ...

2. Insert data

Currently, the table is empty, let's change this by inserting a few rows:

INSERT INTO users (id, name) VALUES (1, "John");
INSERT INTO users (id, name) VALUES (2, "Sam");

3. Read data

Stored data is awesome, so the next step is to read the data. Reading means selecting in SQL. 

SELECT id, name FROM users

With this request, we ask the database to return the fields "id" and "name" of all rows (because we set no limit) from the table "users".

Result:

Note: You can replace the fields with "*" (SELECT * FROM ...), but that's not recommended for security reasons. Try avoiding "*" and write the required fields manually into the query.

Because selecting data is the most complicated part when it comes to SQL, here are a few more examples of what you can do with SELECT.

Note: When using values in statements, remember writing strings in quotes ( "hello world" ) and numbers without ( 1 ).

3.1 Limit and Offset

If you want only a specific amount of data and/or add an offset (for example for a pagination system), use LIMIT:

SELECT name FROM users LIMIT 1

This will set a limit of 1, so we get only 1 row as result:

Now imagine you have a pagination system. Because we only have 2 rows of data, let's say each page has 1 entry. You can use the following statements to get data for a specific page:

Query 1: SELECT name FROM users LIMIT 0, 1
Query 2: SELECT name FROM users LIMIT 1, 1

The first number (LIMIT 0, 1) describes how many rows should be skipped, in this case, 0. In the second query the first number after LIMIT is 1, so the query skips the first entry. The second number is the number of rows we want because it's in both queries 1, we'll get in both queries one row as result.

Result: Query 1 returns "John", query 2 "Sam".

3.2 Filter with WHERE

When working on big projects, you usually use WHERE in nearly every SELECT Statement you execute. WHERE allows you to only include rows in the result that are matching the given expression. As an example, let's say we have a page on our website where we can get the name of a specific user by his ID:

SELECT name FROM users WHERE id = 1

This will only return rows, where the id is equal to 1. We can also do this with the name:

SELECT id FROM users WHERE name = "John"

You can combine multiple expressions with AND:

SELECT name FROM users WHERE id = 1 AND name = "JOHN"

That's great! But now imagine we have a search, where the user can type anything and we have to return a list of matching users. Previously, we used = (equals) in the expressions, but we can also use LIKE. When using LIKE, we can add % to the value which stands for anything.

For example, "John%" would match for example "John", "John Smith", "Johnson", but not "Mr John" or "Joh".

Now implement this into our query:

SELECT id, name FROM users WHERE name LIKE "Jo%"

The result would be:

This article is covering the basics. There is much more than just = and LIKE, you can also do number comparisons with > or < and more.

3.3 Counting

Now our website is working, but as administrator, we need a possibility to see how many users we currently have. We can count entries with COUNT:

SELECT COUNT(id) FROM users

The result would be:

3.4 Ordering

Time to order! To create something like an alphabetical list of all users, we have to order the result:

SELECT id, name FROM users ORDER BY name ASC

The result would be:

We can also revert it by using DESC:

SELECT id, name FROM users ORDER BY name DESC

The result would be:

It's also possible to combine multiple ORDER expressions:

SELECT id, name FROM users ORDER BY id DESC, name ASC

This will order first by id descending, then by name ascending.

3.5 Combining

Well, that's a lot of features. But how do we combine them? What if we want to check how users start with "Jo"? The solution is... just append it :) Just be careful of the order. The example below is a combination of WHERE, LIMIT, and ORDER. If you don't need for example LIMIT, just remove this part.

SELECT id FROM users WHERE name LIKE "Jo%" ORDER BY name ASC LIMIT 0, 1

Just for your info, this statement would return the row with the id 1 (because we have 1 user starting with "Jo" and the id of this row is 1).

3.6 Universal!

The last steps may look a bit complicated and hard to learn - but remember that they can be used (and in the real world are used) in not just SELECT, but also in UPDATE and DELETE statements. You can for example delete all users if their name starts with "Jo" (WHERE) or update a maximum of 5 users (LIMIT).

4. Update

Ok, now you know the most important stuff of SELECT statements, it's now time to use that knowledge for updating a user. For example, rename "Sam" to "Dave":

UPDATE users SET name = "Dave" WHERE name = "Sam"

Our table should now look like this:

Update multiple columns at once by separating with ",":

UPDATE users SET name = "Dave", id = 2 WHERE name = "John"

That's all about update statements! As mentioned in 3.6, you can use other stuff like LIMIT in update statements as well.

5. Delete

From a marketing perspective, it might be not that smart to delete customer data like mail addresses, but before your server runs out of storage and you have to free up disk space, you should remove entries from your database. It's pretty simple - just like UPDATE:

DELETE FROM users WHERE name = "Dave"

Now, our table looks like this:

Finished? Well, time for some more advanced stuff!

Check out the SQL - Advanced article.