EinLinuus

Posted on Jul 7, 2022 • Originally published at linu.us

SQL: The basics and beyond

#sql #beginners #database

Welcome back 👋

Today we'll take a look at SQL, starting with the basics and then moving forwards to more complicated concepts and queries. But before we start writing the first query, make sure you're familiar with the core concepts of SQL and relational databases. Making queries is important, but knowing how to structure a database even more.

What is SQL?

First things first, what the hell is SQL? If you don't know already, SQL stands for "Structured Query Language" and is a database query language. It's used to interact with databases like MySQL, Postgres or SQLite. The data in relational databases is organized in tables and rows.

How the data is organized

You store your data in a database that can contain many tables. For example, the database "blog" contains the tables "users" and "posts". Each table has many columns. The "posts" table may have the columns "title", "image" and "description".

Relations

In relational databases, there are mostly no duplications. If we use the example above, we don't store the user information in the "posts" table, because one user can have multiple posts, which means the user information would be stored multiple times in the table. Instead, we store the user information in a separate table "users" and reference the user in the "posts" table.

Primary and foreign keys

In order to reference something, we need a unique identifier of the row we want to reference.

Note: Using something like the username may seems logical, but I highly recommend to use a separate, unique ID for each user that can't be changed. A username is unique, but if a user changes the username, you'd need to change the username everywhere you referenced it.

This unique identifier is called a primary key. When you use it as a reference, for example in the "posts" table, it's called a foreign key.

Conclusion: A foreign key references a primary key in another table that's unique for that table.

Create the database

To get started, we first need a database. You can install for example MySQL on your local machine, if available on a server or rent a database online.

But if you're just starting and want to follow along with this guide, you can use my SQL playground, based on SQLite:

Open the SQL-Playground on sql.webaze.net

CREATE TABLE IF NOT EXISTS users (
    id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    username VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

Executing this query will create a database called users and the columns

id (Integer) which is the primary key (see PRIMARY KEY (id)) and can't be null
name which is a varchar/string with a max length of 255, can't be null
username which is also a varchar/string with a max length of 255, can't be null

To prevent errors if the table already exists, I used CREATE TABLE IF NOT EXISTS instead of just CREATE TABLE.

Insert data

Now we have the database and table ready, let's create our first user. Insertions are made with the INSERT statement:

INSERT INTO users (id, name, username) VALUES (1, "Linus", "EinLinuus");

We can also insert multiple rows at once:

INSERT INTO
    users (id, name, username)
VALUES
    (1, "Linus", "EinLinuus"),
    (2, "John", "John_Doe");

This should give you the following result:

Query the users

With a few rows in our table, we can now query them. To select data from the database, use the SELECT statement:

/* Select everything (*) */
SELECT * FROM users;

/* Select only specific columns */
SELECT name, username FROM users;

Filtering

Getting all users at once may be useful in some cases, but most often you want to filter them, for example get a specific user by the username or search through them with a search keyword.

/* Get all users with the username "EinLinuus"  */
SELECT * FROM users
WHERE username = "EinLinuus";

/* Get the user with the id 2 */
SELECT * FROM users
WHERE id = 2;

/* Combinations with AND and OR */
SELECT * FROM users
WHERE id = 1 AND name = "Linus";

There are many operators you can use here:

/* is not */
SELECT * FROM users
WHERE username != "EinLinuus";

/* LIKE (% stands for anything) */
SELECT * FROM users
WHERE username LIKE "%Linuus%";

/* Greater / Less than */
SELECT * FROM users
WHERE id > 1;

Limits and pagination

To limit or offset your results, use LIMIT. You can use it simply limit the amount of rows returned, or to offset it (return X rows, but skip the first Y rows).

/* Return only 1 row */
SELECT * FROM users LIMIT 1;

/* Return only 5 rows and skip 1 row */
SELECT * FROM users LIMIT 1, 5;

Ordering

Ordering is possible too, just use ORDER BY followed by the column name and the direction.

/* Order ascending by id */
SELECT * FROM users ORDER BY id ASC;

/* Order descending by id */
SELECT * FROM users ORDER BY id DESC;

You can also order by multiple columns:

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

Counting

Now if your multi-billion startup idea starts getting users, you might want to know how many users exactly:

SELECT COUNT(id) FROM users;

This will return one row with the count. But, the column name in the result is COUNT(id). You might want to have something more readable here, and that's why you can...

Rename columns

To rename a column, just use AS followed by the new name:

SELECT id AS user_id, username FROM users;
SELECT COUNT(id) as total_users FROM users;

Grouping

Grouping allows you to group multiple rows, for example we can get the amount of users with the same name:

SELECT COUNT(id) AS users, name
FROM users GROUP BY name;

Ordering inside the query

If you want to combine multiple of the above, make sure you respect this order:

SELECT Which columns you want to select
FROM Which table you want to query
WHERE Which rows you want to select
GROUP How you want to group the rows
ORDER In which order you want the result
LIMIT How many rows you want

Update rows

Updating rows is just as easy as inserting new ones. Use UPDATE to update a row and use WHERE and LIMIT from the SELECT guide to specify which rows you want to update.

UPDATE users
SET username = "JohnDoe"
WHERE id = 2;

/* Update multiple columns */
UPDATE users
SET name= "John D", username = "JohnDoe"
WHERE id = 2;

The query above changes the username user 2 to "JohnDoe".

Delete rows

Deleting works nearly the same, with the only difference that you just specify a table, because you're deleting the whole row.

DELETE FROM users
WHERE id = 2;

Work with multiple tables

Things get much more interesting when we work with multiple tables. Let's add another table to our example:

CREATE TABLE IF NOT EXISTS posts (
    id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    user INT NOT NULL,
    PRIMARY KEY (id)
);

/* Insert some dummy data */
INSERT INTO posts (id, title, user) VALUES
(1, "Post 1", 1),
(2, "Post 2", 2),
(3, "Post 3", 2),
(4, "Post 4", 1);

If you followed along, you know how to get all posts, filter, order, update and delete them. But you may have noticed the connection between both tables:
The posts table is referencing users from the users table in the posts.user column.

Let's say we want to display all posts on a website. We create a simple SELECT statement to query all available posts:

SELECT * FROM posts;

This works, but we want to show the author's name as well. How can we do that?

Table joins

Inside one SQL query, we can join other tables to access their data as well.

There are different types of joins:

A INNER join connects all rows that are in both tables. If a user has no posts, that user gets skipped.

A LEFT join uses the first table as basis and connects, if possible, the second table.
The RIGHT join is basically the opposite of a left join, here is the second table the basis.

A FULL / FULL OUTER join connects both tables and missing data is represented as NULL.

Now, let's create a query with a JOIN to get all posts and their authors:

SELECT * FROM users
JOIN posts ON posts.user = users.id;

The ON field = otherField part tells the database which row(s) to select in the table we're joining. In this case, the column user in the posts table references a id in the users table.

We can also use the data from the other table inside our WHERE clause:

/* posts.* = Everything from the posts table */
SELECT posts.* FROM users
JOIN posts ON posts.user = users.id
WHERE users.name = "EinLinuus";

Beyond this

SQL is very powerful and can do much more than what I described in this article. Because it's a lot of work to write something like this, you can find the next part of this guide on my BMC-Page. Thank you very much ❤️

Content of Part 2:

Relations: One-to-Many and Many-to-Many
Working with dates (MySQL)
The check constraint
Working with tables
1. Edit tables / Alter
2. Delete tables / Drop
Security: SQL Injections

SQL: The basics and beyond (Part 2)

Thanks for reading 🤗

DEV Community