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 nullname which is a varchar/string with a max length of 255, can't be nullusername 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 * FROM users;
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.
SELECT * FROM users
WHERE username = "EinLinuus";
SELECT * FROM users
WHERE id = 2;
SELECT * FROM users
WHERE id = 1 AND name = "Linus";
There are many operators you can use here:
SELECT * FROM users
WHERE username != "EinLinuus";
SELECT * FROM users
WHERE username LIKE "%Linuus%";
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).
SELECT * FROM users LIMIT 1;
SELECT * FROM users LIMIT 1, 5;
Ordering
Ordering is possible too, just use ORDER BY followed by the column name and the direction.
SELECT * FROM users ORDER BY id ASC;
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 selectFROM Which table you want to queryWHERE Which rows you want to selectGROUP How you want to group the rowsORDER In which order you want the resultLIMIT 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 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 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:
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
- Edit tables / Alter
- Delete tables / Drop
- Security: SQL Injections
SQL: The basics and beyond (Part 2)
Thanks for reading 🤗
