# SQL: The basics and beyond

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.


![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1643558682877/zT8TYBiWw.png)

### 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.

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1643558801573/mVC1cvOEv.png)

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](https://sql.webaze.net/)

```sql
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:

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

We can also insert multiple rows at once:

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

This should give you the following result:

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1643559980052/uFtGDpnin.png)

---

## 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:

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

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

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1643560292006/3iJ1EMsp_.png)

### 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.

```sql
/* 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";
```

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1643560634948/mmi_fkynx.png)

There are many operators you can use here:

```sql
/* 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;
```

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1643560813914/4mtm2EQpl.png)

### 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).

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

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

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1643561167405/5FRh841J1.png)

### Ordering

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

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

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

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1643561485190/xT_aHC2op.png)

You can also order by multiple columns:

```sql
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:

```sql
SELECT COUNT(id) FROM users;
```

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1643562251878/Tm1xUFAFk.png)

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:

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

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1643562236935/bCgj-xVJd.png)

### Grouping

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

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

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1643562534872/y2a1gSc6-.png)

### Ordering inside the query

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

1. `SELECT` Which columns you want to select
1. `FROM` Which table you want to query
1. `WHERE` Which rows you want to select
1. `GROUP` How you want to group the rows
1. `ORDER` In which order you want the result
1. `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.

```sql
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.

```sql
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:

```sql
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:

```sql
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:

```sql
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.

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1643566673720/fRc-ec77b.png)

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

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

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1643566910277/Bk-ixGls8.png)

---

## 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:**
1. Relations: One-to-Many and Many-to-Many
1. Working with dates (MySQL)
1. The check constraint
1. Working with tables
  1. Edit tables / Alter
  1. Delete tables / Drop
1. Security: SQL Injections

[SQL: The basics and beyond (Part 2)](https://www.buymeacoffee.com/EinLinuus/sql-the-full-guide)

---

Thanks for reading 🤗

%%[bmac]