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".
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:
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
namewhich is a varchar/string with a max length of 255, can't be null
usernamewhich 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
Now we have the database and table ready, let's create our first user. Insertions are made with the
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 everything (*) */ SELECT * FROM users; /* Select only specific columns */ SELECT name, username FROM users;
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 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;
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...
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 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:
SELECTWhich columns you want to select
FROMWhich table you want to query
WHEREWhich rows you want to select
GROUPHow you want to group the rows
ORDERIn which order you want the result
LIMITHow many rows you want
Updating rows is just as easy as inserting new ones. Use
UPDATE to update a row and use
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".
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:
posts table is referencing users from the
users table in the
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?
Inside one SQL query, we can join other tables to access their data as well.
There are different types of joins:
INNER join connects all rows that are in both tables. If a user has no posts, that user gets skipped.
LEFT join uses the first table as basis and connects, if possible, the second table.
RIGHT join is basically the opposite of a left join, here is the second table the basis.
FULL OUTER join connects both tables and missing data is represented as
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;
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
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";
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
Thanks for reading 🤗