Course Overview

This course teaches you MySQL commands from basics to advanced topics using practical examples. You'll learn how to create databases, manage tables, insert/update/delete data, and write powerful queries.

MySQL Commands Course

MySQL Commands Course

MySQL Commands Course

A Complete Hands-On Guide for Beginners to Intermediate Users

Course Overview

This course teaches you MySQL commands from basics to advanced topics using practical examples. You'll learn how to create databases, manage tables, insert/update/delete data, and write powerful queries.

Duration: Self-paced
Prerequisites: Basic computer knowledge
Tools Needed: MySQL Server (or MySQL-compatible like MariaDB), MySQL Client (CLI or GUI like MySQL Workbench)

Course Modules

Module 1: Getting Started with MySQL

1.1 Install MySQL

# Ubuntu/Debian
sudo apt update && sudo apt install mysql-server

# macOS (using Homebrew)
brew install mysql

# Windows: Download from https://dev.mysql.com/downloads/

1.2 Start MySQL Server

sudo systemctl start mysql    # Linux
brew services start mysql     # macOS

1.3 Login to MySQL

mysql -u root -p

1.4 Basic Commands

Command Description
SHOW DATABASES; List all databases
SELECT VERSION(); Show MySQL version
STATUS; Show current user & connection info
EXIT; or QUIT; Exit MySQL shell

Module 2: Database Management

2.1 Create a Database

CREATE DATABASE school;

2.2 Use a Database

USE school;

2.3 Show Current Database

SELECT DATABASE();

2.4 Drop a Database

DROP DATABASE school;

Module 3: Table Management

3.1 Create a Table

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT CHECK (age >= 15),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

3.2 Show Tables

SHOW TABLES;

3.3 Describe Table Structure

DESCRIBE students;
-- or
DESC students;

3.4 Modify Table (Add Column)

ALTER TABLE students ADD COLUMN grade CHAR(1);

3.5 Modify Column

ALTER TABLE students MODIFY COLUMN age SMALLINT;

3.6 Drop Column

ALTER TABLE students DROP COLUMN grade;

3.7 Rename Table

RENAME TABLE students TO pupils;

3.8 Drop Table

DROP TABLE pupils;

Module 4: Data Manipulation (DML)

4.1 Insert Data

INSERT INTO students (name, email, age) 
VALUES ('John Doe', 'john@example.com', 18);

Multiple Rows:

INSERT INTO students (name, email, age) VALUES
('Alice', 'alice@example.com', 17),
('Bob', 'bob@example.com', 19);

4.2 Select Data

SELECT * FROM students;

SELECT name, email FROM students WHERE age > 17;

SELECT * FROM students ORDER BY name ASC;

SELECT * FROM students LIMIT 5;

4.3 Update Data

UPDATE students 
SET age = 20 
WHERE name = 'John Doe';

4.4 Delete Data

DELETE FROM students WHERE id = 1;

-- Delete all data (keep table)
TRUNCATE TABLE students;

Module 5: Querying Data (Advanced SELECT)

5.1 WHERE Clause Operators

WHERE age BETWEEN 16 AND 18;
WHERE name LIKE 'A%';
WHERE email IS NULL;
WHERE age IN (17, 18, 19);

5.2 Aggregate Functions

SELECT COUNT(*) AS total_students FROM students;
SELECT AVG(age) AS avg_age FROM students;
SELECT MIN(age), MAX(age) FROM students;
SELECT name, age FROM students GROUP BY age;

5.3 JOIN Tables

Create second table:

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(50)
);

INSERT INTO courses VALUES (101, 'Math'), (102, 'Science');

Enrollment table:

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

JOIN Examples:

-- INNER JOIN
SELECT s.name, c.course_name 
FROM students s 
INNER JOIN enrollments e ON s.id = e.student_id
INNER JOIN courses c ON e.course_id = c.course_id;

-- LEFT JOIN
SELECT s.name, c.course_name 
FROM students s 
LEFT JOIN enrollments e ON s.id = e.student_id
LEFT JOIN courses c ON e.course_id = c.course_id;

Module 6: Indexes & Performance

6.1 Create Index

CREATE INDEX idx_name ON students(name);

6.2 Unique Index

CREATE UNIQUE INDEX idx_email ON students(email);

6.3 Show Indexes

SHOW INDEX FROM students;

6.4 Drop Index

DROP INDEX idx_name ON students;

Module 7: Users & Privileges

7.1 Create User

CREATE USER 'student_user'@'localhost' IDENTIFIED BY 'password123';

7.2 Grant Privileges

GRANT SELECT, INSERT ON school.* TO 'student_user'@'localhost';

7.3 Show Grants

SHOW GRANTS FOR 'student_user'@'localhost';

7.4 Revoke & Drop

REVOKE INSERT ON school.* FROM 'student_user'@'localhost';
DROP USER 'student_user'@'localhost';

Module 8: Backup & Restore

8.1 Backup Database (mysqldump)

mysqldump -u root -p school > school_backup.sql

8.2 Restore Database

mysql -u root -p school < school_backup.sql

Module 9: Useful Commands Cheat Sheet

Command Purpose
SELECT NOW(); Current date & time
SELECT USER(); Current user
SHOW PROCESSLIST; Active connections
SHOW VARIABLES LIKE 'version'; Config variables
EXPLAIN SELECT * FROM students; Query execution plan

Final Project: Build a Student Management System

  1. Create database college_db
  2. Create tables: students, departments, enrollments
  3. Insert 10+ sample records
  4. Write queries:
  5. List students in "Computer Science"
  6. Count students per department
  7. Find students older than average age
  8. Create a view: active_students
  9. Backup the database

Certification Quiz (Sample Questions)

  1. What does AUTO_INCREMENT do?
  2. Difference between DELETE and TRUNCATE?
  3. How to select unique emails from a table?
  4. Write a query to find 2nd highest age.

Resources

Congratulations!
You now know MySQL commands like a pro!

Save this course as mysql_course.md and practice daily.

Want a PDF version or video walkthrough? Let me know!

Last updated: Nov 09, 2025

Course Overview

This course teaches you MySQL commands from basics to advanced topics using practical examples. You'll learn how to create databases, manage tables, insert/update/delete data, and write powerful queries.

MySQL Commands Course

MySQL Commands Course

MySQL Commands Course

A Complete Hands-On Guide for Beginners to Intermediate Users

Course Overview

This course teaches you MySQL commands from basics to advanced topics using practical examples. You'll learn how to create databases, manage tables, insert/update/delete data, and write powerful queries.

Duration: Self-paced
Prerequisites: Basic computer knowledge
Tools Needed: MySQL Server (or MySQL-compatible like MariaDB), MySQL Client (CLI or GUI like MySQL Workbench)

Course Modules

Module 1: Getting Started with MySQL

1.1 Install MySQL

# Ubuntu/Debian
sudo apt update && sudo apt install mysql-server

# macOS (using Homebrew)
brew install mysql

# Windows: Download from https://dev.mysql.com/downloads/

1.2 Start MySQL Server

sudo systemctl start mysql    # Linux
brew services start mysql     # macOS

1.3 Login to MySQL

mysql -u root -p

1.4 Basic Commands

Command Description
SHOW DATABASES; List all databases
SELECT VERSION(); Show MySQL version
STATUS; Show current user & connection info
EXIT; or QUIT; Exit MySQL shell

Module 2: Database Management

2.1 Create a Database

CREATE DATABASE school;

2.2 Use a Database

USE school;

2.3 Show Current Database

SELECT DATABASE();

2.4 Drop a Database

DROP DATABASE school;

Module 3: Table Management

3.1 Create a Table

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT CHECK (age >= 15),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

3.2 Show Tables

SHOW TABLES;

3.3 Describe Table Structure

DESCRIBE students;
-- or
DESC students;

3.4 Modify Table (Add Column)

ALTER TABLE students ADD COLUMN grade CHAR(1);

3.5 Modify Column

ALTER TABLE students MODIFY COLUMN age SMALLINT;

3.6 Drop Column

ALTER TABLE students DROP COLUMN grade;

3.7 Rename Table

RENAME TABLE students TO pupils;

3.8 Drop Table

DROP TABLE pupils;

Module 4: Data Manipulation (DML)

4.1 Insert Data

INSERT INTO students (name, email, age) 
VALUES ('John Doe', 'john@example.com', 18);

Multiple Rows:

INSERT INTO students (name, email, age) VALUES
('Alice', 'alice@example.com', 17),
('Bob', 'bob@example.com', 19);

4.2 Select Data

SELECT * FROM students;

SELECT name, email FROM students WHERE age > 17;

SELECT * FROM students ORDER BY name ASC;

SELECT * FROM students LIMIT 5;

4.3 Update Data

UPDATE students 
SET age = 20 
WHERE name = 'John Doe';

4.4 Delete Data

DELETE FROM students WHERE id = 1;

-- Delete all data (keep table)
TRUNCATE TABLE students;

Module 5: Querying Data (Advanced SELECT)

5.1 WHERE Clause Operators

WHERE age BETWEEN 16 AND 18;
WHERE name LIKE 'A%';
WHERE email IS NULL;
WHERE age IN (17, 18, 19);

5.2 Aggregate Functions

SELECT COUNT(*) AS total_students FROM students;
SELECT AVG(age) AS avg_age FROM students;
SELECT MIN(age), MAX(age) FROM students;
SELECT name, age FROM students GROUP BY age;

5.3 JOIN Tables

Create second table:

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(50)
);

INSERT INTO courses VALUES (101, 'Math'), (102, 'Science');

Enrollment table:

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

JOIN Examples:

-- INNER JOIN
SELECT s.name, c.course_name 
FROM students s 
INNER JOIN enrollments e ON s.id = e.student_id
INNER JOIN courses c ON e.course_id = c.course_id;

-- LEFT JOIN
SELECT s.name, c.course_name 
FROM students s 
LEFT JOIN enrollments e ON s.id = e.student_id
LEFT JOIN courses c ON e.course_id = c.course_id;

Module 6: Indexes & Performance

6.1 Create Index

CREATE INDEX idx_name ON students(name);

6.2 Unique Index

CREATE UNIQUE INDEX idx_email ON students(email);

6.3 Show Indexes

SHOW INDEX FROM students;

6.4 Drop Index

DROP INDEX idx_name ON students;

Module 7: Users & Privileges

7.1 Create User

CREATE USER 'student_user'@'localhost' IDENTIFIED BY 'password123';

7.2 Grant Privileges

GRANT SELECT, INSERT ON school.* TO 'student_user'@'localhost';

7.3 Show Grants

SHOW GRANTS FOR 'student_user'@'localhost';

7.4 Revoke & Drop

REVOKE INSERT ON school.* FROM 'student_user'@'localhost';
DROP USER 'student_user'@'localhost';

Module 8: Backup & Restore

8.1 Backup Database (mysqldump)

mysqldump -u root -p school > school_backup.sql

8.2 Restore Database

mysql -u root -p school < school_backup.sql

Module 9: Useful Commands Cheat Sheet

Command Purpose
SELECT NOW(); Current date & time
SELECT USER(); Current user
SHOW PROCESSLIST; Active connections
SHOW VARIABLES LIKE 'version'; Config variables
EXPLAIN SELECT * FROM students; Query execution plan

Final Project: Build a Student Management System

  1. Create database college_db
  2. Create tables: students, departments, enrollments
  3. Insert 10+ sample records
  4. Write queries:
  5. List students in "Computer Science"
  6. Count students per department
  7. Find students older than average age
  8. Create a view: active_students
  9. Backup the database

Certification Quiz (Sample Questions)

  1. What does AUTO_INCREMENT do?
  2. Difference between DELETE and TRUNCATE?
  3. How to select unique emails from a table?
  4. Write a query to find 2nd highest age.

Resources

Congratulations!
You now know MySQL commands like a pro!

Save this course as mysql_course.md and practice daily.

Want a PDF version or video walkthrough? Let me know!

Last updated: Nov 09, 2025