MySQL

About this part

Almost every application stores its data in a database: users, orders, payments, messages. MySQL is one of the most widely used databases. In this part you learn it from the ground up.

Part 1 covers installing MySQL, creating databases and tables, inserting rows and every useful form of SELECT: filtering, sorting, grouping and joining. Part 2 covers changing data safely with UPDATE, ALTER and DELETE. It also covers keys and constraints that keep data correct, safe-update mode that stops accidental mass deletes, and user accounts with only the privileges they need.

Example: suppose an e-commerce table has 50,000 orders. Running DELETE FROM orders; without a WHERE clause would remove all of them. Safe-update mode blocks exactly that mistake.

The security link is direct. SQL injection, one of the oldest web attacks, works by sneaking SQL into a query. Once you know how SQL is written, you will understand why prepared statements and least-privilege database users are such strong defences.

Related databases. PostgreSQL uses almost the same SQL, so what you learn here carries straight over. MongoDB and Redis are not SQL databases, but the same rules apply: authentication on, no public ports, and least-privilege users. On the cloud you will meet them as Amazon RDS, Azure Database and Google Cloud SQL.

After this part you will be able to

1. Create databases, tables and users in MySQL.
2. Write SELECT queries with WHERE, ORDER BY, GROUP BY and JOIN.
3. Use safe-update mode to avoid accidental mass changes.
4. Give each app its own user with only the privileges it needs.

A real-world picture

Suppose we are a junior data engineer at Flipkart (sample data) checking yesterday's orders: SELECT city, COUNT(*) FROM orders WHERE order_date = CURDATE() - INTERVAL 1 DAY GROUP BY city;. You would see one row per city with its count. The same SQL knowledge also explains how SQL injection works and why prepared statements stop it. Globally, many large data breaches have involved databases that were exposed or queried unsafely. Everyday analogy: a school register. Anyone can read the roll call, but only the class teacher can change marks.

Beginner questions I get in class

Q. MySQL or MariaDB?
For learning they behave almost the same. MariaDB began as a fork of MySQL. Some newer features differ.

Q. Do I need to memorise SQL?
No. Understand SELECT, WHERE, JOIN and GRANT well, and look up the rest.

Q. Why learn users and privileges?
Because most real damage from an attack depends on what the database user is allowed to do.

Key terms

• Primary key: A column that uniquely identifies each row.
• Foreign key: A column that must match a key in another table.
• JOIN: Combines rows from two tables on a matching column.
• GRANT: Gives a user specific rights on specific objects.
• Safe-update mode: Blocks UPDATE or DELETE without a key-based WHERE clause.
• SQL injection: Attacker input changing the meaning of a query. Prevented with prepared statements.

Mini case study

Suppose we are a junior analyst at Tata Consultancy Services, asked to review a new web server on AWS before launch (with the client's written approval). The checklist from this part becomes a short report: what you checked, what you found (for example "port 22 open to 0.0.0.0/0" or "directory listing on"), the evidence (a command and its output), and the fix. Clear, evidence-based reports like this are how defenders work every day.