What is MySQL? It's a Database Management System (DBMS). Think of it as software that helps you create, manage, and access databases. Specifically, MySQL is a Relational Database Management System (RDBMS). This means it organizes data into tables with relationships between them.

Why Backend Devs Need It? Most applications need to store and retrieve data persistently. Imagine social media posts, user profiles, product information in an online store, etc. MySQL is excellent for this because:
- Organized Data: Tables make it easy to structure data logically.
- Fast Retrieval: MySQL is designed for quick access to data.
- Reliable: It's robust and can handle lots of data and users.
- Widely Used: Lots of companies use it, so knowing it is a valuable skill.
- Open Source (mostly): It's free to use in many cases.

Core Concepts: The Building Blocks

Databases and Tables:
- Database: Think of a database as the entire filing cabinet itself. It's a container for all your application's data. You can have multiple databases for different parts of your application or different applications altogether.
- Table: Inside a database, you have tables. Think of tables like folders within the filing cabinet. Each table stores a specific type of data, like "users," "products," "orders," etc. Tables are made up of rows and columns.
- Rows (Records): Each row represents a single item of data in a table. Like one user's information, one product's details, or one order.
- Columns (Fields, Attributes): Columns define the type of data you store in each row. For example, in a "users" table, columns might be "user_id," "username," "email," "password."
Data Types:
- When you create columns, you tell MySQL what kind of data will go in that column. This is called the data type. Common types include:
  - INT (Integer): Whole numbers (e.g., user IDs, quantities).
  - VARCHAR(length): Strings of text of varying length (e.g., usernames, product names). You specify the maximum length.
  - TEXT: Longer strings of text (e.g., descriptions, blog post content).
  - DATE: Dates only (e.g., date of birth).
  - DATETIME: Dates and times (e.g., order timestamps).
  - BOOLEAN/TINYINT(1): True/False values (often stored as 1 for true, 0 for false).
  - DECIMAL(precision, scale): Numbers with decimal points, good for currency (e.g., prices).
SQL - The Language of Databases:
- SQL (Structured Query Language) is the standard language to talk to databases like MySQL. You use SQL to:
  - Create databases and tables.
  - Insert new data into tables.
  - Read (query) data from tables.
  - Update existing data in tables.
  - Delete data from tables.
- We'll learn the basic SQL commands below.

Essential SQL Commands (CRUD Operations)

CRUD stands for Create, Read, Update, Delete. These are the fundamental operations you'll perform on data in your database.

CREATE (Creating Databases and Tables):
- Creating a Database:
```
        CREATE DATABASE my_application_db;
```
- Creating a Table:
```
        CREATE TABLE users (
      user_id INT PRIMARY KEY AUTO_INCREMENT,  -- Unique ID, automatically increases
      username VARCHAR(50) NOT NULL UNIQUE,   -- Username, max 50 chars, must exist, must be unique
      email VARCHAR(100) NOT NULL UNIQUE,    -- Email, max 100 chars, must exist, must be unique
      password VARCHAR(255) NOT NULL,         -- Password, max 255 chars, must exist
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP -- When the user was created, defaults to now
  );
```
  IGNORE_WHEN_COPYING_START
  
  content_copy download
  
  Use code with caution.SQL
  
  IGNORE_WHEN_COPYING_END
  - CREATE TABLE table_name (...): Starts table creation.
  - column_name DATA_TYPE [constraints]: Defines each column.
  - PRIMARY KEY: Uniquely identifies each row in the table. Good for id columns.
  - AUTO_INCREMENT: Automatically increases the value for new rows (usually for primary keys).
  - NOT NULL: Column cannot be empty.
  - UNIQUE: Values in this column must be unique across all rows.
  - DEFAULT value: Sets a default value if none is provided when inserting data.
  - CURRENT_TIMESTAMP: Sets the default value to the current date and time.
READ (Selecting Data):
- SELECT statement: Used to retrieve data from tables.
  - SELECT FROM users;*: Selects all columns (*) from the users table.
  - SELECT username, email FROM users;: Selects only the username and email columns from users.
  - SELECT * FROM users WHERE user_id = 10;: Selects all columns from users where the user_id is 10 (filtering).
  - SELECT * FROM users WHERE username LIKE 'joh%';: Selects users whose username starts with "joh" (LIKE for pattern matching, % is a wildcard).
  - SELECT * FROM users ORDER BY username ASC;: Selects users and sorts them alphabetically by username (ASC for ascending, DESC for descending).
  - SELECT COUNT(*) FROM users;: Counts the total number of users.
  - SELECT MAX(user_id) FROM users;: Finds the maximum user_id.
  - SELECT AVG(user_id) FROM users;: Calculates the average user_id.
  - SELECT * FROM users LIMIT 10;: Selects only the first 10 users.
  - SELECT * FROM users LIMIT 10 OFFSET 20;: Selects 10 users starting from the 21st user (skips the first 20).
INSERT (Adding Data):
- INSERT INTO statement: Adds new rows to a table.
```
        INSERT INTO users (username, email, password) VALUES ('johndoe', 'john.doe@example.com', 'password123');
```
  IGNORE_WHEN_COPYING_START
  
  content_copy download
  
  Use code with caution.SQL
  
  IGNORE_WHEN_COPYING_END
  - INSERT INTO table_name (column1, column2, ...): Specifies the table and columns you're inserting into.
  - VALUES (value1, value2, ...): Provides the values for each column in the same order.
UPDATE (Modifying Data):
- UPDATE statement: Changes existing data in a table. Be careful with UPDATE! Always use a WHERE clause to specify which rows to update.
```
        UPDATE users SET email = 'john.newemail@example.com' WHERE user_id = 10;
```
  IGNORE_WHEN_COPYING_START
  
  content_copy download
  
  Use code with caution.SQL
  
  IGNORE_WHEN_COPYING_END
  - UPDATE table_name SET column1 = new_value1, column2 = new_value2, ...: Specifies the table and columns to update and their new values.
  - WHERE condition: Crucially important! It filters which rows get updated. Without WHERE, you'd update all rows in the table!
DELETE (Removing Data):
- DELETE FROM statement: Removes rows from a table. Be even more careful with DELETE! Always use a WHERE clause.
```
        DELETE FROM users WHERE user_id = 10;
```
  IGNORE_WHEN_COPYING_START
  
  content_copy download
  
  Use code with caution.SQL
  
  IGNORE_WHEN_COPYING_END
  - DELETE FROM table_name: Specifies the table to delete from.
  - WHERE condition: Filters which rows to delete. Without WHERE, you'd delete all rows in the table!

Relationships Between Tables (Joins)

Relational Databases are about relationships. Tables often relate to each other. For example, a users table might be related to an orders table (a user can place multiple orders).
Foreign Keys: We use foreign keys to establish relationships. A foreign key in one table references the primary key of another table.
- Example:
```
        CREATE TABLE orders (
      order_id INT PRIMARY KEY AUTO_INCREMENT,
      user_id INT NOT NULL,  -- Foreign key referencing users table
      order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
      total_amount DECIMAL(10, 2),
      FOREIGN KEY (user_id) REFERENCES users(user_id) -- Defines the relationship
  );
```
  IGNORE_WHEN_COPYING_START
  
  content_copy download
  
  Use code with caution.SQL
  
  IGNORE_WHEN_COPYING_END
  - FOREIGN KEY (column_in_this_table) REFERENCES other_table(column_in_other_table): Creates the link. user_id in orders refers to user_id in users.
JOINs (Combining Data from Related Tables): JOIN statements let you retrieve data from multiple related tables in a single query.
- INNER JOIN: Returns rows only when there's a match in both tables based on the join condition.
```
        SELECT users.username, orders.order_id, orders.order_date
  FROM users
  INNER JOIN orders ON users.user_id = orders.user_id;
```
  IGNORE_WHEN_COPYING_START
  
  content_copy download
  
  Use code with caution.SQL
  
  IGNORE_WHEN_COPYING_END
  - This query gets usernames from the users table and corresponding order IDs and dates from the orders table for users who have placed orders.
  - ON users.user_id = orders.user_id: The join condition, linking rows based on matching user_id values.
- LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table (the one listed first in the FROM clause), and matching rows from the right table. If there's no match in the right table, you'll get NULL values for the right table's columns.
```
        SELECT users.username, orders.order_id
  FROM users
  LEFT JOIN orders ON users.user_id = orders.user_id;
```
  IGNORE_WHEN_COPYING_START
  
  content_copy download
  
  Use code with caution.SQL
  
  IGNORE_WHEN_COPYING_END
  - This would show all usernames, and if a user has orders, their order IDs too. Users without orders will still be listed, but order_id will be NULL.
- RIGHT JOIN (or RIGHT OUTER JOIN): Similar to LEFT JOIN but returns all rows from the right table and matching rows from the left.

Database Design Basics (Normalization)

Normalization: Organizing your database tables to reduce redundancy (duplication of data) and improve data integrity (accuracy and consistency). Think of it as organizing your filing cabinet efficiently to avoid errors and make things easier to find.
Why Normalize?
- Reduces Data Redundancy: Storing data only once, avoiding repetition and saving space.
- Improves Data Integrity: Changes to data need to be made in only one place, reducing inconsistencies.
- Makes Updates and Deletions Easier: Less data to change or remove.
Normalization Forms (1NF, 2NF, 3NF, etc.): These are sets of rules for normalization. For most backend development, aiming for 3NF (Third Normal Form) is a good starting point. Basically:
- 1NF (First Normal Form): Each column should contain atomic values (indivisible). No repeating groups of columns.
- 2NF (Second Normal Form): Must be in 1NF. All non-key columns must be fully dependent on the entire primary key. (Relevant when you have composite primary keys).
- 3NF (Third Normal Form): Must be in 2NF. All non-key columns must be dependent only on the primary key and not on other non-key columns. (Eliminate transitive dependencies).

Indexes - Speeding Up Queries

Indexes: Special lookup tables that the database uses to speed up data retrieval. Think of an index in a book – it helps you quickly find the pages you're looking for.
How Indexes Help: Without an index, MySQL might have to scan through the entire table to find rows matching your query (like reading a book from cover to cover to find something). Indexes allow MySQL to jump directly to the relevant data.

Creating Indexes:

        CREATE INDEX idx_username ON users (username); -- Creates an index on the 'username' column of 'users' table.

IGNORE_WHEN_COPYING_START

content_copy download

Use code with caution.SQL

IGNORE_WHEN_COPYING_END

When to Use Indexes:
- Columns frequently used in WHERE clauses (for filtering).
- Columns used in ORDER BY clauses (for sorting).
- Columns used in JOIN conditions.
Trade-offs:
- Indexes speed up reads (SELECT), but can slightly slow down writes (INSERT, UPDATE, DELETE) because the index also needs to be updated.
- Indexes take up storage space.
Don't over-index! Index columns that are commonly used for querying, but avoid indexing every single column.

Transactions - Ensuring Data Consistency

Transactions: A sequence of database operations that are treated as a single unit of work. Think of it like a deal: either all steps in the deal happen successfully, or none of them do. This is crucial for maintaining data consistency.
ACID Properties (Transactions guarantee these):
- Atomicity: All operations in a transaction are treated as one atomic unit. Either all succeed or all fail (rollback).
- Consistency: Transactions bring the database from one valid state to another valid state. They don't violate database rules or constraints.
- Isolation: Transactions are isolated from each other. One transaction's changes are not visible to other concurrent transactions until it's committed.
- Durability: Once a transaction is committed, its changes are permanent, even if there's a system failure.
Transaction Commands:
- START TRANSACTION; or BEGIN;: Starts a transaction.
- COMMIT;: Saves all changes made within the transaction permanently.
- ROLLBACK;: Reverts all changes made within the transaction back to the state before the transaction started (if something goes wrong).

Example (Transferring money between bank accounts):

        START TRANSACTION;
  UPDATE accounts SET balance = balance - 100 WHERE account_id = 1; -- Deduct 100 from account 1
  UPDATE accounts SET balance = balance + 100 WHERE account_id = 2; -- Add 100 to account 2
  COMMIT; -- If both updates succeed, commit the transaction

IGNORE_WHEN_COPYING_START

content_copy download

Use code with caution.SQL

IGNORE_WHEN_COPYING_END

If either of the UPDATE statements fails (e.g., account 1 doesn't have enough balance), you would use ROLLBACK; to undo both changes and keep the accounts consistent.

Security Basics

User Management:
- CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';: Creates a new MySQL user.
  - 'username'@'hostname' specifies the username and where the user can connect from (e.g., 'localhost' for local connections, '%' for any host - be careful with %).
- GRANT privileges ON database.table TO 'username'@'hostname';: Gives specific permissions to a user.
  - Privileges: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALL PRIVILEGES, etc.
  - database.table: Specifies which database and table the privileges apply to (*.* for all databases and tables).
- REVOKE privileges ON database.table FROM 'username'@'hostname';: Removes privileges.
- FLUSH PRIVILEGES;: Reloads the grant tables so changes take effect immediately.
Principle of Least Privilege: Grant users only the minimum privileges they need to do their job. Don't give everyone ALL PRIVILEGES!
Prevent SQL Injection: A major security vulnerability. Always sanitize and parameterize user inputs when building SQL queries in your backend code. Never directly embed user input into SQL strings. Use prepared statements or parameterized queries provided by your programming language's database libraries.

Performance Optimization - Making Things Faster

Query Optimization: Writing efficient SQL queries.
- Use WHERE clauses effectively: Filter data as early as possible to reduce the amount of data processed.
- Avoid SELECT * when you only need specific columns.
- Use JOINs efficiently: Choose the right type of join and ensure join conditions are indexed.
- Analyze query performance with EXPLAIN: EXPLAIN SELECT ... command shows how MySQL executes a query, helping you identify bottlenecks and areas for improvement (like missing indexes).
Indexing (as discussed earlier): Proper indexing is crucial for performance.
Caching: Storing frequently accessed data in memory (e.g., using a caching layer like Redis or Memcached) to reduce database load.
Connection Pooling: Reusing database connections instead of creating new ones for each request, which is more efficient. Your backend framework/libraries usually handle connection pooling.
Database Tuning: MySQL has configuration settings that can be tuned for performance depending on your workload. This is more advanced.

Basic MySQL Administration (For Backend Devs)

Connecting to MySQL: You'll use a MySQL client (command-line mysql client, GUI tools like MySQL Workbench, DBeaver, or database libraries in your programming language) to connect to your MySQL server. You'll need:
- Hostname (usually localhost if it's on your machine, or the server's IP address).
- Port (default is 3306).
- Username.
- Password.
- Database name (optional initially, you can select a database after connecting).
Running SQL Commands: Once connected, you can type and execute SQL commands directly.
Backup and Restore (Conceptual): Important for data safety.
- Backup: Regularly create copies of your database data. Tools like mysqldump can create SQL scripts or binary backups.
- Restore: Use backups to recover your database in case of data loss or failures.

Tools You'll Use:

MySQL Server: The actual database server software. You'll need to install and configure it.
MySQL Client (Command Line): mysql command-line tool for interacting with MySQL.
GUI Tools (Optional but helpful): MySQL Workbench, DBeaver, HeidiSQL, etc. Provide visual interfaces for database management, query writing, etc.
Database Libraries/ORMs in your Programming Language: Libraries like mysql-connector-python, node-mysql, JDBC (Java), etc., allow your backend code to connect to and interact with MySQL. ORMs (Object-Relational Mappers) like Django ORM, SQLAlchemy, Hibernate, etc., provide a higher-level abstraction to work with databases, often making database interactions easier in code.

Beyond the Basics (For Later Learning):

Stored Procedures and Functions: Precompiled SQL code stored in the database, can improve performance and code reusability.
Views: Virtual tables based on the result-set of SQL statements. Simplify complex queries and provide data abstraction.
Triggers: Automatic actions that are executed in response to certain events (e.g., INSERT, UPDATE, DELETE) on a particular table.
Advanced Indexing Techniques: Composite indexes, covering indexes, etc.
Replication and Clustering: Setting up multiple MySQL servers for high availability, scalability, and disaster recovery.
NoSQL Databases: Understanding when and why to use NoSQL databases (like MongoDB, Cassandra) instead of or alongside relational databases like MySQL.

Practice is Key!

The best way to learn MySQL is to practice.

Install MySQL Server and a Client: Get MySQL set up on your computer.
Create Databases and Tables: Design simple databases for projects you're interested in (e.g., a blog, a to-do list, a simple e-commerce site).
Write SQL Queries: Practice writing SELECT, INSERT, UPDATE, DELETE, JOIN queries.
Use a GUI Tool: Explore a GUI tool to visualize your database and help with query building.
Integrate with Your Backend Code: Connect your backend application (in your chosen programming language) to your MySQL database and perform CRUD operations from your code.

MySQL for backend developers, in simple words

Subscribe to my newsletter

Singaraju Saiteja

Singaraju Saiteja