You are My-SQL 🧡

This article assumes you're a beginner—even if you aren't. Just grab a coffee and let's begin. ☕ 

Why Database? 

In the IT industry, everything revolves around data—collecting it, cleaning it, manipulating it, visualizing it, making sense out of it, and even predicting outcomes based on it. Data is the backbone of modern applications, and we need a structured way to store it—that's where databases come into play. 

There are two primary types of databases: SQL (Structured Query Language) databases and NoSQL (Not Only SQL) databases. 

• SQL Databases (Relational Databases) are structured as tables containing rows and columns. Examples include MySQL, MariaDB, PostgreSQL, and Oracle DB. 
• NoSQL Databases store data in formats like BSON (Binary JSON). A popular example is MongoDB, which is widely used in modern applications. 
• Vector Databases are a relatively new category designed for storing and searching high-dimensional vector embeddings. These are used in AI/ML applications, but for now, we'll focus on MySQL. 

What is SQL? 

SQL (Structured Query Language) is the language used to query, retrieve, and manipulate data in relational databases. It enables users to interact with stored data efficiently. 

Brewing

• Head over to the official MySQL download page: MySQL Downloads 
• Install MySQL on your PC following the setup instructions. 
• Run the installer, accept the terms and conditions, and configure it. If you face any issues, search for your OS-specific installation guides on Google or YouTube. 

Accessing MySQL 

Once installed, open your terminal and enter:

 

mysql -u root -p

• -u root specifies the user (root is the default administrator).

• -p prompts for a password (enter the one you set during installation).

After logging in, check existing databases with:

SHOW DATABASES;

This lists all available databases, including system-generated ones. You can play with.

Note: SQL commands are case-insensitive, but using uppercase for keywords (SELECT, FROM, etc.) is a common practice. The semi-colon (;) at the end of commands is required—it marks the end of an SQL statement.

Let's create and manage DataBase in MySQL

Create a Database:

CREATE DATABASE LabDB;

If you see an "OK" message, you're good to go.

• To use this database run:

use LabDB;

Check for tables (initially, none will exist):

show tables;

A database contains tables, and a table consists of columns (fields) and rows (records).

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(255) NOT NULL,
    Age INT,
    Department VARCHAR(50)
);

Here, PRIMARY KEY ensures each record has a unique identifier.

INT represents an integer data type.

VARCHAR(n) stores strings (up to n characters).

NOT NULL enforces that a column must have a value.

Describe your table, do 'desc' with table name:

desc Students;

Let's insert data into Table by:

INSERT INTO Students (StudentID, Name, Age, Department)  
VALUES 
(1, 'Alice', 20, 'Computer Science'),
(2, 'Bob', 22, 'Physics'),
(3, 'Charlie', 19, 'Mathematics');

Here, we're simply adding values manually respective to the field type. Notice VarChar field gets a String, Int type got an Integer value.

• Created Table, added Values. Now, Let's take a look 👀

SELECT * FROM Students;

'*' means all. Select all from Table Name(Students).

📌 Output:

StudentID	Name	Age	Department
1	Alice	20	Computer Science
2	Bob	22	Physics
3	Charlie	19	Mathematics

Update and Delete Data

• To update a record:

UPDATE Students SET Age = 21 WHERE StudentID = 1;

This updates Alice's age to 21.

• To delete Charlie's record:

DELETE FROM Students WHERE StudentID = 3;

• Use 'Truncate' to remove all records without deleting table

TRUNCATE TABLE Students;

• To modify a table structure :

ALTER TABLE Students ADD Email VARCHAR(100);

It says to add Email field of type string in our 'Students' table.

Filtering Data with WHERE, AND, OR:

Use 'where' clause to query specific data :

SELECT * FROM Students WHERE Department = 'Computer Science';

Fetches students from the Computer Science department.

- AND, OR o

• Using AND/OR operator:

SELECT * FROM Students WHERE Age > 18 AND Department = 'Physics';

Says to select those students whose age is above 18 and belongs to Physics department.

SELECT * FROM Students WHERE Age < 18 OR Department = 'Biology';

Guess what this does? 😉 (Try it out!)

See, I feel SQL commands are pretty much self-explanatory and sort of writing English which makes it easy to master.

Using IN, BETWEEN, LIKE, GROUPBY, ORDERBY, HAVING

• IN ( Multiple values matching)

SELECT * FROM Students WHERE Department IN ('Computer Science', 'Biology');

Fetches students from either department.

• BETWEEN (To query between a range)

SELECT * FROM Students WHERE Age BETWEEN 18 AND 22;

Fetches students whose age is between 18 and 22 (inclusive).

• LIKE (Pattern matching ):

SELECT * FROM Students WHERE Name LIKE 'A%';

Finds names starting with 'A'.
(% is a wildcard representing any number of characters).

• ORDER BY(Sorting purpose)

SELECT * FROM Students ORDER BY Age DESC;

By default ORDER BY arranges your data in Ascending order.

• Group By and Having:

SELECT Department, COUNT(*) FROM Students GROUP BY Department HAVING COUNT(*) > 5;

Using Aggregate Functions:

• To get the average:

Select avg(Age) from Students;

• To get the Max age:

SELECT MAX(Age) FROM Students;

There are others as well.

Creating Views and Column Aliases:

-  View is a virtual table that stores frequently used queries.

CREATE VIEW CS_Students AS  
SELECT * FROM Students WHERE Department = 'Computer Science';

Now, you can retrieve only Computer Science students just by using:,

SELECT * FROM CS_Students;

• Column Aliases is a way to change how columns are displayed.

SELECT Name AS Student_Name, Age AS Student_Age FROM Students;

📌 Output:

Student_Name	Student_Age
Alice	21

• To delete a table entirely:

DROP TABLE Students;

What's Next?

You've learned how to create, insert, retrieve, update, and delete data in MySQL. Now, try JOINs on your own!

🚀 Challenge:

1. Create another table (Courses).

2. Use INNER JOIN, LEFT JOIN, RIGHT JOIN to combine data.

3. Explore user management (GRANT, REVOKE).

Post your results in the comments! See you in the next post—another ☕ awaits.