Getting Started with MySQL
Part 2: Basics
Index
- Part 1: Introduction
- Part 2: Basics (you are here)
I’m using this tutorial as reference.
In this part, we will learn MySQL by creating a simple Instagram database clone. You can find the complete code here.
Here’s the tables we are going to create.
- users
- photos
- comments: user can comment on someone’s photo
- likes
- tags
- photo_tags
- follows
To start, let’s create a database named instagram. Then we will tell MySQL that we are going to use this database.
Users
The schema of users table should have these fields.
- id
- username
- created_at
Photos
The schema of photos table should have these fields.
- id
- image_url
- user_id: id of the user who uploaded the photo
- created_at
Note photos table has a foreign key pointing to users table. One user can have many photos. We call this one-to-many relationship.
Comments
The schema of comments table should have these fields.
- id
- comment_text
- user_id
- photo_id
- created_at
Likes
The schema of likes table should have these fields.
- user_id
- photo_id
- created_at
Follows
The schema of follows table should have these fields.
- follower_id: user who is following
- followee_id: use who is being followed
- created_at
So here, a follower follows a followee.
Hashtags
To implement hashtags, we will first create a table tags, which stores all the hashtags we can add to a photo. Then we create another table photo_tags, which stores the tags associated with a photo.
So the schema of tags table should have these fields.
- id
- tag_name
- created_at
And the schema of photo_tags should have these fields.
- photo_id
- tag_id
Note a photo can have many tags, and a tag can have many photos. This relationship between photo and tag is called many-to-many.
Case Study
We will study various commands available in MySQL through examples. To start, we will insert some data to our database.
Case study 1: Find the 5 oldest users.
Case study 2: What day of the week do most users register on?
Case study 3: Find the users who have never posted a photo.
Case study 4: Who posted the photo that has the most likes?
Case study 5: How many times does user post on average?
Case study 6: What are the top 5 most commonly used hashtags?
Case study 7: Find users who have liked every single photo on the site.
