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 (seePRIMARY 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
- Edit tables / Alter
- Delete tables / Drop
- Security: SQL Injections
SQL: The basics and beyond (Part 2)
Thanks for reading π€
Top comments (0)