MySQL Guide

MySQL is a powerful RDBMS(Relational Database Management System) which uses Structured Query Language to manage data.

On Linux, MySQL runs as a background service. You must know how to manage the process itself.

Start Service: sudo systemctl start mysql
Stop Service: sudo systemctl stop mysql
Check Status: sudo systemctl status mysql
Secure Installation: sudo mysql_secure_installation (Run this after first install to set root passwords).

1. Installation & Connection

Platform	Command / Method	Default Client
Ubuntu/Debian	`sudo apt install mysql-server`	mysql-client
CentOS/RHEL	`sudo dnf install mysql-server`	mysql-client
Connection	`mysql -u root -p`	Terminal

Terminal Commmands:

# Step 1: Update your local package index
sudo apt update

# Step 2: Install the MySQL Server package
sudo apt install mysql-server -y

# Step 3: Verify the service is running
sudo systemctl status mysql

# Optional: Ensure MySQL starts automatically on boot
sudo systemctl enable mysql

# Step 4:Security Setup
sudo mysql_secure_installation

# Step 5:Login to mySQL
sudo mysql -u root -p

2. Database & User Management

-- Create a new database
CREATE DATABASE school_db;

-- Switch to the new database
USE school_db;

-- 1. Create a new user with a secure password
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

-- 2. Grant the user full control over the specific database
GRANT ALL PRIVILEGES ON school_db.* TO 'username'@'localhost';

-- grant all privileges to user on all databases
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost';

-- 3. Refresh the internal permission tables
FLUSH PRIVILEGES;

-- 4. Check existing users (Optional)
SELECT user, host FROM mysql.user;

3. Tables – Creation & Management

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    dob DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Modify table
ALTER TABLE students ADD COLUMN phone VARCHAR(15);
ALTER TABLE students DROP COLUMN phone;
ALTER TABLE students RENAME COLUMN dob TO birth_date;

-- Temporary & Copy table
CREATE TEMPORARY TABLE temp_students LIKE students;
CREATE TABLE archive_students AS SELECT * FROM students;

4. Basic CRUD Operations

-- CREATE
INSERT INTO students (first_name, last_name, email)
VALUES ('john', 'doe', 'johndoe@gmail.com');

-- Multiple rows
INSERT INTO students (first_name, last_name)
VALUES
    ('John', 'Dow'),
    ('David', 'Ticker');

-- READ
SELECT * FROM students;
SELECT first_name, email FROM students WHERE id > 5;

-- UPDATE
UPDATE students
SET email = 'newemail@gmail.com'
WHERE id = 3;

-- DELETE
DELETE FROM students WHERE id = 10;
TRUNCATE TABLE log_entries;   -- Faster than DELETE (no rollback)

5. Important Clauses

SELECT department, COUNT(*) as count
FROM employees
WHERE salary > 50000
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY count DESC
LIMIT 3 OFFSET 1;

6.Operators

Category	Operators
Comparison	=, !=, <, >, <=, >=, BETWEEN, IN, LIKE, IS NULL
Logical	AND, OR, NOT
Set	UNION, UNION ALL, INTERSECT,

SELECT * FROM products
WHERE name LIKE '%phone%'
AND price BETWEEN 15000 AND 80000;

7.Aggregate Functions

SELECT
    department,
    COUNT(*) AS total,
    AVG(salary) AS avg_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary,
    SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000
ORDER BY total_salary DESC;

8.Joins (The Most Important Topic)

Join Type	Description	Matching Rows
INNER JOIN	Only matching records	Both tables
LEFT JOIN	All from left + matching from right	Left + match
RIGHT JOIN	All from right + matching from left	Right + match
FULL JOIN	All records from both (MySQL: UNION)	Both
CROSS JOIN	Cartesian product	All × All
SELF JOIN	Table joined with itself	—

SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

9. Built-in Functions

String Functions:
- CONCAT(), UPPER(), LOWER(), TRIM(), SUBSTRING(), REPLACE(), LENGTH()
Date/Time Functions:
- NOW(), CURDATE(), DATE_FORMAT(), DATEDIFF(), DATE_ADD()

SELECT
    name, salary,
    RANK() OVER (ORDER BY salary DESC) AS rank,
    ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_rank
FROM employees;

10. Performance & Security

Performance Tips

1.Use proper indexes

2.Avoid SELECT * in production

3.Use EXPLAIN to analyze queries

4.Prefer JOIN over subqueries when possible

Security Best Practices

1.Never concatenate user input directly

2.Always use Prepared Statements / Parameterized Queries

3.Least privilege principle for users

4.Enable SSL for remote connections

11. Quick Reference Cheat Sheet

-- Most used commands at a glance

SHOW DATABASES;
USE mydb;

SHOW TABLES;
DESCRIBE students;

SELECT COUNT(*) FROM students;
SELECT * FROM students LIMIT 10;

-- Fast clear table
TRUNCATE TABLE temp_data;

-- Current date & time
SELECT NOW(), CURDATE(), CURTIME();

MySQL Tutorial