The essentials for working with databases.
What is MySQL?
MySQL is a relational database. It stores information in tables made up of rows and columns.
You use SQL (Structured Query Language) to work with that data.
Think:
Database
└── Table
├── Column
├── Column
└── Row
1. Connect to MySQL
From your terminal:
mysql -u root -p
You'll be asked for your password.
To connect to a specific database:
mysql -u root -p my_database
2. See the Databases
SHOW DATABASES;
Choose a database:
USE my_database;
Check which database you're using:
SELECT DATABASE();
3. See the Tables
SHOW TABLES;
See how a table is structured:
DESCRIBE users;
or:
DESC users;
4. Read Data
Get everything:
SELECT * FROM users;
Usually, it's better to select only what you need:
SELECT id, name, email
FROM users;
5. Filter Data
Use WHERE:
SELECT *
FROM users
WHERE id = 10;
Text values use quotes:
SELECT *
FROM users
WHERE name = 'Jacob';
Multiple conditions:
SELECT *
FROM users
WHERE active = 1
AND country = 'Malawi';
6. Limit Results
When a table is large, don't immediately ask for everything.
SELECT *
FROM users
LIMIT 10;
You can combine it with filtering:
SELECT *
FROM users
WHERE active = 1
LIMIT 20;
Good habit: When exploring an unfamiliar table, start with LIMIT.
7. Sort Results
Ascending:
SELECT *
FROM users
ORDER BY name ASC;
Descending:
SELECT *
FROM users
ORDER BY created_at DESC;
DESC is especially useful for finding the newest records.
8. Add Data
Use INSERT:
INSERT INTO users (name, email)
VALUES ('Jacob', 'jacob@example.com');
Multiple rows:
INSERT INTO users (name, email)
VALUES
('Jacob', 'jacob@example.com'),
('Mary', 'mary@example.com');
9. Change Data
Use UPDATE:
UPDATE users
SET active = 0
WHERE id = 10;
⚠️ Always be careful with UPDATE.
This:
UPDATE users
SET active = 0;
changes every row.
Get into the habit of writing and checking your WHERE clause.
10. Delete Data
DELETE FROM users
WHERE id = 10;
⚠️ This is permanent unless you have a backup or transaction you can roll back.
Never casually run:
DELETE FROM users;
That deletes all rows.
11. Count Things
Count all rows:
SELECT COUNT(*)
FROM users;
Count matching rows:
SELECT COUNT(*)
FROM users
WHERE active = 1;
12. Search for Text
Exact match:
WHERE name = 'Jacob'
Partial match:
WHERE name LIKE '%Jacob%';
Examples:
LIKE 'Jacob%'
Starts with Jacob
LIKE '%Jacob'
Ends with Jacob
LIKE '%Jacob%'
Contains Jacob
13. NULL Is Different
NULL means no value / unknown value.
Don't use:
WHERE email = NULL;
Use:
WHERE email IS NULL;
Or:
WHERE email IS NOT NULL;
14. Create a Database
CREATE DATABASE my_database;
Then:
USE my_database;
15. Create a Table
A simple example:
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(255),
active BOOLEAN
);
Common types:
| Type | Used for |
|---|---|
INT | Whole numbers |
DECIMAL | Money / precise numbers |
VARCHAR | Short text |
TEXT | Longer text |
DATE | Dates |
DATETIME | Date + time |
BOOLEAN | True / false |
16. The Primary Key
A primary key uniquely identifies each row.
Example:
id INT AUTO_INCREMENT PRIMARY KEY
So your data might look like:
| id | name | |
|---|---|---|
| 1 | Jacob | jacob@example.com |
| 2 | Mary | mary@example.com |
| 3 | John | john@example.com |
The id identifies each record.
17. Relationships
Databases often have related tables.
For example:
users
|
└── orders
An order can belong to a user.
You can combine related data using JOIN.
SELECT
users.name,
orders.amount
FROM users
JOIN orders
ON orders.user_id = users.id;
This is one of the most important things to learn after basic SQL.
18. Exit MySQL
EXIT;
or:
QUIT;
The 15 Things to Remember
| What you want | Command |
|---|---|
| Connect | mysql -u root -p |
| List databases | SHOW DATABASES; |
| Select database | USE database; |
| List tables | SHOW TABLES; |
| Describe table | DESC table; |
| Read data | SELECT * FROM table; |
| Filter | WHERE |
| Sort | ORDER BY |
| Limit | LIMIT |
| Add data | INSERT |
| Change data | UPDATE |
| Delete data | DELETE |
| Count | COUNT(*) |
| Join tables | JOIN |
| Exit | EXIT; |
A Beginner's Workflow
Imagine you're working with a database you have never seen before.
1. Connect
mysql -u root -p
2. See the databases
SHOW DATABASES;
3. Choose one
USE my_database;
4. See its tables
SHOW TABLES;
5. Inspect a table
DESC users;
6. Look at a few records
SELECT *
FROM users
LIMIT 10;
7. Start filtering
SELECT *
FROM users
WHERE active = 1
LIMIT 10;
That's the basic loop you'll use all the time.
⚠️ The Golden Rules
1. Don't blindly run UPDATE or DELETE.
Always check your WHERE.
2. Don't start with SELECT * on huge tables.
Use:
LIMIT 10;
3. Never experiment on production data.
Use a development/test database whenever possible.
4. Backups matter.
A database is data—not just code you can recreate.
5. SQL keywords aren't scary.
You don't need to memorize everything.
Start with:
SELECT
FROM
WHERE
ORDER BY
LIMIT
INSERT
UPDATE
DELETE
JOIN
Learn the rest as you need it.
