Araz Gholami

Data runs the world. Whether you’re building a website, analyzing customer behavior, or automating tasks, you’ll eventually need to store, retrieve, and manipulate data. That’s where MySQL comes in.

MySQL is an open-source relational database management system (RDBMS) that enables efficient storage, retrieval, and management of structured data using Structured Query Language (SQL). It was created in 1995 by Michael “Monty” Widenius, David Axmark, and Allan Larsson under the Swedish company MySQL AB. Initially designed for speed and reliability, it quickly gained popularity as the backbone of many web applications, including WordPress, Facebook, and Twitter. MySQL was acquired by Sun Microsystems in 2008 and later became part of Oracle Corporation in 2010. Today, it continues to evolve as one of the world’s most widely used databases, with a robust community driving innovations and maintaining its relevance across industries.

This guide takes you from setting up a database to mastering the nuances of queries, functions, indexing, and optimization. Each concept is explained with examples so you not only memorize commands but truly understand their power.

Getting Started

Logging In

To access MySQL, use the command line:

mysql -u root -p

You’ll be prompted for the root password (set during installation).

Setting Up Your Database

Creating a New Database

A database is where all your tables and data live. Let’s create one:

CREATE DATABASE my_database; USE my_database;

The USE command switches the current context to your newly created database.

Creating a User

Good security practice dictates not always using the root account. Create a specific user for your database:

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'secure_password';

Granting Privileges

Define what this user can do:

GRANT ALL PRIVILEGES ON my_database.* TO 'new_user'@'localhost'; FLUSH PRIVILEGES;

The FLUSH PRIVILEGES command ensures changes take effect immediately.

Building and Managing Tables

Creating a Table

A table is a structured format for your data. Let’s create one for storing user information:

CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(150) UNIQUE, age INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

• AUTO_INCREMENT: Automatically increments the id for each new row.
• NOT NULL: Ensures the column cannot be empty.
• UNIQUE: Ensures no duplicate values for emails.

Adding Data

Insert rows into your table:

INSERT INTO users (name, email, age) VALUES ('Alice', '[email protected]', 25), ('Bob', '[email protected]', 30);

Querying Your Data

The Basics

Retrieve everything from a table:

SELECT * FROM users;

Retrieve specific columns:

SELECT name, email FROM users;

Filter results using WHERE:

SELECT * FROM users WHERE age > 25;

Using Wildcards with LIKE

Find patterns in your data:

SELECT * FROM users WHERE email LIKE '%@example.com';

• % matches any number of characters.
• _ matches exactly one character.

Updating and Deleting Data

Updating Records

Change data in a row:

UPDATE users SET age = 29 WHERE name = 'Alice';

Deleting Records

Remove unwanted data:

DELETE FROM users WHERE name = 'Bob';

Joins—Linking Tables

Relational databases shine when tables work together. Let’s create another table:

CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT, product_name VARCHAR(100), order_date DATE, FOREIGN KEY (user_id) REFERENCES users(id) );

Insert data into this table:

INSERT INTO orders (user_id, product_name, order_date) VALUES (1, 'Laptop', '2024-01-01'), (1, 'Tablet', '2024-02-01');

Retrieve data across tables with joins:

Inner Join

Only matches with data in both tables:

SELECT users.name, orders.product_name FROM users INNER JOIN orders ON users.id = orders.user_id;

Left Join

Shows all rows from the left table, even if there’s no match in the right table:

SELECT users.name, orders.product_name FROM users LEFT JOIN orders ON users.id = orders.user_id;

Using Functions

MySQL provides built-in functions for common operations.

String Functions

Concatenate strings:

SELECT CONCAT(name, ' - ', email) AS user_info FROM users;

Aggregate Functions

Perform calculations on data:

• Sum up values:

SELECT SUM(age) AS total_age FROM users;

• Count rows:

SELECT COUNT(*) AS user_count FROM users;

• Find averages:

SELECT AVG(age) AS average_age FROM users;

Indexing for Speed

Indexes improve query performance by allowing MySQL to find data faster.

Creating an Index

CREATE INDEX idx_email ON users(email);

Types of Indexes

• Unique Index: Prevent duplicate values.

CREATE UNIQUE INDEX idx_email_unique ON users(email);

• Composite Index: Optimize queries involving multiple columns.

CREATE INDEX idx_name_email ON users(name, email);

Advanced Query Techniques

Subqueries

Use a query within another query:

SELECT name FROM users WHERE id = (SELECT user_id FROM orders WHERE product_name = 'Laptop');

Using CASE for Conditional Logic

Add logic to your queries:

SELECT name, CASE WHEN age < 30 THEN 'Young' ELSE 'Old' END AS age_group FROM users;

Cleanup and Maintenance

Dropping a Table

DROP TABLE orders;

Dropping a Database

DROP DATABASE my_database;

Advanced MySQL: Leveling Up Your Database Skills

You’ve mastered the basics of MySQL—creating databases, querying data, and even optimizing with indexes. But MySQL is a powerhouse that offers far more than meets the eye. If you’re ready to unlock its full potential, this guide dives into advanced techniques that can supercharge your applications and workflows.

Stored Procedures and Functions

What Are They?

Stored procedures and functions are reusable pieces of SQL code that let you encapsulate logic within the database itself.

Creating a Stored Procedure

Let’s create a procedure to insert a new user:

DELIMITER // CREATE PROCEDURE AddUser(IN userName VARCHAR(100), IN userEmail VARCHAR(150), IN userAge INT) BEGIN INSERT INTO users (name, email, age) VALUES (userName, userEmail, userAge); END // DELIMITER ;

Call it like this:

CALL AddUser('David', '[email protected]', 28);

Creating a Function

Functions return a single value. For example, calculate a user’s age in days:

DELIMITER // CREATE FUNCTION AgeInDays(userID INT) RETURNS INT BEGIN DECLARE days INT; SELECT age * 365 INTO days FROM users WHERE id = userID; RETURN days; END // DELIMITER ;

Call it with:

SELECT AgeInDays(1);

Triggers

What Are Triggers?

Triggers execute automatically in response to certain events, like inserts, updates, or deletes.

Example: Logging User Deletions

Let’s create a trigger to log deleted users into an audit_log table:

CREATE TABLE audit_log ( id INT AUTO_INCREMENT PRIMARY KEY, deleted_user VARCHAR(100), deleted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); DELIMITER // CREATE TRIGGER after_user_delete AFTER DELETE ON users FOR EACH ROW BEGIN INSERT INTO audit_log (deleted_user) VALUES (OLD.name); END // DELIMITER ;

Now, deleting a user automatically logs it:

DELETE FROM users WHERE id = 1;

Transactions

Why Use Transactions?

Transactions ensure data integrity by grouping multiple operations together. Either all operations succeed, or none do.

Example: Transferring Money

START TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE user_id = 1; -- Deduct from sender UPDATE accounts SET balance = balance + 100 WHERE user_id = 2; -- Add to recipient COMMIT; -- Save changes

If something goes wrong, roll back:

ROLLBACK;

Advanced Indexing

Covering Indexes

A covering index includes all the columns required by a query.

CREATE INDEX idx_user_search ON users(name, email);

This speeds up queries like:

SELECT name, email FROM users WHERE name = 'Alice';

Full-Text Indexes

Optimize searches for text-heavy data:

CREATE FULLTEXT INDEX idx_fulltext ON users(email); SELECT * FROM users WHERE MATCH(email) AGAINST('example.com');

Partitioning

What Is Partitioning?

Partitioning splits large tables into smaller, more manageable pieces.

Example: Range Partitioning

Partition a table by age groups:

CREATE TABLE users_partitioned ( id INT, name VARCHAR(100), age INT ) PARTITION BY RANGE (age) ( PARTITION p1 VALUES LESS THAN (20), PARTITION p2 VALUES LESS THAN (40), PARTITION p3 VALUES LESS THAN MAXVALUE );

JSON Data Handling

Storing JSON

MySQL supports JSON as a native data type:

CREATE TABLE products ( id INT AUTO_INCREMENT PRIMARY KEY, details JSON ); INSERT INTO products (details) VALUES ('{"name": "Laptop", "price": 1200}');

Querying JSON

Retrieve specific JSON fields:

SELECT details->>'$.name' AS product_name FROM products;

Optimizing Queries

Explain and Analyze

Use EXPLAIN to understand how MySQL processes queries:

EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

Query Cache

Enable query caching for repeated queries:

SET GLOBAL query_cache_size = 1048576; -- 1MB SET GLOBAL query_cache_type = 1;

Replication

What Is Replication?

Replication copies data from a master server to one or more slaves, improving scalability and reliability.

Setting Up Replication

1. Configure the master server:

[mysqld] server-id=1 log-bin=master-bin

1. Configure the slave server:

[mysqld] server-id=2

1. Start replication:

CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='rep_user', MASTER_PASSWORD='rep_pass', MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=4; START SLAVE;

Dynamic Queries with Prepared Statements

Why Use Prepared Statements?

They prevent SQL injection and improve performance for repeated queries.

Example

PREPARE stmt FROM 'SELECT * FROM users WHERE age > ?'; SET @min_age = 25; EXECUTE stmt USING @min_age; DEALLOCATE PREPARE stmt;

Advanced Security

Securing Connections

Force encrypted connections:

ALTER USER 'user'@'localhost' REQUIRE SSL;

Row-Level Security

Limit data access to specific users:

CREATE VIEW user_data AS SELECT * FROM users WHERE id = CURRENT_USER();

With these advanced techniques, you’ve moved beyond using MySQL as just a database. You’re now leveraging it as a platform for robust, secure, and scalable applications.

Start experimenting with these features in a sandbox environment, and remember: mastery comes with practice.